Hi, There is one variable called innodb_autoinc_lock_mode. If the value is 0, this issue wont come. You might set it to 1 or 2.
-Partha www.mafiree.com ----- Original Message ---- From: Wagner Bianchi <wagnerbianch...@gmail.com> To: 杨涛涛 <david.y...@actionsky.com> Cc: Xavier Correyeur <x.correy...@free.fr>; mysql@lists.mysql.com Sent: Tue, 21 December, 2010 3:28:00 PM Subject: Re: Discontinued AUTO_INCREMENT problem.... Too curious...could you share a SHOW CREATE TABLE from this table as requested before? Best regards. -- Wagner Bianchi 2010/12/21 杨涛涛 <david.y...@actionsky.com> > 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.correy...@free.fr> > > > 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=yueliangdao0...@gmail.com > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org