mysql中键'PRIMARY'的重复项。

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

我有一个名为tbl_jobs的表，它存储在应用程序中运行的一些后台作业的元数据。模式是这样的:

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.

有两种不同的作业同时运行:MAILER_UNLOCKED_REWARDS和mailer_almost_解锁。

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.

主键是类型和last_run_on列的组合。

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?

关于这个问题有什么想法吗?

3 个解决方案

#1

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

在索引中是否使用了整个“类型”字段?还是只有第一个角色?因为MySQL的关键是抱怨。

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

instead of MAILER_...

而不是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:

我怀疑它会在主索引的Sub_part列显示为“1”:

+----------+------------+----------+--------------+-------------+-----------+-------------+    ----------+--------+------+------------+---------+---------------+
| 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.

顺便说一下，主键始终是唯一的，所以第二个索引index2是多余的。

#2

The first thing: you have to make sure you PRIMARY KEY was seted AUTO_INCREMENT. The second thing: you just enable Auto increment by : ALTER TABLE [table name] AUTO_INCREMENT = 1 The third thing: when you execute the insert command you have to skip this key.

第一件事:您必须确保主键是seted AUTO_INCREMENT。第二件事:您只启用自动增量:ALTER TABLE[表名]AUTO_INCREMENT = 1第三件事:当您执行insert命令时，您必须跳过这个键。

#3

I have seen this error if I have a system shutdown or network problem. You really don't have a duplicate in your db. That is a MySQL db error. All you need to do is: if you do your insertion and it is not true, just alter one of the columns of your table you want to insert into either from varchar to text or bigint and then redo the insertion. That solves the problem.

我看到这个错误，如果我有系统关闭或网络问题。你的数据库中没有副本。这是一个MySQL db错误。你需要做的就是:如果你做了插入，这不是真的，只是改变你的表的一个列，你想插入到从varchar到文本或bigint，然后重做插入。来解决这个问题。

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.     

} 


}

#1