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.
Running with default innodb_log_file_size is like running in iron shoes. The default is way to small for any reasonable database.
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.
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.
I needed to create you one very small note just to say thanks once again on your amazing advice you’ve documented on this site. It is quite pretty open-handed of you to allow without restraint just what a few individuals would have offered for sale as an e book to end up making some money for their own end, chiefly seeing that you could have done it in case you decided. These principles as well acted to be the fantastic way to fully grasp the rest have a similar dream similar to my own to see much more with reference to this condition. I am sure there are thousands of more pleasurable times up front for many who scan through your blog.
I wanted to create you this little note to say thanks as before over the wonderful concepts you’ve provided on this website. This has been certainly extremely generous of you to offer unreservedly exactly what a few individuals could possibly have distributed for an e-book to get some money on their own, specifically considering that you might well have done it in case you considered necessary. The things also acted to be the great way to fully grasp that other individuals have the identical passion much like mine to learn a little more with regard to this issue. I believe there are millions of more pleasant instances ahead for many who examine your website.
I together with my pals came checking the excellent hints on your web site and immediately came up with a horrible feeling I never expressed respect to the web blog owner for those strategies. Most of the men were for this reason excited to read through all of them and now have in fact been loving these things. We appreciate you indeed being well considerate and then for finding this sort of helpful issues millions of individuals are really desirous to be aware of. My sincere regret for not expressing gratitude to you earlier.