Before upgrading to MySQL 5.6, 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. Sometimes this
involves dumping and reloading tables, or use of a statement such
CHECK TABLE or
For dump and reload instructions, see
Section 2.11.10, “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 220.127.116.11, “REPAIR TABLE Statement”.
Beginning with MySQL 5.6.6, several MySQL Server parameters have defaults that differ from previous releases. See the notes regarding these changes under Configuration Changes, particularly regarding overriding them to preserve backward compatibility if that is a concern.
Beginning with MySQL 5.6.6, several MySQL Server parameters have defaults that differ from previous releases. The motivation for these changes is to provide better out-of-box performance and to reduce the need for the database administrator to change settings manually. These changes are subject to possible revision in future releases as we gain feedback.
In some cases, a parameter has a different static default value. In other cases, the server autosizes a parameter at startup using a formula based on other related parameters or server host configuration, rather than using a static value. For example, the setting for
back_lognow is its previous default of 50, adjusted up by an amount proportional to the value of
max_connections. The idea behind autosizing is that when the server has information available to make a decision about a parameter setting likely to be better than a fixed default, it will.
The following table summarizes changes to defaults. Any of these can be overridden by specifying an explicit value at server startup.
Parameter Old Default New Default
50 Autosized using
1800 (on Windows) 0
1 8 (platform dependent)
CRC32 (changed back to
INNODBin MySQL 5.6.7)
300 Autosized using
With regard to compatibility with previous releases, the most important changes are:
Therefore, if you are upgrading an existing MySQL installation, have not already changed the values of these parameters from their previous defaults, and backward compatibility is a concern, you may want to explicitly set these parameters to their previous defaults. For example, put these lines in the server option file:
[mysqld] innodb_file_per_table=0 innodb_checksum_algorithm=INNODB binlog_checksum=NONE
Those settings preserve compatibility as follows:
With the new default of
ALTER TABLEoperations following an upgrade will move
InnoDBtables that are in the system tablespace to individual
innodb_file_per_table=0will prevent this from happening.
innodb_checksum_algorithm=INNODBpermits binary downgrades after upgrading to this release. With a setting of
CRC32, InnoDB would use checksumming that older MySQL versions cannot use.
binlog_checksum=NONE, the server can be used as a replication source without causing failure of older replicas that do not understand binary log checksums.
As of MySQL 5.6.5, pre-4.1 passwords and the
mysql_old_passwordauthentication plugin are deprecated. Passwords stored in the older hash format used before MySQL 4.1 are less secure than passwords that use the native password hashing method and should be avoided. To prevent connections using accounts that have pre-4.1 password hashes, the
secure_authsystem variable is now enabled by default. (To permit connections for accounts that have such password hashes, start the server with
DBAs are advised to convert accounts that use the
mysql_old_passwordauthentication plugin to use
mysql_native_passwordinstead. For account upgrade instructions, see Section 18.104.22.168, “Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin”.
In some early development versions of MySQL 5.6 (5.6.6 to 5.6.10), the server could create accounts with a mismatched password hash and authentication plugin. For example, if the default authentication plugin is
mysql_native_password, this sequence of statements results in an account with a plugin of
mysql_native_passwordbut a pre-4.1 password hash (the format used by
SET old_passwords = 1; CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';
As of MySQL 5.6.11, this mismatch no longer occurs. Instead, the server produces an error:
mysql> SET old_passwords = 1; mysql> CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password'; ERROR 1827 (HY000): The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.
To deal with an account affected by a mismatch, the DBA can modify either the
Passwordcolumn in the account's
mysql.usersystem table row to be consistent with the other column:
old_passwordsto 0, then assign a new password to the account using
PASSWORD(). This sets the
Passwordcolumn to have a 4.1 password hash, consistent with the
mysql_native_passwordplugin. This is the preferred method of fixing the account.
Alternatively, the DBA can change the plugin to
mysql_old_passwordto make the plugin match the password hash format, then flush the privileges. This is not recommended because the
mysql_old_passwordplugin and pre-4.1 password hashing are deprecated and support for them will be removed in a future version of MySQL.
SET sql_mode = ''; CREATE TABLE t (d DATE DEFAULT 0); SET sql_mode = 'NO_ZERO_DATE,STRICT_ALL_TABLES'; INSERT INTO t (d) VALUES(DEFAULT);
In this case, 0 should be accepted for the
CREATE TABLEbut rejected for the
INSERT. However, the server did not evaluate
DEFAULTvalues used for inserts or updates against the current
sql_mode. In the example, the
INSERTsucceeds and inserts
As of MySQL 5.6.13, the server applies the proper
sql_modechecks to generate a warning or error at insert or update time.
A resulting incompatibility for replication if you use statement-based logging (
binlog_format=STATEMENT) is that if a replica is upgraded, a nonupgraded source will execute the preceding example without error, whereas the
INSERTwill fail on the replica and replication will stop.
To deal with this, stop all new statements on the source and wait until the replicas catch up. Then upgrade the replicas followed by the source. Alternatively, if you cannot stop new statements, temporarily change to row-based logging on the source (
binlog_format=ROW) and wait until all replicas have processed all binary logs produced up to the point of this change. Then upgrade the replicas followed by the source and change the source back to statement-based logging.
Incompatible change: MySQL 5.6.11 and later supports
CREATE TABLE ... [SUB]PARTITION BY ALGORITHM=, which can be used to create a table whose
n[LINEAR] KEY (...)
KEYpartitioning is compatible with a MySQL 5.1 server (
n=1). (Bug #14521864, Bug #66462) This syntax is not accepted by MySQL 5.6.10 and earlier, although it is supported in MySQL 5.5 beginning with MySQL 5.5.31. mysqldump in MySQL 5.5.31 and later MySQL 5.5 releases includes the
ALGORITHMoption when dumping tables using this option, but surrounds it with conditional comments, like this:
CREATE TABLE t1 (a INT) /*!50100 PARTITION BY KEY */ /*!50531 ALGORITHM = 1 */ /*!50100 () PARTITIONS 3 */
When importing a dump containing such
CREATE TABLEstatements into a MySQL 5.6.10 or earlier MySQL 5.6 server, the versioned comment is not ignored, which causes a syntax error. Therefore, prior to importing such a dump file, you must either change the comments so that the MySQL 5.6 server ignores them (by removing the string
!50531or replacing it with
!50611, wherever it occurs), or remove them.
This is not an issue with dump files made using MySQL 5.6.11 or later, where the
ALGORITHMoption is written using
/*!50611 ... */.
Incompatible change: For
TIMESTAMPcolumns, the storage required for tables created before MySQL 5.6.4 differs from storage required for tables created in 5.6.4 and later. This is due to a change in 5.6.4 that permits these temporal types to have a fractional part. This change can affect the output of statements that depend on the row format, such as
CHECKSUM TABLE. After upgrading from MySQL 5.5 to MySQL 5.6.4 or later, it is recommended that you also upgrade from MySQL 5.5 to MySQL 5.6
ALTER TABLEcurrently allows the creation of tables containing temporal columns in both MySQL 5.5 and MySQL 5.6.4 (or later) binary format but this makes it more difficult to recreate tables in cases where
.frmfiles are not available. Additionally, as of MySQL 5.6.4, the aforementioned temporal types are more space efficient. For more information about changes to temporal types in MySQL 5.6.4, see Date and Time Type Storage Requirements.
As of MySQL 5.6.16,
ALTER TABLEupgrades old temporal columns to 5.6 format for
ADD INDEX, and
FORCEoperations. Hence, the following statement upgrades a table containing columns in the old format:
ALTER TABLE tbl_name FORCE;
This conversion cannot be done using the
INPLACEalgorithm because the table must be rebuilt, so specifying
ALGORITHM=INPLACEin these cases results in an error. Specify
ALTER TABLEdoes produce a temporal-format conversion, it generates a message that can be displayed with
TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.
When upgrading to MySQL 5.6.4 or later, be aware that
CHECK TABLE ... FOR UPGRADEdoes not report temporal columns that use the pre-MySQL 5.6.4 format (Bug #73008, Bug #18985579). In MySQL 5.6.24, two new system variables,
show_old_temporals, were added to provide control over temporal column upgrades (Bug #72997, Bug #18985760).
Due to the temporal type changes described in the previous incompatible change item above, importing pre-MySQL 5.6.4 tables (using
ALTER TABLE ... IMPORT TABLESPACE) that contain
TIMESTAMPtypes into MySQL 5.6.4 (or later) fails. Importing a MySQL 5.5 table with these temporal types into MySQL 5.6.4 (or later) is the mostly likely scenario for this problem to occur.
The following procedures describe workarounds that use the original pre-MySQL 5.6.4
.frmfile to recreate a table with a row structure that is compatible with 5.6.4 (or later). The procedures involve changing the original pre-MySQL 5.6.4
.frmfile to use the
Memorystorage engine instead of
InnoDB, copying the
.frmfile to the data directory of the destination instance, and using
ALTER TABLEto change the table's storage engine type back to
InnoDB. Use the first procedure if your tables do not have foreign keys. Use the second procedure, which has additional steps, if your table includes foreign keys.
If the table does not have foreign keys:
Copy the table's original
.frmfile to the data directory on the server where you want to import the tablespace.
Modify the table's
.frmfile to use the
Memorystorage engine instead of the
InnoDBstorage engine. This modification requires changing 7 bytes in the
.frmfile that define the table's storage engine type. Using a hexidecimal editing tool:
Change the byte at offset position 0003, which is the
Memory), as shown below:
00000000 fe 01 09 06 03 00 00 10 01 00 00 30 00 00 10 00
The remaining 6 bytes do not have a fixed offset. Search the
.frmfile for “
InnoDB” to locate the line with the other 6 bytes. The line appears as shown below:
00001010 ff 00 00 00 00 00 00 06 00 49 6e 6e 6f 44 42 00 |.........InnoDB.|
Modify the bytes so that the line appears as follows:
00001010 ff 00 00 00 00 00 00 06 00 4d 45 4d 4f 52 59 00
ALTER TABLE ... ENGINE=INNODBto add the table definition to the
InnoDBdata dictionary. This creates the
InnoDBtable with the temporal data types in the new format. For the
ALTER TABLEoperation to complete successfully, the
.frmfile must correspond to the tablespace.
Import the table using
ALTER TABLE ... IMPORT TABLESPACE.
If table has foreign keys:
Recreate the tables with foreign keys using table definitions from
SHOW CREATE TABLEoutput. The incorrect temporal column formats do not matter at this point.
Drop all tables and complete the table import process described in steps 1 to 4 in the procedure described above for tables without foreign keys.
After the import operation is complete, add the foreign keys from foreign key definitions that you saved to a text file.
Incompatible change: As of MySQL 5.6, the full-text stopword file is loaded and searched using
utf32. If any table was created with
FULLTEXTindexes while the server character set was
utf32, repair it using this statement:
REPAIR TABLE tbl_name QUICK;
Incompatible change: In MySQL 5.6.20, the patch for Bug #69477 limits the size of redo log
BLOBwrites to 10% of the redo log file size. As a result of this new limit,
innodb_log_file_sizeshould be set to a value greater than 10 times the largest
BLOBdata size found in the rows of your tables. No action is required if your
innodb_log_file_sizesetting is already 10 times the largest
BLOBdata size or your tables contain no
As of MySQL 5.6.42, 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
is called by
InnoDB functions that determine
the maximum row size permitted when creating compressed
InnoDB tables or inserting rows into
InnoDB tables. As a result,
CREATE TABLE ...
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 5.6 test instance prior to upgrading.
Some keywords may be reserved in MySQL 5.6 that were not reserved in MySQL 5.5. 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”.
YEAR(2)data type has certain issues that you should consider before choosing to use it. As of MySQL 5.6.6,
YEAR(2)columns in existing tables are treated as before, but
YEAR(2)in new or altered tables is converted to
YEAR(4). For more information, see Section 11.2.5, “2-Digit YEAR(2) Limitations and Migrating to 4-Digit YEAR”.
As of MySQL 5.6.6, it is explicitly disallowed to assign the value
DEFAULTto stored procedure or function parameters or stored program local variables (for example with a
SETstatement). This was not previously supported, or documented as permitted, but is flagged as an incompatible change in case existing code inadvertently used this construct. It remains permissible to assign
DEFAULTto system variables, as before, but assigning
DEFAULTto parameters or local variables now results in a syntax error.
After an upgrade to MySQL 5.6.6 or later, existing stored programs that use this construct produce a syntax error when invoked. If a mysqldump file from 5.6.5 or earlier is loaded into 5.6.6 or later, the load operation fails and affected stored program definitions must be changed.
In MySQL, the
TIMESTAMPdata type differs in nonstandard ways from other data types:
TIMESTAMPcolumns not explicitly declared with the
NULLattribute are assigned the
NOT NULLattribute. (Columns of other data types, if not explicitly declared as
NOT NULL, permit
NULLvalues.) Setting such a column to
NULLsets it to the current timestamp.
TIMESTAMPcolumn in a table, if not declared with the
NULLattribute or an explicit
ON UPDATEclause, is automatically assigned the
ON UPDATE CURRENT_TIMESTAMPattributes.
TIMESTAMPcolumns following the first one, if not declared with the
NULLattribute or an explicit
DEFAULTclause, are automatically assigned
DEFAULT '0000-00-00 00:00:00'(the “zero” timestamp). For inserted rows that specify no explicit value for such a column, the column is assigned
'0000-00-00 00:00:00'and no warning occurs.
Those nonstandard behaviors remain the default for
TIMESTAMPbut as of MySQL 5.6.6 are deprecated and this warning appears at startup:
[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
As indicated by the warning, to turn off the nonstandard behaviors, enable the new
explicit_defaults_for_timestampsystem variable at server startup. With this variable enabled, the server handles
TIMESTAMPas follows instead:
TIMESTAMPcolumns not explicitly declared as
NULLvalues. Setting such a column to
NULLsets it to
NULL, not the current timestamp.
TIMESTAMPcolumn is assigned the
ON UPDATE CURRENT_TIMESTAMPattributes automatically. Those attributes must be explicitly specified.
TIMESTAMPcolumns declared as
NOT NULLand without an explicit
DEFAULTclause are treated as having no default value. For inserted rows that specify no explicit value for such a column, the result depends on the SQL mode. If strict SQL mode is enabled, an error occurs. If strict SQL mode is not enabled, the column is assigned the implicit default of
'0000-00-00 00:00:00'and a warning occurs. This is similar to how MySQL treats other temporal types such as
To upgrade servers used for replication, upgrade the replicas first, then the source. Replication between the source and its replicas should work provided that all use the same value of
Bring down the replicas, upgrade them, configure them with the desired value of
explicit_defaults_for_timestamp, and bring them back up.
The replicas will recognize from the format of the binary logs received from the source that the source is older (predates the introduction of
explicit_defaults_for_timestamp) and that operations on
TIMESTAMPcolumns coming from the source use the old
Bring down the source, upgrade it, and configure it with the same
explicit_defaults_for_timestampvalue used on the replicas, and bring it back up.