Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 33.4Mb
PDF (A4) - 33.4Mb
PDF (RPM) - 31.3Mb
HTML Download (TGZ) - 7.9Mb
HTML Download (Zip) - 8.0Mb
HTML Download (RPM) - 6.8Mb
Man Pages (TGZ) - 145.7Kb
Man Pages (Zip) - 206.7Kb
Info (Gzip) - 3.1Mb
Info (Zip) - 3.1Mb


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

Pre-General Availability Draft: 2017-09-22

2.10.1.1 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.

Note

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 13.7.2.5, “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: 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 15.8.1.4, “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.

    Note

    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 22.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.

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.14 Renamed InnoDB Information Schema Views

    Old NameNew Name
    INNODB_SYS_COLUMNSINNODB_COLUMNS
    INNODB_SYS_DATAFILESINNODB_DATAFILES
    INNODB_SYS_FIELDSINNODB_FIELDS
    INNODB_SYS_FOREIGNINNODB_FOREIGN
    INNODB_SYS_FOREIGN_COLSINNODB_FOREIGN_COLS
    INNODB_SYS_INDEXESINNODB_INDEXES
    INNODB_SYS_TABLESINNODB_TABLES
    INNODB_SYS_TABLESPACESINNODB_TABLESPACES
    INNODB_SYS_TABLESTATSINNODB_TABLESTATS
    INNODB_SYS_VIRTUALINNODB_VIRTUAL

    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.