Problem/Motivation
Currently Drupal requires MySQL 5.1.21
but we should increase that requirement to at least 5.5.3
(released in 03/2010) in order to be able to use utf8mb4
encoding by default. However, to also force a more secure (and mature) 5.5 version, @crell advocated to raise it even further to 5.5.37
, released on 2014-03-27 and present in most current Linux distros.
See #1314214: MySQL driver does not support full UTF-8 (emojis, asian symbols, mathematical symbols).
Versions of MySQL in shipping distributions:
* Debian wheezey: 5.5.41-0+wheezy1
* OpenSuse: 5.5.42
* Ubuntu 12.04 LTS: 5.5.41
* RedHat EL 5.11: 5.5.37
* CentOS 5: 5.5.37
Hosting utilities:
cPanel & WHM 11.44 (2014-05) require MySQL 5.5
Hosts:
Rackspace offers 5.6 by default while still supporting 5.1
Proposed resolution
Instead of only increasing the minimum version of MySQL it was decided that we better check for the feature in question to be available by doing a "set names utf8mtb4". This is more self explaining and allows for the fact that the different databases that this driver supports might have added this feature in different versions. Might have as according to comment #32, the feature divergence between MySql and MariaDb only started as of version 5.6. so in the future a pure version check might prove not usable.
Remaining tasks
None
Beta phase evaluation
Issue category | Task because version requirement change |
---|---|
Issue priority | Major because this blocks #1314214: MySQL driver does not support full UTF-8 (emojis, asian symbols, mathematical symbols) |
Disruption | Not disruptive as major commonly used linux installs support 5.5 |
Comment | File | Size | Author |
---|---|---|---|
#52 | 2473301-52.patch | 2.08 KB | jcnventura |
#52 | interdiff.txt | 846 bytes | jcnventura |
Comments
Comment #1
yannickooComment #2
morgantocker CreditAttribution: morgantocker commentedMorgan from the MySQL team here. Some context on release dates of 5.5 and supportability:
- As noted, the MySQL team released 5.5 in 2010. I would suggest a minimum of 5.5.8, since this is the GA version.
- 5.1 was released in 2008. It is no longer officially supported, which means security vulnerabilities will not be fixed etc.
- 5.5+ is in all the major linux distros.
- 5.5 is also the first version where InnoDB became the default (and it contains a number of InnoDB perf fixes). Since Drupal only supports transactional tables, this is also quite a big deal.
I think this is a good idea.
Comment #3
fietserwinSome remarks:
With a Beta phase evaluation added to the issue summary I would RTBC.
Comment #4
stefan.r CreditAttribution: stefan.r commentedAdded beta phase evaluation
Comment #5
Crell CreditAttribution: Crell at Palantir.net commentedWait, you mean we didn't do this already? How did I miss that?
Yes, we're even considering moving straight to PHP 5.5 as PHP 5.4 will be out of security support and totally unsupported by the time Drupal 8.0.0 is tagged. If we're even thinking of doing that, we definitely shouldn't have any question about dropping long-dead MySQL versions.
The only question for me is which 5.5.x we require. Can someone do a quick audit of the major distros and see what they ship in their current stable and current LTS release? And if we can get word on what the major web hosts support that would be good as well. (The Drupal-specific hosts like Acquia and Pantheon are irrelevant; they'll upgrade for Drupal 8 support whatever it is.)
Comment #6
fietserwin- A large dutch hoster on which we run some sites has 5.5.41-0+wheezy1-log (and PHP5.6.5-1)
- I have a client on 1and1 (unlimited plus) running 5.1.73-log with maxallowedpacket at 10MB (and PHP5.4.39). It's about time we start to ignore 1and1. They prefer laziness above client satisfaction.
- In general: on their webstes, hosters are not very informative about versions they offer, so this is a bit hard to find out.
Distros:
- Debian wheezey: 5.5.41-0+wheezy1
- OpenSuse: 5.5.42
Comment #7
stefan.r CreditAttribution: stefan.r commentedMore distros:
Ubuntu 12.04 LTS: 5.5.41
RedHat EL 5.11: 5.5.37
CentOS 5: 5.5.37
Hosting utilities:
cPanel & WHM 11.44 (2014-05) require MySQL 5.5
Hosts:
Rackspace offers 5.6 by default while still supporting 5.1
Comment #8
fietserwin- So most hosters and many distros would conform to our raised requirement.
- As to answer #5 "... which 5.5.x version ...": IMO this remains a functionality requirement, not a security, nor performance issue. We could warn for that in the status report, but that is outside the scope of this issue. (To give some security issues insight: 5.5.8 contains 10 security fixes, 5.5.9: 1, 5.5.23: 1 that's what popped up in searches, I did not check each changelog page in detail).
So for me using 5.5.3 instead of e.g. 5.5.8 or 5.5.37, is RTBC.
However, I am not sure about versioning and feature parity of MariaDb that is also covered by this driver, as are Pecona server and other equivalents according to the name() method, but I guess their version numbers are more or less equal. If not we might have to change the requirement to a feature discovery instead of a mere version check. Is there someone who better knows if and how MariaDb differs from MySql?
Comment #9
stefan.r CreditAttribution: stefan.r commentedYep Percona and MariaDB 5.5.3 and up (including all releases in the 10.x series) provide utf8mb4 support. I don't know that Percona did a 5.5.3 release but their 5.5.8 release is OK.
Comment #10
bojanz CreditAttribution: bojanz commentedDo we know the EOL date for MySQL 5.5?
MySQL 5.6 (and MariaDB since 5.3) is the first version that makes subqueries fast enough to be useful, being able to rely on that could completely change the way we write queries that currently require joins for filtering (Views, query access).
Comment #11
stefan.r CreditAttribution: stefan.r commentedThe 5.5 GA release was on Dec 15, 2010 and according to the support policy at https://www.mysql.com/support/ looks like this may still be supported for a while...
Comment #12
Crell CreditAttribution: Crell commentedAdded the info on distributions to the issue summar...
So far it looks to me like bumping to 5.5.37 is a total no-brainer and completely safe. 5.6 would be harder to digest as the current distributions are all shipping 5.5.x still.
Comment #13
morgantocker CreditAttribution: morgantocker commentedI think it's nicer to bump EOL by major version one at a time (5.1->5.5->5.6). My reason is that features are deprecated in one version, to be removed in the next. An upgrade of two versions could move users (and module authors) straight from feature working to removed.
Comment #14
Damien Tournoud CreditAttribution: Damien Tournoud at Centarro commentedWhy do we need a version check? This sounds like a major case of Better to ask for forgiveness than permission.
Comment #15
Damien Tournoud CreditAttribution: Damien Tournoud at Centarro commentedTo expand on the previous: it is really time to move to feature-based checks, not version checks, especially now that MariaDB has cut ties with MySQL's versioning scheme.
Comment #16
stefan.r CreditAttribution: stefan.r commentedAs far as this issue and the MySQL driver is concerned a check for the utf8mb4 character set should be enough, we could then skip the version check in the MySQL driver.
Comment #17
fietserwin+1 for feature based checks, though fast subqueries (#10) can probably only discovered via a version check, which (#10, #15) will have to include the name of the actual db used by this driver.
Setting to NW to have the requirement changed into a check on the availability of the utf8mb4 character set.
Comment #18
stefan.r CreditAttribution: stefan.r commentedComment #19
stefan.r CreditAttribution: stefan.r commentedComment #20
fietserwinThanks for changing the patch to a feature check.
In core\lib\Drupal\Core\Database\Install\Tasks.php the messages are not translated. Looking further, what is done with these tasks: they are passed to runTestquery() which indeed will do a t() itself. So using t() is not needed here. So you r patch from #18 is closer.
Furthermore, to get the driver name, %name may be used in these messages, see runTestQuery(). That should do what you probably wanted to do with the change between #18 and #19.
Comment #21
stefan.r CreditAttribution: stefan.r commentedComment #23
stefan.r CreditAttribution: stefan.r commentedComment #24
fietserwinThanks, looks good to me.
Comment #25
yannickooYeah great!
Comment #26
catchFine with me. Leaving this for enough time for another committer to object if they want to.
Comment #27
fietserwinThanks @catch.
Changed the proposed solution section in the issue summary to reflect that we no longer check for a version but for a feature.
Comment #28
catchAlso tagging for backport.
Comment #29
Crell CreditAttribution: Crell at Palantir.net commentedFeature detection is well and good, but "bug avoidance" is also a factor in version bumping. As noted above it looks like 5.5.37 is probably safe, and I'm sure there's plenty of bug and security issues in those versions.
It's also easier on users if we document a clear version number, on Drupal.org as well, rather than "some version with features X, and Y, and Z". Putting an explicit 5.5.3 or later on d.o's requirements pages is a must, even if we use feature detection in the code.
Why would we do this for MySQL but not for PHP? We have a straight version number for PHP.
Comment #30
webchickLooks like this is still in discussion.
I think the reason for feature detection vs. version detection though is that there are effectively "two" MySQLs and only one PHP.
Comment #31
catchd.o/requirements should have a version number yes.
The issue here is whether we're likely to run into a situation where MariaDB/Percona failed the requirements check due to the version number, despite having the correct capability.
We had a similar thing with Debian versions of PHP (since they were backporting the fixes we needed without incrementing the version number), but then Debian fixed PHP version numbers before that issue ever got resolved.
Comment #32
morgantocker CreditAttribution: morgantocker commentedThe feature divergence started after 5.5, so having version detection for 5.5.x as a minimum is quite safe.
If feature detection is in the tests, it will also make it easier for upstream MySQL to be careful when deprecating and removing functionality. There are cases (such as weak crypto functions) where functionality in newer versions will be removed.
Comment #33
Crell CreditAttribution: Crell at Palantir.net commentedSilly question: Is there any reason we can't do both checks? Ie, you need at least 5.5.3 (or 37, which I think is still safe) AND utf8mb is required? There's plenty of other nominally-optional parts of MySQL we already require so that would be consistent.
If MariaDB is sufficiently divergent from MySQL, we may at some point just make it its own driver; its classes can just extend the MySQL classes in the 99% case but it would let us do separate version and compatibility checks.
Comment #34
fietserwinConsidering #32 (it is still safe to do this for both MySql and MariaDb) and #33 (one of the top D8 architects saying it is OK), I propose to revert the change in the minimumVersion() method and make it look for 5.5.37 (release date 2014-03-27, just over a year old now). @stefan.r?
Comment #35
stefan.r CreditAttribution: stefan.r commentedI actually think #33 is not a bad idea at least for now. When writing the feature tests we assume we are on 5.5.3 anyway so an extra version check just codifies that assumption and makes the check more robust. I like the idea of just giving MariaDB/Percona their own driver whenever necessary.
Personally I'd lean towards requiring 5.5.3 for now (versus 5.5.37) as it's a larger potential audience for Drupal 8, many organizations are slow with their upgrades so requiring a one year old release seems a bit aggressive. I understand supporting older versions implies more risk and a bigger maintenance burden, and I do see the point about babysitting our users not to use versions with severe issues, but there seem to be no severe known issues between those minor versions and according to @morgantocker we ought to be safe.
Also if nobody argues strongly for one version or another and we want to leave options open, we could also just go with 5.5.3, stating on the requirements page that the 5.5.3 requirement is tentative and may be raised as we re-evaluate it prior to some milestone (such as an 8.0.0 release/release candidate).
Comment #36
stefan.r CreditAttribution: stefan.r commentedComment #37
Crell CreditAttribution: Crell at Palantir.net commentedExcept that's not true as of the very next line. :-)
Comment #38
stefan.r CreditAttribution: stefan.r commentedComment #39
jcnventura CreditAttribution: jcnventura commentedI totally support this.
Way better than bumping to 5.1.31 to support #567148: Use ONLY_FULL_GROUP_BY for MySQL.
Comment #40
fietserwinFor me it is RTBC. Security updates are normally advertised via messages in red or the status report, not via refusing to install/work.
If security and performance are to be taken into account, then, given #10 and all the focus on performance improvements from last week, we should better raise the requirement to 5.6, but to me that seems far too aggressive and completely beyond the point of this issue.
I leave setting it to RTBC to one of the other followers.
Comment #41
jcnventura CreditAttribution: jcnventura commentedNo you didn't :) But +1 from me anyway.
Comment #42
Crell CreditAttribution: Crell at Palantir.net commentedI think we need a decision from Dries for bumping the version.
Dries, please do one of the following:
1) Commit as-is (ie, increase MySQL required version to 5.5.3.)
2) Mark CNW with approval to move to 5.5.37.
I favor option 2, based on the stats in the issue summary.
Edit: Fix typo.
Comment #43
joelpittetI'm in favour of @Crell's option 2 as well because I noticed this:
In the issue summary of #1314214: MySQL driver does not support full UTF-8 (emojis, asian symbols, mathematical symbols)
Comment #44
webchickJust indicating that this is blocking #567148: Use ONLY_FULL_GROUP_BY for MySQL.
Comment #45
stefan.r CreditAttribution: stefan.r commented@joelpittet actually we won't need
innodb_large_prefix
anymore as soon as #1923406: Use ASCII character set on alphanumeric fields so we can index all 255 characters goes in. Nor do we really want to require that as it's a nonstandard setting which requires further specific settings (Barracuda + DYNAMIC row storage), which many users won't have control over.MySQL will actually automatically index the first 191 characters on utf8mb4 VARCHAR fields, and for UTF8 primary key/unique fields we can just solve the whole issue by limiting the size on those fields to 191. I don't think we have any such fields left in core anyway after that patch goes in.
Comment #46
joelpittetThanks @stefan.r I'll try not to put too many emoji's in my primary keys:P
Comment #47
webchickActually. According to #2457875: [policy] Evolving and documenting Drupal core's structure, responsibilities, and decision-making I don't think we need to wait on Dries for changes like this anymore. We simply need a framework manager and product manager to sign off, since raising minimum versions of PHP/MySQL has significant impacts to both the product and framework, but it is not fundamentally changing the direction/nature of Drupal itself for which we'd need the BDFL involved. Yay, explicit governance.
So catch (framework manager) already provided sign-off in #26. I'm a product manager, and reviewing the issue summary I don't see any downside with going with 5.5. It's supported by all major distros.
However, there's not a lot of research in the issue summary however about hosts, particularly non-dedicated hosts, so I just capitalized on my early morning jet lag and did some of that using http://trends.builtwith.com/hosting/shared-hosting and https://www.drupal.org/hosting as resources. It is shockingly difficult to find out this information, particularly to the 5.5.xx level.
No problem
- Bluehost has 5.5.6 (source: webchat, since https://my.bluehost.com/cgi/help/3 is geared towards people with an account already)
- GoDaddy has "5.5" when I asked what the last digits were, the agent said "I do not have that we update when the version is clean and it working properly. We are current." (source: webchat, since https://support.godaddy.com/help/article/221/checking-your-mysql-databas... is geared towards people with an account already)
- Acquia Cloud ran Percona 5.5.24 back in October 2014, though are/were working on upgrading to Percona 5.5.40 (source: https://docs.acquia.com/articles/mysql-vulnerability-oct-2014)
Probably ok?
- https://wordpress.org/about/stats/ shows "5.5" as 60.3% of the pie, though it's unclear if that means 5.5.3 or 5.5.37. And these stats will generally be for the barest of bones shared hosting.
- Couldn't find any info for Pantheon, but I imagine they're fine.
Uh-oh
- DreamHost still has 5.1.56, according to http://wiki.dreamhost.com/MySQL#All_about_DreamHost.27s_MySQL_offerings, though the second link in Google was this 6-page forum thread of people raging about the fact that it hasn't moved to 5.5 dating back to 2013. https://discussion.dreamhost.com/thread-138999.html There I also learned that Moodle's required 5.5.31 since May 2014. (WordPress also recommends 5.5 or greater, though they only require 5.1).
---
So given that the only two hosts I was able to find definitive information on are earlier than 5.5.37, and given that there was only one host which was woefully out of date (and has plenty of users angry about it), let's go with 5.5.3.
Leaving this at RTBC for another day or so in case there are any objections.
Comment #48
Crell CreditAttribution: Crell at Palantir.net commentedAs noted above, the Drupal-specific hosts are irrelevant; Acquia, Pantheon, Platform.sh, WebEnabled, and the like will install whatever version we require, since they know Drupal 8 will be a major part of their business. As with PHP, I suspect Drupal compatibility would push a few other hosts to get off their duff, too.
That said, since we've already indicated that increasing the PHP required version during the 8.x cycle is on the table I think it's reasonable to state that the same is true for MySQL. So I can go with 5.5.3 for now and bump it again later if we run into bugs that are most easily fixed by bumping the version.
webchick: If you agree that MySQL is point-release safe to increase version on, let's go ahead and commit.
Comment #49
David_Rothstein CreditAttribution: David_Rothstein commentedThis needs to update INSTALL.txt as well (which apparently hasn't been updated for this since Drupal 7). I don't think there's anywhere else.
So is it correct that the minimum MariaDB and Percona versions would be the same as MySQL? That's what I assumed in the attached.
By the way, this issue is marked for Drupal 7 backport, but would there really be a good reason to change this in Drupal 7? We might make 5.5.3 the recommended minimum, but I don't think we'd change the actual minimum without a very good reason. I would guess we can do #1314214: MySQL driver does not support full UTF-8 (emojis, asian symbols, mathematical symbols) with a version check for Drupal 7 and therefore not actually change the minimum required version (isn't that how WordPress did it?).
Comment #50
Crell CreditAttribution: Crell at Palantir.net commentedAccording to MariaDB's site, 5.5 -> 5.5 but they don't mention a patch version. Perhaps for MariaDB we should just say 5.5? Unless someone has more specific information?
catch marked this for backport; you'd have to ask him why. :-) I wouldn't recommend doing so unless it was part of a specific bugfix, such as the one David links to. I am OK with doing it as part of such a fix if it's handled properly. (We can discuss what properly means in that issue, rather than here.)
Edit: Oops, forgot link:
https://mariadb.com/kb/en/mariadb/mariadb-vs-mysql-compatibility/
Comment #51
jcnventura CreditAttribution: jcnventura commentedThere was never a MariaDB 5.5.3. They started already at 5.5.20 (https://downloads.mariadb.org/mariadb/+releases/). I take it that means they started the 5.5 branch including all the changes from MySQL 5.5.20. So, yes it does not make any sense to require MariaDB >= 5.5.3, but I take it that it makes even less sense to require MariaDB 5.5.23 (the first stable release).
Sooner or later we will have to create sub-classes to hold the differences between the three forks. But I think we should reply "Not today" to the god of class inheritance.
And as for a backport.. I'd be strongly against it. We can maybe use a patch like the one I created for #567148, but we should not raise the minimum system requirements. https://www.drupal.org/node/567148#comment-3330582
Comment #52
jcnventura CreditAttribution: jcnventura commentedVery slight changes to reflect the first actual releases for MariaDB and Percona, as these never had a 5.5.3 release. Changes are only to the INSTALL.txt file.
Comment #55
stefan.r CreditAttribution: stefan.r commentedINSTALL.txt changes look good, so back to RTBC.
What to do with the backport seems still an open question, maybe @catch can clarify. Instead of raising requirements we could also just allow both utf8 and utf8mb4 in D7 instead.
Comment #56
catchIt'd be feasible to do the following for 7.x I think - obviously needs discussion and up to David whether we do it or not:
- add requirement warning here (not error)
- use utf8mb4 collation if available on new installs.
- backport the ascii varchar patch
Since webchick has signed off, I've committed/pushed to 8.0.x.
Not small changes so they might not happen, but worth discussing at least.
fwiw #2301501: [policy, no patch] Explicitly decide and document that mininum requirements for major versions might get raised in minor versions was opened to ensure we had it documented somewhere that we can and will raise requirements with minor releases, so we can always raise again later if we need to, but I think minimum requirements should be the genuine minimum - drupal.org/requirements can always recommend slightly higher versions for security/performance.
Comment #58
yannickoo<3
Comment #59
Crell CreditAttribution: Crell at Palantir.net commented+1! (Subscribe)
Comment #60
Crell CreditAttribution: Crell at Palantir.net commentedI also added a change notice: https://www.drupal.org/node/2476661
Comment #61
stefan.r CreditAttribution: stefan.r commentedJust to follow up on this, in D8 some people (and hosters) seem to be having issues when using older versions of libmysqlclient -- we may want to clarify in the requirements that the version of libmysqlclient needs to be 5.5.3 as well (or 5.0.9 for mysqlnd)
We are working on better error handling for this in #2529188: Provide better error handling for MySQL client and server utf8mb4 incompatibility
Comment #62
eranmatis CreditAttribution: eranmatis as a volunteer commentedHi,
Well, I don't know if this is the place for this but here I go.
I'm trying to install Drupal 8 beta 14 On a MochaHost shared server.
Currently stuck. but maybe information about the process will help with this issue.
First Error: "Database configuration"
SQLSTATE[HY000] [2019] Can't initialize character set utf8mb4 (path: /usr/share/mysql/charsets/).
Solved with a patch: https://www.drupal.org/node/2529188
Second Error: "Database configuration"
The libmysqlclient driver version 5.5.42-cll is less than the minimum required version.
Upgrade to libmysqlclient version 5.5.3 or up, or alternatively switch mysql drivers to MySQLnd version 5.0.9 or up.
The host guys replay:
The libmysql version on the server is 5.5.42-cll like it is listed in the error message which is higher than 5.5.3.
This appears to the application installation bug.
Please check this with the support of the application.
The MySQL server version is 5.5.42
Comment #63
eranmatis CreditAttribution: eranmatis as a volunteer commentedFrom http://php.net/manual/en/function.version-compare.php I get the following:
Note:
Note that pre-release versions, such as 5.3.0-dev, are considered lower than their final release counterparts (like 5.3.0).
So on core/lib/Drupal/Core/Database/Driver/mysql/Install/Tasks.php
On checkEngineVersion(), the following line
version_compare($version, self::LIBMYSQLCLIENT_MINIMUM_VERSION, '<')
will return False for:
$version = 5.5.42-cll
self::LIBMYSQLCLIENT_MINIMUM_VERSION = 5.5.3
Comment #64
stefan.r CreditAttribution: stefan.r commented@eranmatis the error message is reporting the wrong libmysqlclient version.
Your host actually has a version that's actually lower than 5.5.42 - we are looking at this in #2558615: Error message in MySQL client version check shows server version instead of client version and need help in reviewing the patch. You can apply the patch to find out what their actual version is...
Comment #65
cilefen CreditAttribution: cilefen commented@stefan.r ++
Re #64: Everyone keep an eye on #2558615: Error message in MySQL client version check shows server version instead of client version . Until it is committed, the version error is just wrong.
Comment #66
cilefen CreditAttribution: cilefen commented#2558615: Error message in MySQL client version check shows server version instead of client version is fixed.
Comment #72
joseph.olstadFor D7, see child issue with initial backport of the changes made in D8:
#2881982: D7 Raise MySQL recommended version to 5.5.3
Comment #74
David_Rothstein CreditAttribution: David_Rothstein commentedPutting this back to the version it was actually fixed in.