MySQL Upgrade Gotchas: 5.5 to 5.6

New defaults

Starting with MySQL 5.6.6, several MySQL server parameters have defaults that differ from the previous version, MySQL 5.5. The main reason for these changes is to provide better out-of-box performance and to eliminate the need for the database administrator to change settings manually.

In MySQL 5.6, a parameter could have a different static default value or the server could autosize a parameter at startup using a formula based on other related parameters or server host configuration. An example is the back_log setting which is now at its previous default of 50 and automatically adjusted up by an amount proportional to the value of max_connections. The advantage of autosizing is that when the server has information available to make a decision about a parameter, it auto-adjusts the parameter setting to be better than the fixed default.

MySQL Enterprise Edition

Now, starting with MySQL 5.6.10, MySQL Enterprise Edition is available for MySQL 5.6. Specifically, MySQL Enterprise 5.6.10 includes these components previously available only in MySQL 5.5: MySQL Enterprise Security (PAM and Windows authentication plugins), MySQL Enterprise Audit, and MySQL Thread Pool. For more information on this, see MySQL Enterprise Edition.

InnoDB enhancements

There are some enhancements in InnoDB. The performance in InnoDB has been improved via optimization of read operations for compressed tables by skipping redundant tests. Previously, the check for whether any related changes needed to be merged from the insert buffer was being called more often than necessary. Also, after a clean shutdown, InnoDB did not check .ibd file headers at startup.

As a result, in a crash recovery scenario, InnoDB could load a corrupted tablespace file. Now, MySQL 5.6 implements consistency and status checks to avoid loading corrupted files.

Error logging improvements

There are some improvements in the error logging. In earlier versions of MySQL, error messages for ALTER TABLE statement using a LOCK or ALGORITHM value not supported for the given operation were very generic. Now in MySQL 5.6, the server produces more informative messages.

Also, previously, if multiple statements were sent in a single request, the audit log plugin logged only the last one. Now it logs each statement separately. Again, when one or more GTID log events but no previous GTIDs log events were found in the binary log, the resulting error was mishandled and led to a failure of the server. Now in such cases, an appropriate error is issued, and is handled correctly.

The MySQL connections have been improved, since the mysql_config_editor now supports — port and — socket options for specifying TCP/IP port number and Unix socket file name. The MySQL upgrade process works perfectly without errors like before. Now MySQL upgrades the system tables in the mysql database first before tables in other databases. This resolves the issue that results from upgrading user tables before the system tables.

In replication of MySQL 5.6, in the case of a serious unhandled error during an ALTER TABLE operation that copies the original table, any data that could be needed for data recovery is preserved. In addition, utilizing row-based logging to replicate from a table to a same-named view resulted in a failure on the slave.

Now, when using row-based logging, the target object type is checked prior to performing any DML, and an error is given if the target on the slave is not actually a table. Repeated execution of CHANGE MASTER TO statements using invalid MASTER_LOG_POS values could lead to errors and possibly a crash on the slave. Now in such cases, the statement fails with a clear error message.

Partitioning pruning enabled

In partitioning of MySQL 5.6, partition pruning is now enabled for tables using a storage engine that provides automatic partitioning, like the NDB storage engine, which is also explicitly partitioned. Previously, pruning was disabled for all tables using such a storage engine, whether or not the tables had explicitly defined partitions. In addition, as part of this fix, explicit partition selection is now disabled for tables using a storage engine (such as NDB) that provides automatic partitioning.

SSL checks

SSL mechanism in MySQL 5.5 was improved through correction of OpenSSL-related Valgrind warnings and fixation of OpenSSL-related memory leaks. During client connection processing, the server now performs password-expiration checking after SSL checks.

If you require a MySQL upgrade from 5.5 to 5.6, Datavail can help. With more than 600 database administrators worldwide, Datavail is the largest pure-play database services provider in North America. With 24×7 managed database services, including database design, architecture and staffing, Datavail can support your organization as it works with MySQL, regardless of the build you ultimately select.