1.故障描述
项目使用spring boot启动,application.yml配置如下:
jpa:
database-platform: org.hibernate.dialect.SQLServer2012Dialect
database: SQL_SERVER
show-sql: true
open-in-view: true
hibernate:
ddl-auto: update
通过配置ddl-auto: update
,希望可以在启动的时候自动更新数据库表结构。
启动过程中,会出现如下异常:
2019-06-13 18:13:02.362 DEBUG 12268 --- [ main] c.m.s.jdbc.internals.SQLServerStatement : SQLServerStatement:8 Executing (not server cursor) alter table table_name_ add constraint FK5p4sboj9uoritv12cujt3sdek foreign key (country_id) references srm_country
2019-06-13 18:13:02.364 DEBUG 12268 --- [ main] c.m.s.jdbc.internals.SQLServerException : *** SQLException: com.microsoft.sqlserver.jdbc.SQLServerException: 数据库中已存在名为 'FK5p4sboj9uoritv12cujt3sdek' 的对象。 Msg 2714, Level 16, State 5, 数据库中已存在名为 'FK5p4sboj9uoritv12cujt3sdek' 的对象。
2019-06-13 18:13:02.366 WARN 12268 --- [ main] o.h.t.s.i.ExceptionHandlerLoggedImpl : GenerationTarget encountered exception accepting command : Error executing DDL via JDBC Statement
org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL via JDBC Statement
at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlString(AbstractSchemaMigrator.java:525)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlStrings(AbstractSchemaMigrator.java:470)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applyForeignKeys(AbstractSchemaMigrator.java:429)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:245)
at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:110)
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:183)
at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:72)
at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:309)
at org.hibernate.boot.internal.SessionFactoryBuilderImpl.__build(SessionFactoryBuilderImpl.java:452)
at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:40008)
at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:889)
at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.__createEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:60)
at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:40002)
通过查询数据库,发现已经存在同名的表。根据hibernateupdate
的策略,如果数据库已经存在表的话,启动时不会再次创建表。
2.排查过程
通过观察启动异常堆栈,在相关源码中进行断点调试。
GroupedSchemaMigratorImpl.java
final NameSpaceTablesInformation tables = existingDatabase.getTablesInformation( namespace );
for ( Table table : namespace.getTables() ) {
if ( schemaFilter.includeTable( table ) && table.isPhysicalTable() ) {
checkExportIdentifier( table, exportIdentifiers );
final TableInformation tableInformation = tables.getTableInformation( table );
if ( tableInformation == null ) {
createTable( table, dialect, metadata, formatter, options, targets );
}
else if ( tableInformation != null && tableInformation.isPhysicalTable() ) {
tablesInformation.addTableInformation( tableInformation );
migrateTable( table, tableInformation, dialect, metadata, formatter, options, targets );
}
}
}
根本原因:tables.getTableInformation( table )
验证数据库中是否存在实体对应的表,但是对表名的大小写敏感。但是执行自动创建表语句时,MSSQL对表名又大小写不敏感,导致出现异常。
3.解决方案
1.对数据库表中已经的表进行重命名
2.如果时初始化数据库,可以删除数据库中的表,然后重新启动即可自动创建表