Create New Table Copy ignores AUTO_INCREMENT

I don't remember seeing this behaviour before now. I copied a table from Heidi but it did not include the AUTO_INCREMENT flag for the wheel_id column.

Here is the SQL from the table copy that included columns and indexes.

CREATE TABLE db.db_wheels_copy ( wheel_id INT(10) UNSIGNED NOT NULL, init CHAR(4) NOT NULL, number MEDIUMINT(9) NOT NULL, axle_no SMALLINT(6) NOT NULL, install_date DATE NOT NULL DEFAULT '1000-01-01', removal_date DATE NOT NULL DEFAULT '9999-12-31', PRIMARY KEY (init, number, axle_no, install_date), INDEX wheel_id (wheel_id) ) COLLATE 'latin1_swedish_ci' ENGINE=InnoDB ROW_FORMAT=Compact COMMENT='';

Here is the create table code from HeidiSQL from the original table.

CREATE TABLE db_wheels ( wheel_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, init CHAR(4) NOT NULL, number MEDIUMINT(9) NOT NULL, axle_no SMALLINT(6) NOT NULL, install_date DATE NOT NULL DEFAULT '1000-01-01', removal_date DATE NOT NULL DEFAULT '9999-12-31', PRIMARY KEY (init, number, axle_no, install_date), INDEX wheel_id (wheel_id) ) COMMENT='' COLLATE='latin1_swedish_ci' ENGINE=InnoDB ROW_FORMAT=COMPACT;

I had the problem with version 9.3.0.5104 and upgraded to 9.4.0.5125 and still have the same problem.

Thanks

Tony

Can confirm that. The auto_increment clause is not included in the CREATE TABLE code which is fired when copying a table, at least not on my MariaDB 10.0.25 server here. Probably some version specific thing. Will check that.

I just saw in the code that HeidiSQL expected a primary or unique key on a column, otherwise it removed the auto-increment flag. Newer server version seem to support auto-increment columns with normal and even multi-column indexes. So I just added support for such constellations in r5156.

Thanks. I failed to mention that I am running MariaDB 10.1.21.