Seems JPA spring.jpa.hibernate.ddl-auto=update doesn't work

Question

I build a simple SpringBoot App (version: 2.4.3). I have the following Entity:

package com.ksteindl.logolo.domain;

import javax.persistence.Entity;
import javax.persistence.Id;

@Entity
public class Foo {

    @Id
    private String id;
    private String foo;

    public String getFoo() {
        return foo;
    }

    public void setFoo(String foo) {
        this.foo = foo;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getId() {
        return id;
    }
}

and an application.properties

spring.jpa.show-sql=true

spring.datasource.url = jdbc:mysql://localhost:3306/mysql
spring.datasource.username=admin
spring.datasource.password=admin

spring.jpa.database-platform=org.hibernate.dialect.MySQL5Dialect

spring.jpa.hibernate.ddl-auto=update

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL57Dialect

At first run, everything seems fine. The tables are created according to the Entites. However, when I restart my app, I get exceptions for every Entity/Table I have. Moreover, if I modify my Entity (for example introduce a bar attribute in Foo Entity), the corresponding Table didn't change in the DB. It seems to me, that it tries to re-create the tables, but I don't know why. I got the following Exceptions for every Table/Entity I have:

Hibernate: create table foo (id varchar(255) not null, bar varchar(255), baz varchar(255), foo varchar(255), primary key (id)) engine=InnoDB
2021-04-02 16:34:17.000  WARN 16359 --- [  restartedMain] o.h.t.s.i.ExceptionHandlerLoggedImpl     : GenerationTarget encountered exception accepting command : Error executing DDL "create table foo (id varchar(255) not null, bar varchar(255), baz varchar(255), foo varchar(255), primary key (id)) engine=InnoDB" via JDBC Statement

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table foo (id varchar(255) not null, bar varchar(255), baz varchar(255), foo varchar(255), primary key (id)) engine=InnoDB" via JDBC Statement
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlString(AbstractSchemaMigrator.java:559) [hibernate-core-5.4.28.Final.jar:5.4.28.Final]
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlStrings(AbstractSchemaMigrator.java:504) [hibernate-core-5.4.28.Final.jar:5.4.28.Final]
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.createTable(AbstractSchemaMigrator.java:277) [hibernate-core-5.4.28.Final.jar:5.4.28.Final]
    at org.hibernate.tool.schema.internal.GroupedSchemaMigratorImpl.performTablesMigration(GroupedSchemaMigratorImpl.java:71) [hibernate-core-5.4.28.Final.jar:5.4.28.Final]
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:207) [hibernate-core-5.4.28.Final.jar:5.4.28.Final]
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:114) [hibernate-core-5.4.28.Final.jar:5.4.28.Final]
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:184) [hibernate-core-5.4.28.Final.jar:5.4.28.Final]
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:73) [hibernate-core-5.4.28.Final.jar:5.4.28.Final]
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:318) [hibernate-core-5.4.28.Final.jar:5.4.28.Final]
    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:468) [hibernate-core-5.4.28.Final.jar:5.4.28.Final]
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1259) [hibernate-core-5.4.28.Final.jar:5.4.28.Final]
    at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58) [spring-orm-5.3.4.jar:5.3.4]
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365) [spring-orm-5.3.4.jar:5.3.4]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:409) [spring-orm-5.3.4.jar:5.3.4]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:396) [spring-orm-5.3.4.jar:5.3.4]
    at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341) [spring-orm-5.3.4.jar:5.3.4]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1845) [spring-beans-5.3.4.jar:5.3.4]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1782) [spring-beans-5.3.4.jar:5.3.4]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:602) [spring-beans-5.3.4.jar:5.3.4]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:524) [spring-beans-5.3.4.jar:5.3.4]
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335) [spring-beans-5.3.4.jar:5.3.4]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-5.3.4.jar:5.3.4]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333) [spring-beans-5.3.4.jar:5.3.4]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) [spring-beans-5.3.4.jar:5.3.4]
    at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1153) ~[spring-context-5.3.4.jar:5.3.4]
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:907) ~[spring-context-5.3.4.jar:5.3.4]
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:582) ~[spring-context-5.3.4.jar:5.3.4]
    at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:144) ~[spring-boot-2.4.3.jar:2.4.3]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:767) ~[spring-boot-2.4.3.jar:2.4.3]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:759) ~[spring-boot-2.4.3.jar:2.4.3]
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:426) ~[spring-boot-2.4.3.jar:2.4.3]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:326) ~[spring-boot-2.4.3.jar:2.4.3]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1311) ~[spring-boot-2.4.3.jar:2.4.3]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1300) ~[spring-boot-2.4.3.jar:2.4.3]
    at com.ksteindl.logolo.LogoloApplication.main(LogoloApplication.java:10) ~[classes/:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_282]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_282]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_282]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_282]
    at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49) ~[spring-boot-devtools-2.4.3.jar:2.4.3]
