Fun with Bugs #95 - On MySQL Bug Reports I am Subscribed to, Part XXIX

With conferences cancelled or postponed and people forced to stay at home due to COVID-19 wide spreading, what can be better than to give my readers a new list of MySQL bugs to check? Useful reading should help! So today I continue my previous post with a quick review of bugs I've subscribed to in February, 2020, while things were still going as usual for most of us...

Here is the list of InnoDB, replication, optimizer and some other bugs to note while working with recent MySQL 5.7.x and 8.0.19 releases:

• Bug #98473 - "group replication will be block after lock table". This problem report by phoenix Zhang was declared not a bug recently. Looks like for group_replication_consistency= BEFORE_AND_AFTER it is expected to get nodes blocked if one of them executed LOCK TABLE ... WRITE and another tried to insert some rows into that table. Check last comment by Nuno Carvalho for more details. Having multiple nodes that change or block data in clusters is always fun. See also Bug #98643 - "group replication will be block primary node shutdown" from the same bug reporter. Analysis is still in progress for it
• Bug #98498 - "P_S view status_by_thread does not reflect correct results". This bug was reported by Oli Sennhauser (my former colleague in MySQL, founder of FromDual). Really weird results in the output.
• Bug #98501 - "Exchanging partition with tables does not update SDI correctly". Having a data dictionary is cool and useful, but the information there should be consistent in all places/forms where it is stored. Fungo Wang found and reported cases when it is wrong in the individual .ibd files for partitions or imported tablespaces. After some arguing about backups tools supported, the bug was verified.
• Bug #98511 - "OPTIMIZE TABLE on myisam can increase table size (~2x) and reduce performance".  This funny bug looks like a regression in MySQL 8.0.x comparing to 5.7. I doubt Oracle is going to fix anything for MyISAM, but maybe the regression still matters for them. As it often happens, this bug reported by Pete Dishman was verified without adding a regression tag.
• Bug #98520 - "Suboptimal implementations for some load/store functions for little-endian arch". Alexey Kopytov identified some remaining performance problems in low level functions like int6store() and uint6korr() in MySQL 8.0 for platforms like x86_64 or ARM64. He request to optimize those functions for little-endian architectures by providing specialized implementations, as it was done in MySQL 8.0.x for many other similar and more widely used functions.
• Bug #98530 - "crash when inplace encryption resumes makes tablespace unusable". This bug (with nice MTR test case) was reported by Satya Bodapati from Percona. See also his another bug report, Bug #98537 - "inplace encryption resume thread doesn't update DD properly".
• Bug #98546 - "Transient indexes statistics are updated in foreground causing performance issue". As noted by Charly Batista, if persistent statistics is not enabled InnoDB checks if 1/16 rows of the table have been changed and if this was the case, it calls the dict_stats_update function in the foreground. Moreover, it does not only degrade performance while recalculating stats in the foreground thread, but also sets RW_X_LATCH for the table in the process to serialize all access. So, use persistent statistics, but do not let it be automatically recalculated (and here I explained why).
• Bug #98616 - "XA PREPARE/XA COMMIT/XA ROLLBACK lost if mysql crash just after binlog flush". Yet another problem with XA transactions support in MySQL. Dennis Gao provided a patch and kindly explained that currently:
  

  "When mysql start-up recover, the innodb engine will only recover the transaction in prepared state, which means the undo->state of the recovered transaction must be TRX_UNDO_PREPARED (check trx_rollback_resurrected in trx0roll.cc). So if a "xa prepare" transaction only flush binlog, it will just be rollback during start-up recover and lost."

• Bug #98624 - "Can't connect to MySQL after establishing around 65536 connections". Yet another great finding by Fungo Wang. Basically, this is a bug in the MDL subsystem, the scalability of MDL is limited to 65536 users, due to the pins number limitation (LF_PINBOX_MAX_PINS = 65536) of the LF_HASH it employs.
• Bug #98639 - "Redundant row-level locking for secondary index". I think this simple case pointed out by Sergei Muraviev is a yet another case of excessive locking where improvement is possible. I do not agree with simple "Not a bug" status.
• Bug #98642 - "CONSISTENT SNAPSHOT CAN BE CORRUPTED BY OTHER TRANSACTIONS". This is a really serious bug found by Erwan MAS. It took me some efforts to make sure this bug report was properly processed and verified (my comment in the bug report is hidden, but that does not matter as long as it was really treated seriously). Looks like MySQL 8.0.x may be not affected, but recent 5.7.x and 5.6.x versions are affected for sure. Take care!
• Bug #98651 - "Inserting into temporary table from function breaks replication in MIXED mode". This regression bug in MySQL 8 (vs 5.7.29) was reported by Alexey Gavrilov, who had create a separate GitHub repository for the test case.
• Bug #98665 - "replication broken on blackhole node if binlog_rows_query_log_events on master". This bug was reported by Zhenghu Wen. Take care if you use BLACKHOLE tables on slaves. Both MySQL 8.0.19 and 5.7.29 are affected.
• Bug #98673 - "Allow hints to reference query block by system name". I like optimizer hints implementation in MySQL. This feature request to make them even more useful and less confusing in case of complex queries. As Kaiwang CHen put it:
  

  "Note that the query blocks are internally identified with a number
  (SELECT_LEX::select_number), with which system names are defined. That
  system name could be explored to refer to any query block in the query."

  I do not see a patch in the bug report, but there was a plan to provide it.

