auto_increment weirdness

Hi, for some reason, I have an auto_increment field that's magically
bumped up to the next biggest power of 2 after a big INSERT...SELECT
that inserts a bunch of tuples (into an empty table). Is this expected
behavior? I couldn't find any mention of this from the docs (using the
MySQL 5.4.3 beta).

Small reproducible test case:

First, generate some data: from bash, run "seq 30000 > /tmp/seq"

Next, run this in mysql:

create table x (a int auto_increment primary key, b int);
create table y (b int);
load data infile '/tmp/seq' into table y;
insert into x (b) select b from y;
show create table x;

This will show auto_increment = 32768 instead of 30000.

Is this a bug introduced in the beta? Has it been fixed in newer
releases? Couldn't find a mention in the bug database. Thanks in
advance.
--
Yang Zhang
http://www.mit.edu/~y_z/

--
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: auto_increment weirdness

Reproduced in 5.1.43. Could not reproduce it in 5.0.66


-----Original Message-----
From: Yang Zhang [mailto:yanghatespam@gmail.com]
Sent: Wednesday, February 17, 2010 6:05 PM
To: mysql@lists.mysql.com
Subject: auto_increment weirdness

Hi, for some reason, I have an auto_increment field that's magically
bumped up to the next biggest power of 2 after a big INSERT...SELECT
that inserts a bunch of tuples (into an empty table). Is this expected
behavior? I couldn't find any mention of this from the docs (using the
MySQL 5.4.3 beta).

Small reproducible test case:

First, generate some data: from bash, run "seq 30000 > /tmp/seq"

Next, run this in mysql:

create table x (a int auto_increment primary key, b int);
create table y (b int);
load data infile '/tmp/seq' into table y;
insert into x (b) select b from y;
show create table x;

This will show auto_increment =3D 32768 instead of 30000.

Is this a bug introduced in the beta? Has it been fixed in newer
releases? Couldn't find a mention in the bug database. Thanks in
advance.
--
Yang Zhang
http://www.mit.edu/~y_z/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com


This message contains confidential information and is intended only for the=
individual named. If you are not the named addressee, you are notified th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f you have received this e-mail by mistake and delete this e-mail from your=
system. E-mail transmission cannot be guaranteed to be secure or error-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate or incomplete, or contain viruses. The sender therefore does not accept=
liability for any loss or damage caused by viruses or errors or omissions =
in the contents of this message, which arise as a result of e-mail transmis=
sion. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089,=
USA, FriendFinder.com

--
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: auto_increment weirdness

--00504502c5c3a94d15047fef7fd2
Content-Type: text/plain; charset=ISO-8859-1

what is the value u see when you execute
select max(b) from y;

On Thu, Feb 18, 2010 at 1:33 PM, Gavin Towey wrote:

> Reproduced in 5.1.43. Could not reproduce it in 5.0.66
>
>
> -----Original Message-----
> From: Yang Zhang [mailto:yanghatespam@gmail.com]
> Sent: Wednesday, February 17, 2010 6:05 PM
> To: mysql@lists.mysql.com
> Subject: auto_increment weirdness
>
> Hi, for some reason, I have an auto_increment field that's magically
> bumped up to the next biggest power of 2 after a big INSERT...SELECT
> that inserts a bunch of tuples (into an empty table). Is this expected
> behavior? I couldn't find any mention of this from the docs (using the
> MySQL 5.4.3 beta).
>
> Small reproducible test case:
>
> First, generate some data: from bash, run "seq 30000 > /tmp/seq"
>
> Next, run this in mysql:
>
> create table x (a int auto_increment primary key, b int);
> create table y (b int);
> load data infile '/tmp/seq' into table y;
> insert into x (b) select b from y;
> show create table x;
>
> This will show auto_increment = 32768 instead of 30000.
>
> Is this a bug introduced in the beta? Has it been fixed in newer
> releases? Couldn't find a mention in the bug database. Thanks in
> advance.
> --
> Yang Zhang
> http://www.mit.edu/~y_z/
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=gtowey@ffn.com
>
>
> This message contains confidential information and is intended only for the
> individual named. If you are not the named addressee, you are notified that
> reviewing, disseminating, disclosing, copying or distributing this e-mail is
> strictly prohibited. Please notify the sender immediately by e-mail if you
> have received this e-mail by mistake and delete this e-mail from your
> system. E-mail transmission cannot be guaranteed to be secure or error-free
> as information could be intercepted, corrupted, lost, destroyed, arrive late
> or incomplete, or contain viruses. The sender therefore does not accept
> liability for any loss or damage caused by viruses or errors or omissions in
> the contents of this message, which arise as a result of e-mail
> transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA
> 94089, USA, FriendFinder.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
>
>

--00504502c5c3a94d15047fef7fd2--