MySQL: InnoDB: ERROR: the age of the last checkpoint is [number]

One of the mysql database servers I manage started to have issues with doing backups yesterday. mysqldump was running but nothing was happening on the backup side. I started to investigate to see why our full backups were failing. I opened up the mysql error log file (mine is at: /usr/local/mysql/var/hostname.err) and notice there were many instances of following error.

070815 15:31:46 InnoDB: ERROR: the age of the last checkpoint is 9433957,
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.

I poked around and found out our log files were set to default (5 mb) and they needed to be increased. After doing some calculations and research, I decided going to 50 mb at this point would be a good way to go. So I made the change in my.cnf file and added: innodb_log_file_size = 50M

I then stopped our mysql server and restarted it. And to my horror I saw following error messages show up in the mysql error logs:

070815 17:37:40 [ERROR] /usr/local/mysql/libexec/mysqld: Incorrect information in file: './dbname/table_name.frm'
070815 17:37:40 [ERROR] /usr/local/mysql/libexec/mysqld: Incorrect information in file: './dbname/table_name.frm'

I stopped the mysql server right away. I then remember, by help of a friend, that I have to remove the old log files if I change the size by changing innodb_log_file_size. So I issued a command: mv ib_logfile? ..

This command moves both ib_logfile1 and ib_logfile2 down one directory. I didn’t want to just remove ‘em so instead I moved them. After that, I restarted mysql again and to my comfort, everything came back up without errors. So lesson learned: if you change ib_logfile size by using innodb_log_file_size setting, make sure you move existing log files before you start mysql again.

————————————-
DISCLAIMER: Please be smart and use code found on internet carefully. Make backups often. And yeah.. last but not least.. I am not responsible for any damage caused by this posting. Use at your own risk.

3 thoughts on “MySQL: InnoDB: ERROR: the age of the last checkpoint is [number]

  1. Nils

    Also note that there is the innodb_fast_shutdown setting. If activated, the logs don’t get applied upon shutdown, so be sure to disable it before changing logfile size or touching the logfiles in any way.

  2. Ruben

    Hello,

    thanks for share that experience, maybe is a common error if you are not a DBA expert (like me). You method worked fine!!

    Regards.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>