MySQL Bugs: #62269: CREATE TABLE...ENGINE=innodb; Does not fail if InnoDB couldn't load, MyISAM used

Bug #62269	CREATE TABLE...ENGINE=innodb; Does not fail if InnoDB couldn't load, MyISAM used
Submitted:	26 Aug 2011 15:46	Modified:	27 Aug 2011 9:21
Reporter:	Brad House	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.1.54	OS:	Linux (Ubuntu 11.04 amd64)
Assigned to:		CPU Architecture:	Any

Description:
When there is insufficient system memory for InnoDB to load, such as if the innodb_buffer_pool_size cannot be allocated (say some other process on the system consumed more resources than it should have, but is otherwise a legitimate value for the system), the innodb module will not load but MySQL will continue to start, but give this output:

110826 11:37:27  InnoDB: Initializing buffer pool, size = 97.7G
110826 11:37:27  InnoDB: Error: cannot allocate 104857616384 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 38079360 bytes. Operating system errno: 12
InnoDB: Check if you should increase the swap file or
InnoDB: ulimits of your operating system.
InnoDB: On FreeBSD check you have compiled the OS with
InnoDB: a big enough maximum process size.
InnoDB: Note that in most 32-bit computers the process
InnoDB: memory space is limited to 2 GB or 4 GB.
InnoDB: We keep retrying the allocation for 60 seconds...
110826 11:38:27InnoDB: Fatal error: cannot allocate the memory for the buffer pool
110826 11:38:27 [ERROR] Plugin 'InnoDB' init function returned error.
110826 11:38:27 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
110826 11:38:27 [Note] Event Scheduler: Loaded 0 events
110826 11:38:27 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.54-1ubuntu4'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu) 

Now if you create a table with the 'ENGINE=innodb'  argument, it returns success as if nothing was wrong, but it creates a MyISAM table instead.  Obviously this leads to significant issues for applications that rely on things like the Serializable isolation level.

How to repeat:
Easiest way is to set innodb_buffer_pool_size to a number higher than you can allocate, then use the mysql cli and run  'CREATE TABLE foo (bar INTEGER) ENGINE=innodb;' ... You'll note it gets created as a MyISAM table instead of failing with an error!

Suggested fix:
The Create Table should return a failure and not perform some action that was not requested.

Could you please try the sql_mode: NO_ENGINE_SUBSTITUTION. Thanks.

That does appear to correct the issue.  But it is a concern that this is not the default.  Blind substitution is obviously not desirable

This is documented at http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_no_engine_substitution:

"As of MySQL 5.1.12, storage engines can be pluggable at runtime, so the distinction between disabled and invalid no longer applies. All unavailable engines are treated the same way:

With NO_ENGINE_SUBSTITUTION disabled, for CREATE TABLE the default engine is used and a warning occurs if the desired engine is unavailable. For ALTER TABLE, a warning occurs and the table is not altered.

With NO_ENGINE_SUBSTITUTION enabled, an error occurs and the table is not created or altered if the desired engine is unavailable."

So, this is not a bug.