I have few more bugs in my list for February, but let's continue with them next time. We have many boring weeks ahead it seems...

Frida is always searching for something... I do the same with MySQL bugs.

To summarize:

1. Group replication still has a lot of problems to resolve before becoming a really mature solution. Ask Galera developers :) 
2. There are regressions in MySQL 8.0.19.
3. Percona and Alibaba engineers still help to make MySQL better.
4. I still do not see a consistent use of "regression" tag for verified regression bugs. This is unfortunate.
5. InnoDB locking still needs more attention.
6. There is too much Docker usage in the industry for my linking...

Fun with Bugs #93 - On MySQL Bug Reports I am Subscribed to, Part XXVII

No matter what I write and present about dynamic tracing, blog posts about MySQL bugs are more popular based on statistics. So, to make more readers happy, I'd like to continue my review of interesting bugs reported in November with this post on bugs reported during December, 2019.

As usual, I'll try to concentrate on bug reports related to InnoDB, replication and optimizer, but some other categories also got my attention:

• Bug #97911 - "[FATAL] Semaphore wait has lasted > 600 seconds. We intentionally crash the serv...". This bug got marked as a duplicate of other, older long semaphore wait bug (in "Can't repeat" status!) without much analysis. I think all Oracle engineers who added comments to that bug missed one interesting point:
  

  ... [ERROR] [MY-012872] [InnoDB] Semaphore wait has lasted > 39052544 seconds.

  even though bug reporter highlighted it in a comment. Reported wait time is a problem and is surely a bug, no matter how to reproduce the long wait itself and what is its root cause!
• Bug #97913 - "Undo logs growing during partitioning ALTER queries". This bug (affecting only MySQL 5.7.x) was reported by Przemyslaw Malkowski from Percona, who also presented useful examples of monitoring queries to the information_schema.innodb_metrics and performance_schema. Check also comments that may explain why 8.0 is not affected in a similar way.
• Bug #97935 - "Memory leak in client connection using information_schema". It took some efforts (starting from but not limited to Valgrind Massif profiling of heap memory usage) and time for Daniel Nichter to prove the point and get this bug "Verified". It is also not clear if MySQL 8 is also affected.
• Bug #97950 - "buf_read_page_handle_error can trigger assert failure". Bug reporter, Shu Lin, tried his best to make the point. It's clear enough how to repeat this, and one could use one of documented test synchonisation methods if gdb is too much for bug verification. I do not think this bug was handled properly or got the level of attention it truly deserved.
• Bug #97966 - "XA COMMIT in another session will not write binlog event". This bug was reported by Lujun Wang and immediately verified, but again with no documented check if MySQL 8 is affected. This happens too often, unfortunately.
• Bug #97971 - "Roles not handling column level privileges correctly; Can SELECT, but not UPDATE". Clear and simple bug report with a workaround from Travis Bement. It was immediately verified.
• Bug #98014 - "Lossy implicit conversion in conditional breaks ONLY_FULL_GROUP_BY". Yet another case of (IMHO) improper bug processing. The argument presented (from the manual):
  

  "MySQL 5.7.5 and later also permits a nonaggregate column not named in a GROUP BY clause when ONLY_FULL_GROUP_BY SQL mode is enabled, provided that this column is limited to a single value"

  does not apply, as "single value" for = 0 is NOT selected, we have multiple Host values matching it due to conversion. This is how proper version (guess what it is) works:
  

  mysql> SELECT User, Host, COUNT(*) FROM mysql.user WHERE Host = 0 GROUP BY 1;
  ERROR 1055 (42000): 'mysql.user.Host' isn't in GROUP BY
  mysql> select @@sql_mode;
  +--------------------+
  | @@sql_mode         |
  +--------------------+
  | ONLY_FULL_GROUP_BY |
  +--------------------+
  1 row in set (0.001 sec)
  
  mysql> set session sql_mode='';
  Query OK, 0 rows affected (0.029 sec)
  
  mysql> SELECT User, Host, COUNT(*) FROM mysql.user WHERE Host = 0 GROUP BY 1;+---------------+-----------+----------+
  | User          | Host      | COUNT(*) |
  +---------------+-----------+----------+
  | data_engineer |           |        1 |
  | en            | localhost |        1 |
  | ro1           |           |        1 |
  | ro2           |           |        1 |
  | role-1        |           |        1 |
  | root          | ::1       |        3 |
  ...
  | user1         | %         |        1 |
  +---------------+-----------+----------+
  13 rows in set, 17 warnings (0.003 sec)

  I think this bug reported by Joshua Varner must be verified.
