On MySQL Replication Bugs

While writing about problematic Oracle MySQL features previously I concentrated mostly on InnoDB problems that I have to fight with really often and deliberately skipped replication from even the preliminary list of features to study in details for that blog post. First of all, I mostly work with MariaDB users now, and implementation of many replication features in MariaDB is notably different already (and has its own list of known problems). But this happened also because (asynchronous) replication plays a key role in most MySQL environments and deserves a detailed study in a separate post.

It took me some time to prepare lists of recent bugs reported for MySQL replication (row-based and statement-based, GTIDs, multi-threaded and crash safe, hopefully) and then try to summarize my findings from them. Looks like I have something related to share today, finally.

Gondolas in Venice often look very similar to each other. They all are probably replicas of few different original designs. But small differences exist. This should not happen to your data in a replication setup...

Let me start with a summary of problems I found and then proceed with the lists of bug reports I based my conclusions on:

1. Both minor problems with mysqlbinlog outputs, small regressions, missing useful features and real bugs (that may lead to data differences on slaves or break replication), hangs (including MTS deadlocks), crashes and performance problems with replication that can affect production setups do not seem to get high priority over last 2 years. Even those reported in public bugs database by famous MySQL Community members or current and former Oracle MySQL engineers.
2. GTIDs and replication crash safety features were supposed to make it easier to use replication in production. In reality there are some GTID-specific ways to break replication, and it is still not as crash safe as expected in many use cases.
3. Multi-threaded slave (a.k.a MTS or parallel replication) bugs, even serious and verified, are ignored for way more than last 2 years! The feature is not completed and may not help as much as one may expect.
4. Manual is still wrong or missing when describing some details of current replication implementation.
5. Nobody cares much about debug assertions found by Percona QA. We may have some bombs in the replication code that may explode in a weird way any time. 
6. It seems group replication bugs often get proper attention fast, but nobody in Oracle has enough time to deal with asynchronous replication bugs properly. Some of them hang around without even proper verification for months...

Now let's get into details. Here is the list of some recent row-based replication specific bugs and feature requests:

• Bug #91822 - "incorrect datatype in RBR event when column is NULL and not explicit in query". The column missing in the INSERT statement is given the datatype of the column immediately preceding it. This bug was reported by Ernie Souhrada back in July.
• Bug #89339 - "RBR slave index choosing logic is too simple". As Fungo Wang pointed out based on source code review, it is based on rules, not considered data distribution at all. Slave uses primary key if it exists, otherwise tries unique key that does not allow NULLs (if any), then tries other keys and finally falls back to full table scan if there are no indexes. When there is no primary or unique key on columns not allowing NULL values, no statistics is taken into account when picking up indexes vs full table scan, and this easily leads to bad plans. Surely, one has to have primary keys in all tables, especially those involved in replication, but it would be great if slave code was a bit smarter in corner cases (as MariaDB's one).
  By the way, setting slave_rows_search_algorithms to HASH_SCAN may not help, as it may be too slow in practice. See Bug #84887 by Dennis Gao for more details and an example of perf and gdb usage for the analysis.
• Bug #89297 - "please allow number to varchar slave_type_conversions if target is large enough". Nice feature request by Shane Bester.
• Bug #88791 - "Binary log for generated column contains new value as WHERE clause, not old value". This bug was reported by MC Brown. Surely, mysqlbinlog output for row-based events is not supposed to be 100% valid SQL for copy-paste, but it should not be misleading.
• Bug #88595 - "Row-based master-master replication broken by add column or drop column". This is more a documentation issue. It's strange to see this limitation not documented here.
• Bug #88223 - "Replication with no tmpdir space and InnoDB as tmp_storage_engine can break". It's really weird to see this bug reported by Sveta Smirnova hanging in "Analyzing" status for almost a year!
• Bug #88057 - "Intermediary slave does not log master changes with binlog_rows_query_log_events". Yet another bug report from Sveta, this time "Verified". Row query log events for UPDATEs and DELETEs are NOT recorded in the binary log by the intermediate masters...
• Bug #85292 - "unreachable statement in function process_event with event type QUERY_EVENT". Shane Bester found a part of the code that probably should be rewritten. No further public attention to this report for 1.5 years...
• Bug #84287 - "row inserts, statement updates, persistent stats lead to table scans+ lag slaves". Yet another great finding by Shane Bester. As often happens, another problematic feature I truly hate, InnoDB's persistent statistics, is also involved in this case, leading to bad performance on slave.
• Bug #83431 - "RBR slave's "BEGIN" binlog query event doesn't carry a session database name". It was reported almost 2 years ago by Teng Li.
• Bug #83418 - "When binlog_row_image is MINIMAL, some updates queries logs redundant columns". This was reported by Abhinav Sharma also almost two years ago.
• Bug #83090 - "AUTO_INCREMENT not updated when using INSERT ON DUPLICATE KEY UPDATE". This bug was reported by Monty Solomon two years ago. See also his related Bug #83030 - "AUTO_INCREMENT not updated when using REPLACE INTO".
• Bug #80821 - "Replication breaks if multi-table DELETE is used in conjunction with Foreign Key". I do not understand why this perfect (regression?) bug report by Philip Stoev, with a clear MTR test case was immediately verified and then just hangs around for 2.5 years...
• Bug #80770 - "Compression of row-based event". Great feature contributed by Davi Arnaut.
• Bug #79831- "Unexpected error message on crash-safe slave with max_relay_log_size set". Let me just conclude this with a 5.6-spcific (probably) bug report by yours truly...

