MySQL 8.0 upgrade checklist

Recently one of our customers in Fintech. business (among the largest one in the Asia) wanted to upgrade from MySQL 5.7 to MySQL. 8.0. and they approached us for a safest and durable MySQL upgrade strategy, roadmap and execution. In Fintech. business every transaction needs to durable from statutory regulatory compliance perspective and we at MinervaDB never wanted to go for unplanned / easy in-place MySQL 8.0 upgrade method here without proper pre-migration audit, We wanted to list down in detail what are the possible scenarios this MySQL 8.0 upgrade will fail and the compatibility issues between MySQL 5.7 and MySQL 8.0. Thankfully Upgrade Checker utility that comes with MySQL Shell 8.0 can be executed against MySQL 5.7 server to confirm upgrade readiness, We have written a blog on MySQL Shell 8.0 Upgrade Checker here   

Things to remember before MySQL. 8.0 upgrade:

• Metadata on Transactional Data Dictionary tables – MySQL 8.0 manages metadata in transactional data dictionary tables. In the previous MySQL releases, the metadata was stored in non-transactional system tables
  • With the introduction of the –innodb-directoriesfeature, the location of file-per-table and general tablespace files created with an absolute path or in a location outside of the data directory should be added to the innodb_directoriesargument value. Otherwise, InnoDB is not able to locate these files during recovery. To view tablespace file locations, query the INFORMATION_SCHEMA.FILEStable:

    SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES \G

• Caching_sha2_password– Starting from MySQL 8.0 the default value for the system variable –default-authentication-plugin is “caching_sha2_password” . But for existing accounts during upgrades to MySQL 8.0. the authentication plugin will remain unchanged, this is also application for the administrative account ‘root’@’localhost’ . To connect to the server following data directory initialization, you must therefore use a client or connector that supports caching_sha2_password. If you can do this but prefer that the root account use mysql_native_password after installation, install MySQL and initialize the data directory as you normally would. Then connect to the server as root and use ALTER USERas follows to change the account authentication plugin and password:

  ALTER USER 'root'@'localhost'
  IDENTIFIED WITH mysql_native_password
  BY 'NEW_PASSWORD';

• If ever the client or the connector the application use does not support caching_sha2_password , Please modify my.cnf system variable –default-authentication-plugin = mysql_native_password 
• Use mysqlcheck. to detect non-compatible datatypes, functions, orphaned .frm files – MySQL 8.0 in-place upgrade is not supported if the tables contain old temporal columns in pre-5.6.4 format (TIME, DATETIME and TIMESTAMP columns without support for fractional seconds precision )

  mysqlcheck -u root -p --all-databases --check-upgrade

• There must be no tables that have foreign key constraint names longer than 64 characters. To identify tables with too-long constraint names, execute this query:

  SELECT TABLE_SCHEMA, TABLE_NAME
  FROM INFORMATION_SCHEMA.TABLES
  WHERE TABLE_NAME IN
    (SELECT LEFT(SUBSTR(ID,INSTR(ID,'/')+1),
                 INSTR(SUBSTR(ID,INSTR(ID,'/')+1),'_ibfk_')-1)
     FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN
     WHERE LENGTH(SUBSTR(ID,INSTR(ID,'/')+1))>64);

• To avoid a startup failure on MySQL 8.0, remove any instance of NO_AUTO_CREATE_USER from sql_modesystem variable settings in MySQL option files.
• Validate the execution plan of your optimizer hints after upgrade from MySQL 5.7 to 8.0 , Some optimizer hint may even be counterproductive
• There must be no queries and stored program definitions from MySQL 8.0.12 or lower that use ASCor DESCqualifiers for GROUP BYclauses. Otherwise, upgrading to MySQL 8.0.13 or higher may fail, as may replicating to MySQL 8.0.13 or higher slave servers.
• There must be no table partitions that reside in shared InnoDB tablespaces, which include the system tablespace and general tablespaces. Identify table partitions in shared tablespaces by querying INFORMATION_SCHEMA:

  SELECT DISTINCT NAME, SPACE, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES 
    WHERE NAME LIKE '%#P#%' AND SPACE_TYPE NOT LIKE 'Single';

• To move table partitions from shared tablespaces to file-per-table tablespaces, You can run  ALTER TABLE … REORGANIZE PARTITION query:

  ALTER TABLE table_name REORGANIZE PARTITION partition_name 
    INTO (partition_definition TABLESPACE=innodb_file_per_table);

