How to run the EJB3.0 tutrial example on MySQL?

[jbstonehenge]

Hi folks,

what do I have to do to run the author/article example on mySQL database?
I did copy the mysql-ds.xml into the deploy directory. Then I changed the persistence.xml to the new mySQL data source. After redeploy there are some errors probably due to creating the dabase tables in a hibernate dialect which mySQL cannot understand. The author/article throws an exception, too.
After creating the required tables in mySQL manually it works fine.
How can I get rid of the hibernate DDL commands rather using the equivalents of mySQL instead?

Here ist my JBoss console output just after deploying - the hibernated influences and errors are marked with bold text ...

01:26:37,169 INFO [JaccHelper] Initialising JACC Context for deployment: authors-beans.ejb3
01:26:39,013 INFO [Ejb3AnnotationHandler] found EJB3: ejbName=org.jboss.ejb3demo.AuthorsBean, class=org.jboss.ejb3demo.AuthorsBean, type=STATELESS
01:26:41,435 INFO [JaccHelper] org.jboss.ejb3demo.AuthorsBean has no @SecurityDomain - skipping JACC configuration
01:26:41,560 INFO [Ejb3Deployment] Found persistence.xml file in EJB3 jar
01:26:42,888 INFO [Environment] Hibernate 3.1
 01:26:42,966 INFO [Environment] hibernate.properties not found
01:26:42,966 INFO [Environment] using CGLIB reflection optimizer
01:26:42,982 INFO [Environment] using JDK 1.4 java.sql.Timestamp handling
01:26:43,904 INFO [Ejb3Configuration] found EJB3 Entity bean: org.jboss.ejb3demo.Article
01:26:43,935 INFO [Ejb3Configuration] found EJB3 Entity bean: org.jboss.ejb3demo.Author
01:26:47,232 INFO [Configuration] processing extends queue
01:26:47,232 INFO [Configuration] processing collection mappings
01:26:47,232 INFO [CollectionBinder] Mapping collection: org.jboss.ejb3demo.Author.articles -> Article
01:26:47,247 INFO [Configuration] processing association property references
01:26:47,247 INFO [Configuration] processing foreign key constraints
01:26:47,857 INFO [Configuration] processing extends queue
01:26:47,857 INFO [Configuration] processing collection mappings
01:26:47,857 INFO [Configuration] processing association property references
01:26:47,857 INFO [Configuration] processing foreign key constraints
01:26:47,904 INFO [ConnectionProviderFactory] Initializing connection provider: org.hibernate.ejb.InjectedDataSourceConnectionProvider
01:26:47,919 INFO [InjectedDataSourceConnectionProvider] Using provided datasource
01:26:50,122 INFO [SettingsFactory] RDBMS: MySQL, version: 5.0.18-nt
01:26:50,122 INFO [SettingsFactory] JDBC driver: MySQL-AB JDBC Driver, version:
 mysql-connector-java-3.1.12 ( $Date: 2005-11-17 15:53:48 +0100 (Thu, 17 Nov 2005) $, $Revision$ )
01:26:50,466 INFO [Dialect] Using dialect: org.hibernate.dialect.HSQLDialect
01:26:50,513 INFO [TransactionFactoryFactory] Using default transaction strategy (direct JDBC transactions)
01:26:50,576 INFO [TransactionManagerLookupFactory] instantiating TransactionManagerLookup: org.hibernate.transaction.JBossTransactionManagerLookup
01:26:50,591 INFO [TransactionManagerLookupFactory] instantiated TransactionManagerLookup
01:26:50,591 INFO [SettingsFactory] Automatic flush during beforeCompletion():disabled
01:26:50,591 INFO [SettingsFactory] Automatic session close at end of transaction: disabled
01:26:50,591 INFO [SettingsFactory] JDBC batch size: 15
01:26:50,591 INFO [SettingsFactory] JDBC batch updates for versioned data: disabled
01:26:50,591 INFO [SettingsFactory] Scrollable result sets: enabled
01:26:50,591 INFO [SettingsFactory] JDBC3 getGeneratedKeys(): enabled
01:26:50,591 INFO [SettingsFactory] Connection release mode: after_statement
01:26:50,591 INFO [SettingsFactory] Default batch fetch size: 1
01:26:50,607 INFO [SettingsFactory] Generate SQL with comments: disabled
01:26:50,607 INFO [SettingsFactory] Order SQL updates by primary key: disabled
01:26:50,607 INFO [SettingsFactory] Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
01:26:50,716 INFO [ASTQueryTranslatorFactory] Using ASTQueryTranslatorFactory
01:26:50,716 INFO [SettingsFactory] Query language substitutions: {}
01:26:50,716 INFO [SettingsFactory] Second-level cache: enabled
01:26:50,716 INFO [SettingsFactory] Query cache: disabled
01:26:50,716 INFO [SettingsFactory] Cache provider: org.hibernate.cache.HashtableCacheProvider
01:26:50,794 INFO [SettingsFactory] Optimize cache for minimal puts: disabled
01:26:50,794 INFO [SettingsFactory] Structured second-level cache entries: disabled
01:26:50,841 INFO [SettingsFactory] Statistics: disabled
01:26:50,841 INFO [SettingsFactory] Deleted entity synthetic identifier rollback: enabled
01:26:50,841 INFO [SettingsFactory] Default entity-mode: POJO
01:26:52,201 INFO [SessionFactoryImpl] building session factory
01:26:57,263 INFO [SessionFactoryObjectFactory] Not binding factory to JNDI, no JNDI name configured
01:26:57,294 INFO [Configuration] processing extends queue
01:26:57,294 INFO [Configuration] processing extends queue
01:26:57,294 INFO [Configuration] processing collection mappings
01:26:57,294 INFO [Configuration] processing association property references
01:26:57,294 INFO [Configuration] processing foreign key constraints
01:26:57,294 INFO [Configuration] processing extends queue
01:26:57,294 INFO [Configuration] processing collection mappings
01:26:57,294 INFO [Configuration] processing association property references
01:26:57,294 INFO [Configuration] processing foreign key constraints
01:26:57,294 INFO [SchemaExport] Running hbm2ddl schema export
01:26:57,372 INFO [SchemaExport] exporting generated schema to database
01:26:57,466 ERROR [SchemaExport] Unsuccessful: create table Article (articleIdinteger generated by default as identity (start with 1), title varchar(255), body varchar(255), authorId integer, primary key (articleId))
 01:26:57,466 ERROR [SchemaExport] 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 'generated by default as identity (start with 1), title varchar(255), body varcha' at line 1
 01:26:57,466 ERROR [SchemaExport] Unsuccessful: create table Author (authorId integer generated by default as identity (start with 1), name varchar(255), primary key (authorId))
 01:26:57,466 ERROR [SchemaExport] 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 'generated by default as identity (start with 1), name varchar(255), primary key ' at line 1
 01:26:57,560 ERROR [SchemaExport] Unsuccessful: alter table Article add constraint FK379164D6843A2A25 foreign key (authorId) references Author
 01:26:57,560 ERROR [SchemaExport] Table 'jboss.article' doesn't exist