I have to stop at this point, as other RBR-related bug reports are known for way more time and probably are just forgotten for good already by MySQL development. They hardly have a chance for the fix any time soon.

Let's continue with replication bugs that are not closely related to row-based binlog format:

• Bug #92398 - "point in time recovery using mysqlbinlog broken with temporary table -> errors". Shane Bester found yet another case when statement-based binlog format may lead to problems for point in time recovery.
• Bug #92252 - "Mysql generates gtid gaps because of option slave-skip-errors". This bug was reported by Pin Lin. We get gaps in GTID values on slave when errors are skipped. If binlog files on master include transactions which were applied on slave and hit errors in slave-skip-errors were purged, slave can't reconnect to master and reports error 1236. Features that were supposed to make replication more robust lead to more breaks in replication.
• Bug #92132 - "secure-file-priv breaks LOAD DATA INFILE replication in statement mode on 5.7.23". This regression bug was reported by Nicolai Plum. Recent MySQL 5.7 and 8.0 versions are affected.
• Bug #92109 - "Please make replication crash safe with GTID and less durable setting (bis)." Jean-François Gagné keeps looking for options to make replication really crash safe in all practically important cases.
• Bug #92073 - "int change_master(): Assertion `mi->rli->inited' failed." Roel Van de Paar continues his QA efforts in a hope to make MySQL better one day and reports all debug assertions in public. In this case we seem to have a kind of regression in 8.0 vs 5.7. Check also these his bug reports: Bug #91980 - "Assertion `head->variables.gtid_next.type == ANONYMOUS_GTID' failed." and Bug #90946 - "Assertion `get_state() >= 0' failed on RESET MASTER while tx_read_only". Debug assertions are in the code for a reason usually, so in a latter bug report Roel recently asked for some update...
• Bug #91766 - "Log of replication position when running CHANGE MASTER TO is wrong.". Jean-François Gagné found yet another annoying detail - one would expect the error log to have data about the SQL Thread position, but it has data about the IO Thread position.
• Bug #91744 - "START SLAVE UNTIL going further than it should." Now this bug reported by  Jean-François Gagné is really serious. Still it hangs around "Open" since July 20...
• Bug #91633 - "Replication failure (errno 1399) on update in XA tx after deadlock". XA is yet another MySQL feature that is doomed to stay half-baked it seems... More on this you should expect in a separate blog post one day. The bug that causes replication break was reported by Lukas Sydorowski.
• The last but not the least bug in this list (that is actually long if we try to check all active bugs reported over last 2 years) that I want to include is Bug #90448 - "INSERT IGNORE not ignored on SQL thread", by Simon Mudd. Restarting slave helps and it seems MySQL 8.0.x is not affected. See also his Bug #89876 - "mysqladmin flush-hosts is not safe in GTID mode". 

I have to stop at some point. In conclusion let me review a list of (less) recent bugs in a multi-threaded slave (MTS) implementation specifically:

• Bug #91477 - "Slave writes less when connected intermediary master with blackhole engine". Just 2 threads are used in this case as found by Sveta Smirnova.
• Bug #90342 - "MySQL parallel replication automatically restarts". Nobody cares about this crashing bug for 5+ months. It's still "Open".
• Bug #89247 - "Deadlock with MTS when slave_preserve_commit_order = ON."  Jean-François Gagné reported this 9 months ago, the bug was verifvied and based on last comments and pt-pmp outputs the hang really happen for many users. Still nobody cares in any obvious way.
• Bug #89229 - "FLUSH PRIVILEGES may cause MTS deadlock". This bug was reported by Libing Song 9 months ago.
• Bug #87796 - "Commit_order_manager can't terminate MTS worker properly when deadlock happens". This bug was reported by Seunguck Lee a year ago!
• Bug #85747 - "Documentation on MTS statistics". Yet another case of missing manual reported by Juan Arruti:
  

  "I didn't find information in MySQL reference manual about MTS statistics, I had to look into source code in order to get more information about them."

• Bug #85142 - "reducing MTS checkpointing causes high IO load". The bug reported by Trey Raymond is "Verified" for 1.5 years. Check also his Bug #81386 - "MTS hangs on file related *_EVENT forced checkpoint" and Bug #81385 - "relay log info not updated after using sql_after_mts_gaps" that are "Open" since May 2016!
• Bug #73066 - "Replication stall with multi-threaded replication". Ovais Tariq reported this bug more than 4 years ago, when both still worked in Percona. Nobody cares to fix it.
• Bug #71495 - "Logging of MTS breakage is insufficient." One of Oracle customers who use MTS in production, Simon Mudd had reported this practical problem 4.5+ years ago. No fix, no comments from Oracle engineers from more than 3 years.

I have to stop at this stage, as checking more/older replication related bugs makes me even more sad than I planned or want to be. We see fixes in every release, but the backlog seems huge. Dear Oracle MySQL engineers, please, review public MySQL replication bug reports collected over last 4-5 years and spend some more time and resources on processing and fixing them! Otherwise this Acqua alta of bugs will make this great legacy feature obsolete one sad day.

