MySQL Bugs: #41191: Error when Create Index before Create Foreign Key to a table column

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:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
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.

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.

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".