Migrating to MySQL 8.0 without breaking old application

Recently I blogged about the new default authentication plugin in MySQL 8.0 and I got some comments complaining that this new authentication plugin is breaking half of applications.

So first of all, if you are using an old connector or a connector (like the one for Go) not yet supporting caching_sha2_passwordas authentication plugin, you are still able to use the old one. If you have created a new user for your application not supporting the new authentication method, you just have to run the following command (please use the right user account):

ALTER USER 'username'@'hostname' IDENTIFIED WITH 'mysql_native_password' BY 'password';

Let’s got back to the blog post now.

Situation

The exercise of this blog consists in the migration of the MySQL server 5.5.59 used by Druapl 6.2  to MySQL 8.0 without migrating to the latest Drupal version.

This is what we have now:

So far so good 😉

MySQL Upgrade

In the MySQL Manual, we propose 2 different strategies:

• Logical Upgrade
• In-Place Upgrade

The logical method consists of making a logical dump and I restore it, I won’t cover it here. The in-place method is as far as I know the most common one. However, there is something very important that people tend to forget: “Upgrade that skips versions is not supported. For example, upgrading directly from MySQL 5.6 to 8.0 is not supported.”

So know that this is clarified again, let’s continue with our plan.

As we are using 5.5.59, the latest 5.5 version, we don’t need to upgrade the binaries to the latest 5.5, if we would use a older version of 5.5, I would have recommended to upgrade first to the latest version of the same major version too. Our first step is then to put our site in maintenance and then upgrade to the latest 5.6.

MySQL 5.6

# yum update --enablerepo=mysql56-community --disablerepo=mysql57-community mysql-community-server
...
Updated:
  mysql-community-server.x86_64 0:5.6.39-2.el7                                                                                                         

Dependency Updated:
  mysql-community-client.x86_64 0:5.6.39-2.el7      
  mysql-community-common.x86_64 0:5.6.39-2.el7      
  mysql-community-libs.x86_64 0:5.6.39-2.el7     

Complete!

Perfect, let’s run the mandatory mysql_upgrade command:

Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck with default connection arguments
Running 'mysqlcheck with default connection arguments
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
...
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Running 'mysql_fix_privilege_tables'...
Running 'mysqlcheck with default connection arguments
Running 'mysqlcheck with default connection arguments
drupal.access                                      OK
drupal.actions                                     OK
drupal.actions_aid                                 OK
...
drupal.watchdog                                    OK
OK

We are good, let’s put back the site online and check the home page again:

MySQL 5.7

OK, let’s move on and upgrade to the latest 5.7:

# yum upgrade  mysql-community-server


Dependency Installed:
  mysql-community-libs-compat.x86_64 0:5.7.21-1.el7                                                                                                    

Updated:
  mysql-community-server.x86_64 0:5.7.21-1.el7                                                                                                         

Dependency Updated:
  mysql-community-client.x86_64 0:5.7.21-1.el7      
  mysql-community-common.x86_64 0:5.7.21-1.el7      
  mysql-community-libs.x86_64 0:5.7.21-1.el7     

Complete!

# mysql_upgrade 
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
...
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Upgrading the sys schema.
Checking databases.
drupal.access                                      OK
drupal.actions                                     OK
...
sys.sys_config                                     OK
Upgrade process completed successfully.
Checking if update is needed.

Once again, nothing wrong here, let’s check the website:

MySQL 8.0

It’s time now to upgrade to MySQL 8.0 !

Let’s perform like we did for the previous version:

# yum update --enablerepo=mysql80-community --disablerepo=mysql57-community mysql-community-server

Updated:
  mysql-community-server.x86_64 0:8.0.4-0.1.rc.el7                                                                                                     

Dependency Updated:
  mysql-community-client.x86_64 0:8.0.4-0.1.rc.el7  
  mysql-community-common.x86_64 0:8.0.4-0.1.rc.el7  
  mysql-community-libs.x86_64 0:8.0.4-0.1.rc.el7 

Complete!


[root@mysql1 drupal-6.2]# mysql_upgrade 
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Upgrading system table data.
Checking system database.
mysql.columns_priv                                 OK
mysql.component                                    OK
mysql.db                                           OK
mysql.default_roles                                OK
...
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Found outdated sys schema version 1.5.1.
Upgrading the sys schema.
Checking databases.
drupal.access                                      OK
drupal.actions                                     OK
...
drupal.watchdog                                    OK
sys.sys_config                                     OK
Upgrade process completed successfully.
Checking if update is needed.

Again, no problem here ! Let’s see the website:

OH! We have a problem it seems… Did my user’s authentication method changed and my old PHP connector doesn’t support it ?

Let’s verify:

mysql> select Host, User, plugin from mysql.user where User like 'drup%';
+------+---------+-----------------------+
| Host | User    | plugin                |
+------+---------+-----------------------+
| %    | drupal  | mysql_native_password |
+------+---------+-----------------------+
1 rows in set (0.00 sec)

So that’s not the problem. As I said before, users authentication method is not changed. So this new default doesn’t break old applications…. but my site is still not working…

What’s wrong then ?

In fact, this old Drupal, uses a table name that is now part of the reserved keywords. It’s always advised to verify what are the new keywords reserved for MySQL itself. New features can also mean new keywords sometimes.

I searched in the code and I replaced all the calls to system table by `system` and now the result:

Conclusion

If you are using an old application, no the new authentication plugin doesn’t break your application, until you don’t create a new user for it and not specify an authentication method compatible with your connector. But of course other things, like reserved keywords in this case, can be problematic. This is why an major release upgrade always need to be tested in advance. Not only for schema and syntax compatibility but also for performance as the query execution plan might not be the one you expect event if in most cases the MySQL Optimizer becomes smarter and smarter with the releases and has the support of new features like the histograms.

And don’t forget that the new MySQL Shell includes a new utility checking your current environment to identify possible issues like the one covered in this article.