Bug #99792 MySQL 8.0.19 continuously crashes at startup
Submitted: 6 Jun 2020 10:39 Modified: 8 Jul 2020 14:29
Reporter: Sergey Korolev Email Updates:
Status: No Feedback Impact on me:
Category:MySQL Server: Data Dictionary Severity:S2 (Serious)
Version:8.0.19 OS:MacOS (Catalina 10.15.5)
Assigned to: CPU Architecture:x86 (Quad-Core Intel Core i7)
Tags: assertion failure, innodb

[6 Jun 2020 10:39] Sergey Korolev
After some time of extensive work (bulk inserts with millions of records) MySQL server stopped responding and after some time I restarted it. After that it started continuously crash after start in a loop (mysqld_safe tryies to restart it and mysqld crashes again. 

The log:
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.
2020-06-05T21:53:51.6NZ mysqld_safe mysqld restarted
2020-06-05T21:53:52.385767Z 0 [Warning] [MY-010097] [Server] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
2020-06-05T21:53:52.385854Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.19) starting as process 6034
2020-06-05T21:53:52.390352Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive
2020-06-05T21:53:53.299238Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
2020-06-05T21:53:53.310065Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
2020-06-05T21:53:53.384044Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-06-05T21:53:53.405467Z 0 [System] [MY-010931] [Server] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.0.19'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server - GPL.
2020-06-05T21:53:53.545214Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind-address: '::' port: 33060
2020-06-05T21:53:54.324237Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: dict0dict.cc:3338:for_table || ref_table thread 123145364889600
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
21:53:54 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x7f80a4420200
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...
stack_bottom = 700003bade80 thread_stack 0x46000
0   mysqld                              0x0000000103051ffc my_print_stacktrace(unsigned char const*, unsigned long) + 60
1   mysqld                              0x000000010245a3d9 handle_fatal_signal + 425
2   libsystem_platform.dylib            0x00007fff6f9485fd _sigtramp + 29
3   ???                                 0x00000001120d4acb 0x0 + 4597828299
4   libsystem_c.dylib                   0x00007fff6f81e808 abort + 120
5   mysqld                              0x0000000103748fd4 ut_dbg_assertion_failed(char const*, char const*, unsigned long) (.cold.1) + 84
6   mysqld                              0x000000010342a051 ut_dbg_assertion_failed(char const*, char const*, unsigned long) + 273
7   mysqld                              0x00000001031a60a2 dict_foreign_add_to_cache(dict_foreign_t*, char const**, bool, bool, dict_err_ignore_t) + 1714
8   mysqld                              0x00000001031ba3c4 dd_table_load_fk_from_dd(dict_table_t*, dd::Table const*, char const**, dict_err_ignore_t, bool) + 1572
9   mysqld                              0x00000001031c010a dict_table_t* dd_open_table_one<dd::Table>(dd::cache::Dictionary_client*, TABLE const*, char const*, dd::Table const*, THD*, std::__1::deque<char const*, ut_allocator<char const*> >&) + 7098
10  mysqld                              0x00000001031b0614 dd_table_open_on_dd_obj(dd::cache::Dictionary_client*, dd::Table const&, dd::Partition const*, char const*, dict_table_t*&, THD*) + 2196
11  mysqld                              0x00000001031b1d43 dd_table_open_on_id_low(THD*, MDL_ticket**, unsigned long long) + 2019
12  mysqld                              0x00000001031b11f5 dd_table_open_on_id(unsigned long long, THD*, MDL_ticket**, bool, bool) + 2437
13  mysqld                              0x00000001033ad0cb row_purge_step(que_thr_t*) + 763
14  mysqld                              0x0000000103366047 que_run_threads(que_thr_t*) + 375
15  mysqld                              0x0000000103402e1e trx_purge(unsigned long, unsigned long, bool) + 3598
16  mysqld                              0x00000001033dfde7 srv_purge_coordinator_thread() + 2359
17  mysqld                              0x00000001033f1017 void Runnable::operator()<void (*)()>(void (*&&)()) + 199
18  mysqld                              0x00000001033f0e94 void* std::__1::__thread_proxy<std::__1::tuple<std::__1::unique_ptr<std::__1::__thread_struct, std::__1::default_delete<std::__1::__thread_struct> >, Runnable, void (*)()> >(void*) + 52
19  libsystem_pthread.dylib             0x00007fff6f954109 _pthread_start + 148
20  libsystem_pthread.dylib             0x00007fff6f94fb8b thread_start + 15

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0): 
Connection ID (thread ID): 0

How to repeat:
Not sure
[8 Jun 2020 14:29] MySQL Verification Team
Check for duplicate https://bugs.mysql.com/bug.php?id=97310. See comment:

"I modify the /etc/my.cnf and a line and set innodb_force_recovery = 5 to forcing InnoDB Recovery.  mysqld started successfully 。then i clould export my data."
[9 Jul 2020 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".