Mysql ibdata1 is broken

ibdataimportMySQLrecovery

I was importing an old SQL into existing schema. But the MySQL service was stopped suddenly. Now I'm not able to start the service. It seems that the ibdata1 file is somehow corrupted. Here are the logs I got:

2017-12-08T17:56:24.927955Z 0 [Note] InnoDB: Using CPU crc32 instructions
2017-12-08T17:56:24.929802Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-12-08T17:56:24.942256Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-12-08T17:56:24.961017Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-12-08T17:56:24.962154Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 16417491586
2017-12-08T17:56:24.962169Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 16417493278
2017-12-08T17:56:24.963433Z 0 [Note] InnoDB: Database was not shutdown normally!
2017-12-08T17:56:24.963443Z 0 [Note] InnoDB: Starting crash recovery.
2017-12-08T17:56:24.996387Z 0 [Note] InnoDB: Starting an apply batch of log records to the database…
InnoDB: Progress in percent: 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
2017-12-08T17:56:25.501869Z 0 [Note] InnoDB: Apply batch completed
2017-12-08T17:56:25.626479Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2017-12-08T17:56:25.626508Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-12-08T17:56:25.626637Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait …
2017-12-08T17:56:25.649790Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2017-12-08T17:56:25.650460Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-12-08T17:56:25.650469Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-12-08T17:56:25.650670Z 0 [Note] InnoDB: Waiting for purge to start
2017-12-08T17:56:25.655806Z 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.
2017-12-08T17:56:25.655825Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.
2017-12-08 12:56:25 0x7000049f8000 InnoDB: Assertion failure in thread 123145379872768 in file fil0fil.cc line 896
InnoDB: Failing assertion: success
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/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
17:56:25 UTC – mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=0
max_threads=151
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 68218 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f9792000000
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 = 7000049f7e20 thread_stack 0x40000
0 mysqld 0x000000010caa714a my_print_stacktrace + 58
1 mysqld 0x000000010ca039d0 handle_fatal_signal + 688
2 libsystem_platform.dylib 0x00007fffb8d52b3a _sigtramp + 26
3 mysqld 0x000000010d3bac17 _ZZN10binary_log4Uuid9to_stringEPKhPcE11byte_to_hex + 41879
4 libsystem_c.dylib 0x00007fffb8bd7420 abort + 129
5 mysqld 0x000000010ccfd9c1 _Z23ut_dbg_assertion_failedPKcS0_m + 161
6 mysqld 0x000000010cb623bc _ZL18fil_node_open_fileP10fil_node_t + 3948
7 mysqld 0x000000010cb6d962 _ZL23fil_node_prepare_for_ioP10fil_node_tP12fil_system_tP11fil_space_t + 194
8 mysqld 0x000000010cb6e418 _Z6fil_ioRK9IORequestbRK9page_id_tRK11page_size_tmmPvS8_ + 1096
9 mysqld 0x000000010cb27073 _ZL17buf_read_page_lowP7dberr_tbmmRK9page_id_tRK11page_size_tb + 387
10 mysqld 0x000000010cb27288 _Z13buf_read_pageRK9page_id_tRK11page_size_t + 56
11 mysqld 0x000000010cb0bb7a _Z16buf_page_get_genRK9page_id_tRK11page_size_tmP11buf_block_tmPKcmP5mtr_tb + 1290
12 mysqld 0x000000010cae91e8 _Z27btr_cur_search_to_nth_levelP12dict_index_tmPK8dtuple_t15page_cur_mode_tmP9btr_cur_tmPKcmP5mtr_t + 4008
13 mysqld 0x000000010cc97824 _Z21row_search_on_row_refP10btr_pcur_tmPK12dict_table_tPK8dtuple_tP5mtr_t + 164
14 mysqld 0x000000010cc956a6 _ZL34row_purge_remove_clust_if_poss_lowP12purge_node_tm + 438
15 mysqld 0x000000010cc93a68 _Z14row_purge_stepP9que_thr_t + 1944
16 mysqld 0x000000010cc55919 _Z15que_run_threadsP9que_thr_t + 937
17 mysqld 0x000000010ccd9c4d _Z9trx_purgemmb + 2973
18 mysqld 0x000000010ccc2d57 srv_purge_coordinator_thread + 2871
19 libsystem_pthread.dylib 0x00007fffb8d5c93b _pthread_body + 180
20 libsystem_pthread.dylib 0x00007fffb8d5c887 _pthread_body + 0
21 libsystem_pthread.dylib 0x00007fffb8d5c08d thread_start + 13

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

I've tried several ways, such as adding the innodb_force_recovery in my.cnf and killing all of the MySQL process. None of them works. Could anyone help?

Best Answer

Assertion failure in thread can frequently be corrected in my.cnf [mysqld] section

innodb_buffer_pool_size=512M  # from 128M showing in startup

Because we do not know how much RAM you have, this may enable you to proceed. Please post any information you can requested on Jun 30, 2018 by Wilson Hauck. Thanks