Saturday, March 24, 2018

Fun with Bugs #63 - On Bugs Detected by ASan

Among other things Geir Hoydalsvik stated in his nice post yesterday:
 "We’ve fixed a number of bugs detected by UBsan and Asan."
This is indeed true, I already noted many related bugs fixed in recent MySQL 8.0.4. But I think that a couple of details are missing in the blog post. First of all, there still a notable number of bugs detected by ASan or noted in builds with ASan that remain "Verified". Second, who actually found and reported these bugs?

I decided to do a quick search and present my summary to clarify these details. Let me start with the list of "Verified" or "Open" bugs in public MySQL bugs database, starting from the oldest one:
  • Bug #69715 - "UBSAN: Item_func_mul::int_op() mishandles 9223372036854775809*-1". The oldest related "Verified" bug I found was reported back in 2013 by Arthur O'Dwyer. Shane Bester from Oracle kindly keeps checking it with recent and upcoming releases, so we know that even '9.0.0-dmr-ubsan' (built on 20 October 2017) was still affected.
  • Bug #80309 - "some innodb tests fail with address sanitizer (WITH_ASAN)". It was reported by Richard Prohaska and remains "Verified" for more than two years already.
  • Bug #80581 - "rpl_semi_sync_[non_]group_commit_deadlock crash on ASan, debug". This bug reported by Laurynas Biveinis from Percona two years ago is still "Verified".
  • Bug #81674 - "LeakSanitizer-enabled build fails to bootstrap server for MTR". This bug reported by  Laurynas Biveinis affects only MySQL 5.6, but still, why not to backport the fix from 5.7?
  • Bug #82026 - "Stack buffer overflow with --ssl-cipher=<more than 4K characters>". Bug detected by ASan was noted by Yura Sorokin from Percona and reported by Laurynas Biveinis.
  • Bug #82915 - "SIGKILL myself when using innodb_limit_optimistic_insert_debug=2 and drop table". ASan debug builds are affected. This bug was reported by Roel Van de Paar from Percona.
  • Bug #85995 - "Server error exit due to empty datadir causes LeakSanitizer errors". This bug in MySQL 8.0.1 (that had to affect anyone who runs tests on ASan debug builds on a regular basis) was reported by Laurynas Biveinis and stay "Verified" for almost a year.
  • Bug #87129 - "Unstable test main.basedir". This test problem reported by Laurynas Biveinis affects ASan builds, among others. See also his Bug #87190 - "Test main.group_by is unstable".
  • Bug #87201 - "XCode 8.3.3+ -DWITH_UBSAN=ON bundled protobuf build error". Yet another (this time macOS-specific) bug found by Laurynas Biveinis.
  • Bug #87295 - "Test group_replication.gr_single_primary_majority_loss_1 produces warnings". Potential bug in group replication noted by Laurynas Biveinis in ASan builds.
  • Bug #87923 - "ASan reporting a memory leak on merge_large_tests-t". This bug by Laurynas Biveinis is still "Verified", while Tor Didriksen's comment states that it it resolved with the fix for Bug #87922 (that is closed as fixed in MySQL 8.0.4). Why not to close this one also?
  • Bug #89438 - "LeakSanitizer errors on xplugin unit tests". As Laurynas Biveinis found, X Plugin unit tests report errors with LeakSanitizer.
  • Bug #89439 - "LeakSanitizer errors on GCS unit tests". yet another bug report for MySQL 8.0.4 by Laurynas Biveinis.
  • Bug #89961 - "add support for clang ubsan". This request was made by Tor Didriksen from Oracle. It is marked as "fixed in 8.0.12". It means we may get MySQL 8.0.11 released soon. That's why I decided to mention the bug here.
There were also few other test failures noted on ASan debug builds. I skipped them to make this post shorter.

Personally I do not run builds or tests with ASan on a regular basis. I appreciate Oracle's efforts to make code warning-free, UBSan- and ASan-clean, and fix bugs found with ASan. But I'd also want them to process all/most of related bugs in public database properly before making announcements of new related achievement, and clearly admit and appreciate a lot of help and contribution from specific community members (mostly Laurynas Biveinis in this case).

