[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3243) VARBINARY primary key with mysql doesn't function

[Marc Ewert (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3243?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_30212 ] 

Marc Ewert commented on HHH-3243:
---------------------------------

we fixed it for us with aspect4j. The implementation of PrimaryKey was replaced with the following method:

public String sqlConstraintString(PrimaryKey key, Dialect dialect) {
        StringBuilder buf = new StringBuilder("primary key (");
        Iterator iter = key.getColumnIterator();        
        while (iter.hasNext()) {
            Column column = (Column)iter.next();
            Type type = column.getValue().getType();
            int sqlType = type.sqlTypes(null)[0];
            buf.append(column.getQuotedName(dialect));
            if (sqlType == Types.VARBINARY) {
                buf.append("(").append(column.getLength()).append(")");
            }
            if (iter.hasNext()) {
                buf.append(", ");
            }
        }
        return buf.append(')').toString();
    }

This method will be used instead of the original one, if the dialect used is MySQLDialect.

A better solution would be to add an appropriate method to Dialect and override it in MySQLDialect.

Is there any chance to get this in the official release of hibernate? With aop we have to add corresponding runtime options to the JVM, or we have to supply a patched hibernate jar to our users. Both are dirty workarounds.

And it`s not an unlikely feature. We need it to persist UUIDs (with mysql) in an efficient manner. Otherwise we have to convert them to strings, which costs more cpu and storage in the database.

> VARBINARY primary key with mysql doesn't function
> -------------------------------------------------
>
>                 Key: HHH-3243
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3243
>             Project: Hibernate3
>          Issue Type: Bug
>    Affects Versions: 3.2.1
>         Environment: MySQL-5.0.51a, hibernate-3.2.1.ga
>            Reporter: Marc Ewert
>
> We have a 16-byte UUID type, which should be persisted in a VARBINARY (tinyblob) in a mysql database. But the primary key statement is invalid for mysql:
> ERROR 11:34:13 [main] (SchemaExport.java:create:274) -
> Unsuccessful:
> create table T_GRI_USER (C_ID tinyblob not null, C_ATTRIBUTES
> text, C_VERSION integer, C_PASSWORD varchar(255), C_VISIBILITIES 
> blob, C_STATE integer not null, C_LOGIN varchar(255) not null 
> unique, C_EMAIL varchar(255), primary key (C_ID)) 
> ERROR 11:34:13 [main] (SchemaExport.java:create:275) - BLOB/TEXT
> column 'C_ID' used in key specification without a key length
> The statement should end with ... primary key (C_ID({length})). This has to be done in the class PrimaryKey, which forms this part of the statement without involving the Dialect.
> The MySQLDialect has also a little bug, it doesn't write the length while defining the tinyblob column. But unfortunately this doesn't solve the SQL-Error. Unfortunately because the dialect is much better patchable for us as the PrimaryKey class deep in hibernate is...

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira