mysql auto increment error

Question

I have a simple

INSERT INTO t1 (fields...) (SELECT fields... FROM t2);

it inserts around 8.000 rows and the auto increment value of the table is set to ~16.000, but it's not exactly 2x as much. I don't include the auto incremented id in the query, and the tables are truncated. Can it be a bug? I have version 5.5.24. Why is that, and how can I avoid this?

CREATE TABLE `order` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `` int(4) NOT NULL COMMENT '',
  `` int(6) NOT NULL COMMENT '',
  `` varchar(255) NOT NULL COMMENT '',
  `` varchar(255) NOT NULL COMMENT '',
  `` bigint(10) unsigned NOT NULL COMMENT '',
  `` int(5) unsigned NOT NULL COMMENT '',
  `` int(5) unsigned NOT NULL COMMENT '',
  `` int(8) unsigned NOT NULL COMMENT '',
  `` varchar(255) DEFAULT NULL COMMENT '',
  `` int(3) DEFAULT NULL COMMENT '',
  `` int(3) DEFAULT NULL COMMENT '',
  `` date NOT NULL COMMENT '',
  `` date DEFAULT NULL,
  `` date DEFAULT NULL COMMENT '',
  `` int(5) DEFAULT NULL COMMENT '',
  `` varchar(2) DEFAULT NULL COMMENT '',
  `` int(5) DEFAULT NULL COMMENT '',
  `` varchar(255) DEFAULT NULL,
  `` varchar(255) DEFAULT NULL,
  `` char(1) DEFAULT NULL,
  `` datetime DEFAULT NULL,
  `` int(10) unsigned DEFAULT NULL,
  `` datetime DEFAULT NULL,
  `` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `` (``),
  KEY `` (``),
  KEY `` (``),
  CONSTRAINT `` FOREIGN KEY (``) REFERENCES `users` (`id`),
  CONSTRAINT `` FOREIGN KEY (``) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8440 DEFAULT CHARSET=utf8

Thanks.

Is this an initial insert to a new table or may there be some deleted records? — juergen d, Jul 09 '13 at 11:23
Truncating should work. See [here](http://sqlfiddle.com/#!2/dd8e2/6) — juergen d, Jul 09 '13 at 11:36
have you checked the auto_increment system variables that are in the fiddle? — juergen d, Jul 09 '13 at 11:38
I have never seen this fiddle thing before, sorry... I don't really know yet what to do with it. — user2511599, Jul 09 '13 at 11:43
Never mind, you already answered in your other comment, that the auto_inc_offset and increment are set to 1. Could you provide a `SHOW CREATE TABLE t1`? — Jens, Jul 09 '13 at 11:51
I guess I'll never find out why this happens, but I can do the following: http://stackoverflow.com/questions/8923114/mysql-reset-auto-increment — user2511599, Jul 12 '13 at 12:06

Jens · Answer 1 · 2013-07-16T07:57:03.947

This could be caused by your server's configuration (/etc/my.cnf for example): You can set the auto_increment_increment and the auto_increment_offset.

If the first is set to x, you will have (last_id + x) for your next id. If the second id is set to y, you start always with y (and then add xeach time).

More probably: You just deleted all rows in your table and inserted again. This way the auto_increment value is not set to 1 again. You have to TRUNCATE tablename to reset this counter again (if you really want to delete all rows).

Edit:

Just saw your CREATE TABLE statement. If you really created your table with AUTO_INCREMENT=8440 in the options, than of course, after inserting 8000 rows you will have an ID at 16000. If this is just a SHOW CREATE TABLE after the insert statement, I still don't know what's happening.

I have a basic wampserver config, I don't think there are any non-default `auto_increment_offset` or `auto_increment_increment` values set. Yes I have checked it and both are set to `1`. — user2511599, Jul 09 '13 at 11:33

mysql auto increment error

1 Answers1

Linked