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

• Submitted: 16 Oct 2006 0:19
• Modified: 19 Jun 2010 17:58
• Reporter: Arjen Lentz
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 5.0.27-BK, 5.0.22, 4.1.21
• OS: Any
• Assigned to: Marko Mäkelä
• CPU Architecture: Any
• Tags: auto_increment, innodb

[16 Oct 2006 0: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 0: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 11:15] Valeriy Kravchuk

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

[8 Nov 2006 15: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 13: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 8:27] Marko Mäkelä

The fix will be in 5.1 and 5.0.

[19 Apr 2007 2: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 11:34] Bugs System

Pushed into 5.0.42

[26 Apr 2007 11:36] Bugs System

Pushed into 5.1.18-beta

[30 Apr 2007 14:43] MC Brown

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

[5 May 2010 15:15] Bugs System

Pushed into 5.1.47 (revid:[e-mail]-20100505145753-ivlt4hclbrjy8eye) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[6 May 2010 2:28] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.

[28 May 2010 6:11] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100524190136-egaq7e8zgkwb9aqi) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (pib:16)

[28 May 2010 6:39] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100524190941-nuudpx60if25wsvx) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[28 May 2010 7:07] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100524185725-c8k5q7v60i5nix3t) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[29 May 2010 22:43] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

[17 Jun 2010 12:16] Bugs System

Pushed into 5.1.47-ndb-7.0.16 (revid:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 13:03] Bugs System

Pushed into 5.1.47-ndb-6.2.19 (revid:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 13:44] Bugs System

Pushed into 5.1.47-ndb-6.3.35 (revid:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)