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-François Gagné 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.

As usual, I start with most recent bug reports:
  • 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)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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)
So, you can build MySQL 8.0.11 right now and start using it to make your own conclusions about this release.

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-François Gagné 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-François Gagné. 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-François Gagné 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.

Sunday, March 25, 2018

Windows Tools for MySQL DBAs: Basic Minidump Analysis

"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"

There is a nice, easy to find document from Microsoft on how to get cdb and other debugging tools for Windows. For recent versions you just have to download Windows 10 SDK and then install these tools (and everything else you may need) from it. Proceed to this page, read the details, click on "Download .EXE" to get winsdksetup.exe , start it and select "Debugging Tools for Windows" when requested to select the features. Eventually you'll get some 416+ MB downloaded and installed by default in C:\Program Files (x86)\Windows Kits\10\Debuggers\. (on default 64-bit Windows installation with C: as system disk). Quick check shows I have everything I need:
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:
set _NT_SYMBOL_PATH=srv*c:\symbols*http://msdl.microsoft.com/download/symbols
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
Copyright (c) Microsoft Corporation. All rights reserved.


Loading Dump File [h:\mysqld.dmp]
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
oft.com/download/symbols
Symbol search path is: p:\software;srv*c:\symbols*http://msdl.microsoft.com/down
load/symbols
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)

