Drupal and MySQL 8.0.11 – are we there yet ?

on

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.

 

2 thoughts on “Drupal and MySQL 8.0.11 – are we there yet ?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

recent

Last Tweets

  • RT : 昨年開催されたOracle MySQL Innovation Day 2018 秋の模様がEnterpriseZineのDB Onlineに掲載されました / MySQL 8.0の新機能から寿司ビール問題まで――Oracle MySQL Innovation Day 2018 秋 via ,
  • If you have a ticket for the pre-FOSDEM Day and you are not able to join, please release your ticket, there are people on the waiting list ! Thank you in advance ! ,
  • When your laptop dies just before 2 weeks of conferences... ,
  • RT : EVENTS:  MySQL Day, Denver, CO Forget Skiing. On Tuesday, January 29, 2019 Denver is all about NoSQL + SQL = MySQL. Join us to learn about NoSQL+SQL, plus how to improve your database performance, secure your data and prevent downtime. Register today! ,
  • RT : MySQL is ubiquitious for cloud usage. In this series I will explore Oracle Cloud's IaaS offerings that support MySQL. In this particular post I will highlight IaaS Enterprise capabilities that help secure our cloud perimiter. ,
Locations of visitors to this page
categories