1

MySQL 8.0.24 on Windows 10 (10.0.18363.0) crashes near the same point when running a final results query, but only after data input queries with many more records have been run.

A restart of the MySQL service does not resolve the crash, and it's repeatable. After a full reboot of Windows (via shutdown /r /t 1, to ensure a hard reboot) no crash; the final results query runs and completes in 4 minutes.

I suspect Windows memory resources are consumed or corrupted by my large initial data load process are not restored until reboot, but the error only manifests its presence when I run a downstream query.

What could be getting corrupted in Windows by MySQL that would be restored with a reboot? The details below are specific to this instance, but my question is general:

  1. Do large (6Gb) load into Windows/mySQL using new parallel tools
  2. Attempt simple query on results, crash mySQL
  3. Same simple query succeeds only after Windows reboot, mySQL restart does not fix. How can I avoid breaking Windows mySQL with large data loads where the only (and reliably repeatable) fix is a hard reboot?Question is not 'require reboot', but why crashing on large queries?

Details

Much of the detail following below turns out to be irrelevant, see accepted answer for explanation.

The data loading queries run before "final result" are using the mysqhsh util.importTableutility (introduced 8.0.17), an enhanced shell command that invokes LOAD DATA INFILE across parallel threads. This cuts the data load time from a couple of hours to about 50 minutes. I have 6Gb of data, most of it in two tables with 1-2 million rows each. Data is loaded with PRIMARY KEY in the parallel utility, then indexed with ALTER TABLE ADD INDEX.
All of that runs without any error, consistently, with data sets that change and grow slightly daily.
The results query that fails takes values from other tables created during the data loading and post-processing, then UNIONs them to have a data header, and writes out the result to a couple of files. The two queries yield < 100K records and usually many fewer, as they're deltas. That's all it does. All the complexity and big joins are in the data load and process .sql scripts, and all of those run without error but appear to be corrupting Windows. I have tried various settings on the InnoDB family, including different number of buffer pool extensions, hash index on/off, innodb_flush_neighbors, innodb_lru_scan_depth, etc. These do have an effect on performance, but do not affect this problem. During initial data load, I set ALTER INSTANCE DISABLE INNODB REDO_LOG (new as of 8.0.21) to speed up initial data loading, but restore ALTER INSTANCE ENABLE INNODB REDO_LOG before running the query that fails. Off-topic note: If you use this don't forget to re-enable, as any system glitch or crash that affects MySQL will result in a corrupted instance. BTDT. Only use during initial input, as recommended. I have not yet tried running the input process leaving REDO LOG enabled. That's my next test. --- No triggers or stored procures are set up in this schema, and nothing else is running. No triggers at all in this MySQL instance, actually.
### Log and .ini settings The error log (.err) from the crash is below. The first three lines are warnings during input, where I am toggling the redo logging (note the times are spread out), before any trouble. The last of those is an ENABLE (as it should be). Next line SQL has crashed and mySQL80 service is not running. At 2021-05-01T23:17:49 I have restarted the mySQL80 service manually: none ! 2021-05-01T22:20:43.437169Z 1905 [Warning] [MY-013601] [InnoDB] InnoDB redo logging is enabled. Data is now safe and can be recovered in case of a server crash. ! 2021-05-01T22:35:52.244860Z 1966 [Warning] [MY-013600] [InnoDB] InnoDB redo logging is disabled. All data could be lost in case of a server crash. ! 2021-05-01T23:03:05.731314Z 1966 [Warning] [MY-013601] [InnoDB] InnoDB redo logging is enabled. Data is now safe and can be recovered in case of a server crash. ! 23:14:04 UTC - mysqld got exception 0xc0000005 ; ! Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware. ! Thread pointer: 0x280d9a167e0 ! Attempting backtrace. You can use the following information to find out ! where mysqld died. If you see no messages after this, something went ! terribly wrong... ! 7ff686461930 mysqld.exe!?set_compression_level@Zstd_comp@compression@transaction@binary_log@@UEAAXI@Z() ! 7ff68646104a mysqld.exe!?set_compression_level@Zstd_comp@compression@transaction@binary_log@@UEAAXI@Z() ! 7ff684fa32d7 mysqld.exe!?ha_rnd_next@handler@@QEAAHPEAE@Z() ! 7ff685295e71 mysqld.exe!?Read@TableScanIterator@@UEAAHXZ() ! 7ff6854616f7 mysqld.exe!?Read@FilterIterator@@UEAAHXZ() ! 7ff685461916 mysqld.exe!?Read@NestedLoopIterator@@UEAAHXZ() ! 7ff685461916 mysqld.exe!?Read@NestedLoopIterator@@UEAAHXZ() ! 7ff685461916 mysqld.exe!?Read@NestedLoopIterator@@UEAAHXZ() ! 7ff685461916 mysqld.exe!?Read@NestedLoopIterator@@UEAAHXZ() ! 7ff685461916 mysqld.exe!?Read@NestedLoopIterator@@UEAAHXZ() ! 7ff685461757 mysqld.exe!?Read@FilterIterator@@UEAAHXZ() ! 7ff685461916 mysqld.exe!?Read@NestedLoopIterator@@UEAAHXZ() ! 7ff685461916 mysqld.exe!?Read@NestedLoopIterator@@UEAAHXZ() ! 7ff685461916 mysqld.exe!?Read@NestedLoopIterator@@UEAAHXZ() ! 7ff685461916 mysqld.exe!?Read@NestedLoopIterator@@UEAAHXZ() ! 7ff685460b99 mysqld.exe!?MaterializeQueryBlock@MaterializeIterator@@AEAA_NAEBUQueryBlock@1@PEA_K@Z() ! 7ff685460118 mysqld.exe!?Init@MaterializeIterator@@UEAA_NXZ() ! 7ff685304938 mysqld.exe!?filesort@@YA_NPEAVTHD@@PEAVFilesort@@PEAVRowIterator@@_K3PEAVFilesort_info@@PEAVSort_result@@PEA_K@Z() ! 7ff6854302ae mysqld.exe!?DoSort@SortingIterator@@AEAAHXZ() ! 7ff68543063a mysqld.exe!?Init@SortingIterator@@UEAA_NXZ() ! 7ff68545fcb4 mysqld.exe!?Init@AggregateIterator@@UEAA_NXZ() ! 7ff685460b5a mysqld.exe!?MaterializeQueryBlock@MaterializeIterator@@AEAA_NAEBUQueryBlock@1@PEA_K@Z() ! 7ff685460118 mysqld.exe!?Init@MaterializeIterator@@UEAA_NXZ() ! 7ff685460b5a mysqld.exe!?MaterializeQueryBlock@MaterializeIterator@@AEAA_NAEBUQueryBlock@1@PEA_K@Z() ! 7ff685460118 mysqld.exe!?Init@MaterializeIterator@@UEAA_NXZ() ! 7ff6852f99be mysqld.exe!?ExecuteIteratorQuery@Query_expression@@QEAA_NPEAVTHD@@@Z() ! 7ff6852fb0d6 mysqld.exe!?execute@Query_expression@@QEAA_NPEAVTHD@@@Z() ! 7ff68543a51d mysqld.exe!?execute@Sql_cmd_load_index@@UEAA_NPEAVTHD@@@Z() ! 7ff68543a1ca mysqld.exe!?execute@Sql_cmd_create_table@@UEAA_NPEAVTHD@@@Z() ! 7ff685179a71 mysqld.exe!?mysql_execute_command@@YAHPEAVTHD@@_N@Z() ! 7ff685174c2f mysqld.exe!?dispatch_sql_command@@YAXPEAVTHD@@PEAVParser_state@@@Z() ! 7ff685173a4c mysqld.exe!?dispatch_command@@YA_NPEAVTHD@@PEBTCOM_DATA@@W4enum_server_command@@@Z() ! 7ff68517500e mysqld.exe!?do_command@@YA_NPEAVTHD@@@Z() ! 7ff684fc64a8 mysqld.exe!?modify_thread_cache_size@Per_thread_connection_handler@@SAXK@Z() ! 7ff6863ddbe1 mysqld.exe!?set_compression_level@Zstd_comp@compression@transaction@binary_log@@UEAAXI@Z() ! 7ff685fc1d1c mysqld.exe!?my_thread_join@@YAHPEAUmy_thread_handle@@PEAPEAX@Z() ! 7ffb255d10b2 ucrtbase.dll!_beginthreadex() ! 7ffb280d7c24 KERNEL32.DLL!BaseThreadInitThunk() ! 7ffb286ad721 ntdll.dll!RtlUserThreadStart() ! ! Trying to get some variables. ! Some pointers may be invalid and cause the dump to abort. ! Query (280e085ec78): CREATE TABLE `active_fan_export` ! SELECT ! `2021 Web Fan Acct Name`, `fan_profile_acct_name` , `Fan Profile`, `fan_id`, `account_type`, `Account Name` ! , `Account ID`, `Agree Name`, `agree_id`, `postal_code`, `state`, `svid` ! ,`>1 yr`,`GA`,`f enroll`,`Discount`,`oe name`,`disc value` ! ,`mobility enterprise type` ! ,`email domain count`,`email domains` ! FROM ! ( ! SELECT ! -1 as `Rank` /******************** verbatim headers ********************************************************************/ ! ,'web_fan_acct_name' `2021 Web Fan Acct Name`,'fan_profile_acct_name' `fan_profile_acct_name` ,'Fan Profile' `Fan Profile` ! ,'fan_id' `fan_id`,'account_type' `account_type`, 'Account Name' `Account Name` ! ! ,'Account ID' `Account ID`, 'Agree Name' `Agree Name`,'agree_id' `agree_id`,'postal_code' `postal_code` ! ,'state' `state`,'svid' `svid` ! ! ,'>1 yr' `>1 yr`,'GA' `GA`,'f enroll' `f enroll`,'Discount' `Discount`,'oe name' `oe name`,'disc value' `disc value` ! ,'mobility enterprise type' `mobility enterprise ty ! Connection ID (thread ID): 1141 ! Status: NOT_KILLED ! ! The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains ! information that should help you find out what is causing the crash. ! 2021-05-01T23:17:49.000709Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release. !
Also here's my .ini with various parameters. This is on a 512GB SSD, 16 GB RAM Dell Precision 3551 laptop: https://pastebin.com/Kz0y3zFD This reminds me of the bad old days of Windows blue screens and inexplicable crashes. Windows 10 is much improved but even with 16 GB it's still possible to permanently exhaust resources (for example: open too many concurrent applications or windows, and things still break and require a reboot). I think I may be pushing those same limits with the parallel threads, giant memory buffers, etc. Things I will try as time permits: - reduce innodb buffer size - mess with numbers of parititons - use fewer than 11 threads (Intel i7-10850H supports 12 on 6 cores) - turn on REDO LOG during load - enable double-write buffering - run load and final queries with Office 365, chat, etc. shut down - try on Ubuntu or RHEL host - other ideas from answers/comments here --- ### More information ["dev.mysql.com/doc/mysql/en/crashing.html"][1] has the following suggestions from mySQL's official documentation. I will respond to each in italics: * The MySQL server or the server host was killed in the middle of an update. Updates not applied; generally avoid killing server during update * You have found a bug in mysqld that caused it to die in the middle of an update. Possible * Some external program is manipulating data files or index files at the same time as mysqld without locking the table properly.No, this is a desktop and only other processes running are Office and a chat client * You are running many mysqld servers using the same data directory on a system that does not support good file system locks (normally handled by the lockd lock manager), or you are running multiple servers with external locking disabled.Using stock installation on NTFS volume with file locks, not running second server anywhere, no replication ever * You have a crashed data file or index file that contains very corrupt data that confused mysqld.Yes, crash = confused * You have found a bug in the data storage code. This isn't likely, but it is at least possible. In this case, you can try to change the storage engine to another engine by using ALTER TABLE on a repaired copy of the table. Possible, but wondering if the "bug" is in Windows memory management and not mySQL * Start mysqld with the general query log enabled (see Section 5.4.3, “The General Query Log”). To do: will try General Query Log, other suggestions though some are specific to Linux host --- To elaborate on "effect on performance", for example, @@innodb_flush_neighbors set to 0 instead of 2 reduced the time spent by the 11 threads in "waiting for handler commit" status, as observed in Workbench. The effect of that change on the actual time was minimal, about 45 seconds off of a 12 minute load time. I tried COMPRESSED tables on this data and it slowed down input by about half. The source table for the query that fails is !CREATE TABLE `active_fan_export` ( ! `2021 Web Fan Acct Name` varchar(255) NOT NULL DEFAULT '', ! `fan_profile_acct_name` longtext NOT NULL, ! `Fan Profile` longtext NOT NULL, ! `fan_id` varchar(30) DEFAULT NULL, ! `account_type` varchar(12) DEFAULT NULL, ! `Account Name` longtext, ! `Account ID` varchar(15) DEFAULT NULL, ! `Agree Name` longtext, ! `agree_id` varchar(30) DEFAULT NULL, ! `postal_code` varchar(30) NOT NULL DEFAULT '', ! `state` varchar(10) NOT NULL DEFAULT '', ! `svid` varchar(30) NOT NULL DEFAULT '', ! `>1 yr` varchar(5) CHARACTER SET cp850 NOT NULL DEFAULT '', ! `GA` varchar(2) CHARACTER SET cp850 NOT NULL DEFAULT '', ! `f enroll` varchar(8) CHARACTER SET cp850 NOT NULL DEFAULT '', ! `Discount` varchar(15) NOT NULL DEFAULT '', ! `oe name` varchar(255) NOT NULL DEFAULT '', ! `disc value` varchar(15) NOT NULL DEFAULT '', ! `mobility enterprise type` varchar(30) NOT NULL DEFAULT '', ! `email domain count` varchar(21) CHARACTER SET cp850 NOT NULL DEFAULT '', ! `email domains` varchar(200) NOT NULL DEFAULT '', ! KEY `fan_id` (`fan_id`), ! KEY `agree_id` (`agree_id`), ! KEY `Account ID` (`Account ID`), ! KEY `mobility enterprise type` (`mobility enterprise type`) !) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; ! The cp850 is a surprise; I have no idea where that came from. I need to go back through the logic to the initial data load.
The importTable multi-thread processing is I/O bound unless indexes are defined. With indexing included in table definition, the Mb/S on import drops from about 15 to 1, and the 20 minute load time goes up to about 30. Indexing post-load takes 3 min 30 sec, so I do not include the indexes on these tables when I create them. Even with 12 threads, the CPUs are all in the 50% range and GUI response (browsers, Outlook, etc.) is barely affected. The index creation is CPU intensive. It does not use multiple CPUs, but it does bog down response in the GUI (browsers, Outlook, etc.) A note about I/O intensive: This db formerly ran on an HP Z-book with an i7-6820HQ CPU, with 4 fewer threads and slower benchmarks. The Z-book came with a SATA M.2 drive and an empty M.2 nVME slot. I added a WD750 gamer nVME card and moved the mySQL installation and data to it. That made a 30% speed improvement on loading the tables. The replacement Dell has only one M.2 slot and the corporate image is on it and can't be touched. That drive is an nVME KIOXIA 512 GB with benchmarks at about 75% of the WD Black's numbers. Even with a faster CPU and 2019 mobo tech vs 2016, the slightly slower nVME drive yields load times 5-15% greater for the 6Gb data. [1]: https://dev.mysql.com/doc/refman/8.0/en/crashing.html