Percona engineers seem to test ASan builds of MySQL 5.7 and 8.0 (or Percona's closely related versions) regularly, for years, and contribute back public bug reports. I suspect they found way more related bugs than internal Oracle's QA. I think we should explicitly thank them for this contribution that made MySQL better!

Tuesday, March 20, 2018

Fun With Bugs #62 - On Bugs Related to JSON Support in MySQL

Comparing to "online ALTER" or FULLTEXT indexes in InnoDB, I am not that much exposed to using JSON in MySQL. OK, there is EXPLAIN ... FORMAT=JSON statement that is quite useful, optimizer trace in MySQL is also in JSON format, but otherwise I rarely have to even read this kind of data since my lame attempt to pass one introductory MongoDB training couse back in 2015 (when I mostly enjoyed mongo shell). Even less I care about storing JSON in MySQL.

But it seems community users do use it, and there is a book for them... So, out of pure curiosity last week I decided to check known bugs reported by MySQL Community users that are related to this feature (it has a separate category "MySQL Server: JSON" in public bugs database. This is a (short enough) list of my findings:
  • Bug #89543 - "stack-overflow on gis.geojson_functions / json.json_functions_innodb". This is a recent bug report from Laurynas Biveinis. Based on last comment, it may have something to do with specific GCC compiler version and exact build option (WITH_UBSAN=ON) used. But for me it's yet another indication that community (Percona's in this case) extra QA still matters for MySQL quality. 
  • Bug #88033 - "Generated INT column with value from JSON document". As Geert Vanderkelen says: "would be great if JSON null is NULL".
  • Bug #87440 - "use union index,the value of key_len different with used_key_parts’ value." This is actually a bug in optimizer (or EXPLAIN ... FORMAT=JSON), but as it is still "Open" and nobody cared to check it, it still stays in the wrong category.
  • Bug #85755 - "JSON containing null value is extracted as a string "null"". Similar to Geert's case above, Dave Pullin seems to need an easy way to get real NULL in SQL when the value is null in JSON. It is stated in comments that now everything works as designed, and it works exactly the same in MariaDB 10.2.13. Some users are not happy though and the bug remains still "Verified".
  • Bug #84082 - "JSON_OBJECT("k", dateTime) adds milliseconds to time". In MariaDB 10.2.13 the result is different:
    MariaDB [(none)]> SELECT version(), JSON_OBJECT("k", cast("2016-11-19 17:46:31" as datetime(0)));
    | version()       | JSON_OBJECT("k", cast("2016-11-19 17:46:31" as datetime(0))) |
    | 10.2.13-MariaDB | {"k": "2016-11-19 17:46:31"}                                 |
    1 row in set (0.00 sec)
  • Bug #83954 - "JSON handeling of DECIMAL values, JSON from JSON string". Take care when you cast values to JSON type.
  • Bug #81677 - "Allows to force JSON columns encoding into pure UTF-8". This report is still "Open" and probably everything works as designed.
  • Bug #79813 - "Boolean values are returned inconsistently with JSON_OBJECT". Take care while working with boolean properties.
  • Bug #79053 - "Second argument "one" to JSON_CONTAINS_PATH is a misnomer". Nice request from Roland Bouman to use "some" instead of "one" in this case. Probably too late to implement it...
There also several related feature requests, of them the following are still "Open":
  • Bug #84167 - "Managing deeply nested JSON documents with JSON_SET, JSON_MERGE". As a comment says, "It would by nice if MySQL came up with a solution for this in a more constructive way".
  • Bug #82311 - "Retrieve the last value of an array (JSON_POP)". I am not 100% sure what the user wants here, but even I can get last array element in MongoDB using $slice, for example:
    > db.text.find();
    { "_id" : ObjectId("5aafe41c6bd09c09625aa3d1"), "item" : "test" }
    { "_id" : ObjectId("5ab0e8c208d6fe081ebb4f40"), "item" : null }
    { "_id" : ObjectId("5ab0f0f6cc695c62b4b0a301"), "item" : [ { "val" : 10 }, { "val" : 11 } ] }
    > db.text.find({}, { item: { $slice: -1 }, _id: 0});
    { "item" : "test" }
    { "item" : null }
    { "item" : [ { "val" : 11 } ] }
  • Bug #80545 - "JSON field should be allow INDEX by specifying table_flag". It seems some 3rd party storage engine developers want to have a way to crate and use some nice indexes of their engines to get efficient index-based search in JSON documents.
  • Bug #80349 - "MySQL 5.7 JSON: improve documentation and possible improvements". Simon Mudd asked for more documentation on the way feature is designed (for those interested in the details of JSON data type implementation in modern MySQL versions there is a detailed enough public worklog, WL#8132) and asked some more questions. Morgan Tocker answered at least some of them, but this report still stays "Open" since that times...
It would be great for remaining few "Open" reports from community users to be properly processed, and a couple of real bugs identified fixed. There are also features to work on, and in the meantime workarounds and best practices should be shared. Maybe there are enough of them for the entire book.

That's almost all I have to say about JSON in MySQL at the moment.

Sunday, March 11, 2018

Checking User Threads With gdb in MySQL 5.7+

In one of my gdb-related posts last year I noted that there is no more simple global list of user threads in MySQL 5.7+:
"I had highlighted Global_THD_manager singleton also as during my next gdb sessions I had found out that simple global list of threads is also gone and in 5.7 everything is done via that Global_THD_manager. This is a topic for some other post, though."
In that post and many times later when I had to deal with MySQL 5.7+ I just checked OS threads one by one in gdb using thread  1 ... thread N commands. This is not efficient at best, as I also hit numerous background threads that I often do not care about. So, a couple of weeks ago I finally decided to get back to this topic and find out how to check just user threads one by one in recent MySQL versions. I had a nice hint by Shane Bester on how to get information about $i-th thread (that he shared in one of his comments to my Facebook post):
set $value = (THD**)(Global_THD_manager::thd_manager-> + (sizeof(THD**) * $i))
I've attached gdb to an instance of Percona Server 5.7.x that I had running in my CentOS 6.9 VM and tried few commands to check types and content of the Global_THD_manager elements:
(gdb) p Global_THD_manager::thd_manager
$1 = (Global_THD_manager *) 0x7fab087fd000
(gdb) p Global_THD_manager::thd_manager->thd_list
$2 = {m_size = 2, m_capacity = 500, m_buff = {{
      data = "\000\060b\344\252\177\000\000\000\220i\344\252\177\000\000\000\200x\344\252\177", '\000' <repeats 3977 times>, align = {<No data fields>}}},
  m_array_ptr = 0x7fab087fd010, m_psi_key = 0}

So, we see that internally there is some array of elements thd_list with m_size items (2 in my case) probably stored in some pre-allocated buffer of m_capacity (500) elements, stored in The type of elements is not clear, but we can try Shane's hint and assume that they are of type THD**. Let's try to check what we see there after type castings:
(gdb) p (THD**)(Global_THD_manager::thd_manager->
$4 = (THD **) 0x7fab087fd010
(gdb) p  *(THD**)(Global_THD_manager::thd_manager->
$5 = (THD *) 0x7faae4623000
(gdb) p  **(THD**)(Global_THD_manager::thd_manager->
$6 = {<MDL_context_owner> = {
    _vptr.MDL_context_owner = 0x1c51f50}, <Query_arena> = {
So, we get reasonable addresses and when we dereference the resulting THD** pointer twice we indeed get a structure that looks like THD of MySQL 5.7+ (it's very different, say, in MariaDB 10.1.x), with reasonable content (that is huge and skipped above).

I've tried to get processlist id of thread based on findings of that post using intermediate gdb variables:

(gdb) set $ppthd = (THD**)(Global_THD_manager::thd_manager->
(gdb) p *($ppthd)
$7 = (THD *) 0x7faae4623000

(gdb) set $pthd = *($ppthd)
(gdb) p $pthd->m_thread_id
$10 = 5
and then directly, using offsets and checking for security contexts of threads:
(gdb) p  (**(THD**)(Global_THD_manager::thd_manager->
$14 = {m_ptr = 0x7faae463b060 "myuser", m_length = 6, m_charset = 0x1d21760,
  m_alloced_length = 8, m_is_alloced = true}
(gdb) p  (**(THD**)(Global_THD_manager::thd_manager-> + (sizeof(THD**)))).m_main_security_ctx.m_user
$15 = {m_ptr = 0x7faae46b1090 "root", m_length = 4, m_charset = 0x1d21760,
  m_alloced_length = 8, m_is_alloced = true}
(gdb) p  (**(THD**)(Global_THD_manager::thd_manager-> + (sizeof(THD**)))).m_thread_id
$16 = 9
to confirm that I correctly get user names and thread ids for both 2 user threads I had in that "list". As usual Shane Bester was right!

Now, if you want to get more details about Global_THD_manager, you can just check the sql/mysqld_thd_manager.h file. I was interested mostly in the following:
  int get_num_thread_running() const { return num_thread_running; }
  uint get_thd_count() const { return global_thd_count; }

  static Global_THD_manager *thd_manager;

  // Array of current THDs. Protected by LOCK_thd_list.
  typedef Prealloced_array<THD*, 500, true> THD_array;
  THD_array thd_list;

  // Array of thread ID in current use. Protected by LOCK_thread_ids.
  typedef Prealloced_array<my_thread_id, 1000, true> Thread_id_array;
  Thread_id_array thread_ids;
First of all, how consistent it is to use both int and uint data types for values that are always >=0... The fact that our thd_list elements is actually some template-based container, Prealloced_array, it also interesting, as it would be useful to find out how it is implemented. We can find all relevant details in the include/prealloced_array.h file. I'd like to highlight the following here:
"The interface is chosen to be similar to std::vector."

  size_t         m_size;
  size_t         m_capacity;
  // This buffer must be properly aligned.
  my_aligned_storage<Prealloc * sizeof(Element_type), MY_ALIGNOF(double)>m_buff;
Element_type *m_array_ptr;
To summarize, MySQL 5.7+ uses more C++ now, with templates, singletons, iterators and more, but still Oracle prefers to implement their own container types instead of using some standard ones. One of these generic types, Prealloced_array, is widely used and is easy to deal with in gdb, as long as you know the element type.

Sunday, March 4, 2018

On InnoDB's FULLTEXT Indexes

I had recently written about InnoDB features that I try to avoid by all means if not hate: "online" DDL and persistent optimizer statistics. Time to add one more to the list - FULLTEXT indexes.

This feature had a lot of problems when initially introduced in MySQL 5.6. There was a nice series of blog posts about the initial experience with it by my colleague from Percona (at that times) : part I, part II, and part III. Many of the problems mentioned there were resolved or properly documented since that times, but even more were discovered. So, InnoDB FULLTEXT indexes may be used, with care, when MyISAM or other engines/means to add fulltext search is not an option. The list of bugs that are still important and must be taken into account is presented below.

What forced me to get back to this feature recently and hate it sincerely is one customer issue that led to this bug report: MDEV-14773  - "ALTER TABLE ... MODIFY COLUMN ... hangs for InnoDB table with FULLTEXT index". Note that I have to refer to MariaDB bug report here, as related upstream Bug #88844 is hidden from community (probably considered a shame, if not a security problem)! The bug is simple: if one applies any ALTER to the InnoDB table with FULLTEXT index, even not related that index and columns in in any way, chances are high that this ALTER may cause a kind of hang/infinite loop/conflict of the thread that tries to drop temporary table used by ALTER, as one of last steps, and FTS background optimize thread. Similar to other two problematic features, new background threads were introduced and their cooperation with other threads in InnoDB seems to be not that well designed/implemented.

There are many other bugs to take into account if you ever plan to add any single FULLTEXT index to your InnoDB table. Here is the list of the most important ones, mostly still "Verified" or open and ignored, that I collected during one of calm night shifts this week:
  • Bug #78048 - "INNODB Full text Case sensitive not working". This bug was fixed only recently, in MySQL 5.6.39, 5.7.21, and 8.0.4.
  • Bug #83776 - "InnoDB FULLTEXT search returns incorrect result for operators on ignored words". Still "Verified" on all GA versions and 8.0.x.
  • Bug #76210 - "InnoDB FULLTEXT index returns wrong results for key/value pair documents". This bug was reported by Justin Swanhart 3 years ago, quickly verified and then seems to be ignored.
  • Bug #86036 - "InnoDB FULLTEXT index has too strict innodb_ft_result_cache_limit max limit". I reported this bug 10 months ago, and it was immediately "Verified". It seems FULLTEXT indexes are hardly useful in general for large InnoDB tables because of this limitation.
  • Bug #78977 - "Enable InnoDB fulltext index to use generated FTS_DOC_ID column". This is a feature request (still "Open") to get rid of this well known limitation/specific column.
  • Bug #86460 - "Deleted DOCID are not maintained during OPTIMIZE of InnoDB FULLTEXT tables". If you want to get rid of deleted DOC_IDs in the INNODB_FT_DELETED, better just run ALTER TABLE ... ENGINE=InnoDB.
  • Bug #75763 - "InnoDB FULLTEXT index reduces insert performance by up to 6x on JSON docs". yet another verified bug report by Justin Swanhart.
  • Bug #69762 - "InnoDB fulltext match against in boolean mode misses results on join". Let me quote last comment there:
    "Since innodb doesn't support fulltext search on columns without fulltext index, and it is very complicated to support search on columns in multiple fulltext indexes in optimizer, it won't be fixed.

    We admit it's a point innodb fulltext is not compatible with myisam."
  • Bug #85880 - "Fulltext query is too slow when each ngram token match a lot of documents". This bug is still "Open".
  • Bug #78485 - "Fulltext search with char * produces a syntax error with InnoDB". Yet another verified regression comparing to MyISAM FULLTEXT indexes. Nobody cares for 2.5 years.
  • Bug #80432 - "No results in fulltext search for top level domain in domain part of email ". It ended up as "Won't fix", but at least a workaround was provided by Oracle developer.
  • Bug #81819 - "ALTER TABLE...LOCK=NONE is not allowed when FULLTEXT INDEX exists". Online ALTER just does not work for tables with FULLTEXT indexes. This is a serious limitation.
  • Bug #72132 - "Auxiliary tables for InnoDB FTS indexes are always created in shared tablespace". This my bug report was fixed in .5.6.20+ and 5.7.5+, but the fact that this regression was not noted for a long time internally says a lot about the way the feature was developed and maintained.
  • Bug #83560  - "InnoDB FTS - output from mysqldump extremely slow and blocks unrelated inserts". I have yet to check the metadata locks set when the table with FULLTEXT index is used in various SQL statements, but from this "Verified" report it is clear that just lading a dump of a table with FULLTEXT indexes may work too slow for any large table.
  • Bug #71551 - "ft_boolean_syntax has no impact on InnoDB FTS". yet another inconsistency with MyISAM FULLTEXT indexes that was reported 4 years ago and "Verified", but still ignored after that.
  • Bug #83741 - "InnoDB: Failing assertion: lock->magic_n == 22643". Surely, debug assertions can be ignored, but in most cases they are in the code for a good reason. This failure was reported by Roel Van de Paar from Percona.
  • Bug #83397 - "INSERT INTO ... SELECT FROM ... fails if source has > 65535 rows on FTS". This "Verified" bug alone, reported by Daniël van Eeden, makes InnoDB FULLTEXT indexes hardly usable in production for large tables.
  • Bug #80296 - "FTS query exceeds result cache limit". The bug is "Closed" silently (by the bug reporter maybe, Monty Solomon?), but users report that recent enough versions like 5.6.35 and 5.7.17 are still affected. See also Bug #82971 (no fix for MySQL 5.6.x for sure).
  • Bug #85876 - "Fulltext search can not find word which contains "," or ".".  Still "Verified" for 1 months.
  • Bug #68987 - "MySQL crash with InnoDB assertion failure in file". Crash was reported in MySQL 5.6.10, not repeatable. Then (different?) assertion failure was reported in debug builds only in MySQL 5.6.21+, and verified. Not sure what's going on with this bug report...
  • Bug #83398 - "Slow and unexpected explain output on FTS". The fact that EXPLAIN may be slow when the table with FULLTEXT index is involved is now documented, so this report by Daniël van Eeden is closed.
  • Bug #81930 - "incorrect result with InnoDB FTS and subquery". This bug report about wrong results by Sergei Golubchik from MariaDB was immediately "Verified", but ignored since that time.
  • Bug #80347 - "mysqldump backup restore fails due to invalid FTS_DOC_ID (Error 182 and 1030)". There is a workaround based on mydumper/myloader at least...
To summarize, InnoDB FULLTEXT indexes is one of the most problematic InnoDB features for any production use because:
  • There are all kinds of serious bugs, from wrong results to hangs, debug assertions and crashes, that do not seem to get any internal priority and stay "Verified" for years.
  • There are performance regressions and missing features comparing to MyISAM FULLTEXT indexes, so migration may cause problems.
  • InnoDB FULLTEXT indexes are not designed to work with really large tables/result sets.
  • You should expect problems during routine DBA activities, like ALTERing tables or dumps and restores when any table with InnoDB FULLTEXT index is involved. 
If you still plan/have to use it, please, make sure to use the latest MySQL version, check the list above carefully and test/check the results of fulltext searches and routine DBA operations like altering the table. You may get a lot of surprises. Consider alternatives like Sphinx seriously.

Sunday, February 11, 2018

Fun with Bugs #61 - On MySQL Bug Reports I am Subscribed to, Part III

Since my previous post on this topic I've subscribed to 19 more MySQL bugs, so it's time for yet another review of these reports. I am trying to pick up important, funny or hard to process reports every day, and here is the list of the most interesting ones starting from the latest:
  • Bug #89607 - "MySQL crash in debug, PFS thread not handling singals." We have a patch contributed by Robert Golebiowski.
  • Bug #89583 - "no rpm build instructions from source git tree". As Simon Mudd put it:
    "... The sources are supposed to be in the git tree so just tell me how I can use that to produce the files needed to make the rpm SOURCES.

    Without that information only Oracle can build src rpms and no-one else can repeat the process they use and any changes required by people building rpms can't be done in a separate branch of a cloned copy of That would be better as from there I can much more easily provide PRs which should improve the MySQL sources."
    I hope one day Oracle will start to share all the code for open source MySQL, properly, including all test cases and detailed, repeatable build instructions. Having proper, up to date and working build instructions that let one end up with binaries built the same way as vendor builds them is not an easy task in general, and KB articles like this is just a step towards the goal...
  • Bug #89559 - "P_S recording wrong digest/digest_text for select statements using views". Everybody knows how much I like Performance Schema, but I like to notice bugs in it even more This is a funny bug that is still "Open" (even though it was noticed by my dear friend Sinisa Milivojevic already) for some reason.
  • Bug #89534 - "Crash during innodb recovery when working with encryption". Yet another bug report (this time "Verified", in 5.7.21) from Robert Golebiowski. Purge thread running during startup may cause problems.
  • Bug #89519 - "Documentation for SSL/TLS and replication is incomplete for 8.0". In this report  Daniël van Eeden noted that --ssl-verify-server-cert option is not documented.
  • Bug #89444 - "8.0.4rc --initialize-insecure is prohibitively slow". I've noted the same immediately while working on tests for my planned presentation at FOSDEM, but I do not care much about MySQL 8. Fortunately,  Roel Van de Paar cares, so we have a "Verified" bug report about this new behavior.
  • Bug #89441 - "Foreign keys constraints ignored after RENAME TABLE". I am sure we'll see many regressions related to the new data dictionary in MySQL 8. This bug report by Carlos Salguero highlights one of them.
  • Bug #89430 - "Release notes are missing important CVE fixes". Oracle and transparency in anything related to security issues seems to be historically incompatible things. Still, Roel Van de Paar has a hope they may improve their release notes... In the meantime check this great document with all the relevant details.
  • Bug #89375 - "Parallel replication always fails with specific workload from sysbench". It's always great to see public bug report from Oracle employee. Thank you, Frederic Descamps, for sharing your findings with community!
  • Bug #89372 - "Using --no-dd-upgrade seems to have no effect". This bug report by Geert Vanderkelen is still "Open" for some reason.
  • Bug #89367 - "Storing result in a variable(UDV) causes query on a view to use derived tables". Nice optimizer bug reported by Jaime Sicam.
  • Bug #89331 - "Changing lock/release order in group commit causes a deadlock". I think there are at most 10 people in the world who understands all the details of this bug report by Aliaksei Sandryhaila (and I am not one of them). The goal they are trying to achieve is interesting:
    "We are implementing START TRANSACTION WITH CONSTISTENT INNODB SNAPSHOT functionality in 8.0, and want to change it to first taking the next stage's lock, then releasing the previous stage's lock. The implementation of this feature for 5.6 is here: ..."
    I see active discussion with Oracle developer in that report, so I am sure they'll figure out how to proceed.
  • Bug #89272 - "Binlog and Engine become inconsistent when binlog cache file gets out of space". Great finding by Yoshinori Matsunobu and Jeff Jiang. If you ever had "Errcode: 28 - No space left on device" on master while doing some large transactions, make sure to check if slave is in sync. It may be NOT.
  • Bug #89267 - "Unable to access 8.0.4 server after starting on top of 5.7.20 database". I am sure we'll get a lot more bug reports when users actively start to upgrade. For now check comments for possible workaround.
  • Bug #89247 - "Deadlock with MTS when slave_preserve_commit_order = ON." There are many problems with parallel replication/multi-threaded slaves, and Jean-François Gagné probably knows more about them than anyone else. Check also his another bug report about this feature, Bug #89141 - "Error in Group Replication caused by bad Write Set tracking."
  • Bug #89126 - "create table panic on innobase_parse_hint_from_comment". Nice bug report and patch from Yan Huang.
  • Bug #89101 - "MySQL server hang when gtid_mode=on and innodb_thread_concurrency>0". This is a second reason for me to think twice from now on before suggesting to limit innodb_thread_concurrency ever again. Great bug report by Seunguck Lee and MTR test case (and argumentation) by Przemyslaw Malkowski! This is a masterpiece of bug reporting art, Sinisa had to give up...
  • Bug #89098 - "Adding an auto_increment column to existing table creates gaps". Really weird behavior noticed by Riccardo Pizzi. I hope it is truly repeatable literally (but I had not checked myself).
That's all new bug reports in my list of subscriptions. Next time I hope to review few older ones that were not presented in this series yet.

Sunday, January 28, 2018

On InnoDB's Online DDL

I am completing my preparations for the upcoming FOSDEM talk, and one of last things I wanted to do in frames of them is a quick review of known bugs and problems in current (as in MySQL 5.7 GA) implementation of so called "online" DDL for InnoDB tables.

In my previous post I already shared my view on another important InnoDB feature, persistent statistics. Unlike that, I do not really hate online DDL. I just try to avoid it if possible and use tools like pt-online-schema-change or gh-ost instead. Not because it is not documented properly (the documentation is quite detailed, there are still things to clarify though) or does not work as designed, but mostly because the term "online" (if we understand it as "not blocking", or "without blocking/affecting the application and read/write operations to the table being changed is available") is a bit misleading (it is more like "less blocking" or "blocking for shorter periods of time", faster and in-place, sometimes), and because it does not work the way one might expect in any kind of replication setups.

To be more specific:
  • Replication ignores LOCK=NONE :) Slave will only start to apply "concurrent" DML after commit, and this leads to a huge replication lag.
  • In too many cases the entire table is rebuilt (data are (re-)written), in place or by creating a copy, while notable writes in the process of running ALTER TABLE are really required only if we are introducing stricter constraints (and even in this case we can just validate the table, return error if some row does not satisfy new constraint, too long to fit, for example, and then change metadata if all rows are OK) or adding new indexes (that in any case can not be used until they are built).
  • The online log has to be kept (in memory or in temporary file). There is one such log file for each index being created or table being altered. Manual says:
    "This log file stores data inserted, updated, or deleted in the table during the DDL operation. The temporary log file is extended when needed by the value of innodb_sort_buffer_size, up to the maximum specified by innodb_online_alter_log_max_size. If a temporary log file exceeds the upper size limit, the ALTER TABLE operation fails and all uncommitted concurrent DML operations are rolled back. Thus, a large value for this option allows more DML to happen during an online DDL operation, but also extends the period of time at the end of the DDL operation when the table is locked to apply the data from the log."
    The problem is that the size depends on the concurrent DML workload and is hard to predict. Note also "when the table is locked" above to understand how much "online" is this...
There are also bugs, and I'd like to discuss some of them:
  • Bug #82997, "Online DDL fails with". There are not enough public details to be sure with what exactly, but maybe the problems (several are reported) happen when the table altered has generated column. if this is really so, the bug may be fixed in MySQL 5.7.19+.
  • Bug #73196, "Allow ALTER TABLE to run concurrently on master and slave". I can not put this better than Andrew Morgan did it in this verified feature request:
    "With online ALTER TABLE it is possible for the DDL operation to run for many hours while still processing DML on that same table. The ALTER TABLE is not started on the slave until after it has completed on the master and it will again take many hours to run on the slave. While the DDL runs on the slave, it is not possible for it to process any transactions which followed the ALTER TABLE on the master as they may be dependent on the changes that were made to the table's schema. This means that the slave will lag the master by many hours while the ALTER TABLE runs and then while it catches up on the backlog of DML sent from the master while that was happening."
    Both pt-osc and gh-ost resolve this problem, as they take replication topology into account and can throttle changes on master if needed. See also this documentation request by Daniël van Eeden, Bug #77619 , that lists more limitations of "online" DDL, and check how it helped to clarify them here.
  • Bug #67286, "InnoDB Online DDL hangs". It ended up as "Not a bug", but there is a good explanation of exclusive metadata lock set by the "online" ALTER in the comments:
    "The final (short) phase of ALTER where the internal data dictionary is updated requires exclusive access. That's why the ALTER was blocked by the active transaction having a shared lock on the table."
    I once studied similar (and even simpler) case in a lot of details with gdb, see this blog post. I've clearly see MDL_EXCLUSIVE lock request for simple ALTER TABLE ... STATS_AUTO_RECALC=1 that (according to the manual) "permits concurrent DML". Other manual page clarifies:
    "In most cases, an online DDL operation on a table waits for currently executing transactions that are accessing the table to commit or roll back because it requires exclusive access to the table for a brief period while the DDL statement is being prepared. Likewise, the online DDL operation requires exclusive access to the table for a brief time before finishing. Thus, an online DDL statement also waits for transactions that are started while the DDL is in progress to commit or roll back before completing."
    Dear MySQL Oracle developers, just remove "In most cases" (or clarify it), and this would be fair enough!
  • Bug #84004, "Manual misses details on MDL locks set and released for online ALTER TABLE". That's my documentation request I filed after spending some time tracing metadata locks usage in gdb. My request is simple (typos corrected):
    "Describe all kinds of metadata locks used by MySQL, their interactions and order of acquisition and release for most important SQL statements, including (but not limited to) all kinds of online ALTER TABLE statements for InnoDB tables."
  • Bug #68498, "can online ddl for innodb be more online?". This report by Mark Callaghan that refers to this detailed study is still "Verified". Based on the comments to that blog post, it is "enough online", but the details of implementation were not clearly documented at the moment. Check for the details and clarifications in the comments!
  • Bug #72109, "Avoid table rebuild when adding or removing of auto_increment settings". The bug report from Simon Mudd is still "Verified".
  • Bug #57583, "fast index create not used during "alter table foo engine=innodb"". The bug is still "Verified" and I can not tell from the manual if this is implemented in MySQL 5.7 or not.
  • Bug #83557, "Can't use LOCK=NONE to drop columns in table with virtual columns" - nice "Verified" bug report by Monty Solomon.
  • Bug #70790, "ALTER TABLE REBUILD PARTITION SHOULD NOT PREVENT DML IN UNAFFECTED PARTITIONS". My former colleague in Oracle Arnaud Adant simply asked to provide proper and reasonable support of online DDL for partitioned tables. This bug report is still "Verified", but at least we have a separate manual page now that explains the details and limitations of online DDL with partitioned tables (most of Arnaud's requests are still NOT implemented).
  • Bug #81819, "ALTER TABLE...LOCK=NONE is not allowed when FULLTEXT INDEX exists". As Marko Mäkelä explains in the last comment of this "Verified" feature request:
    "However, LOCK=NONE is never supported when a FULLTEXT INDEX exists on the table. Similarly, LOCK=NONE is not supported when SPATIAL INDEX (introduced in MySQL 5.7) exist. Speaking as the author of WL#6255 which implemented ALTER TABLE...LOCK=NONE for InnoDB B-tree indexes in MySQL 5.6, I share the bug reporter's disappointment."
To summarize, online DDL in MySQL 5.7 is surely far above and beyond "fast index creation", but there is still a lot of room from improvements. Real solution (that allows to perform ALTER TABLE fast and without unnecessary changes/writes to data in way more cases) may come with real data dictionary in MySQL and support for multiple table versions there, or from ideas like those implemented in MDEV-11369, "Instant add column for InnoDB", and expressed in MDEV-11424, "Instant ALTER TABLE of failure-free record format changes". Until that all is implemented I'd prefer to rely on good old tools like pt-osc

In any case we speak about backward incompatible changes to the way MySQL works and stores data now.

On InnoDB's Persistent Optimizer Statistics

As I put it in recent Facebook post, one of MySQL features that I truly hate is InnoDB's persistent statistics. I think I should clarify this statement. It's great to have a way to save statistics in between server restarts, to have better control on the way it is estimated (even on a per table basis), set it explicitly, check it with simple SELECT. These all are great additions since MySQL 5.6.2+ that I truly appreciate (even if I may not be happy with some implementation details). They helped to make plans for queries against InnoDB more predictable and allow (with some efforts applied) MySQL query optimizer to really work as "optimizer" instead of "randomizer" or "pessimizer" (as some people called it) for InnoDB tables.

What I hate about it mostly is the way innodb_stats_auto_recalc is implemented, and the fact that it is enabled by default since MySQL 5.6.6+ or so. Here is why:
  1. Even if one enables automatic statistics recalculation, she can not be sure that statistics is correct and up to date. One still really has to run ANALYZE TABLE every time after substantial changes of data to be sure, and this comes with a cost (that Percona tried to finally overcome with the fix to lp:1704195 that appeared in their Percona Server 5.7.20-18+). Or enjoy whatever bits of statistics (taken in the process of background recalculation) may be present at the moment and the resulting execution plans...
  2. The details on automatic statistics recalculation are not properly documented (if only in some comments to some bug reports). This changes to better with time (thanks to continue pressure from MySQL community, including your truly, in a form of bug reports), but still most of MySQL users are far from understanding why something happens or NOT happens when this feature is involved.
  3. Implementation introduced background thread (that does dirty reads) to do recalculation, and separate transactions against InnoDB tables where statistics is stored. This complicates implementation, analysis in gdb etc, and introduced more bugs related to coordination of work performed by this thread and other background and user threads.
  4. Recently nobody from Oracle cares much to fix bugs related to this feature.
Let me try to illustrate and prove the points above with some MySQL bug reports (as usual). Many of these bugs are still "Verified" and not fixed as of recent release of recent GA version, MySQL 5.7. The order is somewhat random:
  • Bug #70741, "InnoDB background stats thread is not properly documented" - that's one of my requests to improve documentation. Some more details were added and the bug is closed, but make sure to read the entire comment "[26 Nov 2013 13:41] Vasil Dimov" if you want to understand better how it all works.
  • Bug #70617, "Default persistent stats can cause unexpected long query times" - this is one of bugs that led me to filing the previous documentation request. Check comments by Vasil Dimov there that he made before closing it as "Not a bug"... His comments are the best documentation of the way feature is implemented that I've seen in public. Make your own conclusions.
  • Bug #78289, "Bad execution plan with innodb_stats_persistent enabled" - note that the problem started after pt-osc was applied (to overcome the problems with another feature I hate, "online" ALTER TABLE, most likely). This utility applies RENAME to the table that is altered at the last stage, and as a result statistics just disappears and you have either to wait until it is calculated again in the background, or run ANALYZE... Surely this is "Not a bug".
  • Bug #80178 and Bug #78066 are about cases when SHOW INDEXES may still give wrong results while (with persistent statistics automatic recalculation disabled) one expects the same values we see in the tables where statistics is stored, or just correct ones. Both bugs are still "Verified", even though from the comment in the latter one may assume that the problem may be fixed in recent MySQL 5.7.x.
  • Bug #75428, "InnoDB persistent statistics not persistent enough". The counter of updated rows since last recalculation does not survive restarts, and 10% threshold is not configurable, so if server restarts often and table is big enough, we may get statistics never updated. Still "Verified".
  • Bug #72368, "Empty/zero statistics for imported tablespace until explicit ANALYZE TABLE". Still "Verified", but may be fixed in versions newer than 5.6. Importing tablespace was NOT a reason for automatic statistics recalculation to ever happen for the table...
  • Bug #84940, "MySQL Server crash possibly introduced in InnoDB statistics calculation". This regression bug in 5.6.35 and 5.7.17 was quickly fixed in the next releases, but still caused troubles for some time.
  • Bug #82184, "Table status inconsistent, requires ANALYZE TABLE executed twice". As Andrii Nikitin stated himself, "Most probably second ANALYZE is needed to give some time to purge thread remove old versions of the rows.", in case the table has huge blobs. The bug is still "Verified".
  • Bug #71814, "Persistent stats activity conflicts with mysqldump import of same info". The bug is "Closed" without any reason stated in public (what a surprise...). Note the following comment by Shane Bester (who actually verified and explained the bug):
    "Personally, I don't like that mysqldump dumps the content of these tables that should be auto-generated."
    He had also suggested a workaround to disable persistent statistics (SET GLOBAL innodb_stats_auto_recalc=0; SET GLOBAL innodb_stats_persistent=0;) before importing a dump. The problem here is a race condition between the importing of mysql database and the background statistics thread that can insert rows into the table between the CREATE and LOCK TABLE in the dump. See Bug #80705, "Mysqlpump in default configuration does not work", also, with a clear request: "Do not dump innodb_index_stats or innodb_table_stats". Something to think about.
  • Bug #84654, "Cardinality reset to 0 with persistent statistics when AUTO_INCREMENT touched".  Still "Verified".
  • Bug #84287, "row inserts, statement updates, persistent stats lead to table scans+ lag slaves". It seems automatic recalculation of statistics on slave is not triggered by inserting more rows via row-based replication events. Still "Verified".
  • Bug #82969 , "InnoDB statistics update may temporarily cause wrong index cardinalities". This bug (still "Verified") is my all times favorite race condition in the implementation of persistent statistics by Oracle (well, this one and Bug #82968 that is fixed at least in recent 5.7.x and in MariaDB).
 There are also bugs related to other details of InnoDB persistent statistics implementation:
  • Bug #78401, "ANALYZE TABLE" may assign temporary values to table stats during its execution". Statistics is not updated atomically, it is first reset and then recaclulated. Still "Verified".
  • Bug #86926, "The field table_name (varchar(64)) from mysql.innodb_table_stats can overflow." - this may be not enough for partitioned table, as partition names may be longer. Still "Verified".
  • Bug #67179, "mysql system tables innodb_table_stats,slave_master_info not accessible on clean". This was a famous bug during early days of MySQL 5.6 that affected many users who tried to upgrade. You may still need this file from it one day, to re-create missing InnoDB tables in the mysql database.
  • Bug #80986, "innodb complains about innodb_table_stats even if persistent stats disabled". Still "Verified".
  • Bug #86927, "Renaming a partitioned table does not update mysql.innodb_table_stats.". Fixed recently in MySQL 5.7.21 and 8.0.4.
  • Bug #84455 - the topic of this bug report is different and not relevant, but Shane Bester noted the following in the error log uploaded:
    [Warning] InnoDB: A transaction id in a record of table `mysql`.`innodb_table_stats` 
    is newer than the system-wide maximum.
    This is both suspicious and scary. May be related to the way background thread works.
  • Bug #74747, "Failing assertion: index->id == btr_page_get_index_id(page) line 899". Yes, this is a debug assertion only provoked explicitly, but note what is written in the error log before it happens:
    InnoDB: Cannot save table statistics for table "db1"."t1": Too many concurrent transactions
    It means background thread opens a separate transaction (no surprise, but still worth to note).
  • Bug #86702, "please disable persistent stats on the mysql.gtid_executed table". This is a valid and verified request to remove the related overhead for this "system" InnoDB table (as it was correctly done for several others).
So, the implementation of InnoDB's Persistent Optimizer Statistics is far from perfect or well documented. One may ask what I'd suggest instead? I often think and state that only engine-independent persistent statistics (in MariaDB style) should exist, and this should be recalculated only by explicit ANALYZE TABLE statement, maybe with more options to set sample size and other details than we have now. No background threads, nothing automatic until automated by the DBA (for this I'd appreciate a package like Oracle's dbms_stats).

This kind of idea is usually not well accepted. One of recent (valid) complains by Domas here were "No, thanks, don't need more MDL holders." and "I prefer lockless versioned stats, when it gets to what I prefer."

Some of the problems mentioned above may be resolved in MySQL 8 (or not) with its atomic data dictionary operations. Other idea presented in MDEV-15020 is to store statistics with data in the same .ibd file. We shell see what may happen, but current implementation, even though it improved a lot since early MySQL 5.6 days, is hardly long term acceptable.