0

In my setup i have datasource.initialization set to always.

Problem

spring.jpa.hibernate.ddl-auto = create This works as designed every time I start up the application or make a change to application it re-creates the DB and then inserts data from data.sql

But when I change from create to update I get MySQLIntegrityConstraintViolationException. Update means that it only start doing work if Hibernate detects a change right? Then why am I getting duplicate entry?

https://docs.jboss.org/hibernate/orm/5.2/userguide/html_single/Hibernate_User_Guide.html#configurations-hbmddl

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '10001' for key 'PRIMARY'
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_144]
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_144]
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_144]
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_144]
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.Util.getInstance(Util.java:408) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2482) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2440) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745) ~[mysql-connector-java-5.1.46.jar:5.1.46]
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95) ~[HikariCP-2.7.8.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-2.7.8.jar:na]
    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:473) ~[spring-jdbc-5.0.5.RELEASE.jar:5.0.5.RELEASE]
    ... 73 common frames omitted

Application.properties

## DB confige
....

## Hibernate Properties
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect


# Hibernate ddl auto (create, create-drop, validate, update)
spring.datasource.initialization-mode=always
spring.jpa.hibernate.ddl-auto = create  ###### <---- Problem

data.sql

insert into course (id, name, created_date, last_updated_date)
values(10001, 'JPA in 50 steps', NOW(), NOW()),
        (10002, 'Spring', NOW(), NOW()),
        (10003, 'Spring Boot', NOW(), NOW());

insert into review(id, description, rating, course_id)
values(50001, 'Okey course', '3', 10001),
        (50002, 'Awesome course', '5', 10001),
        (50003, 'Greate Course', '4', 10003);

insert into passport(id, number)
values(40001, 'ASD2342324'),
        (40002, 'NMWO23423'),
        (40003, 'POOI9098473');


insert into student(id, name, passport_id)
values(20001, 'Ranga', 40001),
        (20002, 'Adam', 40002),
        (20003, 'Jane', 40003);

insert into student_course(student_id, course_id)
values(20001,10001),
(20002,10001),
(20003,10001),
(20001,10003);

1 Answer 1

2

The problem is spring.jpa.hibernate.ddl-auto = create will drop database and recreate everytime you start your application.

While spring.jpa.hibernate.ddl-auto = update will fire query to update your database based on entity changes without dropping it or any data inside.

So with update and your data.sql that is always run, it's obviously going to insert rows that are already there.

As a quick-fix you can set spring.datasource.continue-on-error property and ignore the data.sql failure.

But it might be worth learning to use Flyway or Liquibase as suggested in the docs as they are much more robust then the ddl-auto = update.

https://docs.spring.io/spring-boot/docs/current/reference/html/howto-database-initialization.html

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

2 Comments

Okey... so update it doesn't check if there are any changes. it just re-initialize database with data.sql?.... Humm looking back on the doc I thought I read somewhere it says hibernate will compare and if there are changes it will update
But before spring 2.x the same scenario worked fine without exception.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.