ntdll!NtGetContextThread+0x14:
00007fff`804a7d84 c3              ret
0:053> cdb: Reading initial command '!sym prompts;.reload;.ecxr;q'
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:
00007fff`5f313b0d 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: 00000065`32185000 Unfrozen
      Priority: 0  Priority class: 32
Child-SP          RetAddr           Call Site
00000065`353fed08 00007fff`8046d119 ntdll!NtWaitForAlertByThreadId+0x14
00000065`353fed10 00007fff`7cbd8d78 ntdll!RtlSleepConditionVariableCS+0xc9
00000065`353fed80 00007ff6`2d7d62e7 KERNELBASE!SleepConditionVariableCS+0x28
00000065`353fedb0 00007ff6`2d446c8e mysqld!pthread_cond_timedwait(struct _RTL_CO
NDITION_VARIABLE * cond = 0x000001ce`66805688, struct _RTL_CRITICAL_SECTION * mu
tex = 0x000001ce`668051b8, 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
4-packages\build\src\include\mysql\psi\mysql_thread.h @ 1149]
00000065`353fede0 00007ff6`2d4b1718 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]
00000065`353fee90 00007ff6`2d4af03f mysqld!next_event(struct rpl_group_info * rg
i = 0x000001ce`667fe560, unsigned int64 * event_size = 0x00000065`353ff008)+0x2b
8 [d:\winx64-packages\build\src\sql\slave.cc @ 7148]
00000065`353fef60 00007ff6`2d4bb038 mysqld!exec_relay_log_event(class THD * thd
= 0x000001ce`6682ece8, class Relay_log_info * rli = 0x000001ce`66804d58, struct
rpl_group_info * serial_rgi = 0x000001ce`667fe560)+0x8f [d:\winx64-packages\buil
d\src\sql\slave.cc @ 3866
]
00000065`353ff000 00007ff6`2d7d35cb mysqld!handle_slave_sql(void * arg = 0x00000
1ce`66803430)+0xa28 [d:\winx64-packages\build\src\sql\slave.cc @ 5145]
00000065`353ff780 00007ff6`2d852d51 mysqld!pthread_start(void * p = <Value unava
ilable error>)+0x1b [d:\winx64-packages\build\src\mysys\my_winthread.c @ 62]
(Inline Function) --------`-------- mysqld!invoke_thread_procedure+0xe [d:\th\mi
nkernel\crts\ucrt\src\appcrt\startup\thread.cpp @ 91]
00000065`353ff7b0 00007fff`80338364 mysqld!thread_start<unsigned int (void * par
ameter = 0x00000000`00000000)+0x5d [d:\th\minkernel\crts\ucrt\src\appcrt\startup
\thread.cpp @ 115]
00000065`353ff7e0 00007fff`804670d1 kernel32!BaseThreadInitThunk+0x14
00000065`353ff810 00000000`00000000 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+dd00007fff`5f313b0d 8b1498          mov     edx,dword ptr [rax+rbx*4]

EXCEPTION_RECORD:  (.exr -1)
ExceptionAddress: 00007fff5f313b0d (ha_spider!spider_db_connect+0x00000000000000
dd)
   ExceptionCode: c0000005 (Access violation)
  ExceptionFlags: 00000000
NumberParameters: 2
   Parameter[0]: 0000000000000000
   Parameter[1]: 0000000000000000
Attempt to read from address 0000000000000000
DEFAULT_BUCKET_ID:  NULL_POINTER_READ

PROCESS_NAME:  mysqld.exe

ERROR_CODE: (NTSTATUS) 0xc0000005 - <Unable to get error code text>
...
STACK_TEXT:
00000065`3804e2b0 00007fff`5f3132ad : 00000000`00000000 000001cf`1741ab68 000001
cf`0b083028 000001cf`0ac2e118 : ha_spider!spider_db_connect+0xdd
00000065`3804e330 00007fff`5f3117f8 : 000001ce`669107c8 000001cf`0ac2e118 000001
cf`1741ab68 00000000`00000001 : ha_spider!spider_db_conn_queue_action+0xad
00000065`3804ea20 00007fff`5f31a1ee : 00000000`00000000 000001cf`0abeeef8 000000
00`00000000 000001cd`c0b30000 : ha_spider!spider_db_before_query+0x108
00000065`3804eaa0 00007fff`5f31a0bf : 00000000`00000000 00000000`00000000 000000
65`3804ec70 00000000`00000038 : ha_spider!spider_db_set_names_internal+0x11e
00000065`3804eb30 00007fff`5f369b4e : 00000000`00000000 00000065`3804ec70 00007f
ff`5f387f08 00000000`00000000 : ha_spider!spider_db_set_names+0x3f
00000065`3804eb70 00007fff`5f32f5f1 : 00000000`00000001 00000065`00000000 41cfff
ff`00000001 00000000`00000001 : ha_spider!spider_mysql_handler::show_table_statu
s+0x15e
00000065`3804ece0 00007fff`5f3222e8 : 00000000`00000001 00000065`00000000 000000
00`5ab175cd 000001cf`0b0886f8 : ha_spider!spider_get_sts+0x201
00000065`3804edb0 00007ff6`2d7d35cb : 00000000`00000057 000001cf`0ab49d48 000000
00`00000000 00007fff`5f321c10 : ha_spider!spider_bg_sts_action+0x6d8
00000065`3804fa30 00007ff6`2d852d51 : 000001cf`17008fe0 000001cf`0aa3fef0 000000
00`00000000 00000000`00000000 : mysqld!pthread_start+0x1b
00000065`3804fa60 00007fff`80338364 : 00000000`00000000 00000000`00000000 000000
00`00000000 00000000`00000000 : mysqld!thread_start<unsigned int (__cdecl*)(void
 * __ptr64)>+0x5d
00000065`3804fa90 00007fff`804670d1 : 00000000`00000000 00000000`00000000 000000
00`00000000 00000000`00000000 : kernel32!BaseThreadInitThunk+0x14
00000065`3804fac0 00000000`00000000 : 00000000`00000000 00000000`00000000 000000
00`00000000 00000000`00000000 : 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 00000065`3804e2b0 00007fff`5f3132ad ha_spider!spider_db_connect+0xdd
struct st_spider_share * share = 0x000001cf`165c9cc8
struct st_spider_conn * conn = 0x000001cf`0ac2e118
int link_idx = 0n0
int error_num = <value unavailable>
class THD * thd = 0x000001cf`0abeeef8
int64 connect_retry_interval = <value unavailable>
int connect_retry_count = <value unavailable>
int64 tmp_time = <value unavailable>
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
01 00000065`3804e330 00007fff`5f3117f8 ha_spider!spider_db_conn_queue_action+0xa
d
struct st_spider_conn * conn = 0x000001cf`0ac2e118
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 00000065`3804ea20 00007fff`5f31a1ee ha_spider!spider_db_before_query+0x108
struct st_spider_conn * conn = 0x000001cf`0ac2e118
int * need_mon = 0x000001cf`1741ab68
int error_num = 0n0
bool tmp_mta_conn_mutex_lock_already = true
class ha_spider * spider = <value unavailable>
bool tmp_mta_conn_mutex_unlock_later = <value unavailable>
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
03 00000065`3804eaa0 00007fff`5f31a0bf ha_spider!spider_db_set_names_internal+0x
11e
struct st_spider_transaction * trx = 0x000001cf`0b083028
struct st_spider_share * share = 0x000001cf`0ab49d48
struct st_spider_conn * conn = 0x000001cf`0ac2e118
int all_link_idx = 0n0
int * need_mon = 0x000001cf`1741ab68
bool tmp_mta_conn_mutex_lock_already = true
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
04 00000065`3804eb30 00007fff`5f369b4e ha_spider!spider_db_set_names+0x3f
class ha_spider * spider = <value unavailable>
struct st_spider_conn * conn = <value unavailable>
int link_idx = <value unavailable>
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
05 00000065`3804eb70 00007fff`5f32f5f1 ha_spider!spider_mysql_handler::show_tabl
e_status+0x15e
class spider_mysql_handler * this = 0x000001cf`0a15dd00
int link_idx = 0n0
int sts_mode = 0n1
unsigned int flag = 1
int error_num = 0n1
struct st_spider_share * share = 0x000001cf`0ab49d48
struct st_spider_conn * conn = 0x000001cf`0ac2e118
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 00000065`3804ece0 00007fff`5f3222e8 ha_spider!spider_get_sts+0x201
struct st_spider_share * share = 0x000001cf`0ab49d48
int link_idx = 0n0
int64 tmp_time = 0n1521579469
class ha_spider * spider = 0x00000065`3804ef70
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 00000065`3804edb0 00007ff6`2d7d35cb ha_spider!spider_bg_sts_action+0x6d8
void * arg = 0x000001cf`0ab49d48
int error_num = 0n0
class ha_spider spider = class ha_spider
unsigned int * conn_link_idx = 0x000001cf`1741ab78
unsigned char * conn_can_fo = 0x000001cf`1741ab80 "--- memory read error at addr
ess 0x000001cf`1741ab80 ---"
struct st_spider_conn ** conns = 0x000001cf`1741ab70
int * need_mons = 0x000001cf`1741ab68
int roop_count = 0n0
char ** conn_keys = 0x000001cf`1741ab88
class THD * thd = 0x000001cf`0abeeef8
class spider_db_handler ** dbton_hdl = 0x000001cf`1741ab90
struct st_spider_transaction * trx = 0x000001cf`0b083028
struct st_mysql_mutex spider_global_trx_mutex = <value unavailable>
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
08 00000065`3804fa30 00007ff6`2d852d51 mysqld!pthread_start+0x1b
void * p = <value unavailable>
void * arg = 0x000001cf`0ab49d48
<function> * func = 0x00007fff`5f321c10
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
09 (Inline Function) --------`-------- mysqld!invoke_thread_procedure+0xe
void * context = 0x000001cf`17008fe0
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
0a 00000065`3804fa60 00007fff`80338364 mysqld!thread_start<unsigned int (__cdecl
*)(void * __ptr64)>+0x5d
void * parameter = 0x00000000`00000000
<function> * procedure = 0x00007ff6`2d7d35b0
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
0b 00000065`3804fa90 00007fff`804670d1 kernel32!BaseThreadInitThunk+0x14
Unable to enumerate locals, Win32 error 0n87
Private symbols (symbols.pri) are required for locals.
Type ".hh dbgerr005" for details.
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
0c 00000065`3804fac0 00000000`00000000 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.