Fun with Bugs #97 - On MySQL Bug Reports I am Subscribed to, Part XXXI

Time for the next blog post about MySQL bugs! The previous one covered some bugs I considered interesting in March, this one will be about community bug reports that I've picked up since March 26, 2020. I'd better review bug fixed in MySQL 5.7.30 instead of this, but it is still not released, even though we know it must get some important security fixes based on "Oracle Critical Patch Update Advisory - April 2020" recently published.

As usual, I am mostly interested in replication, InnoDB, optimizer and few more categories. Here is the list:

• Bug #99082 - "Problem with replication: XA transaction, temp tables and row based binlog". As noted by Libor Laichmann, creating temporary tables inside XA transaction leads to broken replication in MySQL 5.7.29, with somewhat misleading error message. This is unfortunate. We do not see any evidence, yet, if 5.6 and 8.0 branches are similarly affected.
• Bug #99094 - "coredump when install information schema plugin". Bug reporter, Lou Shuai, tried to create simple plugin for the information_schema and it crashed MySQL server 8.0.19 upon installation attempt. Bot the plugin source code and patch for the bug were contributed.
• Bug #99100 - "GROUP BY will throw table is full when temptable memory allocation exceed limit". More fun with TempTable storage engine that I blamed previously. This bug was reported by Dean Zhou, who had performed detailed analysis in gdb and suggested a fix. It took some efforts for the bug reporter to get it "Verified" as a regression bug in MySQL 8.0.19 (without a "regression" tag, surely).
• Bug #99101 - "SELECT .. LOCK IN SHARE MODE requires LOCK TABLES grant". Simple regression in MySQL 8.0.11+ (or incompatible change in behavior, if you prefer) vs MySQL 5.7 was found and reported by Matthew Boehm.
• Bug #99136 - "TempTable wastes 1MB for each connection in thread cache". Nikolai Ikhalainen demonstrated this additional memory usage in MySQL 8.0.16 comparing to 5.7 quite clearly.
• Bug #99174 - "Prepare transaction won't rollback if server crash after online ddl prepare stage". This bug was reported by Zhang Xiaojian. Additional debugging code (one DBUG_EXECUTE_IF statement) was added to the source to demonstrate the problem easily and it caused some questionable arguments of a kind:
  

  "First of all, changing our server code in order to cause a bug, can not be considered a repeatable test case."

  But the bug reporter provided more details and clear test case, and had not agreed with the above. The bug was soon "Verified", even though no attempts to check (or explain) if 5.7 may be similarly affected were made in public, so we still have to wonder if this is a regression.
• Bug #99180 - "Accessing freed memory in perfschema when aggregating status vars". Let me just quote Manuel Ung:
  

  "When aggregate_thread_status is called for other threads, it's possible for that thread to have exited and freed the THD between the time we check that the thread was valid, until the time we call get_thd_status_var."

  Ironically, he had to add some conditional my_sleep() call to server code to get a repeatable test case, and this was NOT a problem for a good Oracle engineer to verify the bug immediately. There are still bugs in my beloved Performance Schema. Who could imagine that?
• Bug #99200 - "CREATE USER get stuck on SHOW PROCESSLIST and ps.threads on slave". So, slave may disclose some sensitive information. As demonstrated by Marcelo Altmann, if a query had been rewritten by the parser because it contains sensitive information, it won't be cleaned up when slave's SQL thread applies it, making it visible in SHOW PROCESSLIST and performance_schema.threads. Both 5.7.29 and 8.0.19 are affected. Bug reporter had contributed fixes for both versions. See also another bug in Performance Schema that he reported and contributed a fix for, Bug #99204 - "performance_schema threads table PROCESSLIST_INFO incorrect behaviour".
• Bug #99205 - "xa prepare write binlog while execute failed". Then XA PREPARE from the binary log is executed on slave and... we are in troubles. The bug was reported by Phoenix Zhang. Unfortunately it is still not clear from this verified bug report if MySQL 5.6 and 5.7 are similarly affected (probably they are).
• Bug #99206 - "lock_wait_timeout is taking twice time while adding new partition in table". Nice finding by Lalit Choudhary. MySQL 8.0.19 is not affected by this bug.
• Bug #99244 - "bad performance in hash join because join with no join condition between tables". This optimizer bug (wrong join order when new hash join optimization is used) was reported by Chunyang Xu. But note also a simple test case and perf outputs contributed later by Shane Bester.
• Bug #99257 - "Inconsistent output and wrong ORDER BY Sorting for query with LIMIT". Yet another 5.7-only optimizer bug found by Lalit Choudhary. My quick test presented in a comment shows that MySQL 5.6.27 produced correct results, so this is a regression bug.
• Bug #99273 - "Item_ref in Having Clause points to invalid outer table field". This may lead to wrong results for simple enough queries. This regression bug that affects both MySQL 5.7.29 and 8.0.19 was reported by Shanshan Ying.
• Bug #99286 - "Concurrent update cause crash in row_search_mvcc". This great bug report with code analysis, fix suggested and a test case with detailed instructions was created by Zkong Kong. It was promptly verified, but I still miss any documented attempt to check (by running the test case or by code analysis if the bug applies only to 5.7.29 or MySQL 8.0.x is also potentially affected. For now I'll try to remember this assertion line:
  

  InnoDB: Assertion failure in thread 47758491551488 in file rem0rec.cc line 586

  and "crash with row_search_mvcc in backtrace". Who know when I hit something similar and what fork/version it will be...

Rainy birthday at the seaside in Cap-d'Ail. Almost 15 years of my 50 were spent checking new MySQL bug reports almost every day.

To summarize:

1. We still see many regression bugs in recent versions of MySQL 5.7.x and 8.0.x, often without "regression" tag.
2. There are cases when the bug is verified, but there are no clearly documented checks if all GA versions are affected.
3. XA transactions are (and had always been) a real disaster for modern MySQL versions in replication environments - all kinds of replication breakage and inconsistencies are possible.
4. Check "MySQL Bug Reporter Hall of Fame" if you want to know who from MySQL Community had contributed a lot of bug reports over last 10 years.

Fun with Bugs #96 - On MySQL Bug Reports I am Subscribed to, Part XXX

My weekdays are still busy even during this lockdown period, but weekend is a perfect time for yet another blog post about MySQL bugs I considered interesting. Very few followers read my posts on other topics anyway, especially if they have the "M....DB" word...

So, here is a new list of optimizer, replication, InnoDB and few other bugs in GA versions of MySQL that I've subscribed to since the end of February, 2020:

