Hi,

I'm trying this mapping in MySQL which gives an error message when I run SchemaExportTask:

<property
name="url"
type="string"
update="true"
insert="true"
access="property"
column="URL"
length="1024"
not-null="true"
unique="true"
/>

[schemaexport] (hbm2ddl.SchemaExport 154 ) Unsuccessful:
create table LINK_STATS (
ID INTEGER NOT NULL AUTO_INCREMENT,
URL TEXT not null unique,
NB_VISITS INTEGER not null,
primary key (ID))
[schemaexport] (hbm2ddl.SchemaExport 155 ) General error, message from server: "BLOB column 'URL' used in key specification without a key length"


When I set the length of the column to 255, I don't get an error (since MySQL uses the varchar type for this).

Also, when I allow nulls and duplicate values, it works too, but then I don't have an index on the column.

I tried @hibernate.column to add an index, but have the same problem here.

According to http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html, I need to indicate an index prefix by using something like

CREATE TABLE test (url BLOB, INDEX(url(10)));


Is there any way I can indicate this in my Hibernate mappings (preferably XDoclet)?

Thanks,

Pieter Coucke

Hibernate 2.1.4
Mysql 4.0.18-nt

This is VERY nonstandard SQL. It is not supported, but you should easily be able to add a handwritten CREATE INDEX statement to your DDL.

Pieter--


How did you resolve this? I ran into the same problem--I want unique strings longer than 255 in MySQL. I'd like to generate:

name VARCHAR(512) BINARY not null unique, INDEX(name(255))

(By the way, MySQL automatically converts VARCHARs > 255 to TEXT, which is up to 64K.)

I really don't want to hand-edit the generated SQL. I wonder if I could do something like this in my mapping file:

sql-type="VARCHAR(512) BINARY not null unique, INDEX(name(255))"

Of course, this is overriding what I set for unique= and not-null=, which I don't like.

I'll sure be glad when MySQL 5 gets here, since it supports much longer VARCHARs (that is, without converting to TEXT and requiring the prefix length to index).

I'm having the same problem when I use a text property inside my natural-id hibernate element. Since I generate and update my schema using ant I can't add the handwritten index modifier every time.

As a workaround/hack, I've added the following ant task:

<replaceregexp
match="..."
replace="...">
<fileset dir="${...}">
<include name="create-db.sql" />
</fileset>
</replaceregexp>

Of course replace the ...'s with your own variables and the create-db.sql with the ddl file that schemaExport generates.