Issues with ddl export and mysql

[meetoblivion]

on my local box i'm trying to get my application up and running again.

i have a mysql instance running here too.

I get the following error when trying to setup my repo

[#|2010-04-13T14:35:55.647-0400|INFO|glassfishv3.0|org.hibernate.tool.hbm2ddl.SchemaExport|_ThreadID=29;_ThreadName=Thread-1;|Running hbm2ddl schema export|#]

[#|2010-04-13T14:35:55.655-0400|INFO|glassfishv3.0|org.hibernate.tool.hbm2ddl.SchemaExport|_ThreadID=29;_ThreadName=Thread-1;|exporting generated schema to database|#]

[#|2010-04-13T14:35:55.671-0400|SEVERE|glassfishv3.0|org.hibernate.tool.hbm2ddl.SchemaExport|_ThreadID=29;_ThreadName=Thread-1;|Unsuccessful: create table MODE_OPTIONS (NAME longtext not null, VALUE longtext not null, primary key (NAME))|#]

[#|2010-04-13T14:35:55.673-0400|SEVERE|glassfishv3.0|org.hibernate.tool.hbm2ddl.SchemaExport|_ThreadID=29;_ThreadName=Thread-1;|BLOB/TEXT column 'NAME' used in key specification without a key length|#]

seems odd that name is a PK, considering that it's a blob.

[bcarothers]

What Hibernate dialect are you using?

[meetoblivion]

tried it both with none specified (let hibernate choose one) and then explicitly org.hibernate.dialect.MySQL5Dialect

[bcarothers]

I think something else is afoot.  Could you please jump on IRC and pastebin your config?  I can take a look at it tonight.  Thanks!

[meetoblivion]

here's the pastebin link

http://pastebin.com/bCK1CVhZ

and for those who don't want to travel

1. Here's one w/ nothing specified
3.         <mode:source jcr:name="LiveSource"
4.            mode:classname="org.modeshape.connector.store.jpa.JpaSource"
5.            mode:rootNodeUuid="abcdefghjiukr"
6.            mode:autoGenerateSchema="update"
7.            mode:dataSourceJndiName="jdbc/JCRADMIN"
8.            mode:nameOfDefaultWorkspace="liveWorkspace"/>
10. Here's an InnoDB example
12.         <mode:source jcr:name="LiveSource"
13.            mode:classname="org.modeshape.connector.store.jpa.JpaSource"
14.            mode:rootNodeUuid="abcdefghjiukr"
15.            mode:autoGenerateSchema="update"
16.            mode:dialect="org.hibernate.dialect.MySQL5InnoDBDialect"
17.            mode:dataSourceJndiName="jdbc/JCRADMIN"
18.            mode:nameOfDefaultWorkspace="liveWorkspace"/>
20. and normal
22.         <mode:source jcr:name="LiveSource"
23.            mode:classname="org.modeshape.connector.store.jpa.JpaSource"
24.            mode:rootNodeUuid="abcdefghjiukr"
25.            mode:autoGenerateSchema="update"
26.            mode:dialect="org.hibernate.dialect.MySQL5Dialect"
27.            mode:dataSourceJndiName="jdbc/JCRADMIN"
28.            mode:nameOfDefaultWorkspace="liveWorkspace"/>

I'm in IRC now.  also note, these don't all live in the file at once, but are examples...

[bcarothers]

If you could, please update from trunk and then run the DDL generator with -model Simple and -dialect MySQL5InnoDB.  This will (theoretically) generate the same DDL that Hibernate would generate when you set autogenerateSchema = create.  I'd like to hear if you still get the same LOB malfeasance when the DDL is created that way.

Thanks for working through this with me!

[meetoblivion]

yeah - it still doesn't work for create/update.  It's a bit concerning since I'm the one who sent you guys the code for enabling configurable hibernate options in this case.

[meetoblivion]

Brian, issue ended up being a bit more complicated than thought.

I opened https://jira.jboss.org/jira/browse/MODE-723 as a result.  Even though we got around the BLOB type issue, MySQL doesn't like having a name w/ 512 length PK.  Max is 500 in MyISAM and ~330 something in InnoDB

[bcarothers]

Well, our longest value in that column currently is 32 characters long, so I think we can live with cutting that column down a bit.  I'll have a patch submitted as soon as the regression test passes.

Out of curiosity, what's the 2-byte charset that you're using?  Also, are you still seeing the wonky DDL when autogenerateSchema is create or update?

Thanks for your patience as we work through this.