Bug #23313 AUTO_INCREMENT=# not reported back for InnoDB tables

• Submitted: 16 Oct 2006 2:19
• Modified: 30 Apr 2007 16:43
• Reporter: Arjen Lentz
• Status: Closed
• Category: Server: InnoDB
• Severity: S2 (Serious)
• Version: 5.0.27-BK, 5.0.22, 4.1.21
• OS: Any
• Assigned to: Marko Mäkelä
• Tags: innodb, auto_increment

[16 Oct 2006 2:19] Arjen Lentz

Description:
AUTO_INCREMENT=# is honoured by CREATE TABLE, but is not reported back properly from an
InnoDB table by mysqldump or SHOW CREATE TABLE. Naturally this causes significant
problems for backups and replication slaves.

The information in http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html does
not indicate there might be an exception to the expected mysqldump / SHOW CREATE TABLE
behaviour for InnoDB.

How to repeat:
use test;
DROP TABLE IF EXISTS auto_inc_innodb;

CREATE TABLE auto_inc_innodb (
  t1_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  t1_name VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (t1_id),
  KEY (t1_name)
) ENGINE=innoDB AUTO_INCREMENT = 1000;

INSERT INTO auto_inc_innodb (t1_name) VALUES('MySQL');
INSERT INTO auto_inc_innodb (t1_name) VALUES('MySQL');
INSERT INTO auto_inc_innodb (t1_name) VALUES('MySQL');

SELECT * from auto_inc_innodb;
SHOW CREATE TABLE auto_inc_innodb\G

Suggested fix:
There was an earlier problem (http://bugs.mysql.com/19025) with this for MyISAM, which
was fixed. Apparently the fix did affect behaviour for InnoDB. It might be prudent to
check all main storage engines, and include something in the testsuite.

[16 Oct 2006 2:24] Arjen Lentz

Sorry for the confusion, of course this should not affect backups/replication.
However, the documented behaviour from 5.0.3 is that InnoDB does accept the
AUTO_INCREMENT=# but loses the info on server restart. From the above, it does appear to
not report back the info before server restart either, so it's still unexpected
behaviour.

[16 Oct 2006 13:15] Valeriy Kravchuk

Thank you for a bug report. Verified just as described with 5.0.27-BK on Linux.

[8 Nov 2006 16:47] Heikki Tuuri

A possible fix is to put to ha_innodb.cc a similar function that ha_myisam.cc in 5.0
contains, though we may need to retrieve the auto-inc value using some InnoDB function
rather than the variable in the handle:

void ha_myisam::update_create_info(HA_CREATE_INFO *create_info)
{
  ha_myisam::info(HA_STATUS_AUTO | HA_STATUS_CONST);
  if (!(create_info->used_fields & HA_CREATE_USED_AUTO))
  {
    create_info->auto_increment_value=auto_increment_value;
  }
  if (!(create_info->used_fields & HA_CREATE_USED_RAID))
  {
    create_info->raid_type= raid_type;
    create_info->raid_chunks= raid_chunks;
    create_info->raid_chunksize= raid_chunksize;
  }
  create_info->data_file_name=data_file_name;
  create_info->index_file_name=index_file_name;
}

Assigning this to Marko.

[10 Jan 2007 14:02] Marko Mäkelä

I made a patch for 5.1, and I believe that the fix for 5.0 is identical.

[2 Apr 2007 10:27] Marko Mäkelä

The fix will be in 5.1 and 5.0.

[19 Apr 2007 4:57] Timothy Smith

Patch queued into 5.0- and 5.1-maint, will be merged up to top repos as soon as possible.

[26 Apr 2007 13:34] Bugs System

Pushed into 5.0.42

[26 Apr 2007 13:36] Bugs System

Pushed into 5.1.18-beta

[30 Apr 2007 16:43] MC Brown

A note has been added to the 5.0.42 and 5.1.18 changelogs.