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

huangapple
go评论109阅读模式
英文:

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 - 结果相同。

以下是代码：

  1. public class HibernateCreateRunner {
  3.     public static void main(String[] args) {
  4.         Map<String, String> settings = new HashMap<>();
  5.         settings.put("connection.driver_class", "com.mysql.jdbc.Driver");
  6.         //settings.put("hibernate.dialect", "org.hibernate.dialect.MySQL57Dialect");
  7.         settings.put("hibernate.dialect", "org.hibernate.dialect.MySQL5Dialect");
  8.         settings.put("hibernate.connection.url", "jdbc:mysql://localhost:3306/stats_test?serverTimezone=UTC");
  9.         settings.put("hibernate.connection.username", "root");
  10.         settings.put("hibernate.connection.password", "root");
  11.         settings.put("hibernate.hbm2ddl.auto", "create");
  13.         settings.put("show_sql", "true");
  15.         MetadataSources metadataSources = new MetadataSources(
  16.                 new StandardServiceRegistryBuilder()
  17.                         .applySettings(settings)
  18.                         .build());
  20.         metadataSources.addAnnotatedClass(CaseAndResult.class);
  21.         metadataSources.addAnnotatedClass(CaseObject.class);
  22.         metadataSources.buildMetadata();
  24.         Metadata metadata = metadataSources.buildMetadata();
  26.         SchemaExport schemaExport = new SchemaExport();
  27.         schemaExport.setHaltOnError(true)
  28.             .setFormat(true)
  29.             .setDelimiter(";")
  30.             .setOutputFile("db-schema.sql")
  31.             .createOnly(EnumSet.of(TargetType.DATABASE, TargetType.STDOUT), metadata);
  32.     }
  33. }

这是我收到的异常：

  1. Exception in thread "main" org.hibernate.tool.schema.spi.SchemaManagementException: Halting on error : Error executing DDL "
  2.     create table CASEANDRESULT (
  3.        CASEID varchar(255) not null,
  4.         ASSESSMENTDATE datetime,
  5.         CARRESULTLEVELID integer,
  6.         CARRESULTLEVELNAME varchar(255),
  7.         CASEMODE varchar(255),
  8.         COMPLETE TINYINT,
  9.         LOCALE varchar(255),
  10.         NUMBEROFCHANGEASSESSCYCLES varchar(255),
  11.         NUMBEROFSOURCECHANGES integer,
  12.         PATCHNAME varchar(255),
  13.         POLICYCANBEISSUED TINYINT,
  14.         VERSIONNAME varchar(255),
  15.         primary key (CASEID)
  16.     ) engine=MyISAM" via JDBC Statement
  17.     ...
  18.     ...

任何提示都会非常感谢！

