Discontinued AUTO_INCREMENT problem....

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=gcdmg-mysql-2@m.gmane.org

Re: Discontinued AUTO_INCREMENT problem....

--20cf304343007dd0420497e7cddb
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Hi.
You can show us your show create table statement as well.


杨涛
我博å®=A21:http://yueliangdao0608.cublog.c n
My 我博å®=A22:http://yueliangdao0608.blog.51c to.com


2010/12/20 Xavier Correyeur

> Hi everybody !
>
> A have a discontinued AUTO_INCREMENT sequence when i insert data in a tab=
le
> 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 ===================3D=
==============
>
> -- 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=3DInnoDB
> DEFAULT CHARSET=3Dlatin1;
> 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 =3D> OK
>
> mysql> select max(`id`) from test;
> +-----------+
> | max(`id`) |
> +-----------+
> | 100 |
> +-----------+
> 1 row in set (0.00 sec)
>
> --INSERT DATA WITH CHECKED SELECTREQUEST 2 =3D> 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 =3D> should be 100 + 100 =3D 200
> -- =3D> 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 ===================
===============3D
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=3Dyueliangdao0608@gmail.c om
>
>

--20cf304343007dd0420497e7cddb--

Re: Discontinued AUTO_INCREMENT problem....

--000325555ad6ffa0c10497e8ab39
Content-Type: text/plain; charset=GB2312
Content-Transfer-Encoding: quoted-printable

Too curious...could you share a SHOW CREATE TABLE from this table as
requested before?

Best regards.
--
Wagner Bianchi


2010/12/21 ÑîÌÎÌÎ

> 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
>
> > 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 ===================
===============3D
> >
> > -- 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=3DInnoDB
> > DEFAULT CHARSET=3Dlatin1;
> > 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 =3D> OK
> >
> > mysql> select max(`id`) from test;
> > +-----------+
> > | max(`id`) |
> > +-----------+
> > | 100 |
> > +-----------+
> > 1 row in set (0.00 sec)
> >
> > --INSERT DATA WITH CHECKED SELECTREQUEST 2 =3D> 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 =3D> should be 100 + 100 =3D 200
> > -- =3D> 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 =================3D=
================
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/mysql?unsub=3Dyueliangdao0608@gmail.c om
> >
> >
>

--000325555ad6ffa0c10497e8ab39--

Re: Discontinued AUTO_INCREMENT problem....

Hi, There is one variable called innodb_autoinc_lock_mode. If the valu=
e is 0, this =0Aissue wont come. You might set it to 1 or 2. -Partha=
=0Awww.mafiree.com ----- Original Message ----=0AFrom: Wagner Bi=
anchi =0ATo: 杨涛æ¶=9B yang@actionsky.com>=0ACc: Xavier Correyeur ; mysql@lis=
ts.mysql.com=0ASent: Tue, 21 December, 2010 3:28:00 PM=0ASubject: Re: Disco=
ntinued AUTO_INCREMENT problem.... Too curious...could you share a SHO=
W CREATE TABLE from this table as=0Arequested before? Best regards.=0A=
--=0AWagner Bianchi =0A2010/12/21 杨涛æ¶=9B ang@actionsky.com> > Hi.=0A> You can show us your show create table =
statement as well.=0A>=0A>=0A> 杨涛=0A> 我博=E5=
®¢1:http://yueliangdao0608.cublog.cn=0A> My 我博å®=A2=
2:http://yueliangdao0608.blog.51cto.com=0A>=0A>=0A> 2010/12/20 Xavier Corre=
yeur =0A>=0A> > Hi everybody !=0A> >=0A> > A have a di=
scontinued AUTO_INCREMENT sequence when i insert data in a=0A> table=0A> > =
with a 100 (or more) items SELECT request.=0A> > The problem (or situation)=
is reproductible, you can see an example=0A> below.=0A> >=0A> > Anybody co=
uld explain this to me ?=0A> >=0A> > Cheers=0A> > XC=0A> >=0A> > My MySQL v=
ersion : Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486)=0A> > using =
readline 6.1=0A> >=0A> > == Example ===========3D=
==================== ===0A> >=0A=
> > -- CREATE test table=0A> >=0A> > mysql> create table test(`id` int(10) =
unsigned NOT NULL AUTO_INCREMENT,=0A> > `name` VARCHAR(255), `test` int(10)=
, KEY `keyid`(`id`)) ENGINE=3DInnoDB=0A> > DEFAULT CHARSET=3Dlatin1;=0A> > =
Query OK, 0 rows affected (0.00 sec)=0A> >=0A> > -- INSERT DATA FROM ANOTHE=
R TABLE=0A> >=0A> > mysql> insert into test(name) select `name`from user li=
mit 100;=0A> > Query OK, 100 rows affected (0.01 sec)=0A> > Records: 100 D=
uplicates: 0 Warnings: 0=0A> >=0A> > -- AUTO_INCREMENT ID CHECK =3D> OK=0A=
> >=0A> > mysql> select max(`id`) from test;=0A> > +-----------+=0A> > | ma=
x(`id`) |=0A> > +-----------+=0A> > | 100 |=0A> > +-----------+=0A> > =
1 row in set (0.00 sec)=0A> >=0A> > --INSERT DATA WITH CHECKED SELECTREQUES=
T 2 =3D> DATA INSERT OK=0A> >=0A> > mysql> insert into test(name) select `n=
ame` from userlimit 100;=0A> > Query OK, 100 rows affected (0.01 sec)=0A> >=
Records: 100 Duplicates: 0 Warnings: 0=0A> >=0A> > -- AUTO_INCREMENT ID =
CHECK =3D> should be 100 + 100 =3D 200=0A> > -- =3D> 27 IDs are unset, firs=
t ID of 2nd insert is 128 instead of 101=0A> > -- No field between 100 and =
128=0A> >=0A> > mysql> select max(`id`) from test;=0A> > +-----------+=0A> =
> | max(`id`) |=0A> > +-----------+=0A> > | 227 |=0A> > +-----------+=
=0A> > 1 row in set (0.00 sec)=0A> >=0A> > == End Example =====
==================== =====3D=
=====0A> >=0A> >=0A> >=0A> > --=0A> > MySQL General Mailing List=0A=
> > For list archives: http://lists.mysql.com/mysql=0A> > To unsubscribe:=
=0A> > http://lists.mysql.com/mysql?unsub=3Dyueliangdao0608@gmail.c om=0A> >=
=0A> >=0A>=0A

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Discontinued AUTO_INCREMENT problem....

--20cf30434300d843480497f51cfb
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Hi.
This is a good point for this issue.
David Yeung, In China, Beijing.
My First Blog:http://yueliangdao0608.cublog.cn
My Second Blog:http://yueliangdao0608.blog.51cto.com
My Msn: yueliangdao0608@gmail.com



2010/12/21 partha sarathy

> 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
> To: 杨涛æ¶=9B
> Cc: Xavier Correyeur ; 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 杨涛æ¶=9B
>
> > Hi.
> > You can show us your show create table statement as well.
> >
> >
> > 杨涛
> > 我博å®=A21:http://yueliangdao0608.cublog.c n
> > My 我博å®=A22:http://yueliangdao0608.blog.51c to.com
> >
> >
> > 2010/12/20 Xavier Correyeur
> >
> > > 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 ===================
===============3D
> > >
> > > -- CREATE test table
> > >
> > > mysql> create table test(`id` int(10) unsigned NOT NULL AUTO_INCREMEN=
T,
> > > `name` VARCHAR(255), `test` int(10), KEY `keyid`(`id`)) ENGINE=3DInno=
DB
> > > DEFAULT CHARSET=3Dlatin1;
> > > 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 =3D> OK
> > >
> > > mysql> select max(`id`) from test;
> > > +-----------+
> > > | max(`id`) |
> > > +-----------+
> > > | 100 |
> > > +-----------+
> > > 1 row in set (0.00 sec)
> > >
> > > --INSERT DATA WITH CHECKED SELECTREQUEST 2 =3D> 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 =3D> should be 100 + 100 =3D 200
> > > -- =3D> 27 IDs are unset, first ID of 2nd insert is 128 instead of 10=
1
> > > -- No field between 100 and 128
> > >
> > > mysql> select max(`id`) from test;
> > > +-----------+
> > > | max(`id`) |
> > > +-----------+
> > > | 227 |
> > > +-----------+
> > > 1 row in set (0.00 sec)
> > >
> > > == End Example =================
=================3D
> > >
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> > > http://lists.mysql.com/mysql?unsub=3Dyueliangdao0608@gmail.c om
> > >
> > >
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=3Dyueliangdao0608@gmail.c om
>
>

--20cf30434300d843480497f51cfb--