Upgrading to MySQL 8.0? Here is what you need to know…

In my previous blog post, I had described the steps to perform an in-place upgrade for upgrading from MySQL 5.7 to MySQL 8.0. In this blog post, I will discuss about the considerations that needs to be taken into account while upgrading to MySQL 8.0

The general in-place upgrade requirement is that MySQL 8.0 will understand the disk image produced by MySQL 5.7.  This is generally true, for example MySQL 8.0 is able to read the MySQL 5.7 FRM files and create the new data dictionary based on the MySQL 5.7 image. In most cases, the upgrade from MySQL 5.7 to MySQL 8.0 is seamless.

However, there are a few things we decided to discontinue in MySQL 8.0 (explained in-depth in the next section). When the MySQL 8.0 binary is started on the MySQL 5.7 image, it will check for these incompatibilities and terminate the upgrade process if one or more of these incompatibilities are found in the MySQL 5.7 image. Note that the MySQL 8.0 binary will terminate without having done any changes to the MySQL 5.7 image, so the user can restart MySQL 5.7 and fix the reported issues and then start the upgrade process again.

In my previous blog post, I had described  how the Upgrade Checker utility that comes with the MySQL Shell 8.0 can be run against a MySQL 5.7 server to check for upgrade preparedness. The Upgrade Checker will perform the most of the checks that in-place MySQL 8.0 upgrade does and if it does not find any issues, the upgrade to MySQL 8.0 will succeed.

Another thing to be aware of is that MySQL 8.0 has introduced an auto-upgrade mechanism. As of MySQL 8.0.11, the server version is written to the data dictionary tablespace. When the server starts, it will compare the server version number found in the mysql.ibd file (source) with its own server version number (target) and will perform an in-place upgrade only if it supports the source-target combination. The intention here is to automate the upgrade process, i.e. remove the need for the mysql_upgrade script in the future.  The immediate consequence for MySQL 8.0.11 is that it refuses to upgrade from MySQL 8.0 DMRs (8.0.1, 8.0.1, 8.0.2, 8.0.3, 8.0.4) to MySQL 8.0.11 because this path is not safe. It will accept upgrade from any MySQL 5.7 GA release to MySQL 8.0.11, and it will accept upgrade to upcoming MySQL 8.0 releases (8.0.12, 8.0.13, etc.).

But now, let us take a look at the incompatibilities between MySQL 5.7 and MySQL 8.0, most of which are caught by the Upgrade Checker as well as checked when a MySQL 8.0 server starts on a MySQL 5.7 image.

Following is the list that needs to be reviewed and cleaned up before upgrading to MySQL 8.0 (done automatically by the Upgrade Checker):

  1. The transactional data dictionary(DD) support is introduced in MySQL 8.0 for which several new DD tables are created in the mysql schema. Hence user tables with the conflicting names in the mysql schema should be dropped or renamed prior to upgrade. The DD table names are mentioned below:‘catalogs’, ‘character_sets’, ‘collations’, ‘column_statistics’, ‘column_type_elements’, ‘columns’, ‘dd_properties’, ‘events’, ‘foreign_key_column_usage’, ‘foreign_keys’, ‘index_column_usage’, ‘index_partitions’, ‘index_stats’, ‘indexes’, ‘parameter_type_elements’, ‘parameters’, ‘resource_groups’, ‘routines’, ‘schemata’, ‘st_spatial_reference_systems’, ‘table_partition_values’, ‘table_partitions’, ‘table_stats’, ‘tables’, ‘tablespace_files’, ‘tablespaces’, ‘triggers’, ‘view_routine_usage’, ‘view_table_usage’.  The tables that require to be renamed or dropped can be identified by running the following query in MySQL 5.7. (In this example the catalogs is a user table present in the mysql schema which conflicts with the DD name.)

    Hence such user tables should be renamed or dropped prior to upgrade:

    An attempt to upgrade with any one of the above mentioned tables results in an error:
  2. Data types like old style decimals, old style varchar, old style TIME/DATETIME and TIMESTAMP types which became obsolete in MySQL 5.1, MySQL 5.0 and MySQL 5.6 respectively, that have persisted up until MySQL 5.7 due to the binary upgrade will not be supported in MySQL 8.0.  These tables can be identified by running CHECK TABLE…FOR UPGRADE or mysqlcheck with check-upgrade option in MySQL 5.7 prior to upgrade. In addition tables using old style TIME/DATETIME and TIMESTAMP can be identified by enabling a session variable which is discussed in the following blog.

    Tables using such data types cannot be upgraded and should be fixed through REPAIR TABLE and dump/reload for old style varchar/old style decimal:

    Upgrading to MySQL 8.0 when such tables exists will result in an error:
  3. Support for partitioned tables using non-native partitioning was deprecated in MySQL 5.7 and is removed in MySQL 8.0. These tables can be identified in MySQL 5.7 prior to upgrade by using the following query or by running mysqlcheck with check-upgrade option.

    Such tables should be altered to use an engine which supports native partitioning like InnoDB or the partitions should be removed.

    Trying to upgrade to MySQL 8.0 without performing the cleanup will result in an error:
  4. CREATE TRIGGER prior to MySQL 5.0.17 did not support definer attribute. Such trigger definitions with missing/empty definer  attribute or an invalid creation context (i.e character_set_client, collation_collection, database collation attributes) which have persisted until MySQL 5.7 cannot be upgraded. These triggers can be identified by running mysqlcheck with check-upgrade option or CHECK TABLE in MySQL 5.7.

    Such triggers should be dumped/reloaded to fix the issue:

    Attempting to upgrade such triggers will result an error:
  5. In versions prior to MySQL 8.0, InnoDB auto generates foreign key constraint name by appending the table name with ‘_ibfk_X’ where X is a digit, when it is not explicitly specified by the user. If the table name is a multi byte 64 characters, like the cyrillian table name ‘имя_базы_в_кодировке_утф8_длиной_больше_чем_45имя_азы_в_кодировк’ used in the example below, then the auto generated foreign key constraint name exceeds 64 chars.

    In MySQL 8.0, tables with foreign key constraint where the constraint name exceeds 64 chars is not supported in order to adhere to the maximum identifier length of database objects. Such tables can identified by running the following query:

    These tables should be altered by dropping the constraint and adding the constraint with an explicit constraint name by ensuring the foreign key constraint name does not exceed 64 chars.

    Attempting to upgrade these tables without fixing them will result in an error:
  6.  Prior to MySQL 8.0, users could create views with explicit column name up to 255 chars. To adhere to the maximum length of column name, views having explicit column name greater than 64 chars is not supported in MySQL 8.0. Currently these views can be identified only by doing a SHOW CREATE VIEW  in MySQL 5.7.

    These views should be altered to fix the column name prior to upgrade.

    Attempting to upgrade without fixing these views will result an error:
  7. Prior to MySQL 8.0, the total length of all enum elements could go up to approximately 64k since it was stored in 2 bytes in the FRM file.  Hence tables and stored procedures could be created with enum elements greater than 255 chars. However in MySQL 8.0, tables or stored procedures containing too long enum literals(i.e greater 255 chars) are not supported.

    Such tables or stored procedures should be altered before upgrading to MySQL 8.0.

    Any attempt to perform the upgrade without fixing such tables or stored procedures will result in an error:
  8. Attempting to upgrade tables with mis-matched meta data information in the ‘frm’ file and InnoDB dictionary results in an error.  This could be triggered due to frm corruption. Such tables should be fixed through dump and restore before upgrade.

    Attempting to upgrade without fixing this issue will result in an error:
  9. In MySQL 5.7, several spatial functions available under multiple names were deprecated. Example PointFromText:

    These spatial functions were removed in MySQL 8.0 due to spatial function namespace change. The change helps the naming convention to remain consistent i.e functions begin with ‘ST_’ if it performs an exact operation, or with ‘MBR’ if it performs an operation based on minimum bounding rectangles. Generated columns using such functions should be altered prior to upgrade. The list of removed spatial functions can be found in the following documentation.  The generated columns have to be altered to use the corresponding ‘ST_’or ‘MBR’ functions.

    The use of removed spatial functions in generated column definitions will cause an upgrade error:
  10. Before upgrade, the MySQL 5.7 server must be shut down with innodb_fast_shutdown value set as 0 or 1. This will make sure that InnoDB redo logs are empty. Upgrading to MySQL 8.0 with MySQL 5.7 redo logs which are not empty will result in an error:

