While experimenting with Hibernate Annotations (3.2.0CR1) and having it auto-create (hbm2ddl="update") tables, the class org.hibernate.test.annotations.collectionofelement.Boy will fail to automaticallly create (or update) 2 tables when using MySQL (5.0.21) and the dialect MySQLInnoDBDialect. Should I put this issue with my fix into JIRA? I did a quick search of JIRA (not sure how to use it) and didn't find any noted problems with this example class.

Here are the details....... (1. Cause / 2. Errors / 3. Solution )

=-=-=-=-=-=-=-=-=-=-

1. The Cause:

As you know, using the hibernate setting hbm2ddl set to "update" makes hibernate create the table it does not exist when hibernate builds the sessionFactory. Using one of the standard hibernate annotations (3.2.0.CR1) test classes, I noted this failure error on two "@collectionOfElements" annotations as it tried to create two subordinate tables based on that class.

2. The errors:

=-=-=-=-=-=-=-=-=-=-
May 22, 2006 6:52:51 PM org.hibernate.tool.hbm2ddl.DatabaseMetadata getTableMetadata
INFO: table not found: ScorePerNickName
May 22, 2006 6:52:51 PM org.hibernate.tool.hbm2ddl.SchemaUpdate execute
SEVERE: Unsuccessful: create table Boy_characters (Boy_id integer not null, character varchar(255)) type=InnoDB
May 22, 2006 6:52:51 PM org.hibernate.tool.hbm2ddl.SchemaUpdate execute
SEVERE: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'character varchar(255)) type=InnoDB' at line 1
May 22, 2006 6:52:51 PM org.hibernate.tool.hbm2ddl.SchemaUpdate execute
SEVERE: Unsuccessful: create table ScorePerNickName (BoyId integer not null, fld_score integer, key varchar(255), primary key (BoyId, key)) type=InnoDB
May 22, 2006 6:52:51 PM org.hibernate.tool.hbm2ddl.SchemaUpdate execute
SEVERE: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar(255), primary key (BoyId, key)) type=InnoDB' at line 1
May 22, 2006 6:52:51 PM org.hibernate.tool.hbm2ddl.SchemaUpdate execute
SEVERE: Unsuccessful: alter table Boy_characters add index FKD40EF6DDC735435A (Boy_id), add constraint FKD40EF6DDC735435A foreign key (Boy_id) references Boy (id)
May 22, 2006 6:52:51 PM org.hibernate.tool.hbm2ddl.SchemaUpdate execute
SEVERE: Table 'test.boy_characters' doesn't exist
May 22, 2006 6:52:51 PM org.hibernate.tool.hbm2ddl.SchemaUpdate execute
SEVERE: Unsuccessful: alter table ScorePerNickName add index FK7AC02F91F3EFAD3 (BoyId), add constraint FK7AC02F91F3EFAD3 foreign key (BoyId) references Boy (id)
May 22, 2006 6:52:51 PM org.hibernate.tool.hbm2ddl.SchemaUpdate execute
SEVERE: Table 'test.scorepernickname' doesn't exist
May 22, 2006 6:52:51 PM org.hibernate.tool.hbm2ddl.SchemaUpdate execute
INFO: schema update complete

=-=-=-=-=-=-=-=-=-=-

3. The solution is to avoid using table field names that MySQL (specifically for this configuration the MySQLInnoDBDialect setup) does not like: "character" and "key". I corrected this using annotations at the @Entity level (top of the class). The "character" @AttributeOverride can be removed completely (so it defaults to the field name "element") OR you can change the field name to something unobtrusive such as "characterType":

@AttributeOverride( name="characters.element", column = @Column(name="characterType") ),

The other field can be corrected to use some name other than "key". I used the name "fld_key" since the table uses the "fld_score" to hold the "score" data/value:

@AttributeOverride( name="scorePerNickName.key", column = @Column(name="fld_key") ),

Please let me know if posting this problem here is enough or if I should make a JIRA entry to get this corrected before the next release so people learning, like myself, won't run into this problem.

Regards,
David