2020年8月20日 02:39:45go评论77阅读模式

英文:

Property hibernate.hbm2ddl.auto not working as expected (MySQL 5.7)

问题

我想要实现的目标：

编写一个小的类，在启动过程中创建一个模式（schema）。如果模式已存在，则应将其删除并重新创建。

我必须说我对Hibernate非常新，所以错误很可能出在我的一方。

程序第一次运行正常，但第二次运行时会出现异常（请参见下文），表已经存在（这是真的，但我期望Hibernate会删除然后重新创建它）。

我的配置：

Hibernate 5.4.20，MySQL 5.7.9，MyISAM（在InnoDB上也是相同的结果），MS Windows 10（64位）。

已经尝试了每一个MySQLDialect，MYSQL5Dialect 和 MySQL57Dialect - 结果相同。

以下是代码：

public class HibernateCreateRunner {

    public static void main(String[] args) {
        Map<String, String> settings = new HashMap<>();
        settings.put("connection.driver_class", "com.mysql.jdbc.Driver");
        //settings.put("hibernate.dialect", "org.hibernate.dialect.MySQL57Dialect");
        settings.put("hibernate.dialect", "org.hibernate.dialect.MySQL5Dialect");
        settings.put("hibernate.connection.url", "jdbc:mysql://localhost:3306/stats_test?serverTimezone=UTC");
        settings.put("hibernate.connection.username", "root");
        settings.put("hibernate.connection.password", "root");
        settings.put("hibernate.hbm2ddl.auto", "create");

        settings.put("show_sql", "true");

        MetadataSources metadataSources = new MetadataSources(
                new StandardServiceRegistryBuilder()
                        .applySettings(settings)
                        .build());

        metadataSources.addAnnotatedClass(CaseAndResult.class);
        metadataSources.addAnnotatedClass(CaseObject.class);
        metadataSources.buildMetadata();

        Metadata metadata = metadataSources.buildMetadata();

        SchemaExport schemaExport = new SchemaExport();
        schemaExport.setHaltOnError(true)
            .setFormat(true)
            .setDelimiter(";")
            .setOutputFile("db-schema.sql")
            .createOnly(EnumSet.of(TargetType.DATABASE, TargetType.STDOUT), metadata);
    }
}

这是我收到的异常：

Exception in thread "main" org.hibernate.tool.schema.spi.SchemaManagementException: Halting on error : Error executing DDL "
    create table CASEANDRESULT (
       CASEID varchar(255) not null,
        ASSESSMENTDATE datetime,
        CARRESULTLEVELID integer,
        CARRESULTLEVELNAME varchar(255),
        CASEMODE varchar(255),
        COMPLETE TINYINT,
        LOCALE varchar(255),
        NUMBEROFCHANGEASSESSCYCLES varchar(255),
        NUMBEROFSOURCECHANGES integer,
        PATCHNAME varchar(255),
        POLICYCANBEISSUED TINYINT,
        VERSIONNAME varchar(255),
        primary key (CASEID)
    ) engine=MyISAM" via JDBC Statement
    ...
    ...

任何提示都会非常感谢！

英文:

what I want to achieve:

Write a little class that creates a schema during startup. If the schema exists, it should be dropped and re-created.

I have to say I'm quite new to Hibernate, so chances are the error is on my side.

The program runs fine the first time, but the second time I get an exception (see below) that the table already exists (which is true, but I was expecting Hibernate to delete and then re-create it.

My configuration:

Hibernate 5.4.20, MySQL 5.7.9, MyISAM (same result on InnoDB btw.), MS Windows 10 (64 bit).

Tried already every MySQLDialect, MYSQL5Dialect & MySQL57Dialect - same result.

Here's the code:

public class HibernateCreateRunner {

    public static void main(String[] args) {
        Map&lt;String, String&gt; settings = new HashMap&lt;&gt;();
        settings.put(&quot;connection.driver_class&quot;, &quot;com.mysql.jdbc.Driver&quot;);
        //settings.put(&quot;hibernate.dialect&quot;, &quot;org.hibernate.dialect.MySQL57Dialect&quot;);
        settings.put(&quot;hibernate.dialect&quot;, &quot;org.hibernate.dialect.MySQL5Dialect&quot;);
        settings.put(&quot;hibernate.connection.url&quot;, &quot;jdbc:mysql://localhost:3306/stats_test?serverTimezone=UTC&quot;);
        settings.put(&quot;hibernate.connection.username&quot;, &quot;root&quot;);
        settings.put(&quot;hibernate.connection.password&quot;, &quot;root&quot;);
        settings.put(&quot;hibernate.hbm2ddl.auto&quot;, &quot;create&quot;);

        settings.put(&quot;show_sql&quot;, &quot;true&quot;);

        MetadataSources metadataSources = new MetadataSources(
                new StandardServiceRegistryBuilder()
                        .applySettings(settings)
                        .build());

        metadataSources.addAnnotatedClass(CaseAndResult.class);
        metadataSources.addAnnotatedClass(CaseObject.class);
        metadataSources.buildMetadata();

        Metadata metadata = metadataSources.buildMetadata();;

        SchemaExport schemaExport = new SchemaExport();
        schemaExport.setHaltOnError(true)
            .setFormat(true)
            .setDelimiter(&quot;;&quot;)
            .setOutputFile(&quot;db-schema.sql&quot;)
            .createOnly(EnumSet.of(TargetType.DATABASE, TargetType.STDOUT), metadata);
    }

}

And this is the exception I'm getting:

Exception in thread &quot;main&quot; org.hibernate.tool.schema.spi.SchemaManagementException: Halting on error : Error executing DDL &quot;
    create table CASEANDRESULT (
       CASEID varchar(255) not null,
        ASSESSMENTDATE datetime,
        CARRESULTLEVELID integer,
        CARRESULTLEVELNAME varchar(255),
        CASEMODE varchar(255),
        COMPLETE TINYINT,
        LOCALE varchar(255),
        NUMBEROFCHANGEASSESSCYCLES varchar(255),
        NUMBEROFSOURCECHANGES integer,
        PATCHNAME varchar(255),
        POLICYCANBEISSUED TINYINT,
        VERSIONNAME varchar(255),
        primary key (CASEID)
    ) engine=MyISAM&quot; via JDBC Statement
	at org.hibernate.tool.schema.internal.ExceptionHandlerHaltImpl.handleException(ExceptionHandlerHaltImpl.java:27)
	at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlString(SchemaCreatorImpl.java:443)
	at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlStrings(SchemaCreatorImpl.java:423)
	at org.hibernate.tool.schema.internal.SchemaCreatorImpl.createFromMetadata(SchemaCreatorImpl.java:314)
	at org.hibernate.tool.schema.internal.SchemaCreatorImpl.performCreation(SchemaCreatorImpl.java:166)
	at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:135)
	at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:121)
	at org.hibernate.tool.hbm2ddl.SchemaExport.doExecution(SchemaExport.java:296)
	at org.hibernate.tool.hbm2ddl.SchemaExport.execute(SchemaExport.java:249)
	at org.hibernate.tool.hbm2ddl.SchemaExport.execute(SchemaExport.java:228)
	at org.hibernate.tool.hbm2ddl.SchemaExport.createOnly(SchemaExport.java:224)
	at com.genre.star.database.HibernateCreateRunner.main(HibernateCreateRunner.java:46)
