MySQL Bugs: #38749: Duplicate error on INSERT - primary key auto increment column

Bug #38749	Duplicate error on INSERT - primary key auto increment column
Submitted:	12 Aug 2008 11:39	Modified:	13 Aug 2008 18:29
Reporter:	Alok Sharma	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S1 (Critical)
Version:	5.0.51a-6	OS:	Linux (debian lenny)
Assigned to:		CPU Architecture:	Any
Tags:	duplicate primary key, insert error

Description:
While inserting a row, an error is thrown stating duplicate entry being there for primary key:

mysql> insert into school_learning_teaching(school, teacher, subject, class) values(609, 1800, 3628, 6);
ERROR 1062 (23000): Duplicate entry '10280' for key 1

mysql> desc school_learning_teaching;                                                                                                                        
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment | 
| school  | int(11)      | NO   |     | NULL    |                | 
| teacher | int(11)      | NO   |     | NULL    |                | 
| subject | varchar(255) | YES  |     | NULL    |                | 
| class   | varchar(255) | YES  |     | NULL    |                | 
+---------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

So I manually inserted a row into the database with the next value for the primary key:

mysql> insert into school_learning_teaching(id, school, teacher, subject, class) values(10281, 609, 1800, 3628, 6);                                                                                                      
Query OK, 1 row affected (0.00 sec)

mysql> select * from school_learning_teaching where id = 10280;                                                                                              
Empty set (0.00 sec)

But the following query shows two rows with the same primary key value:

mysql> select * from school_learning_teaching where id >= 10280;                                                                                             
+-------+--------+---------+---------+-------+
| id    | school | teacher | subject | class |
+-------+--------+---------+---------+-------+
| 10281 |    609 |    1800 | 3628    | 6     | 
| 10281 |    609 |    1800 | 3628    | 6     | 
+-------+--------+---------+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from school_learning_teaching where id = 10281;                                                                                              
+-------+--------+---------+---------+-------+
| id    | school | teacher | subject | class |
+-------+--------+---------+---------+-------+
| 10281 |    609 |    1800 | 3628    | 6     | 
+-------+--------+---------+---------+-------+
1 row in set (0.00 sec)

Further, the counts don't agree with the SELECT * queries:

mysql> select count(*) from school_learning_teaching where id = 10280;                                                                                       
+----------+
| count(*) |
+----------+
|        1 | 
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from school_learning_teaching where id >= 10281;                                                                                      
+----------+
| count(*) |
+----------+
|        1 | 
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from school_learning_teaching where id >= 10280;                                                                                      
+----------+
| count(*) |
+----------+
|        2 | 
+----------+
1 row in set (0.00 sec)

How to repeat:
no idea

Suggested fix:
Manually INSERT a row with the explicitly specifying the next sequence number as value for the PRIMARY KEY column

Many thanks for writing a bug report.

Unfortunaltely I can't repeat this with MySQL 5.0.68. Neither with MyISAM nor with InnoDB.

Which storage engine do you use?

Can you provide a short test case.

Please also try our actual version MySQL 5.0.67.

Pleased to see such a quick reply. This happened after some days of use of the database. It had been working fine for quite some time, but yesterday, it suddenly gave the above error.

I will try the versions you mentioned, but as I said, I'm not sure I'll be able to replicate the error easily.

After the initial error, refusing to add a row to the table claiming the primary key violation, today, it tells me the table is marked as crashed:

ERROR 145 (HY000): Table './prajayathna/school_learning_ksqa' is marked as
crashed and should be repaired

I was able to recover it using:

Use myisamchk -r tbl_name

I will close the bug report because the issue was because of broken table and is not a bug.

I'm not sure the issue is because of a broken table, since the "table is crashed" error was *not* showing when the duplicate entry error happened. I was able to do a desc of the table *after* the failed INSERT attempt.

It was only this today, several hours later, that when I tried to do a desc of the same table, I got the above error saying the table is crashed.

Thank you for the feedback.

"I'm not sure the issue is because of a broken table, since the "table is crashed" error was *not* showing when the duplicate entry error happened. I was able to do a desc of the table *after* the failed INSERT attempt."

I also believe this is because of crashed table. Next time if same problem happens please run CHECK TABLE and if this command returns no error reopen the report.