Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 34.7Mb
PDF (A4) - 34.7Mb
PDF (RPM) - 32.6Mb
HTML Download (TGZ) - 8.2Mb
HTML Download (Zip) - 8.3Mb
HTML Download (RPM) - 7.1Mb
Man Pages (TGZ) - 129.9Kb
Man Pages (Zip) - 185.4Kb
Info (Gzip) - 3.2Mb
Info (Zip) - 3.2Mb

MySQL 8.0 Reference Manual  /  ...  /  Changes Affecting Upgrades to MySQL 8.0

Pre-General Availability Draft: 2018-01-12 Changes Affecting Upgrades to MySQL 8.0

Before upgrading to MySQL 8.0, review the changes described in this section to identify upgrade issues that apply to your current MySQL installation and applications.


In addition to the changes outlined in this section, review the Release Notes and other important information outlined in Before You Begin.

Changes marked as either Known issue or Incompatible change are incompatibilities with earlier versions of MySQL, and may require your attention before you upgrade. 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 any upgrade issue applicable to your installation involves an incompatibility that requires special handling, follow the instructions given in the incompatibility description. Sometimes this involves dumping and reloading tables, or use of a statement such as CHECK TABLE or REPAIR TABLE.

For dump and reload instructions, see Section 2.10.3, “Rebuilding or Repairing Tables or Indexes”. Any procedure that involves REPAIR TABLE with the USE_FRM option must be done before upgrading. Use of this statement with a version of MySQL different from the one used to create the table (that is, using it after upgrading) may damage the table. See Section, “REPAIR TABLE Syntax”.

Data Dictionary Changes

Incompatible change: 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 is somewhat different from previous MySQL releases and requires that you verify the upgrade readiness of your installation by checking specific prerequisites. For more information, see Verifying Upgrade Prerequisites for Your MySQL 5.7 Installation. A data dictionary-enabled server entails some general operational differences; see Section 14.7, “Data Dictionary Usage Differences”.

Configuration Changes
  • Incompatible change: As of MySQL 8.0.4, for improved authentication of client connections based on more secure password hashing, the default_authentication_plugin system variable default value has been changed from mysql_native_password to caching_sha2_password. Because the caching_sha2_password authentication plugin is available only as of MySQL 8.0.4, clients linked against a version of libmysqlclient from before MySQL 8.0.4 cannot connect to accounts that authenticate using caching_sha2_password. To work around this problem, relink clients against libmysqlclient from MySQL 8.0.4 or higher.

  • Incompatible change: A MySQL storage engine is now responsible for providing its own partitioning handler, and the MySQL server no longer provides generic partitioning support. InnoDB is the only storage engine providing a native partitioning handler that is supported in MySQL 8.0. (The NDB storage engine also provides native partitioning support, but it is not yet supported in MySQL 8.0.) A partitioned table using any other storage engine must be altered—either to convert it to InnoDB, or to remove its partitioning—before upgrading the server, else it cannot be used afterwards.

    For information about converting MyISAM tables to InnoDB, see Section, “Converting Tables from MyISAM to InnoDB”.

    A table creation statement that would result in a partitioned table using a storage engine without such support fails with an error (ER_CHECK_NOT_IMPLEMENTED) in MySQL 8.0. If you import databases from a dump file created in MySQL 5.7 (or earlier) using mysqldump into a MySQL 8.0 server, you must make sure that any statements creating partitioned tables do not also specify an unsupported storage engine, either by removing any references to partitioning, or by specifying the storage engine as InnoDB or allowing it to be set as InnoDB by default.


    The procedure given at Verifying Upgrade Prerequisites for Your MySQL 5.7 Installation, describes how to identify partitioned tables that must be altered before upgrading to MySQL 8.0.

    See Section 23.6.2, “Partitioning Limitations Relating to Storage Engines”, for further information.

  • Incompatible change: Several server error codes are not used and have been removed (for a list, see Features Removed in MySQL 8.0). Applications that test specifically for any of them should be updated.

  • Important change: The default character set has changed from latin1 to utf8mb4. These system variables are affected:

    As a result, the default character set and collation for new objects differ from previously unless an explicit character set and collation are specified. This includes databases and objects within them, such as tables, views, and stored programs. One way to preserve the previous defaults is to start the server with these lines in the my.cnf file:

