Hibernate fails to process auto_increment primary key when using mode=mysql

[Gerry Matte]

Hello.

I'm a long time user of mysql and I'm migrating a mysql database to H2 so that I can use an embedded database in a standalone java application.

I've created an H2 table after connecting to my H2 database using a url containing mode=mysql

The sql I submitted was generated by the mysql backup utility - see the attached H2 console history.

When I attempted loading data to this table using Hibernate version 5.2.10/Final and JPA 2.1, the hibernate dialect property org.hibernate.dialect.H2Dialect did not correctly handle inserts of new rows.

With mysql, inserts omitted the primary key auto insert column since mysql generates those values.

With H2 in mysql mode (and using org.hibernate.dialect.H2Dialect) hibernate generated an sql insert that passed null as the primary key.  This failed to be accepted by H2 version 1.4.196.

However, if I use a hibernate dialect of org.hibernate.dialect.MySQL5Dialect, hibernate correctly omits the use of my auto increment primary key when generating an insert:

Hibernate: 

    /* insert ca.gerrymatte.finance.model.Tags

        */ insert 

        into

            tags

            (created, createdBy, disabled, invisible, tagDescription, tagName, toBeDeleted, updated, updatedBy) 

        values

            (?, ?, ?, ?, ?, ?, ?, ?, ?)

15-Jun-2017 14:47:37 WARN  [o.h.e.jdbc.spi.SqlExceptionHelper] SQL Error: 23505, SQLState: 23505

15-Jun-2017 14:47:37 ERROR [o.h.e.jdbc.spi.SqlExceptionHelper] Unique index or primary key violation: "TAGNAMEKEY_INDEX_2 ON PUBLIC.TAGS(TAGNAME) VALUES ('Aimee-Van Fuel', 1)"; SQL statement:

/* insert ca.gerrymatte.finance.model.Tags */ insert into tags (created, createdBy, disabled, invisible, tagDescription, tagName, toBeDeleted, updated, updatedBy) values (?, ?, ?, ?, ?, ?, ?, ?, ?) [23505-196]

15-Jun-2017 14:47:37 ERROR [c.g.f.utility.quicken.QIFLoader] Line:3JPA persistence exception creating Tag(JdbcSQLException: Unique index or primary key violation: "TAGNAMEKEY_INDEX_2 ON PUBLIC.TAGS(TAGNAME) VALUES ('Aimee-Van Fuel', 1)"; SQL statement:

/* insert ca.gerrymatte.finance.model.Tags */ insert into tags (created, createdBy, disabled, invisible, tagDescription, tagName, toBeDeleted, updated, updatedBy) values (?, ?, ?, ?, ?, ?, ?, ?, ?) [23505-196])

I have concluded that H2 is not correctly handling the auto increment column.

The org.hibernate.dialect.H2Dialect causes hibernate to generate insert statements that attempt to load null into my id field.

I am using transactions enclosing my hibernate persist() methods ......

Can anyone suggest how I might resolve this problem ?

[Noel Grandin]

On 16 June 2017 at 01:23, Gerry Matte <gerry.m...@gmail.com> wrote:

Hibernate: 

    /* insert ca.gerrymatte.finance.model.Tags

        */ insert 

        into

            tags

            (created, createdBy, disabled, invisible, tagDescription, tagName, toBeDeleted, updated, updatedBy) 

        values

            (?, ?, ?, ?, ?, ?, ?, ?, ?)

15-Jun-2017 14:47:37 WARN  [o.h.e.jdbc.spi.SqlExceptionHelper] SQL Error: 23505, SQLState: 23505

15-Jun-2017 14:47:37 ERROR [o.h.e.jdbc.spi.SqlExceptionHelper] Unique index or primary key violation: "TAGNAMEKEY_INDEX_2 ON PUBLIC.TAGS(TAGNAME) VALUES ('Aimee-Van Fuel', 1)"; SQL statement:

/* insert ca.gerrymatte.finance.model.Tags */ insert into tags (created, createdBy, disabled, invisible, tagDescription, tagName, toBeDeleted, updated, updatedBy) values (?, ?, ?, ?, ?, ?, ?, ?, ?) [23505-196]

15-Jun-2017 14:47:37 ERROR [c.g.f.utility.quicken.QIFLoader] Line:3JPA persistence exception creating Tag(JdbcSQLException: Unique index or primary key violation: "TAGNAMEKEY_INDEX_2 ON PUBLIC.TAGS(TAGNAME) VALUES ('Aimee-Van Fuel', 1)"; SQL statement:

/* insert ca.gerrymatte.finance.model.Tags */ insert into tags (created, createdBy, disabled, invisible, tagDescription, tagName, toBeDeleted, updated, updatedBy) values (?, ?, ?, ?, ?, ?, ?, ?, ?) [23505-196])

This error has nothing to do with auto increment columns. you have a unique index on tagname and you're inserting a duplicate value.