• Bug #98046 - "Inconsistent behavior while logging a killed query in the slow query log". Bug reporter, Pranay Motupalli, provided a clear test case and a detailed analysis, including the gdb debugging session that proves the point. Nice bug report.
• Bug #98055 - "MySQL Optimizer Bug not picking right index". Both the bug reporter (Sudheer Gadipathi) and engineer who verified the bug stated that MySQL 8.0.x is similarly affected (UNIQUE key is preferred for the partitioned table, even though there is a better non-unique index). But 8.0.x is NOT listed in the "Version:" filed. Weird.
• Bug #98068 - "SELECT FOR UPDATE not-exist table in PERFORMANCE SCHEMA reports confusing error". This is a funny (but still a regression) bug report by William ZHANG. Proper versions work like this:
  

  mysql> select database();+--------------------+
  | database()         |
  +--------------------+
  | performance_schema |
  +--------------------+
  1 row in set (0.001 sec)
  
  mysql> select * from not_exist_table;
  ERROR 1146 (42S02): Table 'performance_schema.not_exist_table' doesn't exist
  mysql> select * from not_exist_table for update;
  ERROR 1146 (42S02): Table 'performance_schema.not_exist_table' doesn't exist

• Bug #98072 - "innochecksum summary shows blob pages as other type of page for 8.0 tables". The bug was reported by SERGEY KUZMICHEV. This time "regression" tag is missing, even though it's clearly stated that MySQL 5.7 worked differently. This is from the proper version:
  

  ...
  File::..\data\test\blob_test.ibd
  ================PAGE TYPE SUMMARY==============
  #PAGE_COUNT     PAGE_TYPE
  ===============================================
         1        Index page
         0        Undo log page
         1        Inode page
         0        Insert buffer free list page
       508        Freshly allocated page
         1        Insert buffer bitmap
         0        System page
         0        Transaction system page
         1        File Space Header
         0        Extent descriptor page
        64        BLOB page
         0        Compressed BLOB page
         0        Page compressed page
         0        Page compressed encrypted page
         0        Other type of page
  ...
  

• Bug #98083 - "Restarting the computer when deleting the database will cause directory residues". One would expect that MySQL 8 with a data dictionary should have some means to figure out the remaining database directory for a dropped database upon startup (as it stores information about databases elsewhere) and do proper cleanup. I think this bug reported by Jinming Liao must be verified and fixed. There is no "... manual creation or deletion of tables or databases..." involved in this case.
• Bug #98091 - "InnoDB does not initialize raw disk partitions". As simple as that and both 5.7.29 and 8.0.219 are surely affected. It was not always the case, I've used raw devices myself with older MySQL versions, so this bug reported by Saverio M is a regression. Still, "regression" tag is missing.

That's all bugs reported in December, 2019 that I cared to subscribe to  and mention here. Next time I'll check bugs reported in January, 2020. There are at least 16 in my list already, so stay tuned.

Follow the links in this post to get more details about profiling and creating off-CPU FlameGraphs for MySQL. This post is devoted to bugs, though :)

To summarize:

1. I am happy to see bug reports from people whom I never noticed before. MySQL Community is alive.
2. Some flexibility in following common sense based bugs verification procedures is still visible. Bugs reported for 5.7 are not checked on 8.0 (or the results of this check are not documented in public), nobody cares to read what bug reporter says carefully or go extra mile, "regression" tag not added, and so on. 
3. Probably at this stage my writings are mostly ignored by Oracle's decision makers. But I keep watching them all anyway.

Fun with Bugs #76 - On MySQL Bug Reports I am Subscribed to, Part XIII

