I also posted this to stackoverflow since I wasn't sure which way of asking questions like this is preferred. http://stackoverflow.com/questions/17024482/h2-database-unsuccessful-schema-statement-when-add-index-and-foreign-key-constra
H2 does not appear to support the index/FK syntax generated by hibernate for the MySQL5 dialects when using ;MODE=MYSQL and hibernate with a dialect of org.hibernate.dialect.MySQL5Dialect. My goal here is to have one set of SQL scripts and use hibernate for the ORM parts. Everything works fine in MySQL 5.5 but when I try to use H2 for things like unit tests and starting up a demo version of my app I get hundreds of failures from hibernate generated alter table statements as shown below. Unfortunately, I have not been able to find a way to get hibernate to change the way the statements are generated but that might be an option as well. I tried using org.hibernate.dialect.H2Dialect but that produces more severe errors so I don't think that will work. alter table SAM_PUBLISHEDSECUREDIP_T add index FK1EDEA25B9482C945 (ASSESSMENTID), add constraint FK1EDEA25B9482C945 foreign key (ASSESSMENTID) references SAM_PUBLISHEDASSESSMENT_T (ID) Results in an error like this in H2: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "ALTER TABLE SAM_PUBLISHEDSECUREDIP_T ADD INDEX FK1EDEA25B9482C945 (ASSESSMENTID),[*] ADD CONSTRAINT FK1EDEA25B9482C945 FOREIGN KEY (ASSESSMENTID) REFERENCES SAM_PUBLISHEDASSESSMENT_T (ID) "; SQL statement: alter table SAM_PUBLISHEDSECUREDIP_T add index FK1EDEA25B9482C945 (ASSESSMENTID), add constraint FK1EDEA25B9482C945 foreign key (ASSESSMENTID) references SAM_PUBLISHEDASSESSMENT_T (ID) [42000-172] NOTE: I am open to writing and providing a patch for H2 but I could use some tips on where to look in that codebase. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database?hl=en-US. For more options, visit https://groups.google.com/groups/opt_out.
