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
as CHECK TABLE
or
REPAIR TABLE
.
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 13.7.2.5, “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_log
now is its previous default of 50, adjusted up by an amount proportional to the value ofmax_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 does so.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 back_log
50 Autosized using max_connections
binlog_checksum
NONE
CRC32
--binlog-row-event-max-size
1024 8192 flush_time
1800 (on Windows) 0 innodb_autoextend_increment
8 64 innodb_buffer_pool_instances
1 8 (platform dependent) innodb_checksum_algorithm
INNODB
CRC32 (changed back to
INNODB
in MySQL 5.6.7)innodb_concurrency_tickets
500 5000 innodb_file_per_table
0
1
innodb_old_blocks_time
0 1000 innodb_open_files
300 Autosized using innodb_file_per_table
,table_open_cache
innodb_stats_on_metadata
ON
OFF
join_buffer_size
128KB 256KB max_allowed_packet
1MB 4MB max_connect_errors
10 100 sync_master_info
0 10000 sync_relay_log
0 10000 sync_relay_log_info
0 10000 With regard to compatibility with previous releases, the most important changes are:
innodb_file_per_table
is enabled (previously disabled).innodb_checksum_algorithm
isCRC32
(previouslyINNODB
and changed back toINNODB
in MySQL 5.6.7).binlog_checksum
isCRC32
(previouslyNONE
).
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
innodb_file_per_table
enabled,ALTER TABLE
operations following an upgrade moveInnoDB
tables that are in the system tablespace to individual.ibd
files. Usinginnodb_file_per_table=0
prevents this from happening.Setting
innodb_checksum_algorithm=INNODB
permits binary downgrades after upgrading to this release. With a setting ofCRC32
, InnoDB would use checksumming that older MySQL versions cannot use.With
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_password
authentication 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, thesecure_auth
system variable is now enabled by default. (To permit connections for accounts that have such password hashes, start the server with--secure_auth=0
.)DBAs are advised to convert accounts that use the
mysql_old_password
authentication plugin to usemysql_native_password
instead. For account upgrade instructions, see Section 6.4.1.3, “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 ofmysql_native_password
but a pre-4.1 password hash (the format used bymysql_old_password
):SET old_passwords = 1; CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';
The mismatch produces symptoms such as being unable to connect to the MySQL server and being unable to use
SET PASSWORD
withOLD_PASSWORD()
or withold_passwords=1
.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
plugin
orPassword
column in the account'smysql.user
system table row to be consistent with the other column:Set
old_passwords
to 0, then assign a new password to the account usingSET PASSWORD
andPASSWORD()
. This sets thePassword
column to have a 4.1 password hash, consistent with themysql_native_password
plugin. This is the preferred method of fixing the account.Alternatively, the DBA can change the plugin to
mysql_old_password
to make the plugin match the password hash format, then flush the privileges. This is not recommended because themysql_old_password
plugin and pre-4.1 password hashing are deprecated; expect support for them to be removed in a future version of MySQL.
Incompatible change: It is possible for a column
DEFAULT
value to be valid for thesql_mode
value at table-creation time but invalid for thesql_mode
value when rows are inserted or updated. Example: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 TABLE
but rejected for theINSERT
. However, the server did not evaluateDEFAULT
values used for inserts or updates against the currentsql_mode
. In the example, theINSERT
succeeds and inserts'0000-00-00'
into theDATE
column.As of MySQL 5.6.13, the server applies the proper
sql_mode
checks 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 source which has not been upgraded executes the preceding example without error, whereas theINSERT
fails on the replica and replication stops.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 whosen
[LINEAR] KEY (...)KEY
partitioning 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 theALGORITHM
option 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 TABLE
statements 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!50531
or 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
ALGORITHM
option is written using/*!50611 ... */
.Incompatible change: For
TIME
,DATETIME
, andTIMESTAMP
columns, 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 asCHECKSUM 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.6TIME
,DATETIME
, andTIMESTAMP
types.ALTER TABLE
currently 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.frm
files 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 TABLE
upgrades old temporal columns to 5.6 format forADD COLUMN
,CHANGE COLUMN
,MODIFY COLUMN
,ADD INDEX
, andFORCE
operations. 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
INPLACE
algorithm because the table must be rebuilt, so specifyingALGORITHM=INPLACE
in these cases results in an error. SpecifyALGORITHM=COPY
if necessary.When
ALTER TABLE
does produce a temporal-format conversion, it generates a message that can be displayed withSHOW WARNINGS
: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 UPGRADE
does 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,avoid_temporal_upgrade
andshow_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 containDATETIME
andTIMESTAMP
types 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
.frm
file 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.frm
file to use theMemory
storage engine instead ofInnoDB
, copying the.frm
file to the data directory of the destination instance, and usingALTER TABLE
to change the table's storage engine type back toInnoDB
. 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
.frm
file to the data directory on the server where you want to import the tablespace.Modify the table's
.frm
file to use theMemory
storage engine instead of theInnoDB
storage engine. This modification requires changing 7 bytes in the.frm
file that define the table's storage engine type. Using a hexidecimal editing tool:Change the byte at offset position 0003, which is the
legacy_db_type
, from0c
(forInnoDB
) to06
(forMemory
), 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
.frm
file 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
Run
ALTER TABLE ... ENGINE=INNODB
to add the table definition to theInnoDB
data dictionary. This creates theInnoDB
table with the temporal data types in the new format. For theALTER TABLE
operation to complete successfully, the.frm
file 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 TABLE
output. The incorrect temporal column formats do not matter at this point.Dump all foreign key definitions to a text file by selecting the foreign key information from the Information Schema
TABLE_CONSTRAINTS
andKEY_COLUMN_USAGE
tables.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
latin1
ifcharacter_set_server
isucs2
,utf16
,utf16le
, orutf32
. If any table was created withFULLTEXT
indexes while the server character set wasucs2
,utf16
,utf16le
, orutf32
, 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
BLOB
writes to 10% of the redo log file size. As a result of this new limit,innodb_log_file_size
should be set to a value greater than 10 times the largestBLOB
data size found in the rows of your tables. No action is required if yourinnodb_log_file_size
setting is already 10 times the largestBLOB
data size or your tables contain noBLOB
data.In MySQL 5.6.22, the redo log
BLOB
write limit is relaxed to 10% of the total redo log size (innodb_log_file_size
*innodb_log_files_in_group
). (Bug #19498877)
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.
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 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”.
The
YEAR(2)
data type has certain issues that you should consider before choosing to use it. As of MySQL 5.6.6,YEAR(2)
is deprecated:YEAR(2)
columns in existing tables are treated as before, butYEAR(2)
in new or altered tables is converted toYEAR(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
DEFAULT
to stored procedure or function parameters or stored program local variables (for example with aSET
statement). 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 assignvar_name
= DEFAULTDEFAULT
to system variables, as before, but assigningDEFAULT
to 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
TIMESTAMP
data type differs in nonstandard ways from other data types:TIMESTAMP
columns not explicitly declared with theNULL
attribute are assigned theNOT NULL
attribute. (Columns of other data types, if not explicitly declared asNOT NULL
, permitNULL
values.) Setting such a column toNULL
sets it to the current timestamp.The first
TIMESTAMP
column in a table, if not declared with theNULL
attribute or an explicitDEFAULT
orON UPDATE
clause, is automatically assigned theDEFAULT CURRENT_TIMESTAMP
andON UPDATE CURRENT_TIMESTAMP
attributes.TIMESTAMP
columns following the first one, if not declared with theNULL
attribute or an explicitDEFAULT
clause, are automatically assignedDEFAULT '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
TIMESTAMP
but 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_timestamp
system variable at server startup. With this variable enabled, the server handlesTIMESTAMP
as follows instead:TIMESTAMP
columns not explicitly declared asNOT NULL
permitNULL
values. Setting such a column toNULL
sets it toNULL
, not the current timestamp.No
TIMESTAMP
column is assigned theDEFAULT CURRENT_TIMESTAMP
orON UPDATE CURRENT_TIMESTAMP
attributes automatically. Those attributes must be explicitly specified.TIMESTAMP
columns declared asNOT NULL
and without an explicitDEFAULT
clause 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 asDATETIME
.
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
explicit_defaults_for_timestamp
:Bring down the replicas, upgrade them, configure them with the desired value of
explicit_defaults_for_timestamp
, and bring them back up.The replicas 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 onTIMESTAMP
columns coming from the source use the oldTIMESTAMP
behavior.Bring down the source, upgrade it, configure it with the same
explicit_defaults_for_timestamp
value used on the replicas, and bring it back up.