CODEWITHSUNDEEP
hibernatespring-boot
Eric Huang
Eric Huang

Reputation: 1264

Spring Boot Data Initialization getting exception

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);

Upvotes: 0

Views: 2010

Answers (1)

Zeromus
Zeromus

Reputation: 4542

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

Upvotes: 2

Related Questions