MySQL failures after changing innodb_flush_method to O_DIRECT and innodb_log_file_size

innodbMySQL

We changed innodb_flush_method to O_DIRECT, innodb_log_file_size to a bigger value, and 3 other things (innodb_buffer_pool_size, innodb_additional_mem_pool_size, innodb_log_buffer_size) while MySQL was stopped. Unfortunately DirectAdmin restarted MySQL before the log files were deleted.

Later, when we tried to fix the situation and stopped MySQL (and stopped DA) we deleted log files it didn't help – databases were corrupted. We finally created a new instance, and recovered everything from backups.

We had errors like these:

2013-09-06 01:40:24 7ff777a0c700 InnoDB: Error: page 4 log sequence number 40425569713
2013-09-06 01:40:24 7ff777a0c700 InnoDB: Error: page 3 log sequence number 40425569736
2013-09-06 01:40:24 7ff777a0c700 InnoDB: Error: page 28 log sequence number 40425569736
2013-09-06 01:40:24 7ff777a0c700 InnoDB: Error: page 4 log sequence number 40425542325

Can someone tell me what really happened? We've seen bad things happening when you change innodb_log_file_size and don't delete log files, but we were able to recover from it then.

Was setting innodb_flush_method to O_DIRECT the cause here?

Best Answer

The source of the problem was removing the log files before flushing everything out of the logs

You should run this to shut it down (using innodb_fast_shutdown)

mysql ... -ANe"SET GLOBAL innodb_fast_shutdown = 0"
service mysql stop

After shutting down like this, then you can put in new logs. That way, there is nothing in terms of InnoDB Crash Recovery to worry about from the old logs.

InnoDB Architecture

InnoDB Architecture

What innodb_flush_method does is have the InnoDB storage engine cache any needed blocks to the double write buffer (See the chart). The error messages you have are about the sequence numbers from the logs being misaliged because of the incomplete way you made new logs.

Related Question