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 [at] m.gmane.org

--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.


=E6=9D=A8=E6=B6=9B
=E6=88=91=E5=8D=9A=E5=AE=A21:http://yueliangdao0608.cublog.c n
My =E6=88=91=E5=8D=9A=E5=AE=A22:http://yueliangdao0608.blog.51c to.com


2010/12/20 Xavier Correyeur <x.correyeur [at] free.fr>

> 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
>
> =3D=3D Example =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=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)
>
> =3D=3D End Example =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=3Dyueliangdao0608 [at] gmail.c om
>
>

--20cf304343007dd0420497e7cddb--

--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 =D1=EE=CC=CE=CC=CE <david.yang [at] actionsky.com>

> Hi.
> You can show us your show create table statement as well.
>
>
> =D1=EE=CC=CE
> =CE=D2=B2=A9=BF=CD1:http://yueliangdao0608.cublog.cn
> My =CE=D2=B2=A9=BF=CD2:http://yueliangdao0608.blog.51cto.com
>
>
> 2010/12/20 Xavier Correyeur <x.correyeur [at] 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
> >
> > =3D=3D Example =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=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)
> >
> > =3D=3D End Example =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/mysql?unsub=3Dyueliangdao0608 [at] gmail.c om
> >
> >
>

--000325555ad6ffa0c10497e8ab39--

Hi,=0A=0AThere 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.=0A=0A-Partha=
=0Awww.mafiree.com=0A=0A=0A=0A----- Original Message ----=0AFrom: Wagner Bi=
anchi <wagnerbianchijr [at] gmail.com>=0ATo: =E6=9D=A8=E6=B6=9B=E6=B6=9B <david.=
yang [at] actionsky.com>=0ACc: Xavier Correyeur <x.correyeur [at] free.fr>; mysql [at] lis=
ts.mysql.com=0ASent: Tue, 21 December, 2010 3:28:00 PM=0ASubject: Re: Disco=
ntinued AUTO_INCREMENT problem....=0A=0AToo curious...could you share a SHO=
W CREATE TABLE from this table as=0Arequested before?=0A=0ABest regards.=0A=
--=0AWagner Bianchi=0A=0A=0A2010/12/21 =E6=9D=A8=E6=B6=9B=E6=B6=9B <david.y=
ang [at] actionsky.com>=0A=0A> Hi.=0A> You can show us your show create table =
statement as well.=0A>=0A>=0A> =E6=9D=A8=E6=B6=9B=0A> =E6=88=91=E5=8D=9A=E5=
=AE=A21:http://yueliangdao0608.cublog.cn=0A> My =E6=88=91=E5=8D=9A=E5=AE=A2=
2:http://yueliangdao0608.blog.51cto.com=0A>=0A>=0A> 2010/12/20 Xavier Corre=
yeur <x.correyeur [at] free.fr>=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> > =3D=3D Example =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=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> > =3D=3D End Example =3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D=3D=3D=3D=3D=
=3D=3D=3D=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 [at] 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 [at] m.gmane.o rg

--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 [at] gmail.com



2010/12/21 partha sarathy <partha [at] mafiree.com>

> 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 <wagnerbianchijr [at] gmail.com>
> To: =E6=9D=A8=E6=B6=9B=E6=B6=9B <david.yang [at] actionsky.com>
> Cc: Xavier Correyeur <x.correyeur [at] free.fr>; mysql [at] 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 =E6=9D=A8=E6=B6=9B=E6=B6=9B <david.yang [at] actionsky.com>
>
> > Hi.
> > You can show us your show create table statement as well.
> >
> >
> > =E6=9D=A8=E6=B6=9B
> > =E6=88=91=E5=8D=9A=E5=AE=A21:http://yueliangdao0608.cublog.c n
> > My =E6=88=91=E5=8D=9A=E5=AE=A22:http://yueliangdao0608.blog.51c to.com
> >
> >
> > 2010/12/20 Xavier Correyeur <x.correyeur [at] 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
> > >
> > > =3D=3D Example =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=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)
> > >
> > > =3D=3D End Example =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> > >
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> > > http://lists.mysql.com/mysql?unsub=3Dyueliangdao0608 [at] gmail.c om
> > >
> > >
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=3Dyueliangdao0608 [at] gmail.c om
>
>

--20cf30434300d843480497f51cfb--