• Confirm your MySQL 5.7 is not configured (my.cnf) with deprecated or removed system variable. If any, Your MySQL 8.0 upgrade will fail. The list of removed, deprecated and new system / status variables are available here –https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html  
• If you are doing in-place MySQL 8.0. upgrade, either commit or rollback the XA transactions by issuing XA COMMIT or XA ROLLBACK statement

How to upgrade to MySQL 8.0 ?

There are basically two ways to upgrade to MySQL 8.0:

1. In-Place Upgrade – In this upgrade methodology you will be shutting down the old MySQL Server and replacing the old MySQL binaries or other related packages with new ones. Once successfully completed
2. Logical Upgrade – In this upgrade methodology you will be exporting SQL from the older MySQL instance using logical backup utilities like mysqldump or mysqlpump  and importing to MySQL 8.0

We at MinervaDB are technically ok with both methods of MySQL upgrades so far you have done thorough due diligence of MySQL 5.7 to MySQL 8.0 compatibilities and conflicts.

mysql_upgrade is going way with MySQL 8.0.16

mysql_upgrade binary is deprecated with MySQL 8.0.16, Going forward it will be functionally known as “server upgrade”. This is added alongside the Data Dictionary upgrade (DD Upgrade), which is a process to update the data dictionary table definitions. From MySQL 8.0.16 mysqld binary takes care of entire upgrade procedure if needed.

Manual mysql_upgrade process is still possible !

You don’t want automatic upgrades from “server upgrade” ? It is possible by configuring the MySQL 8.0 system variable –upgrade 

Recommended values:

• AUTO –  MySQL 8.0 performs automatic upgrade from the older release, MySQL 8.0 default value for server option –upgrade is AUTO.
• MINIMAL – MySQL 8.0 performs the upgrade of the data dictionary, the Performance Schema and INFORMATION_SCHEMA. When the server option –upgrade is configured MINIMAL, Group Replication cannot be started, because system tables on which the replication internals depend are not updated. 
• FORCE – When server option –upgrade is set to FORCE , The server upgrades the he data dictionary, the Performance Schema, the INFORMATION_SCHEMA, the system tables in. the mysql schema, the sys schema and other user schemas. 
• NONE – The server performs no automatic upgrades when configured the server option –upgrade to NONE, This option prevents data dictionary upgrade and server exits with an error if the data dictionary is found out of date. 

Troubleshooting MySQL 8.0 upgrade – What usually can go wrong with MySQL 8.0. upgrade ?

• Conflicting with my.cnf of previous MySQL release / installation –If the new mysqld of MySQL 8.0 does not start, Please verify that you do not have an old my.cnf file from your previous installation.  You can check this with the –print-defaults option (for example, mysqld –print-defaults). If this command displays anything other than the program name, you have an active my.cnf file that affects server or client operation.
• Commands out of sync / unexpected core dumps – After MySQL 8.0 upgrade, you experience problems with compiled client programs, such as Commands out of sync or unexpected core dumps, you probably have used old header or library files when compiling your programs. In this case, check the date for your mysql.h file and libmysqlclient.a library to verify that they are from the new MySQL distribution. If not, recompile your programs with the new headers and libraries. Recompilation might also be necessary for programs compiled against the shared client library if the library major version number has changed
• Schema mismatch errors – A schema mismatch in a MySQL 5.7 instance between the .frm file of a table and the InnoDB data dictionary can cause an upgrade to MySQL 8.0 to fail. Such mismatches may be due to .frm file corruption. To address this issue, dump and restore affected tables before attempting the upgrade again.
• User-defined function (UDF) conflicts due to same name – If ever you have created a user-defined function (UDF) / stored functions in MySQL previous releases (eg. MySQL 5.7) with same name of MySQL 8.0 built-in function, the UDF becomes inaccessible.

Downgrading from MySQL 8.0

Downgrade from MySQL 8.0 to MySQL 5.7, or from a MySQL 8.0 release to a previous MySQL 8.0 release, is not supported. The only supported alternative is to restore a backup taken before upgrading. It is therefore imperative that you backup your data before starting the upgrade process.

Conclusion

MySQL 8.0 upgrades are not complex. But, If not carefully planned, there are high chances you will end-up with an unsuccessful upgrade. We strongly recommend to do detailed low-level MySQL 8.0 compatibility assessments before planning for an upgrade, Thanks for your comments.