java - MySQL hibernate 异常

Question

我最近从 mysql-connector-java 5.1.22 更新到 5.1.23。之后，我的应用程序在更新或删除持久对象时不断崩溃。

高达 5.1.22(包括 5.1.22)的 Mysql 连接器工作正常。来自 5.1.23(包括 5.1.23)的 MySQL 连接器不工作。

问题的原因似乎是我用于乐观锁定的时间戳。带有版本的乐观锁定似乎工作正常。但我无法更改整个应用程序，我需要时间戳字段。

5.1.23 的更新日志指出以下错误修正:

If a timestamp value was passed through prepared statement parameters, fractional-second precision was stripped off, even if the underlying field (such as VARCHAR(255)) could store the full value. A workaround was to convert the timestamp value to a string when specifying the prepared statement argument, for example prepped_stmt.setString(1,time_stamp.toString(). This was partly fixed in 5.1.19, but that fix did not cover the case with the setting useLegacyDatetimeCode=true. (Bug #11750017, Bug #40279, Bug #60584)

我怀疑这是我的问题的原因。任何想法如何解决问题？我附加了一个导致错误的简单示例代码。

- -异常(exception)

Jul 02, 2014 9:18:34 AM org.hibernate.annotations.common.reflection.java.JavaReflectionManager <clinit>
INFO: HCANN000001: Hibernate Commons Annotations {4.0.4.Final}
Jul 02, 2014 9:18:34 AM org.hibernate.Version logVersion
INFO: HHH000412: Hibernate Core {4.3.5.Final}
Jul 02, 2014 9:18:34 AM org.hibernate.cfg.Environment <clinit>
INFO: HHH000206: hibernate.properties not found
Jul 02, 2014 9:18:34 AM org.hibernate.cfg.Environment buildBytecodeProvider
INFO: HHH000021: Bytecode provider name : javassist
Jul 02, 2014 9:18:34 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure
WARN: HHH000402: Using Hibernate built-in connection pool (not for production use!)
Jul 02, 2014 9:18:34 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH000401: using driver [com.mysql.jdbc.Driver] at URL [jdbc:mysql://localhost:3306/hibernatedb?useFastDateParsing=false]
Jul 02, 2014 9:18:34 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH000046: Connection properties: {user=root, password=****}
Jul 02, 2014 9:18:34 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH000006: Autocommit mode: false
Jul 02, 2014 9:18:34 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure
INFO: HHH000115: Hibernate connection pool size: 1 (min=1)
Jul 02, 2014 9:18:34 AM org.hibernate.dialect.Dialect <init>
INFO: HHH000400: Using dialect: org.hibernate.dialect.MySQLDialect
Jul 02, 2014 9:18:34 AM org.hibernate.engine.transaction.internal.TransactionFactoryInitiator initiateService
INFO: HHH000399: Using default transaction strategy (direct JDBC transactions)
Jul 02, 2014 9:18:34 AM org.hibernate.hql.internal.ast.ASTQueryTranslatorFactory <init>
INFO: HHH000397: Using ASTQueryTranslatorFactory
Jul 02, 2014 9:18:35 AM org.hibernate.tool.hbm2ddl.SchemaExport execute
INFO: HHH000227: Running hbm2ddl schema export
Jul 02, 2014 9:18:35 AM org.hibernate.tool.hbm2ddl.SchemaExport execute
INFO: HHH000230: Schema export complete
MyPersistentObject (2014-07-02 09:18:35.0) : First persistent object
MyPersistentObject (2014-07-02 09:18:35.0) : A second persistent object
org.hibernate.StaleObjectStateException: Row was updated or deleted by another transaction (or unsaved-value mapping was incorrect) : [MyPersistentObject#2]
    at org.hibernate.persister.entity.AbstractEntityPersister.check(AbstractEntityPersister.java:2541)
    at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:3403)
    at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:3630)
    at org.hibernate.action.internal.EntityDeleteAction.execute(EntityDeleteAction.java:114)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:463)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:349)
    at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:350)
    at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:56)
    at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1222)
    at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:425)
    at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.beforeTransactionCommit(JdbcTransaction.java:101)
    at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.commit(AbstractTransactionImpl.java:177)
    at HibernateExample.manipulatePersistentObjects(HibernateExample.java:143)
    at HibernateExample.main(HibernateExample.java:15)
