从Hibernate获取重复的输入错误，是MySQL责备的吗？ [英] Getting duplicate entry errors from Hibernate, is MySQL to blame?

问题描述

我正在研究一个大部分只读的数据库应用程序，但是有一个表记录了应用程序中的用户移动并且有大量的写入。对于每数千次写入，我们都会在错误日志中看到一些例外情况，如下所示：

  [WARN] [2009-07- 30 11：09：20,083] [org.hibernate.util.JDBCExceptionReporter] SQL错误：1062，SQLState：23000 
 [错误] [2009-07-30 11：09：20,083] [org.hibernate.util。 JDBCExceptionReporter]键1的重复条目'17011'
 [错误] [2009-07-30 11：09：20,083] [org.hibernate.event.def.AbstractFlushingEventListener]无法使数据库状态与会话$ b $同步b org.hibernate.exception.ConstraintViolationException：无法在org.hibernate.exception.SQLStateConverter.convert上执行JDBC批量更新
（SQLStateConverter.java:94）
在org.hibernate.exception.JDBCExceptionHelper.convert （JDBCExceptionHelper.java:66）
 at org.hibernate.jdbc.AbstractBatcher.executeBatch（AbstractBatcher.java:275）
  

有问题的表格有以下模式：

  CREATE TABLE NOT EXISTS`my_table`（
`id` int（11 ）NOT NULL，
`data1` int（11）NOT NULL，
`data2` int（11）NOT NULL，
`timestamp` datetime默认NULL，
 PRIMARY KEY（ `id`）
）ENGINE = MyISAM DEFAULT CHARSET = latin1 COLLATE = latin1_general_ci; 
  

和相应的Hibernate映射XML：

 <休眠映射> 
< class name =mycorp.MyClasstable =my_table> 
< id name =idcolumn =idtype =java.lang.Integer> 
< generator class =increment/> 
< / id> 
< property name =data1column =data1type =java.lang.Integer/> 
< property name =data2column =data2type =java.lang.Integer/> 
< property name =timestampcolumn =timestamptype =java.util.Date/> 
< / class> 
< / hibernate-mapping> 
  

尽管不太可能，但我们的webapp的多个实例可能会一次写入数据库，因为我们在我们的webapp上下文中版本号以无缝地发布新版本的应用程序。客户端的旧版本应用程序缓存在他们的网页浏览器中，因此可以访问服务器的旧版本，我们在几周后取消部署。

无论如何，我不相信这是问题所在，但我怀疑MySQL和Hibernate之间存在一些同步问题。会改变我的发电机序列，seqhilo或希洛帮助？另外，如果你能提供一个在MySQL中设置这样一个生成器的例子，这将是非常有用的，因为大部分在线资源都是从Hibernate手册中极其简单的例子中复制粘贴的。

解决方案

如果您有多个进程写入同一个表，那么Increment肯定是不好的 - 您肯定会碰到冲突。

因为我们正在谈论MySQL，所以最简单的方法是使用 identity 。在你的Hibernate映射中：

 < generator class =identity/> 
  

在您的MySQL脚本中：

<$ p $ （$ b $ id`int`（11）NOT NULL AUTO_INCREMENT，
`data1` int（11）NOT NULL，
`data2` int（11）NOT NULL，
`timestamp` datetime默认NULL，
PRIMARY KEY（`id`）
）ENGINE = MyISAM DEFAULT CHARSET = latin1 COLLATE = latin1_general_ci;

改变现有表格：

 ALTER TABLE`my_table` 
 CHANGE COLUMN`id``id` int（11）NOT NULL AUTO_INCREMENT = $ NEW_VALUE $; 
  

<$>其中$ NEW_VALUE $应该被替换为下一个可用的ID，以便序列不会重置为1。

I am working on a database application which is mostly read-only, but there is one table which records user movement in the app and has a large number of writes to it. For every few thousand writes, we see a few exceptions in the error log like so:

[WARN][2009-07-30 11:09:20,083][org.hibernate.util.JDBCExceptionReporter] SQL Error: 1062, SQLState: 23000
[ERROR][2009-07-30 11:09:20,083][org.hibernate.util.JDBCExceptionReporter] Duplicate entry '17011' for key 1
[ERROR][2009-07-30 11:09:20,083][org.hibernate.event.def.AbstractFlushingEventListener] Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
  at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:94)
  at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
  at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)

The table in question has the following schema:

CREATE TABLE IF NOT EXISTS `my_table` (
  `id` int(11) NOT NULL,
  `data1` int(11) NOT NULL,
  `data2` int(11) NOT NULL,
  `timestamp` datetime default NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

And the corresponding Hibernate mapping XML:

<hibernate-mapping>
  <class name="mycorp.MyClass" table="my_table">
    <id name="id" column="id" type="java.lang.Integer">
      <generator class="increment"/>
    </id>
    <property name="data1" column="data1" type="java.lang.Integer"/>
    <property name="data2" column="data2" type="java.lang.Integer"/>
    <property name="timestamp" column="timestamp" type="java.util.Date"/>
  </class>
</hibernate-mapping>

It is possible, though unlikely, that multiple instances of our webapp could be writing to the database at once, since we version numbers in our webapp context to seamlessly release new versions of the applications. Clients with the old version of the application cached in their web browser would thus access the old versions of the server, which we undeploy after a few weeks.

Anyways, I'm not convinced that this is the problem, but I'm suspicious that there is some synchronization issue between MySQL and Hibernate at hand here. Would changing my generator to sequence, seqhilo or hilo help? Also, if you can provide an example of setting up such a generator in MySQL, that would be very helpful, as most of the resources online are simply copy-pasted from the woefully minimalistic examples in the Hibernate manual.

解决方案

Increment is definitely bad if you have more than one process writing to the same table - you're bound to have collisions.

Since it is MySQL we're talking about, the easiest thing to use would be identity. In your Hibernate mapping:

<generator class="identity"/>

In your MySQL script:

CREATE TABLE IF NOT EXISTS `my_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data1` int(11) NOT NULL,
  `data2` int(11) NOT NULL,
  `timestamp` datetime default NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

To alter an existing table:

ALTER TABLE `my_table`
  CHANGE COLUMN `id` `id` int(11) NOT NULL AUTO_INCREMENT=$NEW_VALUE$;

where $NEW_VALUE$ should be replaced by the next available id so that sequence does not reset to 1.

这篇关于从Hibernate获取重复的输入错误，是MySQL责备的吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！