2 Replies Latest reply on Feb 14, 2005 4:17 PM by Michael Bauer

    Error deploying simple CMP with auto create table for MySQL

    Michael Bauer Newbie

      I am trying to get auto table creation to work with JBoss/MySQL and XDoclet, but I am running into some problems. Every time I try to deploy the EJB JAR file, I get the following (full logs below):

      ERROR [org.jboss.ejb.EntityContainer] Starting failed
      org.jboss.deployment.DeploymentException: Error while creating table TestCMP; - nested throwable: (java.sql.SQLException: Syntax error or access violation, message from server: "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 have tried EVERYTHING I can think of:
      Specifying the class as the PK in the header, the getter, both, nieither

      Specifying the type as VCHAR or CLOB

      Turnning on Query logging in MySQL


      Everything I have tried thus far has not helped. When I turned on query logging in MySQL, I expected that I would see some sort of CREATE TABLE request, but I didn't see anything. At first I thought I didn't turn the logging on right, but then I saw stuff from phpMyAdmin logging, so iit appears that whatever is "unhappy" with the SQL is throwing the exception way before it sends to MySQL (probably the JDBC code).

      I am using:
      JBoss 3.2.3
      JDK 1.4.2_07 (linux, kernel 2.6.10-r3 hardened patches)
      XDoclet 1.2.1
      Ant 1.6.2
      MySQL 4.0.22-r2
      MySQL Connector/J 3.0.11

      Below you will find all pertinent files
      build.xml
      <project name="Tax Liens" default="package">
       <target name="init">
       <property name="basedir" value="./" />
      
       <loadproperties srcFile="custom.properties"/>
      
       <property name="src.dir" value="${basedir}/src" />
       <property name="bin.dir" value="${basedir}/bin" />
       <property name="lib.dir" value="${basedir}/lib" />
       <property name="dist.dir" value="${basedir}/dist" />
       <property name="meta.dir" value="${basedir}/META-INF" />
      
       <property name="jarfile.name" value="TaxLiens.jar" />
      
       <tstamp>
       <format property="TODAY" pattern="d-MM-yy"/>
       </tstamp>
      
       <path id="jar.files">
       <fileset dir="${lib.dir}">
       <include name="**/*.jar"/>
       <include name="**/*.zip"/>
       </fileset>
       <fileset dir="${xdoclet.lib.dir}">
       <include name="**/*.jar"/>
       <include name="**/*.zip"/>
       </fileset>
       <fileset dir="${j2ee.lib.dir}">
       <include name="**/*.jar"/>
       <include name="**/*.zip"/>
       </fileset>
       </path>
       <pathconvert pathsep=":" property="jar.file.prop" refid="jar.files"/>
       <echo>Path: ${jar.file.prop}</echo>
       </target>
      
       <target name="clean">
       <delete failonerror="false">
       <fileset dir="${bin.dir}" >
       <include name="**"/>
       </fileset>
       <fileset dir="${dist.dir}" >
       <include name="**"/>
       </fileset>
       <fileset dir="${meta.dir}" >
       <include name="**"/>
       </fileset>
       </delete>
       </target>
      
       <target name="prepare" depends="init,clean">
       <mkdir dir="${bin.dir}"/>
       <mkdir dir="${dist.dir}"/>
       <mkdir dir="${meta.dir}"/>
       </target>
      
       <target name="xdoclet" depends="prepare">
       <taskdef
       name="ejbdoclet"
       classname="xdoclet.modules.ejb.EjbDocletTask"
       classpathref="jar.files"
       />
      
       <ejbdoclet
       destdir="${src.dir}"
       excludedtags="@version,@author"
       addedtags="@xdoclet-generated at ${TODAY}"
       ejbspec="2.0">
       <fileset dir="${src.dir}">
       <include name="**/*Bean.java"/>
       </fileset>
      
       <dataobject/>
       <packageSubstitution packages="persistence" substituteWith="interfaces"/>
       <remoteinterface pattern="{0}Remote"/>
       <localinterface pattern="{0}"/>
       <homeinterface />
       <localhomeinterface/>
       <entitypk/>
       <entitycmp/>
       <deploymentdescriptor destdir="${meta.dir}"/>
       <jboss version="3.2"
       preferredRelationMapping="relation-table"
       datasource="java:/LocalDS"
       datasourcemapping="mySQL"
       destdir="${meta.dir}"
       />
       </ejbdoclet>
       </target>
      
       <target name="compile" depends="xdoclet">
       <javac
       classpathref="jar.files"
       debug="true"
       srcdir="${src.dir}"
       destdir="${bin.dir}" />
       </target>
      
       <target name="package" depends="compile">
       <jar destfile="${dist.dir}/${jarfile.name}">
       <fileset dir="${bin.dir}">
       <include name="**"/>
       </fileset>
       <metainf dir="${meta.dir}">
       <include name="**"/>
       </metainf>
       </jar>
       </target>
      </project>


      TestCMP.java
      /*
       * Created on Feb 11, 2005
       *
       * TODO To change the template for this generated file go to
       * Window - Preferences - Java - Code Style - Code Templates
       */
      package com.codechimp.taxliens;
      
      import java.rmi.RemoteException;
      
      import javax.ejb.CreateException;
      import javax.ejb.EJBException;
      import javax.ejb.EntityBean;
      import javax.ejb.EntityContext;
      import javax.ejb.RemoveException;
      
      /**
       * @author mbauer
       *
       * @ejb.bean
       * name="TestCMP"
       * description="A test CMP Bean"
       * type="CMP"
       * cmp-version="2.x"
       * view-type="both"
       * jndi-name="TestCMPBean"
       * local-jndi-name="LocalTestCMPBean"
       *
       * @ejb.pk
       * class="java.lang.String"
       * generate="false"
       *
       * @jboss.persistance
       * datasource="java:/LocalDS"
       * alter-table="true"
       * create-table="true"
       * remove-table="true"
       * table-name="test_cmp"
       */
      public abstract class TestCMPBean implements EntityBean {
       private EntityContext _ctx;
      
       /* (non-Javadoc)
       * @see javax.ejb.EntityBean#setEntityContext(javax.ejb.EntityContext)
       */
       public void setEntityContext(EntityContext arg0) throws EJBException, RemoteException {
       _ctx=arg0;
       }
      
       /* (non-Javadoc)
       * @see javax.ejb.EntityBean#unsetEntityContext()
       */
       public void unsetEntityContext() throws EJBException, RemoteException {
       _ctx=null;
       }
      
       /* (non-Javadoc)
       * @see javax.ejb.EntityBean#ejbRemove()
       */
       public void ejbRemove() throws RemoveException, EJBException, RemoteException {}
      
       /* (non-Javadoc)
       * @see javax.ejb.EntityBean#ejbActivate()
       */
       public void ejbActivate() throws EJBException, RemoteException {}
      
       /* (non-Javadoc)
       * @see javax.ejb.EntityBean#ejbPassivate()
       */
       public void ejbPassivate() throws EJBException, RemoteException {}
      
       /* (non-Javadoc)
       * @see javax.ejb.EntityBean#ejbLoad()
       */
       public void ejbLoad() throws EJBException, RemoteException {}
      
       /* (non-Javadoc)
       * @see javax.ejb.EntityBean#ejbStore()
       */
       public void ejbStore() throws EJBException, RemoteException {}
      
       /**
       *
       * @param name
       * @return
       *
       * @ejb.create-method
       */
       public String ejbCreate(String name) throws CreateException {
       setName(name);
      
       return name;
       }
      
       public void ejbPostCreate(String name) throws CreateException {
       setName(name);
       }
      
       /**
       *
       * @return
       *
       * @ejb.pk-field
       * @ejb.persistence
       *
       * @jboss.column-name name="test_name"
       * @jboss.jdbc-type type="CLOB"
       */
       public abstract String getName();
       public abstract void setName(String name);
      }
      


      JBoss logs
      2005-02-13 03:11:13,120 INFO [org.jboss.ejb.EjbModule] Stopping jboss.j2ee:module=TaxLiens.jar,service=EjbModule
      2005-02-13 03:11:13,139 INFO [org.jboss.management.j2ee.EJBModule] destroy(), remove EJB-Module: jboss.management.local:J2EEApplication=null,J2EEServer=Local,j2eeType=EJBModule,name=TaxLiens.jar
      2005-02-13 03:11:13,141 INFO [org.jboss.deployment.MainDeployer] Starting deployment of package: file:/var/lib/jboss/default/deploy/TaxLiens.jar
      2005-02-13 03:11:14,234 INFO [org.jboss.ejb.EjbModule] Deploying TestCMP
      2005-02-13 03:11:14,781 ERROR [org.jboss.ejb.EntityContainer] Starting failed
      org.jboss.deployment.DeploymentException: Error while creating table TestCMP; - nested throwable: (java.sql.SQLException: Syntax error or access violation, message from server: "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")
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.createTable(JDBCStartCommand.java:232)
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.execute(JDBCStartCommand.java:93)
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.startStoreManager(JDBCStoreManager.java:484)
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.start(JDBCStoreManager.java:388)
       at org.jboss.ejb.plugins.CMPPersistenceManager.start(CMPPersistenceManager.java:152)
       at org.jboss.ejb.EntityContainer.startService(EntityContainer.java:342)
       at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
       at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:324)
       at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
       at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
       at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:976)
       at $Proxy14.start(Unknown Source)
       at org.jboss.system.ServiceController.start(ServiceController.java:394)
       at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:324)
       at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
       at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
       at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
       at $Proxy150.start(Unknown Source)
       at org.jboss.ejb.EjbModule.startService(EjbModule.java:331)
       at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
       at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:324)
       at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
       at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
       at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:976)
       at $Proxy14.start(Unknown Source)
       at org.jboss.system.ServiceController.start(ServiceController.java:394)
       at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:324)
       at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
       at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
       at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
       at $Proxy12.start(Unknown Source)
       at org.jboss.ejb.EJBDeployer.start(EJBDeployer.java:544)
       at org.jboss.deployment.MainDeployer.start(MainDeployer.java:832)
       at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:642)
       at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:605)
       at sun.reflect.GeneratedMethodAccessor18.invoke(Unknown Source)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:324)
       at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
       at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
       at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
       at $Proxy6.deploy(Unknown Source)
       at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:302)
       at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:458)
       at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(AbstractDeploymentScanner.java:201)
       at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.loop(AbstractDeploymentScanner.java:212)
       at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.run(AbstractDeploymentScanner.java:191)
      Caused by: java.sql.SQLException: Syntax error or access violation, message from server: "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"
       at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1905)
       at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1109)
       at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1203)
       at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1164)
       at com.mysql.jdbc.Connection.execSQL(Connection.java:2087)
       at com.mysql.jdbc.Connection.execSQL(Connection.java:2037)
       at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1252)
       at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1198)
       at org.jboss.resource.adapter.jdbc.WrappedStatement.executeUpdate(WrappedStatement.java:262)
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.createTable(JDBCStartCommand.java:219)
       ... 56 more
      2005-02-13 03:11:14,786 WARN [org.jboss.system.ServiceController] Problem starting service jboss.j2ee:jndiName=TestCMPBean,service=EJB
      org.jboss.deployment.DeploymentException: Error while creating table TestCMP; - nested throwable: (java.sql.SQLException: Syntax error or access violation, message from server: "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")
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.createTable(JDBCStartCommand.java:232)
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.execute(JDBCStartCommand.java:93)
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.startStoreManager(JDBCStoreManager.java:484)
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.start(JDBCStoreManager.java:388)
       at org.jboss.ejb.plugins.CMPPersistenceManager.start(CMPPersistenceManager.java:152)
       at org.jboss.ejb.EntityContainer.startService(EntityContainer.java:342)
       at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
       at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:324)
       at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
       at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
       at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:976)
       at $Proxy14.start(Unknown Source)
       at org.jboss.system.ServiceController.start(ServiceController.java:394)
       at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:324)
       at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
       at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
       at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
       at $Proxy150.start(Unknown Source)
       at org.jboss.ejb.EjbModule.startService(EjbModule.java:331)
       at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
       at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:324)
       at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
       at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
       at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:976)
       at $Proxy14.start(Unknown Source)
       at org.jboss.system.ServiceController.start(ServiceController.java:394)
       at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:324)
       at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
       at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
       at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
       at $Proxy12.start(Unknown Source)
       at org.jboss.ejb.EJBDeployer.start(EJBDeployer.java:544)
       at org.jboss.deployment.MainDeployer.start(MainDeployer.java:832)
       at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:642)
       at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:605)
       at sun.reflect.GeneratedMethodAccessor18.invoke(Unknown Source)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:324)
       at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
       at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
       at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
       at $Proxy6.deploy(Unknown Source)
       at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:302)
       at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:458)
       at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(AbstractDeploymentScanner.java:201)
       at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.loop(AbstractDeploymentScanner.java:212)
       at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.run(AbstractDeploymentScanner.java:191)
      Caused by: java.sql.SQLException: Syntax error or access violation, message from server: "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"
       at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1905)
       at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1109)
       at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1203)
       at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1164)
       at com.mysql.jdbc.Connection.execSQL(Connection.java:2087)
       at com.mysql.jdbc.Connection.execSQL(Connection.java:2037)
       at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1252)
       at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1198)
       at org.jboss.resource.adapter.jdbc.WrappedStatement.executeUpdate(WrappedStatement.java:262)
       at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.createTable(JDBCStartCommand.java:219)
       ... 56 more
      2005-02-13 03:11:14,788 INFO [org.jboss.ejb.EjbModule] Started jboss.j2ee:module=TaxLiens.jar,service=EjbModule
      2005-02-13 03:11:14,788 INFO [org.jboss.ejb.EJBDeployer] Deployed: file:/var/lib/jboss/default/deploy/TaxLiens.jar
      2005-02-13 03:11:14,802 INFO [org.jboss.deployment.MainDeployer] Deployed package: file:/var/lib/jboss/default/deploy/TaxLiens.jar
      2005-02-13 03:11:14,803 ERROR [org.jboss.deployment.scanner.URLDeploymentScanner] MBeanException: Exception in MBean operation 'checkIncompleteDeployments()'
      Cause: Incomplete Deployment listing:
      Packages waiting for a deployer:
       <none>
      Incompletely deployed packages:
       <none>
      MBeans waiting for classes:
       <none>
      MBeans waiting for other MBeans:
      [ObjectName: jboss.j2ee:jndiName=TestCMPBean,service=EJB
       state: FAILED
       I Depend On:
       Depends On Me: org.jboss.deployment.DeploymentException: Error while creating table TestCMP; - nested throwable: (java.sql.SQLException: Syntax error or access violation, message from server: "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")]
      


      custom.properties
      xdoclet.lib.dir=/usr/share/xdoclet/lib
      j2ee.lib.dir=/opt/sun-j2ee-1.3.1/lib