How to fix InnoDB: ERROR: the age of the last checkpoint
On a MySQL server, the error log keeps getting the following error message
|
1 2 3 4 5 |
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
|
1 |
vi /etc/mysql/mariadb.conf.d/50-server.cnf |
Under [mysqld] section, add
|
1 2 3 |
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.
|
1 2 |
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.
|
1 |
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.
Move log files out
|
1 2 |
mkdir ~/mysql-old-log mv /var/lib/mysql/ib_logfile* ~/mysql-old-log |
Start MySQL service with
|
1 |
systemctl start mysql |
This will recreate MySQL log files (ib_logfile0 and ib_logfile1) with the new size.
See MySQL