MySQL Server now includes Performance Schema, a feature for monitoring server execution at a low level. It is implemented using the PERFORMANCE_SCHEMA storage engine and the performance_schema database. Performance Schema focuses primarily on performance data. This differs from INFORMATION_SCHEMA, which serves for inspection of metadata. For more information, see Chapter 21, MySQL Performance Schema.

Performance Schema support is included in binary MySQL distributions. It is disabled by default. To enable it, start the server with the --performance_schema option.

To create the performance_schema database if you are upgrading from an earlier release, run mysql_upgrade and restart the server. See Section 4.4.7, “mysql_upgrade — Check Tables for MySQL Upgrade”.

This release includes InnoDB 1.0.6. This version is considered of Release Candidate (RC) quality.

Performance: The performance of internal functions that trim multiple spaces from strings when comparing them has been improved. (Bug#14637)

Incompatible Change: CREATE VIEW and DROP VIEW now are prohibited while a LOCK TABLES statement is in effect. (Bug#56571)

Incompatible Change: The following obsolete constructs have been removed. Where alternatives are shown, applications should be updated to use them.

(Bug#48048)

See also Bug#47974, Bug#56408.

Incompatible Change: Aliases for wildcards (as in SELECT t.* AS 'alias' FROM t) are no longer accepted and result in an error. Previously, such aliases were ignored silently. (Bug#27249)

Incompatible Change: Implicit conversion of a number or temporal value to string now produces a value that has a character set and collation determined by the character_set_connection and collation_connection system variables. (These variables commonly are set with SET NAMES. For information about connection character sets, see Section 9.1.4, “Connection Character Sets and Collations”.)

This means that such a conversion results in a character (nonbinary) string (a CHAR, VARCHAR, or LONGTEXT value), except in the case that the connection character set is set to binary. In that case, the conversion result is a binary string (a BINARY, VARBINARY, or LONGBLOB value).

Previously, an implicit conversion always produced a binary string, regardless of the connection character set. Such implicit conversions to string typically occur for functions that are passed numeric or temporal values when string values are more usual, and thus could have effects beyond the type of the converted value. Consider the expression CONCAT(1, 'abc'). The numeric argument 1 was converted to the binary string '1' and the concatenation of that value with the nonbinary string 'abc' produced the binary string '1abc'.

This change in conversion behavior affects several functions that expect string arguments because a numeric or temporal argument converted to a string now results in a character rather than binary string argument:

These functions remain unaffected:

Encryption and compression functions that expect string arguments and previously returned binary strings are affected depending on the content of the return value:

The INET_NTOA() return value contains only ASCII characters, and this function now returns a character string with the connection character set and collation rather than a binary string.

Incompatible Change: Several changes were made to processing of server system variables and command-line options to make their treatment more consistent.

General changes:

Variable-specific changes:

See also Bug#34437, Bug#34635.

Incompatible Change: FLUSH TABLES has a new variant, FLUSH TABLES tbl_list WITH READ LOCK. This variant enables tables to be flushed and locked in a single operation. It provides a workaround for the restriction (due to work done for Bug#989) that FLUSH TABLES is not permitted when there is an active LOCK TABLES ... READ.

See also Bug#42465.

Incompatible Change: The server now includes dtoa, a library for conversion between strings and numbers by David M. Gay. In MySQL, this library provides the basis for improved conversion between string or DECIMAL values and approximate-value (FLOAT/DOUBLE) numbers:

Because the conversions produced by this library differ in some cases from previous results, the potential exists for incompatibilities in applications that rely on previous results. For example, applications that depend on a specific exact result from previous conversions might need adjustment to accommodate additional precision.

For additional information about the properties of dtoa conversions, see Section 11.2, “Type Conversion in Expression Evaluation”.

See also Bug#12860, Bug#21497, Bug#26788, Bug#24541, Bug#34015.

Incompatible Change: The Unicode implementation has been extended to provide support for supplementary characters that lie outside the Basic Multilingual Plane (BMP). Noteworthy features:

In most respects, upgrading to MySQL 5.5 should present few problems with regard to Unicode usage, although there are some potential areas of incompatibility. These are the primary areas of concern:

Consequently, if you want to upgrade tables from utf8 to utf8mb4 to take advantage of supplementary-character support, it may be necessary to change some column or index definitions.

For additional details about the new Unicode character sets and potential incompatibilities, see Section 9.1.10, “Unicode Support”, and Section 9.1.11, “Upgrading from Previous to Current Unicode Support”.

Incompatible Change: Several columns were added to the INFORMATION_SCHEMA.ROUTINES table to provide information about the RETURNS clause data type for stored functions: DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, CHARACTER_SET_NAME, and COLLATION_NAME.

This change produces an incompatibility for applications that depend on column order in the ROUTINES table because the new columns appear between the ROUTINE_TYPE and DTD_IDENTIFIER columns. Such applications may need to be adjusted to account for the new columns.

Important Change: Replication: RESET MASTER and RESET SLAVE now reset the values shown for Last_IO_Error, Last_IO_Errno, Last_SQL_Error, and Last_SQL_Errno in the output of SHOW SLAVE STATUS. (Bug#34654)

See also Bug#44270.

Important Change: The --skip-thread-priority option is now deprecated such that the server won't change the thread priorities by default. Giving threads different priorities might yield marginal improvements in some platforms (where it actually works), but it might instead cause significant degradation depending on the thread count and number of processors. Meddling with the thread priorities is a not a safe bet as it is very dependent on the behavior of the CPU scheduler and system where MySQL is being run. (Bug#35164, Bug#37536)

Cluster Replication: Replication: MySQL Replication now supports attribute promotion and demotion for row-based replication between columns of different but similar types on the master and the slave. For example, it is possible to promote an INT column on the master to a BIGINT column on the slave, and to demote a TEXT column to a VARCHAR column.

The implementation of type demotion distinguishes between lossy and non-lossy type conversions, and their use on the slave can be controlled by setting the slave_type_conversions global server system variable.

For more information, see Row-based replication: attribute promotion and demotion. (Bug#47163, Bug#46584)

Replication: For replication based on row-based and mix-format binary logging, it is now safe to mix transactional and nontransactional statements within a transaction. The nontransactional statements are logged immediately rather than waiting until the transaction ends, ensuring that their results are logged and replicated correctly regardless of the result of the transaction.

mysqltest has a new --max-connections option to set a higher number of maximum permitted server connections than the default 128. This option can also be passed using mysql-test-run.pl. (Bug#51135)

mysql-test-run.pl has a new --portbase option and a corresponding MTR_PORT_BASE environment variable for setting the port range, as an alternative to the existing --build-thread option. (Bug#50182)

SHOW PROFILE CPU has been ported to Windows. Thanks to Alex Budovski for the patch. (Bug#50057)

mysql-test-run.pl has a new --gprof option that runs the server through the gprof profiler, much the same way the currently supported --gcov option runs it through gcov. (Bug#49345)

mysqltest has a new lowercase_result command that converts the output of the next statement to lowercase. This is useful for test cases where the lettercase may vary between platforms. (Bug#48863)

mysqltest has a new remove_files_wildcard command that removes files matching a pattern from a directory. (Bug#39774)

MySQL support for adding collations using LDML specifications did not support the <i> identity rule that indicates one character sorts identically to another. The <i> rule now is supported. (Bug#37129)

For boolean options, the option-processing library now prints additional information in the --help message: If the option is enabled by default, the message says so and indicates that the --skip form of the option disables the option. This affects all compiled MySQL programs that use the library. (Bug#35224)

The use of the SQL_CACHE and SQL_NO_CACHE options in SELECT statements now is checked more restrictively: 1) Previously, both options could be given in the same statement. This is no longer true; only one can be given. 2) Previously, these options could be given in SELECT statements that were not at the top-level. This is no longer true; the options are not permitted in subqueries (including subqueries in the FROM clause, and SELECT statements in unions other than the first SELECT. (Bug#35020)

Added the --auto-vertical-output option to mysql which causes result sets to be displayed vertically if they are too wide for the current window, and using normal tabular format otherwise. (This applies to statements terminated by ; or \G.) (Bug#26780)

TRUNCATE TABLE now is permitted for a table for which a WRITE lock has been acquired with LOCK TABLES. (Bug#20667)

See also Bug#46452.

FLUSH LOGS now takes an optional log_type value so that FLUSH log_type LOGS can be used to flush only a specified log type. These log_type options are permitted:

Thanks to Eric Bergen for the patch to implement this feature. (Bug#14104)

Previously, prepared CALL statements could be used through the C API only for stored procedures that produce at most one result set, and applications could not use placeholders for OUT or INOUT parameters. For prepared CALL statements used using PREPARE and EXECUTE, placeholders could not be used for OUT or INOUT parameters.

For the C API, prepared CALL support now is expanded in the following ways:

For PREPARE and EXECUTE, placeholder support for OUT and INOUT parameters is now available. See Section 12.2.1, “CALL Syntax”. (Bug#11638, Bug#17898)

MySQL now supports IPv6 connections to the local host, using the address ::1. For example:

shell> mysql -h ::1

The address ::1 can be specified in account names in statements such as CREATE USER, GRANT, and REVOKE. For example:

mysql> CREATE USER 'bill'@'::1' IDENTIFIED BY 'secret';
mysql> GRANT SELECT ON mydb.* TO 'bill'@'::1';

The default set of accounts created during MySQL installation now includes an account for 'root'@'::1'.

See Section 5.4.3, “Specifying Account Names”, and Section 2.12.2, “Securing the Initial MySQL Accounts”. (Bug#8836)

Three options were added to mysqldump make it easier to generate a dump from a slave server:

(Bug#8368)

mysqladmin now permits the password value to be omitted following the password command. In this case, mysqladmin prompts for the password value, which enables you to avoid specifying the password on the command line. Omitting the password value should be done only if password is the final command on the mysqladmin command line. Otherwise, the next argument is taken as the password. (Bug#5724)

The optimizer_switch system variable has a new engine_condition_pushdown flag to control whether storage engine condition pushdown optimization is used. The engine_condition_pushdown system variable now is deprecated.

The server now provides a pluggable audit interface that enables information about server operations to be reported to interested parties. Audit plugins may register with the audit interface to receive notification about server operations. When an auditable event occurs within the server, the server determines whether notification is needed. For each registered audit plugin, the server checks the event against those event classes in which the plugin is interested and passes the event to the plugin if there is a match. For more information, see Section 23.2.3.5, “Audit Plugins”.

Some conversions between Japanese character sets are more efficient.

When the server detects MyISAM table corruption, it now writes additional information to the error log, such as the name and line number of the source file, and the list of threads accessing the table. Example: Got an error from thread_id=1, mi_dynrec.c:368. This is useful information to include in bug reports.

The TABLESPACES table has been added to INFORMATION_SCHEMA for tracking tablespace details.

Added the PARAMETERS table to INFORMATION_SCHEMA. The PARAMETERS table provides information about stored procedure and function parameters, and about return values for stored functions.

The maximum length of table comments was extended from 60 to 2048 characters. The maximum length of column comments was extended from 255 to 1024 characters. Index definitions now can include a comment of up to 1024 characters.

Performance: Replication: When writing events to the binary log, transactional events (that is, events that operate on transactional tables) are written to a thread-specific transaction cache, which is then written to the binary log on commit. To handle nontransactional events, there was a lock taken on the binary log (when entering the function MYSQL_BIN_LOG::write()), even when the event was written to the transaction cache instead of the binary log, causing a major bottleneck in replication performance. (Bug#42757)

Security Fix: The server crashed if an account with the CREATE ROUTINE privilege but not the EXECUTE privilege attempted to create a stored procedure. (Bug#44798)

Security Enhancement: When the DATA DIRECTORY or INDEX DIRECTORY clause of a CREATE TABLE statement referred to a subdirectory of the data directory through a symlinked component of the data directory path, it was accepted, when for security reasons it should be rejected. (Bug#39277)

Incompatible Change: Replication: The --binlog_format system variable can no longer be set inside a transaction. In other words, the binary logging format can no longer be changed while a transaction is in progress. (Bug#47863)

Incompatible Change: Replication: Concurrent statements using a stored function and DROP FUNCTION for that function could break statement-based replication.

DDL statements for stored procedures and functions are now prohibited while a LOCK TABLES statement is in effect. (Bug#30977)

See also Bug#57663.

Incompatible Change: For debug builds, wttempts to execute RESET statements within a transaction that had acquired metadata locks led to an assertion failure.

As a result of this bug fix, RESET statements now cause an implicit commit. (Bug#51336)

Incompatible Change: A deadlock occurred for this sequence of events: Session 1 locked a table using LOCK TABLES; Session 2 dropped the database containing the table; Session 1 created any database.

A consequence of this bug fix is that CREATE DATABASE is not permitted within a session that has an active LOCK TABLES statement. (Bug#49988)

Incompatible Change: CREATE TABLE statements (including CREATE TABLE ... LIKE) are now prohibited whenever a LOCK TABLES statement is in effect. (Bug#42546)

Incompatible Change: For application compatibility reasons, when sql_auto_is_null is 1, MySQL converts auto_inc_col IS NULL to auto_inc_col = LAST_INSERT_ID(). However, this was being done regardless of whether the predicate was alone or at the top level. Now it occurs only when it is a single top-level predicate.

In conjunction with this bug fix, the default value of the sql_auto_is_null system variable has been changed from 1 to 0, which may cause incompatibilities with existing applications. (Bug#41371)

Incompatible Change: The parser accepted illegal syntax in a FOREIGN KEY clause:

These illegal syntaxes are now properly rejected. Existing applications that used them will require adjustment. (Bug#34455)

Incompatible Change: The parser accepted an INTO clause in nested SELECT statements, which is invalid because such statements must return their results to the outer context. This syntax is no longer permitted. (Bug#33204)

Incompatible Change: The Locked thread state was equivalent to the Table lock state and has been removed. It no longer appears in SHOW PROCESSLIST output. (Bug#28870)

Incompatible Change: Several changes were made to alias resolution in multiple-table DELETE statements so that it is no longer possible to have inconsistent or ambiguous table aliases.

Statements containing alias constructs that are no longer permitted must be rewritten. (Bug#27525)

See also Bug#30234.

Incompatible Change: DROP TABLE now is permitted only if you have acquired a WRITE lock with LOCK TABLES, or if you hold no locks, or if the table is a TEMPORARY table.

Previously, if other tables were locked, you could drop a table with a read lock or no lock, which could lead to deadlocks between clients. The new stricter behavior means that some usage scenarios will fail when previously they did not. (Bug#25858)

Incompatible Change: If a data definition language (DDL) statement occurred for a table that was being used by another session in an active transaction, statements could be written to the binary log in the wrong order. For example, this could happen if DROP TABLE occurred for a table being used in a transaction. This is now prevented by deferring release of metadata locks on tables used within a transaction until the transaction ends.

This bug fix results in some incompatibilities with previous versions:

(Bug#989, Bug#39675)

Important Change: Replication: For an engine that supported only row-based replication, replication stopped with an error when executing row events.

For information about changes in how the binary logging format is determined in relation to statement type and storage engine logging capabilities, see Section 5.2.4.3, “Mixed Binary Logging Format”.

As part of the fix for this issue, the EXAMPLE storage engine is now changed so that it supports statement-based logging only. Previously, it supported row-based logging only. (Bug#39934)

Important Change: The IPv6 loopback address ::1 was interpeted as a hostname rather than a numeric IP address.

In addition, the IPv6-enabled server on Windows interpeted the hostname localhost as ::1 only, which failed to match the default 'root'@'127.0.0.1' account in the mysql.user privilege table.

(Bug#43006)

See also Bug#38247, Bug#45283, Bug#45584, Bug#45606.

InnoDB Storage Engine: Replication: Column length information generated by InnoDB did not match that generated by MyISAM, which caused invalid metadata to be written to the binary log when trying to replicate BIT columns. (Bug#49618)

InnoDB Storage Engine: SHOW INNODB STATUS could display incorrect information about deadlocks, when the deadlock detection routine stops early (to avoid excessive CPU usage). (Bug#49001)

InnoDB Storage Engine: Concurrent execution of ALTER TABLE for InnoDB table and a transaction that tried to read and then update the table could result in a deadlock between table-level locks and InnoDB row locks, which was detected only after the innodb_lock_wait_timeout timeout occurred. (Bug#37346)

Partitioning: When using a debug build of MySQL, if a query against a partitioned table having an index on one or more DOUBLE columns used that index, the server failed with an assertion. (Bug#45816)

Partitioning: The first time that a query against the INFORMATION_SCHEMA.TABLES table for partitioned tables using the ARCHIVE engine was run, it returned invalid data. If the server had been restarted since such a table had been created, or if the table had never actually been opened, its DATA_LENGTH was reported as 0 bytes. (The second and subsequent attempts to issue the same query returned the expected result.) (Bug#44622)

Partitioning: ALTER TABLE on a partitioned table caused unnecessary deadlocks. (Bug#43867)

See also Bug#46654.

Partitioning: Attempting to drop a partitioned table from one connection while waiting for the completion of an ALTER TABLE that had been issued from a different connection, and that changed the storage engine used by the table, could cause the server to crash. (Bug#42438)

Partitioning: After attempting to create a duplicate index on a partitioned table (and having the attempt fail as expected), a subsequent attempt to create a new index on the table caused the server to hang. (Bug#40181)

Partitioning: When used on a partitioned table, ALTER TABLE produced the wrong error message when the name of a nonexistent storage engine was used in the ENGINE clause. (Bug#35765)

Partitioning: When one user was in the midst of a transaction on a partitioned table, a second user performing an ALTER TABLE on this table caused the server to hang. (Bug#34604)

Partitioning: Portions of the partitioning code were refactored in response to potential regression issues uncovered while working on the fix for Bug#31210. (Bug#32115)

See also Bug#40281.

Replication: When using the row-based or mixed replication format with a debug build of the MySQL server, inserts into columns using the UTF32 character set on the master caused the slave to crash. (Bug#51787)

See also Bug#51716.

Replication: When using the row-based or mixed replication format, column values using the UTF16 character set on the master were padded incorrectly on the slave. (Bug#51716)

See also Bug#51787.

Replication: An issue internal to the code, first seen in Bug#49132 but not completely resolved in the fix for that bug, was removed. This should prevent similar issues to those in the previous bug with binlog_format changes following DDL statements.

For developers working with the MySQL Server code: the public class variable THD::current_stmt_binlog_row_based was supposed to have been removed as part of the fix for Bug#39934, but was still present in the code. If a developer later tried to use this variable, it could cause the previous issues to re-occur, and possibly new ones to arise. The variable has now been removed; the previously added class functions THD::is_current_stmt_binlog_format_row(), THD::set_current_stmt_binlog_format_row(), and THD::clear_current_stmt_binlog_format_row() should be used instead. (Bug#51021)

Replication: Adding an index to a table on the master caused the slave to stop logging slow queries to the slow query log. (Bug#50620)

Replication: If a CHANGE MASTER TO statement set MASTER_HEARTBEAT_PERIOD to 30 or higher, Slave_received_heartbeats did not increase on the slave. This caused the slave to reconnect before the time indicated by slave_net_timeout had elapsed.

This issue affected big-endian 64-bit platforms such as Solaris/SPARC. (Bug#50296)

Replication: The error message given when trying to replicate (using statement-based mode) insertions into an AUTO_INCREMENT column by a stored function or a trigger was improved. (Bug#50192)

Replication: The server could deadlock when FLUSH LOGS was executed concurrently with DML statements. To fix this problem, nontransactional changes are now always flushed before transactional changes. (Bug#50038)

Replication: Metadata for GEOMETRY fields was not properly stored by the slave in its definitions of tables. (Bug#49836)

See also Bug#48776.

Replication: Statement-based replication of user variables having numeric data types did not always work correctly. (Bug#49562)

Replication: When using the semi-synchronous replication plugin on Windows, the wait time calculated when the master was waiting for reply from the slave was incorrect. In addition, when the wait time was less than the current time, the master did not wait for a reply at all.

This issue was caused by the fact that a different internal function was used to get current time by the plugin on Windows as opposed to other platforms, and this function was not correctly implemented. Now the Windows version of the plugin uses the same function as other platforms for this purpose. (Bug#49557)

Replication: Due to a change in the format of the information used by the slave to connect to the master, which could cause to reject connection attempts to older masters by newer slaves. (Bug#49259)

This regression was introduced by Bug#13963.

Replication: When using row-based logging, a failing INSERT...SELECT statement on a nontransactional table was not flagged correctly, such that, if a rollback was requested and no other nontransactional table had been updated, nothing was written to the binary log. (Bug#47175)

See also Bug#40278.

Replication: When using row-based replication, the incomplete logging of a group of events involving both transaction and nontransactional tables could cause STOP SLAVE to hang. (Bug#45940)

See also Bug#319, Bug#38205.

Replication: There were two related issues concerning handling of unsafe statements and setting of the binary logging format when there were open temporary tables on the master, and the existing replication format was row-based or mixed:

(Bug#45855, Bug#45856)

Replication: Statements that updated AUTO_INCREMENT columns in multiple tables were logged using the row-based format when --binlog_format was set to MIXED, but did not cause an Unsafe statement warning to be generated when --binlog_format was set to STATEMENT. (Bug#45827)

See also Bug#39934.

Replication: Even though INSERT DELAYED statements are unsafe for statement-based replication, they caused the statement only to be logged in row format when the binary logging format was MIXED, but did not cause a warning to be generated when the binary logging format was STATEMENT. (Bug#45825)

Replication: When using MIXED binary logging format, statements containing a LIMIT clause and occurring in stored routines were not written to the log as row events. (Bug#45785)

Replication: When using statement-based replication, database-level character sets were not always honored by the replication SQL thread. This could cause data inserted on the master using LOAD DATA to be replicated using the wrong character set.

(Bug#45516)

Replication: STOP SLAVE did not flush the relay log or the master.info or relay-log.info files, which could lead to corruption if the server crashed. (Bug#44188)

Replication: Large transactions and statements could corrupt the binary log if the size of the cache (as set by max_binlog_cache_size) was not large enough to store the changes.

Now, for transactions that do not fit into the cache, the statement is not logged, and the statement generates an error instead.

For nontransactional changes that do not fit into the cache, the statement is also not logged—an incident event is logged after committing or rolling back any pending transaction, and the statement then raises an error.

(Bug#43929)

See also Bug#37148, Bug#46166.

Replication: On Windows, RESET MASTER failed in the event of a missing binlog file rather than issuing a warning and completing the rest of the statement. (Bug#42150, Bug#42218)

Replication: Executing the sequence of statements RESET SLAVE, RESET MASTER, and FLUSH LOGS, when binary log or relay log files listed in the index file could not be found, could cause the server to crash. This could happen, for example, when these files had been moved or deleted manually. (Bug#41902)

Replication: MySQL creates binary logs in a numbered sequence, with a maximum possible 4294967295 concurrent log files, 4294967295 being the maximum value for an unsigned long integer. However, binary log file extensions were turned into negative numbers once the variable used to hold the value reached the maximum value for a signed long integer (2147483647). Consequently, when the sequence value was incremented to the next (negative) number, this caused MySQL to try to create the file using a .000000 extension, causing the server to fail since this file already existed.

Negative file extensions are no longer permitted, and an error is returned when the limit is reached. In addition, FLUSH LOGS now also reports warnings to the user, if the extension number has reached the limit, and warnings are printed to the error log when the limit is approaching. (Bug#40611)

Replication: Issuing concurrent STOP SLAVE, START SLAVE, and RESET SLAVE statements using different connections caused the replication slave to crash. (Bug#38716)

See also Bug#38715, Bug#44312.

Replication: A slave compiled using --with-libevent and run with --thread-handling=pool-of-threads could sometimes crash. (Bug#36929)

Replication: mysqlbinlog sometimes failed when trying to create temporary files; this was because it ignored the specified temp file directory and tried to use the system /tmp directory instead. (Bug#35546)

See also Bug#35543.

Replication: A CHANGE MASTER TO statement with no MASTER_HEARTBEAT_PERIOD option failed to reset the heartbeat period to its default value. (Bug#34686)

Replication: As part of the fix for this issue, the Rpl_recovery_rank column, which had appeared in the output of SHOW SLAVE HOSTS in some MySQL releases, was removed because the corresponding server variable rpl_recovery_rank (now deprecated) was never actually used. (Bug#13963)

See also Bug#21132, Bug#21869.

mysqld_safe did not pass the correct default value of plugin_dir to mysqld. (Bug#51938)

mysqld_multi failed due to a syntax error in the script. (Bug#51468)

ALTER TABLE on a MERGE table that has been locked using LOCK TABLES ... WRITE incorrectly produced an ER_TABLE_NOT_LOCKED_FOR_WRITE error. (Bug#51240)

The mysql could default to the ascii character set, which is not a valid character set choice for MySQL. The latin1 character set will now be used when an ASCII environment has been identified. (Bug#51166)

On some Unix/Linux platforms, an error during build from source could be produced, referring to a missing LT_INIT program. This is due to versions of libtool 2.1 and earlier. (Bug#51009)

Referring to a subquery result in a HAVING clause could produce incorrect results. (Bug#50995)

Aggregate functions on TIMESTAMP columns could yield incorrect or undefined results. (Bug#50888)

Use of filesort plus the join cache normally is preferred to a full index scan. But it was used even if the index is clustered, in which case, the clustered index scan can be faster. (Bug#50843)

For debug builds, SHOW BINARY LOGS caused an assertion to be raised if binary logging was not enabled. (Bug#50780)

The server did not recognize that the stored procedure cache became invalid if a view was created or modified within a procedure, resulting in a crash. (Bug#50624)

Incorrect handling of BIT columns in temporary tables could lead to spurious duplicate-key errors. (Bug#50591)

The second or subsequent invocation of a stored procedure containing DROP TRIGGER could cause a server crash. (Bug#50423)

The return value for calls to put information into the stored routine cache were not consistently checked, causing an assertion to be raised. (Bug#50412)

Full-text queries that used the truncation operator (*) could enter an infinite loop. (Bug#50351)

For debug builds, an assertion was incorrectly raised in the optimizer when matching ORDER BY expressions. (Bug#50335)

Queries optimized with GROUP_MIN_MAX did not clean up KEYREAD optimizations properly, causing subsequent queries to return incomplete rows. (Bug#49902)

mysql --show-warnings crashed if the server connection was lost. (Bug#49646)

For string-valued system variables containing multibyte characters, the byte length was used in contexts where the character length was more appropriate. (Bug#49645)

SHOW VARIABLES did not correctly display string-valued system variables that contained \0 characters. (Bug#49644)

MySQL program option-processing code incorrectly displayed some options when printing ambiguous-option errors. (Bug#49640)

For dynamic format MyISAM tables containing LONGTEXT columns, a bulk INSERT ... ON DUPLICATE KEY UPDATE or bulk REPLACE could cause corruption. (Bug#49628)

Setting binlog_format to DEFAULT assigned a value different from the default. (Bug#49540)

For debug builds, with sql_safe_updates enabled, a multiple-table UPDATE with the IGNORE modifier could raise an assertion. (Bug#49534)

EXPLAIN EXTENDED crashed trying to print column names for a subquery in the FROM clause when the table had gone out of scope. (Bug#49487)

For InnoDB tables, the test for using an index for ORDER BY sorting did not distinguish between primary keys and secondary indexes and expected primary key values to be concatenated to index values the way they are to secondary key values. (Bug#49324)

mysqltest no longer lets you execute an SQL statement on a connection after doing a send command, unless you do a reap first. This was previously accepted but could produce unpredictable results. (Bug#49269)

Valgrind warnings for several logging messages were corrected. (Bug#49130)

For debug builds on Windows, warnings about incorrect use of debugging directives were written to the error log. The directives were rewritten to eliminate these messages. (Bug#49025)

Plugins in a binary release could not be installed into a debug version of the server. (Bug#49022)

On POSIX systems, calls to select() with a file descriptor set larger than FD_SETSIZE resulted in unpredictable I/O errors; for example, when a large number of tables required repair. (Bug#48929)

A dependent subquery containing COUNT(DISTINCT col_name)) could be evaluated incorrectly. (Bug#48920)

If a stored function contained a RETURN statement with an ENUM value in the ucs2 character set, SHOW CREATE FUNCTION and SELECT DTD_IDENTIFIER FROM INFORMATION_SCHEMA.ROUTINES returned incorrect values. (Bug#48766)

An ARZ file missing from the database directory caused the server to crash. (Bug#48757)

Running SHOW CREATE TABLE on a view v1 that contained a function which accessed another view v2 could trigger a infinite loop if the view (v2) referenced within the function caused a warning to be raised while being opened. (Bug#48449)

Invalid memory reads could occur following a query that referenced a MyISAM tale multiple times with a write lock. (Bug#48438)

For debug builds, creating a view containing a row constructor caused an assertion to be raised. (Bug#48294)

An aliasing violation in the C API could lead to a crash. (Bug#48284)

Slow CALL statements were not always logged to the slow query log because execution time for multiple-statement stored procedures was assessed incorrectly. (Bug#47905)

For debug builds, killing a SELECT retrieving from a view that was processing a function caused an assertion to be raised. (Bug#47736)

Failure to open a view with a nonexistent DEFINER was improperly handled and the server would crash later attempting to lock the view. (Bug#47734)

If a prepared statement used both a MERGE table and a stored function or trigger, execution sometimes failed with a No such table error. (Bug#47648)

CREATE VIEW raised an assertion if a temporary table existed with the same name as the view. (Bug#47635)

If a temporary table was created with the same name as a view referenced in a stored routine, routine execution could raise an assertion. (Bug#47313)

Selecting from the process list in the embedded server caused a crash. (Bug#47304)

See also Bug#43733.

Programs did not exit if the option file specfied by --defaults-file was not found. (Bug#47216)

Attempts to print octal numbers with my_vsnprintf() could cause a crash. (Bug#47212)

Corrected a potential problem of unintended overwriting of files when the MY_DONT_OVERWRITE_FILE flag was used. (Bug#47126)

Deadlock occurred if one session was running a multiple-statement transaction that involved a single partitioned table and another session attempted to alter the table. (Bug#46654)

Valgrind warnings about memory allocation overruns for handling CREATE FUNCTION statements for UDFs were corrected. (Bug#46570)

The server could crash attempting to flush privileges after receipt of a SIGHUP signal. (Bug#46495)

If INSERT INTO tbl_name invoked a stored function that modified tbl_name, the server crashed. (Bug#46374)

For queries that used GROUP_CONCAT(DISTINCT ...), the value of max_heap_table_size was used for memory allocation, which could be excessive. Now the minimum of max_heap_table_size and tmp_table_size is used. (Bug#46018)

Improperly closing tables when INSERT DELAYED needed to reopen tables could cause an assertion failure. (Bug#45949)

See also Bug#18484.

Grouping by a subquery in a query with a DISTINCT aggregate function led to incorrect and unordered grouping values. (Bug#45640)

For an IPv6-enabled MySQL server, privileges specified using standard IPv4 addresses for hosts were not matched (only IPv4-mapped addresses were handled correctly).

As part of the fix for this bug, a new build option --disable-ipv6 has been introduced. Compiling MySQL with this option causes all IPv6-specific code in the server to be ignored.

(Bug#45606)

See also Bug#38247, Bug#43006, Bug#45283, Bug#45584.

The hostname cache failed to work correctly. (Bug#45584)

See also Bug#38247, Bug#43006, Bug#45283, Bug#45606.

A Windows Installation using the GUI installer would fail with:

MySQL Server 5.1 Setup Wizard ended prematurely

The wizard was interrupted before MySQL Server 5.1. could be completely installed.

Your system has not been modified. To complete installation at another time, please run
setup again.

Click Finish to exit the wizard

This was due to an step in the MSI installer that could fail to execute correctly on some environments. (Bug#45418)

Propagation of a large unsigned numeric constant in WHERE expressions could lead to incorrect results. This also affected EXPLAIN EXTENDED, which printed incorrect numeric constants in such transformed WHERE expressions. (Bug#45360)

There was no timeout for attempts to acquire metadata locks (for example, a DROP TABLE attempt for a table that was open in another transaction would not time out).

To handle such situations, there is now a lock_wait_timeout system variable that specifies the timeout in seconds for attempts to acquire metadata locks. The permitted values range from 1 to 3153600 (1 year). The default is 3153600.

This timeout applies to all statements that use metadata locks. These include DML and DDL operations on tables, views, stored procedures, and stored functions, as well as LOCK TABLES, FLUSH TABLES WITH READ LOCK, and HANDLER statements.

The timeout value applies separately for each metadata lock attempt. A given statement can require more than one lock, so it is possible for the statement to block for longer than the lock_wait_timeout value before reporting a timeout error. When lock timeout occurs, ER_LOCK_WAIT_TIMEOUT is reported.

lock_wait_timeout does not apply to delayed inserts, which always execute with a timeout of 1 year. This is done to avoid unnecessary timeouts because a session that issues a delayed insert receives no notification of delayed insert timeouts.

In addition, the unused table_lock_wait_timeout system variable was removed. (Bug#45225)

Valgrind warnings about uninitialized variables in optimizer code were corrected. (Bug#45195)

Killing a delayed-insert thread could cause a server crash. (Bug#45067)

Execution of FLUSH TABLES or FLUSH TABLES WITH READ LOCK concurrently with LOCK TABLES resulted in deadlock. (Bug#45066)

The mysql_real_connect() C API function only attempted to connect to the first IP address returned for a hostname. This could be a problem if a hostname mapped to multiple IP address and the server was not bound to the first one returned. Now mysql_real_connect() attempts to connect to all IPv4 or IPv6 addresses that a domain name maps to. (Bug#45017)

See also Bug#47757.

For plugins that did not have command-line options other than the ones to select the plugin itself, those options were not displayed in the mysqld help message. (Bug#44797)

Some plugins configured as mandatory could be disabled at server startup. (Bug#44691)

InnoDB took a shared row lock when executing SELECT statements inside a stored function as a part of a transaction using REPEATABLE READ. This prevented other transactions from updating the row. (Bug#44613)

MySQL Server permitted the creation of a merge table based on views but crashed when attempts were made to read from that table. The following example demonstrates this:

#Create a test table
CREATE TABLE tmp (id int, c char(2));                                           

#Create two VIEWs upon it
CREATE VIEW v1 AS SELECT * FROM tmp;                                            
CREATE VIEW v2 AS SELECT * FROM tmp;                                            

#Finally create a MERGE table upon the VIEWs
CREATE TABLE merge (id int, c char(2))
ENGINE=MERGE UNION(v1, v2);

#Reading from the merge table lead to a crash
SELECT * FROM merge;

The final line of the code generated the crash. (Bug#44040)

A natural join of INFORMATION_SCHEMA tables could cause an assertion failure. (Bug#43834)

When used in conjunction with LOCK TABLES, FLUSH TABLE tbl_list waited for all tables with old versions to clear from the table definition list, rather than only the named tables. (Bug#43685)

HANDLER statements are now not permitted if a table lock has been acquired with LOCK TABLES. (Bug#43272)

In the embedded server, stack overflow checks for recursive stored procedure calls did not work and stack overflow could occur. (Bug#43201)

The server could crash if an attempt to open a MERGE table child MyISAM table failed. (Bug#42862)

Comparison of TIME values could lose the sign of operands. (Bug#42664)

MAKETIME() could lose the sign of negative arguments. (Bug#42662)

SEC_TO_TIME() could lose the sign of negative arguments. (Bug#42661)

Due to work done for Bug#989, FLUSH TABLES is not permitted when there is an active LOCK TABLES ... READ. This caused a problem with mysqlhotcopy, which used that sequence of statements. mysqlhotcopy now uses FLUSH TABLES tbl_list WITH READ LOCK to flush and lock tables. If mysqlhotcopy is used with a server older than MySQL 5.5.3 that does not support this statement, it has a new option --old_server that causes it to use the previous statement sequence. (Bug#42465)

Setting key_buffer_size to a negative value could lead to very large allocations. Now an error occurs. (Bug#42103)

An assertion failure could occur if OPTIMIZE TABLE was started on an InnoDB table and the table was altered to a different storage engine during the optimization operation. (Bug#42074)

The state of a thread for the embedded server was always displayed as Writing to net, which is incorrect because there is no network connection for the embedded server. (Bug#41971)

The patch for Bug#10374 broke named-pipe and shared-memory connections on Windows. (Bug#41860)

Purging the stored routine cache could take a long time and render the server unresponsive. (Bug#41804)

Command-line options for enumeration-type plugin variables were not honored. (Bug#41010)

System variables could be set to invalid values. (Bug#40988)

The CSV storage engine did not parse '\X' characters when they occurred in unquoted fields. (Bug#40814)

When archive tables were joined on their primary keys, a query returned no result if the optimizer chose to use this index. (Bug#40677)

mysqld_safe did not treat dashes and underscores as equivalent in option names. Thanks to Erik Ljungstrom for the patch to fix this bug. (Bug#40368)

SHOW CREATE VIEW returned invalid SQL if the definition contained a SELECT 'string' statement where the string was longer than the maximum length of a column name, due to the fact that this text was also used as an alias (in the AS clause).

Because not all names retrieved from arbitrary SELECT statements can be used as view column names due to length and format restrictions, the server now checks the conformity of automatically generated column names and rewrites according to a predefined format any names that are not acceptable as view column names before storing the final view definition on disk.

In such cases, the name is now rewritten as Name_exp_pos, where pos is the position of the column. To avoid this conversion scheme, define explicit, valid names for view columns using the column_list clause of the CREATE VIEW statement.

As part of this fix, aliases are now generated only for top-level statements. (Bug#40277)

Threads were set to the Table lock state in such a way that use of this state by other threads to check for a lock wait was subject to a race condition. (Bug#39897)

Plugin shutdown could lead to an assertion failure caused by using an already destroyed mutex in the metadata locking subsystem. (Bug#39674)

Dropping a locked Maria table leads to an assertion failure. (Bug#39395)

Host name lookup failure could lead to a server crash. (Bug#39153)

flush_cache_records() did not correctly check for errors that should cause statement execution to stop, leading to a server crash. (Bug#39022)

InnoDB logged an error repeatedly trying to load a page into the buffer pool, filling the error log and using excessive disk space. Now the number of attempts is limited to 100, after which the operation aborts with a message. (Bug#38901)

Valgrind warnings that occurred for SHOW TABLE STATUS with InnoDB tables were silenced. (Bug#38479)

An IPv6-enabled MySQL server did not resolve the IP addresses of incoming connections correctly, with the result that a connection that attempted to match any privilege table entries using fully-qualified domain names for hostnames or hostnames using wildcards were dropped. (Bug#38247)

See also Bug#43006, Bug#45283, Bug#45584, Bug#45606.

For CREATE TABLE ... LIKE with a MERGE source table that included a UNION clause, that clause was omitted from the definition of the destination table. (Bug#37371)

Previously, statements inside a stored program did not clear the warning list. For example, warnings or errors generated by statements within a trigger or stored function would be accumulated and added to the message list for the statement that activated the trigger or invoked the function, “polluting” the output of SHOW WARNINGS or SHOW ERRORS for the outer statement. Normally, messages for a statement that can generate messages replace messages from the previous such statement. The effect was that a statement could have a different effect on the message list depending on whether it executed inside or outside of a stored program.

Now within a stored program, successive statements that can generate messages update the message list and replace messages from the previous such statement. Only messages from the last of these statements is copied to the message list for the outer statement. (Bug#36649)

myisampack --join did not create the destination table .frm file. (Bug#36573)

The parser incorrectly permitted MySQL error code 0 to be specified for a condition handler. (This is incorrect because the condition must be a failure condition and 0 indicates success.) (Bug#36510)

When parsing or formatting interval values of DAY_MICROSECOND type, fractional seconds were not handled correctly when more-significant fields were implied or omitted. (Bug#36466)

mysql_install_db failed if run as root and the root directory (/) was not writable. (Bug#36462)

mysql_stmt_prepare() did not reset the list of messages (those messages available using SHOW WARNINGS). (Bug#36004)

A global read lock obtained with FLUSH TABLES WITH READ LOCK did not prevent sessions from creating tables. (Bug#35935)

mysqlbinlog left temporary files on the disk after shutdown, leading to the pollution of the temporary directory, which eventually caused mysqlbinlog to fail. This caused problems in testing and other situations where mysqlbinlog might be invoked many times in a relatively short period of time. (Bug#35543)

When building MySQL when using a different target directory (for example using the VPATH environment variable), the build of the embedded readline component would fail. (Bug#35250)

String-valued system variables could be assigned literal values, but could not be assigned values using expressions. Now expressions are legal. (Bug#34883, Bug#46314)

The sql_mode system variable could be assigned the illegal value of '?'. (Bug#34834)

Some system variables could not be assigned the value DEFAULT to assign their default value. (Bug#34829, Bug#34878)

Compiling MySQL on FreeBSD would fail due to missing definitions for certain network constants. (Bug#34292)

Creation of a temporary BLOB or TEXT column could create a column with the wrong maximum length. (Bug#33969)

INSERT INTO ... VALUES(DEFAULT) failed to insert the correct value for ENUM columns. For MyISAM tables, an empty value was inserted. For CSV tables, the table became corrupt. (Bug#33717)

When read_only was enabled, the server incorrectly prevented data modifications to TEMPORARY tables belonging to transactional storage engines such as InnoDB. (Bug#33669)

Constant expressions in WHERE, HAVING, or ON clauses were not cached, but were evaluated for each row. This caused a slowdown of query execution, especially if constant user-defined functions or stored functions were used. (Bug#33546)

Plugins could find the unqualified form of their system variables but not the qualified form. For example, a plugin p with a system variable sv could find sv but not p_sv. (Bug#32902)

Killing a statement that invoked a stored function could return an incorrect error message indicating table corruption rather than that the statement had been interrupted. (Bug#32140)

Occurrence of an error within a stored routine did not always cause immediate statement termination. (Bug#31881)

For DROP FUNCTION db_name.func_name (that is, when the function name is qualified with the database name), the statement should apply only to a stored function named func_name in the given database. However, if a UDF with the same name existed, the statement dropped the UDF instead. (Bug#31767)

mysqld sometimes miscalculated the number of digits required when storing a floating-point number in a CHAR column. This caused the value to be truncated, or (when using a debug build) caused the server to crash. (Bug#26788)

See also Bug#12860.

ALTER TABLE could not be used to add columns to a table if the table had an index on a utf8 column with a TEXT data type. (Bug#26180)

If an operation had an InnoDB table, and two triggers, AFTER UPDATE and AFTER INSERT, competing for different resources (such as two distinct MyISAM tables), the triggers were unable to execute concurrently. In addition, INSERT and UPDATE statements for the InnoDB table were unable to run concurrently. (Bug#26141)

Some system variables displayed by SHOW VARIABLES could not be selected using SELECT @@{GLOBAL,SESSION}.var_name. (Bug#25430)

Statements to create, alter, or drop a view were not waiting for completion of statements that were using the view, which led to incorrect sequences of statements in the binary log when statement-based logging was enabled. (Bug#25144)

Previously, the server handled character data types for a stored routine parameter, local routine variable created with DECLARE, or stored function return value as follows: If the CHARACTER SET attribute was present, the COLLATE attribute was not supported, so the character set's default collation was used. (This includes use of BINARY, which in this context specifies the binary collation of the character set.) If there was no CHARACTER SET attribute, the database character set and its default collation were used.

Now for character data types, if there is a CHARACTER SET attribute in the declaration, the specified character set and its default collation is used. If the COLLATE is also present, that collation is used rather than the default collation. If there is no CHARACTER SET attribute, the database character set and collation in effect at routine creation time are used. (The database character set and collation are given by the value of the character_set_database and collation_database system variables.) (Bug#24690)

Data truncated for column col_num at row row_num warnings were generated for some (constant) values that did not have too high precision. (Bug#24541)

A statement that caused a circular wait among statements did not return a deadlock error. Now the server detects deadlock and returns ER_LOCK_DEADLOCK. (Bug#22876)

CREATE TABLE ... LIKE did not always produce an error is the source table column defaults were illegal for the current version of MySQL. (This could occur if the table was created using an older server that was less restrictive about legal default values.) (Bug#22090)

Several data-modification statements were not being counted toward the MAX_UPDATES_PER_HOUR user resource limit. (Bug#21793)

When inserting an extraordinarly large value into a DOUBLE column, the value could be truncated in such a way that the new value cannot be reloaded manually or from the output of mysqldump. (Bug#21497)

The value of sql_slave_skip_counter was empty when displayed by SHOW VARIABLES or INFORMATION_SCHEMA.GLOBAL_VARIABLES. (Bug#20413, Bug#37187)

For INSERT DELAYED statements issued for a table while an ALTER TABLE operation on the table was in progress, the server could return a spurious Server shutdown in progress error. (Bug#18484)

See also Bug#45949.

Delayed-insert threads were counted as connected but not as created, incorrectly leading to a Threads_connected value greater than the Threads_created value. (Bug#17954)

The character set was not being properly initialized for CAST() with a type such as CHAR(2) BINARY, which resulted in incorrect results or a server crash. (Bug#17903)

Stored procedure exception handlers were catching fatal errors (such as out of memory errors), which could cause execution not to stop to due a continue handler. Now fatal errors are not caught by exception handlers and a fatal error is returned to the client. (Bug#15192)

Zero-padding of exponent values was not the same across platforms. (Bug#12860)

For CREATE TABLE, the parser did not enforce that parentheses were present in a CHECK (expr) clause; now it does. The parser did not enforce that CONSTRAINT [symbol] without a following CHECK clause was illegal; now it does. (Bug#11714, Bug#35578, Bug#38696)

If a connection was waiting for a GET_LOCK() lock or a SLEEP() call, and the connection aborted, the server did not detect this and thus did not close the connection. This caused a waste of system resources allocated to dead connections. Now the server checks such a connection every five seconds to see whether it has been aborted. If so, the connection is killed (and any lock request is aborted). (Bug#10374)

perror did not work for errors described in the sql/share/errmsg.txt file. (Bug#10143)

The grammar for GROUP BY, when used with WITH CUBE or WITH ROLLUP, caused a conflict with the grammar for view definitions that included WITH CHECK OPTION. (Bug#9801)

For the DIV operator, incorrect results could occur for noninteger operands that exceed BIGINT range. Now, if either operand has a noninteger type, the operands are converted to DECIMAL and divided using DECIMAL arithmetic before converting the result to BIGINT. If the result exceeds BIGINT range, an error occurs. (Bug#8457)

Labels in stored routines did not work if the character set was not latin1. (Bug#7088)

Previously, for some Asian CJK character sets, the UPPER() and LOWER() functions worked only for basic Latin letters (A-Z, a-z). The affected character sets are ujis, sjis, gb2312, cp932, eucjpms, big5, euckr, and gbk.

Now UPPER() and LOWER() perform case conversion correctly for all characters in these character sets, with the exception that if a character set contains a character in only one lettercase, conversion to the other lettercase cannot be done.