Saturday, June 1, 2013

BLOB/TEXT column 'description' used in key specification without a key length

This post is not about solving some problem. Sadly I don't know hot to solve following problem. There are just work arounds. I have following configuration:
MySQL: 5.5.27
hibernate dialect: org.hibernate.dialect.MySQLInnoDBDialect
hibernate: 3.6.9
In my model I have entity with following attribute:
@Entity
@Table(name = "site")
public class Site extends AbstractEntity {
    
    ...
     
    @Column(unique = true, nullable = true, length = 1024 * 100)
    private String description;
    
    ...
     
}
When I run program follow error appears in logs and table site is not created.
2013-06-01 12:15:24 [main] ERROR org.hibernate.tool.hbm2ddl.SchemaUpdate - 
Unsuccessful: create table site (id_site integer not null auto_increment, description longtext unique, name varchar(250) unique, url_pattern longtext not null unique, id_image integer, primary key (id_site)) ENGINE=InnoDB
2013-06-01 12:15:24 [main] ERROR org.hibernate.tool.hbm2ddl.SchemaUpdate - 
BLOB/TEXT column 'description' used in key specification without a key length
Problem is that column 'description' is marked as unique. In InnoDB maximum length of key could be just 767 characters for a single-byte charset like LATIN1 and only 255 characters for UTF8. Look at possible workarounds:
  1. avoid indexes, don't use unique = true
  2. make column length smaller (255) characters for UTF8
  3. compute hash from long column store in in another column and create index on hash column


If you'll have some other possible solution than please let me know.