Naval Kishore
Reputation: 986
Hibernate : MS SQL 2012 Strange Behavior with hbm2ddl.auto=update
I am facing following problem in my application only if I use MS SQL
Scope
Hibernate : 5.2.8.Final
MS SQL SERVER : 2012
JDBC Driver : net.sourceforge.jtds 1.3.1
Spring : 4.3.7.RELEASE
JAVA : 1.8.0.121
Problem When I use a fresh Schema for the application, all if the table and relations are created successfully. But if I add a new field in entity, Hibernate tries to run this kind of query on all tables
alter table A add constraint FKh20u37tju4rdvm3kmhve8fu8e foreign key (last_modified_by_id) references B
Which results to
Caused by: java.sql.SQLException: There is already an object named 'FKh20u37tju4rdvm3kmhve8fu8e' in the database.
Overall Impact
None of the newly added fields gets added to the schema
I tried some suggestion like
- Change Hibernate Version tried 5.2.5 to 5.2.8
- Changed JDBC Driver to com.microsoft.sqlserver 6.1.0.jre8
- Removing Connection Pool
- Set ValidationQuery in Data source to SELECT 1
- Move @Id field in entities instead of having it in @MappedSuperclass
- Set Default Schema in Hibernate Config
But nothing worked. However, If I use MYSQL instead of MS SQL, everything works like charm
I am pasting some of Entities and Hibernate Configuration below
Base Entity Class Being Extended by Every Entity
@MappedSuperclass
public abstract class AbstractEntity implements Serializable {
private static final long serialVersionUID = 6486192088436426369L;
protected Long id;
private UserInfo createdBy;
private Long createdById;
private Date creationDatetime;
private UserInfo lastModifiedBy;
private Long lastModifiedById;
private Date lastModifiedDatetime;
public AbstractEntity() {
}
public AbstractEntity(UserInfo createdBy, Date creationDatetime,
UserInfo lastModifiedBy, Date lastModifiedDatetime) {
super();
this.createdBy = createdBy;
this.creationDatetime = creationDatetime;
this.lastModifiedBy = lastModifiedBy;
this.lastModifiedDatetime = lastModifiedDatetime;
}
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(unique = true, nullable = false)
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
@ManyToOne(fetch = FetchType.LAZY,cascade=CascadeType.ALL)
@JoinColumn(name = "createdById", nullable = false, insertable = false, updatable = false)
public UserInfo getCreatedBy() {
return createdBy;
}
public void setCreatedBy(UserInfo createdBy) {
this.createdBy = createdBy;
}
@Column(name = "createdById", insertable = true, updatable = true)
public Long getCreatedById() {
return createdById;
}
public void setCreatedById(Long createdById) {
this.createdById = createdById;
}
public Date getCreationDatetime() {
return creationDatetime;
}
public void setCreationDatetime(Date creationDatetime) {
this.creationDatetime = creationDatetime;
}
@ManyToOne(fetch = FetchType.LAZY,cascade=CascadeType.ALL)
@JoinColumn(name = "lastModifiedById", nullable = false, insertable = false, updatable = false)
public UserInfo getLastModifiedBy() {
return lastModifiedBy;
}
public void setLastModifiedBy(UserInfo lastModifiedBy) {
this.lastModifiedBy = lastModifiedBy;
}
@Column(name = "lastModifiedById", insertable = true, updatable = true)
public Long getLastModifiedById() {
return lastModifiedById;
}
public void setLastModifiedById(Long lastModifiedById) {
this.lastModifiedById = lastModifiedById;
}
public Date getLastModifiedDatetime() {
return lastModifiedDatetime;
}
public void setLastModifiedDatetime(Date lastModifiedDatetime) {
this.lastModifiedDatetime = lastModifiedDatetime;
}
}
Entity
@Entity
public class Company extends AbstractEntity{
/**
*
*/
private static final long serialVersionUID = 8145899358902689000L;
private String name;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
DAO Configuration
@Configuration
@EnableTransactionManagement
public class DaoSQLConfiguration {
@Value("${jdbc.database.driver.class.name}")
public String DB_DRIVER;
@Value("${jdbc.database.url}")
public String DB_URL;
@Value("${jdbc.database.schema}")
public String DB_SCHEMA;
@Value("${jdbc.database.username}")
public String DB_USER;
@Value("${jdbc.database.password}")
public String DB_PASSWORD;
@Value("${jdbc.database.maxPoolSize}")
public int DB_MAX_POOL_SIZE;
@Value("${jdbc.database.minPoolSize}")
public int DB_MIN_POOL_SIZE;
@Value("${hibernate.dialect}")
public String HIBERNATE_DIALECT;
@Value("${hibernate.show.sql}")
public String HIBERNATE_SHOW_SQL;
@Value("${hibernate.hbm2ddl.auto}")
public String HIBERNATE_HBM2DDL_AUTO;
@Resource
private EntityInterceptor entityInterceptor;
@Bean
public HibernateTransactionManager transactionManager(SessionFactory sessionFactory) {
HibernateTransactionManager transactionManager = new HibernateTransactionManager();
transactionManager.setSessionFactory(sessionFactory);
return transactionManager;
}
@Bean
public HibernateTemplate hibernateTemplate(SessionFactory sessionFactory) {
HibernateTemplate hibernateTemplate = new HibernateTemplate(sessionFactory);
hibernateTemplate.setCheckWriteOperations(false);
return hibernateTemplate;
}
@Bean
public LocalSessionFactoryBean getSessionFactory(DataSource dataSource) {
LocalSessionFactoryBean sessionFactoryBean = new LocalSessionFactoryBean();
sessionFactoryBean.setDataSource(dataSource);
sessionFactoryBean.setHibernateProperties(getHibernateProperties());
sessionFactoryBean.setPhysicalNamingStrategy(new ImprovedNamingStrategy());
sessionFactoryBean.setPackagesToScan(new String[] { "com.entities" });
sessionFactoryBean.setEntityInterceptor(entityInterceptor);
return sessionFactoryBean;
}
@Bean
public Properties getHibernateProperties() {
Properties properties = new Properties();
properties.put("hibernate.dialect", HIBERNATE_DIALECT);
properties.put("hibernate.show_sql", HIBERNATE_SHOW_SQL);
properties.put("hibernate.hbm2ddl.auto", HIBERNATE_HBM2DDL_AUTO);
properties.put("hibernate.physical_naming_strategy", "com.hipzone.sam.dao.sql.ImprovedNamingStrategy");
properties.put("hibernate.enable_lazy_load_no_trans", "true");
return properties;
}
@Bean
public DataSource dataSource() throws PropertyVetoException {
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
comboPooledDataSource.setDriverClass(DB_DRIVER);
comboPooledDataSource.setJdbcUrl(DB_URL + "/" + DB_SCHEMA);
comboPooledDataSource.setUser(DB_USER);
comboPooledDataSource.setPassword(DB_PASSWORD);
comboPooledDataSource.setMaxPoolSize(DB_MAX_POOL_SIZE);
return comboPooledDataSource;
}
}
Config Properties
jdbc.database.driver.class.name=net.sourceforge.jtds.jdbc.Driver
jdbc.database.url=jdbc:jtds:sqlserver://127.0.0.1:1433
jdbc.database.schema=test
jdbc.database.username=sa
jdbc.database.password=
jdbc.database.maxPoolSize=5
jdbc.database.minPoolSize=1
#Hibernate
hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect
hibernate.show.sql=true
hibernate.hbm2ddl.auto=update
Complete Exception
Hibernate: alter table user_info add constraint FK7gdskowpafinvcqig7p419prj foreign key (created_by_id) references user_info
WARN 19-Mar-17 00:48:58 ExceptionHandlerLoggedImpl:handleException:27 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:445)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:710)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:726)
at org.springframework.orm.hibernate5.LocalSessionFactoryBean.buildSessionFactory(LocalSessionFactoryBean.java:511)
at org.springframework.orm.hibernate5.LocalSessionFactoryBean.afterPropertiesSet(LocalSessionFactoryBean.java:495)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1687)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1624)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:555)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:208)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1138)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1066)
at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:835)
at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:741)
at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:467)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1173)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1067)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:513)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.autowireResource(CommonAnnotationBeanPostProcessor.java:522)
at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.getResource(CommonAnnotationBeanPostProcessor.java:496)
at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor$ResourceElement.getResourceToInject(CommonAnnotationBeanPostProcessor.java:627)
at org.springframework.beans.factory.annotation.InjectionMetadata$InjectedElement.inject(InjectionMetadata.java:169)
at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:88)
at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.postProcessPropertyValues(CommonAnnotationBeanPostProcessor.java:318)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1264)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:553)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.autowireResource(CommonAnnotationBeanPostProcessor.java:522)
at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.getResource(CommonAnnotationBeanPostProcessor.java:496)
at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor$ResourceElement.getResourceToInject(CommonAnnotationBeanPostProcessor.java:627)
at org.springframework.beans.factory.annotation.InjectionMetadata$InjectedElement.inject(InjectionMetadata.java:169)
at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:88)
at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.postProcessPropertyValues(CommonAnnotationBeanPostProcessor.java:318)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1264)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:553)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.autowireResource(CommonAnnotationBeanPostProcessor.java:522)
at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.getResource(CommonAnnotationBeanPostProcessor.java:496)
at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor$ResourceElement.getResourceToInject(CommonAnnotationBeanPostProcessor.java:627)
at org.springframework.beans.factory.annotation.InjectionMetadata$InjectedElement.inject(InjectionMetadata.java:169)
at org.springframework.beans.factory.annotation.InjectionMetadata.inject(InjectionMetadata.java:88)
at org.springframework.context.annotation.CommonAnnotationBeanPostProcessor.postProcessPropertyValues(CommonAnnotationBeanPostProcessor.java:318)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.populateBean(AbstractAutowireCapableBeanFactory.java:1264)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:553)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483)
at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:761)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:866)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:542)
at org.springframework.web.context.ContextLoader.configureAndRefreshWebApplicationContext(ContextLoader.java:443)
at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:325)
at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:107)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4810)
at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5255)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
at org.apache.catalina.core.StandardContext.reload(StandardContext.java:3831)
at org.apache.catalina.loader.WebappLoader.backgroundProcess(WebappLoader.java:292)
at org.apache.catalina.core.StandardContext.backgroundProcess(StandardContext.java:5616)
at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1377)
at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1381)
at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.processChildren(ContainerBase.java:1381)
at org.apache.catalina.core.ContainerBase$ContainerBackgroundProcessor.run(ContainerBase.java:1349)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: There is already an object named 'FK7gdskowpafinvcqig7p419prj' in the database.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:613)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:572)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:809)
at net.sourceforge.jtds.jdbc.JtdsStatement.execute(JtdsStatement.java:1282)
at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:1006)
at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54)
... 91 more
Upvotes: 3
Views: 781
Answers (1)
arVahedi
Reputation: 107
Upgrade your hibernate version to 5.4.3.Final and This problem will be solved.
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>5.4.3.Final</version>
</dependency>
You can find maven repository for hibernate 5.4.3.Final here: Hibernate's core ORM Maven repository
Upvotes: 1
Related Questions
- Hibernate: hbm2ddl.auto=update in production?
- Hibernate hbm2ddl.auto=update doesn't update column definitions in MySQL
- hibernate error when hbm2ddl.auto set to update
- hibernate.hbm2ddl.auto property creates table with create but not with update
- Hibernate doesn't add new columns to tables on hbm2ddl.auto="update"
- Why hbm2ddl.auto 'update' property is not working or how does it works?
- Why is hibernate executing update by itself?
- Hibernate : hbm2ddl.auto value="update" or "none"
- hibernate doesn't create middle tables with hbm2ddl.auto set to "update"
- Hibernate hbm2ddl.auto update does not drop columns with mysql