MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0
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.
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, see Section 2.11.5, “Preparing Your Installation for Upgrade”. A data dictionary-enabled server entails some general operational differences; see Section 14.7, “Data Dictionary Usage Differences”.
The caching_sha2_password
and
sha256_password
authentication plugins
provide more secure password encryption than the
mysql_native_password
plugin, and
caching_sha2_password
provides better
performance than sha256_password
. Due to
these superior security and performance characteristics of
caching_sha2_password
, it is as of MySQL 8.0
the preferred authentication plugin, and is also the default
authentication plugin rather than
mysql_native_password
. This change affects
both the server and the libmysqlclient
client
library:
For the server, the default value of the
default_authentication_plugin
system variable changes from
mysql_native_password
to
caching_sha2_password
.
This change applies only to new accounts created after
installing or upgrading to MySQL 8.0 or higher. For accounts
already existing in an upgraded installation, their
authentication plugin remains unchanged. Existing users who
wish to switch to caching_sha2_password
can do so using the ALTER
USER
statement:
ALTER USERuser
IDENTIFIED WITH caching_sha2_password BY 'password
';
The libmysqlclient
library treats
caching_sha2_password
as the default
authentication plugin rather than
mysql_native_password
.
The following sections discuss the implications of the more
prominent role of caching_sha2_password
:
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 about
caching_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 than
mysql_native_password
(and consequent
improved client connection authentication). However, it also has
compatibility implications that may affect existing MySQL
installations:
Clients and connectors that have not been updated to know
about caching_sha2_password
may have
trouble connecting to a MySQL 8.0 server configured with
caching_sha2_password
as the default
authentication plugin, even to use accounts that do not
authenticate with caching_sha2_password
.
This issue occurs because the server specifies the name of
its default authentication plugin to clients. If a client or
connector is based on a client/server protocol
implementation that does not gracefully handle an
unrecognized default authentication plugin, it may fail with
an error such as one of these:
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, see Authentication Plugin Connector-Writing Considerations.
Clients that use an account that authenticates with
caching_sha2_password
must use either a
secure connection (made using TCP using TLS/SSL credentials,
a Unix socket file, or shared memory), or an unencrypted
connection that supports password exchange using an RSA key
pair. This security requirement does not apply to
mysql_native_passsword
, so the switch to
caching_sha2_password
may require
additional configuration (see
Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”).
However, client connections in MySQL 8.0 prefer use of
TLS/SSL by default, so clients that already conform to that
preference may need no additional configuration.
Clients and connectors that have not been updated to know
about caching_sha2_password
cannot connect to accounts that
authenticate with caching_sha2_password
because they do not recognize this plugin as valid. (This is
a particular instance of how client/server authentication
plugin compatibility requirements apply, as discussed at
Authentication Plugin Client/Server Compatibility.)
To work around this issue, relink clients against
libmysqlclient
from MySQL 8.0 or higher,
or obtain an updated connector that recognizes
caching_sha2_password
.
Because caching_sha2_password
is also now
the default authentication plugin in the
libmysqlclient
client library,
authentication requires an extra round trip in the
client/server protocol for connections from MySQL 8.0
clients to accounts that use
mysql_native_password
(the previous
default authentication plugin), unless the client program is
invoked with a
--default-auth=mysql_native_password
option.
The libmysqlclient
client library for pre-8.0
MySQL versions is able to connect to MySQL 8.0 servers (except
for accounts that authenticate with
caching_sha2_password
). That means pre-8.0
clients based on libmysqlclient
should also
be able to connect. Examples:
Standard MySQL clients such as mysql and
mysqladmin are
libmysqlclient
-based.
The DBD::mysql driver for Perl DBI is
libmysqlclient
-based.
MySQL Connector/Python has a C Extension module that is
libmysqlclient
-based. To use it, include
the use_pure=False
option at connect
time.
When an existing MySQL 8.0 installation is upgraded to MySQL
8.0.4 or higher, some older
libmysqlclient
-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 the
libmysqlclient
in place after an upgrade to
MySQL 8.0.4 or higher, with this result:
Prior to the upgrade, DBI scripts that use DBD::mysql can
connect to a MySQL 8.0 server, except for accounts that
authenticate with caching_sha2_password
.
After the upgrade, the same scripts become able to use
caching_sha2_password
accounts as well.
However, the preceding results occur because
libmysqlclient
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 with
caching_sha2_password
. (Connector/J 8.0.9 or higher
is required to connect to
caching_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 to
mysql_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:
Some MySQL clients support a
--default-auth
option.
(Standard MySQL clients such as mysql and
mysqladmin support this option but can
successfully connect to 8.0 servers without it. However,
other clients may support a similar option. If so, it is
worth trying it.)
Programs that use the libmysqlclient
C
API can call the
mysql_options()
function
with the MYSQL_DEFAULT_AUTH
option.
MySQL Connector/Python scripts that use the native Python implementation of
the client/server protocol can specify the
auth_plugin
connection option.
(Alternatively, use the Connector/Python C Extension, which is able to
connect to MySQL 8.0 servers without the need for
auth_plugin
.)
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 with
caching_sha2_password
as the default
authentication plugin.
These clients and connectors have been upgraded to support
caching_sha2_password
:
The libmysqlclient
client library in
MySQL 8.0 (8.0.4 or higher). Standard MySQL clients such as
mysql and mysqladmin
are libmysqlclient
-based, so they are
compatible as well.
The libmysqlclient
client library in
MySQL 5.7 (5.7.23 or higher). Standard MySQL clients such as
mysql and mysqladmin
are libmysqlclient
-based, so they are
compatible as well.
MySQL Connector/C++ 1.1.11 or higher or 8.0.7 or higher.
MySQL Connector/J 8.0.9 or higher.
MySQL Connector/NET 8.0.10 or higher (through the classic MySQL protocol).
MySQL Connector/Node.js 8.0.9 or higher.
PHP: the X DevAPI PHP extension (mysql_xdevapi) supports
caching_sha2_password
.
PHP: the PDO_MySQL and ext/mysqli extensions do not support
caching_sha2_password
. In addition, when
used with PHP versions before 7.1.16 and PHP 7.2 before
7.2.4, they fail to connect with
default_authentication_plugin=caching_sha2_password
even if caching_sha2_password
is not
used.
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 at
Section 2.10.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 support
caching_sha2_password
, you can use a modified
data directory-initialization procedure that associates the
root
account with
mysql_native_password
as soon as the account
is created. To do so, use either of these techniques:
Supply a
--default-authentication-plugin=mysql_native_password
option along with
--initialize
or
--initialize-insecure
.
Set
default_authentication_plugin
to mysql_native_password
in an option
file, and name that option file using a
--defaults-file
option along
with --initialize
or
--initialize-insecure
. (In
this case, if you continue to use that option file for
subsequent server startups, new accounts are created with
mysql_native_password
rather than
caching_sha2_password
unless you remove
the
default_authentication_plugin
setting from the option file.)
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 with
caching_sha2_password
. For such connections,
the same requirement applies as for other clients that use
accounts that authenticate with
caching_sha2_password
: Use a secure
connection or RSA-based password exchange.
To connect to a caching_sha2_password
account
for source/replica replication:
Use any of the following CHANGE MASTER
TO
options:
MASTER_SSL = 1
GET_MASTER_PUBLIC_KEY = 1
MASTER_PUBLIC_KEY_PATH='path to RSA public key file
'
Alternatively, you can use the RSA public key-related options if the required keys are supplied at server startup.
To connect to a caching_sha2_password
account
for Group Replication:
For MySQL built using OpenSSL, set any of the following system variables:
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
';
Alternatively, you can use the RSA public key-related options if the required keys are supplied at server startup.
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
and
NDB
are the only storage engines that
provide a native partitioning handler that is supported in
MySQL 8.0. 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.
For information about converting MyISAM
tables to InnoDB
, see
Section 15.6.1.5, “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 Section 2.11.5, “Preparing Your Installation for Upgrade”, describes how to identify partitioned tables that must be altered before upgrading to MySQL 8.0.
See Section 24.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:
The default value of the
character_set_server
and
character_set_database
system variables has changed from
latin1
to utf8mb4
.
The default value of the
collation_server
and
collation_database
system variables has changed from
latin1_swedish_ci
to
utf8mb4_0900_ai_ci
.
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. Assuming that the previous defaults were used, one
way to preserve them is to start the server with these lines
in the my.cnf
file:
[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
.
Incompatible change: As of
MySQL 8.0.11, it is prohibited to start the server with a
lower_case_table_names
setting that is different from the setting used when the
server was initialized. The restriction is necessary because
collations used by various data dictionary table fields are
based on the
lower_case_table_names
setting that was defined when the server was initialized,
and restarting the server with a different setting would
introduce inconsistencies with respect to how identifiers
are ordered and compared.
In MySQL 8.0.11, several deprecated features related to
account management have been removed, such as use of the
GRANT
statement to modify
nonprivilege characteristics of user accounts, the
NO_AUTO_CREATE_USER
SQL mode, the
PASSWORD()
function, and the
old_passwords
system variable.
Replication from MySQL 5.7 to 8.0 of statements that refer to these removed features can cause replication failure. Applications that use any of the removed features should be revised to avoid them and use alternatives when possible, as described in Features Removed in MySQL 8.0.
To avoid a startup failure on MySQL 8.0, remove any instance
of NO_AUTO_CREATE_USER
from
sql_mode
system variable
settings in MySQL option files.
Loading a dump file that includes the
NO_AUTO_CREATE_USER
SQL mode in stored
program definitions into a MySQL 8.0 server causes a
failure. As of MySQL 5.7.24 and MySQL 8.0.13,
mysqldump removes
NO_AUTO_CREATE_USER
from stored program
definitions. Dump files created with an earlier version of
mysqldump
must be modified manually to
remove instances of NO_AUTO_CREATE_USER
.
In MySQL 8.0.11, these deprecated compatibility SQL modes
were removed: DB2
,
MAXDB
, MSSQL
,
MYSQL323
, MYSQL40
,
ORACLE
, POSTGRESQL
,
NO_FIELD_OPTIONS
,
NO_KEY_OPTIONS
,
NO_TABLE_OPTIONS
. They can no longer be
assigned to the sql_mode
system variable
or used as permitted values for the
mysqldump
--compatible
option.
Removal of MAXDB
means that the
TIMESTAMP
data type for
CREATE TABLE
or
ALTER TABLE
is no longer
treated as DATETIME
.
Replication from MySQL 5.7 to 8.0 of statements that refer
to the removed SQL modes can cause replication failure. This
includes replication of CREATE
statements
for stored programs (stored procedures and functions,
triggers, and events) that are executed while the current
sql_mode
value includes any
of the removed modes. Applications that use any of the
removed modes should be revised to avoid them.
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.4.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 want
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
) FROMtbl_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.
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.
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 higher.
From MySQL 8.0.13, because of differences between row-based or mixed replication mode and statement-based replication mode in the way that temporary tables are handled, there are new restrictions on switching the binary logging format at runtime.
SET @@SESSION.binlog_format
cannot
be used if the session has any open temporary tables.
SET @@global.binlog_format
and
SET @@persist.binlog_format
cannot
be used if any replication channel has any open
temporary tables. SET
@@persist_only.binlog_format
is allowed if
replication channels have open temporary tables,
because unlike PERSIST
,
PERSIST_ONLY
does not modify the
runtime global system variable value.
SET @@global.binlog_format
and
SET @@persist.binlog_format
cannot
be used if any replication channel applier is running.
This is because the change only takes effect on a
replication channel when its applier is restarted, at
which time the replication channel might have open
temporary tables. This behavior is more restrictive
than before. SET
@@persist_only.binlog_format
is allowed if
any replication channel applier is running.
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.16 Renamed InnoDB Information Schema Views
Old Name | New Name |
---|---|
INNODB_SYS_COLUMNS |
INNODB_COLUMNS |
INNODB_SYS_DATAFILES |
INNODB_DATAFILES |
INNODB_SYS_FIELDS |
INNODB_FIELDS |
INNODB_SYS_FOREIGN |
INNODB_FOREIGN |
INNODB_SYS_FOREIGN_COLS |
INNODB_FOREIGN_COLS |
INNODB_SYS_INDEXES |
INNODB_INDEXES |
INNODB_SYS_TABLES |
INNODB_TABLES |
INNODB_SYS_TABLESPACES |
INNODB_TABLESPACES |
INNODB_SYS_TABLESTATS |
INNODB_TABLESTATS |
INNODB_SYS_VIRTUAL |
INNODB_VIRTUAL |
After upgrading to MySQL 8.0.3 or higher, 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 and
updating rows in compressed InnoDB
tables. As a result,
CREATE TABLE
... ROW_FORMAT=COMPRESSED
,
INSERT
, and
UPDATE
operations with row
sizes very close to the maximum row size that were
successful in earlier releases could now fail. To avoid this
issue, test CREATE TABLE
statements for compressed InnoDB
tables
with large rows on a MySQL 8.0 test instance prior to
upgrading.
With the introduction of the
--innodb-directories
feature, the location of file-per-table and general
tablespace files created with an absolute path or in a
location outside of the data directory should be added to
the innodb_directories
argument value. Otherwise, InnoDB
is not
able to locate these files during recovery. To view
tablespace file locations, query the
INFORMATION_SCHEMA.FILES
table:
SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES \G
Undo logs can no longer reside in the system tablespace. In MySQL 8.0, undo logs reside in two undo tablespaces by default. For more information, see Section 15.6.3.4, “Undo Tablespaces”.
When upgrading from MySQL 5.7 to MySQL
8.0, any undo tablespaces that exist in the
MySQL 5.7 instance are removed and replaced by two new
default undo tablespaces. Default undo tablespaces are
created in the location defined by the
innodb_undo_directory
variable. If the
innodb_undo_directory
variable is undefined, undo tablespaces are created in the
data directory. Upgrade from MySQL 5.7 to MySQL 8.0 requires
a slow shutdown which ensures that undo tablespaces in the
MySQL 5.7 instance are empty, permitting them to be removed
safely.
When upgrading to MySQL 8.0.14 or later from an earlier
MySQL 8.0 release, undo tablespaces that exist in the
pre-upgrade instance as a result of an
innodb_undo_tablespaces
setting greater than 2 are treated as user-defined undo
tablespaces, which can be deactivated and dropped using
ALTER UNDO
TABLESPACE
and
DROP UNDO
TABLESPACE
syntax, respectively, after upgrading.
Upgrade within the MySQL 8.0 release series may not always
require a slow shutdown which means that existing undo
tablespaces could contain undo logs. Therefore, existing
undo tablespaces are not removed by the upgrade process.
Incompatible change: As of
MySQL 8.0.17, the
CREATE
TABLESPACE ... ADD DATAFILE
clause does not permit
circular directory references. For example, the circular
directory reference (/../
) in the
following statement is not permitted:
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
any_directory
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_SCHEMA.INNODB_DATAFILES
table.
Due to a regression introduced in MySQL 8.0.14, in-place
upgrade on a case-sensitive file system from MySQL 5.7 or a
MySQL 8.0 release prior to MySQL 8.0.14 to MySQL 8.0.16
failed for instances with partitioned tables and
lower_case_table_names=1
.
The failure was caused by a case mismatch issue related to
partitioned table file names. The fix that introduced the
regression was reverted, which permits upgrades to MySQL
8.0.17 from MySQL 5.7 or MySQL 8.0 releases prior to MySQL
8.0.14 to function as normal. However, the regression is
still present in the MySQL 8.0.14, 8.0.15, and 8.0.16
releases.
In-place upgrade on a case-sensitive file system from MySQL
8.0.14, 8.0.15, or 8.0.16 to MySQL 8.0.17 fails with the
following error when starting the server after upgrading
binaries or packages to MySQL 8.0.17 if partitioned tables
are present and
lower_case_table_names=1
:
Upgrading from server versionversion_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 TABLEpart_table_name
TOnew_table_name
; RENAME TABLEnew_table_name
TOpart_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:
Restart the server with
--upgrade=force
to force
the upgrade operation to proceed.
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#%';
For each file identified, rename the associated table using a temporary name, then rename the table back to its original name.
mysql> RENAME TABLEtable_name
TOtemporary_table_name
; mysql> RENAME TABLEtemporary_table_name
TOtable_name
;
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)
Run ANALYZE TABLE
on each
renamed table to update the optimizer statistics in the
mysql.innodb_index_stats
and
mysql.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 where
lower_case_table_names=1
.
Attempting to do so results in a “Tablespace is
missing for table” error.
MySQL uses delimiter strings when constructing tablespace
names and file names for table partitions. A “
#p#
” delimiter string precedes
partition names, and an “ #sp#
” delimiter string precedes subpartition names, as
shown:
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 the
lower_case_table_names
setting to ensure case-insensitivity. For example, if a
table partition is created with the name
PART_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:
Partition file names on disk and in the data dictionary to ensure lowercase delimiters and partition names.
Partition metadata in the data dictionary for related issues introduced by previous bug fixes.
InnoDB
statistics data for related
issues introduced by previous bug fixes.
During tablespace import operations, partition tablespace file names on disk are checked and modified if necessary to ensure lowercase delimiters and partition names.
As of MySQL 8.0.21, a warning is written to the error log at
startup or when upgrading from MySQL 5.7 if
tablespace data files are found to reside in unknown
directories. Known directories are those defined by the
datadir
,
innodb_data_home_dir
, and
innodb_directories
variables. To make a directory known, add it to the
innodb_directories
setting.
Making directories known ensures that data files can be
found during recovery. For more information, see
Tablespace Discovery During Crash Recovery.
Incompatible change: As of
MySQL 8.0.13, the deprecated ASC
or
DESC
qualifiers for GROUP
BY
clauses have been removed. Queries that
previously relied on GROUP BY
sorting may
produce results that differ from previous MySQL versions. To
produce a given sort order, provide an ORDER
BY
clause.
Queries and stored program definitions from MySQL 8.0.12 or
lower that use ASC
or
DESC
qualifiers for GROUP
BY
clauses should be amended. Otherwise, upgrading
to MySQL 8.0.13 or higher may fail, as may replicating to
MySQL 8.0.13 or higher replica servers.
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.
Incompatible change: In
MySQL 5.7, specifying a FOREIGN KEY
definition for an InnoDB
table without a
CONSTRAINT
clause, or specifying the
symbol
CONSTRAINT
keyword without a
symbol
, causes InnoDB
to use a generated constraint name. That behavior changed in
MySQL 8.0, with InnoDB
using the
FOREIGN KEY
value instead of a generated name. Because
constraint names must be unique per schema (database), the
change caused errors due to foreign key index names that
were not unique per schema. To avoid such errors, the new
constraint naming behavior has been reverted in MySQL
8.0.16, and index_name
InnoDB
once again uses a
generated constraint name.
For consistency with InnoDB
,
NDB
releases based on MySQL 8.0.16 or
higher use a generated constraint name if the
CONSTRAINT
clause is not specified, or the
symbol
CONSTRAINT
keyword is specified without a
symbol
. NDB
releases
based on MySQL 5.7 and earlier MySQL 8.0 releases used the
FOREIGN KEY
value.
index_name
The changes described above may introduce incompatibilities for applications that depend on the previous foreign key constraint naming behavior.