Saturday, October 3, 2015

Fun with Bugs #37 - Bugs fixed in MySQL 5.6.27

MySQL 5.6.27 was released on September 30 formally. Source code is also available on GitHub, and I have it compiled (some users are less lucky) and running for a couple of days already. In this post I'll comment on some bugs reported by MySQL Community that are fixed there.

I'd like to start with a couple of bugs where patches were also contributed. First of all, the fix suggested by Stewart Smith in Bug #72811, "Set NUMA mempolicy for optimum mysqld performance", helps to allocate memory in a more reasonable way on NUMA-enabled systems. Previously it was like all interleaved or nothing, now there is a way to apply this only to the InnoDB buffer pool. The bug was formally verified by Umesh.

Alexey Kopytov had contributed two important fixes, for Bug #76927, "Duplicate UK values in READ-COMMITTED (again)" that was verified by Shane Bester, and for Bug #74891 (reported by Stewart Smith and verified by Umesh).

As a side note, I like to see fixes for other platforms than x86_64 and other OSes than Linux. Historically I started to work on MySQL by checking compilation or build bugs for unusual platforms, compilers and environments... There is one more bug of this kind fixed in 5.6.27: Bug #76135 , "data corruption on arm64", reported by Daniel Frazier.

Other important InnoDB bug fixes include:
  • Bug #77743, "Auto-increment sequence gets reset", reported by my colleague Marcos Albe and verified by Umesh
  • Bug #75185, "lower_case_table_names=0 on windows leads to problems", reported by Shane Bester and verified by Umesh

A lot of replication bugs were fixed in this release:
  • Bug #78389, "5.6.24: Lost data during master restart if partial transact has been downloaded", reported by Simon Mudd and verified by Shane Bester.
  • Bug #74607, "slave io_thread may get stuck when using GTID and low slave_net_timeouts", reported by Santosh Praneeth Banda and verified by Umesh
  • Bug #76959, "Gaps in Retrieved_Gtid_Set while no gaps in Executed_Gtid_Set", reported by my colleague Sveta Smirnova and verified by Umesh
  • Bug #76727, "Slave assertion in unpack_row with ROLLBACK TO SAVEPOINT in error handler", reported by Philip Stoev and verified by Umesh. In Percona we found out hard way that while one can create a trigger that does ROLLBACK TO SAVEPOINT in the code (see related documentation Bug #77163) and it even seems to work as expected, this leads to problems on slave replicating such a transaction. This fix in 5.6.27 is just a start of the long way to proper fixes in all places, and those interested in that's wrong can check great analysis by Vlad Lesin in Percona Server bug #1483251
  • Bug #76618, "SHOW BINLOG EVENTS completely locks down writes to binlog, hence transactions", reported by Shlomi Noach and verified by Shane Bester
  • Bug #76493, "Binlog statement is not ignored", reported by Dan Lukes and verified by Umesh
  • Bug #76379, "binlog_error_action doesn't handle some failures during binlog rotation", reported by Santosh Praneeth Banda and verified by Umesh
  • Bug #74950, that is still private at the moment (had I written before that I hate when this happens?), is described as follows in release notes:
    "Modifying the master_info_repository or relay_log_info_repository inside a transaction and later rolling back that transaction left the repository in an unusable state. We fix this by preventing any modification of these repositories inside a transaction."
    This is not the last remaining bug related to "crash-safe" replication in 5.6, implemented by storing information in explicit InnoDB tables in mysql database. Changes there are a part of transaction and when changes of transaction are rolled back, we often end up with replication broken in some way or in some otherwise bad state. It seems we are doomed to see these problems until MySQL implements some "micro transactions" for its "data dictionary", that are handled differently than normal transactions to usual InnoDB tables... One day I'll write a separate blog post on all the related bugs already identified
  • Bug #74089, "Report Relay_Log_File and Relay_Log_Pos on relay-log-recovery", is a nice feature request by Jean-François Gagné that was verified by Luis Soares himself, and implemented in 5.6.27. Well done!
  • Bug #73806, still private, and Bug #76746, "Broken replication on SQL thread restart if gtid_mode is enabled", reported by Davi Arnaut who also contributed a patch, and verified by Umesh
  • Bug #68525, "Error "When GTID_NEXT is set to a GTID" ROW based replication", reported by Nogueira Jesus and verified by Sveta Smirnova
I find it important to note that most replication bugs fixed in 5.6.27 were found by MySQL Community users, NOT internally by Oracle. I am also surprised with so many replication bugs still existing in 5.6.x now, 2+ years after GA release. I wonder what's going to happen when new replication features of 5.7 will be tested by MySQL users...

Several optimizer bugs were also fixed:
  • Bug #77135, "Update on varchar and text columns produce incorrect results", reported by Chris Sims and formally verified by Sinisa Milivojevic
  • Bug #76349, "memory leak in add_derived_key()", reported by Vlad Lesin and verified by Shane Bester
  • Bug #75248, "OR conditions on multi-column index may not use all index columns to filter rows", reported by Yoshinori Matsunobu and verified by Umesh

Performance Schema also had got some fixes:
  • Bug #77577, "setup_timers initialization assumes CYCLE timer is always available", reported by Alexey Kopytov who also contributed a patch, and verified by Umesh
  • Bug #74614, "events_statements_history has errors=0 when there are errors", reported by DaniĆ«l van Eeden and verified by Umesh 
I'd also want to mention Bug #76480, "mysqlimport --use-threads doesn't use multiple threads", reported by my colleague Miguel Angel Nieto that got a detailed analysis from yours truly and then was verified by Umesh. That's all I contributed to the release :)

The last but not the least, if you use memcached to work with InnoDB tables, check Bug #75199, "MySQL crashed because of flush_all", reported by Zhai Weixiang and verified by Umesh (as most of the community bug reports for a couple of last years).

MySQL 5.6.27 seems to be a really useful and important release for those who rely on InnoDB and replication (that is, for all of us probably). Note that most of the bugs mentioned above also affected 5.7.x and are fixed in upcoming MySQL 5.7.9 and 5.8.0 releases.

1 comment: