Pre-General Availability Draft: 2018-01-12
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.
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
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 184.108.40.206, “REPAIR TABLE Syntax”.
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”.
Incompatible change: As of MySQL 8.0.4, for improved authentication of client connections based on more secure password hashing, the
default_authentication_pluginsystem variable default value has been changed from
caching_sha2_password. Because the
caching_sha2_passwordauthentication plugin is available only as of MySQL 8.0.4, clients linked against a version of
libmysqlclientfrom before MySQL 8.0.4 cannot connect to accounts that authenticate using
caching_sha2_password. To work around this problem, relink clients against
libmysqlclientfrom 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.
InnoDBis the only storage engine providing a native partitioning handler that is supported in MySQL 8.0. (The
NDBstorage 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
InnoDB, see Section 220.127.116.11, “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
InnoDBor allowing it to be set as
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
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
[mysqld] character_set_server=latin1 collation_server=latin1_swedish_ci
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
MBRprefix 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
MBRnamed 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
SRIDattribute, 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
SRIDattribute is SRID-restricted: The column takes only values with that ID, and
SPATIALindexes on the column become subject to use by the optimizer. The optimizer ignores
SPATIALindexes on spatial columns with no
SRIDattribute. See Section 8.3.3, “SPATIAL Index Optimization”. If you wish the optimizer to consider
SPATIALindexes 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
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
0before 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
0is a required step in In-Place Upgrade.
Table 2.13 Renamed InnoDB Information Schema Views
Old Name New Name
After upgrading to MySQL 8.0.3 or later, update any scripts that reference previous
The zlib library version bundled with MySQL was raised from version 1.2.3 to version 1.2.11.
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
InnoDBfunctions that determine the maximum row size permitted when creating compressed
InnoDBtables or inserting rows into compressed
InnoDBtables. As a result,
CREATE TABLE ... ROW_FORMAT=COMPRESSEDor
INSERToperations with row sizes very close to the maximum row size that were successful in earlier releases could now fail.
If you have compressed
InnoDBtables with large rows, it is recommended that you test compressed table
CREATE TABLEstatements on a MySQL 8.0.3 test instance prior to upgrading.
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.