Server Changes
  • Several spatial functions were removed in MySQL 8.0.0 due to a spatial function namespace change that implemented an ST_ prefix for functions that perform an exact operation, or an MBR prefix for functions that perform an operation based on minimum bounding rectangles. The use of removed spatial functions in generated column definitions could cause an upgrade failure. Before upgrading, run mysqlcheck --check-upgrade for removed spatial functions and replace any that you find with their ST_ or MBR named replacements. For a list of removed spatial functions, refer to Features Removed in MySQL 8.0.

  • As of MySQL 8.0.3, spatial data types permit an SRID attribute, to explicitly indicate the spatial reference system (SRS) for values stored in the column. See Section 11.5.1, “Spatial Data Types”.

    A spatial column with an explicit SRID attribute is SRID-restricted: The column takes only values with that ID, and SPATIAL indexes on the column become subject to use by the optimizer. The optimizer ignores SPATIAL indexes on spatial columns with no SRID attribute. See Section 8.3.3, “SPATIAL Index Optimization”. If you wish the optimizer to consider SPATIAL indexes on spatial columns that are not SRID-restricted, each such column should be modified:

    • Verify that all values within the column have the same SRID. To determine the SRIDs contained in a geometry column col_name, use the following query:

      SELECT DISTINCT ST_SRID(col_name) FROM tbl_name;

      If the query returns more than one row, the column contains a mix of SRIDs. In that case, modify its contents so all values have the same SRID.

    • Redefine the column to have an explicit SRID attribute.

    • Recreate the SPATIAL index.

  • The BACKUP_ADMIN privilege is automatically granted to users with the RELOAD privilege when performing an in-place upgrade to MySQL 8.0.3 or later.

InnoDB Changes
  • Incompatible change: Due to redo log format differences, performing an in-place upgrade to MySQL 8.0 from MySQL 5.7 requires that MySQL 5.7 be shut down using an innodb_fast_shutdown setting of 1 or 0 before upgrading. (It is required that you upgrade from a GA version of MySQL 5.7; that is, 5.7.9 or higher. Upgrades from non-GA versions of MySQL 5.7 are not supported.) Performing a shutdown using innodb_fast_shutdown set to 1 or 0 is a required step in In-Place Upgrade.

  • INFORMATION_SCHEMA views based on InnoDB system tables were replaced by internal system views on data dictionary tables. Affected InnoDB INFORMATION_SCHEMA views were renamed:

    Table 2.13 Renamed InnoDB Information Schema Views

    Old NameNew Name

    After upgrading to MySQL 8.0.3 or later, update any scripts that reference previous InnoDB INFORMATION_SCHEMA view names.

  • The zlib library version bundled with MySQL was raised from version 1.2.3 to version 1.2.11.

    The zlib compressBound() function in zlib 1.2.11 returns a slightly higher estimate of the buffer size required to compress a given length of bytes than it did in zlib version 1.2.3. The compressBound() function is called by InnoDB functions that determine the maximum row size permitted when creating compressed InnoDB tables or inserting rows into compressed InnoDB tables. As a result, CREATE TABLE ... ROW_FORMAT=COMPRESSED or INSERT operations with row sizes very close to the maximum row size that were successful in earlier releases could now fail.

    If you have compressed InnoDB tables with large rows, it is recommended that you test compressed table CREATE TABLE statements on a MySQL 8.0.3 test instance prior to upgrading.

SQL Changes
  • Some keywords may be reserved in MySQL 8.0 that were not reserved in MySQL 5.7. See Section 9.3, “Keywords and Reserved Words”. This can cause words previously used as identifiers to become illegal. To fix affected statements, use identifier quoting. See Section 9.2, “Schema Object Names”.

  • After upgrading, it is recommended that you test optimizer hints specified in application code to ensure that the hints are still required to achieve the desired optimization strategy. Optimizer enhancements can sometimes render certain optimizer hints unnecessary. In some cases, an unnecessary optimizer hint may even be counterproductive.

User Comments
Sign Up Login You must be logged in to post a comment.