Duplicate entry for key "PRIMARY" in mysql

Question

I have a table called tbl_jobs that stores the metadata of some background jobs running in the application. The circuit is similar:

 CREATE TABLE `tbl_jobs` ( `type` varchar(30) NOT NULL DEFAULT '', `last_run_on` datetime NOT NULL, `records_updated` text, PRIMARY KEY (`type`,`last_run_on`), UNIQUE KEY `index2` (`type`,`last_run_on`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1$$ 

Whenever a task is executed, it makes a record in the table with type , which is a unique identifier for different tasks, run time and records updated in this run.

There are two different jobs that run simultaneously with the types: MAILER_UNLOCKED_REWARDS and MAILER_ALMOST_UNLOCKED .

When these tasks try to insert their records with the same timestamp, only one of them is inserted, and the other gives a Duplicate Entry error for the key .

For example, two tasks were performed as follows:

 INSERT INTO tbl_jobs (type, last_run_on, records_updated) VALUES ('MAILER_ALMOST_UNLOCKED', '2012-08-22 19:10:00', 'f8a35230fb214989ac75bf11c085aa28:b591426df4f340ecbce5a63c2a5a0174') 

which worked successfully, but when the second task executed the insert command

 INSERT INTO tbl_jobs (type, last_run_on, records_updated) VALUES ('MAILER_UNLOCKED_REWARDS', '2012-08-22 19:10:00', '8a003e8934c07f040134c30959c40009:59bcc21b33a0466e8e5dc50443beb945') 

He threw a mistake

 Duplicate entry 'M-2012-08-22 19:10:00' for key 'PRIMARY' 

The primary key is a combination of the type and last_run_on .

If I delete the entry for the first job, the insert will succeed, that is, it will request only timestamp uniqueness.

However, a conflict for the same timestamp occurs only between these two jobs. There are other jobs that are inserted for the same timestamp .

Any ideas on what could be the problem?

Answer 1

Are you using the entire type field in your index? Or just the first character? Since key MySQL complains about

 M-2012-08-22 19:10:00 

instead of MAILER _...

Try to run:

  SHOW INDEXES FROM tbl_jobs; 

It should give something like:

 +----------+------------+----------+--------------+-------------+-----------+-------------+ ----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | tbl_jobs | 0 | PRIMARY | 1 | type | A | 0 | NULL | NULL | | BTREE | | | | tbl_jobs | 0 | PRIMARY | 2 | last_run_on | A | 0 | NULL | NULL | | BTREE | | | 

...

and I suspect that instead of the Sub_part column of the PRIMARY index, "1" will be displayed:

 +----------+------------+----------+--------------+-------------+-----------+-------------+ ----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | tbl_jobs | 0 | PRIMARY | 1 | type | A | 0 | 1 | NULL | | BTREE | | | | tbl_jobs | 0 | PRIMARY | 2 | last_run_on | A | 0 | NULL | NULL | | BTREE | | | 

...

BTW, the primary key is always unique, so the second index2 index you declare is redundant.

Answer 2

First: you must make sure that the PRIMARY KEY has been set to AUTO_INCREMENT. Secondly: you simply activate Auto increment: ALTER TABLE [table name] AUTO_INCREMENT = 1 Third: when you execute the paste command, you need to skip this key.

Answer 3

I saw this error if I have a system problem or a network problem. You really have no duplicate in your db. This is a MySQL db error. All you have to do is: if you do your insertion and not true , just change one of the columns of your table that you want to insert, starting from varchar to text or bigint , and then re-insert. This solves the problem.

 If(!$insert) { $alter=Mysql_query("alter table `table_name` change `table_name` `table_name` bigint(255) not null"); If($alter){ //you then redo your insertion. } }