Provide better error handling for MySQL client and server utf8mb4 incompatibility

We already check for this elsewhere in the installer, so there is a case where that check passes but the new one you added doesn't. Having looked for the error on Google it points to configuration issues/version mismatches with client libraries, even if the charset is compiled in.

So ideally we'd need to find out under what circumstance the existing check passes but your new check doesn't, and provide instructions for resolution in the error message.

But #3 you're checking after connecting to the database - the problem is you can't check using database functions before you've connected to the DB. The charset is hardcoded on the connection.

I've seen this at work on @reevo's computer the fix looks good and make sense. It'd be nice to have some before and after screen shots as testing this using simpletest is impossible.

+++ b/core/lib/Drupal/Core/Database/Driver/mysql/Install/Tasks.php
@@ -66,6 +66,12 @@ protected function connect() {
+        $this->fail(t('Your database server must support utf8mb4 character encoding to work with Drupal. Make sure to use a database server that supports utf8mb4 character encoding, such as MySQL/MariaDB/Percona versions 5.5.3 and up, and is compiled with the utf8mb4 character set.'));

@reevo made a good point that we probably should point to MySQL's docs here to be more helpful.

Ah OK. So neither of the checks would pass.

Currently we check by doing "SET NAMES utf8mb4" but I don't know that that's necessary considering we already pass along the charset to the dsn.

Re #7 - yep - it's worth thinking about whether that test is necessary.

Maybe we can test for that in some older MySQL installs...

As mentioned in the parent, this may possibly be a client issue as well. So if we find that updating client libraries fixes the issue it could be good to hint at that in the error message. Possibly we can find a way to check for both server support and client support and provide different messages for each.

Nice, @reevo has provided screenshot in #9. Let's add some tests.

@vijaycs85 that's not possible. Since we'd need an alternate version of mysql just for this specific test.

Maybe some manual testing would be good, as well as finding out why some people seem to be getting utf8mb4 errors even on newer mysql server installs, so we can offer a more helpful error message.

@stefan.r the manual testing is represented by the screenshots and my comment in #5

I meant with older versions.. but I guess older MySQL versions will fail with the current check in any case (as only PHP 5.3.5 and older ignore the charset DSN parameter), so the SET NAMES check could be removed.

Also, per #1314214-309: MySQL driver does not support full UTF-8 (emojis, asian symbols, mathematical symbols) we probably should be checking both server and client support.

I'm thinking version numbers may be the safest bet (in addition to feature tests, in the edge case of utf8mb4 not being compiled in), so we'd want the mysql server version to be over 5.5.3 and mysql_get_client_info() to be over 5.5.3 (libmysql) or over 5.0.9 (mysqlnd)

as well as finding out why some people seem to be getting utf8mb4 errors even on newer mysql server installs, so we can offer a more helpful error message

@stefan.r one instance is we are using vagrant box built by puppet build(with standard mysql package) and the version is 5.5.35

$ mysql --version
mysql  Ver 14.14 Distrib 5.5.35, for Linux (x86_64) using readline 5.1

just spoke with @stefan.r on IRC and found that the client I'm using isn't 5.5 We might need to include that in the patch?

Just to clarify, @vijaycs85's CLI client was 5.5 allright but apparently his libmysqlclient version was 5.1.70

This patch adds a mysql driver version check and removes the SET NAMES client check (as we already check for that through the DSN string).

I think we already do a server version check in Tasks::checkEngineVersion(), can we find a way to make that work while ignoring the DSN error? (or maybe connect without the DSN charset string just for purposes of the server version check)

In terms of error messages, the error message in the previous patch should probably only show if we comply with the client & server versions. The first recommendation should be to upgrade those, and only if the versions are OK and the feature is still missing should we show more complicated error messages.

Also, that error message ("Your database server must support utf8mb4 character encoding to work with Drupal." + link to mysql documentation) still needs a bit of work, it merely refers to the "database server", when a "Can't initialize character set" error can also be due to the PHP mysql driver.

Merging both patches

I am still getting the 'Your database server must support utf8mb4 character'.. exception after applying #25 because the tasks run after connection and this exception is part of connection. I don't see any other way around to get specific client/server version issue unless we connect with utf8 first and do checks before we try to connect with utf8mb4?

@vijaycs85 we could try that...

Ideally we'd want to not display the dsn check if the versions are wrong so maybe we can put the connection code in a try catch block and fallback to utf8 during the installer checks?

Can anyone clarify why I am getting this error on a 5.6.1 install of shared/client/server?
Trying to install beta12 to an empty db.

just to be sure the version is not the problem, what does echo mysql_get_client_info() say?

Hi Stefan, I definitely had all mysql components upgraded to 5.6. Unfortunately I cannot doublecheck right now, as I had to downgrade the server for different reasons. I will be setting up a local environment soon and will be keeping an eye on this issue. Kind regards.

I'm now on test mysql server ver. 5.5.44 and the client version seems to be 5.1.69. (Is this normal? It loaded libmysqlclient16-5.1.69 as dependency.) Beta11 installs, beta12 gives the above error.

@gbyte.co it's the same problem we are trying to provide spacial message at #27

Should this also be backported to D7?

@vijaycs85 Why is this error happening at all on a 5.5.44 mysql server? Is it because of the mysql client version?

@gbyte.co yeah, its the client version. I don't have the full story, but one of my team mate tried to fix this issue in a vagrant box and found that it's not straight forward update (as most of the install methods use pre-build mysql).

@DamienMcKenna, as I mentioned above it would be nice to analyse the impact before port this to D7. As you can see in the parent issue, it's an assumption that the change wouldn't cause any major problem if you have mysql 5.5.3 which is not the case because of client version.

I'll see if I can get the exact problem (of updating client) and how we tried to fix it here.

OK so just to summarize, so far for anyone having this issue the problem seemed to be the client version, which the current patch now checks for.

I don't see a problem with keeping the DSN check in case the versions are OK, but we do want to run the client & server version checks first, so as suggested in #27 we'd probably want o fall back to utf8 just to do the version checks.

@reevo maybe we can hint at that in the error message and suggest either using a later version of libmysqlclient or switching to mysqlnd.

Draft of a patch attached

Does anyone have a setup with an outdated libmysqlclient to further work on this patch? :)

clarifying this is about both client and server

I've not read the full issue yet, but:

1. +++ b/core/lib/Drupal/Core/Database/Connection.php
   index d48c6f2..065fa76 100644
   --- a/core/lib/Drupal/Core/Database/Database.php
   
   --- a/core/lib/Drupal/Core/Database/Database.php
   +++ b/core/lib/Drupal/Core/Database/Database.php
   
   +++ b/core/lib/Drupal/Core/Database/Database.php
   +++ b/core/lib/Drupal/Core/Database/Database.php
   @@ -40,6 +40,15 @@
   
   @@ -40,6 +40,15 @@
      const RETURN_INSERT_ID = 3;
    
      /**
   +   * Flag to indicate whether the MySQL DSN charset should fall back to utf8.
   +   *
   +   * Used in installer version checks.
   +   *
   +   * @var boolean
   +   */
   

   This code does not belong in Database. It's MySQL-specific, so it belongs in the MySQL namespace (connection or installer, as appropriate).

2. +++ b/core/lib/Drupal/Core/Database/Database.php
   @@ -40,6 +40,15 @@
   +  static protected $mysql_dsn_utf8_fallback = FALSE;
   

   Object properties should be lowerCamelCased.

1. +++ b/core/lib/Drupal/Core/Database/Driver/mysql/Install/Tasks.php
   @@ -62,7 +66,22 @@ protected function connect() {
   +          Connection::$dsnUtf8Fallback = TRUE;
   +          Database::getConnection();
   +          Connection::$dsnUtf8Fallback = FALSE;
   

   This code right here shows what's wrong with the static. :-) If we want to set the connection to use the fallback for just this one open connection... get the connection and call a method on it to set it. It will be persisted (because Database already does so), so the next request will get the same object anyway, which this always relies on.

   To wit:

   Database::getConnection()->setCharset('utf8');

   And then instead of a static fallback that we have to if-check a bunch, we just set the charset on the object and we're done. No more if-checks since they can always just access $charset internally.

2. +++ b/core/lib/Drupal/Core/Database/Install/Tasks.php
   @@ -148,14 +148,14 @@ public function runTasks() {
   +    $messages = array();
   

   Short array syntax, please.

@Crell I don't know that Database::getConnection()->setCharset('utf8'); will work? In order to open a connection during getConnection() we are setting the charset in the PDO DSN string, so getConnection() would throw an exception before it can even return a connection object to run setCharset() on.

Just to illustrate, getConnection() calls openConnection(), where the following happens:

    $pdo_connection = $driver_class::open(self::$databaseInfo[$key][$target]);
    $new_connection = new $driver_class($pdo_connection, self::$databaseInfo[$key][$target]);

So that first line fails before we can call setCharset() on the $new_connection object. Am I missing anythng here?

... Carp. You're right, that wouldn't work. In that case, let's at least switch it from a public property to a static method and protected static property. Then clean up point #46.2. That's probably as much as we can do then.

Thanks, stefan.r!

Thanks for the review Crell.

One thing that doesn't work here yet is that the HTML is being sanitized in the bullet point list (the $message variable is fine, it seems just to be happening in throwing the TaskException), but I have seen this problem elsewhere in Drupal as well so there may already be an issue for this?

Just wanted to double check wording of the error messages as well. If only the driver version is wrong, the list of messages is as follows:

• Your mysql server and PHP mysql driver must support utf8mb4 character encoding to work with Drupal. Make sure to use a database system that supports utf8mb4 character encoding, such as MySQL/MariaDB/Percona versions 5.5.3 and up, and that the utf8mb4 character set is compiled in. See the t-initialize-character-set.html" target="_blank">MySQL documentation for more information
• The libmysqlclient driver version 5.1.13 is less than the minimum required version 5.5.3.

The first message here seems a bit redundant, and maybe we can hint at switching drivers to mysqlnd as well (which is more likely to be up-to-date considering our minimum php version requirement) some users have reported having 5.5.3 server/CLI packages but no up-to-date libmysqlclient package and fixed their issue by switching drivers.

this ought to address those points

retesting as the test failure seemed unrelated: copy(/var/lib/drupaltestbot/sites/default/files/checkout/sites/simpletest/202275/settings.php): failed to open stream: No such file or directorycopy('/var/lib/drupaltestbot/sites/default/files/checkout/sites/default/default.settings.php', '/var/lib/drupaltestbot/sites/default/files/checkout/sites/simpletest/202275/settings.php')

1. +++ b/core/lib/Drupal/Core/Database/Driver/mysql/Install/Tasks.php
   @@ -152,13 +152,13 @@
   -        $this->fail(t("The libmysqlclient driver version %version is less than the minimum required version %minimum_version.", array('%version' => Database::getConnection()->version(), '%minimum_version' => self::LIBMYSQLCLIENT_MINIMUM_VERSION)));
   +        $this->fail(t("The libmysqlclient driver version %version less than the minimum required version. Upgrade to libmysqlclient version %libmysqlclient_minimum_version or up, or alternatively switch mysql drivers to MySQLnd version %mysqlnd_minimum_version or up.", array('%version' => Database::getConnection()->version(), '%libmysqlclient_minimum_version' => self::LIBMYSQLCLIENT_MINIMUM_VERSION, '%mysqlnd_minimum_version' => self::MYSQLND_MINIMUM_VERSION)));
   

   The word "is" went missing from the first sentence. :-)

2. +++ b/core/lib/Drupal/Core/Database/Install/Tasks.php
   @@ -151,11 +151,12 @@
          if (!$success) {
   -        $messages[] = SafeMarkup::isSafe($result) ? $result : '<li>' . SafeMarkup::checkPlain($result) . '</li>';
   +        $error = SafeMarkup::isSafe($result) ? $result : SafeMarkup::checkPlain($result);
   +        $messages[] = '<li>' . $error . '</li>';
          }
   

   I really don't know what the "correct" approach here is at the moment so I'll defer to someone who knows the installer. (laugh!)

This patch puts the word "is" back in. As to point number 2, the problem there was that the closing parenthesis in SafeMarkup::set() was misplaced. Having tested this and looked at the related code it ought to show the message in the right way (and still sanitize where needed), unless we have a "more correct" way of making a bullet-point list?

1. +++ b/core/lib/Drupal/Core/Database/Driver/mysql/Connection.php
   @@ -93,7 +112,13 @@ public static function open(array &$connection_options = array()) {
   -    $dsn .= ';charset=utf8mb4';
   +    if (static::$dsnUtf8Fallback === TRUE) {
   +      // Only used during the installer version check, as a fallback from utf8mb4.
   +      $dsn .= ';charset=' . $charset;
   +    }
   +    else {
   +      $dsn .= ';charset=' . $charset;
   +    }
   

   These look... the same? Guessing due to later changes.

2. +++ b/core/lib/Drupal/Core/Database/Driver/mysql/Install/Tasks.php
   @@ -62,7 +66,22 @@ protected function connect() {
   +          $this->fail(t('Your mysql server and PHP mysql driver must support utf8mb4 character encoding to work with Drupal. Make sure to use a database system that supports utf8mb4 character encoding, such as MySQL/MariaDB/Percona versions 5.5.3 and up, and that the utf8mb4 character set is compiled in. See the <a href="@documentation" target="_blank">MySQL documentation</a> for more information.', array('@documentation' => 'https://dev.mysql.com/doc/refman/5.0/en/cannot-initialize-character-set.html')));
   

   MySQL?
   Also we don't usually say "Drupal" in error messages, do we? This is a LONGGGGG error message.

3. +++ b/core/lib/Drupal/Core/Database/Install/Tasks.php
   @@ -148,14 +148,15 @@ public function runTasks() {
   +        $messages[] = '<li>' . $error . '</li>';
   ...
   +      $message = SafeMarkup::set('Resolve all issues below to continue the installation. For help configuring your database server, see the <a href="https://www.drupal.org/getting-started/install">installation handbook</a>, or contact your hosting provider.<ul>' . implode($messages) . '</ul>');
   

   Not sure if this is right either...

   Ooh that

   is ugly...
   Going to say that this is Wrong™.

This should address those 3 points and includes an updated screenshot in the issue summary.

As to the error message display, seems it's being worked on in #2501835: Remove SafeMarkup::set in Drupal\Core\Database\Install\Tasks::runTasks() and ensure multiple messages are printed as well

+++ b/core/lib/Drupal/Core/Database/Install/Tasks.php
@@ -148,15 +148,15 @@ public function runTasks() {
     }
     // Check for failed results and compile message
-    $message = '';
+    $messages = [];
     foreach ($this->results as $result => $success) {
       if (!$success) {
-        $message = SafeMarkup::isSafe($result) ? $result : SafeMarkup::checkPlain($result);
+        $messages[] = SafeMarkup::isSafe($result) ? $result : SafeMarkup::checkPlain($result);
       }
     }
-    if (!empty($message)) {
-      $message = SafeMarkup::set('Resolve all issues below to continue the installation. For help configuring your database server, see the <a href="https://www.drupal.org/getting-started/install">installation handbook</a>, or contact your hosting provider.' . $message);
-      throw new TaskException($message);
+    if (!empty($messages)) {
+      $messages_list = '<ul><li>' . implode('</li><li>', $messages) . '</li></ul>';
+      throw new TaskException(SafeMarkup::set(t('Resolve all issues below to continue the installation. For help configuring your database server, see the <a href="https://www.drupal.org/getting-started/install">installation handbook</a>, or contact your hosting provider. !messages_list', ['!messages_list' => $messages_list])));
     }
   }

Can you leave this to #2501835: Remove SafeMarkup::set in Drupal\Core\Database\Install\Tasks::runTasks() and ensure multiple messages are printed or is this patch held up by that fix?

Yes we can address this in that other issue, I wasn't aware it was already being addressed there so I'll remove that code. Even if we only show the last error message the current patch is still an improvement and as soon as #2501835: Remove SafeMarkup::set in Drupal\Core\Database\Install\Tasks::runTasks() and ensure multiple messages are printed goes in it'll show the full bullet point list like in the issue summary.

This removes it altogether as it can be addressed in that separate issue.

interdiff in that previous comment was wrong

Interdiff with #49 which was RTBC, this should be a very easy review :)

The third part has been reverted to what it originally said as this will be addressed in #2501835: Remove SafeMarkup::set in Drupal\Core\Database\Install\Tasks::runTasks() and ensure multiple messages are printed.

Thanks @stefan.r re-RTBC as per #49

+++ b/core/lib/Drupal/Core/Database/Driver/mysql/Connection.php
@@ -82,6 +94,13 @@ public function query($query, array $args = array(), $options = array()) {
+      // Only used during the installer version check, as a fallback from utf8mb4.

nit: 80 chars. Can be fixed on commit.

+++ b/core/lib/Drupal/Core/Database/Driver/mysql/Connection.php
@@ -153,6 +172,18 @@ public static function open(array &$connection_options = array()) {
   /**
+   * Sets the flag that controls fallback of the MySQL DSN charset to utf8.
+   *
+   * This should be set to TRUE in the installer version checks only.
+   *
+   * @param bool $boolean
+   *   Value to set the flag to.
+   */
+  public static function setDsnUtf8Fallback($boolean) {
+    static::$dsnUtf8Fallback = $boolean;
+  }

I'm not sure about exposing this as public API on the Mysql connection. Have we considered passing this utf8_fallback flag on the connection options?

Yes I felt queasy about that too, we don't want people to use this outside of the installer checks...

So we temporarily overwrite the $databaseInfo property which has the settings.php info with the utf8_fallback toggle, and make sure it's not settable from settings.php?

@stefan.r nice that all the awkwardness is now in the installer where it needs to be. The solution looks good to me.

+++ b/core/lib/Drupal/Core/Database/Driver/mysql/Install/Tasks.php
@@ -62,7 +66,33 @@ protected function connect() {
+          // Connect with the new database info.
+          Database::getConnection();

I think we're missing why we're doing all of this work here. Which (I think) is to connect to the database so we can run the checkEngineVersion test.

@alexpott you mean just to add an explanatory comment?

@stefan.r exactly #73 looks good.

Oh, I like this a lot better! Good catch, alexpott. (I always feel odd saying that when Nat isn't active in the issue...)

Only caveat: Nothing else in the DB API uses a # prefixed array key. That's a Render API-ism. Let's not introduce it here. If we really want to mark it as "weird" we could use a _ prefix for private, but I think even that is excessive here, honestly.

This converts it into an underscore so we can check for "internal" settings while loading from settings.php without hardcoding them, throwing a InvalidDatabaseSettingException in case any setting loaded directly from settings.php starts with an underscore.

I'm not a huge fan of the additional validation that has to run on every request that connects to the db - it looks overkill. I think the correct think here is the underscore and documentation.

FYI for the Twitter module we've solved the problem by converting the {twitter}.text column, which stores each tweet, to a 'blob' field: #1910376: SQL error when importing tweet with emoji

Yes, no big deal if we remove it, in this case it's actually fine even if people were to try to hack a utf8 fallback into their settings file because everything would still break on table create.

@DamienMcKenna: wrong issue? :) Storing as binary circumvents the character set problem but those tweets are not really binary data either. In any case, in D8 this will work as regular varchar...

@stefan.r: Yeah, I got this one mixed up with #1314214: MySQL driver does not support full UTF-8 (emojis, asian symbols, mathematical symbols), sorry.

RTBC for #81. Thanks, stefan.r!

This is an important fix to help people give the right message to their hosting providers. Nice work. Committed 70dfc3b and pushed to 8.0.x. Thanks!

thanks @stefan.r for pointing me tho this issue. I applied the patch to a beta14 installation and run the installation on a DomainFactory ManagedHosting.

It detects the issue but the result of version_compare is wrong: 5.6.19-67.0-log < 5.5.3 seems not correct. Probably due to the "log" at the end?

Resolve all issues below to continue the installation. For help configuring your database server, see the installation handbook, or contact your hosting provider.The libmysqlclient driver version 5.6.19-67.0-log 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.

Ah, nice find! I'll open a followup!

#87: could you apply the patch in #2558615: Error message in MySQL client version check shows server version instead of client version and post a comment if it works for you?

Resolve all issues below to continue the installation. For help configuring your database server, see the installation handbook, or contact your hosting provider.The libmysqlclient driver version 5.1.61 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.

Guess it works!

I get below error when trying to setup database :

Your MySQL server and PHP MySQL driver must support utf8mb4 character encoding. Make sure to use a database system that supports this (such as MySQL/MariaDB/Percona 5.5.3 and up), and that the utf8mb4 character set is compiled in. See the MySQL documentation for more information.

Software details:
Drupal: 8.0.0-beta
Os: RHEL 6
Linux 2.6.32-504.23.4.el6.x86_64 #1 SMP Fri May 29 10:16:43 EDT 2015 x86_64 x86_64 x86_64 GNU/Linux

Mysql: 5.6.26
mysql Ver 14.14 Distrib 5.6.26, for Linux (x86_64) using EditLine wrapper

# rpm -qa | grep -i mysql
MySQL-client-5.6.26-1.el6.x86_64
libmysqlclient16-5.1.69-1.w6.x86_64
php56w-mysql-5.6.12-1.w6.x86_64
MySQL-server-5.6.26-1.el6.x86_64

/etc/my.cnf file:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
character-set-client-handshake = FALSE
character-set-server = utf8mb4
#character-set-client = utf8mb4
collation-server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

@ruchikasb please don't rename and reopen old issues that have already been closed, it is confusing as other issues link to this. Find an existing open issue instead or file a new issue.

Your problem is this: libmysqlclient16-5.1.69-1.w6.x86_64

5.1 is not supported, you'll need to use a later version of libmysqlclient or use the mysqlnd driver that comes with php.

Sorry about that.

You are correct. I updated the php-mysqlnd driver and it works now.

Point to note: php56w-mysqlnd-5.6.10 does not work but php56w-mysqlnd-5.6.12 works.