Lately a customer I am helping to build a Grails application had an issue with creating tables in MySQL. They got the following error message when Hibernate tried to create the tables for their domain model:

Column length too big for column 'text' (max = 21845); use BLOB or TEXT instead

The reason was that the maxSize constraint of this property was set to 40000 and Hibernate tried to create a VARCHAR(40000) column in the MySQL table. Why did Hibernate want to do this? Another interesting thing was that Hibernate was able to create tables for the same model on other developer machines before.

I turned out that on of the developers upgraded the local MySQL installation from 5.1.x to 5.5.x and it was not possible to create any table on the new MySQL version. This was caused by the Hibernate dialect MySQLInnoDBDialect which was used. Switching to MySQL5InnoDBDialect worked partially but brought up the error mentioned above. So what was the difference?

Looking in the source of the different Hibernate dialect classes helped me understand the reasons. MySQLInnoDBDialect appends type=InnoDB to the create table string which was removed from the syntax in 5.5. It was deprecated in 5.1 already in favor of engine=InnoDB but it did still work. I was not aware that the team did still use the MySQLDialect classes and I advice to use the MySQL5Dialect for applications running on MySQL 5.x databases.

The difference between MySQLDialect and MySQL5Dialect that causes the column length can be found in the registerVarcharTypes method:

protected void registerVarcharTypes() {
  registerColumnType( Types.VARCHAR, "longtext" );
// registerColumnType( Types.VARCHAR, 16777215, "mediumtext" );
  registerColumnType( Types.VARCHAR, 65535, "varchar($l)" );
}

This methods defines that for String properties up to 65535 chars length a VARCHAR column should be used while in the MySQLDialect version it looks like:

protected void registerVarcharTypes() {
  registerColumnType( Types.VARCHAR, "longtext" );
// registerColumnType( Types.VARCHAR, 16777215, "mediumtext" );
// registerColumnType( Types.VARCHAR, 65535, "text" );
  registerColumnType( Types.VARCHAR, 255, "varchar($l)" );
}

In this versions Strings with a length greater than 255 chars will be stored in a LONGTEXT column which makes perfect sense. You could store one VARCHAR column with 20000 chars in a table row but if you try to add more you get the following error:

Error Code: 1118
Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

I think this a bug in the MySQL5Dialect and I filed one in the Hibernate JIRA. Meanwhile you can work around this problem by defining the column type explicitly for your properties.