MySQL Bugs: #97310: MySQL 8.0.17 server in startup crash loop

Bug #97310	MySQL 8.0.17 server in startup crash loop
Submitted:	21 Oct 2019 10:38	Modified:	22 Oct 2019 3:06
Reporter:	bob guo	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Dictionary	Severity:	S3 (Non-critical)
Version:	8.0.17	OS:	MacOS (10.14.4 )
Assigned to:		CPU Architecture:	x86

Description:
I install mysql community for mac os x 8.0.17. 

as my computer pow off then pown on. the mysql service crash. 

/usr/local/mysql/data/mysqld.local.err content is: 

2019-10-21T09:48:35.044246Z 0 [System] [MY-010116] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.17) starting as process 95783
2019-10-21T09:48:35.048671Z 0 [Warning] [MY-010159] [Server] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive
2019-10-21T09:48:36.097194Z 0 [System] [MY-010229] [Server] Starting crash recovery...
2019-10-21T09:48:36.114962Z 0 [System] [MY-010232] [Server] Crash recovery finished.
2019-10-21T09:48:36.146669Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2019-10-21T09:48:36.173488Z 0 [System] [MY-010931] [Server] /usr/local/mysql/bin/mysqld: ready for connections. Version: '8.0.17'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server - GPL.
2019-10-21T09:48:36.342238Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind-address: '::' port: 33060
2019-10-21T09:48:37.136119Z 0 [ERROR] [MY-013183] [InnoDB] Assertion failure: dict0dict.cc:3333:for_table || ref_table thread 123145516576768
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.
09:48:37 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x7febc4936400
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 = 70000cc56dd0 thread_stack 0x46000
0   mysqld                              0x0000000106e804dc my_print_stacktrace(unsigned char*, unsigned long) + 60
1   mysqld                              0x000000010628553c handle_fatal_signal + 428
2   libsystem_platform.dylib            0x00007fff6ad2cb5d _sigtramp + 29
3   ???                                 0x000000010bfafb76 0x0 + 4495965046
4   libsystem_c.dylib                   0x00007fff6abec6a6 abort + 127
5   mysqld                              0x0000000107249157 ut_dbg_assertion_failed(char const*, char const*, unsigned long) + 343
6   mysqld                              0x0000000106fcf37b dict_foreign_add_to_cache(dict_foreign_t*, char const**, bool, bool, dict_err_ignore_t) + 1707
7   mysqld                              0x0000000106fe89a4 dd_table_load_fk_from_dd(dict_table_t*, dd::Table const*, char const**, dict_err_ignore_t, bool) + 1540
8   mysqld                              0x0000000106feebe7 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*>
 >&) + 8487
9   mysqld                              0x0000000106fdf17d dd_table_open_on_dd_obj(dd::cache::Dictionary_client*, dd::Table const&, dd::Partition const*, char const*, dict_table_t*&, THD*) + 2413
10  mysqld                              0x0000000106fe0465 dd_table_open_on_id_low(THD*, MDL_ticket**, unsigned long long) + 1733
11  mysqld                              0x0000000106fdfbc3 dd_table_open_on_id(unsigned long long, THD*, MDL_ticket**, bool, bool) + 2115
12  mysqld                              0x00000001071d0aee row_purge_step(que_thr_t*) + 750
13  mysqld                              0x000000010718a1f4 que_run_threads(que_thr_t*) + 372
14  mysqld                              0x00000001072228fc trx_purge(unsigned long, unsigned long, bool) + 3612
15  mysqld                              0x0000000107200b47 srv_purge_coordinator_thread() + 2279
16  mysqld                              0x00000001070c2487 void Runnable::operator()<void (*)()>(void (*&&)()) + 199
17  mysqld                              0x00000001070c22d1 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*
) + 49
18  libsystem_pthread.dylib             0x00007fff6ad352eb _pthread_body + 126
19  libsystem_pthread.dylib             0x00007fff6ad38249 _pthread_start + 66
20  libsystem_pthread.dylib             0x00007fff6ad3440d thread_start + 13

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0):
Connection ID (thread ID): 0
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.

How to repeat:
every time as excute shell cmd :   sudo launchctl load -F com.oracle.oss.mysql.mysqld.plist

correction Synopsis spell and version formate

Hi Mr. guo,

Thank you for your bug report.

However, this is not a bug.

The only things that happen here is that server prints the following message into your error log:

                            "there is no index in referenced table"
                                " which would contain\n"
                                "the columns as the first columns,"
                                " or the data types in the\n"
                                "referenced table do not match"
                                " the ones in table."

This happens because you have not set your MySQL server to be 100 % ACID compliant. Description on how to achieve that is found in our Reference Manual.

Not a bug.

Thank you for your reply。

but I didn't find the message you mentioned in the log.

From the details of the stack, i think the problem should be about the data dictionary, not about the user table. I suspect  the consistency of the data dictionary was compromised when  system was shutdown. and the process in startup could not fix it.

how can i recovery the inconsistency of data?

Hi Mr. guo,

First of all, all user tables have their metadata in the data dictionary. So, simply, the problem could be in interface.

The message that I quoted is printed with specially built debug binary.

Last, but not least, you have written about power down and up. This is not the same as proper, fully ACID set shutdown.

Once again, your problem is, most likely, in your settings which do not provide full ACID compliance. Again, this is described in our Reference Manual.

Thank you for your help. 

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.

After that, remove  innodb_force_recovery option in /etc/my.cnf. I invoked  mysqld with the --initialize and reinitialized the whole data directory. 

So  mysqld  restarted normaly.  at last i successfully imported the data to mysql.

Hi Mr. Guo,

I am glad to hear that you are out of trouble, but, again I must repeat that the problems that you experienced are due to the non-ACID setup of the server and OS. I am using the same OS and I do know that, for example, filesystem cache can not be turned off.