Caused by: org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL &quot;
    create table CASEANDRESULT (
       CASEID varchar(255) not null,
        ASSESSMENTDATE datetime,
        CARRESULTLEVELID integer,
        CARRESULTLEVELNAME varchar(255),
        CASEMODE varchar(255),
        COMPLETE TINYINT,
        LOCALE varchar(255),
        NUMBEROFCHANGEASSESSCYCLES varchar(255),
        NUMBEROFSOURCECHANGES integer,
        PATCHNAME varchar(255),
        POLICYCANBEISSUED TINYINT,
        VERSIONNAME varchar(255),
        primary key (CASEID)
    ) engine=MyISAM&quot; via JDBC Statement
	at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67)
	at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlString(SchemaCreatorImpl.java:439)
	... 10 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table &#39;caseandresult&#39; already exists
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2819)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2768)
	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:894)
	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:732)
	at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54)

Any hints very much appreciated!

答案1

得分: 1

你正在调用 createOnly，它只会创建模式，而表已经存在。

原因是：com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 表 'caseandresult' 已经存在。

如果你想先删除模式，你应该调用 SchemaExport.execute 并使用操作 BOTH。

英文:

You are calling createOnly which only creates the schema, and the table already exists

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table &#39;caseandresult&#39; already exists

If you want to drop the schema first, you should call SchemaExport.execute with action BOTH

答案2

得分: 0

您的代码出现了以下错误：错误代码：1071。指定的键太长；最大键长度为1000字节，因此请仅使用以下长度进行修改：

create table CASEANDRESULT8 (
   CASEID varchar(250) not null,
    ASSESSMENTDATE datetime,
    CARRESULTLEVELID integer,
    CARRESULTLEVELNAME varchar(255),
    CASEMODE varchar(255),
    COMPLETE TINYINT,
    LOCALE varchar(255),
    NUMBEROFCHANGEASSESSCYCLES varchar(255),
    NUMBEROFSOURCECHANGES integer,
    PATCHNAME varchar(255),
    POLICYCANBEISSUED TINYINT,
    VERSIONNAME varchar(255),
    primary key (CASEID)
) engine=MyISAM

英文:

You get with your code following Error Code: 1071. Specified key was too long; max key length is 1000 bytes
so use only following lenghth

create table CASEANDRESULT8 (
   CASEID varchar(250) not null,
    ASSESSMENTDATE datetime,
    CARRESULTLEVELID integer,
    CARRESULTLEVELNAME varchar(255),
    CASEMODE varchar(255),
    COMPLETE TINYINT,
    LOCALE varchar(255),
    NUMBEROFCHANGEASSESSCYCLES varchar(255),
    NUMBEROFSOURCECHANGES integer,
    PATCHNAME varchar(255),
    POLICYCANBEISSUED TINYINT,
    VERSIONNAME varchar(255),
    primary key (CASEID)
) engine=MyISAM

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

属性 “hibernate.hbm2ddl.auto” 的行为与预期不符（MySQL 5.7）

问题

答案1

答案2

如何在反向代理后设置不同端口的Eureka客户端？

拦截 java.lang.Object 的 wait(long) 原生方法，使用 Byte-Buddy。

有没有一种好的方法可以使用Ansible获取JAVA_HOME的实际路径？

如何在Java中使用动态数据追加字符串

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

发表评论