MySQL Bugs: #21099: MySQL 5.0.22 silently creates MyISAM tables even though InnoDB specified.

Bug #21099	MySQL 5.0.22 silently creates MyISAM tables even though InnoDB specified.
Submitted:	17 Jul 2006 18:00	Modified:	12 Nov 2009 20:15
Reporter:	Bryce Nesbitt	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S2 (Serious)
Version:	5.0.22	OS:	Any
Assigned to:	Jon Olav Hauglid	CPU Architecture:	Any
Tags:	innodb, my.cnf, myisam

Description:
From one of our developers: The problem that was causing members to be able to make overlapping reservations is, with near certainty, the fact that foreign key
constraints were not being enforced on the overlapping test.  This is
because the tables were using the default MyISAM engine instead of the
InnoDB engine.

And THIS is because of an inconsistency in the file size of the InnoDB
log file in actuality and its setting in the my.cnf configuration file. 
This seems to have caused the db server to disable the InnoDB engine,
which produces the behavior that all tables are silently created as
MyISAM tables without regard to the create statement engine
specification.

How to repeat:
Configure my.conf as above.
Load a InnoDB dump.
Magically, your tables will be MyISAM.

Causes silently creates MyISAM to be silently converted to InnoDB on restore.

Attachment: my.cnf (application/octet-stream, text), 2.19 KiB.

Here's an example table create which gets converted:

