MySQL 4.1: Specified key was too long; max key length is 102

[czernay]

There seems to be a problem with MySQL 4.1 and JBoss 4.01 (using mysql-connector-java-3.1.6-bin.jar) when creating a CMR-many-to-many-relationship table. I have two simple CMP-beans that I try to connect via a relation-table. The beans both have a simple String primary key that is set to be VARCHAR(250) BINARY for MySQL as JBoss's standard.

This configuration gives me a SQL statement like this:
CREATE TABLE Image2Author (author_id VARCHAR(250) BINARY NOT NULL, image_id VARCHAR(250) BINARY NOT NULL, CONSTRAINT pk_Image2Author PRIMARY KEY (author_id, image_id))

and an error like this:
Error while creating table Image2Author; - nested throwable: (java.sql.SQLException: Specified key was too long; max key length is 1024 bytes)

I reduced the standard String setup to a VARCHAR(190) BINARY and it also didn't work. However, a setup with a VARCHAR(100) BINARY worked, but that seems to be a rather nasty workaround.

I also played around with the MySQL setup to support UTF-8 or not, but that didn't make any difference.

Is there anything one could do to fix this? Any ideas?

Regards,
Alexander

[hlavacd]

Unless error message shows limit 1024, limit is 320 characters per key - so for each part af this key you can use only 160 charactes.

[czernay]

That was a typo, the error message shows limit of 1024.

The to be created table would have a primkey of two varchar-fields. A varchar-field is set to 250 in the JBoss default configuration, even if I assume two-byte characters, that would only sum up to 500 - not even close to 1024.

Nevertheless - I wonder what would be the right way to solve this issue. I don't think the right way would be to reduce the varchar standard to be 100 inside the JBoss standard-setup, as that would affect every field inside the whole application. And what would I have to do, if I had to create primkeys or indexes consisting of even more fields? That would make me end-up with even smaller varchar-fields.

[darranl]

What table type are you using with MySQL? I think it is MyISAM tables that impose this restriction - try setting the default table type to InnoDB tables which are required for transaction support anyway.

[czernay]

You're right, I use the JBoss standard-settings and those don't define any table-type, that gives MyISAM on my MySQL-server. I wonder what would be the best way to set this up for InnoDB?

[darranl]

Are you using a my.ini or my.cnf file for MySQL?

If you add the following line to the file in the [mysqld] section.

default-table-type=InnoDB

Any newly created tables should be InnoDB.

You may need to restart MySQL and if you are running it as a service you may need to reregister it to pick up the new settings.

[czernay]

Well, I know that would work, but I can't do it - it would affect all dbs created by MySQL. Isn't there a JBoss-way of doing it?