Overcoming the MySQL BIT datatype problems with hibernate

I’m fond of optimization. When I code or design my database schema I try to avoid waisting CPU cycles or storage space (at least without a good reason). So when my domain class has the following field:

private boolean active; // determines whether this Person is active

I will let hibernate and the MySQL5InnoDBDialect choose what is most appropriate:

<property name="active" not-null="true" />

In that case it will generate a BIT:

...
active bit not null,
...

The problem

So far so good…
…until you read the blog post called “Why you should not use BIT columns in MySQL” by Xaprb.
Another serious deficiency is the fact that a database dump will not export bit data as “0″ or “1″. Depending on the tool used to dump and the MySQL server version you may find one of the following:

INSERT INTO `person` VALUES (1,"foo","\\0");
INSERT INTO `person` VALUES (2,"bar","");

or

INSERT INTO `person` VALUES (1,"foo"," ");
INSERT INTO `person` VALUES (2,"bar"," ");

The third field is of datatype BIT. Row number 1 is false and row number 2 is true. The problem with that is that some MySQL client tools cannot import such things. It gives you an ERROR 1064 (42000) at line 23: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ” at line 1

Solution #1

Hand edit the sql script and change all false bits to 0 and all true bits to 1, the script can be imported like a charm.

Solution #2

Extend MySQL5InnoDBDialect and make all BITs rendered as TinyInt(1). The code is very simple:

package com.foo.hibernate;

import java.sql.Types;
import org.hibernate.dialect.MySQL5InnoDBDialect;

public class MySQL5InnoDBDialectBitFixed extends MySQL5InnoDBDialect {

  public MySQL5InnoDBDialectBitFixed() {
    super();
    registerColumnType(Types.BIT, "tinyint(1)");
  }
  
}

Now when using the MySQL5InnoDBDialectBitFixed dialect, hbm2ddl will generate:

...
active tinyint(1) not null,
...

Until we get better 5.x MySQL versions, with better BIT support, this plan should do the job nicely.

Good luck

One Response to “Overcoming the MySQL BIT datatype problems with hibernate”

  1. Heartburn Home Remedy Says:

    I noticed that this is not the first time at all that you write about this topic. Why have you decided to touch it again?