MySQL Bugs: #38920: Increasing innodb_log_file_size silently disables InnoDB Engine

Bug #38920	Increasing innodb_log_file_size silently disables InnoDB Engine
Submitted:	20 Aug 2008 16:09	Modified:	1 Aug 2009 7:00
Reporter:	Tammo Schuelke	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.0.32-7etch6	OS:	Linux (Debian etch)
Assigned to:		CPU Architecture:	Any

Description:
During the migration to a new server with more RAM, we increased innodb_buffer_pool_size to 4GB. innodb_log_files_in_group was set to four and we also increased innodb_log_file_size to 25% of innodb_buffer_pool_size, which means 1GB per log, 4GB in total.

As the manual states:
"The size in bytes of each log file in a log group. The combined size of log files must be less than 4GB on 32-bit computers. The default is 5MB. Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group."

(see http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html)

We are on 64 Bit though.
The server restarted without errors and reimporting our database dumps via mysql dbname < dbdump also worked fine.

Today we noticed that all tables were silently created as MyISAM and show engines showed that InnoDB was DISABLED. I then found this article which exactly describes the problem:
http://www.davidpashley.com/blog/databases/mysql/innodb-disabled

So there are in fact several problems related to this:

1) mysql dbname < dbdump doesn't return any warnings when importing the dump

2) the dump is silently being converted to MyISAM instead of just failing

2) even though we're on 64 Bit, mysqld failed to start after we deleted the InnoDB logfiles, because the combined logsize was exactly 4GB and it refused to create them

3) even though the InnoDB engine is disabled, show variables and show status return all InnoDB related rows; one might just notice that all status values are 0, which isn't very surprising on a newly started server

Maybe some of this is Debian specific, but the general problem doesn't seem to be.

It was a pure coincidence that we noticed this today because a transaction wasn't rolled back during a test.

How to repeat:
root@cmsdb01:/var/log# grep innodb_log_file_size /etc/mysql/my.cnf
innodb_log_file_size = 512M

root@cmsdb01:/var/log# ls -lh /var/lib/mysql/innodb/logs/ib_logfile*
-rw-rw---- 1 mysql mysql 5.0M 2006-12-19 18:39 /var/lib/mysql/ib_logfile0
-rw-rw---- 1 mysql mysql 5.0M 2006-12-19 18:39 /var/lib/mysql/ib_logfile1

mysql> show engines;
...
| InnoDB | DISABLED |
...

Import a dump which contains InnoDB tables (ENGINE=InnoDB).

mysql dbname
mysql> show table status;
...
| USER_DATA | MyISAM |
...

Suggested fix:
Make --show-warnings a default or fail if the table type is InnoDB and the InnoDB engine is not available.

Either allow creation of logfiles bigger than 4GB on 64 Bit or be more specific in the description. Is it allowed or is it not allowed?

Thank you for the bug report. Could you please read:

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

"#

NO_ENGINE_SUBSTITUTION

Control automatic substitution of the default storage engine when a statement such as CREATE TABLE or ALTER TABLE specifies a storage engine that is disabled or not compiled in. (Implemented in MySQL 5.0.8)

With NO_ENGINE_SUBSTITUTION disabled, the default engine is used and a warning occurs if the desired engine is known but disabled or not compiled in. If the desired engine is invalid (not a known engine name), an error occurs and the table is not created or altered.

With NO_ENGINE_SUBSTITUTION enabled, an error occurs and the table is not created or altered if the desired engine is unavailable for any reason (whether disabled or invalid)."

This should clearly be a default in my opinion, or there should be an ENGINE_SUBSTITUTION mode instead so it stops with an error by default.
Also, --show-warnings should be a default.

I also wonder why the transactions didn't fail in the first place? BEGIN/COMMIT/ROLLBACK were just silently skipped.

Thank you for the feedback.

This can be verified as "Make NO_ENGINE_SUBSTITUTION enabled by default and make show-warnings enabled by default"  Although this can break applications which rely on current behavior.