JBoss 4.01sp & MySql 4.1: JNDI ok, but cmp will not deploy d

[natgross]

[First off, I have googled, searched, researched, and feel foolish because it seems trivial, but....]
Hi;
JBoss 4.01 with MySql 4.1 on Fedora 3.
No problem setting up and running jdbc from a session ejb, but my first
crack at cmp has JBoss complain about the syntax as follows:

org.jboss.deployment.DeploymentException: Error while creating table
Product; - nested throwable: (java.sql.SQLException: 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 '))' at line 1)

I copied the mysql-jdbc2-service.xml to ../deploy/jms and removed the
hsql counterpart xml.


Please help.
Thanks;
-nat
ps. If removing hsql completely will help this situation, please advise how. Thanks again.
-----
Following are my META-INF dd's. (with some sections/beans snipped)

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE ejb-jar PUBLIC "-//Sun Microsystems, Inc.//DTD Enterprise JavaBeans 2.0//EN" "http://java.sun.com/dtd/ejb-jar_2_0.dtd">

<ejb-jar >

<description>[CDATA[No Description.]]</description>
<display-name>Generated by XDoclet</display-name>

<enterprise-beans>

<!-- Session Beans -->
<session >
<session >
<description>[CDATA[Try some JDBC inside ejb]]</description>
<display-name>JdbcTest</display-name>

<ejb-name>JdbcTest</ejb-name>

<home>cproj14.interfaces.JdbcTestHome</home>
<remote>cproj14.interfaces.JdbcTest</remote>
<ejb-class>cproj14.ejb.JdbcTestBean</ejb-class>
<session-type>Stateful</session-type>
<transaction-type>Container</transaction-type>

</session>


<!--
To add session beans that you have deployment descriptor info for, add
a file to your XDoclet merge directory called session-beans.xml that contains
the <session></session> markup for those beans.
-->

<!-- Entity Beans -->
<entity >
<description>[CDATA[Description for Product]]</description>
<display-name>Name for Product</display-name>

<ejb-name>Product</ejb-name>

<home>cproj14.interfaces.ProductHome</home>
<remote>cproj14.interfaces.Product</remote>
<ejb-class>cproj14.ejb.ProductBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>cproj14.ejb.pk.ProductPK</prim-key-class>
<reentrant>False</reentrant>
<cmp-version>2.x</cmp-version>
<abstract-schema-name>Product</abstract-schema-name>
<cmp-field >
<description>[CDATA[Getter for CMP Field Description]]</description>
<field-name>description</field-name>
</cmp-field>
<cmp-field >
<description>[CDATA[Getter for CMP Field Name]]</description>
<field-name>name</field-name>
</cmp-field>
<cmp-field >
<description>[CDATA[Getter for CMP Field BasePrice]]</description>
<field-name>basePrice</field-name>
</cmp-field>
<cmp-field >
<description>[CDATA[Getter for CMP Field Id]]</description>
<field-name>id</field-name>
</cmp-field>

<!-- Write a file named ejb-finders-ProductBean.xml if you want to define extra finders. -->
</entity>

<!--
To add entity beans that you have deployment descriptor info for, add
a file to your XDoclet merge directory called entity-beans.xml that contains
the <entity></entity> markup for those beans.
-->

<!-- Message Driven Beans -->
<!--
To add message driven beans that you have deployment descriptor info for, add
a file to your XDoclet merge directory called message-driven-beans.xml that contains
the <message-driven></message-driven> markup for those beans.
-->

</enterprise-beans>

<!-- Relationships -->

<!-- Assembly Descriptor -->
<assembly-descriptor >
<!--
To add additional assembly descriptor info here, add a file to your
XDoclet merge directory called assembly-descriptor.xml that contains
the <assembly-descriptor></assembly-descriptor> markup.
-->

<!-- finder permissions -->

<!-- transactions -->

<!-- finder transactions -->
</assembly-descriptor>

</ejb-jar>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE jbosscmp-jdbc PUBLIC "-//JBoss//DTD JBOSSCMP-JDBC 4.0//EN" "http://www.jboss.org/j2ee/dtd/jbosscmp-jdbc_4_0.dtd">

<jbosscmp-jdbc>
<defaults>
</defaults>

<enterprise-beans>

<!--
To add beans that you have deployment descriptor info for, add
a file to your XDoclet merge directory called jbosscmp-jdbc-beans.xml
that contains the <entity></entity> markup for those beans.
-->

<entity>
<ejb-name>Product</ejb-name>

<cmp-field>
<field-name>description</field-name>

</cmp-field>
<cmp-field>
<field-name>name</field-name>

</cmp-field>
<cmp-field>
<field-name>basePrice</field-name>

</cmp-field>
<cmp-field>
<field-name>id</field-name>

</cmp-field>

<!-- jboss 3.2 features -->
<!-- optimistic locking does not express the exclusions needed -->
</entity>

</enterprise-beans>

</jbosscmp-jdbc>

[czernay]

I had a similar problem in another context. There where two major changes from MySQL 4.0 to 4.1:

1. Authentication to the server has changed completly, so be sure to recompile all MySQL libs with support for 4.1.

2. Some SQL syntax has changed. Reconfigure JBoss to output all SQL statements it send to MySQL. Have a look at the CREATE TABLE statements and try them "by hand" directly inside MySQL. Fix those and then adjust JBoss db-scheme.

Good luck!

Alexander

[natgross]

1. My Linux install did NOT include mysql. I dl'd a fresh mysql with all new libs.
2. In mysql I have a 'jboss' schema, with full rights to a user 'jboss', which is what the jndi datasource uses. Now, jboss is correctly creating the jboss default tables (jms_messages, jms_transactions, etc) in this schema upon startup. fwiw.
Are you saying that I need to teach jboss a newer 'create table' syntax? Where do I do that?
Thank you.
-nat

[czernay]

Have a look at /%CONFIG_NAME%/conf/standardjbosscmp-jdbc.xml (%CONFIG_NAME% is your JBoss configuration name, usualy 'default'). There you can tweak all the database settings.