I had not planned to study semi-sync replication bugs in this post (maybe, some day). If you are interested in more details about this feature, crash safety of replication, parallel replication and replication in general, I suggest you to check J-F Gagné's MySQL Blog.

On Fine MySQL Manual

Today I am going to provide some details on the last item in my list of problems with Oracle's way of MySQL server development, maintenance of MySQL Manual. I stated that:

"MySQL Manual still have many details missing and is not fixed fast enough.
Moreover, it is not open source..."

Let me explain the above:

1. MySQL Reference Manual is not open source. It used to be built from DocBook XML sources. Probably that's still the case. But you can not find the source code in open repositories (please, correct me if I am wrong, I tried to search...) That's because it is NOT open source. It says this clearly in Preface:
   

   "This documentation is NOT distributed under a GPL license. Use of this documentation is subject to the following terms:
   ...
   Any other use, such as any dissemination of printed copies or use of this documentation, in whole or in part, in another publication, requires the prior written consent from an authorized representative of Oracle. Oracle and/or its affiliates reserve any and all rights to this documentation not expressly granted above."

   It was NOT Oracle who closed the source (as far as I remember, the manual was not GPL even in 2004, when I started to care about MySQL in general). But Oracle had a chance to change the license and set up some better contribution process for MySQL Community users, with many good writers among them. They decided not do this, so creators of forks and derived software have to duplicate efforts and rewrite everything themselves, and the only real way to help make the manual better is to report bugs.
   
2. Quality of new documentation is not improved much. We, MySQL Community users, have to report bugs in the manual, as it still has some details missing or documented wrongly. Let me illustrate this with recent 10 or so documentation requests users made (I skipped reports for features I do not care about for now, like group replication):
   
   • Bug #91955 - "8.0 broke plugin API for C". According to the comment from Oracle developer in this bug reported by Laurynas Biveinis, writing plugins in C is not supported any more... But this change is NOT properly documented.
   • Bug #91781 - "Add version matrix in "Chapter 2 Keywords and Reserved Words". Good idea in the manual is not consistently implemented.
   • Bug #91743 - "performance_schema.data_locks not working correctly". Nice feature was added to Performance Schema (now we can check and study InnoDB locks with SQL!), but it is still not completely and properly documented.
   • Bug #91654 - "Handling an Unexpected Halt of a Replication Slave" Documentation is uncertain". This is also an example of improper bug handling - when I worked in Oracle newer bugs were usually declared duplicates of older ones. Here the opposite decision was made, even though both reports were from the same user, Uday Varagani, who explicitly asked to change the decision. Obviously, documentation requests do not get any extra care comparing to other kinds of bugs, quite to the contrary...
   • Bug #91648 - "Numeric literals must be a number between 0 and 9999". Surely ports with numbers larger than 9999 can be used.
   • Bug #91642 - "Document how protocol version 4.0 and 4.1 differ on how strings are terminated". As noted by Rene' Cannao', comments in the code are still sometimes more useful than documentation.
   • Bug #91549 - "MDL lock queue order seems to depend on table names". Make sure to read last comment in this nice request by Shane Bester. Dmitry Lenev provides some details on priorities of MDL locks in it. There are still cases when bugs and documentation requests document some details better than fine MySQL Manual!
   • Bug #90997 - "Handling an Unexpected Halt of a Replication Slave" manual page is wrong". Sveta Smirnova highlighted a totally misleading statement in the manual.
   • Bug #90935 - "Modify relay_log_recovery Documentation". Simple documentation request stays "Open" for 3 months. Definitely processing documentation requests is not a highest priority for Oracle engineers.
   • Bug #90680 - "dragnet logging: document how to use / find error_symbol codes". Even if request comes from a customer or otherwise well known bug reporter, like Simon Mudd, and it's related to some totally new cool feature of MySQL 8, it can wait for the fix for months...
   You can make your own conclusions from the above. But I do not see any good trends in the way new documentation is created or documentation requests are processed recently. Same problems as 4 years ago (see more on that in a side note below).
   
3. Older documentation requests get even less attention than recent ones sometimes, even though they may highlight problems with software itself, not the MySQL Manual. Let me illustrate this with a few bugs I reported:
   
   • Bug #83640 - "Locks set by DELETE statement on already deleted record". I explicitly asked to
     

     "...document in the manual what locks this kind of DELETE sets when it encountered a record already marked as deleted, and why"

     This still had NOT happened. Moreover, eventually both MySQL and MariaDB engineers decided that current implementation of locking for this case is wrong and can be improved, so this report ended up as InnoDB bug. Check related  MDEV-11215 for more details.
   • Bug #73413 - "Manual does not explain MTS implementation in details". This is one of my favorite documentation requests. I've got a suggestion to explain what I want to see documented, in details. I tried my best, but if you want to get more details, read this blog.
   • Bug #72368 - "Empty/zero statistics for imported tablespace until explicit ANALYZE TABLE". This is a bug in the way persistent statistics (the feature I truly hate) in InnoDB is re-estimated "automatically". But until the bug is fixed, I asked to document current implementation (and problems with it). So, where current implementation is properly documented? If only in the bug reports...
   • Bug #71294 - "Manual page for P_S.FILE_INSTANCES table does not explain '~' in FILE_NAME".  They pretend they do not get the point:
     

     "Per Marc Alff, the Performance Schema is simply reporting what it gets from runtime. If runtime is reporting nonexistent filenames, that's a server issue.
     
     Recategorizing this as a server bug."

   • Bug #71293 - "Manual page for P_S.FILE_INSTANCES table does not explain EVENT_NAME values". Now they are waiting for the new DOCUMENTATION column in the setup_instruments table to be filled in with something by server developers... The code is the documentation? OK, bus as we know from the experience (see slides 44-46 here) chances to get a bug in Performance Schema fixed fast are even less than to see it properly and completely documented...

