英文:
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<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);
}
}
And this is the exception I'm getting:
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
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 "
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
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 'caseandresult' 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 'caseandresult' 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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论