Liquibase inserting into BIT column, MySQL, data too long for column

Question

In Liquibase, I define a table with a column of type BIT(1)

<changeSet author="foobar" id="create-configuration-table">
    <createTable tableName="configuration">
        <column autoIncrement="true" name="id" type="BIGINT(19)">
            <constraints primaryKey="true" />
        </column>
        <column name="active" type="BIT(1)" />
        <column name="version" type="INT(10)" />
    </createTable>
</changeSet>

In the subsequent changeset, I want to insert data into this table, however, when inserting data into the 'active' column of type BIT(1), MySQL complains 'Data truncation: Data too long for column'

I have tried:

<insert>
   <column name="active" value="1" type="BIT(1)" />
</insert>

and

<insert>
   <column name="active" value="1"/>
</insert>

and

<insert>
   <column name="active" value="TRUE" type="BOOLEAN"/>
</insert>

What is the correct way to insert into a BIT(1) column?

there is an [open issue](https://liquibase.jira.com/browse/CORE-1991) on that — Paizo, Sep 18 '15 at 12:46

dustin.schultz · Accepted Answer · 2016-06-07T14:29:53.233

45

Answering my own question as I figured this out right after I posted it. To insert into a BIT(1) column, you need to define the value as valueBoolean

<insert>
   <column name="active" valueBoolean="true"/>
</insert>

edited Jun 07 '16 at 14:29

answered Jul 06 '15 at 18:13

dustin.schultz

13,076
7
54
63

I had similar problem with data load from CSV. If data is loaded from CSV node should declare field as , and values in CSV should be true/false – igor.beslic Apr 10 '18 at 08:39

score 13 · Answer 2 · answered Aug 12 '16 at 13:09

There is a similar case when loading records per table from csv files with <loadData>. In the <loadData> element, you have to explicitly specify type for each Boolean columns in the table:

<loadData encoding="UTF-8"
          file="path/to/file.csv"
          separator=","
          tableName="MY_TABLE"
>
    <!-- specify that values in my_boolean_column should be interpreted as Boolean values -->
    <column name="my_boolean_column" type="BOOLEAN" />
</loadData>

Hope it helps other folks who landed here having trouble with this.

It didn't work for me. Liquibase still tries to insert '1' into the column. — Pierre Henry, Jan 25 '17 at 09:08

score 3 · Answer 3 · answered Aug 01 '16 at 12:42

3

In my case, I was using loadData instead of insert, and I had to use the following:

<column name="active" type="boolean"/>

answered Aug 01 '16 at 12:42

Christophe Moine

816
1
7
7

To indicate that the value to be inserted is a bit (or multiple bits) Mysql and MariaDB use the notations: b'0' respectively b'1' or for multiple bits e.g. b'00100110' – Hubert Schumacher Feb 04 '17 at 06:07

score 0 · Answer 4 · edited May 28 '17 at 15:57

0

In my case with MariaDB it had to be:

<column name="show_in_app_directory" type="bit" valueBoolean="true" />

Leaving out 'type="bit"' as suggested by dustin.schultz I get a Liquibase validation error:

column 'type' is required for all columns

edited May 28 '17 at 15:57

dustin.schultz

13,076
7
54
63

answered May 28 '17 at 14:09

Hubert Schumacher

1,683
1
16
25

Liquibase inserting into BIT column, MySQL, data too long for column

4 Answers4