Drupal and MySQL 8.0.11 – are we there yet ?

Now that MySQL 8.0 GA is out for almost 3 months, let’s see the status of how it’s integrated with Drupal, a very popular CMS using MySQL.

For people having already a Drupal site and that wants to upgrade to MySQL 8.0, please check this post.

Now if you want to use MySQL 8.0 with a fresh new Drupal 8, let’s have a look how does that work.

Drupal 8.5

Drupal 8.5.5 is the latest available stable release from July 4th 2018.

There is no notes about supporting MySQL 8.0. So let’s try it.

First I install MySQL 8.0.11 and unpack Drupal 8.5.5 in the apache’s datadir. Don’t forget to install php-mysqlnd.

Then I create the drupal8 database and setup an account:

mysql> create database drupal8;
mysql> create role drupal_site;
mysql> grant all on drupal8.* to drupal_site;
mysql> create user drupal_web identified by 'drUp4l';
mysql> grant drupal_site to drupal_web;
mysql> alter user drupal_web default role drupal_site;

As you can see, I’m using the new ROLES feature added in MySQL 8.

We can use our browser and point it to the Drupal url and follow the installation procedure until we arrive to the database form:

The first problem is related to the charset:

So by default, Drupal 8.5 is not compatible with MySQL 8.0 and it’s also written on this page:

Before we move forward and try on Drupal 8.6, let me show you how to configure MySQL to also work on Drupal 8.5.

In my.cnf, please add those lines in [mysqld] section:

character-set-server = utf8
default_authentication_plugin = mysql_native_password

And you can change the authentication plugin for the account used for Drupal:

mysql> ALTER USER drupal_web IDENTIFIED WITH 'mysql_native_password' BY 'drUp4l';

And reload the Drupal page….

… to have our next error 🙁

SQL_MODE doesn’t allow anymore 'NO_AUTO_CREATE_USER' (please check here for other similar changes)

To fix this, we need to modify some Drupal source code and remove it from the line 183 in core/lib/Drupal/Core/Database/Driver/mysql/Connection.php:

$connection_options['init_commands'] += [
-'sql_mode' => "SET sql_mode = 'ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,ONLY_FULL_GROUP_BY'",
+'sql_mode' => "SET sql_mode = 'ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,ONLY_FULL_GROUP_BY'",
];

You can then continue the install process and it works:

Drupal 8.6-dev

As Drupal website states that Drupal 8.6 should work with MySQL 8.0, let’s try it 😉

Also as the PDO Documentation states it (here), the version before 7.1.16 will return errors even with mysql_native_password authentication method, so I update PHP to 7.1.18.

But don’t forget that currently no version of mysqlnd supports caching_sha2_password and if your account uses that authentication method you will get such error:

The only PHP connector for MySQL supporting it is mysql_xdevapi.

So I change again the authentication method to mysql_native_password and reload the configuration wizard of Drupal:

Again this error ?! We already saw how to fix it. So even if Drupal 8.6 should support MySQL 8.0, all is not yet finished as it states here too: issue 2966523

So please check this issue for updates. Currently few tasks are remaining.

Then you can keep running the wizard:

Conclusion

Not everything is already net and smooth but some progress have been made. To have a full support of MySQL 8.0 out of the box, we need to wait for Drupal 5.6 and a version of mysqlnd supporting the new authentication method used in MySQL 8.0 by default.

Since then, some minor changes are required.

If you need help with your community tool or product to support MySQL 8.0, please don’t hesitate to reach me out, it will be a pleasure to help.