Microsoft Windows: Windows Vista, Windows Server 2008, and newer support native symbolic linking using the mklink command. This makes the MySQL Server implementation of database symbolic links using
.sym
files redundant, so that mechanism is now deprecated and will be removed in a future MySQL release. See Using Symbolic Links for Databases on Windows.
The
--random-passwords
option for mysql_install_db is now supported for MySQL install operations (not upgrades) using Solaris PKG packages.
-
Incompatible Change; Replication: A number of variable and other names relating to GTID-based replication have been changed, with a view to making these names more appropriate and meaningful. The old names are no longer supported.
The features so renamed are shown in the following list:
The
disable_gtid_unsafe_statements
system variable has been renamedenforce_gtid_consistency
.-
The
gtid_done
server system variable has been renamedgtid_executed
.The
gtid_lost
server system variable has been renamedgtid_purged
; in addition, this variable is no longer read-only. The
SQL_THREAD_WAIT_AFTER_GTIDS()
function has been renamedWAIT_UNTIL_SQL_THREAD_AFTER_GTIDS()
.
For more information, see Replication with Global Transaction Identifiers, and Global Transaction ID Options and Variables. (Bug #14775984)
-
For client connections restrictd by the server because the client account password is expired, the server now permits
SET PASSWORD
only if the account named in the statement matches the account used by the client. (Bug #14807074)References: See also: Bug #14698309.
-
The server now provides thread information (for
SHOW PROCESSLIST
) to indicate the progress of in-placeALTER TABLE
operations:-
preparing for alter table
The server is preparing to execute an in-place
ALTER TABLE
. -
altering table
The server is in the process of executing an in-place
ALTER TABLE
. -
committing alter table to storage engine
The server has finished an in-place
ALTER TABLE
and is committing the result.
(Bug #14790408)
-
-
InnoDB
automatically extends each secondary index by appending the primary key columns to it. Previously, the optimizer did not take into account the primary key columns of the extended secondary index when determining how and whether to use that index. Now the optimizer takes the primary key columns into account, which can result in more efficient query execution plans and better performance.The optimizer can use extended secondary keys for
ref
,range
, andindex_merge
index access, for Loose Index Scan, for join and sorting optimization, and forMIN()
/MAX()
optimization.The new
use_index_extensions
flag of theoptimizer_switch
system variable permits control over whether the optimizer takes the primary key columns into account when determining how to use anInnoDB
table's secondary indexes. By default,use_index_extensions
is enabled. To check whether disabling use of index extensions will improve performance, use this statement:SET optimizer_switch = 'use_index_extensions=off';
For more information, see Use of Index Extensions. (Bug #62025, Bug #12814559, Bug #56714, Bug #11763940)
mysqld now writes dates to the error log in ISO (
YYYY-MM-DD hh:mm:ss
) format. It also includes its process ID following the date. Thanks to Davi Arnaut for the patch. (Bug #56240, Bug #11763523)
-
Incompatible Change: The
THREAD_ID
column in Performance Schema tables was widened fromINT
toBIGINT
to accommodate 64-bit values.NoteAs a consequence of this change, the
PROCESSLIST_ID
column of thethreads
table is nowNULL
for background threads. Previously, the value was 0 for background threads.If you upgrade to this MySQL release from an earlier version, you must run mysql_upgrade (and restart the server) to incorporate this change to the
performance_schema
database. (Bug #14664453) -
Incompatible Change:
LAST_INSERT_ID(
did not work forexpr
)expr
values greater than the largest signedBIGINT
value. Such arguments now are accepted, with some consequences for compatibility with previous versions:LAST_INSERT_ID()
now returns aBIGINT UNSIGNED
value, not aBIGINT
(signed) value.LAST_INSERT_ID(
now returns an unsigned integer value, not a signed integer value.expr
)For
AUTO_INCREMENT
columns, negative values are no longer supported.
(Bug #20964, Bug #11745891)
-
Incompatible Change: Connection ID (thread ID) values greater than 32 bits can occur on some systems (such as busy or long-running 64-bit systems), causing these problems:
Connection IDs written to the general query log and slow query log were incorrect. This was true for logging to both files and tables.
The
CONNECTION_ID()
function could return a value with a data type too small for values larger than 32 bits.The
mysql_thread_id()
andmysql_kill()
C API functions did not handle ID values larger than 32 bits. This could result in killing the wrong thread; for example, if you invokedmysql_kill(mysql_thread_id())
.
Connection IDs now are permitted to be 64-bit values when the server supports them (when built with 64-bit data types), which has these effects:
-
Connection IDs are logged correctly to the general query log and slow query log.
NoteThis change involves a modification to the log tables, so after upgrading to this release, you must run mysql_upgrade and restart the server.
CONNECTION_ID()
returns a data type appropriate for values larger than 32 bits.-
mysql_thread_id()
is unchanged; the client/server protocal has only 4 bytes for the ID value. This function returns an incorrect (truncated) value for connection IDs larger than 32 bits and should be avoided.mysql_kill()
still cannot handle values larger than 32 bits, but to guard against killing the wrong thread now returns an error in these cases:If given an ID larger than 32 bits,
mysql_kill()
returns aCR_INVALID_CONN_HANDLE
error.After the server's internal thread ID counter reaches a value larger than 32 bits, it returns an
ER_DATA_OUT_OF_RANGE
error for anymysql_kill()
invocation andmysql_kill()
fails.
To avoid problems with
mysql_thread_id()
andmysql_kill()
, do not use them. To get the connection ID, execute aSELECT CONNECTION_ID()
query and retrieve the result. To kill a thread, execute aKILL
statement.
(Bug #19806, Bug #11745768, Bug #65715, Bug #14236124, Bug #44728, Bug #11753308)
Important Change; InnoDB: A DML statement using the index merge access method could lock many rows from the table, even when those rows were not part of the final result set. This fix reduces the excessive locking by releasing the locks of unmatched rows. This optimization affects only transactions with isolation level equal to or less strict than
READ COMMITTED
; it does not apply to transactions usingREPEATABLE READ
orSERIALIZABLE
isolation level. (Bug #14226171)-
Important Change; Replication: Statements involving the Performance Schema tables should not be written to the binary log, because the content of these tables is applicable only to a given MySQL Server instance, and may differ greatly between different servers in a replication topology. The database administrator should be able to configure (
INSERT
,UPDATE
, orDELETE
) or flush (TRUNCATE TABLE
) performance schema tables on a single server without affecting others. However, when using replication with GTIDs enabled (see Replication with Global Transaction Identifiers), warnings about unsafe statements updating Performance Schema tables were elevated to errors, preventing the use ofperformance_schema
and GTIDs together.Similar problems were encountered with replication and system logging tables when GTIDs were enabled.
This fix introduces the concept of a nonreplicated or local table. Now when MySQL replication encounters a table that is marked as local, updates to this table are ignored.
This fix defines as local the following tables, which are no longer replicated:
All tables in the
performance_schema
databasemysql.general_log
mysql.slow_log
mysql.slave_relay_log_info
mysql.slave_master_info
mysql.slave_worker_info
Before this fix, statements using the
performance_schema
and other tables just listed were handled by being marked as unsafe for replication, which caused warnings during execution; the statements were nonetheless written to the binary log, regardless of the logging format in effect.Existing replication behavior for tables in the
INFORMATION_SCHEMA
database is not changed by this fix.For more information, see MySQL Performance Schema. See also MySQL Server Logs, and Replication Metadata Repositories. For information about general and slow query log tables, see Selecting General Query Log and Slow Query Log Output Destinations. (Bug #14741537)
Important Change; Replication: Because running the server with GTIDs enabled prevented changes to nontransactional tables, programs such as mysql_upgrade and mysql_install_db were unable to operate on system tables that used the MyISAM storage engine and therefore could not function correctly. Now, when running with
--enforce-gtid-consistency
(required whenever--gtid-mode=ON
), the server allows single statements on nontransactional tables. (Bug #14722659)Important Change; Replication: Formerly, the value of the
Seconds_Behind_Master
column in the output ofSHOW SLAVE STATUS
was always set toNULL
whenever the SQL thread or the I/O thread was stopped. Now, this column is set toNULL
only if the SQL thread is not running, or if the I/O thread is not running following a check to determine whether or not the SQL thread has processed all of the relay log. (If the SQL thread has finished processing and the I/O thread is running,Seconds_Behind_Master
is 0.) (Bug #12946333)Performance; InnoDB: The timing values for low-level
InnoDB
read operations were adjusted for better performance with fast storage devices, such as SSD. This enhancement primarily affects read operations forBLOB
columns in compressed tables. (Bug #13702112, Bug #64258)InnoDB; Partitioning: Previously, when attempting to optimize one or more partitions of a partitioned table that used a storage engine that does not support partition-level
OPTIMIZE
, such asInnoDB
, MySQL reported Table does not support optimize, doing recreate + analyze instead, then re-created the entire table, but did not actually analyze it. Now in such cases, the warning message is, Table does not support optimize on partitions. All partitions will be rebuilt and analyzed. In addition, the entire table is analyzed after first being rebuilt. (Bug #11751825, Bug #42822)-
InnoDB: The server could halt with an error when two kinds of operations happened simultaneously:
A
ROLLBACK
of an inserted row that contained off-page columns.An online DDL operation involving a table of
ROW_FORMAT=DYNAMIC
orROW_FORMAT=COMPRESSED
(that is, using the Barracuda file format) that rebuilt the table. For example,ADD/DROP COLUMN
,ADD PRIMARY KEY
, changeROW_FORMAT
.
(Bug #14842014)
-
InnoDB: If the server crashed while rows were inserted into a table with a
FULLTEXT
index but before the transaction was committed, an error could occur during the next startup:InnoDB: Assertion failure in thread thread_num in file dict0dict.cc line 1019
(Bug #14826779)
InnoDB: The server could halt with an error when accessing an
InnoDB
table containing aFULLTEXT
index through theHANDLER
statement. (Bug #14788710)InnoDB: A timeout error could occur on Windows systems when doing
ALTER TABLE
statements with theDISCARD TABLESPACE
orIMPORT TABLESPACE
clauses, due to a temporary tablespace file remaining in the file system. (Bug #14776799)InnoDB:
InnoDB
tables withFULLTEXT
indexes could allocate memory for thread handles that was never released, possibly leading to resource issues on Windows systems. (Bug #14759163)-
InnoDB: The server could halt with an assertion error for an
ANALYZE TABLE
operation, depending on the structure of the table and its indexes:InnoDB: Assertion failure in thread thread_num in file dict0dict.ic line 447 InnoDB: Failing assertion: pos < table->n_def
(Bug #14755452)
InnoDB: During an online DDL operation that copies the table, the secondary index of the table could become corrupted. (Bug #14753701)
InnoDB: An online DDL operation for an
InnoDB
table incorrectly reported an empty value (''
) instead of the correct key value when it reported a duplicate key error for a unique index using an index prefix. (Bug #14729221)-
InnoDB: If the server crashed after an online DDL
CREATE INDEX
operation, an error could occur while rolling back incomplete transactions on the next startup:InnoDB: error in sec index entry del undo in ... InnoDB: Assertion failure in thread thread_num in file row0umod.cc line 559
(Bug #14707452)
InnoDB: This fix improves the error handling when an
ALTER TABLE
operation adds a column beyond the maximum number allowed for anInnoDB
table. It also raises the maximum number of columns for anInnoDB
table from 1000 to 1020. (Bug #14705287)InnoDB: This fix makes MySQL more responsive to
KILL QUERY
statements when the query is accessing anInnoDB
table. (Bug #14704286)InnoDB: If the server crashed at a precise moment during an
ALTER TABLE
operation that rebuilt the clustered index for anInnoDB
table, the original table could be inaccessible afterward. An example of such an operation isALTER TABLE ... ADD PRIMARY KEY
The fix preserves the original table if the server halts during this operation. You might still need to rename the.ibd
file manually to restore the original table contents: in MySQL 5.6 and higher, rename from#sql-ib$
tonew_table_id
.ibd
within the database directory; prior to MySQL 5.6, the temporary file to rename istable_name
.ibd
ortable_name
#1#2
. (Bug #14669848)InnoDB: During an online DDL operation that rebuilt the table, a
CHECK TABLE
statement could report a count mismatch for all secondary indexes. (Bug #14606472)InnoDB: After a
FULLTEXT
index was created and dropped from anInnoDB
table, furtherALTER TABLE
operations to add, drop, and rename columns could cause a serious error. Regression of bug #13972248. (Bug #14504337)InnoDB: If an
ALTER TABLE
statement failed while attempting to create aFULLTEXT
index for anInnoDB
table, the server could halt with an assertion error while dropping the incomplete index. (Bug #14504174)-
InnoDB: During shutdown, with the
innodb_purge_threads
configuration option set greater than 1, the server could halt prematurely with this error:mysqld got signal 11
A workaround was to increase
innodb_log_file_size
and setinnodb_purge_threads=1
. The fix was backported to MySQL 5.5 and 5.1, although those versions do not have theinnodb_purge_threads
configuration option so the error was unlikely to occur. (Bug #14234028) -
InnoDB: The server could halt with an error under some combinations of concurrent operations:
InnoDB: unknown error code 20
This issue originated during the 5.6 development cycle. It affected only transactions using the
READ COMMITTED
andREAD UNCOMMITTED
isolation levels. (Bug #13641662, Bug #12424846) -
InnoDB: This fix improves the error message when a foreign key constraint cannot be created. Instead of referring to an inability to create a table with an auto-generated name, the message clearly states the error:
ERROR 1215 (HY000): Cannot add foreign key constraint
Issuing a subsequent
SHOW WARNINGS
statement provides additional detail about any secondary indexes that are required. (Bug #11745444, Bug #15324) Replication: If a table to be replicated had a
FULLTEXT
index, this index was not ruled out when selecting the type of scan to be used in finding the next row, even though it cannot be used to find the correct one. The row applier subsequently tried unsuccessfully to employ an index scan, causing replication to fail. Now in such cases, indexes which do not provide for sequential access (such asFULLTEXT
) are not considered when determining whether to use a table, index, or hash scan for this purpose. (Bug #14843764)Replication: Given a stored routine
R
in which theGTID_SUBTRACT()
function was invoked: OnceGTID_SUBTRACT()
returnedNULL
when called insideR
, it continued to returnNULL
every time it was called withinR
, for the remainder of the client session. (Bug #14838575)Replication: When using the GTID-aware master-slave protocol, the slave I/O thread used the wrong position. When using GTIDs, the position is not normally used, but as a special case, the position was used in addition to the GTID when the slave reconnected to the same master (even though this was not necessary). This problem is fixed by making the GTID-aware master-slave protocol not use positions at all any longer. (Bug #14828028)
-
Replication: MySQL Enterprise Backup, mysqldump, and mysqlhotcopy could not be used with a GTID-enabled MySQL Server, because they were unable to restore the server's GTID state and so could not restore from any point in the binary log other than the very beginning.
As part of the fix for this problem, the
gtid_purged
system variable (formerly namedgtid_lost
) is no longer read-only; now it is possible to add GTIDs to it whengtid_executed
(formerlygtid_done
) is empty. (Bug #14787808) Replication: Restarting replication after the first binary log file was purged resulted in the error Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.' This led GTID-based replication to fail. (Bug #14756691)
Solaris: Installation using Solaris packages ran mysql_install_db during upgrade operations (this should occur only for new installations). (Bug #14747671, Bug #16534721)
mysql_install_db failed to honor the
--user
option. (Bug #15866735)The optimizer could allocate insufficient memory when determining subquery execution strategies, causing the server to exit. (Bug #14846866)
The optimizer could raise an assertion when evaluating a range test against an
IS NOT NULL
condition. (Bug #14843705)init_io_cache()
usedmemset()
to clear a mutex but passed the wrong mutex size. (Bug #14838882)Creating an
InnoDB
table with aFULLTEXT
index could encounter a serious error if the table name contained nonalphanumeric characters. (Bug #14835178, Bug #16036699)Out-of-bounds reads could occur within
filename_to_tablename()
. (Bug #14834378)-
When a backup is taken using mysqldump on a server with global transaction IDs (GTIDs) enabled, the dump file did not contain any GTID information. This eventually results in replicating the transactions from the beginning of history when the backup is used to bring up a slave.
To enable control over GTID information written to the dump file, mysqldump now has a
--set-gtid-purged
option that indicates whether to add aSET
@@GLOBAL.
gtid_purged
statement to the output.The following table shows the permitted option values. The default value is
AUTO
.Value Meaning OFF
Add no SET
statement to the output.ON
Add a SET
statement to the output. An error occurs if GTIDs are not enabled on the server.AUTO
Add a SET
statement to the output if GTIDs are enabled on the server.(Bug #14832472)
With
LOCK TABLES
in effect,CREATE TABLE IF NOT EXISTS ... LIKE
could raise an assertion. (Bug #14788976)An assertion could be raised executing
INSERT
,UPDATE
, orDELETE
after implicitly starting aREAD ONLY
transaction inLOCK TABLES
mode. (Bug #14788540)A query with a union and a join could crash the parser. (Bug #14786792, Bug #16076289)
Attempting to read a
utf16
file withLOAD DATA
raised an assertion. (Bug #14786470)The automatic key generation part of derived table handling did not handle properly columns specified as part of the
VALUES()
clause and caused an assertion to be raised. (Bug #14786324)Invalid memory reads could occur for queries that selected from a zero-length table name. (Bug #14780820)
SHOW PROCESSLIST
output was not sorted inId
order. (Bug #14771006)For some
SELECT
statements,EXPLAIN
could cause the server to exit. (Bug #14761894)Attempting to create an auto-increment column in an
InnoDB
table with aNULL
type attribute could cause a serious error. (Bug #14758479)A memory leak occurred for attempts to use
ALTER TABLE
to set a default value for a tiny, medium, or longBLOB
orTEXT
column. (Bug #14756206)An assertion was raised if
ALTER TABLE
was used to rename a column to same name as an existing column while also reordering the renamed column usingAFTER
orFIRST
. (Bug #14756089)An assertion could be raised if semijoin materialization was used to evaluate a
NOT IN
subquery. (Bug #14751858)After issuing
ALTER TABLE ... DISCARD TABLESPACE
, an online DDL operation for the same table could fail on Windows systems with an error:Got error 11 from storage engine
. AnALTER TABLE
statement with theALGORITHM=INPLACE
clause could also create an empty .ibd file, making the tablespace no longer “discarded”. (Bug #14735917)For some continuation handler nestings, continuation could occur at the wrong location. (Bug #14724836)
Starting the server with
bind_address
and then settinghost_cache_size
to 0 could result in the server stopping for certain kinds of client connections. (Bug #14689561)For
UPDATE
statements,EXPLAIN
showed the total key length in thekey_len
column rather than the length of the used key parts. (Bug #14682438)With index condition pushdown enabled, the optimizer could produce incorrect results for derived tables. (Bug #14640176)
SHOW PROFILE
could be used to cause excessive server memory consumption. (Bug #14629232)The optimizer could incorrectly use a nonspatial index to optimize spatial operations, causing an assertion to be raised. (Bug #14600994)
-
Several problems with mysql_config_editor were fixed:
There was no error message for write errors to the configuration file.
The
--all
option is not supported for theremove
command, but there was no warning message for attempts to useremove --all
.The
--all
option is not supported for theset
command, but there was no warning message for attempts to useset --all
.
In addition, the
--user
,--password
, and--host
options now are supported for theremove
command. When present, theremove
command removes only the requested values from the login path. If none of them is given,remove
removes the entireclient
login path. For example, this command removes only theuser
value from theclient
login path rather than the entireclient
login path:mysql_config_editor remove --login-path=client --user
(Bug #14505672, Bug #14545989, Bug #14545999)
A
LIKE
pattern with too many'%'
wildcards could cause a segmentation fault. (Bug #14303860)-
Previously, the
events_statements_summary_by_digest
Performance Schema table was a summary grouped by theDIGEST
column alone. Now this table contains aSCHEMA_NAME
column and the digest summary is grouped by theSCHEMA_NAME
andDIGEST
columns.If you upgrade to this MySQL release from an earlier version, you must run mysql_upgrade (and restart the server) to incorporate this change into the
performance_schema
database. (Bug #14075527) Query rewriting to scrub passwords for logging was done even if none of the associated logs were enabled. Also,
CREATE SERVER
andALTER SERVER
are now rewritten as necessary. (Bug #14073554)CREATE TABLE ... SELECT
could create a table with a column of typeNULL
, which when accessed caused a server exit. (Bug #14021323, Bug #23280699)CHECK TABLE
andREPAIR TABLE
could crash if aMyISAM
table had a corrupt key (.MYI
) file. Now the server produces an error. (Bug #13556441)CHECK TABLE
andREPAIR TABLE
could crash if aMyISAM
table had a corrupt key (.MYI
) file. Now the server produces an error. (Bug #13556107, Bug #13556000)-
A “buffer too small” error message from the myisamchk command referred to the
myisam_sort_buffer_size
configuration option, when it should have referred tosort_buffer_size
.myisamchk now has a
myisam_sort_buffer_size
variable available as an alternative name tosort_buffer_size
.myisam_sort_buffer_size
is preferable tosort_buffer_size
because its name corresponds to themyisam_sort_buffer_size
server system variable that has a similar meaning.sort_buffer_size
should be considered deprecated. (Bug #11754894, Bug #46578) The
host_cache
Performance Schema table displayed some lines multiple times. This was not an issue with the host cache itself, only with the table that provides information about the cache contents. (Bug #67236, Bug #14764890)On OS X, reinitializing the query cache could cause the server to exit. Thanks to Davi Arnaut for the patch. (Bug #67156, Bug #14741880)
The server failed to use the query cache for queries in which a database or table name contained special characters and the table storage engine was
InnoDB
. (Bug #64821, Bug #13919851)mysqld_safe ignored the value of the
UMASK
environment variable, leading to behavior different from mysqld with respect to the access mode of created files. Now mysqld_safe (and mysqld_multi) attempt to approximate the same behavior as mysqld. (Bug #57406, Bug #11764559)For dumps of the
mysql
database, mysqldump skipped theevent
table unless the--events
option was given. This no longer occurs. To skip theevent
table if that is desired, use the--ignore-table
option instead (Bug #55587, Bug #11762933)For
MEMORY
tables withHASH
indexes,DELETE
sometimes failed to delete all applicable rows. (Bug #51763, Bug #11759445)On OS X,
KILL
could sometimes be unreliable. (Bug #37780, Bug #11748945)