Issue Details (XML | Word | Printable)

Key: NUCRDBMS-354
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Andy Jefferson
Reporter: Andy Jefferson
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
DataNucleus Store RDBMS

MySQL : cannot create index on LONG VARBINARY column if no key length specified

Created: 31/Mar/10 03:05 PM   Updated: 03/Apr/10 10:44 AM   Resolved: 31/Mar/10 03:11 PM
Component/s: Schema
Affects Version/s: 2.0.0.release, 2.0.1, 2.0.2
Fix Version/s: 2.0.3, 2.1.0.m1


 Description  « Hide
When running test.jdo.general "SerialisationTest" on MySQL there is a point where it tries to create an index on the serialised PC field in the following DDL

CREATE TABLE `SERIALISED_HOLDER`
(
    `SERIALISED_HOLDER_ID` BIGINT NOT NULL,
    `NAME` VARCHAR(255) BINARY NULL,
    `SERIALISED_PC` LONG VARBINARY NULL,
    PRIMARY KEY (`SERIALISED_HOLDER_ID`)
) ENGINE=INNODB

CREATE INDEX `SERIALISED_HOLDER_N49` ON `SERIALISED_HOLDER` (`SERIALISED_PC`)

This fails with
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: BLOB/TEXT column 'SERIALISED_PC' used in key specification without a key length
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2941)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3243)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3172)
        at com.mysql.jdbc.Statement.execute(Statement.java:706)
        at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
        at org.datanucleus.store.rdbms.table.AbstractTable.executeDdlStatement(AbstractTable.java:731)
        at org.datanucleus.store.rdbms.table.TableImpl.createIndices(TableImpl.java:652)
        at org.datanucleus.store.rdbms.table.TableImpl.createConstraints(TableImpl.java:426)

Sort Order: Ascending order - Click to sort in descending order
Andy Jefferson added a comment - 31/Mar/10 03:11 PM
SVN trunk now no longer adds indexes on serialised field columns. Can be improved in the future, but is a reasonable default

Andy Jefferson added a comment - 31/Mar/10 03:14 PM
SVN branches/2.0 also has this fix