Tuesday, March 29, 2016

mysql - InnoDB: Error: log file ./ib_logfile0 is of different size



I just added the following lines in /etc/mysql/my.cnf after I converted one database to use InnoDB engine.



innodb_buffer_pool_size = 2560M
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 16

innodb_flush_method = O_DIRECT


But it raise "ERROR 2013 (HY000) at line 2: Lost connection to MySQL server during query" error restarting mysqld. And mysql error log shows the following



InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 268435456 bytes!
100118 20:52:52 [ERROR] Plugin 'InnoDB' init function returned error.
100118 20:52:52 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
100118 20:52:52 [ERROR] Unknown/unsupported table type: InnoDB

100118 20:52:52 [ERROR] Aborting


So I commented out this line



# innodb_log_file_size  = 256M


And it restarted mysql successfully.




I wonder what's the "5242880 bytes of log file" showed in mysql error? It's the first database on InnoDB engine on this server so when and where is that log file created? In this case, how can I enable innodb_log_file_size directive in my.cnf?



EDIT



I tried to delete /var/lib/mysql/ib_logfile0 and restart mysqld but it still failed. It now shows the following in error log.



100118 21:27:11  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 256 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200

InnoDB: Error: log file ./ib_logfile1 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 268435456 bytes!


Resolution



It works now after deleted both ib_logfile0 and ib_logfile1 in /var/lib/mysql


Answer



InnoDB is insanely picky about it's config; if something's not right, it'll just give up and go home. To make a change to the log file size without data loss:





  1. Revert any config changes you've made to the log file size and start MySQL again.

  2. In your running MySQL: SET GLOBAL innodb_fast_shutdown=0;

  3. Stop MySQL

  4. Make the configuration change to the log file size.

  5. Delete both log files.

  6. Start MySQL. It will complain about the lack of log files, but it'll create them and all will be well.


No comments:

Post a Comment

linux - How to SSH to ec2 instance in VPC private subnet via NAT server

I have created a VPC in aws with a public subnet and a private subnet. The private subnet does not have direct access to external network. S...