Issue changing innodb_log_file_size

Question

I haven't done much tweaking in the past so this might be relatively easy however I am running into issues. This is what I do:

1. Stop MySQL
2. Edit my.cnf (changing innodb_log_file_size)
3. Remove ib_logfile0/1
4. Start MySQL

Starts fine however all InnoDB tables have the .frm file is invalid error, the status shows InnoDB engine is disabled so I obviously go back, remove the change and everything works again.

I was able to change every other variable I've tried but I can't seem to find out why InnoDB fails to start even after removing the log files. Am I missing something?

Thanks.

Edit: Pasting of the log below - looks like it still seems to find the log file even though they are not there?

Shutdown:

090813 10:00:14  InnoDB: Starting shutdown...
090813 10:00:17  InnoDB: Shutdown completed; log sequence number 0 739268981
090813 10:00:17 [Note] /usr/sbin/mysqld: Shutdown complete

Startup after making the changes:

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 268435456 bytes!
090813 11:00:18 [Warning] 'user' entry '[email protected]' ignored in --skip-name-resolve mode.
090813 11:00:18 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.81-community-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Edition (GPL)
090813 11:00:19 [ERROR] /usr/sbin/mysqld: Incorrect information in file: './XXXX/User.frm'
090813 11:00:19 [ERROR] /usr/sbin/mysqld: Incorrect information in file: './XXXX/User.frm'
090813 11:00:19 [ERROR] /usr/sbin/mysqld: Incorrect information in file: './XXXX/User.frm'

Its just a spam of the same error until I correct it

When it did start after it recreated the log files so it must be looking in the same spot I am.

Answer 1

First, I must point that before any chage in InnoDB data files, one should read 13.2.5. Adding, Removing, or Resizing InnoDB Data and Log Files.

The steps you pointed are almost correct. It´s recommended to backup before this types of changes. Lets see what happened to you:

The error messages

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

are caused by changing the size of innodb_log_file_size and dont deleting the old files. Probably you forgot to delete de ib_logfile0/1 the first time you ran mysqld after the changing. The message

090813 11:00:18 [Note] /usr/sbin/mysqld: ready for connections.

shows that you resolved this problem (by removing ib_logfiles). But, on removing them you create the other problem. Data corrupt for XXXX\User.frm or some problem with InnoDB Engine.

To be sure that InnoDB is enabled, run this command on a mysql prompt:

show variables like "%inno%";

In the result list must have "have_innodb = YES". Sometimes when InnoDB can not start (value is NO or DISABLED) the Incorrect information in file message appears.
- If this is the problem, you have changed something else that prevents InnoDB Engine to start properly (datadir´s permissions or tmpdir´s permissions, other innodb variables (you didnt show the complete log.), etc.). Review the changes or put the .cnf files (before and after).
- If the InnoDB Engine is YES then you have to repair the table. (If it´s this case, add a comment and I´ll add the information here. I´m too lazy to do it now.)