There are more problems with MySQL documentation (not only reference manual), but at the moment I consider 3 highlighted and illustrated above the most serious.

Regent's Canal is nice. If I only knew how to operate the locks there... MySQL Manual also misses some information about locks.

As a side note, it's not the first time I write about MySQL Manual. You can find some useful details in the following posts:

• "What's wrong with MySQL Manual". In 2014, after spending few weeks reporting up to 5 documentation bugs per day, I thought that, essentially, there is nothing much wrong with it - it's fine, well indexed by Google and has meaningful human-readable URLs. Few problems listed were lack of careful readers (I tried my best to fix that), limited attention to old documentation requests, some pages with not so much useful content and lack of "How to" documents. The later I also tried to fix to some small extent in this blog, see howto tagged posts. The real fix came mostly from Percona's blog, though.
• I have a special "missing manual" tag for blog posts that mention at least one bug in the manual.
• I tried to use "missing_manual" tag consistently for my own documentation requests. Last year I shared a detailed enough review of the bugs with that tag that were still active.

As a yet another side note, I tried once to create a kind of community driven "missing manual" project, but failed. Writing manual from scratch is a (hard) full time job, while my full time job is providing support to users of MySQL, MariaDB and Percona software...

That said, I also wanted to join MySQL's documentation team in the past, but this was not possible at least because I am not a native speaker. If anything changed in this regard, I am still open to a job offer of this kind. My conditions for an acceptable offer from Oracle are known to all interested parties and they include (but are not limited to) at least 4 times the salary I had before I quit (it was almost 6 years ago) and working for Oracle's office in Paris (because in Oracle office you ere employed by formally matters a lot for your happiness and success as employee).

In case of no offer in upcoming week, I'll just continue to write my annoying but hopefully somewhat useful blog posts (until MySQL Reference Manual becomes true open source project ;)

On Oracle's QA for MySQL

In my recent blog posts I presented lists of bugs, fixed and not yet fixed, as usual. Working on these lists side tracked me from the main topic of this summer - problems in Oracle's way of handling MySQL. Time to get back on track!

Among things Oracle could do better for MySQL I mentioned QA:

"Oracle's internal QA efforts still seem to be somewhat limited.
We get regression bugs, ASAN failures, debug assertions, crashes, test failures etc in the official releases, and Oracle MySQL still relies a lot on QA by MySQL Community (while not highlighting this fact that much in public)."

I have to explain these in details, as it's common perception for years already that Oracle improved MySQL QA a lot and invests enormously in it, and famous MySQL experts were impressed even 5 years ago:

"Lets take a number we did get the QA team now has 400 person-years of experience on it. Lets say the QA team was 10 people before, and now it is tripled to 30 people. That means the average QA person has over 13 years experience in QA, which is about a year longer than my entire post-college IT career."

I was in the conference hall during that famous keynote, and QA related statements in it sounded mostly funny for me. Now, 5 years later, let me try to explain why just adding people and person-years of experience may not work that well. I'll try to present some examples and lists of bugs, as usual, to prove my points.

Emirates Air Line in London lets you see nice views of London, and it costed a lot, but hardly it's the most efficient public transport system between the North Greenwich Peninsula and Royal Victoria Dock one could imagine.

1. We still get all kinds of regression bugs reported by MySQL Community for every release, even MySQL 8.0.12. Here is the short list of random recent examples:
   
   • Bug #90209 - "Performance regression with > 15K tables in MySQL 8.0 (with general tablespaces)".
   • Bug #91878 - "Wrong results with optimizer_switch='derived_merge=OFF';".
   • Bug #91377 - "Can't Initialize MySQl if internal_tmp_disk_storage_engine is set to MYISAM".
   • Bug #90100 - "Year type column have index, query value more than 2156 , the result is wrong".
   • Bug #91927 - "8.0.12 no longer builds with Mac brew-installed ICU".
   • Bug #91975 - "InnoDB: Assertion failure: dict0dd.cc:1071:!fail".
   It means that Oracle's MySQL QA may NOT do enough/proper regression testing. We sometimes can not say this for sure, as Oracle hides some test cases. So, we, users of MySQL, just may not know what was the intention of some recent change (tests should show it even if the fine manual may not be clear enough - a topic for my next post).
   
