Create New Table Copy ignores AUTO_INCREMENT

[expired user #10566]'s profile image [expired user #10566] posted 7 years ago in General Permalink

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

ansgar's profile image ansgar posted 7 years ago Permalink

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.

Code modification/commit d4b294f from ansgarbecker, 7 years ago, revision 5156
Support auto_increment flag on indexes other than primary or unique, on copy table dialog. If that still fails, give the user a hint to select the right index. See https://www.heidisql.com/forum.php?t=23383
ansgar's profile image ansgar posted 7 years ago Permalink

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.

[expired user #10566]'s profile image [expired user #10566] posted 7 years ago Permalink

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

Please login to leave a reply, or register at first.