01:26:57,560 INFO [SchemaExport] schema export complete
01:26:57,576 INFO [Configuration] processing extends queue
01:26:57,576 INFO [Configuration] processing collection mappings
01:26:57,576 INFO [Configuration] processing association property references
01:26:57,576 INFO [Configuration] processing foreign key constraints
01:26:57,576 INFO [Configuration] processing extends queue
01:26:57,576 INFO [Configuration] processing collection mappings
01:26:57,576 INFO [Configuration] processing association property references
01:26:57,576 INFO [Configuration] processing foreign key constraints
01:26:57,591 INFO [NamingHelper] JNDI InitialContext properties:{java.naming.factory.initial=org.jnp.interfaces.NamingContextFactory, java.naming.factory.url.pkgs=org.jboss.naming:org.jnp.interfaces}
01:26:57,591 INFO [SessionFactoryImpl] Checking 0 named queries
01:26:57,591 INFO [Ejb3Deployment] Create EntityManager with JNDI name: MyJBossDS
01:26:57,622 INFO [JaccHelper] JACC Policy Configuration for deployment has been put in service
01:26:57,622 INFO [Ejb3Deployment] EJB3 deployment time took: 20671
01:26:58,107 INFO [ProxyDeployer] no declared remote bindings for : org.jboss.ejb3demo.AuthorsBean
01:26:58,138 INFO [ProxyDeployer] there is remote interfaces for org.jboss.ejb3demo.AuthorsBean
01:26:58,138 INFO [ProxyDeployer] default remote binding has jndiName of org.jboss.ejb3demo.Authors
01:26:58,451 INFO [EJB3Deployer] Deployed: file:/D:/Web/JBoss/jboss-4.0.3/server/examples/deploy/authors-beans.ejb3
01:26:58,607 INFO [TomcatDeployer] deploy, ctxPath=/authors, warUrl=.../tmp/deploy/tmp27399authors-exp.war/

Thanks in advance,

Johannes

[chrismalan]

Hi Johannes,

This is a bit late, you may never see this or may have solved the problem.

The problem is: "Using dialect: org.hibernate.dialect.HSQLDialect"

Set up your persistence.xml to use org.hibernate.dialect.MySQLDialect

That should do it.

Chris

[jbstonehenge]

Hi Chris,

what do you mean by late? I do not expect replies within minutes or days. You were perfectly well on time! Thanks for your help.

I changes the dialect attribute in persistance.xml and all worked fine.

By the way... why are hibernate classes controlling how to acces a persistance database other than Hibernate and which dialect to choose. This is a bit confusing. Is Hibernate so elementary to JBoss or it it the first choice Open Source Database...?

Best regards,
Johannes

[chrismalan]

Hi Johannes,

I looked at your join date - my mistake.

Hibernate is a way to access a database, not a database itself. Before EJB3 there was EJB2.1. That was a notoriously complicated way of using a database. JBoss then decided to use Hibernate, which was much closer to the present (and future) ejb3 and much less complicated. As ejb3 is still not finalised, they continue to use Hibernate for JBoss' implementation of EJB3. Oracle uses Toplink in their application server for the same purposes.

BTW, Johannes, as you know, is Dutch, Afrikaans, maybe German and maybe found in other Nordic languages. But Stonehenge is as English as can come. Unusual combination. I've met a Digby de Villiers and other unusual combinations in my time.

Regards,

Chris

[jbstonehenge]

Hi Chris,

thanks for your explanation.

You're right, Johannes is German. The "Stonehenge" came to my mind remembering the unforgettable site in England and the resembling sound of my surname which is - translated into English - "Stonehouses".

Best regards,
Johannes