Practical experience upgrading MySQL 5.7 to version 8.0

We recently upgraded our servers from MySQL 5.7 to 8.0.



Let us leave outside the scope of this article why and what new buns appeared in MySQL 8.0, but instead we will talk about what difficulties we encountered during the upgrade process.



Firstly, before updating it is worth looking at the list of changes and correcting your config file.



At a minimum, the following options have been deleted:

innodb_file_format, innodb_file_format_check, innodb_file_format_max, innodb_large_prefix

query_cache_limit, query_cache_min_res_unit, query_cache_size, query_cache_type, query_cache_wlock_invalidate.

In the sql_mode parameter, in particular, NO_AUTO_CREATE_USER was deleted - which is especially important, because in MySQL 5.7, it was turned on by default.



Percona has instructions for in-place upgrade. And in the general case, you can follow it, however, we managed to update only one cluster in this way; for the rest, an attempt to update this failed with a similar error:

2019-06-22T05:04:18.510888Z 1 [ERROR] [MY-011014] [Server] Found partially upgraded DD. Aborting upgrade and deleting all DD tables. Start the upgrade process again. 2019-06-22T05:04:23.115018Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed. 2019-06-22T05:04:23.115655Z 0 [ERROR] [MY-010119] [Server] Aborting
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Therefore, we updated the remaining clusters by raising a new empty instance and restoring the database dump from the previous version.



To do this, firstly, you need a database dump. And here the danger # 1 awaits - a dump made in the standard way:

 mysqldump -u root -p --hex-blob --default-character-set=utf8mb4 --all-databases --triggers --routines --events > dump.sql
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

it does not recover giving an error:

 ERROR 3554 (HY000) at line 15915: Access to system table 'mysql.innodb_index_stats' is rejected.
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

The description is in the MySQL bugtracker (with the status Not a bug :), there is also advice on how to dump, so that you can restore it:

 mysqldump -u root -p --hex-blob --default-character-set=utf8mb4 --all-databases --triggers --routines --events --ignore-table=mysql.innodb_index_stats --ignore-table=mysql.innodb_table_stats > dump.sql
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

But when trying to use such a dump, if triggers were present in it (and we had them), danger # 2 could be expected, in the form of such an error:

 ERROR 1231 (42000) at line 54: Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

The reason for this is how MySQL uses sql_mode for triggers, namely: MySQL saves the sql_mode value for a trigger at the time it is created and then always executes it with that value. And accordingly saves this value in a dump.



Help description for this:

dev.mysql.com/doc/refman/8.0/en/create-trigger.html

MySQL stores the sql_mode system variable setting in effect when a trigger is created, and always executes the trigger body with this setting in force, regardless of the current server SQL mode when the trigger begins executing.

What do we do? We just sed cut NO_AUTO_CREATE_USER from the finished dump. Similar command:

 sed "s/50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER/50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO/g" dump.sql > dump2.sql
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

After that, the dump is successfully restored, but danger # 3 awaits us (the quite expected truth) - the system tables are restored to the state from version 5.7 and we have the following errors in the logs:

 [ERROR] [MY-013143] [Server] Column count of mysql.user is wrong. Expected 51, found 45. The table is probably corrupted
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

From experience with previous versions, this is treated by running mysql_upgrade - but since version 8.0.16 - this does not work, because mysql_upgrade is declared deprecated and does nothing.



Now, to cause the system tables to be updated, you must start MySQL with the option upgrade = FORCE .



On fresh ubuntu, this can be done as follows:

 systemctl set-environment MYSQLD_OPTS="--upgrade=FORCE"
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Then restart MySQL. Well, after a successful update, delete it:

 systemctl unset-environment MYSQLD_OPTS