MySQL now supports pluggable authentication, such that the server uses plugins to authenticate incoming client connections. Client programs load an authentication plugin that interacts properly with the corresponding server plugin.
Pluggable authentication enables two important capabilities, external authentication and proxy users:
Pluggable authentication makes it possible for clients to connect to the MySQL server with credentials that are appropriate for authentication methods other than native authentication based on passwords stored in the
mysql.usertable. For example, plugins can be created to use external authentication methods such as PAM, Windows login IDs, LDAP, or Kerberos.
If a user is permitted to connect, an authentication plugin can return to the server a user name different from the name of the connecting user, to indicate that the connecting user is a proxy for another user. While the connection lasts, the proxy user is treated, for purposes of access control, as having the privileges of a different user. In effect, one user impersonates another.
Pluggable authentication entails these changes:
mysql.usertable, there are new columns that specify plugin information. The
plugincolumn, if nonempty, indicates which plugin authenticates connections for an account. The
authentication_stringcolumn is a string that the server passes to the plugin for connections by clients that authenticate using the plugin.
mysql_options()C API function, there are new
MYSQL_PLUGIN_DIRoptions that enable client programs to load authentication plugins.
For the mysql client, there are new
--plugin-diroptions for specifying which authentication plugin and plugin directory to use. These options will be added to other clients in future releases.
For the mysqltest client, there is a new
--plugin-diroption for specifying which plugin directory to use, and a new
connect()command argument to specify an authentication plugin.
For the server plugin API, there is a new
A new client plugin API enables client programs to manage plugins.
The native authentication methods previously supported in MySQL have been reimplemented as plugins. These methods provide against the current password format and pre-MySQL 4.1.1 format that uses shorter password hash values. This change reimplements the native methods as plugins that cannot be unloaded. Existing clients authenticate as before with no changes needed. In particular, starting the server with the
--secure-authoption still prevents clients that have pre-4.1.1 password hashes from connecting, and
--skip-grant-tablesstill disables all password checking.
Proxy user capability entails these changes:
Due to these changes, the server requires that a new grant table,
proxies_priv, be present in the
mysqldatabase. If you are upgrading to MySQL 5.5.7 from a previous MySQL release rather than performing a new installation, the server will find that this table is missing and exit during startup with the following message:
Table 'mysql.proxies_priv' doesn't exist
To create the
proxies_privtable, start the server with the
--skip-grant-tablesoption to cause it to skip the normal grant table checks, then run mysql_upgrade. For example:
shell> mysqld --skip-grant-tables & shell> mysql_upgrade
Then stop the server and restart it normally.
You can specify other options on the mysqld command line if necessary. Alternatively, if your installation is configured so that the server normally reads options from an option file, use the
--defaults-fileoption to specify the file (enter each command on a single line):
shell> mysqld --defaults-file=/usr/local/mysql/etc/my.cnf --skip-grant-tables & shell> mysql_upgrade
--skip-grant-tablesoption, the server does no password or privilege checking, so any client can connect and effectively have all privileges. For additional security, use the
--skip-networkingoption as well to prevent remote clients from connecting.Note
The upgrade problem just described is fixed in MySQL 5.5.8. The server treats a missing
proxies_privtable as equivalent to an empty table.
For additional information, consult these references:
Information about pluggable authentication, including installation and usage instructions: Pluggable Authentication.
Information about proxy users: Proxy Users.
Information about the server and client plugin API: Writing Plugins.
Information about the C API functions for managing client plugins: See C API Client Plugin Functions.
Information about current restrictions on the use of pluggable authentication, including which connectors support which plugins: See Restrictions on Pluggable Authentication. Third-party connector developers should read that section to determine the extent to which a connector can take advantage of pluggable authentication capabilities and what steps to take to become more compliant.
MySQL releases now are built using CMake rather than the GNU autotools. Accordingly, the instructions for installing MySQL from source have been updated to discuss how to build MySQL using CMake. See Installing MySQL from Source. If you are familiar with autotools but not CMake, you might find these transition instructions helpful: Autotools to CMake Transition Guide
The build process is now similar enough on all platforms, including Windows, that there are no longer sections dedicated to notes for specific platforms.
The default installation layout when compiling from source now matches that used for binary distributions. You will notice these differences for installations from source distributions:
mysqld is installed in
mysql_install_db is installed in
The data directory is
The make_binary_distribution and
make_win_bin_distscripts are now obsolete. To create a binary distribution, use make package.
Incompatible Change: Previously, if you flushed the logs using
FLUSH LOGSor mysqladmin flush-logs and mysqld was writing the error log to a file (for example, if it was started with the
--log-erroroption), it renamed the current log file with the suffix
-old, then created a new empty log file. This had the problem that a second log-flushing operation thus caused the original error log file to be lost unless you saved it under a different name. For example, you could use the following commands to save the file:
shell> mysqladmin flush-logs shell> mv host_name.err-old backup-directory
To avoid the preceding file-loss problem, renaming no longer occurs. The server merely closes and reopens the log file. To rename the file, you can do so manually before flushing. Then flushing the logs reopens a new file with the original file name. For example, you can rename the file and create a new one using the following commands:
shell> mv host_name.err host_name.err-old shell> mysqladmin flush-logs shell> mv host_name.err-old backup-directory
References: See also: Bug #56821.
The unused and undocumented
thread_pool_sizesystem variable was removed. (Bug #57338)
pstacklibrary was nonfunctional and has been removed, along with the
--with-pstackoption for configure and the
--enable-pstackoption for mysqld. (Bug #57210)
Added a new
Waiting for query cache lock. This indicates that a session is waiting to take the query cache lock while it performs some query cache operation. (Bug #56822)
A new status variable,
Handler_read_last, displays the number of requests to read the last key in an index. With
ORDER BY, the server issues a first-key request followed by several next-key requests, whereas with
ORDER BY DESC, the server issues a last-key request followed by several previous-key requests. (Bug #52312)
Previously, the server supported values of
--option format for controlling plugin loading using an option named after the plugin. Such options now support a
FORCE_PLUS_PERMANENTvalue. This value is like
FORCE, but in addition prevents the plugin from being unloaded at runtime. If a user attempts to do so with
UNINSTALL PLUGIN, an error occurs. See Installing and Uninstalling Plugins.
In addition, the
INFORMATION_SCHEMA.PLUGINStable now has a
LOAD_OPTIONcolumn that indicates the plugin loading value (
FORCE_PLUS_PERMANENT). See The INFORMATION_SCHEMA PLUGINS Table.
Security Fix; Incompatible Change; InnoDB: Issuing
TRUNCATE TABLEand examining the same table's information in the
INFORMATION_SCHEMAdatabase at the same time could cause a crash in the debug version of the server.
As a result of this change,
InnoDBalways uses the fast truncation technique, equivalent to
CREATE TABLE. It no longer performs a row-by-row delete for tables with parent-child foreign key relationships.
TRUNCATE TABLEreturns an error for such tables. Modify your SQL to issue
DELETE FROMfor such tables instead. (Bug #54678)
Security Fix: The server crashed for assignment of values of types other than
Geometryto items of type
MultiSurface). Now the server checks the value type and fails with
bad geometry valueif it detects incorrect parameters. (Bug #55531)
EXPLAIN EXTENDEDcaused a server crash with some prepared statements. (Bug #54494)
Security Fix: The
PolyFromWKB()function could crash the server when improper WKB data was passed to the function. (Bug #51875, Bug #11759554)
Incompatible Change; Replication: The behavior of
INSERT DELAYEDstatements when using statement-based replication has changed as follows:
INSERT DELAYEDcontinues to be executed as
DELAYEDoption). The behavior of
INSERT DELAYEDremains unchanged when using
INSERT DELAYEDgenerates no warnings, is executed as
INSERT DELAYED, and is logged using the row-based format.
This change also affects
INSERT DELAYEDis no longer considered unsafe. Now, when the logging format is
MIXED, no switch to row-based logging occurs. This means that the statement is logged as a simple
INSERT(that is, without the
DELAYEDoption), using the statement-based logging format. (Bug #54579, Bug #11762035)
References: See also: Bug #56678, Bug #11763907, Bug #57666. This issue is a regression of: Bug #39934, Bug #11749859.
HANDLER ... READstatements that invoke stored functions can cause replication errors. Such statements are now disallowed and result in an
ER_NOT_SUPPORTED_YETerror. (Bug #54920)
Important Change; InnoDB: The server could crash with an assertion, possibly leading to data corruption, while updating the primary key of an
BLOBor other columns requiring off-page storage. This fix applies to the
InnoDBPlugin in MySQL 5.1, and to
InnoDB1.1 in MySQL 5.5. (Bug #55543)
Performance; InnoDB: The master
InnoDBbackground thread could sometimes cause transient performance drops due to excessive flushing of modified pages. (Bug #56933)
InnoDB; Replication: If the master had
innodb_strict_mode=OFF), or both, certain
CREATE TABLEoptions, such as
KEY_BLOCK_SIZE, were ignored. This could permit the master to avoid raising
However, the ignored
CREATE TABLEoptions were still written into the binary log, so that, if the slave had
innodb_file_format=Barracuda, it could encounter an
ER_TOO_BIG_ROWSIZEerror while executing the record from the log, causing the slave SQL thread to abort and replication to fail.
In the case where the master was running MySQL 5.1 and the slave was MySQL 5.5 (or later), the failure occurred when both master and slave were running with default values for
innodb_file_format. This could cause problems during upgrades.
To address this issue, the default values for
innodb_file_formatare reverted to the MySQL 5.1 default values—that is,
Antelope, respectively. (Bug #56318, Bug #11763590)
InnoDBincorrectly reported an error when a cascading foreign key constraint deleted more than 250 rows. (Bug #57255)
InnoDB: If the server crashed during an
ALTER TABLEoperation on an
InnoDBtable, examining the table through
SHOW CREATE TABLEor querying the
INFORMATION_SCHEMAtables could cause the server to stop with an assertion error. (Bug #56982)
InnoDB: The output from the
SHOW ENGINE INNODB STATUScommand can now be up to 1MB. Formerly, it was truncated at 64KB. Monitoring applications that parse that output can check whether it exceeds this new, larger limit by testing the
Innodb_truncated_status_writesstatus variable. (Bug #56922)
InnoDB: For debug builds, a
SELECT ... FOR UPDATEstatement affecting a range of rows in an
InnoDBtable could cause a server crash. (Bug #56716)
InnoDB: Improved the performance of
InnoDBtables, when only non-indexed columns are changed. (Bug #56340)
InnoDB: When MySQL was restarted after a crash with the option
innodb_force_recovery=6, certain queries against
InnoDBtables could fail, depending on
Usually in such a disaster recovery situation, you dump the entire table using a query without these clauses. During advanced troubleshooting, you might use queries with these clauses to diagnose the position of the corrupted data, or to recover data following the corrupted part. (Bug #55832)
CHECK TABLEcommand could cause a time-consuming verification of the
InnoDBadaptive hash index memory structure. Now this extra checking is only performed in binaries built for debugging. (Bug #55716)
InnoDB: A heavy workload with a large number of threads could cause a crash in the debug version of the server. (Bug #55699)
InnoDB: The server could crash on shutdown, if started with
--innodb-use-system-malloc=0. (Bug #55627)
InnoDB: If the server crashed during a
RENAME TABLEoperation on an
InnoDBtable, subsequent crash recovery could fail. This problem could also affect an
ALTER TABLEstatement that caused a rename operation internally. (Bug #55027)
InnoDB: Setting the
PACK_KEYS=0table option for an
InnoDBtable prevented new indexes from being added to the table. (Bug #54606)
InnoDB: The server could crash when opening an
InnoDBtable linked through foreign keys to a long chain of child tables. (Bug #54582, Bug #11762038)
InnoDB: Changed the locking mechanism for the
InnoDBdata dictionary during
ROLLBACKoperations, to improve concurrency for
REPLACEstatements. (Bug #54538)
InnoDB: With multiple buffer pools enabled,
InnoDBcould flush more data from the buffer pool than necessary, causing extra I/O overhead. (Bug #54346)
InnoDBtransactions could be incorrectly committed during recovery, rather than rolled back, if the server crashed and was restarted after performing
ALTER TABLE ... ADD PRIMARY KEYon an
InnoDBtable, or some other operation that involves copying the entire table. (Bug #53756)
InnoDBstartup messages now include the start and end times for buffer pool initialization, and the total buffer pool size. (Bug #48026)
ALTER TABLEstatement acting on table partitions that failed while the affected table was locked could cause the server to crash. (Bug #56172)
UPDATEstatements involving a partitioned
MyISAMtable could cause this table to become corrupted. Not all tables affected by the
UPDATEneeded to be partitioned for this issue to be observed. (Bug #55458)
EXPLAIN PARTITIONSreturned bad estimates for range queries on partitioned
MyISAMtables. In addition, values in the
EXPLAIN PARTITIONSoutput did not take partition pruning into account. (Bug #53806, Bug #46754)
SET PASSWORDcaused failure of row-based replication between a MySQL 5.1 master and a MySQL 5.5 slave.
This fix makes it possible to replicate
SET PASSWORDcorrectly, using row-based replication between a master running MySQL 5.1.53 or a later MySQL 5.1 release to a slave running MySQL 5.5.7 or a later MySQL 5.5 release. (Bug #57098)
References: See also: Bug #55452, Bug #57357.
Replication: Prepared multiple-row
INSERT DELAYEDstatements were written to the binary log without
DELAYED. (Bug #56678, Bug #11763907)
References: This issue is a regression of: Bug #54579, Bug #11762035.
Replication: Backticks used to enclose identifiers for savepoints were not preserved in the binary log, which could lead to replication failure when the identifier, stripped of backticks, could be misinterpreted, causing a syntax or other error.
This could cause problems with MySQL application programs making use of generated savepoint IDs. If, for instance,
java.sql.Connection.setSavepoint()is called without any parameters, Connector/J automatically generates a savepoint identifier consisting of a string of hexadecimal digits
Fencased in backtick (
`) characters. If such an ID took the form
Nrepresents a string of the decimal digits
eis a literal uppercase or lowercase “E” character). Removing the backticks when writing the identifier into the binary log left behind a substring which the slave MySQL server tried to interpret as a floating point number, rather than as an identifier. The resulting syntax error caused loss of replication. (Bug #55961)
References: See also: Bug #55962.
Replication: When a slave tried to execute a transaction larger than the slave's value for
max_binlog_cache_size, it crashed. This was caused by an assertion that the server should roll back only the statement but not the entire transaction when the error
ER_TRANS_CACHE_FULLoccurred. However, the slave SQL thread always rolled back the entire transaction whenever any error occurred, regardless of the type of error. (Bug #55375)
Replication: The error message for
ER_SLAVE_HEARTBEAT_VALUE_OUT_OF_RANGEwas hard coded in English in
sql_yacc.yy, so that it could not be translated in
errmsg.txtfor other languages.
Additionally, this same error message was used for three separate error conditions:
When the heartbeat period exceeded the value of
When the heartbeat period was nonnegative but shorter than 1 millisecond.
When the value for the heartbeat period was either negative or greater than the maximum permitted.
These issues have been addressed as follows:
By using three distinct error messages for each of the conditions listed previously.
By moving the sources for these error messages into the
errmsg-utf8.txtfile to facilitate translations into languages other than English.
Microsoft Windows: When mysqld was started as a service on Windows and mysqld was writing the error log to a file (for example, if it was started with the
--log-erroroption), the server reassigned the file descriptors of the
stderrstreams to the file descriptor of the log file. On Windows, if
stderris not associated with an output stream, the file descriptor returns a negative value. Previously, this caused the file descriptor reassignment to fail and the server to abort. To avoid this problem on Windows, the server now first assigns the
stderrstreams to the log file stream by opening this file. This causes the
stderrfile descriptors to be nonzero and the server can successfully reassign them to the file descriptor of the log file. (Bug #56821)
References: This issue is a regression of: Bug #29751.
Solaris: On Solaris with gcc 3.4.6,
ha_example.sowas built with DTrace support even if the server was not, causing plugin loading problems. (Bug #55966)
Solaris: A bad
fill_schema_schemata()caused server crashes on Solaris. (Bug #54478)
mysqldsegfaulted if compiled with gcc 4.6. (Bug #61509, Bug #14548064)
A buffer overrun could occur when formatting
DBL_MAXnumbers. (Bug #57209)
COALESCE()in MySQL 5.5 could return a result different from MySQL 5.1 for some arguments. (Bug #57095)
SUBTIME()expressions could return incorrect results. (Bug #57039)
The server could crash inside
memcpy()when reading certain Performance Schema tables. (Bug #56761, Bug #58003)
Deadlock could occur for heavily concurrent workloads consisting of a mix of DML, DDL, and
FLUSH TABLESstatements affecting the same set of tables. (Bug #56715, Bug #56404, Bug #56405)
Memory leaks detected by Valgrind were corrected. (Bug #56709)
Performance for certain read-only queries, in particular
point_select, had deteriorated compared to previous versions. (Bug #56585)
It was possible to compile mysqld with Performance Schema support but with a dummy atomic-operations implementation, which caused a server crash. This problem does not affect binary distributions. It is helpful as a safety measure for users who build MySQL from source. (Bug #56521)
XA ENDafter an XA transaction was already ended raised an assertion. (Bug #56448)
References: This issue is a regression of: Bug #38999.
The server crashed if a table maintenance statement such as
REPAIR TABLEwas executed on a
MERGEtable and opening and locking a child table failed. For example, this could happen if a child table did not exist or if a lock timeout happened while waiting for a conflicting metadata lock to disappear.
As a consequence of this bug fix, it is now possible to use
CHECK TABLEfor log tables without producing an error. (Bug #56422, Bug #56494)
Comparison of one
STR_TO_DATE()result with another could return incorrect results. (Bug #56271)
tcmalloclibrary was missing from binary MySQL packages for Linux. (Bug #56267)
INSERT DELAYEDstatement for a
MERGEtable could cause deadlock if it occurred as part of a transaction or under
LOCK TABLES, and there was a concurrent DDL or
LOCK TABLES ... WRITEstatement that tried to lock one of its underlying tables. (Bug #56251)
In debug builds, the server raised an assertion for
DROP DATABASEin installations that had an outdated or corrupted
mysql.proctable. For example, this affected mysql_upgrade when run as part of a MySQL 5.1 to 5.5 upgrade. (Bug #56137)
The ordering for supplementary characters in the
utf32_bincollations was incorrect. (Bug #55980)
Short (single-letter) command-line options did not work. (Bug #55873)
If a query specified a
DATETIMEvalue in a format different from
'YYYY-MM-DD hh:mm:ss', a greater-than-or-equal (
>=) condition matched only greater-than values in an indexed
TIMESTAMPcolumn. (Bug #55779, Bug #50774, Bug #11758558)
If a view was named as the destination table for
CREATE TABLE ... SELECT, the server produced a warning whether or not
IF NOT EXISTSwas used. Now it produces a warning only when
IF NOT EXISTSis used, and an error otherwise. (Bug #55777)
CASEexpressions with a mix of operands in different character sets sometimes returned incorrect results. (Bug #55744)
After the fix for Bug #39653, the shortest available secondary index was used for full table scans. The primary clustered key was used only if no secondary index could be used. However, when the chosen secondary index includes all columns of the table being scanned, it is better to use the primary index because the amount of data to scan is the same but the primary index is clustered. This is now taken into account. (Bug #55656)
References: See also: Bug #39653.
The server entered an infinite loop with high CPU utilization after an error occurred during flushing of the I/O cache. (Bug #55629)
For the Performance Schema, the default number of rwlock classes was increased to 30, and the default number of rwlock and mutex instances was increased to 1 million. These changes were made to account for the volume of data instrumented when the
InnoDBstorage engine is used (because of the
InnoDBbuffer pool). (Bug #55576)
If there was an active
SELECTstatement, an error arising during trigger execution could cause a server crash. (Bug #55421)
The server could crash during shutdown due to a race condition relating to Performance Schema cleanup. (Bug #55105, Bug #56324)
Queries involving predicates of the form
could return incorrect data due to incorrect handling by the range optimizer. (Bug #54802)
UPDATE IGNOREstatement including a subquery that was evaluated using a temporary table, an error transferring the data from the temporary was ignored, causing an assertion to be raised. (Bug #54543)
If one session attempted to drop a database containing a table which another session had opened with
HANDLER, any instance of
CREATE DATABASE, or
DROP DATABASEissued by the latter session produced a deadlock. (Bug #54360)
INFORMATION_SCHEMAplugins with no
deinit()method resulted in a memory leak. (Bug #54253)
Row subqueries producing no rows were not handled as
UNKNOWNvalues in row-comparison expressions. (Bug #54190)
SETUP_INSTRUMENTS.TIMER = 'NO'caused
TIMER_WAITvalues for aggregations to be
NULLrather than 0. (Bug #53874)
max_lengthmetadata value of
MEDIUMBLOBtypes was reported as 1 byte greater than the correct value. (Bug #53296)
If an application using the embedded server called
mysql_library_init()a second time after calling
mysql_library_end()to start and stop the server, the application crashed when reading option files. (Bug #53251)
The fix for Bug #30234 caused the server to reject the
DELETEAccess compatibility syntax for multiple-table
DELETEstatements. (Bug #53034)
References: See also: Bug #30234.
plugin_audit.hinclude files are part of the public API/ABI, but were not tested by the ABI check. (Bug #52821)
An atomic “compare and swap” operation using x86 assembly code (32 bit) could access incorrect data, which would make it work incorrectly and lose the intended atomicity. This in turn caused the MySQL server to work on inconsistent data structures and return incorrect data. That code affected only 32-bit builds; the effect has been observed when icc was used to build binaries. With gcc, no incorrect results have been observed during tests, so this fix is a proactive one. Other compilers do not use this assembly code. (Bug #52419)
LOAD DATA, using a
SETclause to set a column equal to itself caused a server crash. (Bug #51850)
An assertion could be raised by
DELETEon a view that referenced another view which in turn (directly or indirectly) referenced more than one table. (Bug #51099)
In some cases, when the left part of a
NOT INsubquery predicate was a row and contained
NULLvalues, the query result was incorrect. (Bug #51070)
CHECKSUM TABLEfor Performance Schema tables could cause a server crash due to uninitialized memory reads. (Bug #50557)
For some queries, the optimizer produced incorrect results using the Index Merge access method with
InnoDBtables. (Bug #50402)
EXPLAINproduced an incorrect
rowsvalue for queries evaluated using an index scan and that included
GROUP BY, and
ORDER BYon a computed column. (Bug #50394)
mysql_use_result()are not for use with prepared statements and are not intended to be called following
mysql_stmt_execute(), but failed to return an error when invoked that way. (Bug #47485)
REPAIR TABLEon a
MERGEtable caused the server to crash. (Bug #46339)
If the global and session
debugsystem variables had the same value, the debug trace file could be closed twice, leading to freeing already freed memory and a server crash. (Bug #46165)
Many type-punning warnings during compilation were silenced. (Bug #42733, Bug #11751755)
Trailing space removal for
utf32strings was done with non-multibyte-safe code, leading to incorrect result length and assertion failure. (Bug #42511)
A malformed packet sent by the server when the query cache was in use resulted in lost-connection errors. (Bug #42503)
Multiple-statement execution could fail. (Bug #40877)
CREATE TABLEfailed if a column referred to in an index definition and foreign key definition had different lettercases in the two definitions. (Bug #39932)
mysqlcheck behaved differently depending on the order in which options were given on the command line. (Bug #35269)
When invoked to display a help message, mysqld also displayed spurious warning or error messages. (Bug #30025)