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.

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.

Configuration Changes

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

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:

Setting
innodb_checksum_algorithm=INNODB
permits binary downgrades after upgrading to this
release. With a setting of CRC32,
InnoDB would use checksumming that older MySQL versions
cannot use.

With
binlog_checksum=NONE,
the server can be used as a replication master without
causing failure of older slaves 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, the
secure_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.)

Known issue: 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_password but a pre-4.1
password hash (the format used by
mysql_old_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 'mypass';
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 or
Password column in the account's
mysql.user table row to be consistent
with the other column:

Set old_passwords to 0,
then assign a new password to the account using
SET PASSWORD and
PASSWORD(). This sets the
Password column to have a 4.1
password hash, consistent with the
mysql_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 the
mysql_old_password plugin and pre-4.1
password hashing are deprecated and support for them
will be removed in a future version of MySQL.

Server Changes

Incompatible change: It is
possible for a column DEFAULT value to be
valid for the sql_mode
value at table-creation time but invalid for the
sql_mode value when rows
are inserted or updated. Example:

In this case, 0 should be accepted for the
CREATE TABLE but rejected for
the INSERT. However, the
server did not evaluate DEFAULT values
used for inserts or updates against the current
sql_mode. In the example, the
INSERT succeeds and inserts
'0000-00-00' into the
DATE 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 slave is upgraded, a nonupgraded master will
execute the preceding example without error, whereas the
INSERT will fail on the slave
and replication will stop.

To deal with this, stop all new statements on the master and
wait until the slaves catch up. Then upgrade the slaves
followed by the master. Alternatively, if you cannot stop
new statements, temporarily change to row-based logging on
the master
(binlog_format=ROW) and
wait until all slaves have processed all binary logs
produced up to the point of this change. Then upgrade the
slaves followed by the master and change the master back to
statement-based logging.

Incompatible change: MySQL
5.6.11 and later supports
CREATE
TABLE ... [SUB]PARTITION BY
ALGORITHM=n [LINEAR] KEY
(...), which can be used to create a table whose
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 the
ALGORITHM option when dumping tables
using this option, but surrounds it with conditional
comments, like this:

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, and
TIMESTAMP 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 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 TIME,
DATETIME, and
TIMESTAMP 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
Storage Requirements for Date and Time Types.

As of MySQL 5.6.16, ALTER
TABLE upgrades old temporal columns to 5.6 format
for ADD COLUMN, CHANGE
COLUMN, MODIFY COLUMN,
ADD INDEX, and FORCE
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 specifying
ALGORITHM=INPLACE in these cases results
in an error. Specify ALGORITHM=COPY if
necessary.

When ALTER TABLE does produce
a temporal-format conversion, it generates a message that
can be displayed with SHOW
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 and
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
DATETIME and
TIMESTAMP 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 the Memory storage engine
instead of InnoDB, copying the
.frm file to the data directory of the
destination instance, and using ALTER
TABLE to 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 .frm file
to the data directory on the server where you want to
import the tablespace.

Modify the table's .frm file to use
the Memory storage engine instead of
the InnoDB 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, from
0c (for
InnoDB) to 06
(for 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 .frm file for
“InnoDB” to locate
the line with the other 6 bytes. The line appears as
shown below:

Run ALTER
TABLE ... ENGINE=INNODB to add the table
definition to the InnoDB data
dictionary. This creates the InnoDB
table with the temporal data types in the new format.
For the ALTER TABLE
operation to complete successfully, the
.frm file must correspond to the
tablespace.

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 if
character_set_server is
ucs2, utf16,
utf16le, or utf32. If
any table was created with FULLTEXT
indexes while the server character set was
ucs2, utf16,
utf16le, or 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 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 largest
BLOB data size found in the
rows of your tables. No action is required if your
innodb_log_file_size
setting is already 10 times the largest
BLOB data size or your tables
contain no BLOB data.

SQL Changes

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, but YEAR(2) in new or
altered tables are converted to YEAR(4).
For more information, see
Section 11.3.4, “YEAR(2) Limitations and Migrating to YEAR(4)”.

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 a SET
var_name = DEFAULT
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 assign DEFAULT to
system variables, as before, but assigning
DEFAULT 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 the NULL
attribute are assigned the NOT NULL
attribute. (Columns of other data types, if not
explicitly declared as NOT NULL,
permit NULL values.) Setting such a
column to NULL sets it to the current
timestamp.

The first TIMESTAMP
column in a table, if not declared with the
NULL attribute or an explicit
DEFAULT or ON
UPDATE clause, is automatically assigned the
DEFAULT CURRENT_TIMESTAMP and
ON UPDATE CURRENT_TIMESTAMP
attributes.

TIMESTAMP columns
following the first one, if not declared with the
NULL attribute or an explicit
DEFAULT clause, 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
TIMESTAMP but as of MySQL
5.6.6 are deprecated and this warning appears at startup:

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 handles
TIMESTAMP as follows instead:

TIMESTAMP columns not
explicitly declared as NOT NULL
permit NULL values. Setting such a
column to NULL sets it to
NULL, not the current timestamp.

No TIMESTAMP column is
assigned the DEFAULT
CURRENT_TIMESTAMP or ON UPDATE
CURRENT_TIMESTAMP attributes automatically.
Those attributes must be explicitly specified.

TIMESTAMP columns
declared as NOT NULL and without an
explicit DEFAULT 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 as
DATETIME.

To upgrade servers used for replication, upgrade the slaves
first, then the master. Replication between the master and
its slaves should work provided that all use the same value
of
explicit_defaults_for_timestamp:

The slaves will recognize from the format of the binary
logs received from the master that the master is older
(predates the introduction of
explicit_defaults_for_timestamp)
and that operations on
TIMESTAMP columns coming
from the master use the old
TIMESTAMP behavior.