Why is INSERT ... ON DUPLICATE KEY UPDATE giving Duplicate entry 'x' for key 'PRIMARY' in MySQL?

Question

I have a table that I am importing thousands of user accounts from LDAP/AD into, and want it to update them if a duplicate is found in case there was a change. I'm using an auto-incremented surrogate key for the primary key, and Domain and Username as a unique key index. The primary key is used as a foreign key in another table. Also, this table contains a foreign key from the Locations table.

I've done a lot of reading and trial and error trying to get the MySQL INSERT ... ON DUPLICATE KEY UPDATE syntax to work, without success. Every time I try it, I get the same result (with the key incremented): Duplicate entry '27305' for key 'PRIMARY'. Note that there is no entry with this key in the table.

I'm using MySQL 5.6.30 with PHP 5.6.21.

Here is the table structure:

--Table structure for table users

CREATE TABLE `users` (
`Userid` bigint(20) UNSIGNED NOT NULL,
`Domain` varchar(17) NOT NULL,
`Username` varchar(25) NOT NULL,
`Firstname` varchar(25) DEFAULT NULL,
`Lastname` varchar(35) DEFAULT NULL,
`Realname` varchar(60) DEFAULT NULL,
`Dept` varchar(10) DEFAULT NULL,
`Email` varchar(50) DEFAULT NULL,
`Site` varchar(3) DEFAULT NULL,
`Location` bigint(20) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `users`
ADD PRIMARY KEY (`Userid`),
ADD UNIQUE KEY `Domain` (`Domain`,`Username`) USING BTREE,
ADD KEY `Realname` (`Realname`,`Dept`) USING BTREE,
ADD KEY `Email` (`Email`),
ADD KEY `Location` (`Location`),
ADD KEY `Firstname` (`Firstname`,`Lastname`,`Dept`) USING BTREE,
ADD KEY `Firstname_2` (`Firstname`,`Lastname`,`Location`);

ALTER TABLE `users`
MODIFY `Userid` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
AUTO_INCREMENT=27306;

ALTER TABLE `users`
ADD CONSTRAINT `Location_fk` FOREIGN KEY (`Location`) REFERENCES `locations`  
(`Locid`) ON UPDATE CASCADE;

Here is my insert statement according to PHP:

INSERT INTO `users` (`Domain`, `Username`, `Firstname`, `Lastname`,
`Realname`, `Dept`, `Email`, `Site`, `Location`) VALUES ('americas',
'JACLEAR', 'Foo', 'Bar', 'Foo Bar', 'IT', 'jlear@t-systems.com', '165', '0')
ON DUPLICATE KEY UPDATE `Userid`=LAST_INSERT_ID(`Userid`),
`Domain`=`Domain`, `Username`=`Username`, `Firstname`=VALUES(`Firstname`),
`Lastname`=VALUES(`Lastname`), `Realname`=VALUES(`Realname`),
`Dept`=VALUES(`Dept`), `Email`=VALUES(`Email`), `Site`=VALUES(`Site`),
`Location`=VALUES(`Location`);

Again, I've tried changing several things in the insert statement, but it always yields the same error. I've tried things such as removing the primary and unique keys from the update portion of the statement, removing VALUES from the unique key, removing LAST_INSERT_ID from the primary key, etc. I tried pasting the statement into the SQL console directly but didn't get anything more helpful for output -- it returned the same message: Duplicate entry '27305' for key 'PRIMARY'

Since running the statement on the SQL Console returned the same error, I don't think it has anything to do with PHP. I was going to mention that I am using PDO->prepare() and PDOStatement->execute().

It looks like I will have to do SELECT and then INSERT or UPDATE based on the SELECT result, unless someone can see what I'm doing wrong. But that will make the script a lot less efficient.

Any help would be appreciated. Thanks.

Answer 1

I tried to do an INSERT without the "ON DUPLICATE KEY UPDATE" and MySQL responded with a helpful error that actually pointed to the REAL problem. It told me that the value I was inserting for the foreign key (Location) was a 'violation'. This was the case because there was no record in the Location table with key 0. I updated my sample insert to use a valid Location foreign key and that worked. The update succeeded.

So in short, make sure you are inserting valid values! ;)

Answer 2

This is the culprit:

`Userid`=LAST_INSERT_ID(`Userid`)

You should not be having this clause here. LAST_INSERT_ID() in this statement is the last_insert_id from the previous insert statement that succeeded. If no such statement exists. It will be zero.

So in short, mysql is finding a duplicate for the domain column, trying to do an update instead and that update results in the primary key getting duplicated.