Thursday, December 31, 2015

New Year Wishes for Providers of MySQL Support Services

Three years ago I shared my wishes for customers of Oracle's MySQL Support Services. There I basically asked them to report any problem that they suspect to be caused by the bug in MySQL software at year I want to share wishes mostly for myself (and other providers of MySQL Support services).

I have a job of MySQL Support Engineer for almost 10.5 years. I did it in MySQL AB, Sun, Oracle and Percona. I had enough opportunities to see all kinds of approaches, types, kinds and qualities of services. But I still have some dreams in this area that I'd like to see fulfilled for both myself as a provider of service and for customers of such a service:
  1. I wish to see MySQL Support mostly done in an asynchronous way, via emails and (when absolutely needed and possible) remote login sessions.

    In most cases it's enough for customer to know that she will get a detailed, best possible answer to any her initial question (or problem statement) or any followups question or request in a predictable, well defined time. There is no need for engineer and customer to always work in sync, by talking on phone, chatting or doing a shared screen sessions.

    Support should work the same way UNIX operating system does: by sharing all available resources (engineers) among all tasks (support requests) at hand, allocating resources for the task for some small amount of time and then forcing the resource to switch to other task, either when time unit allocated is ended or immediately when we have to wait for something to complete. Surely this mode is beneficial for support providers (because of ability to work for more customers concurrently than they have engineers online), but customers also get clear benefits. They can move on and work on something else until they get email back (or time to get a reply passes), and they may get a reply based on concurrent (but asynchronous) work of several engineers ("fan-out").

  2. At the same time, I wish each support provider to have a well defined SLA (time of getting a guaranteed useful technical reply, either a solution, suggestion or further question) not only for the initial reply (as we can see here and, honestly, almost everywhere), but also for the followups, for each and every customer email.

    Ideally both sides should be able to negotiate the date(time) of the next reply (even if it's different from formal official SLA), and then make sure to meet this deadline in 100% of cases. Some steps towards this goal are visible here, but so far no well know Support provider is perfect with followups in time, based on my knowledge.

  3. I wish Support engineers to never be involved in phone conferences with customers without a clearly defined agenda related to MySQL and limited time to be spent on phone (see item 1 above for the reasons).

    Sometimes somebody from "services" side should be "there", in case of questions during some long discussion. I think this is a job for customer's TAM (technical assistance manager), Sales Engineer (if the topic is related to purchasing some service or software) or anyone who is paid per hour (like Consultant).

  4. I wish Support engineers, no matter what Support provider they work for, to always report upstream MySQL bugs at and fork-specific bugs at their public bug trackers, as openly available (public) to all MySQL users.

    Some bugs may be repeatable only with customer-specific and confidential data, and some bugs may have security implications. Ideally, Support engineers should always work on a repeatable test case or otherwise well grounded bug report NOT containing customer data. As for security problems, there is always a way to explain in public important details of the possible security attack vector and list versions affected, without giving enough details for "script kiddies" to just blindly copy-paste the test case to get unauthorized access or crash well-managed public MySQL server.

  5. I wish Support engineers to present their work and share their experience in public.

    We all should try to share knowledge we have and get while working with customers, not only internally to our colleagues in services or via internal knowledge bases, but also in our own blogs, articles, on public MySQL forums and on MySQL-related conferences.

    MySQL Support providers should encourage support engineers to make the results of their work public whenever possible. Not only bugs, but problem solving approaches, code written (if any), experience gained should be shared with MySQL community. This will give us all customers who known more about MySQL and will help us not to re-invent the wheel.
To summarize, I wish our customers in the New Year of 2016 to get a simple, but well-defined, responsible, and reliable 24x7 Support service provided by the engineers who are well known to the Community based on their public work on MySQL (via blog posts, bug reports and conference presentations). I wish all MySQL Support Service providers to deliver what they promise (or more) in 100% of cases. I wish myself to work for MySQL Support Provider that cares about my wishes and tries to help me to see my dreams expressed here coming true.

Happy New Year, MySQL Community!

Saturday, December 19, 2015

Tricking the Optimizer, or How Checking Bug Reports Help to Solve Real Problems

I've got several useful habits over the years of work in MySQL Support. One of them is to start working on every problem with search for known MySQL bugs related to the problem at hand. I'd like to share one recent case where this habit helped me to get a solution for customer almost instantly.

It was one of rare cases when customer opened a support request with a very clear question and even a test case. The problem was described very precisely, more or less as follows (with table and column names, and data changed for this blog post, surely).

Let's assume we have two tables created like these:

mysql> create table t1(id int auto_increment primary key, c1 varchar(2), c2 varchar(100));Query OK, 0 rows affected (0.27 sec)

mysql> create table t2(id int auto_increment primary key, t1_id int, ctime datetime, cvalue decimal(10,2), key(t1_id, ctime));
Query OK, 0 rows affected (0.15 sec)

So, we have a couple of tables with assumed (even if not formally declared) relation, of a "master-details" kind. We have few rows in the "master" table:

mysql>  insert into t1(c1,c2) values ('UA','Val'),('US','Tom'),('UK','Jerry');
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

and usually several (or very many) rows for each row from "master" in the "details" table:

mysql>  insert into t2(t1_id, ctime, cvalue) values(1,NOW(),10),(1,NOW(),20),(1,NOW(),30),(2,NOW(),10),(2,NOW(),50),(3,NOW(),100);
Query OK, 6 rows affected (0.07 sec)
Records: 6  Duplicates: 0  Warnings: 0

Just to double check, this is what we have to begin with in our tables:

mysql> select * from t1;
| id | c1   | c2    |
|  1 | UA   | Val   |
|  2 | US   | Tom   |
|  3 | UK   | Jerry |
3 rows in set (0.01 sec)

mysql> select * from t2;
| id | t1_id | ctime               | cvalue |
|  1 |     1 | 2015-12-19 17:30:59 |  10.00 |
|  2 |     1 | 2015-12-19 17:30:59 |  20.00 |
|  3 |     1 | 2015-12-19 17:30:59 |  30.00 |
|  4 |     2 | 2015-12-19 17:30:59 |  10.00 |
|  5 |     2 | 2015-12-19 17:30:59 |  50.00 |
|  6 |     3 | 2015-12-19 17:30:59 | 100.00 |
6 rows in set (0.00 sec)

Now, let's try to get many more rows to the "details" table (do not mind the warnings, I did that on the server with binary log enabled and binlog_format = STATEMENT):

mysql>  insert into t2(t1_id, ctime, cvalue) select t1_id, NOW(), cvalue from t2;
Query OK, 6 rows affected, 1 warning (0.04 sec)
Records: 6  Duplicates: 0  Warnings: 1


mysql>  insert into t2(t1_id, ctime, cvalue) select t1_id, NOW(), cvalue from t2;
Query OK, 196608 rows affected, 1 warning (25.63 sec)
Records: 196608  Duplicates: 0  Warnings: 1

Now, we have index on both (t1_id, ctime) columns in our t2 table, so queries like the following are executed in a fast and efficient way:

mysql> explain select * from t1 join t2 on = t2.t1_id where = 3 and ctime between '2015-12-19 17:30:59' and '2015-12-19 17:31:10'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: range
possible_keys: t1_id
          key: t1_id
      key_len: 11
          ref: NULL
         rows: 1
        Extra: Using index condition
2 rows in set (0.01 sec)

mysql> select sql_no_cache * from t1 join t2 on = t2.t1_id where = 3 and ctime between '2015-12-19 17:30:59' and '2015-12-19 17:31:10';
| id | c1   | c2    | id | t1_id | ctime               | cvalue |
|  3 | UK   | Jerry |  6 |     3 | 2015-12-19 17:30:59 | 100.00 |
1 row in set (0.00 sec)

Note range access and both columns used for the t1_id index of the t2 table. But what if we specify rows from the "master" (t1) table indirectly? Like this:

mysql> explain select * from t1 join t2 on = t2.t1_id where t1.c1 = 'UK' and ctime between '2015-12-19 17:30:59' and '2015-12-19 17:31:10'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: ref
possible_keys: t1_id
          key: t1_id
      key_len: 5
         rows: 1
        Extra: Using index condition
2 rows in set (0.00 sec)

mysql> select sql_no_cache * from t1 join t2 on = t2.t1_id where t1.c1 = 'UK' and ctime between '2015-12-19 17:30:59' and '2015-12-19 17:31:10';
| id | c1   | c2    | id | t1_id | ctime               | cvalue |
|  3 | UK   | Jerry |  6 |     3 | 2015-12-19 17:30:59 | 100.00 |
1 row in set (0.22 sec)

We see that all rows from the "master" table are checked (this is expected, and we have only 3 there anyway), but then we see "ref" access via t1_id index of the "details" (t2) table, and, what's more important, we get the result notably slower than before.

Now the question: Why we are only using the t1_id element of the compound key? This becomes an issue when the leading element of the key is not highly selective.

Another question: Is there any way we can prompt the optimizer to make better use of the index so that when it has evaluated the t1_id values it scans the t2 table using the full depth of the index?

So, this was the customer request I've got. Based on my good habit, I started to search for known optimizer bugs, using the following search string in Google: join range compound index

On the very first page of results I've got a link to the following bug (that was reported by my former colleague Justin Swanhart, probably for some other customer issue, and maybe we even discussed it in the past with him, but still, I just googled for the string above, without any specific details in mind):
  • Bug #70002 - "very low performance join - eq_ref and ref access broken for compound indexes"
The bug was declared a "Duplicate" of an older Bug #52030, "WHERE clause with != primary key check slows a query from 0.06 sec to 4 min", and their similarity was far from clear to me. That older bug was reported for 5.1.x and still is "Verified". But there was no need to understand the relation, as the last comment (by Jørgen Løland) in the bug I found stated:
"Thanks for the bug report. This looks like a duplicate of BUG#52030 but with a much less obscure reproducible. I'll link the two bugs together and request that the implementor verifies your test case as well when the issue has been fixed.

A short summary of what happens: MySQL can only do '[eq_]ref' access if the comparison operator is =. That happens to be the case for the first keypart. If MySQL is to make use of the BETWEEN predicate when looking up rows in the index, 'dynamic range' access has to be used. This is the kind that says "Range checked for each record (index 0x...)" in EXPLAIN. 'Dynamic range' is more costly to use than 'ref' because parts of the optimizer has to be invoked for every row in the referred-to table (cust_car in this case). Because of this higher cost, 'dynamic range' will not be chosen by MySQL if 'ref' access is possible. Admittedly, this heuristic sometimes fail like in this bug report."
This gives the answer to the first customer question: we are using only the first column of the compound index there is a heuristic inside optimizer that prefers ref access, and this can be considered a bug/known limitation of current MySQL's optimizer.

Moreover, I've immediately tried to trick optimizer to use dynamic range access path:

mysql> explain select * from t1 join t2 on ( >= t2.t1_id and <= t2.t1_id) where t1.c1 = 'UK' and ctime between '2015-12-19 17:30:59' and '2015-12-19 17:31:10'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: ALL
possible_keys: t1_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 392850
        Extra: Range checked for each record (index map: 0x2)
2 rows in set (0.00 sec)

mysql> select * from t1 join t2 on ( >= t2.t1_id and <= t2.t1_id) where t1.c1 = 'UK' and ctime between '2015-12-19 17:30:59' and '2015-12-19 17:31:10';
| id | c1   | c2    | id | t1_id | ctime               | cvalue |
|  3 | UK   | Jerry |  6 |     3 | 2015-12-19 17:30:59 | 100.00 |
1 row in set (0.01 sec)

and, as you can see, got both the desired plan (on MySQL 5.6.27) and fast execution.

So, I've immediately suggested to customer to use the trick of replacing the join condition with the one that prevents "ref" access, and this workaround helped to get fast execution time for the real case as well! This was the answer to the second question: replace '=' in WHERE clause with logically equal condition that bypasses the heuristics and let the entire index to be used for each row selected for the outer table.

Problem was solved for customer in 20 minutes or so after he had opened the support request! This is the power of good habit and the result of using the Community and Oracle engineer's work on maintaining public bugs database! We do not know if the bug will ever be fixed, but we are able to use the results of its discussion to solve problems.

Side note: the same trick applied to older version, without dynamic range optimization, does not give any real benefit. The resulting query on 5.5.x, for example, is notably slower. So, another good habit to mention is: check the suggested solution on the exact version customer used before recommending it!

Sunday, December 13, 2015

Fun with Bugs #40 - Bugs fixed in MySQL 5.7.10

MySQL 5.7.10, the first release after MySQL 5.7 GA announcement, appeared almost a week ago. It's interesting to check what bugs reported by MySQL Community were fixed in this version, hence this post (that had to wait during the weekdays busy with usual support work). As usual, I'll concentrate mostly on InnoDB, replication and optimizer-related bug fixes and highlight names of those who reported and verified each bug.

Let's start with InnoDB. The following bugs reported by MySQL Community users were fixed:
  • Bug #78623 - "Small tablespaces with BLOBs take up to 80 times more space in 5.7 than in 5.6". This serious regression was reported by my colleague Laurynas Biveinis and verified by Umesh well before 5.7.9 release, but probably too late to get fixed there. This is not the only example of bugs that could be fixed in the first 5.7 GA release theoretically. Either MySQL release process is too long in Oracle now, or they were just in a hurry to announce 5.7GA "in time"...
  • Bug #78494 - "InnoDB: Failing assertion: !(&buf_pool->mutex)->is_owned() line 3388", was reported by  Roel Van de Paar and verified by Umesh. As well as some other bugs I am going to mention today, it shows that when proper QA efforts are applied community users may still find cases that are missed by Oracle MySQL QA.
  • Bug #77321 - "Import tablespace may fail with invalid corruption error". This bug was originally found in MySQL 5.6 by Santosh Praneeth Banda, who had also suggested a way to fix, and verified by Shaohua Wang (probably).  
  • Bug #78410 is still private, so we can not get the details. I do not understand why this bug is still hidden, as according to the release notes:
    "After a crash on Windows, copying the data directory to a non-Windows platform to perform the restore caused a crash recovery failure on startup. The code did not convert file path separators from \ to / in the redo log."
  • Bug #78336 - "handle_fatal_signal (sig=11) in dict_table_t::get_ref_count". It was reported for debug build by  Roel Van de Paar and verified by Shane Bester, who had provided an even simpler test case and clarified the impact. I just do not get it while, with the fix ready back on September 22 (according to the last comment), it was NOT included in 5.7.9...
  • Bug #78197 - "COMPRESSION column in innodb_sys_tablespaces is not correct", was reported by Zhai Weixiang and verified by Umesh.
  • Bug #77572 - "The bogus duplicate key error in online ddl with incorrect key name". It also affects 5.6 and was reported (with both MTR test case and patch suggested) by Zhang Yingqiang. Surely, it was easy for Sinisa Milivojevic to verify it.
  • Bug #77128 - "Regression in dealing with filesystem limits", was reported for 5.6 and 5.7 by my colleagues Przemyslaw Malkowski and verified by Umesh.
  • Bug #73225 - "If tablespace exists, can't CREATE table, but can ALTER ENGINE=InnoDB". It was reported for 5.6 by Federico Razzoli and verified by my colleague Sveta Smirnova while she was still working in Oracle... 
  • Bug #78728 - "InnoDB: Failing assertion: 0 in file line 3526 ". This debug-binaries only assertion failure was reported by Roel Van de Paar and verified by Umesh.
There are many more bugs fixed in InnoDB, but they were internal-only, probably found by Oracle QA.

I see only two replication-related bugs from Community users fixed in 5.7.10:
  • Bug #76795 - "2pc succeeds even though binlog flush/sync fails". It was originally reported for MySQL 5.6 by  Santosh Praneeth Banda, who had suggested the patch, and verified by Umesh. See also Bug #68953 reported long time ago as a regression bug in 5.6 by Laurynas Biveinis.
As for optimizer, the list is also really short:
  • Bug #78260  - "handle_fatal_signal (sig=11) in Partition_helper::ph_read_range_first on SELECT". It was reported by Roel Van de Paar and verified by Umesh. The bug is related to partition pruning, so we can blame optimizer as well.
  • Bug #78665 - "GCOLS: GCOL VALUE CHANGES WHEN SESSION CHANGES SQL_MODE". Guilhem Bichot had reported this for MySQL Community to note, and probably he had fixed the bug himself later.The bug was verified by Miguel Solorzano.
  • Bug #78641 - "cast of too big HEX literal to BIGINT UNSIGNED: bad result and no warning". It was reported by Guilhem Bichot and verified by Umesh. MySQL 5.6 was also affected, and the fix is in 5.6.28.
  • Bug #77480 - "Wrong results for truncated column and aggregation". It was reported by Daniël van Eeden and verified by Umesh. Shane Bester had added a very simple test case later that also demonstrates the problem in MySQL 5.5 and 5.6 (where it is NOT fixed).
To summarize, we see a lot of fixes in InnoDB and 5.7.10 finally fixed some problems known for a long time (now that it's the latest GA release we'll probably get most fixes in 5.7 and only some of them will be back ported to 5.6), but there is still a long way to go. If you are interested in bugs I considered important in 5.7.9, check this post. Only 6 bugs listed there are closed.

Saturday, October 24, 2015

Fun with Bugs #39 - Known Bugs in MySQL 5.7.9 GA

These days everybody is excited with recent announcement of MySQL 5.7.9 GA release. If you are not aware of this event yet (I've noted it from numerous posts even during my short vacation), wait for the Oracle Open World 2015 to begin tomorrow to announce it even wider and louder!

I already have 5.7.9 built from source, up and running, so it's time to check what else we can expect from this new GA release besides new great features (this is a topic for a separate post or two) and usual excitement. Yes, I mean known, verified bugs in MySQL 5.7.9.

Let me start with a quick summary and then present the details. So, even though MySQL Community tried hard to check 5.7.x at early stages and report bugs to Oracle, MySQL 5.7.9 GA has a number of known installation and upgrade problems, code is not always clean, not all community suggested patches are included. As usual with new releases, there are some performance issues (one should expect single thread to run slower than on older versions in many cases), including those on replication slaves. Some new features are not completely documented, some implementation details are missing here and there, and transparent page compression, for example, is hardly can be widely used at the moment. All these are expected for users who had seen previous GA releases of MySQL. What's a bit worse, I suspect there are known crashing bugs (sequence of SQL statements that, when executed by a user with enough privileges, crashes non-debug binaries) in MySQL 5.7.9. Also, MySQL 5.7.9 inherits some regression bugs from MySQL 5.6.x.

Quick search for active bugs in version 5.7 reported over last 6 months gives me a list of 157 bugs that probably affect MySQL 5.7.9. I've excluded those that are not yet verified formally, those affecting older versions (5.6.x and 5.5.x), most of documentation and test problems and ended up with the following list that I want to present to those who plan to upgrade to 5.7 GA really soon:
  • Bug #78936 - "Installing MySQL on Linux Using Debian Packages missing dependency". This is mostly a documentation request. Please, check comments there for the correct order of .deb packages installation, if you plan to use dpkg.
  • Bug #78591 - "mysqld --initialize fails on empty data directory". Some more installation problems on Ubuntu...
  • Bug #78895 - "Lack information about generated columns in mysql_fetch_fields function". Now, this is typical, unfortunately, for new MySQL features. They are rarely completely implemented in first few GA releases. Oracle still has to rely on MySQL Community to find these small missing implementation details (or design problems) here and there.
  • Bug #78848 - "Docs still claim Performance Schema does not allocate memory after startup". While formally it's just a documentation bug, it is about a notable change in behavior. In 5.7 additional memory may be allocated after startup to support performance_schema. This had never been the case before. My report that is based on real customer case, Bug #78808, "Manual is wrong when explaining the default UMASK value", is of a similar kind - some clear changes in behavior are NOT documented properly before making GA announcement. MySQL 5.7 follows the same bad tradition of 5.0, 5.1, 5.5, and 5.6 GA releases here...
  • Bug #78778 - "mysqlclient does not return correct mysql_insert_id via database handle". It seems to be a regression bug (even though it is not tagged so) and as a result your DBD::mysql Perl module may not work with 5.7 the way you expect.
  • Bug #78774 - "old-password subcommand is still in mysqladmin --help in 5.7". Minor problem, but I wonder why it could not be fixed in the code before GA release. It seems the build/QA/release process still takes a lot of time in Oracle, so even those bugs noted by Community in RC releases were reported "too late" to influence the GA release. This is unfortunate.
  • Bug #78758 - "Inserting (invalid) utf8 data into 5.6.25 master breaks 5.7 slave". There is a workaround documented, but this bug report shows that upgrade from 5.6 may not be seamless and simple (are you surprised?) and that upgrade was NOT really tested really well by the internal QA process. I am not surprised, it had always been the case.
  • Bug #78672 - "assert fails in fil_io during linkbench with transparent innodb compression". Facebook engineers are trying hard, but transparent page compression mostly does NOT work well (if only on NVMFS, see Bug #78277 also). Just remember that for now, before you became too excited with the feature.
  • Bug #78495 - "Table 'mysql.gtid_executed' cannot be opened." Don't be scared with this warning, it's "Ok"...
  • Bug #78374 - "CREATE USER IF NOT EXISTS" reports an error". New feature that just does not work as documented. Well done.
  • Bug #78352 - "Slow startup of 5.7.x slave with relay_log_recovery = ON and many relay logs". This my report is also based on a real life customer case. Take care when upgrading, really.
  • Bug #78254 - "After running mysql_upgrade proxies_priv user columns are not updated to 32". mysql_upgrade is not able to fix all incompatibilities for further seamless use of MySQL 5.7.
  • Bug #78251 - "handle_fatal_signal (sig=11) in mach_double_read". No further comments for now.
  • Bug #78236 - "InnoDB: Failing assertion: holder != requestor". Again, no comments.
  • Bug #78219 - "compression can be defined on a compressed table if innodb_strict_mode is OFF"
  • Bug #78068 - "replication_* tables miss information about binary log position and other". I should not comment on performance_schema any more probably.
  • Bug #77740 - "silent failure to start if mysql.gtids_executed gets HA_ERR_LOCK_WAIT_TIMEOUT". You can end up with failures and error log does not help to find out why...
  • This bug was actually reported back in February, so it was not in the list of 157 recent reports, and there is nothing unexpected here, but still. Note that single thread performance of MySQL 5.7.x GA may be worse than for older versions, see Bug #75981, "MySQL 5.7.5 30% slower than 5.6.23 for 1 thread write", for example.
Some potential performance issues, mistakes in the code and debug-only assertions (that may show huge internal corruptions) are never noted until somebody starts to test a lot or read the code carefully:
  • Bug #78894 - ''buf_pool_resize can lock less in checking whether AHI is on or off".
  • Bug #78637 - "Incorrect for loop condition in"
  • Bug #78732 - "InnoDB: Failing assertion: *mbmaxlen < 5 in file line 1803".
  • Bug #78728 - "InnoDB: Failing assertion: 0 in file line 3526" 
  • ... check the list yourself, there are many more assertion failures and code review problems reported  ...
  • Bug #77094 - "Reduce log_sys->mutex contention by allowing concurrent mtr commit and log write". This is just one of many patches contributed by Zhai Weixiang that remain just "Verified"
I expect many more reports of this kind from Roel, Laurynas and my other colleagues now, when Percona is actively working on its own Percona Server 5.7 GA release.

The last but not the least, if you are upgrading to 5.7 GA from 5.5.x or older versions, check known regressions in MySQL 5.6. Many of them are affecting 5.7.9 as well, unfortunately.

Saturday, October 10, 2015

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.

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.

Saturday, September 19, 2015

My Guidebook for Percona Live Amsterdam, 2015 - Part II

I see on Facebook that many of my friends are already on their way to Amsterdam for Percona Live Amsterdam 2015 that starts on Monday. As I explained, I am not going there, but I'd like to share my ideas on what presentations are worth attending.

So, the program for the last day, September 23, also looks really interesting and I'd have real problems on where to go for most of the slots. As usual, I'd try to listen to all keynotes, but I am especially interested in The Virtues of Boring Technology as I had not seen my former MySQL AB's colleague Kristian Köhntopp (now from for a long time already. The panel discussion after it, on The next disruptive technology, should be also interesting.

After short break I'd probably go to one of these sessions:
But I would not be surprised to find myself listening about MariaDB 10.1 as well...

Next slot is a real problem. I never miss a chance to find out what new performance records Dimitrii Kravtchuk managed to get from poor old MySQL, so
MySQL 5.7 Performance: Scalability & Benchmarks is a must. But I also try to visit all presentations on replication by Luis Soares from Oracle as well, so I'd hardly could afford to miss
The Latest and Greatest MySQL 5.7 Replication Features and More! But these sessions happen at the same time. I'd get a real problem if I'd go to the conference, so lucky I am that I stay at home and can spend time cutting good oak wood. Winter is coming, you know...

After lunch I'd go to listen to my colleague Vlad Lesin, who speaks about TokuDB internals. I've already attended a couple of presentations on fractal trees and TokuDB performance achievements, and surely I can always ask a question to my colleagues in Support who previously worked in Tokutek, but I really wonder what Vlad thinks about the code. He is one of the best developers in Percona and I am impressed by his analysis and work on several MySQL bugs, so I'd consider this session a real opportunity to find out what we can expect from TokuDB in production.

Next slot is also not a problem for me. I'd like to be at the Non Blocking DDL in Galera Cluster session by Seppo Jaakola from Codership. "Online" DDL for a single MySQL instance is already a problem, so doing it in a truly non-blocking manner on Galera/PXC cluster requires something new. I'd like to know more about this feature of Galera 4.0.

After that I'd either got to listen to LeFred explaining his hacks at Undelete rows from the binary log a hacking session (as far as I remember he presented something similar on FOSDEM 2015) or, if I'd like to speak more than to listen, I'd just go and tell Oracle engineers what MySQL still misses! 
MySQL 5.8 Dreaming and Brainstorming is the ideal session to do this. Personally I want constraints checks at transaction boundaries (not for each row), fully cost-based optimizer, ALTER that is really online/non-blocking, and war in Ukraine stopped. Guess what of these will happen at the moment of MySQL 5.8 GA...

Lightning Talks are always fun, so I'd go to listen to them next.

For the last slot I'd have to choose one of the following sessions:
  • MySQL at Wikipedia: How we do relational data at the Wikimedia Foundation - yet another case of web-scale MySQL usage presented by my former colleague from Percona (Jaime Crespo), and MySQL AB (Sean Pringle). I miss working with Sean a lot, he was a key member of "dream team" that provided MySQL support during APAC hours in MySQL AB. I stayed online and working more than once to cover APAC hours, as it was a real pleasure to work with this team!
  • At the same time, for the very last slot, we have Jeremy and Davi speaking about InnoDB data storage structures again at the session InnoDB: A hands-on exploration of on-disk storage with innodb_ruby. Surely I can just try myself and real great blog posts, but I never miss their talks... What shell I do? 

I shell keep cutting oak wood and help Percona customers, but you, those who plan to attend Percona Live Amsterdam, are in real troubles!

Friday, September 18, 2015

My Guidebook for Percona Live Amsterdam, 2015 - Part I

Unfortunately I am not going to Percona Live Amsterdam 2015 that starts next week. Somebody has to work and keep those customers happy who prefer to work as usual even during such a festival.

I am still asking myself: if I'd be able to go there, what tutorials and sessions I'd like to attend (besides those I'd present)? "All of them" is not a correct answer, as often great and useful sessions happens at the same time in different places. So, I decided to create a list of sessions for myself (as a reminder to check slides at least after they are published), and I'd like to share it.

I tried to pick up one session per slot. but for most slots this was liteally impossible even for myself - I just can not decide now what's more important for my very limited set of database-related interests. So, you will see 2 sessions from most time slots. I tried to add some comments about speakers and other reasons for my interest.

On September 21 we have tutorials, and I'd visit one of these two (they are both full day ones it seems):
  • Advanced Percona XtraDB Cluster in a nutshell, la suite : Hands on tutorial not for beginners! - I've visited tutorials on PXC conducted by Frederic Descamps (aka LeFred) and other my colleagues more than once, and it's always great experience and easy way to study something new about Galera clusters. Be ready for a lot of work to do during this tutorial if you really want to follow it closely and get experience
  • Query Optimization: From 0 to 10 (and up to 5.7) - Usually I try to keep myslef as far from clusters (including PXC/Galera) as possible, so chances are high that I'd visit this tutorial by my former colleague Jaime Crespo instead. He is a very experinced trainer who now works on real life problems as DBA, so it's quite possible that I'd study a trick or two or get a chance to discuss some real life query optimization issues and approaches.
No comments on reception @ Delirium Cafe, sorry.

September 22 starts with keynotes, and usually I am present at all of them. But even if I'd meet colleagues to talk to ASAP durings keynotes slot, I'd still like to visit at least this one, MySQL and MongoDB for web-scale data management., by Mark Callaghan from Facebook.I am following him as a writer, speaker and customer for many years already and I am really interested in his opinions on MongoDB. Really, why would a person like him decide to work on yet another database (after Informix, Oracle, MySQL, many more), especially MongoDB? I'd like to understand...

 The first sessions slot that day is really a problem for me. I'd like to visit these sessions:
  • InnoDB: A journey to the core - I'd like to be there both because I am interested in InnoDB internals and because all previous sessions by Jeremy Cole and Davi Arnaut I had a chance to attend were just great.
  • XtraDB 5.6 and 5.7: Key Performance Algorithms -on the other hand, I'd like to listen to my colleague Laurynas. I do not yet follow InnoDB changes in 5.7 really closely (if only new types of locks added) and even less I know about Percona's plans on XtraDB for 5.7. Good chance to find out.
 Next slot is again a problem:
  • State of MySQL Group Replication - It was nice to listen to Nuno Carvalho last year at Oracle Open World. This is a nice feature that has a long way to go still to be ready to compete with Galera, but it may go far beyond it as well and become a foundation for new HA architectures for MySQL. Who knows... I'd like to find out what they were able to achieve over last year.
  • Performance Schema and Sys Schema in MySQL 5.7 - at the same time, I'd really want to listen to Mark Leith, whom I named a "godfather of PERFORMANCE_SCHEMA" two years ago. I do not speak on this topic any more, but I am still intersted in proper instrumentation for MySQL.
After the lunch I'd go directly to attend Giuseppe Maxia's Pivot tables: analytics in pure SQL. I used to be a big fan of Oracle DBMS's analytical functions back in my Oracle-related days, till 2005. I always wonder about the ways to live without them in MySQL and still execute queries efficiently. Also, I just like to listen to Giuseppe no matter what he is talking about.

Next slot is also clear for me. I'd go to The highs and lows of semi-synchronous replication to find out more on Facebook's experience with semi-sync replication.

Then I'd have a problem again:
  • MySQL 5.7: What Is New in the Optimizer? - I am always happy to meet my colleague since good old Sun's days Manyi Lu and find out what's going to happen with MySQL optimizer in 5.7 GA from
    Olav Sandstå. I know a lot about their plans since Oracle Open World 2014, but it would be useful to check the progress and details now when MySQL 5.7 GA is probably 100% feature complete.
  • Solid State Storage for Your MySQL Databases: What You Need to Know to Optimize Performance -on the other hand, I'd like to listen to Peter Zaitsev explaining how to use SSDs efficiently for MySQL. These days I see that customers have a lot of hope on SSDs, but often are still faced with notable I/O performance problems. Maybe I just miss few things to check while trying to help them...
The last slot for the day is also intersting:
  • Database Encryption on MariaDB 10.1 - Customers often ask about encryption for MySQL data, and it seems MariaDB finally has the answer that would make them happy. It's useful to check the details with Sergei Golubchik to set proper expectations to them. Blame me for whatever, but I like to meet MariaDB engineers. Most of them were my colleagues back in MySQL AB, and they still do their job well.
  • Anatomy of a Proxy Server: MaxScale Internals -I had not cared much about this new technology until recently, but it becomes incresingly popular from what I read in different sources. Maybe it's time to study how it works andwhat it can provide.
Again, no comments on Community Dinner at - I wish I'd be there... 

More on sessions I'd like to attend on September 23 in the next post that should be ready on Monday, September 21.

Sunday, May 31, 2015

Fun with Bugs #36 - Bugs fixed in MySQL 5.6.25

Two days ago Oracle had released MySQL 5.6.25, so it's time to check what bugs reported by MySQL Community are fixed there. As usual, I'll mention both a bug reporter and engineer who verified the bug. Please, pay attention to fixes in replication and partitioning - if you use these features (or queries to INFORMATION_SCHEMA with a lot of complex tables in your database), please, consider upgrading ASAP.

The following InnoDB related bugs were fixed:
  • Bug #69990 - CREATE_TIME and UPDATE_TIME are wrong for partitioned tables. Finally this bug reported by my colleague Justin Swanhart and verified by Umesh (almost immediately after it was reported) is fixed!
  • Bug #75790 - memcached SET command accepts negative values for expire time. This bug (that Oracle put into InnoDB section in the release notes) was reported and verified by Umesh
  • Bug #74686  - Wrong relevance ranking for InnoDB full text searches under certain conditions. This bug was reported by Tim McLaughlin and verified by Miguel Solorzano.
  • The last but not the least, new innodb_stress test suite by Mark Callaghan is included now, thanks to the Bug #76347 reported by Viswanatham Gudipati.
Oracle had fixed several more memcached and InnoDB-related bugs in 5.6.25, but as they were reported only internally, they are out of the scope of my posts.

A set of related bugs in Partitioning category was fixed:
  • Bug #74288 - Assertion `part_share->partitions_share_refs->num_parts >= m_tot_parts' failed. It was reported by my colleague Roel Van de Paar and verified by Umesh.
  • Bug #74634 - this bug is still private, so we do not see the details.
  • Bug #74451 - this bug is also private. We can probably assume that in case of private bug we had assertion failures or crashes on non-debug builds. So, if you use partitioning a lot, please, consider upgrading to 5.6.25 ASAP.
A lot of replication related bugs were fixed in 5.6.25:
  • Bug #75879 - memory consumed quickly while executing loop in procedure. It was reported by Zhai Weixiang (who had also provided a patch) and verified by Shane Bester. If you ask me, based on the contributions over last 2 years it's about time for Percona to hire Zhai Weixiang into our development team, or Oracle may approach him faster. He is a really brilliant engineer!
  • Bug #75781 - log lock may not be unlocked if add_logged_gtid failed. It was reported by Fangxin Flou (who had provided a patch as well) and verified by Sinisa Milivojevic.
  • Bug #75769 - this bug is still private. Release notes describes the problem as follows: "A slave running MySQL 5.6.24 or earlier could not connect to a master running MySQL 5.7.6 and later that had gtid_mode=OFF_PERMISSIVE or gtid_mode=ON_PERMISSIVE." I wonder why such a bug can be private. Either it was reported like that or we do not see all the details about the impact.
  • Bug #75574 - Can not execute change master after Error occurred in MTS mode. It was reported by Zhang Yingqiang and verified by Sveta Smirnova (while she still worked in Oracle).
  • Bug #75570 - semi-sync replication performance degrades with a high number of threads. The problem was studied in details and reported by Rene' Cannao' and verified by Umesh.
  • Bug #74734  - mysqlbinlog can't decode events > ~1.6GB. It was reported by Hartmut Holzgraefe and verified by Umesh.
  • Bug #69848 - mysql 5.6 slave out of memory error. It was reported by  Jianjun Yang and verified by Sveta SmirnovaBug #72885 (where Shane Bester had clearly identified the memory leak)was declared a duplicate. If you use master-info-repository = TABLE on your 5.6.x slaves, please, consider upgrading to 5.6.25 ASAP.
  • Bug #70711 - mysqlbinlog prints invalid SQL from relay logs when GTID is enabled. This bug was reported by Yoshinori Matsunobu and probably verified formally by Luis Soares.
 There are several fixes in other categories:
  • Bug #75740 - Fix errors detected by ASan at runtime. It was reported and verified by Anitha Gopi based on request from WebScaleSQL team. 
  • Bug #76612 - would like ability to throttle firewall ACCESS DENIED messages in error log. This feature was requested by Shane Bester. Should I tell you again how much I am happy when I see public bug reports from Oracle employees?
  • Bug #76552 - Cannot shutdown MySQL using JDBC driver. This regression bug was reported by Davi Arnaut (who provided a patch as well) and verified by Umesh.
  • Bug #76019 is private. Release notes say: "Inappropriate -Werror options could appear in mysql_config --cflags output." Why on the Earth anyone could set or leave this bug as private is beyond my imagination.
  • Bug #74517 - thread/sql/main doesn't change state/info after startup. PERFORMANCE_SCHEMA was meant to be perfect already, but still some fixes are needed. The bug was reported by Kolbe Kegel and verified by Umesh.
  • Bug #72322 - Query to I_S.tables and I_S.columns leads to huge memory usage. Now I am impressed and I want to check the fix ASAP (as release notes do not say much)! If this bug (reported by my colleague Przemyslaw Malkowski just few weeks ago, on April 11, and verified by Umesh) is really fixed, it's a huge step forward in making INFORMATION_SCHEMA usable.
  • Bug #69638 - Wrong results when running a SELECT that includes a HAVING based on a function. The only optimizer bug from Community fixed in this version was reported by Roger Esteban and verified by Umesh.
  • Bug #69453 - Prepared statement is written to general query log after its execution is finish. It was reported by my colleague Sergei Glushchenko and verified by Umesh.
  • Bug #68999 - SSL_OP_NO_COMPRESSION not defined. It was reported by Remi Colletand verified probably by Georgi Kodinov.
To summarize, 24 or so bug reports from public bugs database were fixed in 5.6.25, of them fixes for replication, partitioned tables and INFORMATION_SCHEMA look really important and impressive. At least 10 of these bug reports were verified by Umesh. 4 bugs remain private, and I think it's probably wrong.

Tuesday, April 14, 2015

Fun with Bugs #35 - Bugs fixed in MySQL 5.6.24

I had not reviewed bug fixes in MySQL 5.6 for quite a some time, so I decided to check what bugs reported by MySQL Community were fixed in recently released MySQL 5.6.24. I'll mention both a bug reporter and engineer who verified the bug in the list below, because I still think that in MySQL world names should matter.

So, MySQL 5.6.24 includes fixes for the following bugs from I'd start with InnoDB and memcached-related fixes:
  • Bug #72080 - truncate temporary table crash: !DICT_TF2_FLAG_IS_SET(table, DICT_TF2_TEMPORARY). Reported by Doug Warner and verified by Shane Bester after a lot of testing. Note how fast it was fixed after verification!
  • Bug #75755 - Fulltext search behaviour with MyISAM vs. InnoDB (wrong result with InnoDB). Reported by Elena Stepanova from MariaDB and confirmed by my former boss Miguel Solorzano, this wrong results bug was also promptly fixed.
  • Bug #70055 - Expiration time ignored. This memcached-related bug was reported by Miljenko Brkic and verified by Umesh
  • Bug #74956 - Can not stop mysql with memcached plugin. This regression bug was reported by my colleague Nilnandan Joshi and verified by Umesh
  • Bug #75200 - MySQL crashed because of append operation. Reported by
    by already famous bug reporter (and developer) Zhai Weixiang, it was verified by Umesh and fixed fast enough.
    As you can see MySQL 5.6.24 fixed several more memcached-related bugs (reported internally), so if you use memcached it really makes sense to upgrade.
  • Bug #73361 - mutex contention caused by dummy table/index creation/free. Reported by Zhai Weixiang (who also suggested a patch) and verified by my dear friend and teacher Sinisa Milivojevic.  
Let's move on to partitioning. Just a couple of fixes there that fixed a long list of bugs reported by Percona QA engineers:
  •  Bug #74841 - handle_fatal_signal (sig=11) in cmp_rec_and_tuple | sql/ This was reported by Percona's recent QA super star, Ramesh Sivaraman, and verified by Miguel Solorzano.
  • Bug #74860 - handle_fatal_signal (sig=11) in generate_partition_syntax. This was reported by Percona's all times QA superstar, Roel Van de Paar, and verified by Umesh.
  • Bug #74869 - handle_fatal_signal (sig=11) in ha_partition::handle_opt_partitions. It was reported by Ramesh Sivaraman, and verified by Miguel Solorzano.
  • Bug #74288 - Assertion `part_share->partitions_share_refs->num_parts >= m_tot_parts' failed. Reported by Roel Van de Paar and verified by Umesh.
  • Several other bugs mentioned remain private and not visible to us: Bug #74451, Bug #74478, Bug #74491, Bug #74560, Bug #74746, Bug #74634. I am not sure why they are private (or why the previous ones are still public, and for how long). Let's assume they were reported as private (and/or security ones) by my colleagues.
Now, only one replication bug reported at was fixed, but serious one:
  • Bug #74607 - slave io_thread may get stuck when using GTID and low slave_net_timeouts. This bug was reported by Santosh Praneeth Banda and verified by Umesh.
There were several other bugs fixed in several categories:
  • Bug #74037 - group_concat_max_len=18446744073709547520 not accepted in my.cnf. It was reported by Leandro Morgado from Oracle and verified by himself probably. I am always to happy to see Oracle engineers reporting bugs in public.
  • Bug #73373 - Warning message shows old variable name. This was reported by Tsubasa Tanaka and verified by Miguel Solorzano.
  • Bug #71634 - P_S digest looks wrong for system variables, shown as @ @ variable... Reported by Simon Mudd and verified by the author of PERFORMANCE_SCHEMA, Marc Alff.
  • Bug #69744 - ssl.cmake silently chooses bundled yassl instead of erroring for old openssl ver. Good old build problem reported and verified by Shane Bester.
  • Bug #69423 - Double close() on the same file descriptor inside mysql_real_connect(). Reported by Yao Deng and verified by Igor Solodovnikov.
  • Bug #60782 - Audit plugin API: no MYSQL_AUDIT_GENERAL_LOG notifications with general log off. This one was reported by Olle Nilsson and verified (as a feature request) by ...yours truly almost 4 years ago.
A couple of issues were also fixed by introducing new server behavior:
  • Bug #74917 - Failed restarts contain no version details. Reported by my Oracle colleague Shawn Green and probably verified by him as well. Now server version is mentioned in the new error log file with the first message.
  • Bug #72997 - "fast" ALTER TABLE CHANGE on enum column triggers full table rebuild. Reported by famous Oracle customer Simon Mudd and verified by even more famous Shane Bester. The (temporary?) fix introduced two new variables, avoid_temporal_upgrade to control conversion to new "temporal" columns format (and rebuilding the table for any ALTER as a result), and show_old_temporals to control adding comments about old format of "temporal" column in SHOW CREATE TABLE output and corresponding INFORMATION_SCHEMA.COLUMNS.COLUMN value. Both variables are immediately declared as deprecated, so they may disappear in 5.7 (or 5.8? I am a bit lost with recent deprecation practices of Oracle).
That's all fixes for bugs reported at in 5.6.24. Stay tuned, maybe one day we'll discuss MySQL 5.7.7 as well.

Wednesday, April 8, 2015

Using gdb to understand what locks (and when) are really set by InnoDB. Part II.

In the previous post we checked lock requests while executing INSERT INTO t ... SELECT FROM tt where there is an auto_increment column for which the value is generated in the destination table with default innodb_autoinc_lock_mode=1. Based on it I've reported Bug #76563 that is already verified.

Let's continue to study a special case of the statement mentioned above, INSERT INTO t ... SELECT FROM t, that is, when source and destination table is the same. We again start with table t having just 4 rows:

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t;
| id | val  |
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
4 rows in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t(val) select 100 from t;

Tracing in gdb with the same breakpoints set shows the following:

[Switching to Thread 0x7fd74cf79700 (LWP 1866)]

Breakpoint 1, lock_table (flags=0, table=0x7fd7233f69e8, mode=LOCK_IS, thr=0x7fd7233de6f0) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/lock/
4426            if (flags & BTR_NO_LOCKING_FLAG) {
(gdb) p table->name
$41 = 0x7fd7383feac0 "test/t"

So, we start with IS lock on the table. Probably we plan to read some rows. Let's continue:

(gdb) c

Breakpoint 2, lock_rec_lock (impl=0, mode=2, block=0x7fd7249af000, heap_no=2, index=0x7fd7233f7b68, thr=0x7fd7233de6f0) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/lock/
2329            switch (lock_rec_lock_fast(impl, mode, block, heap_no, index, thr)) {
(gdb) p index->table_name
$42 = 0x7fd7383feac0 "test/t"
(gdb) c

Breakpoint 2, lock_rec_lock (impl=0, mode=2, block=0x7fd7249af000, heap_no=3, index=0x7fd7233f7b68, thr=0x7fd7233de6f0) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/lock/
2329            switch (lock_rec_lock_fast(impl, mode, block, heap_no, index, thr)) {
(gdb) c

Breakpoint 2, lock_rec_lock (impl=0, mode=2, block=0x7fd7249af000, heap_no=4, index=0x7fd7233f7b68, thr=0x7fd7233de6f0) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/lock/
2329            switch (lock_rec_lock_fast(impl, mode, block, heap_no, index, thr)) {
(gdb) c

Breakpoint 2, lock_rec_lock (impl=0, mode=2, block=0x7fd7249af000, heap_no=5, index=0x7fd7233f7b68, thr=0x7fd7233de6f0) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/lock/
2329            switch (lock_rec_lock_fast(impl, mode, block, heap_no, index, thr)) {
(gdb) c

Breakpoint 2, lock_rec_lock (impl=0, mode=2, block=0x7fd7249af000, heap_no=1, index=0x7fd7233f7b68, thr=0x7fd7233de6f0) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/lock/
2329            switch (lock_rec_lock_fast(impl, mode, block, heap_no, index, thr)) {

We set S (mode=2) locks on each row in the "source" (t) table and supremum record there, to begin with. We read all rows that we plan to insert into the temporary table before inserting them. One could assume this just from checking the plan:

mysql> explain insert into t(val) select 100 from t\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 4
        Extra: Using index; Using temporary
1 row in set (0.00 sec)

Note that this stage, selecting all rows into the temporary table, may take a long time for a big table t. During all this time there is NO AUTO-INC lock set. Probably it explains what happened in cases discussed in the original Bug #72748.

(gdb) c

Breakpoint 3, row_lock_table_autoinc_for_mysql (prebuilt=0x7fd7233e3068) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/row/
1146            if (trx == table->autoinc_trx) {
(gdb) c

Breakpoint 1, lock_table (flags=0, table=0x7fd7233f69e8, mode=LOCK_AUTO_INC, thr=0x7fd7233e3780) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/lock/
4426            if (flags & BTR_NO_LOCKING_FLAG) {
(gdb) p table->name
$43 = 0x7fd7383feac0 "test/t"

Only now we set AUTO-INC lock on the "destination" table (again t in this case) and proceed with expected insertion:

(gdb) c

Breakpoint 1, lock_table (flags=0, table=0x7fd7233f69e8, mode=LOCK_IX, thr=0x7fd7233e3780) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/lock/
4426            if (flags & BTR_NO_LOCKING_FLAG) {
(gdb) p table->name
$44 = 0x7fd7383feac0 "test/t"
(gdb) c

Breakpoint 3, row_lock_table_autoinc_for_mysql (prebuilt=0x7fd7233e3068) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/row/
1146            if (trx == table->autoinc_trx) {
(gdb) c

Breakpoint 3, row_lock_table_autoinc_for_mysql (prebuilt=0x7fd7233e3068) at /usr/src/debug/percona-server-5.6.23-72.1/storage/innobase/row/
1146            if (trx == table->autoinc_trx) {
(gdb) c

Our UPDATE completes finally:

Query OK, 4 rows affected, 1 warning (5 min 45.02 sec)
Records: 4  Duplicates: 0  Warnings: 1

mysql> show warnings\G*************************** 1. row ***************************
  Level: Note
   Code: 1592
Message: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
1 row in set (0.00 sec)

mysql> select * from t;
| id | val  |
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
| 12 |  100 |
| 13 |  100 |
| 14 |  100 |
| 15 |  100 |
8 rows in set (0.00 sec)

To summarize, the following locks are set (and in the following order) while executing INSERT INTO t ... SELECT FROM t where there is an auto_increment column for which the value is generated (with default innodb_autoinc_lock_mode=1 and using default REPEATABLE READ transaction isolation level) according to our tracing in gdb:

  1. IS lock on the table (t)
  2. S locks on each row in t we read with SELECT
  3. S lock on the supremum record in t
  4. AUTO-INC lock on t
  5. IX lock on t
  6. Probably implicit X lock on each row inserted (we had not seen this in gdb clearly). We read these rows from the temporary table we've created
I see two potential problems here.

There is a real chance to run (and commit) fast concurrent INSERT (that uses AUTO-INC lock) into the table t while we read all rows from it before we request AUTO-INC lock on the destination. As a result, some of rows inserted may be read during SELECT stage and lead to more rows inserted than we could expect.

Also, there is no explicit explanation for this (corner) case of INSERT ... SELECT in the manual. I plan to file a documentation bug later.