Hibernate 5.2.14 ddl update tries to recreate tables in Oracle


#1

Hello there Hibernate community,

I’ve currently migrated from hibernate version 3.x.x to 5.2.x and seem to be having trouble with the hbm2ddl feature when being set to update when being run against Oracle 11g or 12c, because it is trying to recreate tables that are already existent, and trying to assign foreign key constraints to tables that already have a FK defined.

I found this out from the debug logs :
2018-06-11 09:54:41 DEBUG [org.hibernate.SQL] - <create table TICKET (ID number(10,0) not null, ...>
Which fails with: ORA-00955: name is already used by an existing object (because the table already exists)

Also, I see from the log files that hibernate is trying to create FK’s for objects that already have FKs associated:
2018-06-11 09:54:42 DEBUG [org.hibernate.SQL] - <alter table ACL_PERMISSION add constraint FKf3uxxowxhq1obcesxydy92yuq foreign key (PERMISSIONSUBJECTID) references ACL_PERMISSIONSUBJECTS>
Which fails with: ORA-02275: such a referential constraint already exists in the table

After googling around, I’ve found that this seems to be related to both HHH-12059 and HHH-10574 which suggest that the 5.2.14 version of hibernate should have this working. This did not solve my problem.

I’ve also found a stackoverflow topic (can’t provide link due to new user), which suggests the use of Implicit and Physical naming strategies in order to achieve naming backwards compatibility.

I’'ve created the following physical naming strategy (can’t provide link due to new account):

public class ImprovedNamingStrategy implements PhysicalNamingStrategy {

    @Override
    public Identifier toPhysicalCatalogName(Identifier identifier, JdbcEnvironment jdbcEnv) {
        return convert(identifier);
    }

    @Override
    public Identifier toPhysicalColumnName(Identifier identifier, JdbcEnvironment jdbcEnv) {
        return convert(identifier);
    }

    @Override
    public Identifier toPhysicalSchemaName(Identifier identifier, JdbcEnvironment jdbcEnv) {
        return convert(identifier);
    }

    @Override
    public Identifier toPhysicalSequenceName(Identifier identifier, JdbcEnvironment jdbcEnv) {
        return convert(identifier);
    }

    @Override
    public Identifier toPhysicalTableName(Identifier identifier, JdbcEnvironment jdbcEnv) {
        return convert(identifier);
    }

    private Identifier convert(Identifier identifier) {
        if (identifier == null || StringUtils.isBlank(identifier.getText())) {
            return identifier;
        }

        String regex = "([a-z])([A-Z])";
        String replacement = "$1_$2";
        String newName = identifier.getText().replaceAll(regex, replacement).toLowerCase();
        return Identifier.toIdentifier(newName);
    }
}

which I’ve tried using, but hibernate seems to be completely ignoring (a breakpoint in the class never got called).

My current configuration:

<!-- Using Spring XML configuration -->
 <bean id="sessionFactory" class="org.springframework.orm.hibernate5.LocalSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <property name="hibernateProperties" ref="hibernateProperties"/>
        <property name="annotatedClasses" ref="annotatedClasses"/>
    </bean>

<bean id="hibernateProperties" class="org.springframework.beans.factory.config.PropertiesFactoryBean" >
        <property name="properties">
            <props>
                <prop key="hibernate.dialect">${hibernate.dialect}</prop>
                <prop key="hibernate.max_fetch_depth">3</prop>
                <prop key="hibernate.cache.use_second_level_cache">true</prop>
                <prop key="hibernate.cache.use_query_cache">true</prop>
                <prop key="hibernate.hbm2ddl.auto">update</prop>
                <!-- I've tried both creating a custom implicit strategy or using an existent one, but they seem to never get called -->
                <prop key="hibernate.implicit_naming_strategy">legacy-hbm</prop>
                <prop key="hibernate.physical_naming_strategy">net.grouplink.ehelpdesk.dao.hibernate.naming.ImprovedNamingStrategy</prop>
                
                <prop key="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory</prop>
                <prop key="hibernate.cache.provider_configuration">classpath:ehcache.xml</prop>
                <prop key="hibernate.jdbc.batch_size">15</prop>
                <prop key="hibernate.show_sql">${jdbc.showSql}</prop>
                <prop key="hibernate.search.default.directory_provider">org.hibernate.search.store.impl.FSDirectoryProvider</prop>
                <prop key="hibernate.search.default.indexBase">${hibernate.search.default.indexBase}</prop>
                <prop key="javax.persistence.validation.mode">none</prop>
            </props>
        </property>
    </bean>

Current framework versions:

<spring.version>4.3.8.RELEASE</spring.version>
<hibernate.version>5.2.14.Final</hibernate.version>
<oracle.ojdbc.version>11.2.0.3</oracle.ojdbc.version>

Should the naming strategies be passed on differently to the LocalSessionFactoryBean ? Am I missing something?

Thank you very much, and keep leading the defining the ORM world :smiley:


#2

The stackoverflow post regarding Implicit / Physical naming strategies: https://stackoverflow.com/questions/32165694/spring-hibernate-5-naming-strategy-configuration

The hibernate docs:
https://docs.jboss.org/hibernate/orm/5.2/userguide/html_single/chapters/domain/naming.html


#3

Try with 5.2.17 and see if it’s working.

If it does not, create a replicating test case as explained in this article:

http://in.relation.to/2018/06/04/best-way-write-hibernate-orm-issue-test-case/

If you can prove the issue, open a Jira issue.


#4

Thank you @vlad for the quick response,

I’ve also tried using 5.2.17 and still have the same issues, so I’ll try creating a test case.

Cheers


#5

Great, thanks. Anyway, hbm2dll is not recommended for production usage. You should use a tool like FlywayDB instead


#6

I totally agree with you on this one that a solution such as Flyway or Liquibase would do wonders :smiley:

I’ve checked out the hibernate repo, and noticed that the changelog.txt shows that the issue: HHH-12059 is resolved in hibernate version 5.3.0.CR2.

I did try upgrading hibernate to this version, and now I get ehcache config errors.

Is a test case still relevant in this context?


#7

5.3.1 still has an issue with auto registering caches. Maybe we should backport that fix to 5.2 as well.


#8

Started creating a test case (which is a really cool process guys, GG) , but I don’t know how I can run this test against multiple versions of hibernate, in order to test the naming strategy.

Seems like I may be up against a grim scenario with the ddl update :frowning:


#9

You can’t run it against multiple versions o Hibernate, but you could just take the DDL script from 3.x and run it before you bootstrap Hibernate.


#10

I’ve tried upgrading our project to hibernate 5.3.1 and removing all the cache configurations, and although I see in the hibernate commit log that issue HHH-12059 was addressed and fixed, it seems like the errors won’t go away, and the hbm2ddl is still trying to recreate the tables.

Hibernate commits addressing issue HHH-12059:

Are you sure that this can’t be somehow solved using a naming strategy?

I do not not where to find the mentioned DDL script for hibernate 3.x and neither how to run it within the test against the dockerized oracle db. Can i find an example for doing this somewhere in the repo?


#11

I’m not sure if you can completely solve it with the naming strategy. Most likely you’ll have to debug Hibernate and try to see why it does not detect the tables, columns.