How to force Hibernate to add ID column to insert query?

Question

I'm using Spring Boot 1.4.0.RELEASE with the following DB connectors in dependencies

<!-- runtime dependencies -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

And there is an entity class with GenerationType.AUTO policy for ID generation (code below is not complete)

@Entity
@Table(name = "scanner_run")
public class ScannerRun extends BaseObject {

    private Long id;

    @Id @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    public Long getId() {
        return this.id;
    }
}

There are not problems with insert of new entities, when H2 is used

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

Hibernate generates insert into scanner_run (id, completed_ts, repository_id, started_ts, success) values (null, ?, ?, ?, ?) query and new record is created.

However with MySQL

spring.datasource.url=jdbc:mysql://localhost/db_dev?createDatabaseIfNotExist=true&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&connectionCollation=utf8_general_ci
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

the generated query is insert into scanner_run (completed_ts, repository_id, started_ts, success) values (?, ?, ?, ?) - ID is not in the query - and it fails.

There are no other differences, only change in application.properties to swapp the database. The same code with older versions of Hibernate and MySQL connector works with the same installation of MySQL. MySQL connector resolves to mysql:mysql-connector-java:jar:5.1.39

Can you spot anything wrong? The exact messages and exception in the logs are:

2016-08-26 14:38:03.964 DEBUG 32555 --- [  myScheduler-1] org.hibernate.SQL                        : insert into scanner_run (completed_ts, repository_id, started_ts, success) values (?, ?, ?, ?)
2016-08-26 14:38:03.967  WARN 32555 --- [  myScheduler-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1364, SQLState: HY000
2016-08-26 14:38:03.967 ERROR 32555 --- [  myScheduler-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : Field 'id' doesn't have a default value
2016-08-26 14:38:03.979 ERROR 32555 --- [  myScheduler-1] o.s.s.s.TaskUtils$LoggingErrorHandler    : Unexpected error occurred in scheduled task.

jvr.decrex.exception.ExecutionError: Failed to save ScannerRun{id=null, repository=/jv-ration/projects/jv-ration/deCrex/jvr-decrex/, startedTs=Fri Aug 26 14:38:03 CEST 2016, completedTs=null}
    at jvr.decrex.service.impl.GenericManagerImpl.insert(GenericManagerImpl.java:107)
    at jvr.decrex.scanner.service.impl.ScannerRunManagerImpl.createScan(ScannerRunManagerImpl.java:79)
.........
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
Caused by: org.springframework.orm.jpa.JpaSystemException: could not execute statement; nested exception is org.hibernate.exception.GenericJDBCException: could not execute statement
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:333)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:244)
.........
    at jvr.decrex.scanner.dao.jpa.ScannerRunDaoJpa$$EnhancerBySpringCGLIB$$5e6c846a.insert()
    at jvr.decrex.service.impl.GenericManagerImpl.insert(GenericManagerImpl.java:105)
    ... 21 common frames omitted
Caused by: org.hibernate.exception.GenericJDBCException: could not execute statement
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
..........
    at com.sun.proxy.$Proxy126.persist(Unknown Source)
    at jvr.decrex.dao.jpa.GenericDaoJpa.insert(GenericDaoJpa.java:137)
    at jvr.decrex.dao.jpa.GenericDaoJpa$$FastClassBySpringCGLIB$$6605cd4e.invoke()
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:720)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
    ... 25 common frames omitted
Caused by: java.sql.SQLException: Field 'id' doesn't have a default value
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
.........
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2376)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2360)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:204)
    ... 58 common frames omitted

I tried using older 5.1.27 version of mysql-connector-java, which works with older version of Hibernate - it throws the same error

I added the exact errors and exception to the question. Thanks — Viktor, Aug 26 '16 at 12:46

Neil Stockton · Accepted Answer · 2016-08-26T13:57:45.417

You omit the database schema(s) so an amount of guesswork is to be made. The following is what can be said :-

AUTO generation strategy means the JPA provider can choose whatever it wants as the strategy. It seems that for MySQL it uses AUTOINCREMENT columns (equivalent to IDENTITY generation strategy), and for H2 it maybe uses a SEQUENCE (guessing since you provide no details of how). Maybe you don't have AUTOINCREMENT defined for the PK column with MySQL? but you can't use AUTO strategy in that case, and you are.

You could handle it by having an orm.xml for each datastore you will deploy to, and then you can use different generation strategies based on which datastore.

Alternatively choose TABLE generation strategy and it will insert the "id" column each time regardless of the datastore.

Or choose IDENTITY (when you use MySQL AUTOINCREMENT column for PK, and H2 IDENTITY column for PK) since H2 would then use that also (clearly this is not an option if you also need to support another datastore that has no such IDENTITY support).

Using `TABLE` strategy helps, although I'm seeing `HHH90000015: Found use of deprecated [org.hibernate.id.MultipleHiLoPerTableGenerator] table-based id generator` now. Will switch to `TABLE` to insure it remains working with next upgrades — Viktor, Aug 26 '16 at 13:46

How to force Hibernate to add ID column to insert query?

1 Answers1