If the upgrade fails due to any of the above errors (1-10), the server reverts all changes to the data directory. Following which all the redo log files should removed and the MySQL 5.7 server should be started on same data directory to fix the above errors. Once the errors are fixed, a slow shutdown of the 5.7 server should be performed and an in-place upgrade to MySQL 8.0 should be attempted again. Once the server has started, ‘mysql_upgrade’ should be run on the server which marks the completion of upgrade to 8.0 .

Few checks which are not enforced during upgrade and needs to be taken into account before upgrade are:

  • The default/preferred authentication plugin has been changed to ‘caching_sha2_password’ since it provides more secure password encryption ‘mysql_native_password’ plugin and better performance than the ‘sha256_password’. This may cause some of the applications to throw errors related to ‘caching_sha2_password’ plugin after upgrade to MySQL 8.0. This maybe due to the fact the clients/connectors are yet to support ‘caching_sha2_password’ plugin. More information about on how to resolve this can be found in the following blog.
  • GIS support has also undergone substantial changes in MySQL 8.0. The following presentation by Norvald highlights the changes made in MySQL 8.0: GIS Overview . The possible issues that can be encountered while upgrading has been discussed in the presentation under the upgrade section.
  • Several of the defaults have been changed in MySQL 8.0. The most important change in defaults has been the change in the default value of ‘character_set_server’ and ‘character_set_database’ from ‘latin1’ to ‘utf8mb4’. Also the default value of ‘collation_server’ and ‘collation_database’ has been changed from ‘latin1_swedish_ci’ to ‘utf8mb4_0900_ai_ci’. Since the defaults have been changed, there is a possibility of noticing a change in the behavior after upgrading to MySQL 8.0. See New Defaults in MySQL 8.0. Information about variables that have been added, deprecated and removed can be found in the MySQL documentation.
  • The undo log is moved out of the system tablespace as part of the upgrade and the innodb_undo_tablespaces is set to a minimum and default value of ‘two’ undo tablespaces.  The system tablespace cannot be used for InnoDb undo log in MySQL 8.0. This provides flexibility to the user to configure the undo log tablespaces and also truncate the undo log tablespaces without stopping the server.
  • Upgrade from MySQL 5.7 which contains encrypted data files requires an additional step: ALTER INSTANCE ROTATE INNODB MASTER KEY should be executed to ensure it works well with replication.

Further reading:

  • The list of features added, deprecated and removed are listed in the MySQL documentation and can be found here..
  • The prerequisites for upgrading to 8.0 can be found in the MySQL documentation.
  • The changes which affect upgrade to 8.0 can be found here.
  • More information about the upgrade process to 8.0 can be found here.

As always, THANK YOU for using MySQL!

About Nisha Gopalakrishnan

Nisha Gopalakrishnan has been working with MySQL database since 2012. She is working out of the Oracle office in Bangalore as a Senior Software Engineer. Her industry background has been system software development in database internals, file system and security.

Leave a Reply