Bug #41191 Error when Create Index before Create Foreign Key to a table column
Submitted: 3 Dec 2008 1:44 Modified: 3 Jan 2009 4:26
Reporter: Sam OK Email Updates:
Status: No Feedback Impact on me:
Category:MySQL Server Severity:S1 (Critical)
Version:5 OS:Any
Assigned to: CPU Architecture:Any

[3 Dec 2008 1:44] Sam OK
Server: Windows XP (or Windows Server)
MySQL server: 5.0.15
Client Program: MySQL Query Browser
Connectors: PHP-MySQL class library

Indexed an INTEGER column and then create Foreign Key index to the column can occasionally (1 out of 100 records created) auto-update a record FK column where the updated record's FK will minus 128 or minus 32768 of the actual value.

How to repeat:
I've created 2 tables t1, t2.

t1.id is the primary key for table t1.
t2.fk_id is the foreign key for table t2 referenced to table t1.id.

Before setting the t2.fk_id as foreign key, i created index for t2.fk_id.
After that, i set the t2.fk_id as foreign key referenced to the t1.id.

For a hundred record created or updated, it will some how change the value of the t2.fk_id (-128 or -32768 of the actual value of t2.fk_id). The larger the volume the more it occurs.

The t2.fk_id is only inserted once into the record when a record is created.
There is no updating of the t2.fk_id done anywhere else.

Suggested fix:
remove the foreign key index of t2.fk_id, and then remove the index created for t2.fk_id.

recreate t2.fk_id as foreign key again. 

Only this time, do not create index for t2.fk_id before creating foreign key references in t2.fk_id. Foreign Key will create its own index.
[3 Dec 2008 4:26] Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with a newer version, 5.0.67. If the problem is repeatable there, please, send a complete test case, with all the data, or at least exact CREATE TABLE statements used.
[4 Jan 2009 0: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".