2. We still get valid test failure bugs found by MySQL Community members. Some recent examples follows:
   
   • Bug #90633 - "innodb_fts.ngram_1 test fails (runs too long probably)".
   • Bug #90608 - "rpl_gtid.rpl_perfschema_applier_status_by_worker_gtid_skipped_transaction fails".
   • Bug #90631 - "perfschema.statement_digest_query_sample test fails sporadically".
   • Bug #89431 - "innodb_undo.truncate_recover MTR test failing with a server error log warning". It's fixed, but only in MySQL 8.0.13.
   • Bug #91175 - "rpl_semi_sync_group_commit_deadlock.test is not simulating flush error ".
   • Bug #91022 - "audit_null.audit_plugin_bugs test always failing".
   • Bug #86110 - "A number of MTR test cases fail when run on a server with no PERFSCHEMA".
   For me it means that Oracle's MySQL QA either do not care to run regression tests suite properly, in enough combination of platforms, options and build types, or they do not analyze the failures they get properly (and release when needed, not when all tests pass on all platforms). This is somewhat scary.
   
3. We still get crashing bugs in GA releases. It's hard to notice them as they are got hidden fast or as soon as they get public attention, but they do exist, and the last example, Bug #91928, is discussed here.
   
4. It seems some tools that helps to discover code problems may not be used properly/regularly in Oracle. I had a separate post "On Bugs Detected by ASan", where you can find some examples. Lucky we are that Percona engineers test ASan builds of MySQL 5.7 and 8.0 regularly, for years, and contribute back public bug reports.
   
5. Oracle's MySQL QA engineers do not write much about their work in public recently. I can find some posts here and there from 2013 and 2014, but very few in recent years. One may say that's because QA engineers are working hard and have no time for blogging (unlike lazy annoying individual like me), but that's not entirely true. There is at least one Oracle engineer who does a lot of QA and makes a lot of information about his work public - Shane Bester - who is brave enough and cares enough to report MySQL bugs in public. Ironically, I doubt he has any formal relation to any of QA teams in Oracle!
   
