MySQL log is in the future!

After last time MySQL crashed, the log file has not been working properly. But it is a silent error so I didn’t notice it until now.

InnoDB: Error: page 570 log sequence number 7289495
InnoDB: is in the future! Current system log sequence number 5574939.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.

The most helpful tips I found is  and MySQL InnoDB crash recovery guide for 11.23,11.24 (mysql will not start) and MySQL Forums :: InnoDB :: innodb log sequence error

This one gives answers in a more sophisticated way.

My database is relative small in size so I decided to go with the first two easy recipes. But the “dumpdbs.pl” perl script failed to work because of the mysql root password. I used mysqldump to do the work. Here is what I did:

1) stop mysqld: sudo service mysqld stop

2) backup all the data : mysqldump -u root -p --all-databases > all-databases.sql

3) rename the files in case anything bad happens:

mv ib_logfile0 ib_logfile0.bak

mv ib_logfile1 ib_logfile1.bak

mv ibdata1 ibdata1.bak

4) restart mysqld with “innodb_force_recovery=4” in my.cnf : sudo service mysqld start
Now the “log in the future” problem should be gone with the ibdata1 file reconstructed. But all the data are gone, too.

5) restore the data: mysql -u root -p < all-databases.sql

6) you need to restart the mysqld again with “innodb_force_recovery=4”: sudo service mysqld restart
Otherwise you will get another error in your mysql.log file:

InnoDB: Error: table `xxx`.`xx` does not exist in the InnoDB internal
InnoDB: data dictionary though MySQL is trying to drop it.
InnoDB: Have you copied the .frm file of the table to the
InnoDB: MySQL database directory from another database?
InnoDB: You can look for further help from
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html

Now everything should be back to normal, double check if you lost any data. I got lucky. You can delete the .bak file if you want.

NOTE: if your database is huge, the dump and restore process will take a very long time. So you might want to explore the solutions given by the Any better way out of MySQL InnoDB log “in the future”?

Advertisements

Setup a wordpress multisite network on AWS EC2 (1)

I tried to setup a blog for the share future project in the beginning of Dec. At the beginning I forgot that AWS Free Usage Tier included RDS service, so I used the MySQL service on our micro EC2 instance. As you can expect, the whole site crashed before long because out of memory.

The mysqld.log:

121212 1:03:04 InnoDB: Fatal error: cannot allocate memory for the buffer pool
121212 1:03:04 [ERROR] Plugin ‘InnoDB’ init function returned error.
121212 1:03:04 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
121212 1:03:04 [ERROR] Unknown/unsupported storage engine: InnoDB
121212 1:03:04 [ERROR] Aborting

After some research, I found that making swap space solved the problem (Ref here).

Now we decided to setup a separate wordpress site for the project itself. I decided to use the RDS service for the database handling and use the multisite network feature support by wordpress 3.0.

In the following posts I will write down my notes.

%d bloggers like this: