List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:April 15 2002 10:33pm
Subject:Re: auto_increment fields get mangled in some cases when doing an
alter table
View as plain text  
At 14:17 -0700 4/15/02, Tani Hosokawa wrote:
>If you have a table with an auto_increment column and one of the rows
>has the value set to 0, doing an ALTER TABLE will change that value to
>either 2147483647 or the next auto_increment value.

Storing anything other than positive integers in an AUTO_INCREMENT
column is not supported and you are playing with fire by doing so.

>
>mysql> create table blah (id int not null primary key 
>auto_increment,stuff char(128));
>Query OK, 0 rows affected (0.00 sec)
>
>mysql> insert into blah set stuff='blah';
>Query OK, 1 row affected (0.00 sec)
>
>mysql> select * from blah;
>+----+-------+
>| id | stuff |
>+----+-------+
>|  1 | blah  |
>+----+-------+
>1 row in set (0.01 sec)
>
>mysql> update blah set id=0;
>Query OK, 1 row affected (0.00 sec)
>Rows matched: 1  Changed: 1  Warnings: 0
>
>mysql> select * from blah;
>+----+-------+
>| id | stuff |
>+----+-------+
>|  0 | blah  |
>+----+-------+
>1 row in set (0.00 sec)
>
>mysql> alter table blah add morestuff int not null;
>Query OK, 1 row affected (0.00 sec)
>Records: 1  Duplicates: 0  Warnings: 0
>
>mysql> select * from blah;
>+----+-------+-----------+
>| id | stuff | morestuff |
>+----+-------+-----------+
>|  2 | blah  |         0 |
>+----+-------+-----------+
>1 row in set (0.00 sec)
>
>--
>tani hosokawa
>river styx internet

Thread
auto_increment fields get mangled in some cases when doing an alter tableTani Hosokawa15 Apr
  • Re: auto_increment fields get mangled in some cases when doing analter tablePaul DuBois16 Apr