A solution for MySQL Assertion failure FIL_NULL

This blog post was published 10 years ago and may or may not have aged well. While reading please keep in mind that it may no longer be accurate or even relevant.

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.

If you think you found a mistake in this blog post, or would like to suggest an improvement to this blog post, you can write me an e-mail to the address public dot michael at franzl dot name; as subject please use the prefix "Comment to blog post" and append the post title.
 
Copyright © 2023 Michael Franzl