[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - SQL Syntax Error

[costas723]

I have a problem to create a table via CMP. Everything was OK using the Hypersonic DB. I switched to MySQL and encounter the following problem.

Running on JBoss 3.2.1, MySQL 5.

I imported the mysl-connector-java-3.1.14.bin.jar into server\default\lib and mysql-ds.xml into the server\default\deploy.

    <jndi-name>MySqlDS</jndi-name>
  |     <connection-url>jdbc:mysql://localhost:3306/auditdb</connection-url>
  |     <driver-class>com.mysql.jdbc.Driver</driver-class>
  |     <user-name>root</user-name>
(correct password used)

and changed standardjaws.xml

   <datasource>java:/MySqlDS</datasource>
  |    <type-mapping>mySQL</type-mapping>
  |    <debug>false</debug>

and standardjbosscmp-jdbc.xml:

    <defaults>
  |       <datasource>java:/MySqlDS</datasource>
  |       <datasource-mapping>mySQL</datasource-mapping>
  |      
  |       <create-table>true</create-table>
  |       <remove-table>false</remove-table>
  |       <read-only>false</read-only>
  |       <time-out>300</time-out>
  |       <pk-constraint>true</pk-constraint>
  |       <fk-constraint>false</fk-constraint>
  |       <row-locking>false</row-locking>
  |       <preferred-relation-mapping>foreign-key</preferred-relation-mapping>
  |       <read-ahead>
  |       <strategy>on-load</strategy>
  |       <page-size>1000</page-size>
  |       <eager-load-group>*</eager-load-group>
  |       </read-ahead>
  |       <list-cache-max>1000</list-cache-max>
  | 
  |       <unknown-pk>
  |          <key-generator-factory>UUIDKeyGeneratorFactory</key-generator-factory>
  |          <unknown-pk-class>java.lang.String</unknown-pk-class>
  |          <jdbc-type>VARCHAR</jdbc-type>
  |          <sql-type>VARCHAR(32)</sql-type>
  |       </unknown-pk>
  | 
  |       <entity-command name="default"/>
  | 
  |    </defaults>
  | 
xdoclet.xml:

datasource="java:/MySqlDS"
  | datasourcemapping="mySQL"
  | preferredrelationmapping="foreign-key"

jbosscmp-jdbc.xml

<jbosscmp-jdbc>
  |    <defaults>
  |      <datasource>java:/MySqlDS</datasource>
  |      <datasource-mapping>mySQL</datasource-mapping>
  |      <preferred-relation-mapping>foreign-key</preferred-relation-mapping>
  |    </defaults>
  | 
  |    <enterprise-beans>
  | 
  |        <entity>
  |          <ejb-name>Baustein</ejb-name>
  | 
  |          <table-name>baustein</table-name>
  | 
  |          <cmp-field>
  |             <field-name>bausteinid</field-name>
  |             <column-name>bausteinid</column-name>
  | 
  |             <jdbc-type>VARCHAR</jdbc-type>
  |             <sql-type>INTEGER</sql-type>
  | 
  |         </cmp-field>
  |          <cmp-field>
  |             <field-name>kuerzel</field-name>
  |             <column-name>kuerzel</column-name>
  | 
  |             <jdbc-type>VARCHAR</jdbc-type>
  |             <sql-type>VARCHAR</sql-type>

When I try to deploy my project, I receive an error:

16:33:08,000 ERROR [EntityContainer] Starting failed
org.jboss.deployment.DeploymentException: Error while creating table; - 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 ' CONSTRAINT pk_baustein PRIMARY KEY (bausteinid))' at line 1)

...and I have no more ideas, how I can fix it. Any hints?

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4006410#4006410

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4006410