3
  • I have refined the general question, the details are for the curious but not necessarily applicable to what's asked in the top section (1) large data input (2) mysql crashes on smaller subsequent query (3) windows Reboot fixes, mySQL restart does not
    – wistlo
    May 6 at 19:02
  • Your column names include a space character such as Agree Name and >1 yr, Please review this URL content and reconsider your column names. anandarajpandey.com/2015/05/10/… - Also, I notice you have no PRIMARY key for your innodb table named active_fan_export - innodb can reduce overhead when you have a primary key. Data space required for index storage can be reduced when you have a Primary Key. May 9 at 14:29
  • @Wilson Hauck: The output goes to users who require those column names. I could change the headers internally to get rid of spaces and non-standard characters, but I'm trying to keep the code readable for the end user who verifies the logic. I did ultimately predefine all tables with primary keys (rather than just create by CREATE/SELECT)
    – wistlo
    May 17 at 13:42
1

Consider these my.ini [mysqld] section changes

innodb_thread_concurrency=0  # from 25 to allow Windows autosizing
thread_cache=size=10  # from 30 for your 6 core cpu to leave 2 for other purposes.

The error log appears to be having trouble with thread management.

2
  • Good news, bad news: Bad news is this did not solve the crashes. Good news is that the innodb_thread_concurrency-0 setting greatly smoothed out data writing to the disk. Writes observed in the the Workbench Dashboard jumped from intermittent bursts of 2-10Mbs to a steady 40-50 Mbs, which approaches the SSD's maximum
    – wistlo
    May 17 at 13:38
  • Please post last 100 lines of your error log after a crash and maybe we can provide further suggestions. Thanks for your positive news on innodb_thread_concurrency. May 17 at 13:47
