0 Replies Latest reply on Feb 16, 2007 9:56 PM by Bas Ven

    Mysql 5.1, unique index and (tiny)blob

    Bas Ven Newbie

      We created our own hibernate type which is defined as a Type.Varbinary.
      In the pojo we define the column lenght (size = 16 ).

      In that same pojo the attribute is also used to create a unique index.

      Works fine with sqlserver, hsql and oracle 10G. In mysql 5.1 however, I get a nasty error

       Reason: java.lang.RuntimeException: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: BLOB/TEXT column 'PREFIX' used in key specification without a key length
      


      The create statement:

      create table MY_TEST_TABLE (id bigint not null, attr-3 tinyblob not null, attr-2 integer not null, attr-1 bigint not null, primary key (id), unique (attr-1, attr-2, attr-3(16)))
      


      It seems like mysql wants you to set the size of the index fields for tiny (and probably any) blob fields, so it would look something like

      create table MY_TEST_TABLE (id bigint not null, attr-3 tinyblob not null, attr-2 integer not null, attr-1 bigint not null, primary key (id), unique (attr-1, attr-2, attr-3))


      We are using annotations, is there a way in hibernate to specify this? How have others handled this problem.

      TIA.