Fix for InnoDB: ERROR: the age of the last checkpoint is …

I recently have to restore production DB to local environment and I saw mysql log giving with bunch of errors like this

InnoDB: largest such row.
150817 23:22:56  InnoDB: ERROR: the age of the last checkpoint is 9440934,
InnoDB: which exceeds the log group capacity 9433498.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.
150817 23:23:25  InnoDB: ERROR: the age of the last checkpoint is 9439873,
InnoDB: which exceeds the log group capacity 9433498.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.
150817 23:23:51  InnoDB: ERROR: the age of the last checkpoint is 9442549,
InnoDB: which exceeds the log group capacity 9433498.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.

Well, what does all this mean?
So, as it seems, the InnoDB log file settings need to be updated. I found a listing of the steps to take on MySQL’s website. Here are those steps fleshed out a little.
1) Make sure your innodb_fast_shutdown settings is not 2To fix this, set it to one by runnning the following query:

SET GLOBAL innodb_fast_shutdown = 1;

2) Shut down mysql and look for errors in the log to make sure nothing went wrong.

service mysqld stopcat /var/log/mysql.log

3) Copy the old log files to a new place in case something goes wrong.

mv /var/lib/mysql/ib_logfile* ..
4) Next edit your /etc/my.cnf file to increase your InnoDB log size:

innodb_log_file_size = 128M

You may see people suggesting really large values for the log file size — I saw a value of 768M in one StackOverflow answer. If that seems like a suspiciously large, random value to you (especially considering the default is 5M) then you’re on to something. But that begs the question, what should the value be? Here is a helpful article on how to properly size your log files.
5) Lastly, watch your /var/log/mysql.log file and start MySQL back up.

service mysqld start

One helpful tip, if mysql gives any error then you probably tried to skip step 3, like I did. Turns out, this is an important step and your MySQL server may not even start up, without it. It’s easy to fix though, either go back and do step 3 or remove your edits made in step 4 and restart mysqld. You should be all set.
This entry was posted in Linux, MySQL. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.