Bug #3853 Primary key corruption while inserting
Submitted: 22 May 2004 0:33 Modified: 22 May 2004 1:23
Reporter: Chris Irwin Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.1-alpha, 4.0.18 OS:Windows (Win 2003 Enterprise, WinXP Pro)
Assigned to: Dean Ellis CPU Architecture:Any

[22 May 2004 0:33] Chris Irwin
Description:
When inserting on a table that has similar field names as the primary key, mysql updates all of the primary key's values (set as an auto increment) with the wrong data, usually a "1". 

Here is a quick example of the results after inserting to this table. RoomID below is the primary key set to auto increment...

RoomID  RoomTypeID  CustomerID
-----------------------------
1       5           4
1       3           4
1       7           4

How to repeat:
Here is the EXACT table (Note the primary key is named 'RoomID' and the Foreign Key is named 'RoomTypeID'):

CREATE TABLE `rooms` (
  `RoomID` smallint(5) unsigned NOT NULL auto_increment,
  `RoomTypeID` smallint(5) unsigned NOT NULL default '0',
  `CustomerID` smallint(5) unsigned NOT NULL default '0',
  `LocationID` smallint(5) unsigned NOT NULL default '0',
  `Added` datetime NOT NULL default '0000-00-00 00:00:00',
  `LastModified` timestamp NOT NULL,
  `ModifiedBy` smallint(5) unsigned NOT NULL default '0',
  `FloorLevel` tinyint(4) NOT NULL default '0',
  `LevelOfSoil` tinyint(1) unsigned NOT NULL default '0',
  `FloorType` smallint(5) unsigned NOT NULL default '0',
  `CleaningType` smallint(5) unsigned NOT NULL default '0',
  `SqFeet` mediumint(8) unsigned NOT NULL default '0',
  `RoomName` char(20) NOT NULL default '',
  PRIMARY KEY  (`RoomID`)
)

Here is the SQL query. NOTE, these  three queries are not executed all at once or inline, but instead are programmically looped and individually parsed with dynamic variables (values) that are added during execution: 

INSERT INTO rooms(RoomTypeID,CustomerID)
VALUES(5,4);

INSERT INTO rooms(RoomTypeID,CustomerID)
VALUES(3,4);

INSERT INTO rooms(RoomTypeID,CustomerID)
VALUES(7,4);

RESULTS!!!

RoomID  RoomTypeID  CustomerID
-----------------------------
1       5           4
1       3           4
1       7           4

As you can see, the RoomID is not specified in any of the the insert querys, although RoomTypeID is, so you would think that your not going to alter data in the RoomID field. When this query is executed, the "RoomID" field gets inserted a value of "1" instead of it getting auto incremented. When doing this SAME query without the RoomTypeID, the results are expected and the RoomID gets auto incremented.

INSERT INTO rooms(CustomerID)
VALUES(4);

INSERT INTO rooms(CustomerID)
VALUES(4);

INSERT INTO rooms(CustomerID)
VALUES(4);

RESULTS!!!

RoomID  CustomerID
-----------------------------
1       4
2       4
3       4

I have been able to duplicate this on MySQL 4.1.1-alpha and 4.0.18

Suggested fix:
Hey, thats your job.

Obviously this kind of insert has some kind of bug when inserting with similar field names that are possibly related to the primary/foreign keys. I can give further info and supply the actual raw code if requested.
[22 May 2004 1:23] Dean Ellis
I cannot repeat this with 4.0.18/WindowsXP using your SQL above.

You could attach your my.ini/my.cnf (if you use one), along with the full SHOW CREATE TABLE output.  I would also like to see the SQL you are using to retrieve your data.  If the SQL you have already given consistently causes the problem then I need to ensure our tables and server settings match perfectly (and that your query is actually retrieving the RoomIDs).