List:General Discussion« Previous MessageNext Message »
From:杨涛涛 Date:December 21 2010 8:55am
Subject:Re: Discontinued AUTO_INCREMENT problem....
View as plain text  
Hi.
   You can show us your show create table statement as well.


杨涛
我博客1:http://yueliangdao0608.cublog.cn
My 我博客2:http://yueliangdao0608.blog.51cto.com


2010/12/20 Xavier Correyeur <x.correyeur@stripped>

> Hi everybody !
>
> A have a discontinued AUTO_INCREMENT sequence when i insert data in a table
> with a 100 (or more) items SELECT request.
> The problem (or situation) is reproductible, you can see an example below.
>
> Anybody could explain this to me ?
>
> Cheers
> XC
>
> My MySQL version : Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486)
> using readline 6.1
>
> == Example =================================
>
> -- CREATE test table
>
> mysql> create table test(`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
> `name` VARCHAR(255), `test` int(10), KEY `keyid`(`id`)) ENGINE=InnoDB
> DEFAULT CHARSET=latin1;
> Query OK, 0 rows affected (0.00 sec)
>
> -- INSERT DATA FROM ANOTHER TABLE
>
> mysql> insert into test(name) select `name`from user limit 100;
> Query OK, 100 rows affected (0.01 sec)
> Records: 100  Duplicates: 0  Warnings: 0
>
> -- AUTO_INCREMENT ID CHECK => OK
>
> mysql> select max(`id`) from test;
> +-----------+
> | max(`id`) |
> +-----------+
> |      100 |
> +-----------+
> 1 row in set (0.00 sec)
>
> --INSERT DATA WITH CHECKED SELECTREQUEST 2 => DATA INSERT OK
>
> mysql> insert into test(name) select `name` from userlimit 100;
> Query OK, 100 rows affected (0.01 sec)
> Records: 100  Duplicates: 0  Warnings: 0
>
> -- AUTO_INCREMENT ID CHECK => should be 100 + 100 = 200
> -- => 27 IDs are unset, first ID of 2nd insert is 128 instead of 101
> -- No field between 100 and 128
>
> mysql> select max(`id`) from test;
> +-----------+
> | max(`id`) |
> +-----------+
> |      227 |
> +-----------+
> 1 row in set (0.00 sec)
>
> == End Example =================================
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>

Thread
Discontinued AUTO_INCREMENT problem....Xavier Correyeur20 Dec
  • Re: Discontinued AUTO_INCREMENT problem....杨涛涛21 Dec
    • Re: Discontinued AUTO_INCREMENT problem....Wagner Bianchi21 Dec
      • Re: Discontinued AUTO_INCREMENT problem....partha sarathy21 Dec
        • Re: Discontinued AUTO_INCREMENT problem....杨涛涛22 Dec