Duplicate entry for key 'PRIMARY' for every INSERT query I am trying in a particular table

Question

I have the following table structure (using 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 simply fetch a few recent values (I've changed the IPs 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 this, I'll try to execute a simple INSERT statement:

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 already exist in the table. It results in:

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

Selecting the LAST_INSERT_ID gives:

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

If that wasn't already weird behaviour; I'll try to insert an already 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'

I'm now officially stumped at this behaviour from MySQL. All other queries are working fine in all other processes that are running. The error is occurring only when I try to INSERT to 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 resolve this? The error has been occurring since today afternoon and I never faced such a problem before.

P.S.: 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 is able to perform the INSERT query.

Answer 1

A force insertion of a new row (with id different from 253691) seems to have solved this problem for now. I am still not sure as to why the error occurred.

The command I used was:

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

and the system was working normally again (for all 4 users, on every connection). I'll leave the question as still unanswered since I still don't know what caused the problem. My best guess would be that it is a possible MySQL bug.