Recentl
y 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 oldone. 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:
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.
via Planet MySQL
Migrating to MySQL 8.0 without breaking old application