Bug #46773 Created table named roots: unable to delete primary key on it or even drop it
Submitted: 18 Aug 2009 3:19 Modified: 19 Sep 2009 5:30
Reporter: Philippe Adib Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: DDL Severity:S2 (Serious)
Version:MySQL 5.0.37-community-nt OS:Windows (XP Home SP3)
Assigned to: CPU Architecture:Any
Tags: Foreign key constraint, improper table naming, table naming

[18 Aug 2009 3:19] Philippe Adib
Description:
I created a table named roots in my db schema. I wanted to change the name of the primary key from index to something else, since index is a keyword in queries. However, I was not able to modify the key name, being presented with error 1025 every time.

After a long process of bug tracking, I ended up dropping all the tables in my schema and ultimately dropping the schema itself, since it was impossible for me to drop the table roots, being presented with error 1217 every time I tried that.

Now, I recreated that same schema, again with table roots, with only one column. I can only successfully create the table roots if I keep the same old name "index" as the name of the primary key. I also created another schema, again with a table roots to it, with one column named index as primary key. This time, I did not run into any problems modifying the key name or dropping the table.

I checked the schema information_schema, table KEY_COLUMN_USAGE, and found no constraint other than the primary keys for the two schema, one for each roots table's keys. When I dropped the problematic schema, the corresponding row in KEY_COLUMN_USAGE disappeared accordingly, so no ghosting there.

How to repeat:
I don't know how to repeat this bug. I tried recreating it, as described above, but the tests failed.

I also terminated all the processes I suspected could be holding on to a db resource and could maybe be causing the bug. And anyways, my db is local and I have not told other users about it.
[18 Aug 2009 5:47] Sveta Smirnova
Thank you for the report.

But version 5.0.37 is old and many bugs were fixed since. Please upgrade to current version 5.0.85, try with it and if problem still exists provide output of SHOW CREATE TABLE roots
[18 Aug 2009 18:37] Philippe Adib
Can't update right now, but when the time comes I intend to write back.

Phil
[19 Aug 2009 5:30] Sveta Smirnova
Thank you for the feedback.

No problem. Please update the report when you can.
[19 Sep 2009 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".