"PRIMARY"键发生重复条目记录

Question

正如你和我都知道的那样，这种错误消息有很多问题。

但是我找不到任何好的答案，因为有太多的答案。

我有一个存储从客户端发送的nonce的表。

但有时候（偶尔）即使没有完全相同的主键记录，数据库也会抱怨重复的主键插入。

这里是JVM显示的内容。

[#|2012-11-09T11:06:52.098+0900|WARNING|glassfish3.1.2|javax.enterprise.system.container.ejb.com.sun.ejb.containers|_ThreadID=236;_ThreadName=Thread-2;|EJB5184:A system exception occurred during an invocation on EJB Nonce2Bean, method: public java.lang.Object kr.co.ticomms.gameground.business.AbstractEntityFacade.persist(java.lang.Object)|#]

[#|2012-11-09T11:06:52.099+0900|WARNING|glassfish3.1.2|javax.enterprise.system.container.ejb.com.sun.ejb.containers|_ThreadID=236;_ThreadName=Thread-2;|javax.ejb.EJBException
        ...
Caused by: javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'c8b4bdb84606fed0-c8b4bdb84606fed0_1352426820765_1880007534138556' for key 'PRIMARY'
Error Code: 1062
Call: INSERT INTO NONCE2 (NONCE, UDID, CREATED_DATE) VALUES (?, ?, ?)
        bind => [3 parameters bound]
Query: InsertObjectQuery(c8b4bdb84606fed0/c8b4bdb84606fed0_1352426820765_1880007534138556402)
        ...
        ... 29 more
Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'c8b4bdb84606fed0-c8b4bdb84606fed0_1352426820765_1880007534138556' for key 'PRIMARY'
Error Code: 1062
Call: INSERT INTO NONCE2 (NONCE, UDID, CREATED_DATE) VALUES (?, ?, ?)
        bind => [3 parameters bound]
Query: InsertObjectQuery(c8b4bdb84606fed0/c8b4bdb84606fed0_1352426820765_1880007534138556402)
       ...
       ... 59 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry 'c8b4bdb84606fed0-c8b4bdb84606fed0_1352426820765_1880007534138556' for key 'PRIMARY'
|#]

这里是mysql显示的内容。

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+-------------------------+
| Variable_name           | Value                   |
+-------------------------+-------------------------+
| protocol_version        | 10                      |
| version                 | 5.1.62-0ubuntu0.10.04.1 |
| version_comment         | (Ubuntu)                |
| version_compile_machine | x86_64                  |
| version_compile_os      | debian-linux-gnu        |
+-------------------------+-------------------------+
5 rows in set (0.00 sec)

mysql> DESC NONCE2;
+--------------+--------------+------+-----+-------------------+-------+
| Field        | Type         | Null | Key | Default           | Extra |
+--------------+--------------+------+-----+-------------------+-------+
| CREATED_DATE | timestamp    | NO   |     | CURRENT_TIMESTAMP |       |
| UDID         | varchar(255) | NO   | PRI | NULL              |       |
| NONCE        | varchar(255) | NO   | PRI | NULL              |       |
+--------------+--------------+------+-----+-------------------+-------+
3 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE NONCE2;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| NONCE2 | CREATE TABLE `NONCE2` (
  `CREATED_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UDID` varchar(255) NOT NULL,
  `NONCE` varchar(255) NOT NULL,
  PRIMARY KEY (`UDID`,`NONCE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| NONCE2 |          0 | PRIMARY  |            1 | UDID        | A         |           7 |     NULL | NULL   |      | BTREE      |         |
| NONCE2 |          0 | PRIMARY  |            2 | NONCE       | A         |         403 |     NULL | NULL   |      | BTREE      |         |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

mysql>

我尝试删除并重新创建表格，但是同样的问题仍然存在。我正在使用GlassFish上的JPA。有什么帮助吗？

----------------------------------------- 更新

我正在使用JPA工作。这是ID类。

public class NonceId implements Serializable {

    public static NonceId newInstance(final String udid, final String nonce) {
        if (udid == null) {
            throw new IllegalArgumentException("null udid");
        }
        if (nonce == null) {
            throw new IllegalArgumentException("null nonce");
        }
        final NonceId instance = new NonceId();
        instance.udid = udid;
        instance.nonce = nonce;
        return instance;
    }

    @Override
    public int hashCode() {
        int hash = 7;
        hash = 23 * hash + (this.udid != null ? this.udid.hashCode() : 0);
        hash = 23 * hash + (this.nonce != null ? this.nonce.hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object obj) {
        if (obj == null) {
            return false;
        }
        if (getClass() != obj.getClass()) {
            return false;
        }
        final NonceId other = (NonceId) obj;
        if ((this.udid == null) ? (other.udid != null) : !this.udid.equals(other.udid)) {
            return false;
        }
        if ((this.nonce == null) ? (other.nonce != null) : !this.nonce.equals(other.nonce)) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return udid + "/" + nonce;
    }

    private String udid;

    private String nonce;
}

实体类。

@Entity
@IdClass(NonceId.class)
@Table(name = "NONCE2")
@XmlTransient
public class Nonce2 implements Serializable {

    public static final int UDID_SIZE_MIN = 1;
    public static final int UDID_SIZE_MAX = 255;
    public static final int NONCE_SIZE_MIN = 1;
    public static final int NONCE_SIZE_MAX = 255;

    public static Nonce2 newInstance(final String udid, final String nonce) {
        if (nonce == null) {
            throw new NullPointerException("null value");
        }
        final Nonce2 instance = new Nonce2();
        instance.udid = udid;
        instance.nonce = nonce;
        return instance;
    }

    public Date getCreatedDate() {
        return createdDate;
    }

    public String getUdid() {
        return udid;
    }

    public String getNonce() {
        return nonce;
    }

    @PrePersist
    protected void _PrePersist() {
        createdDate = new Date();
    }

    @Override
    public String toString() {
        return udid + "/" + nonce;
    }

    @Column(name = "CREATED_DATE", nullable = false, updatable = false)
    @Temporal(TemporalType.TIMESTAMP)
    @NotNull
    private Date createdDate;

    @Id
    @Column(name = "UDID", nullable = false, updatable = false)
    @NotNull
    @Size(min = UDID_SIZE_MIN, max = UDID_SIZE_MAX)
    private String udid;

    @Id
    @Column(name = "NONCE", nullable = false, updatable = false)
    @NotNull
    @Size(min = NONCE_SIZE_MIN, max = NONCE_SIZE_MAX)
    private String nonce;
}

在我的过滤器中，我执行以下操作：

@WebFilter(urlPatterns = {"/*"})
public class Filter_ implements Filter {

    @Override
    public void doFilter(final ServletRequest request,
                         final ServletResponse response,
                         final FilterChain chain)
        throws IOException, ServletException {

        // check whether nonce is already exist via em.find();

        chain.doFilter(request, response);

        // store nonce via em.persist(); // EXCEPTION IS HERE
        // THERE IS NO SUCH RECORD check direct SQL console.
    }
}

我的JPA提供者似乎执行了这个语句。

INSERT INTO NONCE2 (NONCE, UDID, CREATED_DATE) VALUES (?, ?, ?)

- Jin Kwon

---

你最终解决了问题吗？我们在一个使用JPA和唯一VARCHAR（255）列的项目中遇到了类似的问题。似乎异常错误消息只显示索引值的前64个字符。当我们直接将类似的记录插入数据库时，不会出现重复键错误。在你的情况下，“c8b4bdb84606fed0-c8b4bdb84606fed0_1352426820765_1880007534138556”也是64个字符长。但它并不是你尝试插入的完整值：末尾缺少402。 - tvirtualw

我们找到了解决问题的方法。默认情况下，MySQL排序规则对大小写不敏感。我们尝试将值插入到表中时，这些值具有相同的字符，有时只是一个字母的大小写不同，例如'abcd'与'aBcd'。在默认排序规则下，这会导致唯一索引违规（我猜主键也是如此）。我们将其更改为utf8_bin，然后它对我们起作用了。 - tvirtualw

我猜nonce在英国俚语中一定有不同的含义！ - Strawberry

Answer 1

您在表中定义了复合主键。

也许您正在同一会话期间批量插入记录。而且该批次可能包含所述键列的重复条目。请检查一下。

还请发布带有示例数据的插入代码。

- Ravinder Reddy

---

也许是apache-http-client的自动重试功能导致了这个问题。我会进一步研究它。 - Jin Kwon