Skip to content

Rage Against the Shell

Linux tips and other things…

  • Home
  • Contact
  • Privacy Policy

Day: September 25, 2015

MySQL innodb checksum mismatch

Posted on September 25, 2015 - September 25, 2016 by Mr. Reboot

Tested on Ubuntu 12 and 14 / Debian 7

When you find the error InnoDB: Error: checksum mismatch in data file ./ibdata1 (or ./ibdataX) at the mysql error log, it means that ibdata checksum is not correct and there are transactions that have not been completed and wrote to the ibdata, for example due to a low memory, oom killer, mysql service crash etc.

This could be the error at mysql error log:

...
InnoDB: Error: checksum mismatch in data file ./ibdata1
InnoDB: Could not open or create data files.
InnoDB: If you tried to add new data files, and it failed here,
InnoDB: you should now edit innodb_data_file_path in my.cnf back
InnoDB: to what it was, and remove the new ibdata files InnoDB 
InnoDB: created in this failed attempt. InnoDB only wrote those 
InnoDB: files full of zeros, but did not yet use them in any way.  
InnoDB: But be careful: do not remove old data files which contain 
InnoDB: your precious data!
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[ERROR] Unknown/unsupported storage engine: InnoDB
[ERROR] Aborting
...

You can control the checksum, certainly it will show one or more errors:

~ $ innochecksum -d ibdata1

To solve it writing all changes to the ibdata file, first start mysql in recovery mode, at level 4. Add this to the my.cnf under [mysqld] tag:

innodb_force_recovery = 4

Start mysql:

~ $ /etc/init.d/mysql start

Now at the error log we can see:

...InnoDB: Log scan progressed past the checkpoint lsn 5294845868
InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the 
InnoDB: doublewrite buffer...
InnoDB: Warning: database page corruption or a failed
InnoDB: file read of space 0 page 0.
InnoDB: Trying to recover it from the doublewrite buffer.
InnoDB: Recovered the page from the doublewrite buffer.
InnoDB: Warning: database page corruption or a failed
InnoDB: file read of space 0 page 371.
InnoDB: Trying to recover it from the doublewrite buffer.
InnoDB: Recovered the page from the doublewrite buffer.
InnoDB: Warning: database page corruption or a failed
InnoDB: file read of space 0 page 197.
InnoDB: Trying to recover it from the doublewrite buffer.
InnoDB: Recovered the page from the doublewrite buffer.
InnoDB: Doing recovery: scanned up to log sequence number 5294846217
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 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 
InnoDB: Apply batch completed
InnoDB: Waiting for the background threads to start
InnoDB: 5.5.44 started; log sequence number 5294846217
InnoDB: !!! innodb_force_recovery is set to 4 !!!
...

It will make a data recovery. Now you have to disable innodb_force_recovery at my.cnf and kill mysql:

~ $ killall -9 mysqld

Usually mysql should start after kill, if not you can start it:

~ $ /etc/init.d/mysql start

At this time mysql should be already working fine!

Posted in MySQL7 Comments

Search

Calendar

September 2015
M T W T F S S
 123456
78910111213
14151617181920
21222324252627
282930  
« Aug   Oct »

Categories

  • Apache
  • Cisco
  • Command line
  • Distros
  • Dovecot
  • File systems
  • Gadgets
  • GlusterFS
  • MySQL
  • Nginx
  • NTP
  • Opendkim
  • Pacemaker + Corosync
  • Postfix
  • Raspberrypi
  • SSH
  • SSL
  • Varnish

RSS RSS

  • Using qrencode January 16, 2022
  • Compile varnish module vmod_vsthrottle April 22, 2020
  • SSH vpn with sshuttle April 9, 2020
  • Disable swap in systemd December 16, 2019
  • Getting the parent process pid October 12, 2018
Proudly powered by WordPress | Theme: micro, developed by DevriX.