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.
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:
In the MySQL Manual, we propose 2 different strategies:
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.
# 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
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:
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:
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:
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:
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.