On Oct 1, 6:20 pm, flarosa <fr...@franklarosa.com> wrote:
> I maintain a schema creation script which specifies the InnoDB engine
> type for most tables, so I can have referential integrity and so
> forth.
>
> I gave my script to another developer who ran it against a MySQL
> implementation in which the InnoDB engine was not available.
> (Actually, it was supposed to be available, but the server didn't load
> it because it was asking for too much memory).
>
> When the other developer ran my script, it generated no errors.
> Instead, it simply decided to create my InnoDB tables as MyISAM
> tables! We didn't find the problem until weeks later.
>
> MySQL made two grave mistakes here. One, it started without InnoDB
> support even though its configuration file specified InnoDB support.
> It should have failed to start, and let us read the log files to
> figure out why. Barring that, it should have, at least, failed to
> create a table that explicitly specified InnoDB as the engine type. It
> should not have decided on its own to make the table a different type!
> I asked for InnoDB for a reason.
>
> Is there any way I can configure it so that this doesn't happen again?
>
> Frank

I took the liberty of reading the manual for you and found this under
"create table:"

<http://dev.mysql.com/doc/refman/5.0/en/create-table.html>

<snip>

If a storage engine is specified that is not available, MySQL uses the
default engine instead. Normally, this is MyISAM. For example, if a
table definition includes the ENGINE=BDB option but the MySQL server
does not support BDB tables, the table is created as a MyISAM table.
This makes it possible to have a replication setup where you have
transactional tables on the master but tables created on the slave are
non-transactional (to get more speed). In MySQL 5.0, a warning occurs
if the storage engine specification is not honored.

Engine substitution can be controlled by the setting of the
NO_ENGINE_SUBSTITUTION SQL mode, as described in Section 5.2.6, "SQL
Modes".

</snip>

Then, I used my mouse to click on the link for Section 5.2.6, "SQL
Modes" and found this:

<snip>

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).

</snip>

So, had you taken the time to read the manual, you would have known
this already.