Holidays season is almost over here, so it's time to get back to my main topic of MySQL bugs. Proper MySQL bug reporting will be a topic of my FOSDEM 2019 talk in less than 4 weeks (and few slides with recent examples of bugs are not yet ready), so I have to concentrate on bugs.

Last time in this series I reviewed some interesting bug reports filed in November, 2018. Time to move on and proceed with bugs reported in December, 2018, as I've subscribed to 27 or so of them. As usual, I'll review them briefly starting from the oldest and try to check if MariaDB 10.3 is also affected when the bug report is about common features:

• Bug #93440 - "Noop UPDATE query is logged to binlog after read_only flag is set". Nice corner case found by Artem Danilov. super_read_only, even if set to ON successfully, may not prevent from committing and advancing GTID value.
• Bug #93450 - "mysqldump does not wrap SET NAMES into mysql-extension comment". This is a regression bug in MySQL 8.0 that may break compatibility with 3rd party tools not aware of MySQL extensions. This bug was reported by Mattias Jonsson.
• Bug #93451 - "The table comment is cut down on selecting with ORDER BY". Nice regression in MySQL 8. As one can easily check, MariaDB 10.3.x and older MySQL versions are not affected.
• Bug #93491 - "Optimizer does not correctly consider attached conditions in planning". Clear and useful bug report from Morgan Tocker.
• Bug #93544 - "SHOW BINLOG EVENTS FROM <bad offset> is not diagnosed". Yet another regression bug in MySQL 8 found by Laurynas Biveinis from Percona. MariaDB 10.3 does not accept bad offsets:
  

  MariaDB [test]> show binlog events from 14 limit 1;
  ERROR 1220 (HY000): Error when executing command SHOW BINLOG EVENTS: Wrong offset or I/O error
  MariaDB [test]> show binlog events limit 4;
  +------------------+-----+-------------------+-----------+-------------+--------
  ---------------------------------------+
  | Log_name         | Pos | Event_type        | Server_id | End_log_pos | Info
                                         |
  +------------------+-----+-------------------+-----------+-------------+--------
  ---------------------------------------+
  | pc-PC-bin.000001 |   4 | Format_desc       |         1 |         256 | Server
  ver: 10.3.7-MariaDB-log, Binlog ver: 4 |
  | pc-PC-bin.000001 | 256 | Gtid_list         |         1 |         285 | []
                                         |
  | pc-PC-bin.000001 | 285 | Binlog_checkpoint |         1 |         328 | pc-PC-b
  in.000001                              |
  | pc-PC-bin.000001 | 328 | Gtid              |         1 |         370 | GTID 0-
  1-1                                    |
  +------------------+-----+-------------------+-----------+-------------+--------
  ---------------------------------------+
  4 rows in set (0.002 sec)
  
  MariaDB [test]> show binlog events from 256 limit 1;
  +------------------+-----+------------+-----------+-------------+------+
  | Log_name         | Pos | Event_type | Server_id | End_log_pos | Info |
  +------------------+-----+------------+-----------+-------------+------+
  | pc-PC-bin.000001 | 256 | Gtid_list  |         1 |         285 | []   |
  +------------------+-----+------------+-----------+-------------+------+
  1 row in set (0.002 sec)

