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.