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 2. To 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.