6. A lot of real MySQL QA is still done by MySQL Community, while these efforts are not that much acknowledged recently (you usually get your name mentioned in the official release notes if you submitted a patch, but the fact that you helped Oracle by finding a real bug their QA missed is NOT advertised any more since last Morgan's "Community Release Notes" published 2 years ago). Moreover, only MySQL Community tries to make QA job popular and educate users about proper tools and approaches (Percona and Roel Van de Paar personally are famous for this).

To summarize, for me it seems that real MySQL QA is largely still performed by MySQL Community and in public, while the impact of hidden and maybe huge Oracle's investments in QA is way less clear and visible. Oracle's MySQL QA investments look like those into the Emirates Air Line cable car in London to me - the result is nice to have, but it's the most expensive cable system ever built with a limited efficiency for community as a public transport.

Fun with Bugs #70 - On MySQL Bug Reports I am Subscribed to, Part VIII

More than 2 months passed since my previous review of active MySQL bug reports I am subscribed to, so it's time to describe what I was interested in this summer.

Let's start with few bug reports that really surprised me:

• Bug #91893 - "LOAD DATA INFILE throws error with NOT NULL column defined via SET". The bug was reported yesterday and seem to be about a regression in MySQL 8.0.12 vs older versions. At least I have no problem to use such a way to generate columns for LOAD DATA with MariaDB 10.3.7.
• Bug #91847 - "Assertion `thread_ids.empty()' failed.". As usual, Roel Van de Paar finds funny corner cases and assertion failures of all kinds. This time in MySQL 8.0.12.
• Bug #91822 - "incorrect datatype in RBR event when column is NULL and not explicit in query". Ernie Souhrada found out that the missing column is given the datatype of the column immediately preceding it, at least according to mysqlbinlog output.
• Bug #91803 - "mysqladmin shutdown does not wait for MySQL to shut down anymore". My life will never be the same as before after this. How can we trust anything when even shutdown command is no longer works as expected? I hope this bug is not confirmed after all, it's still "Open".
• Bug #91769 - "mysql_upgrade return code is '0' even after errors". Good luck to script writers! The bug is still "Open".
• Bug #91647 - "wrong result while alter an event". Events may just disappear when you alter them. Take care!
• Bug #91610 - "5.7 or later returns an error on strict mode when del/update with error func". Here Meiji Kimura noted a case when the behavior of strict sql_mode differs in MySQL 5.6 vs never versions.
• Bug #91585 - "“dead” code inside the stored proc or function can significantly slow it down". This was proved by Alexander Rubin from Percona.
• Bug #91577 - "INFORMATION_SCHEMA.INNODB_FOREIGN does not return a correct TYPE". This is a really weird bug in MySQL 8.
• Bug #91377 - "Can't Initialize MySQl if internal_tmp_disk_storage_engine is set to MYISAM". It seems Oracle tries really hard to get rid of MyISAM by all means in MySQL 8 :)
• Bug #91203 - "For partitions table, deal with NULL with is mismatch with reference guide". All version affected. maybe manual is wrong, but then we see weird results in information_schema as well. So, let's agree for now that it's a "Verified" bug in partitioning...

As usual, I am interested in InnoDB-related bugs:

• Bug #91861 - "The buf_LRU_free_page function may leak some memory in a particular scenario". This is a very interesting bug report about the memory leak that happens when tables are compressed. It shows how to use memory instrumentation in performance_schema to pinpoint the leak. This bug report is still "Open".
• Bug #91630 - "stack-use-after-scope in innobase_convert_identifier() detected by ASan". Yura Sorokin from Percona had not only reported this problem, but also contributed a patch.
• Bug #91120 - "MySQL8.0.11: ibdata1 file size can't be more than 4G". Why nobody tries to do anything about this "Verified" bug reported 2 months ago?
• Bug #91048 - "ut_basename_noext now dead code". This was reported by Laurynas Biveinis.

Replication problems are also important to know about:

• Bug #91744 - "START SLAVE UNTIL going further than it should." This scary bug in cyclic replication setup was reported by  Jean-François Gagné 2 weeks ago and is still "Open" at the moment.
• Bug #91633 - "Replication failure (errno 1399) on update in XA tx after deadlock". On top of all other problems with XA transactions we have in MySQL, it seems that replication may break upon executing a row update immediately after a forced transaction rollback due to a deadlock being detected while in an XA transaction.

Some optimizer bugs also caught my attention:

• Bug #91486 - "Wrong column type , view , binary". We have a "Verified" regression bug here without a "regression" tag or exact versions checked. Well done, Sinisa Milivojevic!
• Bug #91386 - "Index for group-by is not used with primary key for SELECT COUNT(DISTINCT a)". Yet another case where a well known bug reporter, Monty Solomon, had  to apply enormous efforts to get it "Verified" as a feature request.
• Bug #91139 - "use index dives less often". A "Verified" feature request from Mark Callaghan.

The last but not the least, documentation bugs. We have one today (assuming I do not care that much about group replication):

• Bug #91074 - "INSTANT add column undocumented". It was reported by my former colleague in Percona, Jaime Crespo. The bug is still "Verified" as of now, but since MySQL 8.0.12 release I think it's no longer valid. I see a lot of related details here, for example. But nobody cares to close this bug properly and provide the links to manual that were previously missing.

That's all for today, folks! Stay tuned.

Fun with Bugs #69 - On Some Public Bugs Fixed in MySQL 5.7.23

Several MySQL releases happened yesterday, but of them all I am mostly interested in MySQL 5.7.23, as MySQL 5.7 (either directly or indirectly, via forks and upstream fixes they merge) is probably the most widely used MySQL GA release at the moment.

In this post (in a typical manner for this "Fun with Bugs" series)  I'd like to describe several bugs reported by MySQL Community users and fixed in MySQL 5.7.23. As usual, I'll try to concentrate mostly on InnoDB, replication, partitioning and optimizer-related bugs (if any).

Checking MySQL release notes is like swimming with dolphins - it's pure fun

 
I'd like to start with InnoDB and partitioning-related fixes:

• The most important fix (that will make downgrades from 5.7.23 more problematic probably) is for Bug #86926 - "The field table_name (varchar(64)) from mysql.innodb_table_stats can overflow.", reported by Jean-François Gagné a year ago. The length of the table_name column in mysql.innodb_table_stats and mysql.innodb_index_stats tables has been increased from 64 to 199 characters (not 100% sure where this new value comes from), and one has to run mysql_upgrade while upgrading to 5.7.23.
• Bug #90296 - "Hard error should be report when fsync() return EIO". Now this recent request from Allen Lai is implemented.
• Bug #88739 - "Mysql scalability improvement patch: Optimized away excessive condition". The patch that removes unnecessary check for read-only transactions was suggested by Sandeep Sethia.
• Bug #86370 - "crash on startup, divide by zero, with inappropriate buffer pool configuration". This funny bug was reported by Shane Bester. I wonder how many more bombs of this kind (use of uint or ulint data types) still remain in the code...
• Bug #87253 - "innodb partition table has unexpected row lock". This bug was reported by  Yx Jiang and a patch was later suggested by Zhenghu Wen. If the comment at the end is correct, the fix is NOT included in MySQL 8.0.12.

Now let's check some replication bugs fixed:

• Bug #89272 - "Binlog and Engine become inconsistent when binlog cache file gets out of space". This bug was reported by Yoshinori Matsunobu. Good question in the last comment there, by Artem Danilov:
  

  "I wonder why does only ENOSPC end up with flush errors and clears the binary log cached. What about any other disk error? Can this fix be extended for all disk errors?"

• Bug #88891 - "Filtered replication leaves GTID holes with create database if not exists". This serious bug was reported by Simon Mudd. 
• Bug #89938 - "Rejoin old primary node may duplicate key when recovery", was reported by Zhenghu Wen, who cares about group replication a lot (unlike me, I have Galera for that).
• Yes another bug report from Jean-François Gagné, this time in group replication - Bug #89146 - "Binlog name and Pos are wrong in group_replication_applier channel's error msgs".
• Bug #81500 - "dead branch in search_key_in_table()". This bug was reported 2 years ago by Andrei Elkin, now my colleague (again) in MariaDB. Index is properly used on slave to find rows after this fix.

Surely in MySQL 5.7.23 there had to be some community bug fixed that is kept private. This time it's a bug in mysqldump:

"mysqldump exited abnormally for large --where option values. (Bug #26171967, Bug #86496, Bug #27510150)"

and this is a complete nonsense to me!

The last but not the least, we all should be thankful to Daniël van Eeden for these bug reports and patches contributed:

• Bug #89001 - "MySQL aborts without proper error message on startup if grant tables are corrupt".
• Bug #89578 - "Contribution: Use native host check from OpenSSL"

Had you noted any fixes to numerous public bug reports for InnoDB FULLTEXT indexes, "online" DDL or InnoDB compression, or optimizer maybe? I had not, and no wonder - the way these features are used by Community is obviously not something Oracle cares much about.

Anyway, I'd suggest to consider upgrade to MySQL 5.7.23 to anyone who cares about security and uses OpenSSL, replication of any kind or InnoDB partitioned tables in production.

On Some Problematic Oracle MySQL Server Features

In one of my previous posts I stated that in Oracle's MySQL server some old enough features remain half-backed, not well tested, not properly integrated with each other, and not documented properly. It's time to prove this statement.

I should highlight from the very beginning that most of the features I am going to list are not that much improved by other vendors. But they at least have an option of providing other, fully supported storage engines that may overcome the problems in these features, while Oracle's trend to get rid of most engines but InnoDB makes MySQL users more seriously affected by any problems related to InnoDB.

The Royal Pavilion in Brighton looks nice from the outside and is based on some great engineering decisions, but the decorations had never been completed, some interiors were ruined and never restored, and the building was used for too many different purposes over years.

The list of problematic MySQL server features includes (but is not limited to) the following:

• InnoDB's data compression
  
  Classical InnoDB compression (row_format=compressed) has limited efficiency and does not get any attention from developers recently. Transparent page compression for InnoDB seems to be originally more like a proof of concept in MySQL that may not work well in production on commodity hardware and filesystems, and was not integrated with backup tools.
• Partitioning
  
  Bugs reported for this feature by MySQL Community do not get proper attention. DDL against partitioned tables and partition pruning do not work the way DBAs may expect. We still miss parallel processing for partitioned tables (even though proof of concept for parallel DDL and some kinds of SELECTs was ready and working 10 years ago). Lack of careful testing of partitioning integration with other features is also visible.
• InnoDB's FULLTEXT indexes
  This feature appeared in MySQL 5.6, but 5 years later there are still all kinds of serious bugs in it, from wrong results to hangs, debug assertions and crashes. There are performance regressions and missing features comparing to MyISAM FULLTEXT indexes, and this makes the idea to use InnoDB for everything even more problematic. Current implementation is not designed to work with really large tables and result sets. DBAs should expect problems during routine maintenance activities, like ALTERing tables or dumps and restores when any table with InnoDB FULLTEXT index is involved.
  
  
• InnoDB's "online" DDL implementation
  It is not really "online" in too many important practical cases and senses. Replication ignores LOCK=NONE and slave starts to apply "concurrent" DML only after commit, and this may lead to a huge replication lag. The entire table is often rebuilt (data are (re-)written) to often, in place or by creating a copy. One recent improvement in MySQL 8, "instant ADD COLUMN", was actually contributed by Community. The size of the "online log" (that is kept in memory and in temporary file) created per table altered or index created, depends on concurrent DML workload and is hard to predict. For most practical purposes good old pt-online-schema-change or gh-ost tool work better.
  
  
• InnoDB's persistent optimizer statistics
  
  Automatic statistics recalculation does not work as expected, and to get proper statistics explicit ANALYZE TABLE calls are still needed. The implementation is complicated and introduced separate implicit transactions (in dirty reads mode) against statistics tables. Bugs in the implementation do not seem to get proper priority and are not fixed.

I listed only those features I recently studied in some details in my previous blog posts. I've included main problems with each feature according to my older posts. Click on the links in the list above to find the details.

The Royal Pavilion of InnoDB in MySQL is beautiful from the outside (and somewhere inside), but is far from being completed, and some historical design decisions do not seem to be improved over years. We are lucky that it is still used and works nice for many current purposes, but there are too many dark corners and background threads there where even Oracle engineers rarely look and even less are improving them...

Problems with Oracle's Way of MySQL Bugs Database Maintenance

In one of my previous posts I stated that Oracle does not care enough to maintain public MySQL bugs database properly. I think it's time to explain this statement in details.

The fact that https://bugs.mysql.com/ still exists and community bug reports there are still processed on a regular basis by my former colleagues, Miguel Solorzano, Sinisa Milivojevic, Umesh Shastry, Bogdan Kecman and others, is awesome. Some probably had not expected this to still be the case for 8+ years since Oracle took over the software and procedures around it. My former bugs verification team still seems to exist and even get some new members. Moreover, today we have less "Open" bugs than 6 years ago, when I was preparing to leave Oracle to join (and build) the best MySQL support team in the industry elsewhere...

That's all good and beyond the best expectation of many. Now, what's wrong then with the way Oracle engineers process community bug reports these days?

On the photo above that I made last autumn we see the West Pier in Brighton, England. It keeps collapsing after major damages it got in 2002 and 2003 from storms and fires, and this spring I've seen even more ruins happened. Same happens to MySQL public bugs database - we see it is used less than before, and severe damage to the Community is made by some usual and even relatively simple actions and practices. Let me summarize them.

1. "Security" bugs are handled in such a way that they never becomes public back, even after the problem is fixed in all affected versions and this fix is documented.
   
   Moreover, often it takes bug reporter to check "security" flag by mistake or for whatever reason for nobody else ever to be able to find out what exactly the bug was about. This is done even in cases when all other vendors keep the information public or open it after the fix is published. Even worse, sometimes when somebody "escalates" some public bug forgotten for a long time (or wrongly handled) to Oracle engineers, in public, the bug immediately becomes private, even though nobody cared about it for months before. I have complained about this many times everywhere.
   I would so much like to publish all the details of mishandling of Bug #91118, for example, but the bug was made private, so you'd have to just trust my words and quotes, while I prefer to provide arguments everyone can verify... Remember the bug number though and this (top) part of the stack trace:
   

   ...
   /home/openxs/dbs/8.0/bin/

   mysqld(row_search_mvcc(unsigned char*,
   page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long)+0x2b76)
   [0x1dc78a6]
   /home/openxs/dbs/8.0/bin/mysqld(ha_innobase::general_fetch(unsigned
   char*, unsigned int, unsigned int)+0x15f) [0x1c98c1f]
   /home/openxs/dbs/8.0/bin/mysqld(handler::ha_index_next_same(unsigned
   char*, unsigned char const*, unsigned int)+0x1e4) [0xe909a4]
   /home/openxs/dbs/8.0/bin/mysqld() [0xc5636a]
   ...

   The practice of hiding bugs once and forever was advocated by some MySQL engineers well before Oracle's acquisition, but in Oracle it became a rule that only bravest engineers sometimes can afford NOT to follow.
   
   
2. Some older bug reports are never processed or never revisited back by Oracle engineers.
   
   Consider this simple example, Bug #70237 - "the mysqladmin shutdown hangs". It was reported almost 5 years ago by a famous Community member, Zhai Weixiang, who even suggested a patch. This bug had not got a single public comment from anyone in Oracle.
   The oldest open server bug today is Bug #44411  - "some Unicode text garbled in LOAD DATA INFILE with user variables". It was reported more than 9 years ago. Moreover, it was once "Verified", but then silently became "Not a bug" and then was reopened by the bug reporter. Nobody cares, even though it's clear that many Oracle engineers should get notification emails whenever any change to public bug report happens. This is also fundamentally wrong, no matter what happened to assignee or engineer who worked on the bug in the past.
   
   This specific problem with bugs handling is not new, we always had a backlog of bugs to verify and some bugs were re-checked maybe once in many years, but Oracle now has all kinds of resources to fix this problem, and not just reduce the number of open reports by closing them by all fair and not that fair means... See the next item also.
   
   
3. Recently some bug reports are handled wrongly, with a trend of wasting bug reporter time on irrelevant clarifications or closing the bug too early when there is a problem to reproduce it.
   
   If you report some MySQL problem to Oracle, be ready to see your report closed soon with a suggestion to contact Support. Check this recent example, Bug #90375 - "Significantly improve performance of simple functions". OK, this is a known limitation, but user suggested several workarounds that are valid feature requests for optimizer, that can be smart enough to inline the stored function if it just returns some simple expression. Why not to verify this as a valid feature request?
   Another great example is Bug #80919 - "MySQL Crashes when Droping Indexes - Long semaphore wait". It was closed (after wasting more than a year on waiting) as a "Duplicate" with such a nice comment:

   "[16 Jan 15:53] Sinisa Milivojevic

   Hi!
   
   This is a duplicate bug, because it is very similar to an internal-only bug, that is not present in the public bugs database.
   
   I will provide the internal bug number in the hidden comment, but will update this page with public comment, once when that bug is fixed."

   No reference to the internal bug number, just nothing. If you are wondering what is the real problem, check MDEV-14637.
   Yet another case of "Not a bug", where the decision is questionable and further statements from the bug reporter are ignored is Bug #89065 - "sync_binlog=1 on a busy server and slow binary log filesystem stalls slaves".
   The bug may be "Verified" finally, but after some time wasted on discussions and clarifications when the problem is obvious and bug report contains everything needed to understand this. Nice example is Bug #91386 - "Index for group-by is not used with primary key for SELECT COUNT(DISTINCT a)". Yet another example is Bug #91010 - "WolfSSL build broken due to cmake typo". Knowing parties involved in that discussions in person, I wish they spent their time on something more useful than arguing on the obvious problems.
   
   This practice discourage users from reporting bugs to Oracle. Not that bug handling mistakes never happened before Oracle (I did many myself), but recently I see more and more wrongly handled bugs. This trend is scary!
   
   
4. Oracle mostly fixes bugs reported internally.
   
   Just check any recent Release Notes and make your own conclusion. One may say it means that internal QA and developers find bugs even before Community notices them, but the presence of all kinds of test failures, regression bugs etc in the same recent versions still tells me that Community QA is essential for MySQL quality. But it does not set the agenda for the bug fixing process, for many years.
   One may also say that bug fixing agenda is defined by Oracle customers mostly. I let Oracle customers to comment here if they are happy with what they get. Some of the were not so happy with the speed of resolution even for their real security related bugs.

I can continue with more items, but let's stop for now.

If we want MySQL public bugs database to never reach the state of the West Pier (declared to be beyond repair in 2004), we should force Oracle to do something to fix the problems above. Otherwise we'll see further decline of bugs database and Community activity switched elsewhere (to Percona's and MariaDB's public bugs databases, or somewhere else). It would be sad to not have any central public location for all problem reports about core MySQL server functionality...