Fun with Bugs #31 - what's new in MySQL 5.6.17

MySQL 5.6.17 will probably be announced loudly at or immediately before Percona Live MySQL Conference & Expo next week. But official release announcement via email was made on March 28, release notes and binaries to download are already available, so why not to check them carefully to find out what to expect from this 8th minor release of MySQL 5.6 GA...

First of all, it seems Oracle still does not hesitate to introduce new features and behavior in the process. Just check these major changes:

• Starting with 5.6.17, MySQL now supports rebuilding regular and partitioned InnoDB tables using online DDL (ALGORITHM=INPLACE) for OPTIMIZE TABLES, ALTER TABLE ... FORCE and ALTER TABLE ... ENGINE=InnoDB. Rebuilding tables (to defragment them and free some disk space mostly) is probably as popular kind of ALTER as adding new columns or changing indexes, so it's great to see a built-in way to do these ALTERs without blocking DML (well, assuming the table does not have foreign keys at least). Previously one had to rely on third party tools like pt-online-schema-change for these cases.
• "The AES_ENCRYPT() and AES_DECRYPT() functions now permit control of the block encryption mode and take an optional initialization vector argument ... These changes make statements that use AES_ENCRYPT() or AES_DECRYPT() unsafe for statement-based replication and they cannot be stored in the query cache." So, we should expect more warnings now for STATEMENT-based replication setups, but has more secure implementation now. Note that having the details of call to these functions visible in the logs make them less secure, as pointed out in recent Bug #72158.

Oracle also continues to push deprecation of features actively:

• "The deprecated ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE SQL modes now do nothing. Instead, their previous effects are included in the effects of strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES)." - it seems it's time to check all the code and remove these modes, as they may just disappear in 5.6 even before MySQL 5.7 GA with this kind of activity.
• "The IGNORE clause for ALTER TABLE is now deprecated and will be removed in a future version of MySQL." - as this deprecation happened in 5.6.x minor release probably the clause will be removed well before 5.8. So it's not like your code is safe for next 2+ years.

Speaking about fixes for public bugs (these I mostly care about), I consider the following as most important:

• Bug #71197. "mysql slave sql thread memory leak". I've pointed out this bug this week (and not for the first time) as a possible explanation for the OOM conditions in big replication cluster. It's really nice to see it fixed!
• Bug #70669. "Slave can't continue replication after master's crash recovery", even with sync_binlog=1. This is now fixed.
• One more fix in replication: Bug #68429 - "When running the slave with --slave-parallel-workers at 1 or greater, setting --slave-skip-errors=all caused the error log to be filled with with instances of the warning Slave SQL: Could not execute Query event. Detailed error: ;, Error_code: 0."
• Bug #69680. "Auto_inc value not properly generated with RBR and auto_inc column only on slave". It was reported by my colleague Ovais Tariq long time ago and is now fixed. 
• Bug #71436. "Compressed Tables Regression from 5.6.13". Finally this performance regression when large InnoDB compressed tables are used is fixed. It was reported by my colleague Jervin Real.
• Bug #70430. "Race condition between purge coordinator and InnoDB monitor crash on shutdown". It was reported by Laurynas Biveinis from Percona.
• Bug #70063. "Create table is slower in 5.6". It was reported by my former colleague Arnaud Adant from Oracle and the problem was tracked down to inserts into the mysql.innodb_index_stats table (one of two to store InnoDB persistent statistics). Maybe this fix will help to run CREATE or ALTER for partitioned tables faster as well - the problem reported recently as a regression comparing to MySQL 5.5 as Bug #72115 - "Very slow create/alter table with partitions and InnoDB".
• Bug #65225. "InnoDB miscalculates auto-increment after changing auto_increment_increment" - this one was reported by Elena Stepanova almost 2 years ago and was considered a regression comparing to... MySQL 5.0. Nice to see that we have one less case when old MySQL 5.0 can be considered "better" that recent greatest MySQL 5.6 :)

There are more important fixed in replication and InnoDB categories. Fixes in optimizer are less visible in release notes, but these are really important optimizer bugs that are fixed finally:

• Bug #70657. "SELECT DISTINCT...GROUP BY returns wrong results in some cases".
• Bug #69969. "Failing assertion: prebuilt->trx->conc_state == 1 from subselect". It was reported by my colleague Raghavendra Prabhu.
• Bug #71244. "Wrong result computation using ALL() and GROUP BY".

The last but not the least, for a second release in a row Oracle engineers had fixed something I've reported (even though it's minor enough problem):

• Bug #71554. Release notes says: " innodb_ft_result_cache_limit now has a hardcoded maximum value of 4294967295 bytes or (2**32 -1). The maximum value was previously defined as the maximum value of ulong." One of those cases when fixing the server code happens as a result of a simple documentation request.

To summarize, MySQL 5.6.17 seems to be a very good release for those who use new InnoDB and replication features in MySQL 5.6. It fixed several regressions and (assuming no new regression bugs introduced) should be implemented everywhere as soon as possible.