• Bug #93572 - "parallel workers+slave_preserve_commit_order+flushtables with read lock deadlock". I subscribed to it as it's just yet another example of improper handling of useful bug reports, as already discussed in my post "Problems with Oracle's Way of MySQL Bugs Database Maintenance". I think Ashe Sun's point is clear and suggestions like "don't do it" have nothing to do with proper bugs processing.
• Bug #93587 - "Error when creating a table with long partition names". Nice regression bug in MySQL 8 comparing to 5.7 was found by Sergei Glushchenko from Percona.
  
  MariaDB 10.3.7 on Windows also fails with error message that is not clear:
  

  ERROR 1005 (HY000): Can't create table `mc5noglq9ofy7ym76z1t758ztptj6iplvsldhmse
  xt63mlvhcpew4dnu2opqdrre`.`th6edxfx5d1u5blb3i50ln5dfo415jirp9xkuc0h9o2ionkql3iom
  fyw4zvocfpp` (errno: 168 "Unknown (generic) error from engine")

  In the error log I see:
  

  2019-01-06 19:36:46 10 [ERROR] InnoDB: Operating system error number 3 in a file operation.
  2019-01-06 19:36:46 10 [ERROR] InnoDB: The error means the system cannot find the path specified.
  2019-01-06 19:36:46 10 [ERROR] InnoDB: File .\mc5noglq9ofy7ym76z1t758ztptj6iplvs
  ldhmsext63mlvhcpew4dnu2opqdrre\th6edxfx5d1u5blb3i50ln5dfo415jirp9xkuc0h9o2ionkql
  3iomfyw4zvocfpp#p#o8w7066agxadomywht89twmbjomtfdmdc74wj7iupkd75lvu1enov1j008sjbk
  kf#sp#ywkq987ztkdj33zbmlw526153x86vxl4x44r15spf8jqs92665mt0qi6bsnkazy5.ibd: 'cre
  ate' returned OS error 203.
  2019-01-06 19:36:46 10 [ERROR] InnoDB: Cannot create file '.\mc5noglq9ofy7ym76z1
  t758ztptj6iplvsldhmsext63mlvhcpew4dnu2opqdrre\th6edxfx5d1u5blb3i50ln5dfo415jirp9
  xkuc0h9o2ionkql3iomfyw4zvocfpp#p#o8w7066agxadomywht89twmbjomtfdmdc74wj7iupkd75lv
  u1enov1j008sjbkkf#sp#ywkq987ztkdj33zbmlw526153x86vxl4x44r15spf8jqs92665mt0qi6bsn
  kazy5.ibd'

• Bug #93600 - "Setting out of range fractional part produces incorrect timestamps". After some arguing this bug reported by Evgeny Firsov was "Verified". In MariaDB 10.3 truncation happens:
  

  MariaDB [test]> SET SESSION TIMESTAMP=1.9999996;
  Query OK, 0 rows affected (0.039 sec)
  
  MariaDB [test]> SELECT CURRENT_TIMESTAMP(6);
  +----------------------------+
  | CURRENT_TIMESTAMP(6)       |
  +----------------------------+
  | 1970-01-01 02:00:01.999999 |
  +----------------------------+
  1 row in set (0.010 sec)
  
  MariaDB [test]> CREATE TABLE t1( ts TIMESTAMP(6), dt DATETIME(6) );
  Query OK, 0 rows affected (0.387 sec)
  
  MariaDB [test]> INSERT INTO t1 values (CURRENT_TIMESTAMP(6), CURRENT_TIMESTAMP(6));
  Query OK, 1 row affected (0.079 sec)
  
  MariaDB [test]> select * from t1;
  +----------------------------+----------------------------+
  | ts                         | dt                         |
  +----------------------------+----------------------------+
  | 1970-01-01 02:00:01.999999 | 1970-01-01 02:00:01.999999 |
  +----------------------------+----------------------------+
  1 row in set (0.016 sec)

• Bug #93603 - "Memory access error with alter table character change." This bug was reported by Ramesh Sivaraman from Percona QA. I've subscribed mostly to find out how bug reports with new severity level (S6) are going to be processed and fixed. See also his Bug #93701 - "Assertion `maybe_null' failed |Item_func_concat::val_str(String*)".
  
  I've subscribed to S7 Bug #93617 - "Conditional jump or depends on uninitialized value(s) in Field_num::Field_num" from Laurynas Biveinis for similar reason.
• Bug #93649 - "STOP SLAVE SQL_THREAD deadlocks if done while holding LOCK INSTANCE FOR BACKUP". New MySQL 8 feature, LOCK INSTANCE FOR BACKUP statement, is an attempt to introduce backup locks to MySQL. Sergei Glushchenko found that it may cause deadlocks. I am surprised that the bug is still "Open", since December 18, 2018.
• Bug #93683 - "Got error 155 when reading table './test/t1'". I am not sure if this message in the error log noted by Roel Van de Paar from Percona is a bug or a problem. MariaDB 10.3.7 produces the same error message. Let's find out, so far this report is "Verified".
• Bug #93684 - "mysql innodb dump restore slows down after upgrade mysql 5.7 to 8.0". Florian Kopp reported this potential notable performance regression of MySQL 8.0.13 vs 5.7, but it is still not verified. I am not sure how this report may end up, but it's not the first report about performance regressions in MySQL 8 :)

One can hardly find any bugs in this winter forest. But they are hiding and will affect everyone there one day, in spring...

To summarize:

1. Some regression bugs are still not marked with "regression" tag.
2. Some MySQL bug reports are still handled wrongly, with a trend of wasting bug reporter time on irrelevant clarifications and claims the problem is not clear, when there is a good enough explanation of the test case.
3. Percona engineers still contribute a lot of MySQL 8 QA, by reporting numerous bugs. No wonder with their first Percona Server for MySQL 8 GA release happened in December...

I still have a dozen or so December 2018 bug reports (mostly "Open" at the moment) to review one day, so stay tuned!

Fun with Bugs #75 - On MySQL Bug Reports I am Subscribed to, Part XII