Caused by: java.sql.SQLSyntaxErrorException: Table 'foo' already exists
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.23.jar:8.0.23]
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.23.jar:8.0.23]
    at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:762) ~[mysql-connector-java-8.0.23.jar:8.0.23]
    at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:646) ~[mysql-connector-java-8.0.23.jar:8.0.23]
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95) ~[HikariCP-3.4.5.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-3.4.5.jar:na]
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54) ~[hibernate-core-5.4.28.Final.jar:5.4.28.Final]
    ... 40 common frames omitted

Can someone please help me? Thanks

can you try with spring.jpa.hibernate.hbm2ddl.auto=update? — Eugene
– Eugene, Commented Apr 2, 2021 at 15:51
Unfortunately it doesn't work. If I replace, it works like there was no ddl property at all (the default is active, like spring.jpa.hibernate.ddl-auto=none). — Kristóf Steindl
– Kristóf Steindl, Commented Apr 2, 2021 at 18:21

Dharman · Accepted Answer · 2021-04-02 16:01:52Z

4

In some cases, Hibernate is not able to make some changes on the database schema by itself.

That's why in general we try not to set ddl-auto to update and we prefer to use a data migration tool like liquibase or flyway.

In very simple cases like adding a column for example, Hibernate will work very well because it's a non breaking change. On the other hand, if you want to delete a column for example, it becomes more delicate. In this case your java entity will not know about this column anymore, but in the database it will still exist.

This is both a problem and a good thing because Hibernate makes sure that you don't lose data, but it is also the reason why some changes are not made.

As I told you above, we will prefer to use data migration tools, which are made to do this kind of things.

You can have a look at Liquibase for example, which is a very good tool. Here are some useful links:

But you can also use flyway (which I don't know) : https://flywaydb.org/

And finally, here is a comparison between Liquibase and Flyway rather interesting: https://dzone.com/articles/flyway-vs-liquibase

edited Apr 2, 2021 at 16:01

Dharman♦

33.9k27 gold badges106 silver badges157 bronze badges

answered Apr 2, 2021 at 15:56

Bad_Pop

1251 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kristóf Steindl Over a year ago

Thanks for your reply! However, I get errors even if I don't modify anything. From the error it seems to me that Hibernate tries to create tables, but I don't know why. Tables are already created (during the very first run).

Bad_Pop Over a year ago

I just saw your stacktrace because you edited your post, and if you look carefully it is a warning and not an error. Can you try to name explicitly all your constraints and keys ? Because I think Hibernate tries to recreate them but they already exist and that it's because you didn't name it explicitly

Kristóf Steindl Over a year ago

Yes, but after that warning, an Exception is thrown, and my table is not updating. I am trying to implement a light-weight solution, and this is exactly what SpringBoot with JPA promises. Giving names for constraints is something that I expect to do JPA for me. Of course that I can be specific, and sometimes this is the way. However, I think the problem is simply my new version of SpringBoot is not compatible with my new version of Mysql (see my response above), with Postgres everything works fine.

Bad_Pop Over a year ago

Indeed you have an exception but it is in the warning log. But if the problem does not occur with Postgres it may be a compatibility problem indeed. Anyway I think you can either ignore these warnings or try to name everything explicitly, because it should work since "they are only" warnings. And again I think you should take a look at a data migration tool, it will make your life easier ;) Good luck to you anyway

Kristóf Steindl · Accepted Answer · 2021-04-03 17:05:21Z

-1

It looks like in 2.4.3 SpringBoot JPA ddl-auto=update behavior doesn't work with MySql 8. With PostgreSQL 13, the ddl-auto=update behavior works perfectly, without any error. my application.yml

spring:
  jpa:
    hibernate:
      ddl-auto: update
    database-platform: org.hibernate.dialect.PostgreSQLDialect
  datasource:
    url: "jdbc:postgresql://localhost:5432/mydb"
    username: myuser
    password: mypassword

If someone can confrim/condtradict, that would be reassuring.

answered Apr 3, 2021 at 17:05

Kristóf Steindl

581 gold badge2 silver badges7 bronze badges

Collectives™ on Stack Overflow

Seems JPA spring.jpa.hibernate.ddl-auto=update doesn't work

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related