3.5 Changes in MySQL 8.0 (original) (raw)

Before upgrading to MySQL 8.0, review the changes described in this section to identify those that apply to your current MySQL installation and applications. Perform any recommended actions.

Changes marked as Incompatible change are incompatibilities with earlier versions of MySQL, and may require your attention before upgrading. Our aim is to avoid these changes, but occasionally they are necessary to correct problems that would be worse than an incompatibility between releases. If an upgrade issue applicable to your installation involves an incompatibility, follow the instructions given in the description.

Data Dictionary Changes

MySQL Server 8.0 incorporates a global data dictionary containing information about database objects in transactional tables. In previous MySQL series, dictionary data was stored in metadata files and nontransactional system tables. As a result, the upgrade procedure requires that you verify the upgrade readiness of your installation by checking specific prerequisites. For more information, seeSection 3.6, “Preparing Your Installation for Upgrade”. A data dictionary-enabled server entails some general operational differences; seeSection 16.7, “Data Dictionary Usage Differences”.

caching_sha2_password as the Preferred Authentication Plugin

The caching_sha2_password andsha256_password authentication plugins provide more secure password encryption than themysql_native_password plugin, andcaching_sha2_password provides better performance than sha256_password. Due to these superior security and performance characteristics ofcaching_sha2_password, it is as of MySQL 8.0 the preferred authentication plugin, and is also the default authentication plugin rather thanmysql_native_password. This change affects both the server and the libmysqlclient client library:

ALTER USER user  
  IDENTIFIED WITH caching_sha2_password  
  BY 'password';

The following sections discuss the implications of the more prominent role of caching_sha2_password:

caching_sha2_password Compatibility Issues and Solutions

Important

If your MySQL installation must serve pre-8.0 clients and you encounter compatibility issues after upgrading to MySQL 8.0 or higher, the simplest way to address those issues and restore pre-8.0 compatibility is to reconfigure the server to revert to the previous default authentication plugin (mysql_native_password). For example, use these lines in the server option file:

[mysqld]
default_authentication_plugin=mysql_native_password

That setting enables pre-8.0 clients to connect to 8.0 servers until such time as the clients and connectors in use at your installation are upgraded to know aboutcaching_sha2_password. However, the setting should be viewed as temporary, not as a long term or permanent solution, because it causes new accounts created with the setting in effect to forego the improved authentication security provided by caching_sha2_password.

The use of caching_sha2_password offers more secure password hashing thanmysql_native_password (and consequent improved client connection authentication). However, it also has compatibility implications that may affect existing MySQL installations:

Authentication plugin 'caching_sha2_password' is not supported  
Authentication plugin 'caching_sha2_password' cannot be loaded:  
dlopen(/usr/local/mysql/lib/plugin/caching_sha2_password.so, 2):  
image not found  
Warning: mysqli_connect(): The server requested authentication  
method unknown to the client [caching_sha2_password]

For information about writing connectors to gracefully handle requests from the server for unknown default authentication plugins, seeAuthentication Plugin Connector-Writing Considerations.

The libmysqlclient client library for pre-8.0 MySQL versions is able to connect to MySQL 8.0 servers (except for accounts that authenticate withcaching_sha2_password). That means pre-8.0 clients based on libmysqlclient should also be able to connect. Examples:

When an existing MySQL 8.0 installation is upgraded to MySQL 8.0.4 or higher, some olderlibmysqlclient-based clients may“automatically” upgrade if they are dynamically linked, because they use the new client library installed by the upgrade. For example, if the DBD::mysql driver for Perl DBI uses dynamic linking, it can use thelibmysqlclient in place after an upgrade to MySQL 8.0.4 or higher, with this result:

However, the preceding results occur becauselibmysqlclient instances from MySQL 8.0 installations prior to 8.0.4 are binary compatible: They both use a shared library major version number of 21. For clients linked to libmysqlclient from MySQL 5.7 or older, they link to a shared library with a different version number that is not binary compatible. In this case, the client must be recompiled against libmysqlclient from 8.0.4 or higher for full compatibility with MySQL 8.0 servers and caching_sha2_password accounts.

MySQL Connector/J 5.1 through 8.0.8 is able to connect to MySQL 8.0 servers, except for accounts that authenticate withcaching_sha2_password. (Connector/J 8.0.9 or higher is required to connect tocaching_sha2_password accounts.)

Clients that use an implementation of the client/server protocol other than libmysqlclient may need to be upgraded to a newer version that understands the new authentication plugin. For example, in PHP, MySQL connectivity usually is based on mysqlnd, which currently does not know about caching_sha2_password. Until an updated version of mysqlnd is available, the way to enable PHP clients to connect to MySQL 8.0 is to reconfigure the server to revert tomysql_native_password as the default authentication plugin, as previously discussed.

