MySQL8 crash recovery

crashMySQLmysql-8.0

My dev MySQL-8 server crashed last night and would not restart. The error is:

[ERROR] [MY-012646] [InnoDB] File ./ibdata1: 'open' returned OS error 71. Cannot continue operation

I tried the classic "start with innodb_force_recovery=1" but that has 0 effect (also removed tried =2 without much success. I also tried removing the ibdata and ib logfiles: it won't start either.

I'm really surprised that these "standard" methods do not work for something that is a mere crash … but evils is often in the details as we know.

So the last option I tried is re-creating the tables in the database and then replacing the .idb files that are automatically generated with the ones from the crashed server.

The positive is that the MySQL database starts … but when I want to see the content of the tables I have an error message saying:

Error Code: 1812. Tablespace is missing for table <table>.

I feel I'm nearly there … any help or suggestion would be welcomed.

Many thanks – Chris

Best Answer

Some update: when I start it with innodb_force_recovery=2, I can start the db and dump my data which is one (big) step forward.

But when I set to innodb_force_recovery=0, it fails with the message below. Is InnoDB not healing itelf when you start with a force recovery?

Thanks - C

InnoDB: End of page dump
InnoDB: Page may be an update undo log page
2021-03-24T10:38:09.631071Z 1 [ERROR] [MY-011899] [InnoDB] [FATAL] Unable to read page [page id: space=4294967279, page number=176] into the buffer pool after 100 attempts. The most probable cause of this error may be that the table has been corrupted. Or, the table was compressed with with an algorithm that is not supported by this instance. If it is not a decompress failure, you can try to fix this problem by using innodb_force_recovery. Please see http://dev.mysql.com/doc/refman/8.0/en/ for more details. Aborting...
2021-03-24T10:38:09.631894Z 1 [ERROR] [MY-013183] [InnoDB] Assertion failure: ut0ut.cc:532 thread 139943235643136
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.
10:38:09 UTC - mysqld got signal 6 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x5e68130
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 = 7f4712d95bc0 thread_stack 0x46000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x1f70f0d]
/usr/sbin/mysqld(handle_fatal_signal+0x333) [0xfc3603]
/lib64/libpthread.so.0(+0xf5f0) [0x7f472b48d5f0]
/lib64/libc.so.6(gsignal+0x37) [0x7f4729544337]
/lib64/libc.so.6(abort+0x148) [0x7f4729545a28]
/usr/sbin/mysqld() [0xd2e0aa]
/usr/sbin/mysqld(ib::fatal::~fatal()+0xa4) [0x221d614]
/usr/sbin/mysqld(Buf_fetch<Buf_fetch_normal>::read_page()+0x192) [0x227c182]
/usr/sbin/mysqld(Buf_fetch_normal::get(buf_block_t*&)+0x18) [0x227c1d8]
/usr/sbin/mysqld(Buf_fetch<Buf_fetch_normal>::single_page()+0x42) [0x2281882]
/usr/sbin/mysqld(buf_page_get_gen(page_id_t const&, page_size_t const&, unsigned long, buf_block_t*, Page_fetch, char const*, unsigned long, mtr_t*, bool)+0x1cd) [0x228244d]
/usr/sbin/mysqld(trx_undo_lists_init(trx_rseg_t*)+0x262) [0x2217c22]
/usr/sbin/mysqld(trx_rseg_mem_create(unsigned long, unsigned int, unsigned int, page_size_t const&, unsigned long, std::priority_queue<TrxUndoRsegs, std::vector<TrxUndoRsegs, ut_allocator<TrxUndoRsegs> >, TrxUndoRsegs>*, mtr_t*)+0x3b3) [0x21ff0d3]
/usr/sbin/mysqld(trx_rsegs_init(std::priority_queue<TrxUndoRsegs, std::vector<TrxUndoRsegs, ut_allocator<TrxUndoRsegs> >, TrxUndoRsegs>*)+0x7cd) [0x2200b2d]
/usr/sbin/mysqld(trx_sys_init_at_db_start()+0x134) [0x2205b74]
/usr/sbin/mysqld(srv_start(bool, std::string const&)+0x2055) [0x21cc055]
/usr/sbin/mysqld() [0x204860e]
/usr/sbin/mysqld(dd::bootstrap::DDSE_dict_init(THD*, dict_init_mode_t, unsigned int)+0x90) [0x1ca5960]
/usr/sbin/mysqld(dd::upgrade_57::do_pre_checks_and_initialize_dd(THD*)+0x19a) [0x1f3b1ca]
/usr/sbin/mysqld() [0x107fe88]
/usr/sbin/mysqld() [0x246d2bc]
/lib64/libpthread.so.0(+0x7e65) [0x7f472b485e65]
/lib64/libc.so.6(clone+0x6d) [0x7f472960c88d]

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