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:

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.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

18 Comments

  1. […] また、既存のMySQL 5.7からアップグレードすることもできます。その過程で、新しいMySQL Shell(mysqlsh)のUpgrade Checkerを使用してみたくなるかもしれません。このユーティリティは既存のMySQL 5.7サーバーを解析し、MySQL 8.0と非互換な点を教えてくれます。他のリソースとしては、Frederic Descampsのブログ記事、Migrating to MySQL 8.0 without breaking old applicationがあります。 […]

  2. So you end the article by just saying “I replaced a bunch of code!” and not telling anyone *how* you did it? Thanks for nothing…

    • Hey Andrew, you are welcome… Ah I also explained the changes, maybe you didn’t read well. Quote: “I searched in the code and I replaced all the calls to system table by `system` .. “

      • Thank you for the post Lefred! This was very helpful!

        If you ever create a post for the “Logical Upgrade” please update us here! 🙂

        Mahalo!

      • But how did you exactly search for the word system? When I search for the word system I get lines like this: “$result = db_query(“SELECT * FROM {system} WHERE type”
        so do you change that line to “$result = db_query(“SELECT * FROM {`system`} WHERE type”

        • Thank you for the idea lefred, it helped.

          Ronny, you could also replace {system} with `system` inside the /includes/database/database.inc:query() function, just add:

          “`
          // XXX: mysql 8.0 compatibility
          $query = str_replace(‘{system}’, ‘`system`’, $query);
          “`

          • If I add that to the public function query – it breaks the site !? That does not work

  3. Hi, I think your blog might be having browser compatibility issues.

    When I look at your blog site in Chrome, it looks fine but when opening in Internet Explorer, it
    has some overlapping. I just wanted to give you a quick heads up!
    Other then that, terrific blog!

  4. I am currently using MySQL 5.1 version (all my tables are innodb, most tables on latin and some on utf8).. if I want to upgrade to mysql 8.0, will it be an easy upgrade? or is it better I upgrade to mysql 5.7 first and then 8.0?

    is there any performance gains on 8.0? on my existing queries?

    • Hi Eric, it’s better to migrate to latest 5.7 first, but from 5.1 this can be journey depending if your datatypes like date and time and of course the size. About queries performance it all depends. Usually for a single threaded simple workload without any concurrency that doesn’t require any optimizer enhancements we made in the next releases, you might see a regression if you enable the full instrumentation. It’s all depends on your usage.

Leave a Reply to EduardCancel 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.

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.