[Dbmail] MySQL 4.1 and 2.0rc5 Create Tables

[John M. Brown]

I recently had a problem running the create tables script (both innoDB and
regular MyISAM) on MySQL 4.1.1-alpha (standard).

The problem is that the "DEFAULT '0' NOT NULL auto_increment" are not
valid options when used in conjunction...you should remove the "DEFAULT
'0'" when you specify that a column should be an auto_increment column.

Example:

--< OLD     >--------------
DROP TABLE IF EXISTS aliases;
CREATE TABLE aliases (
        alias_idnr bigint(21) DEFAULT '0' NOT NULL auto_increment,
        alias varchar(100) NOT NULL default '',
        deliver_to varchar(250) NOT NULL default '',
        client_idnr bigint(21) NOT NULL default '0',
        PRIMARY KEY  (alias_idnr),
        INDEX (alias),
        INDEX (client_idnr)
) TYPE=InnoDB;


--< CORRECT >--------------
DROP TABLE IF EXISTS aliases;
CREATE TABLE aliases (
        alias_idnr bigint(21) NOT NULL auto_increment,
        alias varchar(100) NOT NULL default '',
        deliver_to varchar(250) NOT NULL default '',
        client_idnr bigint(21) NOT NULL default '0',
        PRIMARY KEY  (alias_idnr),
        INDEX (alias),
        INDEX (client_idnr)
) TYPE=InnoDB;


I've been wrong before, but that's what made it work for me.