Liquibase: Change a INT autoincrement column to BIGINT using modifyDataType refactoring with H2 database

Question

I have a primary key column which is an INT column which I would like to change to a BIGINT. Our test and production environment uses MySQL, but for unit tests we use the embedded H2 database.

I have created the following Liquibase refactoring:

...
<changeSet id="1" author="trond">
    <modifyDataType tableName="event" columnName="id" newDataType="BIGINT" />
    <rollback>
        <modifyDataType tableName="event" columnName="id" newDataType="INT" />
    </rollback>
</changeSet>
...

The refactoring works, but when I try to persist an object to the database using Hibernate, I get the following error message (I've wrapped the error message):

ERROR org.hibernate.util.JDBCExceptionReporter [main]: NULL not allowed for column "ID"; 
    SQL statement: insert into event (id, eventtime, guid, meta, objectguid, originatorid, subtype, type) values (null, ?, ?, ?, ?, ?, ?, '0') [90006-140]

JDBC exception on Hibernate data access: 
    SQLException for SQL [insert into event (id, eventtime, guid, meta, objectguid, originatorid, subtype, type) values (null, ?, ?, ?, ?, ?, ?, '0')]; 
    SQL state [90006]; error code [90006]; could not insert: [event.MyEvent]; 
    nested exception is org.hibernate.exception.GenericJDBCException: could not insert: [event.MyEvent]

The MyEvent class inherits from a AbstractBaseEvent which has defined the following Hibernate mapping in the code:

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private long id;

A few points:

• The hibernate mapping works before the refactoring of the data type
• Version of Liquibase is 2.0.1
• Whether or not this works with MySQL hasn't been tested yet

Answer 1

I tested (Hibernate 3.6.2.Final, H2 1.3.160, dialect: org.hibernate.dialect.H2Dialect) what is happening in your case:

• When GenerationType is AUTO and data type is INT, actual generation type is SEQUENCE.
• When GenerationType is AUTO and data type is BIGINT, actual generation type is IDENTITY. As result this will fail, if id-field is defined as ID BIGINT PRIMARY KEY and not as ID BIGINT IDENTITY (adding PRIMARY KEY here with H2 would be redundant).

What you can do:

If you want actual generation type to be SEQUENCE, as before, then

@GeneratedValue(strategy = GenerationType.SEQUENCE)

seems to work. No changes needed for sequence itself, because according documentation type is BIGINT anyway. I would do this way, because then nothing really changes and it is clear which way sequence is generated

Other possibility is to define column as IDENTITY with startValue(because of possible existing values) and use GenerationType.AUTO as before.

Answer 2

I would first change your @GenerationType to something specific (like IDENTITY) just to rule out any problems with Hibernate getting weird values from a sequence. Or remove it altogether.

Your refactoring looks fine, and I can't see any obvious problems.

H2 and Liquibase often don't play together nicely when it comes to quoted identifiers; the H2 database class in Liquibase quotes some and doesn't quote others. Maybe case conversion is screwing you up?

EclipseLink sometimes has problems when a primitive type is 0 (!) since it sometimes will treat such a value as null or uninitialized, but to my knowledge Hibernate does not suffer from this limitation.

This is not really an answer, I know, but hopefully should get you pointed in the right direction.