0

An apparent solution (as in no crashes for several runs, will mark solved if this stands up over time) was found in this post, where similar text was found in the .err log.

By disabling specific entries in mySQL's optimizer_switch system, no crashes were observed with multiple runs. The data increases by 10-25K rows per week, so the more recent runs have had slightly larger data sets. With the newer data sets, crashes were observed even after a clean reboot. This negates the reboot fix theory from the original post.

optimizer_switch flags

These switches where changed from their default settings, based on this post:

SET GLOBAL optimizer_switch=
'derived_merge=off,
subquery_to_derived=off,
prefer_ordering_index=off,
semijoin=off';

The crashes stopped happening, and there was no measurable decrease in performance. The query takes about 550 seconds to complete either way (though without these switches turned off, it does not complete and crashes more often than not).

Workbench caution: flags are fixed for duration of Workbench session

Workbench appears to read these switches once at start up and they will not change until the tool is fully exited and restarted.

For example, these commands can be issued from inside a Workbench window

workbench sql script commands

but the value reported by the system

workbench SQL command executed from edit window

does not reflect the changes: workbench status, viewed in data field viewer

Be sure to confirm the settings from Workbench itself before running a query from the tool. The REDO_LOG setting is shown as a reminder that a mySQL crash with this flag disabled can wipe out the instance, which happened here. (Fortunately I had recent backups of my instance at hand). Update: I had another crash with Workbench running, then ran the completion after shutting it down. I won't be running this query with Workbench active, as it seems to be related to either causing the crash or ignoring the optimizer_switch session settings set in my SQL script.

Changes made from the shell work intuitively as expected: GLOBAL takes effect on restart, and SESSION changes the value in the current session:

fine print: shows SQL commands to change flags and value of optimizer_switch between each step

1
  • I have run this solution five times with increasing data sizes with no crashes, so I am marking this as a solution. It is important to make sure Workbench is not running when this script is running.
    – wistlo
    Jun 26 at 22:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.