MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
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 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 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_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
is CRC32
(previously
INNODB
and changed back to
INNODB
in MySQL 5.6.7).
binlog_checksum
is
CRC32
(previously
NONE
).
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 move
InnoDB
tables that are in
the system tablespace to individual
.ibd
files. Using
innodb_file_per_table=0
prevents this from happening.
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 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, 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
.)
DBAs are advised to convert accounts that use the
mysql_old_password
authentication plugin
to use mysql_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 of
mysql_native_password
but a pre-4.1
password hash (the format used by
mysql_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
with
OLD_PASSWORD()
or with
old_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 '
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.password
';
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
system 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; 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 the sql_mode
value at table-creation time but invalid for the
sql_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
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 replica is upgraded, a source which has not
been upgraded executes the preceding example without error,
whereas the INSERT
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 whose
n
[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 the
ALGORITHM
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
, 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
Date and Time Type Storage Requirements.
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:
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 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.
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
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
and
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
.
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.
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, 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 DEFAULT
to stored procedure or
function parameters or stored program local variables (for
example with a SET
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 var_name
= DEFAULTDEFAULT
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:
[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 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
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 on
TIMESTAMP
columns coming
from the source use the old
TIMESTAMP
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.