MyPersistentObject (2014-07-02 09:18:35.0) : First persistent object
MyPersistentObject (2014-07-02 09:18:35.0) : A second persistent object
Jul 02, 2014 9:18:35 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl stop
INFO: HHH000030: Cleaning up connection pool [jdbc:mysql://localhost:3306/hibernatedb?useFastDateParsing=false]

---主类

import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.service.ServiceRegistry;

public class HibernateExample {
    private final org.hibernate.SessionFactory _sessionFactory;
    private MyPersistentObject _myPersistentObject1;
    private MyPersistentObject _myPersistentObject2;
    private MyPersistentObject _myPersistentObject3;

    final static public void main(final String arguments[]) {
        HibernateExample example;
        example = new HibernateExample();
        example.createPersistentObjects();
        example.readAndDisplayPersistentObjects();
        example.manipulatePersistentObjects();
        example.readAndDisplayPersistentObjects();
        example.cleanup();
    }

    private HibernateExample() {
        org.hibernate.cfg.Configuration configuration;

        java.util.logging.Logger.getLogger("org.hibernate").setLevel(java.util.logging.Level.SEVERE); // Supress
        // Hibernate's
        // excessive
        // output

        configuration = new org.hibernate.cfg.Configuration();
        configuration.setProperty("hibernate.dialect", "org.hibernate.dialect.MySQLDialect"); // Customize
        // this
        // for
        // your
        // particular
        // RDBMS
        configuration.setProperty("hibernate.connection.driver_class", "com.mysql.jdbc.Driver"); // Customize
        // this
        // for
        // your
        // particular
        // RDBMS

        // configuration.setProperty("hibernate.connection.url",
        // "jdbc:mysql://localhost:3306/hibernatedb?useLegacyDatetimeCode=true");
        // // Customize

        configuration.setProperty("hibernate.connection.url",
                "jdbc:mysql://localhost:3306/hibernatedb?useFastDateParsing=false"); // Customize

        // this
        // for
        // your
        // particular
        // RDBMS
        configuration.setProperty("hibernate.connection.username", "root"); // Customize
        // this
        // for
        // your
        // particular
        // RDBMS
        configuration.setProperty("hibernate.connection.password", "root"); // Customize
        // this
        // for
        // your
        // particular
        // RDBMS
        // installation
        configuration.setProperty("hibernate.connection.pool_size", "1"); // Customize
        // this
        // for
        // your
        // particular
        // RDBMS
        // installation
        configuration.setProperty("hibernate.cache.provider_class", "org.hibernate.cache.internal.NoCacheProvider"); // This
        // is
        // not
        // ready
        // for
        // prime-time
        configuration.setProperty("hibernate.show_sql", "false"); // Tell
        // hibernate
        // to not
        // echo the
        // SQL
        configuration.setProperty("hibernate.hbm2ddl.auto", "create");

        configuration.setProperty("useLegacyDatetimeCode", "true");

        configuration.addAnnotatedClass(MyPersistentObject.class);

        // configuration.configure();
        final ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder().applySettings(
                configuration.getProperties()).build();
        this._sessionFactory = configuration.buildSessionFactory(serviceRegistry);

    }

    final private void createPersistentObjects() {
        org.hibernate.Session session;
        boolean committed;
        org.hibernate.Transaction transaction;

        session = this._sessionFactory.openSession();

        try {
            committed = false;
            transaction = session.beginTransaction();

            try {
                this._myPersistentObject1 = new MyPersistentObject("First persistent object", new java.util.Date());
                session.save(this._myPersistentObject1);

                this._myPersistentObject2 = new MyPersistentObject("A second persistent object", new java.util.Date());
                session.save(this._myPersistentObject2);

                transaction.commit();
                session.flush();
                committed = true;
            } finally {
                if (!committed) {
                    transaction.rollback();
                }
            }
        } finally {
            session.close();
        }
    }

    final private void manipulatePersistentObjects() {
        org.hibernate.Session session;
        org.hibernate.Transaction transaction;

        session = this._sessionFactory.openSession();

        try {
            transaction = session.beginTransaction();

            this._myPersistentObject3 = new MyPersistentObject("A third persistent object", new java.util.Date());
            // session.save(this._myPersistentObject3);

            session.delete(this._myPersistentObject2);

            transaction.commit();
            session.flush();
        } catch (final Exception e) {
            e.printStackTrace();
        } finally {

            session.close();
        }
    }

    final private void readAndDisplayPersistentObjects() {
        org.hibernate.Session session;
        java.util.List<MyPersistentObject> result;

        session = this._sessionFactory.openSession();

        try {
            session.beginTransaction();

            result = (session.createQuery("from MyPersistentObject").list());

            for (final MyPersistentObject persistentObject : result) {
                System.out.println("MyPersistentObject (" + persistentObject.getDate() + ") : "
                        + persistentObject.getTitle());
            }

            session.getTransaction().commit();
            session.flush();
        } catch (final Exception e) {
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

    final private void cleanup() {
        if (this._sessionFactory != null) {
            this._sessionFactory.close();
        }
    }
}

- -目的

import java.util.Calendar;

import javax.persistence.PrePersist;
import javax.persistence.PreUpdate;
import javax.persistence.Version;

@javax.persistence.Entity
public class MyPersistentObject {

    @javax.persistence.Id
    @javax.persistence.GeneratedValue(generator = "increment")
    @org.hibernate.annotations.GenericGenerator(name = "increment", strategy = "increment")
    private Long _persistenceID;
    private String _title;
    @javax.persistence.Temporal(javax.persistence.TemporalType.TIMESTAMP)
    @javax.persistence.Column(name = "OBJECT_DATE")
    private java.util.Date _date;

    @Version
    private Calendar timestemp;

    // Hibernate needs a no-argument constructor
    private MyPersistentObject() {
    }

    // for application use, to create new persistent objects
    public MyPersistentObject(final String title, final java.util.Date date) {
        this._title = title;
        this._date = date;
    }

    public java.util.Date getDate() {
        return this._date;
    }

    public String getTitle() {
        return this._title;
    }

    @PrePersist
    protected void onCreate() {
        System.out.println("create");
    }

    @PreUpdate
    protected void onUpdate() {
        System.out.println("update");
    }
}

---pom

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>Hibernate34Migrate</groupId>
    <artifactId>Hibernate34Migrate</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>4.3.5.Final</version>
            <type>jar</type>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-ehcache</artifactId>
            <version>4.3.5.Final</version>
            <type>jar</type>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.31</version>
        </dependency>
    </dependencies>
</project>

Answer 1

背景资料

MySQL < 5.6.4 不支持分数的存储

在 MySQL 5.6.4 之前，MySQL 确实支持一些可以在几分之一秒内操作的函数，但 MySQL 不支持分数的存储:

http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html

However, when MySQL stores a value into a column of any temporal data type, it discards any fractional part and does not store it.

MySQL 5.6.4 and up expands fractional seconds support for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision:

To define a column that includes a fractional seconds part, use the syntax >type_name(fsp), where type_name is TIME, DATETIME, or TIMESTAMP, and fsp is the fractional seconds precision. For example:

CREATE TABLE t1 (t TIME(3), dt DATETIME(6));

MySQL Connector/J < 5.1.23 剥离分数

http://dev.mysql.com/doc/relnotes/connector-j/en/news-5-1-23.html

If a timestamp value was passed through prepared statement parameters, fractional-second precision was stripped off, even if the underlying field (such as VARCHAR(255)) could store the full value. A workaround was to convert the timestamp value to a string when specifying the prepared statement argument, for example prepped_stmt.setString(1,time_stamp.toString(). This was partly fixed in 5.1.19, but that fix did not cover the case with the setting useLegacyDatetimeCode=true. (Bug #11750017, Bug #40279, Bug #60584)

问题:Hibernate 使用 3 个小数位数忽略 MySQL

当 Hibernate 将支持版本控制的对象插入数据库时​​，它会执行如下操作:

2014-nov-07;20:32:05.775 TRACE org.hibernate.engine.internal.Versioning - Seeding: 2014-11-07 20:32:05.774
2014-nov-07;20:32:05.786 TRACE org.hibernate.persister.entity.AbstractEntityPersister - Version: 7-11-14 20:32
2014-nov-07;20:32:05.787 DEBUG org.hibernate.SQL - insert into A (b_id, version) values (?, ?)
Hibernate: insert into A (b_id, version) values (?, ?)
2014-nov-07;20:32:05.799 TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [1] as [BIGINT] - [null]
2014-nov-07;20:32:05.799 TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [2] as [TIMESTAMP] - [2014-11-07 20:32:05.774]

因此，Hibernate 似乎在内部使用 3 位数字作为小数秒精度，并且还尝试将分数存储到数据库中，即使 MySQL 版本不支持它。

使用较旧的 Connector/J 时，将去除分数并存储日期时间(具有第二个精度)。
当实体 A 发生变化时，Hibernate 执行乐观锁定，会发生这样的事情:

2014-nov-07;20:32:05.822 DEBUG org.hibernate.internal.util.EntityPrinter - entities.B{id=27}
2014-nov-07;20:32:05.822 DEBUG org.hibernate.internal.util.EntityPrinter - entities.A{b=entities.B#27, id=29, version=2014-11-07 20:32:05.774}
2014-nov-07;20:32:05.825 TRACE org.hibernate.persister.entity.AbstractEntityPersister - Updating entity: [entities.A#29]
2014-nov-07;20:32:05.825 TRACE org.hibernate.persister.entity.AbstractEntityPersister - Existing version: 7-11-14 20:32 -> New version:7-11-14 20:32
2014-nov-07;20:32:05.825 DEBUG org.hibernate.SQL - update A set b_id=?, version=? where id=? and version=?
Hibernate: update A set b_id=?, version=? where id=? and version=?
2014-nov-07;20:32:05.826 TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [1] as [BIGINT] - [27]
2014-nov-07;20:32:05.826 TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [2] as [TIMESTAMP] - [2014-11-07 20:32:05.82]
2014-nov-07;20:32:05.826 TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [3] as [BIGINT] - [29]
2014-nov-07;20:32:05.826 TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [4] as [TIMESTAMP] - [2014-11-07 20:32:05.774]

(不知道为什么 Hibernate 显示“20:32:05.82”作为绑定(bind)参数，我希望它是一个更高的版本，所以它可能会截断零并表示“20:32:05.820”，这与对数分数相同.)

但是因为较旧的连接器/J 在插入语句和 Hibernate 版本控制更新语句(其中从 SQL-WHERE 子句中删除了该部分)中都删除了分数，所以它还不会导致问题。

但是当使用新版本 (>= 5.1.23) 的 Connector/J 时，分数会被保留并且确实会到达数据库。这基本上还不是问题，甚至比没有分数还要好，因为它在并发方面更安全。
但是当数据库列的类型为“DATETIME”(Hibernate默认)，即“DATETIME(0)”，没有小数时，就会出现Hibernate版本控制问题，因为

update A set b_id=?, version=? where id=? and version=?

将导致 0 个更新，导致 Hibernate 认为“行已被另一个事务更新或删除”。

解决方案 1(已测试):向列中添加分数

将列类型从“DATETIME”更改为“DATETIME(3)”。这可能是一个安全的操作，因为 MySQL 只是将分数 000 附加到日期时间。

为了完整起见，还将其添加到版本化时间戳实例变量中:

@Column(columnDefinition = "datetime(3)")

方案二(已测试):让MySQL生成日期版本

如果你不想在列中添加分数，但让数据库生成版本没有问题，那么你可以使用以下方法:

将以下内容添加到 verioned 时间戳实例变量:

@Generated(GenerationTime.ALWAYS)

这将让 Hibernate 使用数据库列来生成版本。

然后将列类型从“日期时间”更改为:

DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

该解决方案不使用分数，因此在并发方面是危险的，即使在 Hibernate 中结合使用 MySQL Connector/J < 5.1.23 或 MySQL Server < 5.6.4 是正常行为。

解决方案 3:使用专用版本号而不是日期时间

见 http://docs.jboss.org/hibernate/orm/4.3/devguide/en-US/html/ch05.html#d5e1246

这在并发性方面是安全的，但如果您无论如何都需要更新日期时间，则不太方便。

其他解决方案(未经测试)

另一种解决方案可能是实现您自己的 org.hibernate.usertype.UserVersionType。

https://docs.jboss.org/hibernate/core/4.3/javadocs/org/hibernate/usertype/UserVersionType.html

https://developer.jboss.org/wiki/UserTypeForNon-defaultTimeZone

新 Hibernate 应用程序的另一个解决方案可能是修改 MySQL 方言类以自动使用带分数的日期时间。

讨论

很难说这是否是 Hibernate 的错误。最好的解决方案是 Hibernate 是否会自动测试 MySQL 环境是否支持小数日期时间列，如果支持，则默认为“datetime(3)”列而不是“datetime”。
或者，当 MySQL 环境不支持时，Hibernate 应该从其内部版本控制中删除分数，但这在并发性方面有点危险。

笔记

使用 TemporalType.TIMESTAMP 时，Hibernates MySQL5InnoDBDialect 不使用 MySQL 的 TIMESTAMP，而是使用 DATETIME。