The InnoDB Plugin is included in MySQL 5.5 releases as the built-in version of InnoDB. The version of the InnoDB in this release is 1.0.5 and is considered of Release Candidate (RC) quality.

This version of InnoDB offers new features, improved performance and scalability, enhanced reliability and new capabilities for flexibility and ease of use. Among the features are “Fast index creation,” table and index compression, file format management, new INFORMATION_SCHEMA tables, capacity tuning, multiple background I/O threads, and group commit.

For information about these features, see InnoDB Plugin 1.0 for MySQL 5.1 User’s Guide. For general information about using InnoDB in MySQL, see Section 13.6, “The InnoDB Storage Engine”.

In this version of InnoDB, the innodb_file_io_threads system variable has been removed and replaced with innodb_read_io_threads and innodb_write_io_threads. If you upgrade from MySQL 5.1 to MySQL 5.5 and previously explicitly set innodb_file_io_threads at server startup, you must change your configuration. Either remove any reference to innodb_file_io_threads or replace it with references to innodb_read_io_threads and innodb_write_io_threads.

Incompatible Change: MySQL Server now includes a plugin services interface that complements the plugin API. The services interface enables server functionality to be exposed as a “service” that plugins can access through a function-call interface. The libmysqlservices library provides access to the available services and dynamic plugins now must be linked against this library (use the -lmysqlservices flag). For an example showing what Makefile.am should look like, see Section 23.2.6, “MySQL Services for Plugins”. (Bug#48461)

Incompatible Change: Several changes have been made regarding the language and character set of error messages:

For more information, see Section 9.2, “Setting the Error Message Language”, and Section 9.1.6, “Character Set for Error Messages”.

See also Bug#46218, Bug#46236.

Partitioning: New PARTITION BY RANGE COLUMNS(column_list) and PARTITION BY LIST COLUMNS(column_list) options are added for the CREATE TABLE and ALTER TABLE statements.

A major benefit of RANGE COLUMNS and LIST COLUMNS partitioning is that they make it possible to define ranges or lists based on column values that use string, date, or datetime values.

These new extensions also broaden the scope of partition pruning to provide better coverage for queries using comparisons on multiple columns in the WHERE clause, some examples being WHERE a = 1 AND b < 10 and WHERE a = 1 AND b = 10 AND c < 10.

For more information, see Section 18.2.1, “RANGE Partitioning”, Section 18.2.2, “LIST Partitioning”, and Section 18.4, “Partition Pruning”.

Partitioning: A new ALTER TABLE option, TRUNCATE PARTITION, makes it possible to delete rows from one or more selected partitions only. Unlike the case with ALTER TABLE ... DROP PARTITION, ALTER TABLE ... TRUNCATE PARTITION merely deletes all rows from the specified partition or partitions, and does not change the definition of the table.

Partitioning: It is now possible to assign indexes on partitioned MyISAM tables to key caches using the CACHE INDEX and to preload such indexes into the cache using LOAD INDEX INTO CACHE statements. Cache assignment and preloading of indexes for such tables can be performed for one, several, or all partitions of the table.

This functionality is supported for only those partitioned tables that employ the MyISAM storage engine.

Replication: The global server variable sync_relay_log is introduced for use on replication slaves. Setting this variable to a nonzero integer value N causes the slave to synchronize the relay log to disk after every N events. Setting its value to 0 permits the operating system to handle synchronization of the file. The action of this variable, when enabled, is analogous to how the sync_binlog variable works with regard to binary logs on a replication master.

The global server variables sync_master_info and sync_relay_log_info are introduced for use on replication slaves to control synchronization of, respectively, the master.info and relay.info files.

In each case, setting the variable to a nonzero integer value N causes the slave to synchronize the corresponding file to disk after every N events. Setting its value to 0 permits the operating system to handle synchronization of the file instead.

The actions of these variables, when enabled, are analogous to how the sync_binlog variable works with regard to binary logs on a replication master.

An additional system variable relay_log_recovery is also now available. When enabled, this variable causes a replication slave to discard relay log files obtained from the replication master following a crash.

These variables can also be set in my.cnf, or by using the --sync-relay-log, --sync-master-info, --sync-relay-log-info, and --relay-log-recovery server options.

For more information, see Section 17.1.3.3, “Replication Slave Options and Variables”. (Bug#31665, Bug#35542, Bug#40337)

Replication: Because SHOW BINLOG EVENTS cannot be used to read events from relay log files, a new SHOW RELAYLOG EVENTS statement has been added for this purpose. (Bug#28777)

Replication: In circular replication, it was sometimes possible for an event to propagate such that it would be reapplied on all servers. This could occur when the originating server was removed from the replication circle and so could no longer act as the terminator of its own events, as normally happens in circular replication.

To prevent this from occurring, a new IGNORE_SERVER_IDS option is introduced for the CHANGE MASTER TO statement. This option takes a list of replication server IDs; events having a server ID which appears in this list are ignored and not applied. For more information, see Section 12.5.2.1, “CHANGE MASTER TO Syntax”.

In conjunction with the introduction of IGNORE_SERVER_IDS, SHOW SLAVE STATUS has two new fields. Replicate_Ignore_Server_Ids displays information about ignored servers. Master_Server_Id displays the server_id value from the master. (Bug#25998)

See also Bug#27808.

Replication: A replication heartbeat mechanism has been added to facilitate monitoring. This provides an alternative to checking log files, making it possible to detect in real time when a slave has failed.

Configuration of heartbeats is done using a new MASTER_HEARTBEAT_PERIOD = interval clause for the CHANGE MASTER TO statement (see Section 12.5.2.1, “CHANGE MASTER TO Syntax”); monitoring can be done by checking the values of the status variables Slave_heartbeat_period and Slave_received_heartbeats (see Section 5.1.6, “Server Status Variables”).

The addition of replication heartbeats addresses a number of issues:

(Bug#20435, Bug#29309, Bug#30932)

Replication: MySQL now supports an interface for semisynchronous replication: A commit performed on the master side blocks before returning to the session that performed the transaction until at least one slave acknowledges that it has received and logged the events for the transaction. Semisynchronous replication is implemented through an optional plugin component. See Section 17.3.8, “Semisynchronous Replication”.

On Linux (and perhaps other systems), the performance of MySQL Server can be improved by using a different malloc() implementation, developed by Google and called tcmalloc. The gain is noticeable with a higher number of simultaneous users. To support use of this library, the following changes have been made:

The --malloc-lib option works by modifying the LD_PRELOAD environment value to affect dynamic linking to enable the loader to find the memory-allocation library when mysqld runs:

As a result of the preceding changes, Linux users can use the libtcmalloc_minimal.so now included in binary packages by adding these lines to the my.cnf file:

[mysqld_safe]
malloc-lib=tcmalloc

Those lines also suffice for users on any platform who have installed a tcmalloc package in /usr/lib. To use a specific tcmalloc library, specify its full path name. Example:

[mysqld_safe]
malloc-lib=/opt/lib/libtcmalloc_minimal.so

(Bug#47549)

The InnoDB buffer pool is divided into two sublists: A new sublist containing blocks that are heavily used by queries, and an old sublist containing less-used blocks and from which candidates for eviction are taken. In the default operation of the buffer pool, a block when read in is loaded at the midpoint and then moved immediately to the head of the new sublist as soon as an access occurs. In the case of a table scan (such as performed for a mysqldump operation), each block read by the scan ends up moving to the head of the new sublist because multiple rows are accessed from each block. This occurs even for a one-time scan, where the blocks are not otherwise used by other queries. Blocks may also be loaded by the read-ahead background thread and then moved to the head of the new sublist by a single access. These effects can be disadvantageous because they push blocks that are in heavy use by other queries out of the new sublist to the old sublist where they become subject to eviction.

InnoDB now provides two system variables that enable LRU algorithm tuning:

For additional information, see Section 7.9.1, “The InnoDB Buffer Pool”. (Bug#45015)

Two status variables have been added to SHOW STATUS output. Innodb_buffer_pool_read_ahead and Innodb_buffer_pool_read_ahead_evicted indicate the number of pages read in by the InnoDB read-ahead background thread, and the number of such pages evicted without ever being accessed, respectively. Also, the status variables Innodb_buffer_pool_read_ahead_rnd and Innodb_buffer_pool_read_ahead_seq status variables have been removed. (Bug#42885)

Columns that provide a catalog value in INFORMATION_SCHEMA tables (for example, TABLES.TABLE_CATALOG) now have a value of def rather than NULL. (Bug#35427)

The deprecated --default-table-type server option has been removed. (Bug#34818)

Previously, mysqldump would not dump the INFORMATION_SCHEMA database and ignored it if it was named on the command line. Now, mysqldump will dump INFORMATION_SCHEMA if it is named on the command line. Currently, this requires that the --skip-lock-tables (or --skip-opt) option be given. (Bug#33762)

Several undocumented C API functions were removed: mysql_manager_close(), mysql_manager_command(), mysql_manager_connect(), mysql_manager_fetch_line(), mysql_manager_init(), mysql_disable_reads_from_master(), mysql_disable_rpl_parse(), mysql_enable_reads_from_master(), mysql_enable_rpl_parse(), mysql_master_query(), mysql_master_send_query(), mysql_reads_from_master_enabled(), mysql_rpl_parse_enabled(), mysql_rpl_probe(), mysql_rpl_query_type(), mysql_set_master(), mysql_slave_query(), and mysql_slave_send_query(). (Bug#31952, Bug#31954)

Sinhala collations utf8_sinhala_ci and ucs2_sinhala_ci were added for the utf8 and ucs2 character sets. (Bug#26474)

If the value of the --log-warnings option is greater than 1, the server now writes access-denied errors for new connection attempts to the error log (for example, if a client user name or password is incorrect). (Bug#25822)

On WIndows, use of POSIX I/O interfaces in mysys was replaced with Win32 API calls (CreateFile(), WriteFile(), and so forth) and the default maximum number of open files has been increased to 16384. The maximum can be increased further by using the --open-files-limit=N option at server startup. (Bug#24509)

The TRADITIONAL SQL mode now includes NO_ENGINE_SUBSTITUTION. (Bug#21099)

MySQL now implements the SQL standard SIGNAL and RESIGNAL statements. See Section 12.7.8, “SIGNAL and RESIGNAL”. (Bug#11661)

The undocumented, deprecated, and not useful SHOW COLUMN TYPES statement has been removed. (Bug#5299)

The libmysqlclient client library is now built as a thread-safe library. The libmysqlclient_r client library is still present for compatibility, but is just a symlink to libmysqlclient.

The FORMAT() function now supports an optional third parameter that enables a locale to be specified to be used for the result number's decimal point, thousands separator, and grouping between separators. Permissible locale values are the same as the legal values for the lc_time_names system variable (see Section 9.7, “MySQL Server Locale Support”). For example, the result from FORMAT(1234567.89,2,'de_DE') is 1.234.567,89. If no locale is specified, the default is 'en_US'.

The Greek locale 'el_GR' is now a permissible value for the lc_time_names system variable.

Previously, in the absence of other information, the MySQL client programs mysql, mysqladmin, mysqlcheck, mysqlimport, and mysqlshow used the compiled-in default character set, usually latin1.

Now these clients can autodetect which character set to use based on the operating system setting, such as the value of the LANG or LC_ALL locale environment language on Unix system or the code page setting on Windows systems. For systems on which the locale is available from the OS, the client uses it to set the default character set rather than using the compiled-in default. Thus, users can configure the locale in their environment for use by MySQL clients. For example, setting LANG to ru_RU.KOI8-R causes the koi8r character set to be used. The OS character set is mapped to the closest MySQL character set if there is no exact match. If the client does not support the matching character set, it uses the compiled-in default. (For example, ucs2 is not supported as a connection character set.)

An implication of this change is that if your environment is configured to use a non-latin1 locale, MySQL client programs will use a different connection character set than previously, as though you had issued an implicit SET NAMES statement.

Third-party applications that wish to use character set autodetection based on the OS setting can use the following mysql_options() call before connecting to the server:

mysql_options(mysql,
              MYSQL_SET_CHARSET_NAME,
              MYSQL_AUTODETECT_CHARSET_NAME);

See Section 9.1.4, “Connection Character Sets and Collations”.

mysql_upgrade now has an --upgrade-system-tables option that causes only the system tables to be upgraded. With this option, data upgrades are not performed.

The CREATE TABLESPACE privilege has been introduced. This privilege exists at the global (superuser) level and enables you to create, alter, and drop tablespaces and logfile groups.

The server now supports a Debug Sync facility for thread synchronization during testing and debugging. To compile in this facility, configure MySQL with the --enable-debug-sync option. The debug_sync system variable provides the user interface Debug Sync. mysqld and mysql-test-run.pl support a --debug-sync-timeout option to enable the facility and set the default synchronization point timeout.

Added the TO_SECONDS() function, which converts a date or datetime value to a number of seconds since the year 0.

Parser performance was improved for identifier scanning and conversion of ASCII string literals.

The LOAD XML INFILE statement was added. This statement makes it possible to read data directly from XML files into database tables. For more information, see Section 12.2.7, “LOAD XML Syntax”.

Performance: The server unnecessarily acquired a query cache mutex even with the query cache disabled, resulting in a small performance decrement which could show up as threads often in state “invalidating query cache entries (table)”, particularly on a replication slave with row-based replication. Now if the server is started with query_cache_type set to 0, it does not acquire the query cache mutex. This has the implication that the query cache cannot be enabled at runtime. (Bug#38551)

Performance: The InnoDB adaptive hash latch is released (if held) for several potentially long-running operations. This improves throughput for other queries if the current query is removing a temporary table, changing a temporary table from memory to disk, using CREATE TABLE ... SELECT, or performing a MyISAM repair on a table used within a transaction. (Bug#32149)

Important Change: Security Fix: It was possible to circumvent privileges through the creation of MyISAM tables employing the DATA DIRECTORY and INDEX DIRECTORY options to overwrite existing table files in the MySQL data directory. Use of the MySQL data directory in DATA DIRECTORY and INDEX DIRECTORY is no longer permitted. This is now also true of these options when used with partitioned tables and individual partitions of such tables. (Bug#32167, CVE-2008-2079)

See also Bug#39277.

Security Fix: MySQL clients linked against OpenSSL can be tricked not to check server certificates. (Bug#47320, CVE-2009-4028)

Security Fix: The server crashed if an account without the proper privileges attempted to create a stored procedure. (Bug#44658)

Incompatible Change: Replication: Concurrent transactions that inserted rows into a table with an AUTO_INCREMENT column could break statement-based or mixed-format replication error 1062 Duplicate entry '...' for key 'PRIMARY' on the slave. This was especially likely to happen when one of the transactions activated a trigger that inserted rows into the table with the AUTO_INCREMENT column, although other conditions could also cause the issue to manifest.

As part of the fix for this issue, any statement that causes a trigger or function to update an AUTO_INCREMENT column is now considered unsafe for statement-based replication. For more information, see Section 17.4.1.1, “Replication and AUTO_INCREMENT”. (Bug#45677)

See also Bug#42415, Bug#48608, Bug#50440, Bug#53079.

Incompatible Change: For system variables that take values of ON or OFF, OF was accepted as a legal variable. Now system variables that take “enumeration” values must be assigned the full value. This affects some other variables that previously could be assigned using unambiguous prefixes of permissible values, such as tx_isolation. (Bug#34828)

Incompatible Change: In binary installations of MySQL, the supplied binary-configure script would start and configure MySQL, even when command help was requested with the --help command-line option. The --help, if provided, will no longer start and install the server. (Bug#30954)

Incompatible Change: Access privileges for several statements are more accurately checked:

(Bug#27145)

Important Change: Replication: MyISAM transactions replicated to a transactional slave left the slave in an unstable condition. This was due to the fact that, when replicating from a nontransactional storage engine to a transactional engine with autocommit turned off, no BEGIN and COMMIT statements were written to the binary log; thus, on the slave, a never-ending transaction was started.

The fix for this issue includes enforcing autocommit mode on the slave by replicating all autocommit=1 statements from the master. (Bug#29288)

Important Change: Replication: The CHANGE MASTER TO statement required the value for RELAY_LOG_FILE to be an absolute path, while the MASTER_LOG_FILE path could be relative.

The inconsistent behavior is resolved by permitting relative paths for RELAY_LOG_FILE, and by using the same basename for RELAY_LOG_FILE as for MASTER_LOG_FILE. For more information, see Section 12.5.2.1, “CHANGE MASTER TO Syntax”. (Bug#12190)

Important Change: An option that requires a value, when specified in an option file without a value, was assigned the text of the next line in the file as the value. Now, if you fail to specify a required value in an option file, the server aborts with an error.

This change does not effect how options are handled by the server when they are used on the command line. For example, starting the server using mysqld_safe --relay-log --relay-log-index & causes the server to create relay log files named --relay-log-index.000001, --relay-log-index.000002, and so on, because the --relay-log option expects an argument. (Bug#25192)

Partitioning: An ALTER TABLE ... ADD PARTITION statement that caused open_files_limit to be exceeded led to a crash of the MySQL server. (Bug#46922)

See also Bug#47343.

Partitioning: When performing an INSERT ... SELECT into a partitioned table, read_buffer_size bytes of memory were allocated for every partition in the target table, resulting in consumption of large amounts of memory when the table had many partitions (more than 100).

This fix changes the method used to estimate the buffer size required for each partition and limits the total buffer size to a maximum of approximately 10 times read_buffer_size. (Bug#45840)

Partitioning: The cardinality of indexes on partitioned tables was calculated using the first partition in the table, which could result in suboptimal query execution plans being chosen. Now the partition having the most records is used instead, which should result in better use of indexes and thus improved performance of queries against partitioned tables in many if not most cases. (Bug#44059)

Partitioning: Truncating a partitioned MyISAM table did not reset the AUTO_INCREMENT value. (Bug#35111)

Partitioning: For partitioned tables with more than ten partitions, a full table scan was used in some cases when only a subset of the partitions were needed. (Bug#33730)

Replication: When using statement-based or mixed-format replication, the database name was not written to the binary log when executing a LOAD DATA statement. This caused problems when the table being loaded belonged to a database other than the current database; data could be loaded into the wrong table (if a table having the same name existed in the current database) or replication could fail (if no table having that name existed in the current database). Now a table referenced in a LOAD DATA statement is always logged using its fully qualified name when the database to which it belongs is not the current database. (Bug#48297)

Replication: When a session was closed on the master, temporary tables belonging to that session were logged with the wrong database names when either of the following conditions was true:

(Bug#48216)

See also Bug#46861, Bug#48297.

Replication: When using row-based replication, changes to nontransactional tables that occurred early in a transaction were not immediately flushed upon committing a statement. This behavior could break consistency since changes made to nontransactional tables become immediately visible to other connections. (Bug#47678)

See also Bug#47287, Bug#46864, Bug#43929, Bug#46129.

Replication: When mysqlbinlog --verbose was used to read a binary log that had been recorded using the row-based format, the output for events that updated some but not all columns of tables was not correct. (Bug#47323)

Replication: Performing ALTER TABLE ... DISABLE KEYS on a slave table caused row-based replication to fail. (Bug#47312)

Replication: When using the row-based format to replicate a transaction involving both transactional and nontransactional engines, which contained a DML statement affecting multiple rows, the statement failed; if this transaction was followed by a COMMIT, the master and the slave could diverge, because the statement was correctly rolled back on the master, but was applied on the slave. (Bug#47287)

See also Bug#46864.

Replication: BEGIN statements were not included in the output of mysqlbinlog. (Bug#46998)

Replication: A problem with the BINLOG statement in the output of mysqlbinlog could break replication; statements could be logged with the server ID stored within events by the BINLOG statement rather than the ID of the running server. With this fix, the server ID of the server executing the statements can no longer be overridden by the server ID stored in the binary log's format description statement. (Bug#46640)

This regression was introduced by Bug#32407.

Replication: When using row-based replication, DROP TEMPORARY TABLE IF EXISTS was written to the binary log if the table named in the statement did not exist, even though a DROP TEMPORARY TABLE statement should never be logged in row-based logging mode, whether the table exists or not. (Bug#46572)

Replication: The internal function get_master_version_and_clock() (defined in sql/slave.cc) ignored errors and passed directly when queries failed, or when queries succeeded but the result retrieved was empty. Now this function tries to reconnect the master if a query fails due to transient network problems, and to fail otherwise. The I/O thread now prints a warning if the same system variables do not exist on master (in the event the master is a very old version of MySQL, compared to the slave.) (Bug#45214)

Replication: Replicating TEXT or VARCHAR columns declared as NULL on the master but NOT NULL on the slave caused the slave to crash. (Bug#43789)

See also Bug#38850, Bug#43783, Bug#43785, Bug#47741, Bug#48091.

Replication: By default, all statements executed by the mysql_upgrade program on the master are written to the binary log, then replicated to the slave. In some cases, this can result in problems; for example, it attempted to alter log tables on replicated databases (this failed due to logging being enabled).

As part of this fix, a mysql_upgrade option, --write-binlog, is added. Its inverse, --skip-write-binlog, can be used to disable binary logging while the upgrade is in progress. (Bug#43579)

Replication: This fix handles 2 issues encountered on replication slaves during startup:

(Bug#43075)

Replication: When the logging format was set without binary logging being enabled, the server failed to start. Now in such cases, the server starts successfully, binlog_format is set, and a warning is logged instead of an error. (Bug#42928)

Replication: On the master, if a binary log event is larger than max_allowed_packet, the error message ER_MASTER_FATAL_ERROR_READING_BINLOG is sent to a slave when it requests a dump from the master, thus leading the I/O thread to stop. On a slave, the I/O thread stops when receiving a packet larger than max_allowed_packet.

In both cases, however, there was no Last_IO_Error reported, which made it difficult to determine why the slave had stopped in such cases. Now, Last_IO_Error is reported when max_allowed_packet is exceeded, and provides the reason for which the slave I/O thread stopped. (Bug#42914)

See also Bug#14068, Bug#47200, Bug#47303.

Replication: When using statement-based replication and the transaction isolation level was set to READ COMMITTED or a less strict level, InnoDB returned an error even if the statement in question was filtered out according to the --binlog-do-db or --binlog-ignore-db rules in effect at the time. (Bug#42829)

Replication: When using row-based format, replication failed with the error Could not execute Write_rows event on table ...; Field '...' doesn't have a default value when an INSERT was made on the master without specifying a value for a column having no default, even if strict server SQL mode was not in use and the statement would otherwise have succeeded on the master. Now the SQL mode is checked, and the statement is replicated unless strict mode is in effect. For more information, see Section 5.1.7, “Server SQL Modes”. (Bug#38173)

See also Bug#38262, Bug#43992.

Replication: When autocommit was set equal to 1 after starting a transaction, the binary log did not commit the outstanding transaction. The reason this happened was that the binary log commit function saw only the values of the new settings, and decided that there was nothing to commit.

This issue was first observed when using the Falcon storage engine, but it is possible that it affected other storage engines as well. (Bug#37221)

Replication: FLUSH LOGS did not close and reopen the binary log index file. (Bug#34582)

See also Bug#48738.

Replication: An error message relating to permissions required for SHOW SLAVE STATUS was confusing. (Bug#34227)

Replication: The --base64-output option for mysqlbinlog was not honored for all types of events. This interfered in some cases with performing point-in-time recovery. (Bug#32407)

See also Bug#46640, Bug#34777.

Replication: The value of Slave_IO_running in the output of SHOW SLAVE STATUS did not distinguish between all 3 possible states of the slave I/O thread (not running; running but not connected; connected). Now the value Connecting (rather than No) is shown when the slave I/O thread is running but the slave is not connected to a replication master.

The server system variable Slave_running also reflects this change, and is now consistent with what is shown for Slave_IO_running. (Bug#30703, Bug#41613, Bug#51089)

Replication: Queries which were written to the slow query log on the master were not written to the slow query log on the slave. (Bug#23300)

See also Bug#48632.

Replication: Valgrind revealed an issue with mysqld that as corrected: memory corruption in replication slaves when switching databases. (Bug#19022)

API: The fix for Bug#24507 could lead in some cases to client application failures due to a race condition. Now the server waits for the “dummy” thread to return before exiting, thus making sure that only one thread can initialize the POSIX threads library. (Bug#42850)

Certain INTERVAL expressions could cause a crash on 64-bit systems. (Bug#48739)

Following a literal, the COLLATE clause was mishandled such that different results could be produced depending on whether an index was used. (Bug#48447)

SUM() artificially increased the precision of a DECIMAL argument, which was truncated when a temporary table was created to hold the results. (Bug#48370)

See also Bug#45261.

GRANT and REVOKE crashed if a user name was specified as CURRENT_USER(). (Bug#48319)

If an outer query was invalid, a subquery might not even be set up. EXPLAIN EXTENDED did not expect this and caused a crash by trying to dereference improperly set up information. (Bug#48295)

A query containing a view using temporary tables and multiple tables in the FROM clause and PROCEDURE ANALYSE() caused a server crash.

As a result of this bug fix, PROCEDURE ANALYSE() is legal only in a top-level SELECT. (Bug#48293)

See also Bug#46184.

Error handling was missing for SELECT statements containing subqueries in the WHERE clause and that assigned a SELECT result to a user variable. The server could crash as a result. (Bug#48291)

An assertion could fail if the optimizer used a SPATIAL index. (Bug#48258, Bug#47019)

Memory-allocation failures were handled incorrectly in the InnoDB os_mem_alloc_large() function. (Bug#48237)

WHERE clauses with outer_value_list NOT IN subquery were handled incorrectly if the outer value list contained multiple items at least one of which could be NULL. (Bug#48177)

Searches using a non-default collation could return different results for a table when partitioning was and was not used. (Bug#48161)

A combination of GROUP BY WITH ROLLUP, DISTINCT and the const join type in a query caused a server crash when the optimizer chose to employ a temporary table to resolve DISTINCT. (Bug#48131)

The subquery optimizer had a memory leak. (Bug#48060)

Server shutdown failed on Windows. (Bug#48047)

In some cases, using a null microsecond part in a WHERE condition (for example, WHERE date_time_field <= 'YYYY-MM-DD HH:MM:SS.0000') could lead to incorrect results due to improper DATETIME comparison. (Bug#47963)

A build configured using the --without-server option did not compile the yaSSL code, so if --with-ssl was also used, the build failed. (Bug#47957)

When a query used a DATE or DATETIME value formatted using any separator characters other than hyphen ('-') and a >= condition matching only the greatest value in an indexed column, the result was empty if an index range scan was employed. (Bug#47925)

mysys/mf_keycache.c requires threading, but no test was made for thread support. (Bug#47923)

For debug builds, an assertion could fail during the next statement executed for a temporary table after a multiple-table UPDATE involving that table and modified an AUTO_INCREMENT column with a user-supplied value. (Bug#47919)

The mysys/mf_strip.c file, which defines the strip_sp() function, has been removed from the MySQL source. The function was no longer used within the main build, and the supplied function was causing symbol errors on Windows builds. (Bug#47857)

When building storage engines on Windows it was not possible to specify additional libraries within the CMake file required for the build. An ${engine}_LIBS macro has been included in the files to support these additional storage-engine specific libraries. (Bug#47797)

When building a pluggable storage engine on Windows, the engine name could be based on the directory name where the engine was located, rather than the configured storage engine name. (Bug#47795)

During cleanup of a stored procedure's internal structures, the flag to ignore the errors for INSERT IGNORE or UPDATE IGNORE was not cleaned up, which could result in a server crash. (Bug#47788)

If the first argument to GeomFromWKB() function was a geometry value, the function just returned its value. However, it failed to preserve the argument's null_value flag, which caused an unexpected NULL value to be returned to the caller, resulting in a server crash. (Bug#47780)

InnoDB could crash when updating spatial values. (Bug#47777)

The pthread_cond_wait() implementations for Windows could deadlock in some rare circumstances. (Bug#47768)

The encoding of values for SET system_variable = identifier statements was incorrect, resulting in incorrect error messages. (Bug#47597)

On WIndows, when an idle named pipe connection was forcibly closed with a KILL statement or because the server was being shut down, the thread that was closing the connection would hang infinitely. (Bug#47571, Bug#31621)

On Mac OS X or Windows, sending a SIGHUP signal to the server or an asynchronous flush (triggered by flush_time) caused the server to crash. (Bug#47525)

Debug builds could not be compiled with the Sun Studio compiler. (Bug#47474)

Queries of the form SELECT SUM(DISTINCT varchar_key) FROM tbl_name caused a server crash. (Bug#47421)

A function call could end without throwing an error or setting the return value. For example, this could happen when an error occurred while calculating the return value. This is fixed by setting the value to NULL when an error occurs during evaluation of an expression. (Bug#47412)

mysqladmin debug could crash on 64-bit systems. (Bug#47382)

A simple SELECT with implicit grouping could return many rows rather than a single row if the query was ordered by the aggregated column in the select list. (Bug#47280)

An assertion could be raised for CREATE TABLE if there was a pending INSERT DELAYED or REPLACE DELAYED for the same table. (Bug#47274)

InnoDB raised errors in some cases in a manner not compatible with SIGNAL and RESIGNAL. (Bug#47233)

A multiple-table UPDATE involving a natural join and a mergeable view raised an assertion. (Bug#47150)

On FreeBSD, memory mapping for MERGE tables could fail if underlying tables were empty. (Bug#47139)

Solaris binary packages now are compiled with -g0 rather than -g. (Bug#47137)

If an InnoDB table was created with the AUTO_INCREMENT table option to specify an initial auto-increment value, and an index was added in a separate operation later, the auto-increment value was lost (subsequent inserts began at 1 rather than the specified value). (Bug#47125)

Incorrect handling of predicates involving NULL by the range optimizer could lead to an infinite loop during query execution. (Bug#47123)

EXPLAIN caused a server crash for certain valid queries. (Bug#47106)

Repair by sort or parallel repair of MyISAM tables could fail to fail over to repair with key cache. (Bug#47073)

InnoDB did not compile on some Solaris systems. (Bug#47058)

On WIndows, when a failed I/O operation occurred with return code of ERROR_WORKING_SET_QUOTA, InnoDB intentionally crashed the server. Now InnoDB sleeps for 100ms and retries the failed operation. (Bug#47055)

The mysql_config script contained a reference to @innodb_system_libs@ that was not replaced with the corresponding library flags during the build process and ended up in the output of mysql_config --libs. (Bug#47007)

The configure option --without-server did not work. (Bug#46980)

InnoDB now ignores negative values supplied by a user for an AUTO_INCREMENT column when calculating the next value to store in the data dictionary. Setting AUTO_INCREMENT columns to negative values is undefined behavior and this change should bring the behavior of InnoDB closer to what users expect. (Bug#46965)

Failed multiple-table DELETE statements could raise an assertion. (Bug#46958)

When MySQL crashed (or a snapshot was taken that simulates a crash), it was possible that internal XA transactions (used to synchronize the binary log and InnoDB) could be left in a PREPARED state, whereas they should be rolled back. This occurred when the server_id value changed before the restart, because that value was used to construct XID values.

Now the restriction is relaxed that the server_id value be consistent for XID values to be considered valid. The rollback phase should then be able to clean up all pending XA transactions. (Bug#46944)

When creating a new instance on Windows using mysqld-nt and the --install parameter, the value of the service would be set incorrectly, resulting in a failure to start the configured service. (Bug#46917)

The test suite was missing from RPM packages. (Bug#46834)

For InnoDB tables, an unnecessary table rebuild for ALTER TABLE could sometimes occur for metadata-only changes. (Bug#46760)

The server could crash for queries with the following elements: 1. An “impossible where” in the outermost SELECT; 2. An aggregate in the outermost SELECT; 3. A correlated subquery with a WHERE clause that includes an outer field reference as a top-level WHERE sargable predicate; (Bug#46749)

InnoDB did not compile using gcc 4.1 on PPC systems. (Bug#46718)

If InnoDB reached its limit on the number of concurrent transactions (1023), it wrote a descriptive message to the error log but returned a misleading error message to the client, or an assertion failure occurred. (Bug#46672)

See also Bug#18828.

A Valgrind error during index creation by InnoDB was corrected. (Bug#46657)

Concurrent INSERT INTO ... SELECT statements for an InnoDB table could cause an AUTO_INCREMENT assertion failure. (Bug#46650)

The Serbian locale name 'sr_YU' is obsolete. It is still recognized for backward compatibility, but 'sr_RS' now should be used instead. (Bug#46633)

On Solaris and HP-UX systems with the environment set to the default C locale, MySQL client programs issued an Unknown OS character set error. (Bug#46619)

SHOW CREATE TRIGGER for a MERGE table trigger caused an assertion failure. (Bug#46614)

DIV operations that are out of range generated an error Error (Code 1264): Out of range value (correct), but also an error: Error (Code 1041): Out of memory (incorrect). (Bug#46606)

If a transaction was rolled back inside InnoDB due to a deadlock or lock wait timeout, and a statement in the transaction had an IGNORE clause, the server could crash at the end of the statement or on shutdown. (Bug#46539)

TRUNCATE TABLE for a table that was opened with HANDLER did not close the handler and left it in an inconsistent state that could lead to a server crash. Now TRUNCATE TABLE for a table closes all open handlers for the table. (Bug#46456)

Trailing spaces were not ignored for user-defined collations that mapped spaces to a character other than 0x20. (Bug#46448)

See also Bug#29468.

The server crashed if a shutdown occurred while a connection was idle. This happened because of a NULL pointer dereference while logging to the error log. (Bug#46267)

Dropping an InnoDB table that used an unknown collation (created on a different server, for example) caused a server crash. (Bug#46256)

The GPL and commercial license headers had different sizes, so that error log, backtrace, core dump, and cluster trace file line numbers could be off by one if they were not checked against the version of the source used for the build. (For example, checking a GPL build backtrace against commercial sources.) (Bug#46216)

A query containing a subquery in the FROM clause and PROCEDURE ANALYSE() caused a server crash. (Bug#46184)

See also Bug#48293.

After an error such as a table-full condition, INSERT IGNORE could cause an assertion failure for debug builds. (Bug#46075)

On 64-bit systems, --skip-innodb did not skip InnoDB startup. (Bug#46043)

InnoDB did not disallow creation of an index with the name GEN_CLUST_INDEX, which is used internally. (Bug#46000)

CREATE TABLE ... SELECT could cause a server crash if no default database was selected. (Bug#45998)

Configuring MySQL for DTrace support resulted in a build failure on Solaris if the directory for the dtrace executable was not in PATH. (Bug#45810)

An infinite hang and 100% CPU usage occurred after handler tried to open a merge table.

If the command mysqladmin shutdown was executed during the hang, the debug server generated the following assert:

mysqld: table.cc:407: void free_table_share(TABLE_SHARE*): Assertion `share->ref_count ==
0' failed.
090610 14:54:04 - mysqld got signal 6 ;

(Bug#45781)

During the build of the Red Hat IA64 MySQL server RPM, the system library link order was incorrect. This made the resulting Red Hat IA64 RPM depend on "libc.so.6.1(GLIBC_PRIVATE)(64bit)", thus preventing installation of the package. (Bug#45706)

The caseinfo member of the CHARSET_INFO structure was not initialized for user-defined Unicode collations, leading to a server crash. (Bug#45645)

Appending values to an ENUM or SET definition is a metadata change for which ALTER TABLE need not rebuild the table, but it was being rebuilt anyway. (Bug#45567)

The socket system variable was unavailable on Windows. (Bug#45498)

The combination of MIN() or MAX() in the select list with WHERE and GROUP BY clauses could lead to incorrect results. (Bug#45386)

Truncation of DECIMAL values could lead to assertion failures; for example, when deducing the type of a table column from a literal DECIMAL value. (Bug#45261)

See also Bug#48370.

Client flags were incorrectly initialized for the embedded server, causing several tests in the jp test suite to fail. (Bug#45159)

Concurrent execution of statements requiring a table-level lock and statements requiring a non-table-level write lock for a table could deadlock. (Bug#45143)

For settings of lower_case_table_names greater than 0, some queries for INFORMATION_SCHEMA tables left entries with incorrect lettercase in the table definition cache. (Bug#44738)

mysqld_safe could fail to find the logger program. (Bug#44736)

The have_community_features system variable was renamed to have_profiling.

Previously, to enable profiling, it was necessary to run configure with the --enable-community-features and --enable-profiling options. Now only --enable-profiling is needed. (Bug#44651)

Some Perl scripts in AIX packages contained an incorrect path to the perl executable. (Bug#44643)

With InnoDB, renaming a table column and then creating an index on the renamed column caused a server crash to the .frm file and the InnoDB data directory going out of sync. Now InnoDB 1.0.5 returns an error instead: ERROR 1034 (HY000): Incorrect key file for table 'tbl_name'; try to repair it. To work around the problem, create another table with the same structure and copy the original table to it. (Bug#44571)

For debug builds, executing a stored procedure as a prepared statement could sometimes cause an assertion failure. (Bug#44521)

Using mysql_stmt_execute() to call a stored procedure could cause a server crash. (Bug#44495)

InnoDB did not always disallow creating tables containing columns with names that match the names of internal columns, such as DB_ROW_ID, DB_TRX_ID, DB_ROLL_PTR, and DB_MIX_ID. (Bug#44369)

An InnoDB error message incorrectly referred to the nonexistent innodb_max_files_open variable rather than to innodb_open_files. (Bug#44338)

SELECT ... WHERE ... IN (NULL, ...) was executed using a full table scan, even if the same query without the NULL used an efficient range scan. (Bug#44139)

See also Bug#18360.

InnoDB use of SELECT MAX(autoinc_column) could cause a crash when MySQL data dictionaries went out of sync. (Bug#44030)

LOAD DATA INFILE statements were written to the binary log in such a way that parsing problems could occur when re-executing the statement from the log. (Bug#43746)

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

See also Bug#47304.

Attempts to enable large_pages with a shared memory segment larger than 4GB caused a server crash. (Bug#43606)

For ALTER TABLE, renaming a DATETIME or TIMESTAMP column unnecessarily caused a table copy operation. (Bug#43508)

The weekday names for the Romanian lc_time_names locale 'ro_RO' were incorrect. Thanks to Andrei Boros for the patch to fix this bug. (Bug#43207)

XA START could cause an assertion failure or server crash when it is called after a unilateral rollback issued by the Resource Manager (both in a regular transaction and after an XA transaction). (Bug#43171)

Redefining a trigger could cause an assertion failure. (Bug#43054)

The FORCE INDEX FOR ORDER BY index hint was ignored when join buffering was used. (Bug#43029)

DROP DATABASE did not clear the message list. (Bug#43012, Bug#43138)

The NUM_FLAG bit of the MYSQL_FIELD.flags member now is set for columns of type MYSQL_TYPE_NEWDECIMAL. (Bug#42980)

Incorrect handling of range predicates combined with OR operators could yield incorrect results. (Bug#42846)

Failure to treat BIT values as unsigned could lead to unpredictable results. (Bug#42803)

For the embedded server on Windows, InnoDB crashed when innodb_file_per_table was enabled and a table name was in full path format. (Bug#42383)

SHOW ERRORS returned an empty result set after an attempt to drop a nonexistent table. (Bug#42364)

If the server was started with an option that had a missing or invalid value, a subsequent error that would cause normally the server to shut down could cause it to crash instead. (Bug#42244)

Some queries with nested outer joins could lead to crashes or incorrect results because an internal data structure was handled improperly. (Bug#42116)

The server used the wrong lock type (always TL_READ instead of TL_READ_NO_INSERT when appropriate) for tables used in subqueries of UPDATE statements. This led in some cases to replication failure because statements were written in the wrong order to the binary log. (Bug#42108)

A Valgrind warning in open_tables() was corrected. (Bug#41759)

In a replication scenario with innodb_locks_unsafe_for_binlog enabled on the slave, where rows were changed only on the slave (not through replication), in some rare cases, many messages of the following form were written to the slave error log: InnoDB: Error: unlock row could not find a 4 mode lock on the record. (Bug#41756)

After renaming a user, granting that user privileges could result in the user having additional privileges other than those granted. (Bug#41597)

The mysql-stress-test.pl test script was missing from the noinstall packages on Windows. (Bug#41546)

With a nonstandard InnoDB page size, some error messages became inaccurate.

(Bug#41490)

In some cases, the server did not recognize lettercase differences between GRANT attributes such as table name or user name. For example, a user was able to perform operations on a table with privileges of another user with the same user name but in a different lettercase.

In consequence of this bug fix, the collation for the Routine_name column of the mysql.proc table is changed from utf8_bin to utf8_general_ci. (Bug#41049)

See also Bug#48872.

When a storage engine plugin failed to initialize before allocating a slot number, it would acidentally unplug the engine installed in slot 0. (Bug#41013)

Optimized builds of mysqld crashed when built with Sun Studio on SPARC platforms. (Bug#40244)

CREATE TABLE failed if a column name in a FOREIGN KEY clause was given in a lettercase different from the corresponding index definition. (Bug#39932)

The mysql_stmt_close() C API function did not flush all pending data associated with the prepared statement. (Bug#39519)

INFORMATION_SCHEMA access optimizations did not work properly in some cases. (Bug#39270)

ALTER TABLE neglected to preserve ROW_FORMAT information from the original table, which could cause subsequent ALTER TABLE and OPTIMIZE TABLE statements to lose the row format for InnoDB tables. (Bug#39200)

Simultaneous ANALYZE TABLE operations for an InnoDB tables could be subject to a race condition. (Bug#38996)

mysqlbinlog had a memory leak in its option-processing code. (Bug#38468)

The ALTER ROUTINE privilege incorrectly permitted SHOW CREATE TABLE. (Bug#38347)

Setting the general_log_file or slow_query_log_file system variable to a nonconstant expression caused the variable to become unset. (Bug#38124)

For certain SELECT statements using ref access, MySQL estimated an incorrect number of rows, which could lead to inefficient query plans. (Bug#38049)

A workload consisting of CREATE TABLE ... SELECT and DML operations could cause deadlock. (Bug#37433)

The MySQL client library mishandled EINPROGRESS errors for connections in nonblocking mode. This could lead to replication failures on hosts capable of resolving both IPv4 and IPv6 network addresses, when trying to resolve localhost. (Bug#37267)

See also Bug#44344.

Previously, InnoDB performed REPLACE INTO T SELECT ... FROM S WHERE ... by setting shared next-key locks on rows from S. Now InnoDB selects rows from S with shared locks or as a consistent read, as for INSERT ... SELECT. This reduces lock contention between sessions. (Bug#37232)

Some warnings were being reported as errors. (Bug#36777)

Privileges for SHOW CREATE VIEW were not being checked correctly. (Bug#35996)

Different invocations of CHECKSUM TABLE could return different results for a table containing columns with spatial data types. (Bug#35570)

Result set metadata for columns retrieved from INFORMATION_SCHEMA tables did not have the db or org_table members of the MYSQL_FIELD structure set. (Bug#35428)

SHOW CREATE EVENT output did not include the DEFINER clause. (Bug#35297)

For its warning count, the mysql_info() C API function could print the number of truncated data items rather than the number of warnings. (Bug#34898)

Concurrent execution of FLUSH TABLES along with SHOW FUNCTION STATUS or SHOW PROCEDURE STATUS could cause a server crash. (Bug#34895)

Executing SHOW MASTER LOGS as a prepared statement without binary logging enabled caused a crash for debug builds. (Bug#34741)

There were spurious warnings about "Truncated incorrect DOUBLE value" in queries with MATCH ... AGAINST and > or < with a constant (which was reported as an incorrect DOUBLE value) in the WHERE condition. (Bug#34374)

A COMMENT longer than 64 characters caused CREATE PROCEDURE to fail. (Bug#34197)

mysql_real_connect() did not check whether the MYSQL connection handler was already connected and connected again even if so. Now an CR_ALREADY_CONNECTED error occurs. (Bug#33831)

INSTALL PLUGIN and UNINSTALL PLUGIN did not handle plugin identifiers consistently with respect to lettercase. (Bug#33731)

The default values for the general query log and slow query log file are documented to be based on the server host name and located in the data directory. However, they were in fact being based on the basename and location of the process ID (PID) file. The name and location defaults for the PID file are based on the server host name and data directory, so if it was not assigned a different name explicitly, its defaults were used and the general query log and slow query log file defaults were as documented. But if the PID file was assigned a value with the --pid-file option, the defaults for the general query log and slow query log file were incorrect. This has been rectified so that the defaults for all three files are based on the server host name and data directory.

A remaining problem is that the binary log and relay log .NNNNNN and .index basename defaults are based on the PID file basename, contrary to the documentation. This issue is to be addressed as Bug#45359. (Bug#33693)

The SHOW FUNCTION CODE and SHOW PROCEDURE CODE statements are not present in nondebug builds, but attempting to use them resulted in a “syntax error” message. Now the error message indicates that the statements are disabled and that you must use a debug build. (Bug#33637)

The LAST_DAY() and MAKEDATE() functions could return NULL, but the result metadata indicated NOT NULL. Thanks to Hiromichi Watari for the patch to fix this bug. (Bug#33629)

Instance Manager (mysqlmanager) has been removed, but a reference to it still appeared in the mysql.server script. (Bug#33472)

There was a race condition between the event scheduler and the server shutdown thread. (Bug#32771)

When an InnoDB tablespace filled up, an error was logged to the client, but not to the error log. Also, the error message was misleading and did not indicate the real source of the problem. (Bug#31183)

ALTER TABLE statements that added a column and added a nonpartial index on the column failed to add the index. (Bug#31031)

For const tables that were optimized away, EXPLAIN EXTENDED displayed them in the FROM clause. Now they are not displayed. If all tables are optimized away, FROM DUAL is displayed. (Bug#30302)

There were cases where string-to-number conversions would produce warnings for CHAR values but not for VARCHAR values. (Bug#28299)

In mysql, using Control-C to kill the current query resulted in a ERROR 1053 (08S01): Server shutdown in progress" message if the query was waiting for a lock. (Bug#28141)

When building MySQL on Windows from source, the WITH_BERKELEY_STORAGE_ENGINE option would fail to configure BDB support correctly. (Bug#27693)

The default database is no longer changed to NULL (“no database”) if DROP DATABASE for that database failed. (Bug#26704)

DROP TABLE for INFORMATION_SCHEMA tables produced an Unknown table error rather than the more appropriate Access denied. (Bug#24062)

SELECT COUNT(DISTINCT) was slow compared with SELECT DISTINCT. Now the server can use loose index scan for certain forms of aggregate functions that use DISTINCT. See Section 7.13.10.1, “Loose Index Scan”. (Bug#21849, Bug#38213)

Referring to a stored function qualified with the name of one database and tables in another database caused a “table doesn't exist” error. (Bug#18444)

A Table ... doesn't exist error could occur for statements that called a function defined in another database. (Bug#17199)