Mysql Innodb: InnoDB: ERROR: the age of the last checkpoint is InnoDB: which exceeds the log group capacity

I really need some mysql expertize. I am a newbi to mysql and I am seeing some server crash of my db in the past 1 week.

I am using mysql 5.1.36 on Ubuntu. This is a dedicated mysql server with Dual core and 4GB memory and 40GB SSD.

The log errors are:

120413 23:57:15 [Note] Plugin 'FEDERATED' is disabled.
120413 23:57:15 [Warning] option 'innodb-autoextend-increment': unsigned value 2000 adjusted to 1000
120413 23:57:15  InnoDB: Initializing buffer pool, size = 2.9G
120413 23:57:15  InnoDB: Completed initialization of buffer pool
120413 23:57:16  InnoDB: Started; log sequence number 0 44234
120413 23:57:16 [Note] Event Scheduler: Loaded 0 events
120413 23:57:16 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.58-1ubuntu1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
120414  0:00:25 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=e2-relay-bin' to avoid this problem.
120414  0:00:25 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port='3306', master_log_file='', master_log_pos='4'. New state master_host='', master_port='3306', master_log_file='mysql-bin.000043', master_log_pos='87039427'.
120414  0:58:37 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port='3306', master_log_file='mysql-bin.000043', master_log_pos='87039427'. New state master_host='', master_port='3306', master_log_file='mysql-bin.000043', master_log_pos='87846901'.
120414  2:20:34  InnoDB: ERROR: the age of the last checkpoint is 241588252,
InnoDB: which exceeds the log group capacity 241588224.
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.

My.cnf is as below.

```
default-storage-engine=innodb
default-table-type=innodb
key_buffer = 384M
max_all

At first glance, I would say that your innodb_log_file_size is way too small. It should be bigger to do two things:

• Accommodate any big BLOB or TEXT fields

• Holding bigger transactions

Here is what you should do for now to see if it helps:

STEP 01) Change the following in /etc/my.cnf

[mysqld]
innodb_log_buffer_size          = 32M
innodb_buffer_pool_size         = 3G
innodb_log_file_size            = 768M

STEP 02) service mysql stop

STEP 03) rm -f /var/lib/mysql/ib_logfile*

STEP 04) service mysql start

This will rebuild the following files

• /var/lib/mysql/ib_logfile0

• /var/lib/mysql/ib_logfile1

Give it a Try !!!
UPDATE 2013-07-03 12:37 EDT

I have updated my other posts on this and missed this one

ButtleButkus just commented at 2013-07-03 07:18:56 EDT

Wouldn't it be advisable to copy the ib_logfile* to another location for backup before deleting them?

Since there could be unfinished transactional data inside, here is what should be done

STEP 01) Change the following in /etc/my.cnf

[mysqld]
innodb_log_buffer_size          = 32M
innodb_buffer_pool_size         = 3G
innodb_log_file_size            = 768M

STEP 02) mysql -uroot -p -e"SET GLOBAL innodb_fast_shutdown = 0;"

STEP 03) service mysql stop

STEP 04) rm -f /var/lib/mysql/ib_logfile*

STEP 05) service mysql start

I added SET GLOBAL innodb_fast_shutdown = 0;. What does that do? It forces InnoDB to completely purge transactional changes from all of InnoDB moving parts, including the transactional logs (ib_logfile0, ib_logfile1). Thus, there is no need to backup the old ib_logfile0, ib_logfile1. If deleting them makes you nervous, then make Step 04

mv /var/lib/mysql/ib_logfile* ..

to put the old logs in /var/lib. If the recreation of the logs is successful and mysqld starts up, then you can delete the old logs.

I have been using this feature for a year now. I have updated my other posts to reflect this...

• May 20, 2013 : Innodb one file per tablespace

• May 05, 2013 : Issue after moving the ib_logfile1 and ib_logfile0 files

• Jan 10, 2013 : Finding and fixing InnoDB index corruption

• Dec 17, 2012 : MySQL Start/Stop

• Feb 16, 2011 : How to safely change MySQL innodb variable 'innodb_log_file_size'? (Last Update)

• Feb 04, 2011 : MySQL InnoDB - innodb_file_per_table cons? (Last Update)

• Oct 29, 2010 : Howto: Clean a mysql InnoDB storage engine? (Added as Step 03 on Jun 04, 2013)

If there are other older posts of mine where I do not mention innodb_fast_shutdown, let me know so I can update it. Thanks again, ButtleButkus.

StackExchange Database Administrators Q#16510, answer score: 27