Skip to content

Upgrading from MySQL 5.7 to 8.0

This page summarises the main changes between MySQL 5.7 and MySQL 8.0

For more information, please refer to the official MySQL documentation:

What Is New in MySQL 8.0 summarizes what has been added to, deprecated in, and removed from MySQL 8.0

Changes in MySQL 8.0 should be reviewed to identify the changes - especially those "incompatible" - that apply to your current MySQL installation and applications and perform any recommended actions.

Main differences between MySQL 5.7 and 8.0

  1. caching_sha2_password is now the default authentication plugin: you should check if your applications and proxies will work properly with it or, change the configuration—for example back to the previous mysql_native_password authentication plugin—but make sure you won’t end up with MySQL 8.0 and apps that cannot connect to it. NB: Reverting to the old authentication plugin should not be considered a permanent solution as it could be deprecated soon.

  2. the default character set has changed from latin1 to utf8mb4: when using the new character set the data size is likely to increase, the memory buffers will be able to store less data and generally performance can be reduced. The default server and database collation parameters are also changed. Assuming that the previous defaults were used, one way to preserve the old settings and prevent new objects from using the new default is to start the server with:

     (in the my.cnf configuration file)
 [mysqld]
 character_set_server=latin1
 collation_server=latin1_swedish_ci

    This MUST be used when upgrading a primary/replica(s) cluster until all nodes (replicas first) are upgraded. After the upgrade, the new default can be safely applied to the primary and all the replicas so that new objects are created with the new character set. NB: Please be aware that utf8mb3 is deprecated and all the existing objects using it must be sooner or later converted to utf8mb4. Our recommendation is to get in touch to planning in advance for this operation.

  3. when planning the upgrade a primary/replica(s) cluster, we suggest to open a SR because:

    When you upgrade a source to 8.0 from an earlier MySQL release series, you should first ensure that all the replicas of this source are using the same 8.0.x release. If this is not the case, you should first upgrade the replicas. To upgrade each replica, shut it down, upgrade it to the appropriate 8.0.x version, restart it, and restart replication. Relay logs created by the replica after the upgrade are in 8.0 format. A full explanation of the technical reasons can be found at Upgrading a Replication Setup in the official documentation

  4. MySQL server no longer provides generic partitioning support. MyISAM engine does not support native partitioning. Only InnoDB and NBD have this functionality. A partitioned table using any other storage engine must be altered—either to convert it to InnoDB or NDB, or to remove its partitioning—before upgrading the server, else it cannot be used afterwards. see Section 15.6.1.5, “Converting Tables from MyISAM to InnoDB”

  5. As usual with new MySQL releases, the list of reserved keywords has changed. People should confirm that it’s not affecting their queries and table structure. More details in the MySQL documentation: MySQL 8.0 New Keywords and Reserved Words

  6. Some configuration parameters are becoming obsolete and deprecated, among those more commonly used:

New Features and Noticeable Improvements

There are many good reasons to upgrade to MySQL 8. A number of new features and important improvements have been released to ramp up the evolution of MySQL and make it more capable to compete with alternative DB technologies and new ways of managing data stores. Here is not exhaustive list of the significant changes which should be reviewed as opportunities to evolve and improve your data intensive applications.

SQL and Applications

  • Single, atomic DDL transactions across all involved components (new transactional data dictionary, storage engine operations, binary log writes) can be particularly beneficial for schema migrations.
  • Expressions can now be used to set default values in data type specifications (also for BLOB, TEXT, GEOMETRY, and JSON)
  • Error logging has been rewritten to use the MySQL component architecture, allowing the usage of traditional built-in components and loadable components like system log and a new JSON log writer.
  • New Document Store functionalities can now allow SQL and NoSQL applications to coexist in a single database, see Using MySQL as a Document Store

Administration

  • A new resource groups management can be used to achieve a better control of resource consumption (threads execute according to the resources available to the group they are assigned) and potentially to provide mitigation of the "noisy neighbors" effects, if/when applied consistently across the entire estate.
  • Invisible indexes: making an index invisible can now be used to test the effect of the missing index on query performance without actually removing it.
  • A simpler upgrade process: all upgrades tasks to system tables, sys schema, user schema performed at next startup (no need for DBA to invoke mysql_upgrade)
  • The new LOCK INSTANCE FOR BACKUP permits DML during online backups while preventing operations that could cause an inconsistent snapshot.
  • More administrative resilience when max_connections are established by configuring a dedicated TCP/IP port as an alternative to the single administrative connection (which is otherwise the only allowed way to connect).
  • A new SET PERSIST syntax can now be used to persist global system variable settings to a file named mysqld-auto.cnf to overcome the current limitations of configuring global system variables at runtime, which either require login access to the server host, or do not provide the capability of persistently configuring the server at runtime or remotely.

