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
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:
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.