469,946 Members | 1,805 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,946 developers. It's quick & easy.

ALTER TABLE MODIFY adds duplicate keys

Hello!

I have a weird problem... The application I use issues this SQL from
time to time:

alter table t modify id int(6) unique not null auto_increment;

The problem is that this SQL adds a key on id every time it is run!

mysql> show keys;

+-------+------------+----------+--------------+-------------+--
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
+-------+------------+----------+--------------+-------------+--
| t | 0 | PRIMARY | 1 | id |
| t | 0 | id | 1 | id |
| t | 0 | id_2 | 1 | id |
| t | 0 | id_3 | 1 | id |
| t | 0 | id_4 | 1 | id |
| t | 0 | id_5 | 1 | id |
| t | 0 | id_6 | 1 | id |
| t | 0 | id_7 | 1 | id |
+-------+------------+----------+--------------+-------------+--

(output after 7 runs of the above SQL query)

Is this normal behaviour or is it a bug? I would say it is a bug but I
haven't found anything on the topic on the Internet.
Is there an equivalent SQL that does not create duplicate keys?

I want my application to take care of fields data type and I don't want
to 'know' the current data type - the application should just issue
'alter table modify' query and change data type as appropriate. It all
works well except for this glitch.

The database in question runs very slowly once it has 32 indexes... :(

Could someone verify this behaviour on MySQL 3.x database?

Any comments are welcome - I could really use some help here.

Thank you!

Anze

Jul 23 '05 #1
0 1363

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

21 posts views Thread by Johan Tibell | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.