MySQL BIT / TINYINT Conversion on existing DB

General Discussion
1

Hello all,

Not sure if this is better suited asked on Hibernate or here, so let me know if this doesn’t sound like the right place for this.

We have several Spring Boot apps that run liquibase on MySQL, and are recently in the process of trying to upgrade our liquibase version from 4.25.0 to latest. However, on Liquibase 4.25.1 or newer, we get the following error:

Schema-validation: wrong column type encountered in column [COLUMN] in table [TABLE]; found [tinyint (Types#TINYINT)], but expecting [bit (Types#BOOLEAN)]

I understand that liquibase now correctly maps booleans in MySQL to TINYINT instead of BIT, but are there steps to help existing databases make this transition more easily? We can map booleans to TINYINT properly via hibernate.type.preferred_boolean_jdbc_type=TINYINT, however, we already have boolean columns mapped to BIT in the databases of our environments, and using this property throws the opposite error (expecting bit found tinyint).

The possible solutions we have thought of are:

  1. If liquibase had an option to choose what it should map booleans to (between BIT or TINYINT).
  2. Run changesets to modify columns from java.sql.boolean to tinyint.
  3. Rerun changelogs on existing databases to instantiate the columns as TINYINT instead of BIT (this is a last resort and we really don’t want to do this.)

I have found little regarding this error, but I feel like this should be more common or we are doing something completely wrong. Any thoughts are appreciated, thanks!

Versions used:
Liquibase 4.29.2
Hibernate 6.6.1
MySQL J Connector 8.3.0
MySQL 8.0.37

2

The same thing happened to us; I don’t think we are doing something wrong since this is a breaking change, even if it is a fix.

About your post, I would love to see a configuration like option one since liquibase changes the boolean type so frequently, first on 4.24.0 and then on 4.25.1, that would help us keep the same behavior we were expecting at some point.

As a result of these updates, we had to forbid the use of liquibase boolean type altogether and directly use the specific Mysql types we want on our migrations since we don’t know if boolean will change to another type in the future.