## Friday, July 6, 2018

### Problems of Oracle's MySQL as an Open Source Product

In my previous summary blog post I listed 5 problems I see with the way Oracle handles MySQL server development. The first of them was that "Oracle does not develop MySQL server in a true open source way" and this is actually what I started my draft of that entire blog post with. Now it's time to get into details, as so far there was mostly fun around this and statements that MariaDB also could do better in the related Twitter discussion I had.

So, let me explain what forces me to think that Oracle is treating MySQL somewhat wrong for the open source product.

 Nice pathway on this photo, but it's not straight and it's not clear where it goes. Same with MySQL development...
We get MySQL source code updated at GitHub only when (or, as it often happened in the past, some time after) the official release of new version happens. You can see, for example, that MySQL 8.0 source code at GitHub was actually last time updated on April 3, 2018, while MySQL 8.0.11 GA was released officially on April 19, 2018 (and that's when new code became really available in public repository). We do not see any code changes later than April 3, while it's clear that there are bug fixes already implemented for MySQL 8.0.12 (see Bug #90523 - "[MySQL 8.0 GA Release Build] InnoDB Assertion: (capacity & (capacity - 1)) == 0", for example. There is an easy way to crash official MySQL 8.0.11 binaries upon startup, fixed back before April 30, with some description of the fix even, but no source code of the fix is published) and 8.0.13 even (see Bug #90999 - "Bad usage of ppoll in libmysql"). With Oracle's approach to sharing the source code, we can not see the fixes that are already made long time ago, apply them, test them or comment on them. This is fundamentally wrong, IMHO, for any open source software.

In other projects we usually can see the code as soon as it is pushed to the branch (check MariaDB if you care, last change few hours ago at the moment). Main branches may have more strict rules for updating, but in general we see fixes as they happen, not only when new official release happens.
Side note: if you see that Bug #90523 became private after I mentioned it here, that's another wrong thing they often do. More on the in the next post, on community bug reports handling by Oracle...
Interesting enough, when the fix comes from community we can usually see the patch. This happened to the Bug #90999 mentioned above - we have a fix provided by Facebook and one can see the patch in Bug #91067 - "Contribution by Facebook: Do not use sigmask in ppoll for client libraries". When somebody makes pull request, patch source is visible. But one can never be sure if it's the final patch and had it passed all the usual QA tests and reviews, or what happens to pull requests closed because developer had not signed the agreement...

If the fix is developed by Oracle you'll see the code changed only with/after the official release. Moreover, it would be on you to identify the exact commit(s) that introduced the fix. For a long time Laurynas Biveinis from Percona cared to add comments about the exact commit that fixed the bug to public bug reports (see Bug #77689 - "mysql_execute_command SQLCOM_UNLOCK_TABLES redundant trans_check_state check?" as one of examples). Community members have to work hard to "reverse engineer" Oracle's fixes and link them back to details of real problems (community bug reports) they were intended to resolve!

Compare this to a typical changelog of MariaDB that leads you directly to commits and code changes.

What's even worse, Oracle started a practice to publish only part of their changes made for the release. Some tests, those for "security" bugs, are NOT published even if we assume they exist or even can be 100% sure they exist.

My recent enough favorite example is the "The CREATE TABLE of death" bug reported by Jean-François Gagné. If you follow his blog post and links in it you can find out all the details, including the test case that is public in MariaDB. With this public information you can go and crash any affected older MySQL versions. Bug reporter did everything to inform affected vendors properly, and responsible vendors disclosed the test (after they fixed the problem)!

Now, try to find similar test in public GitHub tree of Oracle MySQL. I tried to find it literally, try to find references to somewhat related public bug numbers etc, but failed. If you know better and can identify the related public test at GitHub, please, add a comment and correct me!

To summarize, this is what I am mostly concerned about:
1. Public source code is updated only with the releases. There are no feature-specific code branches, development branches, just nothing public until the official release.
2. Oracle does not provide any details about commits and their relations to bugs fixed in the release notes or anywhere else outside GitHub. One has to go study the source code to make his own conclusions.
3. Oracle does not share some of test cases in their commits. So, some test cases remain non-public and we can only guess (based on code analysis) what was the real intention of the fix. This applies to security bugs and who knows to what else.
I would not go into other potential problems (I've heard about some others from developers, for example, related to code refactoring Oracle does) or more details. The above is enough for me to state that Oracle do wrong things with the way they publish source code and threat MySQL as open source product.

All the problems mentioned above were introduced by Oracle, these never happened in MySQL AB or Sun. MariaDB and Percona servers may have their own problems, but the above do NOT apply to them, so I state that other vendors develop MySQL forks and related projects differently, and still are in business and doing well!

## Sunday, July 1, 2018

### What's Right and What's Wrong With Oracle's Way of MySQL Server Development

Recently it's quite common to state that "Oracle's Acquisition Was Actually the Best Thing to Happen to MySQL". I am not going to argue with that - Oracle proved over years that they are committed to continue active development of this great open source RDBMS, and they have invested a lot into making it better and implementing features that were missed or became important recently. Unlike Sun Microsystems, they seem to clearly know what to do with this software to make it more popular and make money on it.

Among the right things Oracle does for MySQL server development I'd like to highlight the following:
1. MySQL server development continues, with new features added, most popular OSes supported, regular releases happened and source code still published at GitHub under GPL license.
2. Oracle continues to maintain public MySQL bugs database and fix bugs reported there.
3. Oracle accepts external contributions to MySQL server under clear conditions. They acknowledge contributions in public. The release notes, in particular, mention authors of each community-provided patch.
4. Oracle works hard on improving performance and scalability of MySQL server.
5. Oracle tries to provide good background for their new MySQL designs (check new InnoDB redo logging, for example).
6. Oracle cooperates with MySQL Community. They organize their own community events and participate in numerous related conferences, including (but not limited to) the biggest Percona Live ones. Oracle engineers speak and write about their work in progress. Oracle seems to actively support some open source tools that work with MySQL server, like ProxySQL.
7. Oracle still keeps and maintains pluggable storage engine architecture and plugin APIs, even though their own development is recently mostly related to InnoDB storage engine.
8. Oracle still maintains and improves public MySQL Manual.
So, Oracle is doing good with MySQL, and dozens of their customers, community members and MySQL experts keep stating this all the time. But, as a former and current "MySQL Entomologist" (somebody who worked on processing MySQL bug reports from community and reported MySQL bugs for 13 years), I clearly see problems with the way Oracle handles MySQL server development. I write and speak about these problems in public since the end of 2012 or so, and would like to summarize them in this post.
 MySQL's future is bright, but there are some clouds

Here is the list of problems I see:
1. Oracle does not develop MySQL server in a true open source way.
2. Oracle does not care enough to maintain public bugs database properly.
3. Some older MySQL features remain half-backed, not well tested, not properly integrated with each other and new features, and not documented properly, for years.
In general, Oracle's focus seem to be more on new developments and cool features for MySQL (with some of them got ignored and going nowhere with time).
4. 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).
5. MySQL Manual still have many details missing and is not fixed fast enough.
Moreover, it is not open source, so there is no other way for community to fix or improve it other than add comments or report documentation bugs, and wait.
In the upcoming weeks I am going to explain each of these items in a separate post, with some links to my older blog posts, MySQL server bug reports and other sources that should illustrate my points. In the meantime I am open for comments from those who disagree with the theses presented above.

## Sunday, June 24, 2018

### On InnoDB Data Compression in MySQL

Another story that I've prepared back in April for my meeting with one of customers in London was a "compression story". We spent a lot of time on it in several support issues in the past, with only limited success.

In case of InnoDB tables, there are actually two ways to compress data (besides relying on filesystem compression or compressing individual columns at server or application side). Historically the first one was introduced by the Barracuda InnoDB file format and ROW_FORMAT=COMPRESSED it supported. Notable number of related bugs were reported with time, and it may be not that easy to identify them all (you can find current list of bugs tagged with "compression" here). I've picked up the following bugs for my "story":
• Bug #88220 - "compressing and uncompressing InnoDB tables seems to be inconsistent". Over years Simon Mudd, Monty Solomon (see related Bug #70534 - "Removing table compression leaves compressed keys") and other community members reported several bugs related to inconsistencies and surprises with key_block_size option. It is used for both MyISAM and InnoDB storage engines (for compressed tables) and it seems nobody is going to fix the remaining problems until they are gone with MyISAM engine.
• Bug #69588 - "MyISAM to InnoDB compressed slower than MyISAM to InnoDB, Then InnoDB to Compressed". Just a detail to take into account, noted 5 years ago by Joffrey MICHAIE, verified almost 4 years ago and then getting zero public attention from Oracle engineers.
• Bug #62431 - "What is needed to make innodb compression work for 32KB pages?". Nothing can be done according to the manual:
"In particular, ROW_FORMAT=COMPRESSED in the Barracuda file format assumes that the page size is at most 16KB and uses 14-bit pointers."
• Bug #78827 - "Speedup replication of compressed tables". Come on, Daniël van Eeden, nobody cares that
"Replication and InnoDB compressed tables are not efficiently working together."
The bug is still "Open".
• Bug #75110 - "Massive, to-be-compressed not committed InnoDB table is total database downtime". This problem was reported by Jouni Järvinen back in 2014. Surely this is not a bug, but it seems nobody even tried to speed up compression in any way on multiple cores.
• Bug #84439 - "Table of row size of ~800 bytes does not compress with KEY_BLOCK_SIZE=1". It was reported by Jean-François Gagné, who asked for a reasonable error message at least. Nothing happens after verification.
• Bug #77089 - "Misleading innochecksum error for compressed tables with key_block_size=16". This problem was reported by Laurynas Biveinis more than three years ago, immediately verified and then got zero attention.
 The boats above do not use the space for mooring efficiently. They need better compression.
Transparent Page Compression for InnoDB tables was added later and looked promising. If you are lucky to use filesystem with sparse file and hole punching support and proper OS or kernel version, then you could expect notable saving of disk space with very few additional keystrokes (like COMPRESSION="zlib") when defining the table. Different compression libraries were supported. Moreover (see here), only uncompressed pages are stored in memory in this case, and this improved the efficiency of buffer pool usage. Sounded promising originally, but there are still bugs to consider:
• Bug #78277 - "InnoDB deadlock, thread stuck on kernel calls from transparent page compression". This bug alone (reported by Mark Callaghan back in 2015) may be a reason to NOT use the feature in production, as soon as you hit it (chances are high). there are many interesting comments that there are environments where the feature works as fast as expected, but I think this summary is good enough for most users:
"[19 Oct 2015 15:56] Mark Callaghan
...
Slow on XFS, slow on ext4, btrfs core team tells me it will be slow there. But we can celebrate that it isn't slow on NVMFS - closed source, not GA, can't even find out where to buy it, not aware of anyone running it."
The bug is still "Open".
• Bug #81145 - "Sparse file and punch hole compression not working on Windows". Not that I care about Windows that much, but still. The bug is "Verified" for 2 years.
• Bug #87723 - "mysqlbackup cannot work with mysql5.7 using innodb page-level compression" Now this is awesome! Oracle's own MySQL Enterprise Backup does NOT support the feature. Clearly they cared about making it useful...
As a side note, same problem affects Percona's xtrabackup (see PXB-1394). MariaDB resolved the problem (and several related ones like MDEV-13023) with mariabackup tool.
• Bug #87603 - "compression/tablespace ignored in create/alter table when not using InnoDB". COMPRESSION='.../' option is supported for MyISAM tables as well, and this again leads to problems when switching to another storage engine, as Tomislav Plavcic noted.
• Bug #78672 - "assert fails in fil_io during linkbench with transparent innodb compression". This crash (assertion failure) was noted by Mark Callaghan back in 2015. May not crash anymore since 5.7.10 according to the last comment, but nobody cares to close the bug or comment anything useful. The bug is still "Verified".
That's almost all I prepared for my "compression story". It had to be sad one.

What about the moral of the story? For me it's the following:
1. Classical InnoDB compression (page_format=compressed) has limited efficiency and does not get any attention from developers recently. If you hit some problem with this feature you have to live with it.
2. 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 software and was not integrated with backup tools. MariaDB improved it, added support for backing up page compressed tables efficiently with the same familiar xtrabackup-based approach, but there are still open problems to resolve (see MDEV-15527 and MDEV-15528 that I also picked up for my "story").
3. It seems (based on public sources review at least) that both compression options do not get much attention from Oracle developers recently. If you check new features of MySQL 8.0 GA here,  you may notice that zlib version is updated, compressed temporary InnoDB tables are no longer supported and... that's all about compression for InnoDB!
This story could probably be shortened to just one link to the summary post by Mark Callaghan from Facebook (who studied the efficiency of data compression by various engines a lot, among other performance metrics), or by simple statement that if you want data to be compressed efficiently at server side do NOT use current InnoDB implementations and better use RocksDB engine (with MariaDB or Percona Server if you need other modern features also). But I can not write any story about MySQL without referring to some bugs, and this is how I've ended up with the above.

What if you just switched to MySQL 8.0 GA and need some new features from it badly? Then just wait for a miracle to happen (and hope Percona will make it one day :)

## Saturday, June 23, 2018

### On Partitioning in MySQL

Back in April I was preparing for vacations that my wife and I planned to spend in UK. Among other things planned I wanted to visit a customer's office in London and discuss few MySQL and MariaDB related topics, let's call them "stories". I tried to prepare myself for the discussion and collected a list of known active bugs (what else could I do as MySQL entomologist) for each of them. Surely live discussion was not suitable to share lists of bugs (and for some "stories" they were long), so I promised to share them later, in my blog. Time to do what I promised had finally come!

One of the stories we briefly discussed was "partitioning story". Right now I can immediately identify at least 47 active MySQL bugs in the related category.  While preparing I checked the same list and picked up 15 or so bug reports that had to illustrate my points. Let me share them here in no specific order, and add few more.
In April the latest still active bug in partitioning reported by MySQL community was  Bug #88916 - "Assertion table->s->db_create_options == part_table->s->db_create_options'", from my colleague Elena Stepanova. Note a very simple test case that leads to assertion in debug builds, immediately verified.

Recently two more bugs were reported. Reporter of Bug #91190 - "DROP PARTITION and REORGANIZE PARTITION are slow" suspects a performance regression in MySQL 8.0.11. I've subscribed to this bug and is following the progress carefully. Same with Bug #91203 - "For partitions table, deal with NULL with is mismatch with reference guide". I think what happens with NULL value and range partitioning perfectly matches the manual, but the fact that INFORMATION_SCHEMA.PARTITIONS table may return wrong information after dropping partition with NULL value is somewhat unexpected.

Now back to the original lists for the "story" I prepared in April:
• Bug #60023 - "No Loose Index Scan for GROUP BY / DISTINCT on InnoDB partitioned table". It was reported by Rene' Cannao' and since 2013 I strongly suspect that it's fixed in MySQL 5.6+ or, as noted in another comment, may depend on statistics properly collected for the table. Still the status remains "Verified".
• Bug #78164 - "alter table command affect partitioned table data directory". Your custom DATA DIRECTORY settings may get lost when ALTER is applied to the whole table. Quick test shows that at least in MariaDB 10.3.7 this is no longer the case. The bug is still "Verified".
• Bug #85126 - "Delete by range in presence of partitioning and no PK always picks wrong index". It was reported by Riccardo Pizzi 16 months ago, immediately verified (without explicit list of versions affected, by the way). One more case when ordering of indexes in CREATE TABLE may matter...
• Bug #81712 - "lower_case_table_names=2 ignored on ADD PARTITION on Windows". Who cares about Windows these days?
• Bug #84356 - "General tablespace table encryption". It seems partitioning allows to overcome documented limitation. If this is intended, then the manual is wrong, otherwise I suspect the lack of careful testing of partitioning integration with other features.
• Bug #88673 - "Regression CREATE TBL from 5.7.17 to 20 (part #1: innodb_file_per_table = ON)." I've probably mentioned this bug reported by Jean-François Gagné in more than one blog post already. Take care and do not use long partition names.
• Bug #85413 - "Failing to rename a column involved in partition". As simple as it sounds, and it still happens.
• Bug #83435 - "ALTER TABLE is very slow when using PARTITIONED table". It was reported by Roel Van de Paar back in 2016 and still remains "Verified".
• Bug #73084 - "Exchanging partitions defined with DATA DIRECTORY and INDEX DIRECTORY options". The bug still remains "Open" (see Bug #77772 also).
• Bug #73648 - "innodb table replication is very slow with some of the partitioned table". It seems to be fixed last year as internal Bug #25687813 (see release notes for 5.6.38), but nobody cares to find this older duplicate and change its status or re-verify it.
• Bug #83750 - "Import via TTS of a partitioned table only uses 1 cpu core". This feature requested by Daniël van Eeden makes a lot of sense. I truly hope to see parallel operations implemented for partitioned tables in GA MySQL versions (as I saw some parallel processing for partitions done for some upcoming "6.1" or so version back in 2008 in Riga during the MySQL's last company meeting I've attended).
• Bug #64498 - "Running out of file handles when ALTERing partitioned MyISAM table". Too many file handles are needed. This is a documented limitation that DBAs should still take into account.
I also prepared a separate small list of partition pruning bugs:
• Bug #83248 - "Partition pruning is not working with LEFT JOIN". I've reported it back in 2016 and it is still not fixed. There are reasons to think it is not so easy.
• Bug #75085 - "Partition pruning on key partitioning with ENUM". It was reported by  Daniël van Eeden back in 2014!
• Bug #77318 - "Selects waiting on MDL when altering partitioned table". One of the worst expectations DBA may have is that partitioned tables help to workaround "global" MDL locks because of partition pruning! This is not the case.
Does this story have any moral? I think so, and for me it's the following:
1. Partitioning bugs do not get proper attention from Oracle engineers. We see bugs with wrong status and even a bug with a clear test case and a duplicate that is "Open" for 4 years. Some typical use cases are affected badly, and still no fixes (even though since 5.7 we have native partitioning in InnoDB and changing implementation gave good chance to review and either fix or re-check these bugs).
2. MySQL DBAs should expect all kinds of surprises when running usual DDL statements (ALTER TABLE to add column even) with partitioned tables. In the best case DDL is just unexpectedly slow for them.
3. Partition pruning may not work they way one expects.
4. We miss parallel processing for partitioned tables. They should allow to speed up queries and DDL, not to slow them down instead...
5. One can suspect that there is no careful internal testing performed on integration of partitioning with other features, or even basic partition maintenance operations.

## Monday, May 28, 2018

### Fun with Bugs #68 - On MySQL Bug Reports I am Subscribed to, Part VII

Last time I reviewed my recent subscriptions to MySQL bugs it was April 1, 2018. I was busy working, blogging about other bugs, running random MTR tests on MySQL 8.0.11 and got two weeks of vacation since than. Now it's time to briefly review 20 recent bug reports (mostly for MySQL 8.0.11) I was interested in.

• Bug #91010 - "WolfSSL build broken due to cmake typo". MySQL 8 can be compiled using wolfSSL according to the manual, but as Laurynas Biveinis found this is not the case in practice, and not only because of the typo in libutils.cmake. It seems nobody tried to test this kind of build recently. I wonder what else they had not tested in a hurry to release nice MySQL 8.0.11 GA...
• Bug #91009 - "Incorrect usage of std::atomic::compare_exchange_weak". My dear friend Sinisa Milivojevic verified this report by Alexey Kopytov as a feature request. I think it's still a bug, even if it does not have any visible effect on processors currently supported. Let's see what may happen with this report next.
• Bug #90968 - "Several incorrect function type indirect call UBSan errors". It seems Laurynas Biveinis found yet another kind of testing that Oracle decided not to bother much with while working on MySQL 8 GA release. More test runs with UBSan are needed while working on MySQL 8.0.12.
• Bug #90959 - "XA transactions can lock forever if a gap lock is also taken on the slave". This bug was reported by Andreas Wederbrand and additional test case by Sveta Smirnova shows how serious it might be.
• Bug #90890 - "CPU overhead for insert benchmark load increased by 2X in 8.0.11". Serious problem for single-threaded case was reported by Mark Callaghan. Single thread insert rate continues to drop from 5.6 to 5.7 and 8.0.11.
• Bug #90847 - "Query returns wrong data if order by is present". Wrong results bugs are the worst, probably, as it may be hard to notice the problem until it's too late... This bug was reported by Vincenzo Antolini.
• Bug #90794 - "GR 5.7.22 not compatible with earlier versions due to lower_case_table_names". I can afford not to care at all about group replication at the moment, but this regression bug noted by Kenny Gryp may affect many less lucky people during upgrade in production.
• Bug #90670 - "InnoDB assertion failure in log0write.cc time_elapsed >= 0". I do not see any public attempts to process this bug reported by Mark Callaghan. It may be not easy to repeat, but Mark's idea of more useful information in the assert message is great anyway.
• Bug #90643 - "use different mutex to protect trx_sys->serialisation_list". Nice feature request from Zhai Weixiang.
• Bug #90617 - "using gdb to attach mysqld will shutdown the instance". I can not reproduce this bug with binaries I've built from source, but that would be a really awful bug if it happens with Oracle binaries. I am surprised that this bug report by Zhai Weixiang is still "Open" and had not got proper attention from Oracle engineers for more than a month...
• Bug #90579 - "please document how to configure the dragnet 8.0 logging non-interactively". Unlike Simon Mudd, I had not started to read the manual for new 8.0 features carefully yet. It may become a source of dozens of additional bug reports if it's of the same quality as, say, MySQL 5.6's manual at the moment of GA. We shall see.
• Bug #90571 - "Don't generate warnings about successful actions (dragnet filter rules)". MySQL 8 reduced the number of messages in the error log and allows to control the content better, but as Simon Mudd reasonably noted, adding filters successfully should not generate warnings by itself.
• Bug #90554 - "Undesired change for Windows users in 8.0". As Peter Laursen noted, the idea to disable network connections if server is started with --skip-grant-tables may look good from security point of view, but this unexpected change may leave Windows users (who had not configured any other connectivity options) without a known way to set/reset passwords.
• Bug #90534 - "InnoDB Cluster members stop by failed start of Group Replication". This bug was reported by Ryusuke Kajiyama. Based on recent comments, this may happen not only on macOS Sierra version 10.12.6 (as it was stated initially).
• Bug #90484 - "No (easy) way to know if a GR node is writeable or not". Nice feature request from Kenny Gryp to
"Make it possible to determine _easily_ if a node is part of primary partition AND which node can accept writes."
• Bug #90448 - "INSERT IGNORE not ignored on SQL thread". It happens for a table with partition missing for the date, but still is unexpected. Fortunately this bug reported by Simon Mudd is NOT repeatable on MySQL 8.0.11 GA (it seems to affect only 5.7.x).
• Bug #90351 - "GLOBAL STATUS variables drift after rollback". As it was noted by Iwo P, the value of Handler_rollback may decrease in some cases. There is a patch from Zsolt Parragi in this bug report.
• Bug #90331 - "Server does not raise error on unrecognized collation id". This really serious issue was reported by Manuel Ung. Until older server versions starts to produce error when asked for a collation they do not support, it may be not safe to use 8.0.x clients with older server versions.
• Bug #90307 - "host blocking limit seems not to be used correctly". We all know that Performance Schema is near perfect. But Simon Mudd still found a bug in it that probably affects MySQL 8.0.11 as well.
• Bug #90291 - "load_file() will not raise an error if secure_file_priv option was not set". This bug was reported by Shahriyar Rzayev from Percona. It is not clear if it was checked on MySQL 8.0.x.
That's all for now. Stay tuned! I plan to write few more posts about bugs related to partitioning and InnoDB data compression, soon.

## Friday, April 20, 2018

### Fun with Bugs #67 - On Some Public Bugs Fixed in MySQL 8.0.11 GA

I stopped reviewing MySQL Release Notes for quite a some time, but major GA releases of MySQL do not happen often, so I decided to make an exception and write about some bugs from Community users fixed in MySQL 8.0.11 GA.

I'll start with good news about MySQL 8.0.11 GA! You can get sources at GitHub, and I had no problems to build on Fedora 27 on my good old QuadCore box, using the following cmake command line:
[openxs@fc23 mysql-server]$cmake . -DCMAKE_BUILD_TYPE=RelWithDebInfo -DBUILD_CONFIG=mysql_release -DFEATURE_SET=community -DWITH_EMBEDDED_SERVER=OFF -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/home/openxs/boost -DENABLE_DOWNLOADS=1 -DWITH_UNIT_TESTS=OFF -DCMAKE_INSTALL_PREFIX=/home/openxs/dbs/8.0 ... [openxs@fc23 mysql-server]$ time make -j 4
...

[100%] Built target mysqld

real    33m52.791s
user    105m47.475s
sys     8m19.018s
Comparing to previous experience, I had minor problem with unit tests, so just skipped them with -DWITH_UNIT_TESTS=OFF option. There is no problem to run the resulting binaries, unless you try to use data directory from older 8.0.x. Then you'll end up with:
2018-04-19T15:36:35.165841Z 1 [ERROR] [MY-011092] [Server] Upgrading the data dictionary from dictionary version '80004' is not supported.
2018-04-19T15:36:35.166239Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2018-04-19T15:36:35.166310Z 0 [ERROR] [MY-010119] [Server] Aborting
I had to remove data directory and initialize it from scratch (it was testing instance anyway, last time used for real while I worked on this presentation):
[openxs@fc23 8.0]$rm -rf data/* [openxs@fc23 8.0]$ bin/mysqld --no-defaults --initialize-insecure --port=3308 --socket=/tmp/mysql.sock --basedir=/home/openxs/dbs/8.0 --datadir=/home/openxs/dbs/8.0/data --skip-log-bin
2018-04-19T15:43:55.324606Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 1024 (requested 8161)
2018-04-19T15:43:55.324726Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 431 (requested 4000)
2018-04-19T15:43:55.325147Z 0 [System] [MY-013169] [Server] /home/openxs/dbs/8.0/bin/mysqld (mysqld 8.0.11) initializing of server in progress as process 20034
2018-04-19T15:44:14.438776Z 4 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2018-04-19T15:44:29.625227Z 0 [System] [MY-013170] [Server] /home/openxs/dbs/8.0/bin/mysqld (mysqld 8.0.11) initializing of server has completed
[openxs@fc23 8.0]$bin/mysqld_safe --no-defaults --port=3308 --socket=/tmp/mysql.sock --basedir=/home/openxs/dbs/8.0 --datadir=/home/openxs/dbs/8.0/data --skip-log-bin & [1] 20080 [openxs@fc23 8.0]$ 2018-04-19T15:44:58.224816Z mysqld_safe Logging to '/home/openxs/dbs/8.0/data/fc23.err'.
2018-04-19T15:44:58.271255Z mysqld_safe Starting mysqld daemon with databases from /home/openxs/dbs/8.0/data

[openxs@fc23 8.0]\$ bin/mysql -uroot --socket=/tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.11 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 8.0.11                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2        |
| version                 | 8.0.11                       |
| version_comment         | MySQL Community Server (GPL) |

| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
| version_compile_zlib    | 1.2.11                       |
+-------------------------+------------------------------+
9 rows in set (0.00 sec)

I still do not care about NoSQL, JSON, new cool features etc. You'll see megabytes of texts about these by the end of 2018. I am going to concentrate mostly on InnoDB, replication bugs and few others:
•  I am happy to start with Bugt #89509 - "Valgrind error on innodb.blob_page_reserve, bundled zlib", reported by Laurynas Biveinis. See also his Bug #89597 - "Valgrind reporting memory leak on MTR test main.validate_password_component" and Bug #89433 - "NULL dereference in dd::tables::DD_properties:unchecked_get". Percona engineers spent a lot of efforts recently testing MySQL 8.0.x and reporting bugs noted. I think Oracle should explicitly admit the impact of Percona's QA effrots for the quality of this GA release.
• Biug #89127 - "Optimize trx_rw_is_active() by tracking the lowest active transaction id". This bug was reported by Zhai Weixiang, who had suggested a patch also.
• Bug #89129 - "create table+DML on innodb_ddl_log table=crash in lock0lock.cc:7414:release_lock". This bug was reported by Ramana Yeruva. Tables were made protected and DDL and DML operations on these tables are no longer permitted.
• Bug #89087 - "Assertion key->flags & 1' failed". This debug assertion (related to the way PRIMARY key was created based on UNIQUE one) was reported by Roel Van de Paar for 5.7.21, but we see the fix documented only for 8.0.x.
• Bug #87827 - "Performance regression in "create table" speed and scalability in 8.0.3". It was reported by Alexander Rubin from Percona.
• Bug #87812 - "Concurrent DDL operation in progress even after acquiring backup lock". Nice bug report from Debarun Banerjee.
• Bug #87532 - "Replay log record cause mysqld crash during online DDL". I am happy to see impovements in "online ALTER" implementation that covers all GA versions, not just 8.0. I am also happy to see Oracle engineers (Ohm Hong in this case) reporting bugs in public!
• Bug #88272 - "Assertion new_value >= 0' failed.". Yet another debug assertion found by Roel Van de Paar, this time related to GTIDs and XA transactions. Check also his Bug #88262 - "ERROR 1598 (HY000): Binary logging not possible + abort".
• Bug #84415 - "slave don't report Seconds_Behind_Master when running slave_parallel_workers > 0". Yet another contribution from Percona engineers. This bug was reported by Marcelo Altmann and patches were provided by Robert Golebiowski. This bug is also fixed in MySQL 5.7.22.
• Bug #89793 - "INFORMATION_SCHEMA.STATISTICS field type change". Unexpected change in early 8.0.x versions was noted and reported by Mark Guinness.
• Bug #89584 - "5.7->8.0 upgrade crash with default-time-zone set". Nice to see this bug (reported by Shane Bester) fixed in GA release.
• Bug #89487 - "ALTER TABLE hangs in "Waiting for tablespace metadata lock" state". This regression bug was reported by Sveta Smirnova.
• Bug #89324 - "main.comment_column2 fails with compression". This regression was noted and reported by Manuel Ung.
• Bug #89122 - "Severe performance regression in server bootstrap". I am really happy to see this bug reported by Georgi Kodinov fixed. I noted it as soon as I started testing 8.0.x (see a duplicate by Roel Van de Paar, Bug #89444) and it was very annoying. I've already checked (see above) that the problem is gone!
• Bug #89038 - "Add new column to 'mysql.routines' to accommodate the Polygot project". So, Oracle is planning to support stored programs in different languages! Thank you, Sivert Sørumgård, for reporting this in public! See also his Bug #89035 - "Reject LCTN changing after --initialize".
• Bug #87836 - "XA COMMIT/ROLLBACK rejected by non-autocommit session with no active transaction". It would be sad if this bug is not fixed in MySQL 5.7.x, where it was originally found by Wei Zhao.
• Bug #87708 - "MDL for column statistics is not properly reflected in P_S.METADATA_LOCKS". It was reported by Erik Frøseth.
• Bug #85997 - "inplace alter table with foreign keys causes table definition mismatch". This bug was reported by Magnus Blåudd.
• Bug #85561 - "Users can be assigned non-existing roles as default". Nice to see this bug reported by Giuseppe Maxia fixed in GA release.
• Bug #33004 - "integer constants casted to bigints by unions". This bug was reported by Domas Mituzas more than 10 years ago!
Now I have to stop, as I found private bug in release notes, Bug #89512. Based on description:
"Window function row-buffer handling has been refactored to reduce the number of handler reads by 25%. (Bug #89512, Bug #27484133)"
I truly do not get why it remains private (or why it was reported in public for such a "sensitive" matter), so I better stop.

MySQL 8 is GA, finally! There are a lot more fixes there that I had not mentioned above. I am surely there is even more bugs to find. So, happy hunting!

## Sunday, April 15, 2018

### Fun with Bugs #66 - On MySQL Bug Reports I am Subscribed to, Part VI

I have some free time today, but I am still lazy enough to work on numerous planned and pending "ToDo" kind of posts, so why not to continue review of older MySQL bugs I am subscribed to. Today I am going to list 15 more bugs reported more than a year ago and still not fixed:
• Bug #85805 - "Incorrect ER_BAD_NULL_ERROR after LOAD DATA LOCAL INFILE". This detailed bug report by Tsubasa Tanaka stays "Verified" for more than a year already. It's a great example of gdb use for MySQL troubleshooting. Setting a couple of breakpoints may really help to understand how MySQL works and why some weird errors happen.
• Bug #85536 - "Build error on 5.5.54". It's clear that almost nobody besides Roel Van de Paar cares about build problem of MySQL 5.5.x(!) on Ubuntu 16.10(!). Anyway, it's strange that the bug remains "Verified" and not closed in any way if Oracle really does not intend to support MySQL 5.5 any longer. For now it seems MySQL 5.5 is still under extended support, so I hope to see this build problem fixed with some final 5.5.x release.
• Bug #85501 - "Make all options settable as variables in configuration files". We usually see Umesh Shastry processing bugs reported by other, but this is a rare case when he reports something himself. It's a great feature request.
• Bug #85447 - "Slave SQL thread locking issue on a certain XA workload on master". There are good reasons to think that this bug reported by Laurynas Biveinis may be fixed since MySQL 5.7.18, but no one cares to close it properly.
• Bug #85382 - "Getting semi-sync reply magic number errors when slave_compressed_protocol is 1". This bug was reported by Jaime Sicam. Read also comments from other community members and make your own conclusions. It seems setting slave_compressed_protocol to 1 is a bad idea in general...
• Bug #85191 - "performance regression with HANDLER READ syntax". Zhai Weixiang found clear performance regression in the way MySQL 5.7 uses metadata locking for HANDLER commands.
• Bug #85016 - "better description for: OS error: 71". Clear and simple request from Shane Bester still stays "Verified". I am not that Oracle customer affected anyway, but this seems strange to me.
• Bug #84958 - "InnoDB's MVCC has O(N^2) behaviors". This one bug report from Domas Mituzas could be a topic for a series of blog posts... It clearly states that:
"if there're multiple row versions in InnoDB, reading one row from PK may have O(N) complexity and reading from secondary keys may have O(N^2) complexity"
There is a patch that partially fixes the problem submitted by Laurynas Biveinis and created by Alexey Midenkov. While this bug is still "Verified" take carer when using secondary indexes in concurrent environments when the same data are often changed.
• Bug #84868 - "Please make it possible to query replication information consistently". Great feature request (or bug report, if you consider inconsistency as a bug) from Simon Mudd.
• Bug #84615 - "More steps in connection processlist state/ events_stages". Sveta Snirnova cared to ask to split some well known statement execution stages like "cleaning up" into more detailed ones. I think this is really important to simplify troubleshooting with performance_schema. Wrong/misleading/too generic stages forces to use other tools and may lead to wrong conclusions. I hit this with "statistics" also, see Bug #84858. Rare case when Sveta's request just stays "Open", for more than a year already.
• Bug #84467 - "ALTERing KEY_BLOCK_SIZE keeps the old kbs in KEYs.". Jean-François Gagné and other well known bug reporters found several problems related to KEY_BLOCK_SIZE. It seems Oracle engineers decided NOT to fix them (see Bug #88220). But then why this bug still stays "Verified"? Consistency in bugs processing is one of my dreams...
• Bug #84439 - "Table of row size of ~800 bytes does not compress with KEY_BLOCK_SIZE=1." Yet another bug report from Jean-François Gagné. Based on lack of activity, those looking for smaller data size, compression etc should look elsewhere and do not expect much from Oracle's InnoDB. Question is, what other engines with data compression will be supported by Oracle's MySQL 8 (or 9) GA? When you get tired wondering, consider MariaDB or Percona Server instead - they do support storage engines that are both transactional and were designed with write efficiency and space efficiency in mind. Hint: they rock...
• Bug #84274 - "READ COMMITTED does not scale after 36 threads (in 5.6 after 16 threads)". Sveta Smirnova had a chance to run benchmarks on 144 cores (the largest box I ever had a chance to use for benchmarking had 12 cores, so what do I know...) and the result is clear - READ COMMITTED transaction isolation level does not scale well (comparing to default REPEATABLE READ). It's counter intuitive for many, but that's what we have. I doubt MySQL 8 is going to change this (unfortunate) situation.
• Bug #84241 - "Potential Race Condition". This was found in MySQL 5.7 by Rui Gu with a little help from Helgrind.
• Bug #84024 - "Optimizer thinks clustered primary key is not covering". This bug was reported by Manuel Ung. Let me quote a comment by Øystein Grøvlen:
"I can agree that the cost model for join buffering is not perfect. If so, I think we should improve this model, not rely on heuristics about covering indexes versus table scan."
I can not agree more! Let's hope this really happens in MySQL 9 at least.
You probably noted that we see mostly already famous bug reporters mentioned in this list. But names of reporters, their customer or partner status, known achievements, even clear regressions found or patches provided do not force Oracle to fix problems faster these days... They have their own agenda and great plans for MySQL, obviously.

I also have my own agenda, so I'll proceed with this glass of wine...

## Tuesday, April 10, 2018

### Fun with Bugs #65 - On MySQL Bug Reports I am Subscribed to, Part V

I think it's time to review some bugs I've subscribed to several months ago, those older than in the first post from this series. There are several really serious bugs in the list of 15 below:
• Bug #87560 - "XA PREPARE log order error in replication and binlog recovery". This bug was reported by Wei Zhao, who also provided patches.
• Bug #87526 - "The output of 'XA recover convert xid' is not useful". This bug reported by Sveta Smirnova is well known and is a real pain for DBAs who have to deal with incomplete XA transactions after some crash or unexpected restart. Check PS-1818 and MariaDB task MDEV-14593. The problem is resolved in MariaDB 10.3.3+ by a new XA RECOVER FORMAT='SQL' option.
• Bug #87164 - "Queries running much slower in version 5.7 versus 5.6". It was reported by  Alok Pathak from Percona and stays "Verified" since August, 2017.
• Bug #87130 - "XA COMMIT not taken as transaction boundary". Yet another XA bug report with a patch contributed by Wei Zhao.
• Bug #87084 - "FK DELETE CASCADE does not honor innodb_lock_wait_timeout". Nice report by Elena Stepanova from MariaDB. As you can find out from MDEV-15219, it's properly fixed in MariaDB 10.2.13+.
• Bug #87065 - "Release lock on table statistics after query plan created". Great feature request by Sveta Smirnova. The actual problem behind this feature request was resolved by Percona in versions 5.7.20-18+. This fix is one of few really good reasons to use recent Percona Server 5.7, so I opened MDEV-15101 for MariaDB also.
• Bug #86926 - "The field table_name (varchar(64)) from mysql.innodb_table_stats can overflow.". I really wonder why this bug report by Jean-François Gagné still remains just "Verified".
• Bug #86865 - "InnoDB does unnecessary work when extending a tablespace". Bug report and patch by Alexey Kopytov.
• Bug #86705 - "Memory leak of Innodb". Great example of a leak (in MySQL 5.5.x only) found with a help of Valgrind/Massif. Qinglin Zhang also suggested a simple patch.
• Bug #86475 - "Error with functions and group by with ONLY_FULL_GROUP_BY". Nice feature request from Arnaud Adant.
• Bug #86462 - "mysql_ugprade: improve handling of upgrade errors". Simon Mudd asked for some better error messages at least, so that running under strace would not be needed.
• Bug #86215 - "MySQL is much slower in 5.7 vs 5.6". This report from Mark Callaghan includes a lot of results and details on performance regressions at low concurrency starting from 5.0 and up to 8.0. For some cases studied the biggest drop in QPS is from 5.6 to 5.7.
• Bug #86163 - "can't update temporary table when joined with table with triggers on read-only". I'd call this bug found by Bret Westenskow funny. The rest of them in this list are serious.
• Bug #85970 - "Memory leak with transactions greater than 10% of the total redo log size". Nice corner case was studied by Joffrey MICHAÏE. Whenever you see
The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size...
error messages, take care, as memory allocated may not be released.
• Bug #85910 - "Increased Performance_Schema overhead on Sending data". Seems to be a known problem fixed in MySQL 5.7. Still a nice report by Jervin R on the overhead one may expect from MySQL 5.6 on some systems.
So, we traveled back in time for a year. I am really sorry to see XA-related bugs and some reports with patches properly contributed still active.

## Sunday, April 1, 2018

### Fun with Bugs #64 - On MySQL Bug Reports I am Subscribed to, Part IV

I've subscribed to more than 15 new MySQL bug reports since the previous post in this series, so it's time for a new one. I am trying to follow important, funny or hard to process bug reports every day. Here is the list of the most interesting recent ones starting from the latest (with several still not processed properly):
• Bug #90211 - "Various warnings and errors when compiling MySQL 8 with Clang".  Roel Van de Paar and Percona in general continue their QA efforts in a hope to make MySQL 8 better. Current opinion of Oracle engineers on this bug is the following:
"First of all, these issues are in protobuf, not MySQL per se. There are some warnings with Clang 6, but since they're in third-party code, we have simply disabled them when compiling protobuf (will be part of 8.0.11). Optionally, -DUSE_SYSTEM_LIBS=1 will use system protobuf and thus not compile the files in question.
As for the crash, we don't support prerelease compilers (more generally, we support platforms, not compilers). Given the stack trace, it is highly likely that the issue either is in the prerelease Clang, or in protobuf.
"
Let's see how it may end up. Roel rarely gives up easily...
• Bug #90209 - "Performance regression with > 15K tables in MySQL 8.0 (with general tablespaces)". Nice regression bug report from Alexander Rubin. It is still "Open".
• Bug #90190 - "Sig=6 assertion in MYSQL_BIN_LOG::new_file_impl | binlog.cc:6862". Yet another bug report from Percona employee, Ramesh Sivaraman.
• Bug #89994 - "INDEX DIRECTORY shown as valid option for InnoDB table creation". Everybody knows how much I like fine MySQL manual. Even more I like when missing or wrong details are found there, like in this case reported by by colleague from MariaDB, Claudio Nanni.
• Bug #89963  - "Slowdown in creating new SSL connection". Maybe it's comparing apples to oranges, as stated in one of comments, but I am surprised that this (performance regression) bug report by Rene' Cannao' is still "Open". It requires more attention, IMHO. Speed of connections matters a lot for MySQL.
• Bug #89904 - "Can't change innodb_max_dirty_pages_pct to 0 to flush all pages". Good intentions to set better default value (applied a bit later than needed) led to the problem. As Simon Mudd put it:
"innodb_max_dirty_pages_pct_lwm setting has existed since 5.6. This issue only comes up as by changing the default value to 10 those of us who have ignored it until now never noticed it existed. That is a shame as setting this value to a value other than 0 (e.g. 10 which is the new default) should be better and trigger some background flushing of dirty pages avoiding us hitting innodb_max_dirty_pages_pct which would trigger much more aggressive behaviour which is not really desirable."
• Bug #89876 - "mysqladmin flush-hosts is not safe in GTID mode". Yet another bug report from Simon Mudd. See also Bug #88720 that highlights even more problems with various FLUSH statements and GTIDs.
• Bug #89870 - "Group by optimization not used with partitioned tables". For some reason this report from Arnaud Adant is still "Open". As my colleague Richard Stracke stated:
"The only solution would be, that the optimizer is able to check, if the condition in the where clause include the whole table (or partition) and in this case use group by optimization."
• Bug #89860 - "XA may lost prepared transaction and cause different between master and slave." As this (and other, like Bug #88534) bug report from Michael Yang shows, there is still a long way to go until it would be safe to use XA transactions with MySQL.
• Bug #89834 - "Replication will not connect on IPv6 - does not function in an IPv6 only environ". This bug report from Tim St. Pierre is still "Open".
• Bug #89822 - "InnoDB retries open on EINTR error only if innodb_use_native_aio is enabled". We have patch contributed by Laurynas Biveinis from Percona.
• Bug #89758 - "Conversion from ENUM to VARCHAR fails because mysql adds prefix index". This funny bug was found and reported by Monty Solomon.
• Bug #89741 - "Events log Note level messages even for log_warnings=0". Nikolai Ikhalainen found that this problem happens only in versions 5.5.x and 5.6.x, so chances to see it fixed are low. But I still want to know if this ever happens.
• Bug #89696 - "Cyclic dependencies are not resolved properly with cascade removal". Make sure to check nice discussion that my dear friend Sinisa Milivojevic had with a bug reporter, Andrei Anishchenko, before marking the bug as "Verified". This regression was most likely caused by a change in MySQL 5.7.21:
"InnoDB: An iterative approach to processing foreign cascade operations resulted in excessive memory use. (Bug #26191879, Bug #86573)"
• Bug #89625 - "please package the debug symbols *.pdb files!". Shane Bester always cared about having a way to debug on Windows. Recently I also started to care about this...
---
It's April Fools' Day today, so why not to make fool of myself assuming that anyone cares about the series of blog posts.

# "To a man with a hammer, everything looks like a nail."

Even though I had written many posts explaining the use of gdb for various MySQL-related tasks, I have to use other OS level troubleshooting tools from time to time. Moreover, as MySQL and MariaDB are still supported and used under Microsoft Windows in production by customers I have to serve them there, and use Windows-specific tools sometimes. So, I decided to start a series of posts (that I promised to my great colleague Vladislav Vaintroub (a.k.a Wlad) who helped me a lot over years and actually switched my attention from Performance Schema towards debuggers) about different Windows tools for MySQL DBAs (and support engineers).

Developers (and maybe even power users) on Windows probably know all I plan to describe and way more, by heart, but for me many things were not obvious and took some time to search, try or even ask for some advises... So, this series of posts is going to be useful at least for me (and mostly UNIX users, like me), as a source of hints and links that may save me some time and efforts in the future.

In this first post I plan to describe basic installation of "Debugging Tools for Windows" and use of cdb command line debugger to analyze minidumps (that one gets on Windows upon crashes when core-file option is added to my.ini and may get for hanging mysqld.exe process with minimal efforts using different tools) and get backtraces and few other details from them. I also plan to show simple command lines to share with DBAs and users whom you help, that allow to get useful details (more or less full backtraces, crash analysis, OS details etc) for further troubleshooting when/if dumps can not or should not be shared.
---
I have to confess: I use Microsoft Windows on desktops and laptops. I started from Windows 3.0 back in 1992 and ended with Windows 10 on my wife's laptop. I use Windows even for work. Today 2 of my 4 machines used for work-related tasks run Windows (64-bit XP on old Dell box I've got from MySQL AB back in 2005 and 64-bit Windows 7 on this Acer netbook). At the same time, most of work I have to do since 1992 is related to UNIX of all kinds (from Xenix and SCO OpenDesktop that I connected to from VT220 terminal in at my first job after the university, to recent Linux versions used by customers in production, my Fedora 27 box and Ubuntu 14.04 netbook used as build, Docker, VirtualBox, testing, benchmarking etc servers). I had never become a real powerful user of Windows (no really complex .bat files, PowerShell programming or even Basic macros in Word, domains, shadow copy services usage for backups, nothing fancy). But on UNIX I had to master shell, vi :), some Perl and a lot of command line tools.

I had to do some software development on Windows till 2005, built MySQL on Windows sometimes up to 2012 when I joined Percona (that had nothing to do with Windows at all), so I have old version of Visual Studio, some older WinDbg and other debugging tools here and there, but had not used them more than once a year, until recently... Last time I attached WinDbg to anything MySQL-related it was MariaDB 10.1.13, during some troubleshooting related to MDEV-10191.

Suddenly in March I've got issues from customers related to hanging upon startup/InnoDB recovery and under load, and crashing while using some (somewhat exotic) storage engine, all these - on modern versions of Microsoft Windows, in production. I had no other option but to get and study backtraces (of all threads or crashing threads) and check source code. It would be so easy to get them on Linux (just ask them to install gdb , attach it to hanging mysqld process or point out to the mysqld binary and core, and get the output of thread apply all backtrace, minor details aside). But how to do this on Winsdows, in command line if possible (as I hate to share screenshots and write long explanations on where to click and what to copy/paste)? I had to check in WinDbg, get some failures because of my outdated and incomplete environment (while customer with proper environment provided useful outputs anyway), then, eventually, asked Wlad for some help. Eventually I was able to make some progress.

To be ready to do this again next time with confidence, proper test environment and without wasting anybody else's time, I decided to repeat some of these efforts in clean environment and make notes, that I am going to share in this series of blog posts. Today I'll concentrate on installing current "Debugging Tools for Windows" and using cdb from them to process minidumps.

#### 1. Installing "Debugging Tools for Windows"

C:\Program Files (x86)\Windows Kits\10\Debuggers\x64>dir
...
11/10/2017  11:55 PM           154,936 cdb.exe
...
11/10/2017  11:55 PM           576,312 windbg.exe
...
Here is the list of most useful cdb options for the next step:
C:\Program Files (x86)\Windows Kits\10\Debuggers\x64>cdb /?
cdb version 10.0.16299.91
usage: cdb [options]

Options:

<command-line> command to run under the debugger
-? displays command line help text
...
-i <ImagePath> specifies the location of the executables that generated the
fault (see _NT_EXECUTABLE_IMAGE_PATH)
...
-lines requests that line number information be used if present
...
-logo <logfile> opens a new log file
...
-p <pid> specifies the decimal process ID to attach to
...
-pv specifies that any attach should be noninvasive
...
-y <SymbolsPath> specifies the symbol search path (see _NT_SYMBOL_PATH)
-z <CrashDmpFile> specifies the name of a crash dump file to debug
...
Environment Variables:

_NT_SYMBOL_PATH=[Drive:][Path]
Specify symbol image path.
...
Control Keys:

<Ctrl-B><Enter> Quit debugger
...
Remember Crtl-B key combination as a way to quit from cdb. I looked as funny as the beginner vi user few times, clicking on everything to get out of that tool...

#### 2. Basic Use of cdb to Process Minidump

Let's assume you've got mysqld.dmp minidump file (a kind of "core" file on UNIX, but better, at least smaller usually) created during some crash. Depending on binaries used, you may need to make sure you have .PDB files in some directory, for the mysqld.exe binary and all .dll files for plugins/extra storage engines used, in some directory. Default path to .PDB files is defined by the _NT_SYMBOL_PATH environment variable and may include multiple directories ad URLs.

Initially I've got advice to set this environment variable as follows:
This assumes that I have a collection of .PDB files in c:\symbols on some locally available server and rely on Microsoft's symbols server for the rest. For anything missing we can always add -y option to point to some directory with additional .PDB files. Note that MariaDB provides .pdb files along with .exe in .msi installer, not only in .zip file with binaries.

So, if your mysqld.dmp file is located in h:\, mysqld.exe for the same version as generated that minidump is located in p:\software and all related .dll files and .pdb files for them all are also there, the command to get basic details about the crash in file h:\out.txt would be the following:
cdb -z h:\mysqld.dmp -i p:\software -y p:\software -logo h:\out.txt -c "!sym prompts;.reload;.ecxr;q"
You can click on every option underlined above to get details. It produces output like this:
C:\Program Files (x86)\Windows Kits\10\Debuggers\x64>cdb -z h:\mysqld.dmp -i p:\
software -y p:\software -logo h:\out.txt -c "!sym prompts;.reload;.ecxr;q"

Microsoft (R) Windows Debugger Version 10.0.16299.91 AMD64

User Mini Dump File: Only registers, stack and portions of memory are available

************* Path validation summary **************
Response                         Time (ms)     Location
OK                                             p:\software

************* Path validation summary **************
Response                         Time (ms)     Location
OK                                             p:\software
Deferred                                       srv*c:\symbols*http://msdl.micros
Symbol search path is: p:\software;srv*c:\symbols*http://msdl.microsoft.com/down
Executable search path is: p:\software
Windows 10 Version 14393 MP (4 procs) Free x64
Product: Server, suite: TerminalServer SingleUserTS
10.0.14393.206 (rs1_release.160915-0644)
Machine Name:
Debug session time: ...
System Uptime: not available
Process Uptime: 0 days X:YY:ZZ.000
............................................
This dump file has an exception of interest stored in it.
The stored exception information can be accessed via .ecxr.
(1658.fd0): Access violation - code c0000005 (first/second chance not available)

00007fff804a7d84 c3              ret
quiet mode - symbol prompts on
............................................
rax=0000000000000000 rbx=0000000000000000 rcx=0000000000000006
rdx=000001cf0ac2e118 rsi=000001cf0abeeef8 rdi=000001cf0ac2e118
rip=00007fff5f313b0d rsp=000000653804e2b0 rbp=000001cf165c9cc8
r8=0000000000000000  r9=00007fff5f384448 r10=000000653804ef70
r11=000000653804eb28 r12=0000000000000000 r13=000001cf0ab49d48
r14=0000000000000000 r15=000001cf0b083028
iopl=0         nv up ei pl zr na po nc
cs=0033  ss=002b  ds=002b  es=002b  fs=0053  gs=002b             efl=00010246
ha_spider!spider_db_connect+0xdd:
00007fff5f313b0d 8b1498          mov     edx,dword ptr [rax+rbx*4] ds:00000000
00000000=????????
quit:

C:\Program Files (x86)\Windows Kits\10\Debuggers\x64>
that also goes to the file pointed out by the -logo option. Here we have some weird crash in Spider engine of MariaDB that  is not a topic of current post.

If you think the crash is related to some activity of other threads, you can get all unique stack dumps with the following options:
cdb -lines -z h:\mysqld.dmp -i p:\software -y p:\software -logo h:\out.txt -c "!sym prompts;.reload;!uniqstack -p;q"
This is how the backtrace of slave SQL thread may look like, note files with line numbers for each frame (-lines option):
. 44  Id: 1658.1584 Suspend: 0 Teb: 0000006532185000 Unfrozen
Priority: 0  Priority class: 32
00000065353fed08 00007fff8046d119 ntdll!NtWaitForAlertByThreadId+0x14
00000065353fed10 00007fff7cbd8d78 ntdll!RtlSleepConditionVariableCS+0xc9
00000065353fed80 00007ff62d7d62e7 KERNELBASE!SleepConditionVariableCS+0x28
00000065353fedb0 00007ff62d446c8e mysqld!pthread_cond_timedwait(struct _RTL_CO
NDITION_VARIABLE * cond = 0x000001ce66805688, struct _RTL_CRITICAL_SECTION * mu
tex = 0x000001ce668051b8, struct timespec * abstime = <Value unavailable error>
)+0x27 [d:\winx64-packages\build\src\mysys\my_wincond.c @ 85]
(Inline Function) ---------------- mysqld!inline_mysql_cond_wait+0x61 [d:\winx6
00000065353fede0 00007ff62d4b1718 mysqld!MYSQL_BIN_LOG::wait_for_update_relay_
log(class THD * thd = <Value unavailable error>)+0xce [d:\winx64-packages\build\
src\sql\log.cc @ 8055]
00000065353fee90 00007ff62d4af03f mysqld!next_event(struct rpl_group_info * rg
i = 0x000001ce667fe560, unsigned int64 * event_size = 0x00000065353ff008)+0x2b
8 [d:\winx64-packages\build\src\sql\slave.cc @ 7148]
00000065353fef60 00007ff62d4bb038 mysqld!exec_relay_log_event(class THD * thd
= 0x000001ce6682ece8, class Relay_log_info * rli = 0x000001ce66804d58, struct
rpl_group_info * serial_rgi = 0x000001ce667fe560)+0x8f [d:\winx64-packages\buil
d\src\sql\slave.cc @ 3866
]
00000065353ff000 00007ff62d7d35cb mysqld!handle_slave_sql(void * arg = 0x00000
1ce66803430)+0xa28 [d:\winx64-packages\build\src\sql\slave.cc @ 5145]
00000065353ff780 00007ff62d852d51 mysqld!pthread_start(void * p = <Value unava
(Inline Function) ---------------- mysqld!invoke_thread_procedure+0xe [d:\th\mi
00000065353ff7b0 00007fff80338364 mysqld!thread_start<unsigned int (void * par
ameter = 0x0000000000000000)+0x5d [d:\th\minkernel\crts\ucrt\src\appcrt\startup
00000065353ff7e0 00007fff804670d1 kernel32!BaseThreadInitThunk+0x14
00000065353ff810 0000000000000000 ntdll!RtlUserThreadStart+0x21
For crash analysis usually !analyze command is also used:
cdb -lines -z h:\mysqld.dmp -i p:\software -y p:\software -logo h:\out.txt -c "!sym prompts;.reload;!analyze -v;q"
It may give some details about the exception happened:
...
FAULTING_IP:
ha_spider!spider_db_connect+dd00007fff5f313b0d 8b1498          mov     edx,dword ptr [rax+rbx*4]

EXCEPTION_RECORD:  (.exr -1)
dd)
ExceptionCode: c0000005 (Access violation)
ExceptionFlags: 00000000
NumberParameters: 2
Parameter[0]: 0000000000000000
Parameter[1]: 0000000000000000

PROCESS_NAME:  mysqld.exe

ERROR_CODE: (NTSTATUS) 0xc0000005 - <Unable to get error code text>
...
STACK_TEXT:
000000653804e2b0 00007fff5f3132ad : 0000000000000000 000001cf1741ab68 000001
cf0b083028 000001cf0ac2e118 : ha_spider!spider_db_connect+0xdd
000000653804e330 00007fff5f3117f8 : 000001ce669107c8 000001cf0ac2e118 000001
cf1741ab68 0000000000000001 : ha_spider!spider_db_conn_queue_action+0xad
000000653804ea20 00007fff5f31a1ee : 0000000000000000 000001cf0abeeef8 000000
0000000000 000001cdc0b30000 : ha_spider!spider_db_before_query+0x108
000000653804eaa0 00007fff5f31a0bf : 0000000000000000 0000000000000000 000000
653804ec70 0000000000000038 : ha_spider!spider_db_set_names_internal+0x11e
000000653804eb30 00007fff5f369b4e : 0000000000000000 000000653804ec70 00007f
ff5f387f08 0000000000000000 : ha_spider!spider_db_set_names+0x3f
000000653804eb70 00007fff5f32f5f1 : 0000000000000001 0000006500000000 41cfff
ff00000001 0000000000000001 : ha_spider!spider_mysql_handler::show_table_statu
s+0x15e
000000653804ece0 00007fff5f3222e8 : 0000000000000001 0000006500000000 000000
005ab175cd 000001cf0b0886f8 : ha_spider!spider_get_sts+0x201
000000653804edb0 00007ff62d7d35cb : 0000000000000057 000001cf0ab49d48 000000
0000000000 00007fff5f321c10 : ha_spider!spider_bg_sts_action+0x6d8
000000653804fa30 00007ff62d852d51 : 000001cf17008fe0 000001cf0aa3fef0 000000
0000000000 0000000000000000 : mysqld!pthread_start+0x1b
000000653804fa60 00007fff80338364 : 0000000000000000 0000000000000000 000000
0000000000 0000000000000000 : mysqld!thread_start<unsigned int (__cdecl*)(void
* __ptr64)>+0x5d
000000653804fa90 00007fff804670d1 : 0000000000000000 0000000000000000 000000
0000000000 0000000000000000 : kernel32!BaseThreadInitThunk+0x14
000000653804fac0 0000000000000000 : 0000000000000000 0000000000000000 000000
0000000000 0000000000000000 : ntdll!RtlUserThreadStart+0x21
...
Finally (for this post), this is how we can get information about a crashing thread, including details about local variables (like full backtrace in gdb). We apply !for_each_frame extension and use dv to "display variable":
cdb -z h:\mysqld.dmp -i p:\software -y p:\software -logo h:\out.txt -c "!sym prompts;.reload;.ecxr;!for_each_frame dv /t;q"
The result will include details about each frame, parameters and local variables, like this:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
00 000000653804e2b0 00007fff5f3132ad ha_spider!spider_db_connect+0xdd
struct st_spider_share * share = 0x000001cf165c9cc8
struct st_spider_conn * conn = 0x000001cf0ac2e118
int error_num = <value unavailable>
class THD * thd = 0x000001cf0abeeef8
int64 connect_retry_interval = <value unavailable>
int connect_retry_count = <value unavailable>
int64 tmp_time = <value unavailable>
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
01 000000653804e330 00007fff5f3117f8 ha_spider!spider_db_conn_queue_action+0xa
d
struct st_spider_conn * conn = 0x000001cf0ac2e118
int error_num = 0n0
char [1532] sql_buf = char [1532] ""
class spider_string sql_str = class spider_string
class spider_db_result * result = <value unavailable>
struct st_spider_db_request_key request_key = struct st_spider_db_request_key
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
02 000000653804ea20 00007fff5f31a1ee ha_spider!spider_db_before_query+0x108
struct st_spider_conn * conn = 0x000001cf0ac2e118
int * need_mon = 0x000001cf1741ab68
int error_num = 0n0
class ha_spider * spider = <value unavailable>
bool tmp_mta_conn_mutex_unlock_later = <value unavailable>
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
03 000000653804eaa0 00007fff5f31a0bf ha_spider!spider_db_set_names_internal+0x
11e
struct st_spider_transaction * trx = 0x000001cf0b083028
struct st_spider_share * share = 0x000001cf0ab49d48
struct st_spider_conn * conn = 0x000001cf0ac2e118
int * need_mon = 0x000001cf1741ab68
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
04 000000653804eb30 00007fff5f369b4e ha_spider!spider_db_set_names+0x3f
class ha_spider * spider = <value unavailable>
struct st_spider_conn * conn = <value unavailable>
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
05 000000653804eb70 00007fff5f32f5f1 ha_spider!spider_mysql_handler::show_tabl
e_status+0x15e
class spider_mysql_handler * this = 0x000001cf0a15dd00
int sts_mode = 0n1
unsigned int flag = 1
int error_num = 0n1
struct st_spider_share * share = 0x000001cf0ab49d48
struct st_spider_conn * conn = 0x000001cf0ac2e118
class spider_db_result * res = <value unavailable>
unsigned int64 auto_increment_value = 0
unsigned int pos = 0
struct st_spider_db_request_key request_key = struct st_spider_db_request_key
struct st_spider_db_request_key request_key = struct st_spider_db_request_key
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
06 000000653804ece0 00007fff5f3222e8 ha_spider!spider_get_sts+0x201
struct st_spider_share * share = 0x000001cf0ab49d48
int64 tmp_time = 0n1521579469
class ha_spider * spider = 0x000000653804ef70
double sts_interval = 10
int sts_mode = 0n1
int sts_sync = 0n0
int sts_sync_level = 0n2
unsigned int flag = 0x18
int error_num = <value unavailable>
int get_type = 0n1
struct st_spider_patition_handler_share * partition_handler_share = <value unava
ilable>
double tmp_sts_interval = <value unavailable>
struct st_spider_share * tmp_share = <value unavailable>
int tmp_sts_sync = <value unavailable>
class ha_spider * tmp_spider = <value unavailable>
int roop_count = <value unavailable>
int tmp_sts_mode = <value unavailable>
class THD * thd = <value unavailable>
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
07 000000653804edb0 00007ff62d7d35cb ha_spider!spider_bg_sts_action+0x6d8
void * arg = 0x000001cf0ab49d48
int error_num = 0n0
class ha_spider spider = class ha_spider
unsigned int * conn_link_idx = 0x000001cf1741ab78
unsigned char * conn_can_fo = 0x000001cf1741ab80 "--- memory read error at addr
ess 0x000001cf1741ab80 ---"
struct st_spider_conn ** conns = 0x000001cf1741ab70
int * need_mons = 0x000001cf1741ab68
int roop_count = 0n0
char ** conn_keys = 0x000001cf1741ab88
class THD * thd = 0x000001cf0abeeef8
class spider_db_handler ** dbton_hdl = 0x000001cf1741ab90
struct st_spider_transaction * trx = 0x000001cf0b083028
struct st_mysql_mutex spider_global_trx_mutex = <value unavailable>
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
08 000000653804fa30 00007ff62d852d51 mysqld!pthread_start+0x1b
void * p = <value unavailable>
void * arg = 0x000001cf0ab49d48
<function> * func = 0x00007fff5f321c10
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
09 (Inline Function) ---------------- mysqld!invoke_thread_procedure+0xe
void * context = 0x000001cf17008fe0
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
0a 000000653804fa60 00007fff80338364 mysqld!thread_start<unsigned int (__cdecl
*)(void * __ptr64)>+0x5d
void * parameter = 0x0000000000000000
<function> * procedure = 0x00007ff62d7d35b0
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
0b 000000653804fa90 00007fff804670d1 kernel32!BaseThreadInitThunk+0x14
Unable to enumerate locals, Win32 error 0n87
Private symbols (symbols.pri) are required for locals.
Type ".hh dbgerr005" for details.
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
0c 000000653804fac0 0000000000000000 ntdll!RtlUserThreadStart+0x21
Unable to enumerate locals, Win32 error 0n87
Private symbols (symbols.pri) are required for locals.
Type ".hh dbgerr005" for details.
---

Stay tuned. I keep working on complex MySQL/MariaDB problems under Windows, so soon will have few more findings and links to share.