Geek Answers Handbook

Duplicate entry for the "PRIMARY" key for each INSERT request that I am trying to use in a specific table

I have the following table structure (using the SHOW CREATE command just now):

 CREATE TABLE `ipstats` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `ip` VARCHAR(15) NOT NULL, `online` ENUM('n','y') NOT NULL DEFAULT 'y', `last_used` DATETIME NOT NULL DEFAULT '1981-09-30 00:00:00', PRIMARY KEY (`id`), UNIQUE INDEX `ip` (`ip`) ) COLLATE='utf8_general_ci' ENGINE=MyISAM AUTO_INCREMENT=253691;

Now I just retrieve the last few values ​​(I changed the IP addresses for demonstration):

 mysql> SELECT * FROM ipstats ORDER BY id DESC LIMIT 10; +--------+----------------+--------+---------------------+ | id | ip | online | last_used | +--------+----------------+--------+---------------------+ | 253690 | 10.204.102.38 | n | 2013-10-19 14:14:33 | | 253689 | 10.188.124.196 | n | 2013-10-19 10:46:25 | | 253688 | 10.166.124.194 | n | 2013-10-19 16:49:40 | | 253687 | 10.250.137.166 | n | 2013-10-19 13:51:56 | | 253686 | 10.221.102.39 | n | 2013-10-19 14:13:03 | | 253685 | 10.129.102.57 | n | 2013-10-19 18:45:20 | | 253684 | 10.214.102.39 | n | 2013-10-19 03:43:55 | | 253683 | 10.31.142.41 | n | 2013-10-19 17:27:08 | | 253682 | 10.41.142.154 | n | 2013-10-19 00:52:11 | | 253681 | 10.41.124.84 | n | 2013-10-19 10:37:12 | +--------+----------------+--------+---------------------+

After that, I will try to execute a simple INSERT :

 INSERT INTO `ipstats` (`ip`, `last_used`) VALUES ( '10.3.100.244', NOW() ) ON DUPLICATE KEY UPDATE `online` = 'y', `last_used` = NOW(), `id` = LAST_INSERT_ID(`id`)

where the value 10.3.100.244 does not yet exist in the table. This leads to:

 ERROR 1062 (23000): Duplicate entry '253691' for key 'PRIMARY'

Choosing LAST_INSERT_ID gives:

 mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 0 | +------------------+

If it was no longer strange behavior; I will try to insert an existing IP entry:

 mysql> INSERT INTO `ipstats` (`ip`, `last_used`) VALUES ( '10.204.102.38', NOW() ) ON DUPLICATE KEY UPDATE `online` = 'y', `last_used` = NOW(), `id` = LAST_INSERT_ID(`id`); ERROR 1062 (23000): Duplicate entry '253691' for key 'PRIMARY'

Now I am officially configured for this behavior in MySQL. All other requests work fine in all other running processes. The error occurs only when trying to INSERT in the ipstats table. I even tried the following:

 mysql> UPDATE ipstats SET `online` = 'n', `last_used` = NOW(), `id` = LAST_INSERT_ID(`id`) WHERE ip = '10.204.102.38'; Affected rows: 1 Found rows: 0 Warnings: 0 Duration for 1 query: 0.000 sec. mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 253690 | +------------------+ Affected rows: 0 Found rows: 1 Warnings: 0 Duration for 1 query: 0.000 sec. mysql> INSERT INTO `ipstats` (`ip`, `last_used`) VALUES ( '10.204.102.38', NOW() ) ON DUPLICATE KEY UPDATE `online` = 'y', `last_used` = NOW(), `id` = LAST_INSERT_ID(`id`); Error 1062 (23000): Duplicate entry '253691' for key 'PRIMARY'

What could be the problem? How can i solve this? The error has been happening from today, and I have never encountered such a problem before.

PS . The error occurs with all user accounts in my MySQL installation. There are 4 users (including root) who can access the database. None of them can execute an INSERT request.

+7
sql mysql duplicates sql-insert primary-key
source share
1 answer

Forcing a new line (with an id different from 253691 ) seems to have solved this problem 253691 now. I'm still not sure why the error occurred.

The command I use is:

 INSERT INTO `ipstats` (id, ip, online, last_used) VALUES ( 253699, '10.204.102.38', 'n', NOW() );

and the system worked fine again (for all 4 users, with each connection). I will leave this question unanswered since I still don’t know what caused the problem. My best guess would be that this is a possible MySQL error.

+1
source share

More articles:


All Articles