A solution for MySQL Assertion failure FIL_NULL

A defective RAM module recently caused data corruption in MySQL tables. MySQL would log the following to /var/log/syslog in regular intervals, about every few minutes:

140125 5:04:41 InnoDB: Assertion failure in thread 140046518261504 in file fut0lst.ic line 83
InnoDB: Failing assertion: addr.page == FIL\_NULL || addr.boffset >= FIL\_PAGE\_DATA
InnoDB: We intentionally generate a memory trap.

Reading MySQL documentation and various blogs didn’t yield much. I ran CHECK TABLES on all the tables and they all reported OK. Then I ran

mysqlcheck --all-databases

and still all tables reported OK.

Nevertheless, the Assertion Failures continued. Then I stumbled across this excellent blog post, which suggested to dump evertying into a .sql file, wipe /var/lib/mysql (not without making a backup, mind you!), and re-import everything from scratch. This is what I did and it worked.

I recorded the passwords for each database and collected it into a SQL script like this:

GRANT ALL PRIVILEGES ON database\_name1 TO 'username1'@'localhost' IDENTIFIED BY 'password1';
GRANT ALL PRIVILEGES ON database\_name2 TO 'username2'@'localhost' IDENTIFIED BY 'password2';

When dumping all databases into a .sql file, it does not dump the permissions, so we will need to restore them later with this script.

Next, the dumping part and reinstallation of MySQL:

cd ~
mysqldump -u root -ppass --all-databases > alldbs.sql
cd /var/lib
cp -vpr mysql mysql-backup
apt-get reinstall mysql-server

Here, I had to reset the MySQL admin password:

dpkg-reconfigure mysql-server-5.5

Then, I re-imported all databases from the dump file:

cd ~
mysql -u root -ppass < alldbs.sql

Then I run the SQL permission script that I mention above.