liquibase is not creating unsigned columns

Question

My Environment:

java: 1.8.0_20, 64 bit
liquibase: 3.3.1
mysql: 5.5.34
mysql connector: mysql-connector-java-5.1.34-bin.jar
mysql driver: com.mysql.jdbc.Driver
mysql connection string: jdbc:mysql://localhost/my_db
mysql user: root user
os: windows 7 64

database change log xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" 
xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.3.xsd">

<changeSet author="jbenton" id="create my_test_tbl table">
   <sql> SET storage_engine=MYISAM; </sql>
    <createTable tableName="my_test_tbl">
        <column autoIncrement="true" name="my_test_tbl_id" type="INT UNSIGNED">
            <constraints primaryKey="true"/>
        </column>
        <column defaultValueNumeric="0" name="col_smallint" type="SMALLINT">
            <constraints nullable="false"/>
        </column>
        <column defaultValueNumeric="0" name="col_smallint_unsigned" type="SMALLINT UNSIGNED"/>
        <column defaultValueNumeric="0" name="col_smallint_unsigned_not_null" type="SMALLINT UNSIGNED">
            <constraints nullable="false"/>
        </column>
    </createTable>
</changeSet>
</databaseChangeLog>

Using the updateSql command, I see the following sql being generated

CREATE TABLE my_db.my_test_tbl (
   my_test_tbl_id INT AUTO_INCREMENT NOT NULL, 
  col_smallint SMALLINT DEFAULT 0 NOT NULL, 
  col_smallint_unsigned SMALLINT DEFAULT 0 NULL, 
  col_smallint_unsigned_not_null SMALLINT DEFAULT 0 NOT NULL, 
  CONSTRAINT PK_MY_TEST_TBL PRIMARY KEY (my_test_tbl_id));

My goal is that the columns would be SMALLINT UNSIGNED. Is there something that I am doing wrong?

Sorry, I don't really see what could be wrong here. Looking at the sources I can see a comment /always smallint regardless of parameters passed on the SmallIntType.java class. I don't oversee the whole code and therefore can't say for sure if this really indicates that further parameters to the type (like "unsigned") are left out. Maybe you can file a bug/enhancement on liquibase-github for this? — Jens
I'll file a bug. I just wanted to make sure there was nothing obvious that I had missed. I have seen several examples on stackoverflow where it seems this has worked for others. Note that the id is an int that I defined as 'int unsigned' and it ends up as a plain old int. — Jeff Benton
in version 3.3.2 type="BIGINT UNSIGNED" even for that also its created just BIGINT(20) not unsigned — arulraj.net
some people are saying UNSIGNED is not an SQL standard. So that liquibase may not be supported that. — arulraj.net

Mark Chesney Mark Chesney · Accepted Answer · 2015-05-01T04:44:28

I implemented CORE-2300 Unsigned Int / Bigint cannot be created that fixes this issue. It has been merged to the master branch. If you can use a -SNAPSHOT version based on master then you could get the fix before the 3.4.0 release, which I suspect will be in the next few months.

If you're using Maven:

<dependency>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-core</artifactId>
    <version>3.4.0-SNAPSHOT</version>
</dependency>

Or download it from the build server:

https://liquibase.jira.com/builds/browse/CORE-LB-90/artifact

liquibase-3.4.0-SNAPSHOT-bin.zip

Or if you want to use 3.3.x you could create your own 3.3.4-SNAPSHOT

$ git clone https://github.com/liquibase/liquibase.git
$ cd liquibase
$ git checkout 3.3.x
$ git cherry-pick 5bf8fc591477587c3f61c876e91011d0b8a6d362
$ git status
$ # resolve the merge conflicts
$ vi liquibase-core/src/main/java/liquibase/datatype/core/*IntType.java
$ git add '*.java'
$ git cherry-pick --continue
$ mvn clean verify

Then update your POM:

<dependency>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-core</artifactId>
    <version>3.3.4-SNAPSHOT</version>
</dependency>

or use the resultant distribution zip file.

liquibase is not creating unsigned columns

2 Answers