Fun with Bugs #38 - Regression Bugs in MySQL 5.6

I often have to reply to questions related to upgrade from, say, MySQL 5.5.x to latest MySQL (or Percona) Server 5.6.x (5.6.27 for MySQL and 5.6.26 for Percona at the moment). One of them is sometimes about "any known bugs" that may affect a user after upgrade.

One may assume that now, 2 years and 8 months after the first GA release of MySQL 5.6.10, there should be very few bugs (we call them regression bugs) of a kind that is interesting for the user planning upgrade from 5.5.x. I checked yesterday and found out it's not the case actually. It took me half an hour to end up with the list of more than 20 regression bugs that are still "Verified", so may affect even the latest recent release, 5.6.27. I had not checked them one by one on 5.6.27 yet (it will take more than half an hour probably), but still would like to present them, classified into separate categories.

First of all, there are performance regressions of various kinds and impact. Mostly they are about single thread performance (that is well known to be worse in 5.6 and may influence replication slaves badly) or related to negative effects of new optimizer features that are enabled by default in 5.6, but sometimes reasons are different or not clear:

• Bug #68825 - "performance regressions for single-threaded workloads". It was not the first result in my Google search, but this is a classical bug report by Mark Callaghan. There are many test results and observations, regressions continue in 5.7.8. Useful reading. With this bug in mind we all should assume by default that performance of single thread processing is expected to go down with each major MySQL release...
• Bug #76933 - "Performance Regression in 5.6: Update does not use index". As simple as that, some update is fast in 5.5.x (and 5.7.x), but is slow in 5.6 as full table scan is used. There are no comments about the reason. Pure regression clearly marked with a "regression" tag.
• Bug #69350 - "performance regression between 5.5 and 5.6 for simple statement in routine loop". Your stored procedures may run slower in 5.6 comparing to 5.5. Note that this bug report does NOT have a usual "regression" tag, so search by tag is not enough in general.
• Bug #68979 - "performance regression of MySQL 5.6.10?". If you use derived tables, they are no longer materialized at optimization stage (due to "Delayed materialization of derived tables" feature of 5.6), so optimizer is not able to make some choices early.
• Bug #69801 - "performance regression between 5.5 and 5.6 for str_to_date function". 5.7 is also affected. No "regression" tag.
• Bug #76247 - ''Regression case with semijoin - query with many INs stuck on statistics state". This is the only "Not a bug" in my list. We can continue arguing is it a bug or not, but you should expect the problem in 5.6 and 5.7 with default settings (some queries may just "hang" forever or for a long time, those that were executed fast enough in 5.5 or older versions) and check the list of workarounds, from disabling semijoin optimization to query rewrite or setting optimizer_prune_level to 1.
• Bug #68919 - "Performance regression when DS-MRR is used for query with small limit". The workaround is to set mrr=off for this specific kind of queries. No "regression" tag.
• Bug #69219 - "CREATE TEMPORARY TABLE ... SELECT is slower on 5.6 than on 5.1". Bug title mentions 5.1, but actually regression was noted comparing to 5.5. Nobody cared to work on this any further, so the reason is not clear, but note that this happens, to different extents, to both InnoDB and MyISAM storage engines used for these explicitly created temporary tables.

What I consider the worst kind of regressions, is a wrong results bugs, just (usually undocumented in any clear way) change in behavior out of nothing that leads to different data (or metadata) returned by some queries. The fact that such bugs exist tells a lot about the processes of development, documenting and QA:

• Bug #76355 - "Function "addtime" returns wrong column type (regression)." No "regression" tag, 5.7 is also affected.
• Bug #70491 - "SELECT DISTINCT may return a wrong result if a join buffer is involved". I remember more bugs for SELECT DISTINCT, with wrong results, but this one remains "Verified" 2 years after it was reported. Nobody cares.
• Bug #75447 - "INSERT into a view sometimes ignores default values". 5.7 is also affected, unlike 5.5. It seems noted by a developer actually, and we have a clear "regression" tag.
• Bug #70091 - ''tinyint(1) column type returned by jdbc connector becomes Integer with ORDER BY". Regression happened in 5.6.13, so obviously some tests are missing when this happens during GA bug fixing cycle.
• Bug #68972 - "Can't find temporary table". You can call some procedures that create temporary tables, among other things, just once. Second call fails, and this was not the case in 5.5. Several users claim to be affected since it was reported.
• Bug #75668 - "Use of Distinct, Group_concat, and Group by together produces 1 NULL row result.". Classical wrong results case (unexpected results) in default installation. Affects 5.6 and 5.7 it seems.

This kind of bugs is bad in general as one can hit the bug any time, unless all code paths in application are covered by proper test cases that are checked on every upgrade. No 3rd party benchmarks or reading the manual carefully may help, only code fully covered with tests (somethings that MySQL vendors seem not to have!).

There are other regressions/unexpected changes in behavior that affect old familiar environments or procedures:

• Bug #74908 - "Unable to detect network timeout in 5.6 when using SSL (regression from 5.5)". In case of network problem in between slave and master, slave connected via SSL no longer restarts after slave_net_timeout seconds passed. The slave status will continue to state "waiting for master to send event". The slave will remain in this state until the slave is stopped and restarted explicitly. 
• Bug #77227 - "mysqld_multi stop does not work". Regression happened in 5.6.25 it seems. So, while upgrading from some old 5.5.x to latest and greatest 5.6.x usually allows to hit fewer bugs in 5.6, some 5.6-specific regressions also happen sometimes, and they may affect you badly during upgrade. 
• Bug #72108 - "Hard to read history file". See also Bug #69991 and Bug #68925.

Other regressions in InnoDB were just unexpected:

• Bug #72851 - "Fix for bug 16345265 in 5.6.11 breaks backward compatibility for InnoDB recovery". Read it entirely, but basically remember to do a clean shutdown in older versions before upgrading to 5.6 (or 5.7) in place.
• Bug #77128 - "Regression in dealing with filesystem limits". InnoDB just works differently now on filesystems like ext3 (or fat32) when file size limit is hit by autoextending file from shared tablespace. In 5.5 there was an easy way to proceed, now you end up with error messages and no way to start. Note that at least 5.7.7 was also affected. There is no "regression" tag.
• Bug #74609 - "Unable to update Foreign Key created as NOT NULL to table named with a Dollar $". 5.7 is also affected. 
• Bug #77654 - "After 5.6.15 innodb_force_recovery greater or equal than 4 is useless". What else to add, it just does not start...

Some regressions are probably just formal and irrelevant to any real life use cases, but still weird to see them happening:

• Bug #71477 - "transaction_allow_batching is available in non-Cluster MySQL Server 5.6". Nobody cares, I know, but it was not like that in 5.5...

To summarize, one has to search carefully for known regression bugs in 5.6.x before upgrading. Performance regressions may happen for almost any load and should be expected. But problem with stored procedures, built-in functions, replication and even command line utilities are also possible, and most of them are NOT explained in any section of the manual.

Same applies to 5.7 as well (some of the bugs above are regressions in 5.7 comparing to 5.5 or older versions). I plan to write another blog post about regressions in 5.7 comparing to 5.6, as well as problems one should expect during upgrade, documented or not.

Take care.