From the lack of comments to my previous post it seems everything is clear with ERROR 1213 in different kinds and forks of MySQL. I may still write a post of two about MyRocks or TokuDB deadlocks one day, but let's get back to my main topic of MySQL bugs. Today I continue my series of posts about community bug reports I am subscribed to with a review of bugs reported in November, 2018, starting from the oldest and skipping those MySQL 8 regression ones I've already commented on. I also skip documentation bugs that should be a topic for a separate post one day (to give more illustration to these my statements).

These are the most interesting bug reports from Community members in November 2018:

• Bug #93139 - "mysqldump temporary views missing definer". This bug reported by Nikolai Ikhalainen from Percona looks like a regression (that can appear in a bit unusual case of missing root user) in all versions starting from 5.6. There is no regression tag, surely. Also for some reason I do not see 8.0.x as affected version, while from the text it seems MySQL 8 is also affected.
• Bug #93165 - "Memory leak in sync_latch_meta_init() after mysqld shutdown detected by ASan". This bug was reported by Yura Sorokin from Percona, who also made important statement in his last comment (that I totally agree with):
  

  "In commit https://github.com/mysql/mysql-server/commit/e93e8db42d89154b37f63772ce68c1efda637609 you literally made 14 MTR test cases ignore ALL memory problems detected by ASan, not only those which you consider 'OK' when you terminate the process with the call to 'exit()'. In other words, new memory leaks introduced in FUTURE commits may not be detected because of those changes. Address Sanitizer is a very powerful tool and its coverage should be constantly extending rather than shrinking."

• Bug #93196 - "DD crashes on assert if ha_commit_trans() returns error". It seems Vlad Lesin from Percona spent notable time testing everything related to new MySQL 8 data dictionary (maybe while Percona worked on their Percona Server for MySQL 8.0 that should have MyRocks also supported, should be able to provide native partitioning and proper integration with data dictionary). See also his Bug #93250 - "the result of tc_log->commit() is ignored in trans_commit_stmt()".
• Bug #93241 - "Query against full text index with ORDER BY silently fails". Nice finding by Jonathan Balinski, with detailed test cases and comments added by Shane Bester. One more confirmation that FULLTEXT indexes in InnoDB are still problematic.
• Bug #93276 - "Crash when calling mysql_real_connect() in loop". Nice regression in C API (since 8.0.4!) noted by Reggie Burnett and still not fixed.
• Bug #93321 - "Assertion `rc == TYPE_OK' failed". The last but not the least, yet another debug assertion (and error in non-debug build) found in MySQL 8.0.13 by Roel Van de Paar from Percona. You already know where QA for MySQL happens to large extent, don't you?
• Bug #93361 - "memory/performance_schema/table_handles have memory leak!". It's currently in "Need Feedback" status and may end up as not a bug, but I've never seen 9G of memory used for just one Performance Schema table so far. It's impressive.
• Bug #93365 - "Query on performance_schema.data_locks causes replication issues". Probably the first case when it was proved that query to some Performance Schema table may block some important server activity. Nice finding by Daniël van Eeden.
• Bug #93395 - "ALTER USER succeeds on master but fails on slave." Yet another way to break replication was found by Jean-François Gagné. See also his Bug #93397 - "Replication does not start if restart MySQL after init without start slave."
• Bug #93423 - "binlog_row_image=full not always honored for binlog_format=MIXED". For some reason this bug (with a clear test case) reported by James Lawrie is still "Open".
• Bug #93430 - "Inconsistent output of SHOW STATUS LIKE 'Handler_read_key';". This weird inconsistency was found by Przemysław Skibiński from Percona.

Thinking about the future of MySQL 8 somewhere in Greenwich...

To summarize this review:

1. I obviously pay a lot of attention to bug reports from Percona engineers.
2. It seems memory problems detected by ASan in some MTR test cases are deliberately ignored instead of being properly fixed.
3. There are still many surprises waiting for early adopters of MySQL 8.0 GA :) 

That's all I have to say about specific MySQL bugs in 2018. Next "Fun with Bugs" post, if any, will appear only next year. I am already subscribed to 11 bugs reported in December 2018. Stay tuned!

Fun with Bugs #73 - On MySQL Bug Reports I am Subscribed to, Part X

It's time to continue my review of MySQL bug reports that I considered interesting for some reason recently. I had not got any notable reaction from Oracle engineers to my previous post about recent regression bugs in MySQL 8.0.13, so probably this topic is not really that hot. In this boring post I'll just review some bugs I've subscribed to since August that are still not closed, starting from the oldest.