If a client or connector supports an option to explicitly specify a default authentication plugin, use it to name a plugin other than caching_sha2_password. Examples:

caching_sha2_password-Compatible Clients and Connectors

If a client or connector is available that has been updated to know about caching_sha2_password, using it is the best way to ensure compatibility when connecting to a MySQL 8.0 server configured withcaching_sha2_password as the default authentication plugin.

These clients and connectors have been upgraded to supportcaching_sha2_password:

caching_sha2_password and the root Administrative Account

For upgrades to MySQL 8.0, the authentication plugin existing accounts remains unchanged, including the plugin for the'root'@'localhost' administrative account.

For new MySQL 8.0 installations, when you initialize the data directory (using the instructions atSection 2.9.1, “Initializing the Data Directory”), the'root'@'localhost' account is created, and that account uses caching_sha2_password by default. 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 USER as follows to change the account authentication plugin and password:

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

If the client or connector that you use does not yet supportcaching_sha2_password, you can use a modified data directory-initialization procedure that associates theroot account withmysql_native_password as soon as the account is created. To do so, use either of these techniques:

caching_sha2_password and Replication

In replication scenarios for which all servers have been upgraded to MySQL 8.0.4 or higher, replica connections to source servers can use accounts that authenticate withcaching_sha2_password. For such connections, the same requirement applies as for other clients that use accounts that authenticate withcaching_sha2_password: Use a secure connection or RSA-based password exchange.

To connect to a caching_sha2_password account for source/replica replication:

MASTER_SSL = 1  
GET_MASTER_PUBLIC_KEY = 1  
MASTER_PUBLIC_KEY_PATH='path to RSA public key file'

To connect to a caching_sha2_password account for Group Replication:

SET GLOBAL group_replication_recovery_use_ssl = ON;  
SET GLOBAL group_replication_recovery_get_public_key = 1;  
SET GLOBAL group_replication_recovery_public_key_path = 'path to RSA public key file';

Configuration Changes

[mysqld]  
character_set_server=latin1  
collation_server=latin1_swedish_ci

In a replicated setting, when upgrading from MySQL 5.7 to 8.0, it is advisable to change the default character set back to the character set used in MySQL 5.7 before upgrading. After the upgrade is completed, the default character set can be changed to utf8mb4.
In addition, you should be aware that MySQL 8.0 enforces checks on permitted characters in a given character set which MySQL 5.7 does not; this is a known issue. This means that, prior to attempting to upgrade, you should ensure that no comments contain characters that are not defined for the character set in use. You can fix this in either of two ways:

Server Changes

InnoDB Changes

SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES \G  
CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd 'any_directory/../ts1.ibd';

An exception to the restriction exists on Linux, where a circular directory reference is permitted if the preceding directory is a symbolic link. For example, the data file path in the example above is permitted if_anydirectory_ is a symbolic link. (It is still permitted for data file paths to begin with '../'.)
To avoid upgrade issues, remove any circular directory references from tablespace data file paths before upgrading to MySQL 8.0.17 or higher. To inspect tablespace paths, query the Information SchemaINNODB_DATAFILES table.

Upgrading from server version version_number with  
partitioned tables and lower_case_table_names == 1 on a case sensitive file  
system may cause issues, and is therefore prohibited. To upgrade anyway, restart  
the new server version with the command line option 'upgrade=FORCE'. When  
upgrade is completed, please execute 'RENAME TABLE part_table_name  
TO new_table_name; RENAME TABLE new_table_name  
TO part_table_name;' for each of the partitioned tables.  
Please see the documentation for further information.