Security & Account Management

  • The new caching_sha2_password authentication plugin is now the default. It combine more secure encryption than mysql_native_password and better performance than sha256_password.
  • Support for roles (named collections of privileges) is now available. Roles can have privileges granted to and revoked from them and can be granted to and revoked from user accounts.
  • Support for password history, enabling restrictions on reuse of previous passwords.
  • New account configuration to allow temporary locking when too many consecutive login failures due to incorrect passwords are detected.

InnoDB

  • Maximum auto-increment counter value persistent across server restarts
  • Better corruption detection during recovery consulting: a corruption flag written to the redo log (making index tree corruption flag crash-safe) and in-memory corruption flag data (written to an engine-private system table on each checkpoint).
  • Disabling deadlock detection is now possible with the new dynamic variable innodb_deadlock_detect. On high concurrency systems deadlock detection can cause a slowdown when numerous threads wait for the same lock and it may be more efficient to disable deadlock detection and rely on the innodb_lock_wait_timeout setting for transaction rollback when a deadlock occurs.
  • Temporary tables are now created in the shared temporary tablespace, ibtmp1
  • Mysql system tables and data dictionary tables are now created in a single InnoDB tablespace file named mysql.ibd
  • Undo logs are no longer created in the system tablespace but reside in two undo tablespaces created during instance initialization.
  • Useful for instances running on a dedicated server: innodb_dedicated_server variable, disabled by default, can be used to automatically configure innodb_buffer_pool_size, innodb_log_file_size, and innodb_flush_method according to the amount of memory detected on the server.
  • Tablespace files can be moved or restored to a new location while the server is offline using the innodb_directories option.

Recommendations

  1. Before upgrading your production instance from the DBOD web portal, we recommend you to validate first the upgrade process in a test instance that you may have running MySQL 5.7, or by creating a clone of your current production instance through the web interface. As usual, the clone will be available for one month.
  2. Please validate your applications against the test/clone instance that you just upgraded to be sure that everything will work as expected when you'll upgrade your production instance.

Known Issues & Unwanted Collateral Effects

  1. Enhancements in the handling of MEDIUMBLOB and LONGBLOB columns can result in [ERROR] [MY-013131] [Server] Out of sort memory, consider increasing server sort buffer size! when trying to sort rows containing very large (multi-megabtye) TEXT, BLOB, JSON or GEOMETRY values if the sort buffers are of insufficient size; this can be compensated for in the usual fashion by increasing the value of the sort_buffer_size system variable. (Bug #30400985, Bug #30804356). A full explanation is reported here. We recommend to check your database for such columns types and values and discover by testing what is the sort-buffer-size required for your use case to prevent the errors.

    Sometimes it would be useful to know what query is causing the problem before going ahead with increasing the parameter values: sort_buffer_size which default is currently 256K and is relevant for order/group by operations (not using an index).

    It is recommended reading how to proceed to identify the best value for sort_buffer_size at https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_sort_buffer_size keeping in mind for example that "On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation, so you should consider staying below one of those values." and also "The optimizer tries to work out how much space is needed but can allocate more, up to the limit. Setting it larger than required globally slows down most queries that perform sorts. It is best to increase it as a session setting, and only for the sessions that need a larger size".

    You can test the new candidate value on a cloned instance. Before that you should be able to change the parameter dynamically at the session level. You could also play on the clone with the optimizer hint : /*+ SET_VAR(sort_buffer_size = 1M)*/ as described at https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-set-var to find out the minimum value required for running the query successfully but it would be good to do some sanity checks for example to verify if the sort in memory is preventable by using an index or if some columns might be omitted from the sort order.

    MySQL v8.0.28 comes with a bug fix providing some mitigation of this issue:

    Sorts of some column types, including JSON and TEXT, sometimes exhausted the sort buffer if its size was not at least 15 times that of the largest row in the sort. Now the sort buffer need only be only 15 times as large as the largest sort key. (Bug #103325, Bug #105532, Bug #32738705, Bug #33501541)

  2. the NO_AUTO_CREATE_USER sql mode parameter is incompatible with MySQL 8. It is going to block the instance restart required after the upgrade. Unfortunately it is not detected by the upgrade checker job. Please verify your configuration file and remove the parameter before the upgrade.