Let me start with a couple of bug reports that remain "Open":

• Bug #91959 - "UBSAN: signed integer overflow in lock_update_trx_age". It's really unusual to see bug reported by Shane Bester himself just "Open" for months. As he noted, it's really strange to see age defined as 32-bit value here when age_updated two lines later is 64-bit. On the other hand, from comments in the code it seems the age value is supposed to be small enough and if it grows too much this is a problem.
• Bug #92964 - "Slave performance degrades over time". As description says:
  

  "When mysql slave is running gtid based asynchronous multi threaded replication, performance degrades over time in significant way when session_track_gtids is set to OWN_GTID; "

  The bug is assigned, but there are no public comments, while the way to reproduce seem to be defined clearly.

Same as the weather at Seven Sisters Cliffs back on September 14, 2018, some recent MySQL bug reports do not add confidence to those few visitors who explore them...

Now let me continue with some "Verified" bugs:

• Bug #91981 - "Inconsistent user@host definitions for definer/grantor/grantee columns". It seems consistency is not a high priority these days.
• Bug #92020 - "Introduce new SQL mode rejecting queries with results depending on query plan". I already mentioned this report while reviewing recent MySQL optimizer bugs. I have nothing to add to my conclusion there:
  

  "So, current MySQL considers different results when different execution plans are used normal!"

• Bug #92032 - "Restrict usage of session foreign_key_checks". It's a great request from Federico Razzoli and I am happy to see it verified as a bug, fast.
• Bug #92093 - "Replication crash safety needs relay_log_recovery even with GTID." Recently Jean-François Gagné pays special attention to MySQL replication crash safety in his bug reports, talks and blog posts. This specific report ended up as a request for a more clear documentation that should not provoke (false) safety feelings. See also his Bug #92109 - "Please make replication crash safe with GTID and less durable setting (bis)." for the request to improve MySQL in this regard one day.
• Bug #92131 - "ASan: Direct leak of 272 byte(s) in main.mysqlpump_partial_bkp MTR test case". This kind of reports make me nervous. They make me think that still there is no regular testing of ASan-enabled builds in Oracle. Lucky we are, Percona engineers (like Yura Sorokin) do this for Oracle.
• Bug #92133 - "DICT_SYS mutex contention causes complete stall when running with 40 mill tables". Proper data dictionary in MySQL 8.0 was supposed to solve some performance problems for instances with a lot of tables. As this perfect bug report from Alexander Rubin shows, this is not yet the case. We can still push MySQL instance over the limits and single instance for thousands of different small databases is hardly usable.
• Bug #92209 - "AVG(YEAR(datetime_field)) makes an error result because of overflow". Perfect analysis from Pin Lin. All recent MySQL (and MariaDB) versions are affected.
• Bug #92252 - "Mysql generates gtid gaps because of option slave-skip-errors". Yet another bug report from Pin Lin. 
• Bug #92364 - "events_transactions_summary_global_by_event_name not working as expected". This bug was reported by Przemyslaw Malkowski from Percona. We all know Performance Schema is near perfect, but it turned out that "...performance_schema.events_transactions_summary_global_by_event_name behavior for instrumenting transactions seems completely broken.".
• Bug #92398 - "point in time recovery using mysqlbinlog broken with temporary table -> errors". Let me quote Shane Bester:
  

  "Running a multi-threaded workload involving temporary and non-temporary tables leads to binary log playback giving errors."

• Bug #92421 - "Queries with views and operations over local variables don't use indexes". This is a kind of regression in MySQL 5.7. MySQL 8 allows to workaround the problem properly using ROW_NUMBER() function.
• Bug #92540 - "Comparison between DATE and DECIMAL does not round nanoseconds". This bug was reported by Alexander Barkov from MariaDB. MariaDB 10.3.x is not affected based on my tests.

To summarize:

1. It seems ASan-enabled builds are not tested by Oracle engineers on a regular basis.
2. Percona engineers help a lot with regular MySQL QA and testing for various extreme use cases.
3. There is a lot to do to make GTID-based replication crash-safe and working fast in common use cases.

 More bug review are coming soon, so stay tuned.

Fun with Bugs #55 - On Some Public Bugs Fixed in MySQL 8.0.2

I do not care much about MySQL 8.0.x at the moment, as it's far from being GA and is work in progress. It is not yet used by customers whom I have to support. But I know about many interesting changes and improvements there that, eventually, are going to influence all main forks and kinds of MySQL. So, it would not be wise to ignore MySQL 8.0.c entirely even for me.

