109

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

jack
  • 1,705
  • 5
  • 21
  • 24

1 Answers1

127

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.
DarckBlezzer
  • 173
  • 1
  • 1
  • 7
womble
  • 95,029
  • 29
  • 173
  • 228
  • 1
    Hi, thanks for your answer, I tried your method but still failed. – jack Jan 19 '10 at 03:30
  • 44
    Deleting both ib_logfile0 and ib_logfile1 solved the problem for me. – Temnovit Apr 17 '12 at 16:33
  • 1
    And for me. Thank you so much, we have our wiki back! – Stephen Kennedy Apr 20 '12 at 18:46
  • 1
    Since I'm relocating a newly installed db, I skip step 1-4 and start with 5 - and it works :) – Limbo Peng Jul 27 '14 at 15:13
  • Thanks @Temnovit deleting these 2 files also did the trick for me. – bashan Sep 02 '15 at 16:35
  • On Debian, look for the log files in `/var/lib/mysql/` – Twilite Apr 15 '19 at 09:29
  • If anyone gets stuck on this with `Docker` and `FROM mysql:5.5.46`, the only solution is to delete all images and containers, manually import the database, do a `mysqldump` of that, then add that mysqldump to `ADD db-dump.sql /docker-entrypoint-initdb.d`. Otherwise it will throw the above errors, but the log files will not exist anywhere to be deleted. The database dump has to match *perfectly* or you're gonna have a really bad time. – userqwert Jul 03 '20 at 08:15