How to fix InnoDB: ERROR: the age of the last checkpoint

On a MySQL server, the error log keeps getting the following error message

221024 17:50:03  InnoDB: ERROR: the age of the last checkpoint is X,
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.

The error is because you are trying to insert more data into InnoDB and the InnoDB log is getting full before the data can be written into the data file. You need to set the value of innodb_log_file_size value at least ten times bigger than max_allowed_packet.

To fix the error, edit your MySQL configuration file

On Debian/Ubuntu with MairaDB

vi /etc/mysql/mariadb.conf.d/50-server.cnf

Under [mysqld] section, add

innodb_buffer_pool_size=3G
innodb_log_file_size=1G
innodb_log_buffer_size=32M

To select a proper size for these MySQL variables for your system, use MySQL tuner.

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl

It will suggest you recommended values of innodb_buffer_pool_size and innodb_log_file_size.

Disable fast MySQL shutdown.

mysql -u root -p -e "SET GLOBAL innodb_fast_shutdown = 0;"

This will flush all changes done on MySQL from log file to data file so we can safely remove MySQL InnoDB log files. We need to remove existing log files before MySQL can create log files with different size.

Stop MySQL server.

systemctl stop mysql

Move log files out

mkdir ~/mysql-old-log
mv /var/lib/mysql/ib_logfile* ~/mysql-old-log

Start MySQL service with

systemctl start mysql

This will recreate MySQL log files (ib_logfile0 and ib_logfile1) with the new size.

See MySQL

Need help with Linux Server or WordPress? We can help!

Leave a Reply

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