Duplicate entry for key 'PRIMARY' in mysql

Question

I have a table called tbl_jobs that stores the meta data of some background jobs running in the application. The schema is like :

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 job runs it makes an entry in the table with the type which is a unique identifier for different jobs, run time and the records updated in that run.

There are two different jobs that run at same time with types : MAILER_UNLOCKED_REWARDS and MAILER_ALMOST_UNLOCKED.

When these jobs try to insert their entries with the same timestamp only one of them gets inserted and the other throws a Duplicate Entry for key error.

For instance the two jobs ran the following :

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

that ran successfully but when the second job ran 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')

It threw the error

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

The primary key is combination of type and last_run_on columns.

If I delete the entry for the first job the insertion succeeds, i.e it is asking for timestamp alone to be unique.

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

Any ideas on what could be the issue?

Answer 1

Are you using the whole "type" field in your index? Or only the first character? Because the key MySQL is complaining about is

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

instead of MAILER_...

Try running:

 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 it will show instead "1" in the Sub_part column of the PRIMARY index:

+----------+------------+----------+--------------+-------------+-----------+-------------+    ----------+--------+------+------------+---------+---------------+
| 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 index index2 you declare there is redundant.