If you encounter this error when upgrading to MySQL 8.0.17, perform the following workaround:

  1. Restart the server with--upgrade=force to force the upgrade operation to proceed.
  2. Identify partitioned table file names with lowercase partition name delimiters (#p# or#sp#):
mysql> SELECT FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME LIKE '%#p#%' OR FILE_NAME LIKE '%#sp#%';
  1. For each file identified, rename the associated table using a temporary name, then rename the table back to its original name.
mysql> RENAME TABLE table_name TO temporary_table_name;  
mysql> RENAME TABLE temporary_table_name TO table_name;
  1. Verify that there are no partitioned table file names lowercase partition name delimiters (an empty result set should be returned).
mysql> SELECT FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME LIKE '%#p#%' OR FILE_NAME LIKE '%#sp#%';  
Empty set (0.00 sec)
  1. Run ANALYZE TABLE on each renamed table to update the optimizer statistics in themysql.innodb_index_stats andmysql.innodb_table_stats tables.
    Because of the regression still present in the MySQL 8.0.14, 8.0.15, and 8.0.16 releases, importing partitioned tables from MySQL 8.0.14, 8.0.15, or 8.0.16 to MySQL 8.0.17 is not supported on case-sensitive file systems wherelower_case_table_names=1. Attempting to do so results in a “Tablespace is missing for table” error.
      schema_name.table_name#p#partition_name#sp#subpartition_name  
      table_name#p#partition_name#sp#subpartition_name.ibd

Historically, delimiter strings have been uppercase (#P# and #SP#) on case-sensitive file systems such as Linux, and lowercase (#p# and #sp#) on case-insensitive file systems such as Windows. As of MySQL 8.0.19, delimiter strings are lowercase on all file systems. This change prevents issues when migrating data directories between case-sensitive and case-insensitive file systems. Uppercase delimiter strings are no longer used.
Additionally, partition tablespace names and file names generated based on user-specified partition or subpartition names, which can be specified in uppercase or lowercase, are now generated (and stored internally) in lowercase regardless of thelower_case_table_names setting to ensure case-insensitivity. For example, if a table partition is created with the namePART_1, the tablespace name and file name are generated in lowercase: 

      schema_name.table_name#p#part_1  
      table_name#p#part_1.ibd

During upgrade, MySQL checks and modifies if necessary: 

mysql> ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

SQL Changes

Changed Server Defaults

MySQL 8.0 comes with improved defaults, aiming at the best out of the box experience possible. These changes are driven by the fact that technology is advancing (machines have more CPUS, use SSDs and so on), more data is being stored, MySQL is evolving (InnoDB, Group Replication, AdminAPI), and so on. The following table summarizes the defaults which have been changed to provide the best MySQL experience for the majority of users.

For more information about options or variables which have been added, see Option and Variable Changes for MySQL 8.0, in the_MySQL Server Version Reference_.

The following sections explain the changes to defaults and any impact they might have on your deployment.

Server Defaults

InnoDB Defaults

Performance Schema Defaults

Replication Defaults

Group Replication Defaults

Most of these defaults are reasonably good for both development and production environments. An exception to this is the--innodb-dedicated-server option, whose default value remains OFF, although we recommend ON for production environments. The reason for defaulting to OFF is that it causes shared environments such as developer laptops to become unusable, because it takes all the memory it can find.

For production environments we recommend using--innodb-dedicated-server, which determines values for the following InnoDB variables (if not specified explicitly), based on available memory:innodb_buffer_pool_size,innodb_log_file_size, andinnodb_flush_method. SeeSection 17.8.12, “Enabling Automatic InnoDB Configuration for a Dedicated MySQL Server”.

Although the new defaults are the best configuration choices for most use cases, there are special cases, as well as legacy reasons for using existing 5.7 configuration choices. For example, some people prefer to upgrade to 8.0 with as few changes to their applications or operational environment as possible. We recommend to evaluate all the new defaults and use as many as you can. Most new defaults can be tested in 5.7, so you can validate the new defaults in 5.7 production before upgrading to 8.0. For the few defaults where you need your old 5.7 value, set the corresponding configuration variable or startup option in your operational environment.

MySQL 8.0 has the Performance Schemavariables_info table, which shows for each system variable the source from which it was most recently set, as well as its range of values. This provides SQL access to all there is to know about a configuration variable and its values.

Valid Performance Regressions

Performance regressions are expected between MySQL versions 5.7 and 8.0. MySQL 8.0 has more features, changes default values, is more robust, and adds security functionality and additional diagnostic information. Listed here are valid reasons for regressions between these versions which includes potential mediation options. This is not an exhaustive list.

Changes related to default values changing between MySQL versions 5.7 and 8.0:

Transactional Data Dictionary (atomic DDL) was introduced in 8.0.

The more modern TLS ciphers/algorithms used as of 5.7.28 has an effect when TLS (SSL) is enabled (the default):

Performance Schema (PFS) instrumentation is much wider in 8.0 than in 5.7:

Truncating undo tablespaces is enabled by default in 8.0 which can significantly impact performance:

The character classes [[:alpha:]] or[[:digit:]] do not perform as well with regular expression functions such as REGEXP() and RLIKE() in MySQL 8.0 as they did in MySQL 5.7. This is due to the replacement in MySQL 8.0 of the Spencer regular expression library with the ICU library, which uses UTF-16 internally.

Mediation: In place of[[:alpha:]], use [a-zA-Z]; in place of [[:digit:]], use[0-9].