Wednesday, June 8, 2011

InnoDB storage engine failed ?

if you happened to find in your mysql server log the same error as below when you start up the mysql server; And you are not able to see the InnoDB storage engine as one of the enabled storage engine in your mysql server via 

mysql> show engines \G

Suppose Result
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Engine: InnoDB
     Support: YES
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Server error log:
110608 22:53:00 InnoDB: Initializing buffer pool, size = 2.0G
110608 22:53:00 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 268435456 bytes!
110608 22:53:00 [ERROR] Plugin 'InnoDB' init function returned error.
110608 22:53:00 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.


Solution:
1. Remove ibdata1 and  ib_logfile* from the location /var/lib/mysql on your server (depends on which location you install your mysql server data folder)

CAUTIONS:
Backup your innodb tables first, before you delete the ibdata1 file, in case you haven't enable the "innodb_file_per_table" option in your my.cnf

NOTES:
My case: this error happened when i have mass changes on the settings options inside the my.cnf file (mainly on the buffering, and caching values) and trying to restart the mysql server. i have existing InnoDB tables runnning. So i have to dump out the data to be restore again later


No comments:

Post a Comment