For this post I decided to briefly check what community reported bugs were fixed in the recent release, 8.0.2, based on release notes. For me it's a measure of community interest in MySQL 8.0.x and Oracle's interest in further working with MySQL Community. I ended up with the following, short enough list of bug fixes in the categories I usually care about (InnoDB, partitioning, replication and optimizer):

• The very first InnoDB bug mentioned in the release notes, Bug #85043, is private. I fail to see any valid reason for a bug in the version currently under development and not declared GA to remain private after the fix is released. If only it affects GA versions, and this is the case. The bug is fixed in 5.7.19 as well, as you can see in my previous post.
• Another bug that is related to InnoDB and optimizer, is Bug #81031. It was also fixed in MySQL 5.6.37 and 5.7.19.
• Bug #84038 - "Errors when restarting MySQL after FLUSH TABLES FOR EXPORT, RENAME and DROP", was also fixed in MySQL 5.7.19. I am actually surprised that as this stage we still have older InnoDB internal data dictionary tables in MySQL 8.0.x.
• Group replication related Bug #85667, Bug #85047, Bug #84728  and Bug #84733 were also listed as fixed in MySQL 5.7.19.
• Same situation with normal async replication bugs: Bug #83184, Bug #82283, Bug #81232, Bug #77406 etc. It's expected to see fixes applied to the oldest version affected and then fixes merged to newer versions.
• The first really unique fix in 8.0.2 that I found was Bug #85639 - "XA transactions are 'unsafe' for RPL using SBR". It was reported by João Gramacho (who probably works for Oracle) originally for MySQL 5.7 and is going to be fixed in MySQL 5.7.20 also.
• Replication-related Bug #85739 is still private. Release notes say:
  

  "Issuing SHOW SLAVE STATUS FOR CHANNEL 'group_replication_recovery' following a restart of a server using group replication led to an unplanned shutdown."

• Yet another private replication bugs fixed in 8.0.2 are: Bug #85405, Bug #85084, Bug #84646, Bug #84471, Bug #82467 and Bug #80368. I do not know who reported them, what versions are affected (but I suspect .5.7.x also) and why are they remaining private after being fixed in 8.0.2.
• The first 8.0.x specific public bug report I've found in the release notes was reported by Andrey Hristov and verified by Umesh Shastry. It is Bug #85937 - "Unchecked read after allocated buffer".
• Bug #86120 - "Upgrading a MySQL instance with a table name of >64 chars results in breakage", was reported by Daniël van Eeden and verified by Umesh Shastry. I expect a lot of problem reports when users starts to upgrade to 8.0... See also Bug #84889 - "MYSQL 8.0.1 - MYSQLD ERRORLOG UPGRADE ERRORS AT SERVER START LIVE UPGRADE", by Susan Koerner.
• Bug #85994 - "Out-of-bounds read in mysqld.cc fix_paths", was reported by Laurynas Biveinis and verified by Umesh Shastry. Percona seems to care a lot about improving MySQL 8.0 (as well as other Oracle MySQL GA versions). See also Bug #85678 by Laurynas and Bug #85059 by Roel Van de Paar.
• Jon Olav Hauglid seems to care about the new data dictionary code quality, so he had reported related public bugs: Bug #85811, Bug #85800, and Bug #83473.
• Bug #85704 - "mysql 8.0.x crashes when old-style trigger misses the "created" line in .TRG", was reported by Shane Bester. Workaround was also suggested by Jesper Krogh. Shane also reported this nice regression Bug #83019 - "queries in "show processlist" oscillate with constant times higher each day", that is now fixed in 8.0.2.
• Bug #85614 - "alter table fails when default character set changes to utf8mb4", was reported by Tor Didriksen. He had also reported Bug #85224 - "Illegal mix of collations for time/varchar".
• Bug #85518 - "Distinct operations on temp tables allocate too little memory for sort keys", was reported by Steinar Gunderson. He had also reported Bug #85487 - "num_tmp_files in filesort optimizer trace is nonsensical".
• Bug #85179 - "Assert in sql/field.cc:... virtual String* Field_varstring::val_str", was reported by Matthias Leich.

I skipped several bugs that are fixed also in older versions. Many of them were already discussed in my posts. I also skipped all build/compilation/packaging bugs for now.

To summarize, while total number of public bug reports fixed in MySQL 8.0.2 is notable, many of these bugs were reported by few Oracle engineers who are still brave enough to report bugs in public. From Community, it seems only mostly Percona and Booking.com engineers do care to check MySQL 8.0.x at this early stage and report bugs. I am especially concerned with the number of private bug reports mentioned in the release notes of 8.0.2...