MySQL Bugs: #100722: tinyInt1isBit Configuration Property Is No Longer Honored

Bug #100722	tinyInt1isBit Configuration Property Is No Longer Honored
Submitted:	2 Sep 2020 20:14	Modified:	9 Sep 2020 9:59
Reporter:	Chase Barrett	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.0.21	OS:	Any (Discovered on RHEL 8, but likely not OS related)
Assigned to:		CPU Architecture:	Any (Discovered on x86_64, but likely not CPU related)

Description:
We have a Java application using Hibernate/JPA and the Connector/J library to communicate with various versions of MySQL and MariaDB servers, according to customer preference. As late as version 8.0.17 of the Connector, the default value of the "tinyInt1isBit" flag (being 'true') has allowed us to model TINYINT(1) columns in our Java application like so:

    @Column(columnDefinition = "bit")
    private Boolean isApplication;

    @Column(columnDefinition = "bit")
    private Integer relationship;

However, after upgrading to version 8.0.21 of Connector/J, we are seeing the following errors at bootup when Hibernate does its schema validation:

    org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: wrong column type encountered in column [relationship] in table [iga_identity_resource]; found [tinyint (Types#TINYINT)], but expecting [bit (Types#INTEGER)]

It appears that Connector/J is no longer treating TINYINT(1) columns as BIT columns as described in the documentation. 

Even though 'true' is the default value, we added '&tinyInt1isBit=true' to the end of our JDBC URL, and as suspected, it had no effect.

The workaround for this is to disable hibernate DDL validation, which allows the application to boot. So far, it appears that runtime queries are not affected by this non-conversion, at least not for our limited use cases.

How to repeat:
Create a `test` database with a `my_table` table that has a `my_tinyint` column of type TINYINT(1).

Create a Hibernate 5.4/JPA 2.2 application with an entity that models the table and its column similar to the following:

  @Entity
  @Table(name = "my_table")
  public class MyTable {

    @Column(name = "my_tinyint", columnDefinition = "bit")
    private Boolean myTinyint;

    public MyTable() {}
  }

Ensure the application has set the "hbm2ddl.auto" flag to "validate" in its session factory definition.

Ensure the application has v8.0.21 of the Connector/J library on the classpath.

Configure the application's JDBC connection URL with something like this: 

  jdbc:mysql://localhost:3306/test?tinyInt1isBit=true

Boot the application, which should produce a SchemaManagementException as detailed in the description.

Hi Chase,

There was a significant change in MySQL 8.0.19 under WL#13127, "Deprecate integer display width and ZEROFILL option". We had to follow in c/J with Bug#97413.

The change is that starting from MySQL 8.0.19 only _signed_ TINYINT(1) can be treated as Boolean or Bit, other variants are not supplied with a display width thus always treated as TINYINT.

Though there is no UNSIGNED for TINYINT(1) in your report I don't see any other reason for such behaviour. Please check your DDL to confirm that.

We could add the server version check to c/J in order TINYINT(1) UNSIGNED could work with server versions prior to MySQL 8.0.19 but it would be better to follow the server changes and rework your DB structure to avoid possible problems with future migration.

Please share your thoughts.

Hello Alexander,

Thanks for the explanation. The columns I am working with are all:

  TINYINT(1) UNSIGNED NULL DEFAULT 0

While I don't have control over the schema, knowing the behavior of the latest Connector/J allows me to adjust our software to accommodate. For my purposes at least, I don't believe another change to C/J is necessary.

Thanks Chase, I close this bug as a duplicate of Bug#100309.