mysql's inndob engine crashes

Keywords: MySQL Database SQL mysqldump

tail-30/data/mysql/localhost.localdomain.err (log file path depends on the situation),



161108 11:36:45 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/var


2017-08-15 11:36:46 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).


2017-08-15 11:36:46 5497 [Note] Plugin 'FEDERATED' is disabled.


2017-08-15 11:36:46 7f11c48e1720 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.

2017-08-15 11:36:46 5497 [Note] InnoDB: Using atomics to ref count buffer pool pages

2017-08-15 11:36:46 5497 [Note] InnoDB: The InnoDB memory heap is disabled

2017-08-15 11:36:46 5497 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2017-08-15 11:36:46 5497 [Note] InnoDB: Memory barrier is not used

2017-08-15 11:36:46 5497 [Note] InnoDB: Compressed tables use zlib 1.2.3

2017-08-15 11:36:46 5497 [Note] InnoDB: Using CPU crc32 instructions

2017-08-15 11:36:46 5497 [Note] InnoDB: Initializing buffer pool, size = 16.0M

2017-08-15 11:36:46 5497 [Note] InnoDB: Completed initialization of buffer pool

InnoDB: Database page corruption on disk or a failed

InnoDB: file read of page 5.

InnoDB: You may have to recover from a backup.

2017-08-15 11:36:46 7f11c48e1720 InnoDB: Page dump in ascii and hex (16384 bytes):

 len 16384; hex 7478d078000000050000000000000000000000000f271f4d000700000000000000000000000000000000001b4000000000000000000200f20000000000000006000000000000002d000000000000002e000000000000002f0000000000000030000000000(A lot of similar code is omitted.

InnoDB: End of page dump

2017-08-15 11:36:46 7f11c48e1720 InnoDB: uncompressed page, stored checksum in field1 1954074744, calculated checksums for field1: crc32 993334256, innodb 2046145943, none 3735928559, stored checksum in field2 1139795846, calculated checksums for field2: crc32 993334256, innodb 1606613742, none 3735928559, page LSN 0 254222157, low 4 bytes of LSN at page end 254221236, page number (if stored to page already) 5, space id (if created with >= MySQL-4.1.1 and stored already) 0

InnoDB: Page may be a transaction system page

InnoDB: Database page corruption on disk or a failed

InnoDB: file read of page 5.

InnoDB: You may have to recover from a backup.

InnoDB: It is also possible that your operating

InnoDB: system has corrupted its own file cache

InnoDB: and rebooting your computer removes the

InnoDB: error.

InnoDB: If the corrupt page is an index page

InnoDB: you can also try to fix the corruption

InnoDB: by dumping, dropping, and reimporting

InnoDB: the corrupt table. You can use CHECK

InnoDB: TABLE to scan your table for corruption.

InnoDB: See also

InnoDB: about forcing recovery.

InnoDB: Ending processing because of a corrupt database page.

2017-08-15 11:36:46 7f11c48e1720  InnoDB: Assertion failure in thread 139714288817952 in file line 4201

InnoDB: We intentionally generate a memory trap.

InnoDB: Submit a detailed bug report to

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: about forcing recovery.

03:36:46 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.

We will try our best to scrape up some info that will hopefully help

diagnose the problem, but since we have already crashed, 

something is definitely wrong and this may fail.







It is possible that mysqld could use up to 

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 798063 K  bytes of memory

Hope that's ok; if not, decrease some variables in the equation.


Thread pointer: 0x0


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 = 0 thread_stack 0x40000








































The manual page at contains


information that should help you find out what is causing the crash.


161108 11:36:46 mysqld_safe mysqld from pid file /usr/local/mysql/var/ ended



II. Problem Analysis

You can see from the log that there is something wrong with the InnoDB engine. The log prompts to view the mandatory recovery method. Under the [mysqld] field found in MySQL configuration file my.cnf, add innodb_force_recovery=1:


innodb_force_recovery = 1

If innodb_force_recovery = 1 does not work, try two to six numbers
Then restart mysql and restart successfully. Then use mysqldump or pma to export data and perform repair operations. After the repair is completed, the parameter is commented out and the default value is restored to 0.
Configuration file parameters: innodb_force_recovery
innodb_force_recovery affects the recovery status of the entire InnoDB storage engine. The default is 0, which means that all recovery operations (i.e. checking data pages / purge undo / insert buffer merge / rolling back & forward) are performed when recovery is required, and mysql may not be started and error logs are recorded when effective recovery operations are not performed.
innodb_force_recovery can be set to 1-6, with large numbers containing the effects of all the previous numbers. When the parameter value is greater than 0, the table can be selected, created, drop operations, but insert,update or delete operations are not allowed.


1(SRV_FORCE_IGNORE_CORRUPT): Ignore the corrupt page checked.
2(SRV_FORCE_NO_BACKGROUND): Blocking the operation of the main thread, such as the need for the main thread to perform a full purge operation, results in crash.
3(SRV_FORCE_NO_TRX_UNDO): Transaction rollback is not performed.
4(SRV_FORCE_NO_IBUF_MERGE): The merge operation of insertion buffer is not performed.
5(SRV_FORCE_NO_UNDO_LOG_SCAN): Without looking at the redo log, the InnoDB storage engine treats uncommitted transactions as committed.
6(SRV_FORCE_NO_LOG_REDO): No rollforward operation is performed.
III. Analytical Scheme

Reference for general repair methods:

The first method

Create a new form:
Creatable demo_bak is the same as the original table structure except that INNODB is changed to MYISAM.
Import data in
insert into demo_bak select * from demo;
Delete the original table:
drop table demo;
After annotating innodb_force_recovery, restart.
rename table demo_bak to demo;
Finally, change back to the storage engine:
alter table demo engine = innodb

The second method

Another method is to export the table using mysqldump and then import it back into the InnoDB table. The results of these two methods are the same.
Backup export (including structure and data):
mysqldump -uroot -p123 test > test.sql
Reduction method 1:
use test;
source test.sql
Restore Method 2 (System Command Line):
mysql -uroot -p123 test < test.sql;
Note that the CHECK TABLE command is basically useless in InnoDB databases.

The third method

1. Configure my.cnf

Configure innodb_force_recovery = 1 or 2-6 to restart MySQL

2. Export data script

mysqldump -uroot -p123 test > test.sql
Export the SQL script. Or use Navicat to import all databases / tables into the databases of other servers.
Note: The data here must be backed up successfully. Then delete the data in the original database.

3. Delete ib_logfile 0, ib_logfile 1, ibdata1

Back up the ib_logfile 0, ib_logfile 1 and ibdata1 files in the MySQL data directory, and then delete the three files

4. Configure my.cnf

Remove or configure innodb_force_recovery = 0 to restart MySQL service by deleting or configuring the line configuration of innodb_force_recovery = 1 or 2-6 digits in my.cnf

5. Importing data into MySQL database

Mysql-uroot-p123 test < test.sql; or use Navicat to import the backup data into the database.
In this way, we should pay attention to the following problems:
1. The three files of ib_logfile 0, ib_logfile 1 and ibdata1 must be backed up and deleted first.
2. Make sure that the original data has been exported successfully.
3. When the data in the original database is deleted after the data export is successful, if the prompt cannot be deleted, you can enter the MySQL data directory on the command line and manually delete the relevant database folder or the data table files under the database folder, provided that the data must be exported or backed up successfully.


Posted by jschultz on Sat, 07 Sep 2019 05:18:16 -0700