MySQL Bugs: #6877: MySQL should give an error if the requested table type is not available

Bug #6877	MySQL should give an error if the requested table type is not available
Submitted:	29 Nov 2004 15:40	Modified:	20 Jun 2005 22:28
Reporter:	Heikki Tuuri	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0	OS:	Any (All)
Assigned to:	Antony Curtis	CPU Architecture:	Any

Description:
Hi!

Currently, 5.0 crashes if one has

skip-innodb

in my.cnf, and one tries to create a table of TYPE=InnoDB :). But when Gluh's bug fix to 4.1 gets approved, 5.0 will probably just give a 'warning'. A warning is not enough. MySQL should give an error if a transactional table type is requested, but is not available. We waste time answering questions on the mailing list why ROLLBACK does not work even though the table is created as InnoDB type.

Regards,

Heikki

How to repeat:
Put

skip-innodb

to my.cnf.

Do

CREATE TABLE t(a INT) TYPE=InnoDB;

Suggested fix:
Give an error, not a warning.

Patch committed - bk commit - 4.1 tree (acurtis:1.2282) BUG#6877

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

We should NOT by default give any errors if the requested engine doesn't exist.
Doing so, would make life harder for MySQL users, as they following things wouldn't work:

- One could not do a mysqldump and restore to a MySQL server without the requested table type.
- One could not have slaves that used MyISAM tables instead of InnoDB tables

In 4.1 and 5.0 we give a warning when MySQL uses another table engine, so any users using the MySQL client or GUI client should notice the problem when they create a table.
The reason we still get a lot of questions on the email list is probably mostly because everyone hasn't yet switched to 4.1 and 5.0 :)

I have now replied to Antony's patch and suggested some things to do it better (and more compatible with the current release)

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/internals/25660

Ok to push in 5.0 after changes described in review

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/internals/26143

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Pushed to 5.0.8 repository

New SQL_MODE - NO_ENGINE_SUBSTITUTION
Prevents automatic substitution of storage engine when the requested storage engine is disabled or not compiled in.

Documented in 5.0.8 changelog and SQL_MODE section of refman.