MySQL boolean fields created as tinyint(1) on OSX and Linux, bit(1) on Windows
Description
Environment
MySQL 5.5 community edition, OSX Mavericks
MySQL 5.5 community edition, Ubuntu linux 14.04
Activity
Show:
Nathan Voxland March 14, 2016 at 4:27 PM
Since the original reporting, Liquibase preserves "bit" types in mysql
Spencer Williams September 22, 2014 at 6:42 PMEdited
Late comment, but a temporary workaround is to just manually specify columns as being either tinyint(1) or bit(1) (depending on your team's preference) ensures cross-OS consistency.
Given the same changelog with an example changeset:
<addColumn tableName="someTable"> <column name="isBooleanCreatedCorrectly" type="boolean" defaultValueBoolean="false" /> </addColumn>and MySQL 5.5 community edition servers in all cases,
On OSX and Ubuntu, this generates a tinyint(1) column.
On Windows, this generates a bit(1) column.
So then later if you need to update some fields using an "update" change, it fails on Windows and succeeds on OSX and Ubuntu:
<update tableName="someTable"> <column name="isBooleanCreatedCorrectly">1</column> </update>this also fails:
<update tableName="someTable"> <column name="isBooleanCreatedCorrectly" value="1"/> </update>The reason for this is that the generated SQL statement is:
UPDATE someTable SET isBooleanCreatedCorrectly='1';On OSX and Ubuntu, the string '1' is coerced to an integer 1, and is stored correctly.
On Windows, the string '1' is interpreted as a bitstring of more than 1 bit, and so it fails with "Data too long for column 'isBooleanCreatedCorrectly'"
Liquibase should generate the same field for the same database and version consistently across host operating systems.