• Bug #98675 - "LIMIT clause in derived table garbles EXPLAIN Note". It's not a big deal and in specific case presented by Øystein Grøvlen optimizer works properly (note text aside), but some clarifications to the note and/or documentation would still help. Comments are useful to read and shows a great example of proper cooperation from Oracle side.
• Bug #98719 - "Parallel CREATE TABLE statement binlogged in a wrong order". This bug report from Song Libing shows that Oracle engineers readily accept and verify bug reports where some code modification (adding debug sync point, for example) is needed to demonstrate the problem with a repeatable, deterministic test case. I've seen other bug reports where code modification was considered a problem and bug was not verified immediately as a result. Note that this bug seem to be not repeatable on MySQL 8.0.19 and this was explicitly tested, with test results shared in public. Good job, Umesh Shastry!
• Bug #98734 - "Same digest for different queries if we use integer value in ORDER BY caluse". Different column numbers in ORDER BY often cause totally different execution plans, so assuming these queries are the same ("ORDER BY ?") for the purpose of digesting is misleading. Moreover, as noted by Lalit Choudhary from Percona, with column names instead of numbers such queries are not considered the same, so Performance Schema has to be more consistent.
• Bug #98739 - "TempTable storage engine slow if mmap is required". Take care when large temporary tables are used for your queries in MySQL 8.0.x. By default (without temptable_use_mmap = OFF) when the table is larger than temptable_max_ram you may notice a very slow query execution. Nice finding by Marcelo Altmann. This is not the only related performance regression I've seen reported recently. Looks like TempTable storage engine problems is a real main topic of this post!
• Bug #98750 - "SHOW FIELDS command regression on MySQL 8.0". This performance regression bug was reported by Juan Arruti. See also similar Bug #92195 - "checking permissions 90 time" that was wrongly(!) declared "Not a bug" back in 2018 just because of the way the problem was demonstrated. This time Przemyslaw Malkowski helped to make the point based on Performance Schema instrumentation, so there was no other option but to accept this as a real performance regression bug. Take care if you use SHOW statements with MySQL 8!
• Bug #98782 - "Using TempTable engine for GROUP BY is slower than using MEMORY engine". In this bug report Øystein Grøvlen demonstrated that MEMORY engine is about 10% faster for temporary tables in MySQL 8. Make sure to use internal_tmp_mem_storage_engine=MEMORY if you care about performance.
• Bug #98869 - "Temp ibt tablespace truncation at disconnection stuck InnoDB under large BP". Bug reporter, Fungo Wang, used different methods to show the performance impact of the regression probably introduced by WL#11613. From pt-pmp to perf and other OS level tools. Make sure to check all comments that point out to other bugs and problems.
• Bug #98974 - "InnoDB temp table could hurt InnoDB perf badly". Yet another bug report by Fungo Wang. This time it took a lot of time and efforts from the bug reporter and many MySQL Community members (including me) to get this bug properly processed, even though it started with a detailed source code analysis, properly described test case, stack traces analysis and perf profiling details shared. The ideal bug report got far from ideal treatment, unfortunately.
• Bug #98976 - "Case of referenced column of foreign key not corrected on import". Tim Düsterhus found that in versions with the fix for Bug #88718 - "Foreign key is always in lower case" the case is not always correct in the output of subsequent mysqldump.
• Bug #98980 - "A state inside mysql_real_connect_nonblocking still blocks". This bug report by Jay Edgar was verified surprisingly fast. This is why it ended up in my list.
• Bug #98990 - "avg_count_reset for monitor set owner is always NULL in I_S.INNODB_METRICS ". In this case Fungo Wang had not only found a bug, but also provided a patch that was accepted by Oracle after signing the OCA.
• Bug #99006 - "GTID Synchronisation speed when doing START SLAVE". Simon Mudd noted that in some cases with GTIDs START SLAVE may take a lot of time:
  

  root@myhost [(none)]> start slave;
  Query OK, 0 rows affected (3 min 17.74 sec)

  and what's worse nothing is logged to the error log in the process to show the reason, progress or anything useful. Probably it's expected that with many binary logs finding proper GTID in them takes time, but some feedback would be useful. The bug does not have any clear public test case and is still under analysis.
• Bug #99010 - "The mtr case --send command not use ps-protocol when run with --ps-protocol ". Does not sound like a big deal for anyone but developers who write MTR test cases, but in this report Ze Yang had provided source code analysis and quite detailed how to repeat steps and still got useless requests for additional feedback and nothing else till today. This is, again, unfortunate.
• Bug #99039 - "PSI thread info not getting updated after statement execution". Who could imagine that regression bugs may be introduced even into Performance Schema? But Pranay Motupalli found one introduced into 8.0.18, 5.7.28+ and 5.6.47+ by this commit! I hope to see it fixed soon.
• Bug #99051 - "XA commit may do engine commit before MYSQL_BIN_LOG::ordered_commit". XA transactions is one of my favorite weak areas of MySQL. In this bug report Dennis GAO described a case when XA COMMIT operation may do a engine commit before binlog flush on Ubuntu 18.04. He had contributed the fix based on the assumption that the best way is to ensure the sequence in the plugin_hash, so that the binlog plugin should be always before all the transnational engine plugins. I only hope that one day a long list of XA bugs will be fixed in one of forks if not in MySQL itself.

This is my favorite point of view this year.

To summarize:

1. I see some really good examples of bugs verification by Oracle engineers recently. All supported versions are tested, nobody tries to argue against the bug reporter approach used to demonstrate the problem, even if it includes source code modifications. Looks really promising.
2. Even ideal bug reports sometimes are not processed properly without extra efforts from MySQL Community, unfortunately.
3. Percona engineers still contribute a lot to MySQL with both new bug reports and useful comments and clarifications. I hope Oracle appreciates that.
4. Looks like code review and regression testing in Oracle still may benefit from some improvements, as we still see new regression bugs...