Fun with Bugs #29 - important bug fixes in MySQL 5.6.16

As you should already know, Oracle had released MySQL 5.6.16 officially on January 31, 2014. You can find all the details in official release notes. In this post I decided to concentrate on important fixes for bugs reported by community in 4 most important categories: InnoDB, replication, partitioning and optimizer.

Let's start with Bug #70768, "Persistent optimizer statistics often causes LOCK_open stalls". It's well known that persistent statistics for InnoDB tables stored in two (again, InnoDB) tables in mysql database may cause various problems, not only bad plans based on outdated statistics there. One of these problems seems solved in 5.6.16, but note that the bug report itself is closed without any specific comment on what exactly was fixed.

Hardly you are affected by bugs like Bug #70087, "InnoDB can not use the doublewrite buffer properly", every day. But it's great that yet another case of corruption when recovery is possible is now covered. Another problem in case of InnoDB recovery on a server where crash-safe (with information stored in tables) replication is configured was reported as Bug #69907. Here instead of a real fix (if it's at all possible) we've just got more clear error message explaining the inability to store replication related information in tables while innodb_force_recovery > 0.

Having ALTER TABLE statement on InnoDB to work "in place", without making copy of the entire table, is a great feature. Unfortunately for some cases it had not performed the change expected. One of this cases, resetting AUTO_INCREMENT with ALTER TABLE, now works again without explicitly mentioned ALGORITHM thanks to the fix for Bug #69882. Good to see this kind of a regression comparing to older MySQL versions fixed. Another fix of a regression bug, Bug #69424, finally allowed to use raw devices for InnoDB shared tablespace in MySQL 5.6. Not that it's common these days, but still could cause problems during upgrade.

Yet another bug in ALTER TABLE, Bug #69325, noted and reported by my colleague Justin Swanhart, could badly affect users with big partitioned InnoDB tables. Attempts to add indexes to such a table caused huge and unexpected use of memory. Now the amount of memory used is somewhat reduced it seems.

The last but not the least, Oracle fixed my Bug #68079! A year of work, detailed analysis, workarounds suggested and now a real fix with totally new implementation! Well done, really! I should test the fix tomorrow. It's not the first time I write about this bug (I've created entire presentation around it), and not the last...

For partitioning itself (besides InnoDB-related cases mentioned above) MySQL 5.6.16 had not added many fixes. Still, fix for Bug #70588 should be mentioned, as now we have one less case of wrong results against partitioned table. I am surprised badly that, for example, fix for my Bug #71095 (that is public since December 6, 2013, and really helped to solve a problem) is NOT included.

In replication category Oracle engineers were mostly concentrated on fixing bugs reported internally. I see only this (looking minor enough) semi-sync replication public bug fixed, Bug #70410.

I had not expected many fixes in optimizer, but there are fixes to mention. Check Bug #69005, for example, if you order by columns with utf8_bin collation. Now this regression bug is fixed and order should be correct. Another case of ORDER BY failing, this time with views, is fixed in frames of Bug #69678.

Some other optimizer bug fixes to check are the following:

• Bug #70025, "Complex updates of Performance Schema tables involving joins or subqueries failed to update every row."
• Bug #71028, "COUNT(DISTINCT) sometimes produced an incorrect result when the last read row contained a NULL value."
• Bug #70608, "Incorrect result could be returned for a query with an IF() predicate in the WHERE clause combined with OUTER JOIN in a subquery that is transformed to a semi-join.". It was a regression comparing to 5.5, so more good news to those who plan to upgrade.

 There are some other fixes I can not comment on right now, as bug report still remains private. So, we can only trust release notes in cases like this:

"A full-text search combined with derived tables (subqueries in the FROM clause) caused a server exit.

Now if a full-text operation depends on a derived table, the server produces an error indicating that a full-text search cannot be done on a materialized table. (Bug #68751, Bug #16539903)"

To summarize, 5.6.16 seems useful release, with many fixes in InnoDB and optimizer, including fixes to regression bugs and performance improvements. I had not tested it in production yet, but at least upgrade from 5.6.15 on Windows was seamless and I have it up and running for further checks. Stay tuned!