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

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.JDBC开发者_如何转开发ExceptionReporter [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

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.

This is an old question and the accepted answer has a very good analysis but I came across the same problem and have spent 3 hours to find the real issue and a solution, so it's worth leaving it here for future references.

So the real problem is that on liquibase this statement

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

is generating this alter query

ALTER TABLE event ALTER COLUMN id BIGINT

which removes the default sequence value from the id column in H2

As a result, when you try to insert a new row the id is null thus the SQL error is thrown.

Unfortunately, the behavior of ALTER column varies per database vendor so probably the best solution would be to create different migration changelogs for testing (H2) and production (MySQL)

and on H2 you can make the id auto_increment again after the modifyDataType change

<addAutoIncrement
    columnDataType="bigint"
    columnName="id"
    incrementBy="1"
    startWith="1"
    tableName="event"/>
</changeSet>`

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.