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.