英文:

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:

  1. public class HibernateCreateRunner {
  3.     public static void main(String[] args) {
  4.         Map&lt;String, String&gt; settings = new HashMap&lt;&gt;();
  5.         settings.put(&quot;connection.driver_class&quot;, &quot;com.mysql.jdbc.Driver&quot;);
  6.         //settings.put(&quot;hibernate.dialect&quot;, &quot;org.hibernate.dialect.MySQL57Dialect&quot;);
  7.         settings.put(&quot;hibernate.dialect&quot;, &quot;org.hibernate.dialect.MySQL5Dialect&quot;);
  8.         settings.put(&quot;hibernate.connection.url&quot;, &quot;jdbc:mysql://localhost:3306/stats_test?serverTimezone=UTC&quot;);
  9.         settings.put(&quot;hibernate.connection.username&quot;, &quot;root&quot;);
  10.         settings.put(&quot;hibernate.connection.password&quot;, &quot;root&quot;);
  11.         settings.put(&quot;hibernate.hbm2ddl.auto&quot;, &quot;create&quot;);
  13.         settings.put(&quot;show_sql&quot;, &quot;true&quot;);
  15.         MetadataSources metadataSources = new MetadataSources(
  16.                 new StandardServiceRegistryBuilder()
  17.                         .applySettings(settings)
  18.                         .build());
  20.         metadataSources.addAnnotatedClass(CaseAndResult.class);
  21.         metadataSources.addAnnotatedClass(CaseObject.class);
  22.         metadataSources.buildMetadata();
  24.         Metadata metadata = metadataSources.buildMetadata();;
  26.         SchemaExport schemaExport = new SchemaExport();
  27.         schemaExport.setHaltOnError(true)
  28.             .setFormat(true)
  29.             .setDelimiter(&quot;;&quot;)
  30.             .setOutputFile(&quot;db-schema.sql&quot;)
  31.             .createOnly(EnumSet.of(TargetType.DATABASE, TargetType.STDOUT), metadata);
  32.     }

}

And this is the exception I'm getting:

  1. Exception in thread &quot;main&quot; org.hibernate.tool.schema.spi.SchemaManagementException: Halting on error : Error executing DDL &quot;
  2.     create table CASEANDRESULT (
  3.        CASEID varchar(255) not null,
  4.         ASSESSMENTDATE datetime,
  5.         CARRESULTLEVELID integer,
  6.         CARRESULTLEVELNAME varchar(255),
  7.         CASEMODE varchar(255),
  8.         COMPLETE TINYINT,
  9.         LOCALE varchar(255),
  10.         NUMBEROFCHANGEASSESSCYCLES varchar(255),
  11.         NUMBEROFSOURCECHANGES integer,
  12.         PATCHNAME varchar(255),
  13.         POLICYCANBEISSUED TINYINT,
  14.         VERSIONNAME varchar(255),
  15.         primary key (CASEID)
  16.     ) engine=MyISAM&quot; via JDBC Statement
  17. 	at org.hibernate.tool.schema.internal.ExceptionHandlerHaltImpl.handleException(ExceptionHandlerHaltImpl.java:27)
  18. 	at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlString(SchemaCreatorImpl.java:443)
  19. 	at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlStrings(SchemaCreatorImpl.java:423)
  20. 	at org.hibernate.tool.schema.internal.SchemaCreatorImpl.createFromMetadata(SchemaCreatorImpl.java:314)
  21. 	at org.hibernate.tool.schema.internal.SchemaCreatorImpl.performCreation(SchemaCreatorImpl.java:166)
  22. 	at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:135)
  23. 	at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:121)
  24. 	at org.hibernate.tool.hbm2ddl.SchemaExport.doExecution(SchemaExport.java:296)
  25. 	at org.hibernate.tool.hbm2ddl.SchemaExport.execute(SchemaExport.java:249)
  26. 	at org.hibernate.tool.hbm2ddl.SchemaExport.execute(SchemaExport.java:228)
  27. 	at org.hibernate.tool.hbm2ddl.SchemaExport.createOnly(SchemaExport.java:224)
  28. 	at com.genre.star.database.HibernateCreateRunner.main(HibernateCreateRunner.java:46)
  29. Caused by: org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL &quot;
  30.     create table CASEANDRESULT (
  31.        CASEID varchar(255) not null,
  32.         ASSESSMENTDATE datetime,
  33.         CARRESULTLEVELID integer,
  34.         CARRESULTLEVELNAME varchar(255),
  35.         CASEMODE varchar(255),
  36.         COMPLETE TINYINT,
  37.         LOCALE varchar(255),
  38.         NUMBEROFCHANGEASSESSCYCLES varchar(255),
  39.         NUMBEROFSOURCECHANGES integer,
  40.         PATCHNAME varchar(255),
  41.         POLICYCANBEISSUED TINYINT,
  42.         VERSIONNAME varchar(255),
  43.         primary key (CASEID)
  44.     ) engine=MyISAM&quot; via JDBC Statement
  45. 	at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67)
  46. 	at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlString(SchemaCreatorImpl.java:439)
  47. 	... 10 more
  48. Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table &#39;caseandresult&#39; already exists
  49. 	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
  50. 	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
  51. 	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
  52. 	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
  53. 	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
  54. 	at com.mysql.jdbc.Util.getInstance(Util.java:386)
  55. 	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
  56. 	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
  57. 	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)
  58. 	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)
  59. 	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
  60. 	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2819)
  61. 	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2768)
  62. 	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:894)
  63. 	at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:732)
  64. 	at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54)

Any hints very much appreciated!

答案1

得分: 1

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

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

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

英文:

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

  1. 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字节，因此请仅使用以下长度进行修改：

  1. create table CASEANDRESULT8 (
  2.    CASEID varchar(250) not null,
  3.     ASSESSMENTDATE datetime,
  4.     CARRESULTLEVELID integer,
  5.     CARRESULTLEVELNAME varchar(255),
  6.     CASEMODE varchar(255),
  7.     COMPLETE TINYINT,
  8.     LOCALE varchar(255),
  9.     NUMBEROFCHANGEASSESSCYCLES varchar(255),
  10.     NUMBEROFSOURCECHANGES integer,
  11.     PATCHNAME varchar(255),
  12.     POLICYCANBEISSUED TINYINT,
  13.     VERSIONNAME varchar(255),
  14.     primary key (CASEID)
  15. ) 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

  1. create table CASEANDRESULT8 (
  2.    CASEID varchar(250) not null,
  3.     ASSESSMENTDATE datetime,
  4.     CARRESULTLEVELID integer,
  5.     CARRESULTLEVELNAME varchar(255),
  6.     CASEMODE varchar(255),
  7.     COMPLETE TINYINT,
  8.     LOCALE varchar(255),
  9.     NUMBEROFCHANGEASSESSCYCLES varchar(255),
  10.     NUMBEROFSOURCECHANGES integer,
  11.     PATCHNAME varchar(255),
  12.     POLICYCANBEISSUED TINYINT,
  13.     VERSIONNAME varchar(255),
  14.     primary key (CASEID)
  15. ) engine=MyISAM

huangapple
  • 本文由 发表于 2020年8月20日 02:39:45
  • 转载请务必保留本文链接：https://go.coder-hub.com/63493108.html
  • ddl
  • hibernate
  • java
  • mysql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定