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

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.

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

True, but if "engine substitution" is the default, than IMO, that's wrong.


--
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

On 2 Oct, 07:22, "Martijn Tonies" <m.ton...@upscene.removethis.com>
wrote:
> > So, had you taken the time to read the manual, you would have known
> > this already.
>
> True, but if "engine substitution" is the default, than IMO, that's wrong.
>
> --
> Martijn Tonies
> Database Workbench - development tool for MySQL, and more!
> Upscene Productionshttp://www.upscene.com
> My thoughts:http://blog.upscene.com/martijn/
> Database development questions? Check the forum!http://www.databasedevelopmentforum.com

Why is it wrong? It is what the manual states. Maybe you think that
not having stricT SQL mode set is wrong and that you should have to
configure it to do other things.

The manual states what is the default and tells you haw to change it.
What is wrong about that?

"Captain Paralytic" <paul_lautman@yahoo.com> wrote in message
news:1191327544.797196.47130@22g2000hsm.googlegrou ps.com...
> On 2 Oct, 07:22, "Martijn Tonies" <m.ton...@upscene.removethis.com>
> wrote:
> > > So, had you taken the time to read the manual, you would have known
> > > this already.
> >
> > True, but if "engine substitution" is the default, than IMO, that's
wrong.
> >
> Why is it wrong? It is what the manual states. Maybe you think that
> not having stricT SQL mode set is wrong and that you should have to
> configure it to do other things.
>
> The manual states what is the default and tells you haw to change it.
> What is wrong about that?

It's a silly default :-)

IMO, "strict sql" should be the default as well. MySQL is slowly getting
more and more strict, which is a good thing. It's just strange that it
wasn't
strict when it started


--
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

On Tue, 2 Oct 2007 14:47:34 +0200, Martijn Tonies wrote:
>
> "Captain Paralytic" <paul_lautman@yahoo.com> wrote in message
> news:1191327544.797196.47130@22g2000hsm.googlegrou ps.com...
>> On 2 Oct, 07:22, "Martijn Tonies" <m.ton...@upscene.removethis.com>
>> wrote:
>> > > So, had you taken the time to read the manual, you would have known
>> > > this already.
>> >
>> > True, but if "engine substitution" is the default, than IMO, that's
> wrong.
>> >
>> Why is it wrong? It is what the manual states. Maybe you think that
>> not having stricT SQL mode set is wrong and that you should have to
>> configure it to do other things.
>>
>> The manual states what is the default and tells you haw to change it.
>> What is wrong about that?
>
> It's a silly default :-)
>
> IMO, "strict sql" should be the default as well. MySQL is slowly getting
> more and more strict, which is a good thing. It's just strange that it
> wasn't
> strict when it started

So noted. I'm sure the developers will give your opinion all due
consideration when looking into whether to change established behavior.

--
35. I will not grow a goatee. In the old days they made you look diabolic.
Now they just make you look like a disaffected member of Generation X.
--Peter Anspach's list of things to do as an Evil Overlord

> >> > > So, had you taken the time to read the manual, you would have known
> >> > > this already.
> >> >
> >> > True, but if "engine substitution" is the default, than IMO, that's
> > wrong.
> >> >
> >> Why is it wrong? It is what the manual states. Maybe you think that
> >> not having stricT SQL mode set is wrong and that you should have to
> >> configure it to do other things.
> >>
> >> The manual states what is the default and tells you haw to change it.
> >> What is wrong about that?
> >
> > It's a silly default :-)
> >
> > IMO, "strict sql" should be the default as well. MySQL is slowly getting
> > more and more strict, which is a good thing. It's just strange that it
> > wasn't
> > strict when it started
>
> So noted. I'm sure the developers will give your opinion all due
> consideration when looking into whether to change established behavior.

Hmm, not too quick. Backwards compatibility is a good thing...

The MySQL crew and I disagree on some of those with regard to
major and minor releases and making things deprecated...

I must say I don't remember if the MySQL 5 (Windows) installer has
some kind of option whether you want "strict SQL" or not, that would
be nice.


--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

flarosa <frank@franklarosa.com> wrote in news:1191277219.708757.143970
@w3g2000hsg.googlegroups.com:


> 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?

do a "SHOW ENGINES" and make sure InnoDB isn't reported as DISABLED