DROP TABLE IF EXISTS `CCS_STATUS`;
CREATE TABLE `CCS_STATUS` (
  `STATUS_ID` int(11) NOT NULL auto_increment,
  `STATUS_NAME` varchar(127) default NULL,
  PRIMARY KEY  (`STATUS_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--

What did we expect?
1) An error message when the InnoDB table type was ignored.
2) An error message when the FK check was created, on a MyISAM table that does not support FK checks.

Note OS redhat-release-3WS-1

Thank you for the bug report. Please read:
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.25-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DROP TABLE IF EXISTS `CCS_STATUS`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `CCS_STATUS` (
    ->   `STATUS_ID` int(11) NOT NULL auto_increment,
    ->   `STATUS_NAME` varchar(127) default NULL,
    ->   PRIMARY KEY  (`STATUS_ID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ERROR 1289 (HY000): The 'InnoDB' feature is disabled; you need MySQL built with 'InnoDB' to have it working
mysql> SELECT @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION | 
+------------------------+
1 row in set (0.01 sec)

mysql>

For a multi-megabyte database, we are not using the interactive console:

$ cat test.sql

DROP TABLE IF EXISTS `CCS_STATUS`;
CREATE TABLE `CCS_STATUS` (
  `STATUS_ID` int(11) NOT NULL auto_increment,
  `STATUS_NAME` varchar(127) default NULL,
  PRIMARY KEY  (`STATUS_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

indigo@elcapitan /tmp $ mysql -u root innodb_bug_test < test.sql

indigo@elcapitan /tmp $ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2327 to server version: 5.0.22-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use innodb_bug_test;
Database changed
mysql> show create table CCS_STATUS; +------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                           |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CCS_STATUS | CREATE TABLE `CCS_STATUS` (
  `STATUS_ID` int(11) NOT NULL auto_increment,
  `STATUS_NAME` varchar(127) default NULL,
  PRIMARY KEY  (`STATUS_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Note the lack of warning or error.
Actually I might have expected this to be an ERROR, not a WARNING anyway.

How can we get warnings out of the "mysql < file" case?

Note lack of warning even in (single) verbose mode.  Double verbose does show the warning:

$ mysql -v -u root innodb_bug_test < test.sql
--------------
DROP TABLE IF EXISTS `CCS_STATUS`
--------------

--------------
CREATE TABLE `CCS_STATUS` (
  `STATUS_ID` int(11) NOT NULL auto_increment,
  `STATUS_NAME` varchar(127) default NULL,
  PRIMARY KEY  (`STATUS_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
--------------

I started the server with the relevant sql_mode, then nothing with the
client use:
miguel@hegel:~/dbs/5.0> libexec/mysqld --skip-innodb --sql_mode="NO_ENGINE_SUBSTITUTION"
060717 15:34:03 [Note] libexec/mysqld: ready for connections.
Version: '5.0.25-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

miguel@hegel:~/dbs/5.0> bin/mysqladmin -uroot create innodb_bug_test
miguel@hegel:~/dbs/5.0> bin/mysql -u root innodb_bug_test < /home/miguel/test.sql
ERROR 1289 (HY000) at line 2: The 'InnoDB' feature is disabled; you need MySQL built with 'InnoDB' to have it working
miguel@hegel:~/dbs/5.0>

Not the lack of warning or error messages from our logs.
I'm sure what YOU did causes warnings.  But whatever WE did does not.  Note the silent conversion of InnoDB tables to MyISAM tables.

I will get more information from our developer, on exactly what puts mysql into the state where it silently converts the table types.  Apparently it is not as simple as a total disabling of the InnoDB table type.

Thanks for the feedback. Please ask for the developer for to start the
server with the sql_mode I mentioned and verify if still the silence
conversion is done. Thanks in advance.

No, that's not how we run the server.  We want InnoDB *enabled*, not *disabled*.  We will not test with InnoDB disabled.

I am not asking you for to disable InnoDB instead to start the server with
sql_mode NO_ENGINE_SUBSTITUTION that will prevents with the eventual disabling
of the InnoDB engine not to make the silence conversion.

But that's not what this bug report is about.  We are reporting a bug that there is no warning in the *default* configuration.  The bug report is about the lack of warning -- the NO_ENGINE_SUBSTITUTION option is not in use, and not relevant to this bug report.

As the developer working with Bryce, here is how this happened as near as I can tell (have not yet verified with a new install):

1. Start with a mysql 5.0.22 install with these conditions:
* InnoDB compiled in (the default)
* a database containing at least one InnoDB table
* NO my.cnf file
* the server has been started at least once so that the ibdata1 & ib_logfile0 & ib_logfile1 files have been created

2. add a my.cnf containing the following line
innodb_log_file_size = 64M

3. restart the db server. There may have been an error message at this point, probably about invalid data somewhere

4. drop and recreate the database (to fix the invalid data)

5. note that the tables in the db are MyISAM

I believe what happened is that as the innodb log file, ib_logfile0, was created prior to the my.cnf file being created, it used a default size which was different from the size then configured. This caused the InnoDB engine to not start up, and somehow the --skip-innodb setting was enabled. Under --skip-innodb, I believe silent conversion of InnoDB to MyISAM is the normal behavior.

The relevant line in the mysql log file is this:

InnoDB: Error: log file /opt/mysql/data/ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 67108864 bytes!
060712 18:13:45 [Note] /opt/mysql/bin/mysqld: ready for connections.

In my opinion, the bug is that --skip-innodb is set if the log file size doesn't match my.cnf. This results in a server that is thought to be using InnoDB, but actually is not. I would suggest that the server refuse to start at all in this case, or the log file be automatically modifed to match the configuration file if possible.

Please, send the exact content of the error log after you attempted to start server with changed InnoDB log size. I suppose, messages will be clear enough...

Hi!

This is a known problem. If mysqld is not able to start an engine (for example, if the InnoDB log size is specified wrong in my.cnf), mysqld just gives a warning if you try to create a table in the inactive engine, and creates a MyISAM table instead!

mysqld should give an error, because it is often fatal error if a table is created non-transactional.

Workaround: in CREATE TABLE, always carefully check the warnings returned by MySQL.

Regards,

Heikki

I started mysqld with a wrong log size:

InnoDB: Error: log file .\ib_logfile0 is of different size 0 104857600 bytes
InnoDB: than specified in the .cnf file 0 10485760 bytes!
060727  8:51:42 [Note] mysqld: ready for connections.
Version: '5.0.22-log'  socket: ''  port: 3306  Source distribution

Then:

mysql> create table k(a int) engine = innodb;
Query OK, 0 rows affected, 1 warning (0.20 sec)

mysql> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1266 | Using storage engine MyISAM for table 'k' |
+---------+------+-------------------------------------------+
1 row in set (0.02 sec)

mysql>

In my opinion, NO_ENGINE_SUBSTITUTION should be the default. It could be overriden in replication slaves where the user wants InnoDB tables to be replicated to MyISAM tables.

Regards,

Heikki

Hi, Bryce & all.

The meat of this bug, as I understand it, is that the 'mysql' client does not display any warnings when run in batch mode from the command line, by default.  Nor with the --verbose option.  One must specify --show-warnings explicitly for the warnings to be shown.

While I agree that this is a serious problem, I don't think it can be fixed in 5.0  (current GA version) without risking affecting someone's current setup.

My recommendation on this is to fix 5.1 so that:
- warnings are printed to stderr, not stdout, in batch mode; this will allow them to be handled separately from the actual command output (currently --show-warnings writes to stdout, mixed in with the normal output)
- warnings are printed by default in batch mode; --skip-show-warnings can be specified if this is not desired

For 5.0, a workaround may be to add this to your my.cnf:

[mysql]
show_warnings

Or, for this specific example of the problem:

[mysqld]
sql_mode=no_engine_substitution

Or, to add --show-warnings to scripts selectively, if adding it to my.cnf causes problems.

I've changed the Category for this bug to "Server: Command-line clients", and set it to "Feature request"; I've added it to our internal TODO list, and will ask product management to schedule it for 5.1.  It's not a big task, but this will ensure it doesn't get overlooked.

Regards,

Timothy

I question the default of "engine substitution".  When we specify:

(...) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I think we really mean it.  If we don't get InnoD, the application will be horribly broken, and severe data corruption will result.  It seems a very unsafe default to silently convert table types.

Who benefits from "engine substitution", and what would break were the default to change to "no engine substitution"?

Bryce,

I too would prefer a default of NO_ENGINE_SUBSTITUTION, along with Heikki and others.  Changing defaults, even on these historical "features" which cause
surprising behavior, inevitably trips someone up.  I'm sure there's someone
out there who is happily importing InnoDB dumps into servers w/o InnoDB support
and they'd be upset if their production system stopped working with a minor
upgrade.

I will ask our architecture team to consider this for 5.1, where such an incompatible change is appropriate.

Regards,

Timothy

I'm also quite nonplussed about the fact that MySQL will in some cases silently create MyISAM tables when told to create something else without even notifying the user.  I've been bitten twice by this.  Somebody asking for engine X has very good reasons for wanting engine X and not engine Y, especially when Y defaults to something not ACID compliant, and especially if there is code depending on features of engine X.

Please fix this by notifying the user in all cases with an error and defaulting 'engine substitution' to OFF.

I'm also quite nonplussed about the fact that MySQL will in some cases silently create MyISAM tables when told to create something else without even notifying the user.  I've been bitten twice by this.  Somebody asking for engine X has very good reasons for wanting engine X and not engine Y, especially when Y defaults to something not ACID compliant, and especially if there is code depending on features of engine X.

Please fix this by notifying the user in all cases with an error and defaulting 'engine substitution' to OFF.

What's more reasonable?

Forcing people who want ENGINE=InnoDB to be extra careful?
Or forcing people who specify ENGINE=InnoDB without really meaning it to refactor?

I claim the later is better.  If MySQL ignores what I tell it, that's a bug.

It should NOT be a stronger warning.  It should be an ERROR.

Fixing category on this bug/feature-request, which is really about whether NO_ENGINE_SUBSTITUTION should be set by default, which is not a behavior that is up to the mysql client.

The issue with the mysql command-line client not printing warnings to stderr has been split out to Bug #44732.

Note the patch for Bug#19027 which adds an option to control the load policy of built in plugins from the command line.

I suppose NO_ENGINE_SUBSTITUTION works on table level, and it might still make sense to have this as the default setting.

The plugin load policy can prevent the server from starting if any mandatory plugin fails to initialize.

If needed I can probably make it so that if --innodb=FORCE then for all innodb tables, NO_ENGINE_SUBSTITUTION is true. This would prevent SE substitution on tables where localized table space errors would lead to a trigger of the sql_mode policy, if that ever is the case.

No engine substitution, HOWEVER it is accomplished, needs to be the default behavior.

If I specify an engine, I want that engine, not some other engine.

It has been almost three years since this bug was reported.

Forget debating the merits the NO_ENGINE_SUBSTITUTION setting default. There is no sensible reason to even have this be configurable.  If you ask for an engine that's not there, it should BAIL.  The Engine matters, which is exactly why it should never substitute -- not silently -- not even noisily -- never.  If there is some obscure backup/restore setup out there that breaks, so be it.  MySQL has never shied away from breaking existing code (on accident or on purpose) so this particular issue hardly seems like the place to draw the line.  Break 'em.  On purpose.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/75711

Ok to push.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/76168

2793 Jon Olav Hauglid	2009-06-12
      Followup to Bug#34197 and Bug#21099
      
      This is an update of 6 result files in the funcs_1 test suite, 
      that by mistake were not updated when NO_ENGINE_SUBSTITUTION 
      was added to strict mode (21099) and COMMENT field in mysql.proc 
      was changed to text data type (34197).

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090617073019-azsawauatv99124t) (version source revid:jon.hauglid@sun.com-20090616092759-pelq3sqh8ql0qxoa) (merge vers: 5.4.4-alpha) (pib:11)

Noted in 5.4.4 changelog.

The TRADITIONAL SQL mode now includes NO_ENGINE_SUBSTITUTION.

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/86397

Pushed to mysql-next-mr-runtime (version 5.5.0)

Noted in 5.5.0 changelog.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091102151658-j9o4wgro47m5v84d) (version source revid:alik@sun.com-20091023064702-2f8jdmny61bdl94u) (merge vers: 6.0.14-alpha) (pib:13)

Noted in 6.0.14 changelog.

Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091103113702-p61dlwc6ml6fxg18) (merge vers: 5.5.0-beta) (pib:13)

The log file size problem is Bug#13494.