Exception creating MySQL table using backquoted table name

SourceForge Submitter: jsimone .
According to the mySQL reference:

http://dev.mysql.com/doc/mysql/en/Legal_names.html

It is possible to create tables and columns with reserved
names by using the backquote character: `.

Using JBoss 4.0.0, JDK 1.5 and mySQL 4.0.21 (the latest
production version) results in a SQL exception from
using the following in jbosscmp-jdbc.xml :

<entity>
<ejb-name>Session</ejb-name>
<table-name>`SESSION`</table-name>
<cmp-field>
<field-name>id</field-name>
<column-name>ID</column-name>
....

However, when the backquotes are used for column
names - NO EXCEPTION occurs.

If I try and issue the SQL commands manually with the
backquotes this works fine.

It would appear the generated SQL is incorrect when
reserved words for table names are backquoted.

Below is the exception when trying to create the table
`SESSION`.

16:29:07,730 ERROR [EntityContainer] Starting failed
jboss.j2ee:jndiName=local/Comment,service=EJB
org.jboss.deployment.DeploymentException: Error while
creating table `SESSION`; - 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 'SESSION` PRIMARY KEY (ID))' at line 1")
at
org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.create
Table(JDBCStartCommand.java:540)
at
org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.execu
te(JDBCStartCommand.java:196)
at
org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.startSt
oreManager(JDBCStoreManager.java:484)
at
org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.start
(JDBCStoreManager.java:381)
at
org.jboss.ejb.plugins.CMPPersistenceManager.start
(CMPPersistenceManager.java:157)
at org.jboss.ejb.EntityContainer.startService
(EntityContainer.java:340)
at
org.jboss.system.ServiceMBeanSupport.jbossInternalStar
t(ServiceMBeanSupport.java:271)
at
org.jboss.system.ServiceMBeanSupport.jbossInternalLifec
ycle(ServiceMBeanSupport.java:221)
at
sun.reflect.GeneratedMethodAccessor82.invoke
(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke
(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke
(Method.java:585)
at
org.jboss.mx.interceptor.ReflectedDispatcher.invoke
(ReflectedDispatcher.java:141)
at org.jboss.mx.server.Invocation.dispatch
(Invocation.java:80)
at org.jboss.mx.server.Invocation.invoke
(Invocation.java:72)
at
org.jboss.mx.server.AbstractMBeanInvoker.invoke
(AbstractMBeanInvoker.java:242)
at org.jboss.mx.server.MBeanServerImpl.invoke
(MBeanServerImpl.java:642)
at
org.jboss.system.ServiceController$ServiceProxy.invoke
(ServiceController.java:891)
at $Proxy0.start(Unknown Source)
at org.jboss.system.ServiceController.start
(ServiceController.java:416)
at
sun.reflect.GeneratedMethodAccessor5.invoke(Unknown
Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke
(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke
(Method.java:585)
at
org.jboss.mx.interceptor.ReflectedDispatcher.invoke
(ReflectedDispatcher.java:141)
at org.jboss.mx.server.Invocation.dispatch
(Invocation.java:80)
at org.jboss.mx.server.Invocation.invoke
(Invocation.java:72)
at
org.jboss.mx.server.AbstractMBeanInvoker.invoke
(AbstractMBeanInvoker.java:242)
at org.jboss.mx.server.MBeanServerImpl.invoke
(MBeanServerImpl.java:642)
at org.jboss.mx.util.MBeanProxyExt.invoke
(MBeanProxyExt.java:176)
at $Proxy278.start(Unknown Source)
at org.jboss.ejb.EjbModule.startService
(EjbModule.java:394)
at
org.jboss.system.ServiceMBeanSupport.jbossInternalStar
t(ServiceMBeanSupport.java:271)
at
org.jboss.system.ServiceMBeanSupport.jbossInternalLifec
ycle(ServiceMBeanSupport.java:221)
at
sun.reflect.GeneratedMethodAccessor82.invoke
(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke
(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke
(Method.java:585)
at
org.jboss.mx.interceptor.ReflectedDispatcher.invoke
(ReflectedDispatcher.java:141)
at org.jboss.mx.server.Invocation.dispatch
(Invocation.java:80)
at org.jboss.mx.server.Invocation.invoke
(Invocation.java:72)
at
org.jboss.mx.server.AbstractMBeanInvoker.invoke
(AbstractMBeanInvoker.java:242)
at org.jboss.mx.server.MBeanServerImpl.invoke
(MBeanServerImpl.java:642)
at
org.jboss.system.ServiceController$ServiceProxy.invoke
(ServiceController.java:891)
at $Proxy0.start(Unknown Source)
at org.jboss.system.ServiceController.start
(ServiceController.java:416)
at
sun.reflect.GeneratedMethodAccessor5.invoke(Unknown
Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke
(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke
(Method.java:585)
at
org.jboss.mx.interceptor.ReflectedDispatcher.invoke
(ReflectedDispatcher.java:141)
at org.jboss.mx.server.Invocation.dispatch
(Invocation.java:80)
at org.jboss.mx.server.Invocation.invoke
(Invocation.java:72)
at
org.jboss.mx.server.AbstractMBeanInvoker.invoke
(AbstractMBeanInvoker.java:242)
at org.jboss.mx.server.MBeanServerImpl.invoke
(MBeanServerImpl.java:642)
at org.jboss.mx.util.MBeanProxyExt.invoke
(MBeanProxyExt.java:176)
at $Proxy19.start(Unknown Source)
at org.jboss.ejb.EJBDeployer.start
(EJBDeployer.java:605)
at org.jboss.deployment.MainDeployer.start
(MainDeployer.java:935)
at org.jboss.deployment.MainDeployer.start
(MainDeployer.java:927)
at org.jboss.deployment.MainDeployer.deploy
(MainDeployer.java:746)
at org.jboss.deployment.MainDeployer.deploy
(MainDeployer.java:709)
at
sun.reflect.GeneratedMethodAccessor29.invoke
(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke
(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke
(Method.java:585)
at
org.jboss.mx.interceptor.ReflectedDispatcher.invoke
(ReflectedDispatcher.java:141)
at org.jboss.mx.server.Invocation.dispatch
(Invocation.java:80)
at
org.jboss.mx.interceptor.AbstractInterceptor.invoke
(AbstractInterceptor.java:119)
at org.jboss.mx.server.Invocation.invoke
(Invocation.java:74)
at
org.jboss.mx.interceptor.ModelMBeanOperationIntercepto
r.invoke(ModelMBeanOperationInterceptor.java:131)
at org.jboss.mx.server.Invocation.invoke
(Invocation.java:74)
at
org.jboss.mx.server.AbstractMBeanInvoker.invoke
(AbstractMBeanInvoker.java:242)
at org.jboss.mx.server.MBeanServerImpl.invoke
(MBeanServerImpl.java:642)
at org.jboss.mx.util.MBeanProxyExt.invoke
(MBeanProxyExt.java:176)
at $Proxy8.deploy(Unknown Source)
at
org.jboss.deployment.scanner.URLDeploymentScanner.de
ploy(URLDeploymentScanner.java:305)
at
org.jboss.deployment.scanner.URLDeploymentScanner.sc
an(URLDeploymentScanner.java:463)
at
org.jboss.deployment.scanner.AbstractDeploymentScann
er$ScannerThread.doScan
(AbstractDeploymentScanner.java:204)
at
org.jboss.deployment.scanner.AbstractDeploymentScann
er$ScannerThread.loop
(AbstractDeploymentScanner.java:215)
at
org.jboss.deployment.scanner.AbstractDeploymentScann
er$ScannerThread.run
(AbstractDeploymentScanner.java:194)
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 'SESSION` PRIMARY KEY (ID))' at
line 1"
at com.mysql.jdbc.MysqlIO.checkErrorPacket
(MysqlIO.java:1997)
at com.mysql.jdbc.MysqlIO.sendCommand
(MysqlIO.java:1167)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect
(MysqlIO.java:1278)
at com.mysql.jdbc.MysqlIO.sqlQuery
(MysqlIO.java:1224)
at com.mysql.jdbc.Connection.execSQL
(Connection.java:2244)
at com.mysql.jdbc.Connection.execSQL
(Connection.java:2192)
at com.mysql.jdbc.Statement.executeUpdate
(Statement.java:1259)
at com.mysql.jdbc.Statement.executeUpdate
(Statement.java:1205)
at
org.jboss.resource.adapter.jdbc.WrappedStatement.exec
uteUpdate(WrappedStatement.java:229)
at
org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.create
Table(JDBCStartCommand.java:527)
... 75 more
16:29:07,770 WARN [ServiceController] Problem
starting service
jboss.j2ee:jndiName=local/Comment,service=EJB
org.jboss.deployment.DeploymentException: Error while
creating table `SESSION`; - 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 'SESSION` PRIMARY KEY (ID))' at line 1")
at
org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.create
Table(JDBCStartCommand.java:540)
at
org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.execu
te(JDBCStartCommand.java:196)
at
org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.startSt
oreManager(JDBCStoreManager.java:484)
at
org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.start
(JDBCStoreManager.java:381)
at
org.jboss.ejb.plugins.CMPPersistenceManager.start
(CMPPersistenceManager.java:157)
at org.jboss.ejb.EntityContainer.startService
(EntityContainer.java:340)
at
org.jboss.system.ServiceMBeanSupport.jbossInternalStar
t(ServiceMBeanSupport.java:271)
at
org.jboss.system.ServiceMBeanSupport.jbossInternalLifec
ycle(ServiceMBeanSupport.java:221)
at
sun.reflect.GeneratedMethodAccessor82.invoke
(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke
(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke
(Method.java:585)
at
org.jboss.mx.interceptor.ReflectedDispatcher.invoke
(ReflectedDispatcher.java:141)
at org.jboss.mx.server.Invocation.dispatch
(Invocation.java:80)
at org.jboss.mx.server.Invocation.invoke
(Invocation.java:72)
at
org.jboss.mx.server.AbstractMBeanInvoker.invoke
(AbstractMBeanInvoker.java:242)
at org.jboss.mx.server.MBeanServerImpl.invoke
(MBeanServerImpl.java:642)
at
org.jboss.system.ServiceController$ServiceProxy.invoke
(ServiceController.java:891)
at $Proxy0.start(Unknown Source)
at org.jboss.system.ServiceController.start
(ServiceController.java:416)
at
sun.reflect.GeneratedMethodAccessor5.invoke(Unknown
Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke
(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke
(Method.java:585)
at
org.jboss.mx.interceptor.ReflectedDispatcher.invoke
(ReflectedDispatcher.java:141)
at org.jboss.mx.server.Invocation.dispatch
(Invocation.java:80)
at org.jboss.mx.server.Invocation.invoke
(Invocation.java:72)
at
org.jboss.mx.server.AbstractMBeanInvoker.invoke
(AbstractMBeanInvoker.java:242)
at org.jboss.mx.server.MBeanServerImpl.invoke
(MBeanServerImpl.java:642)
at org.jboss.mx.util.MBeanProxyExt.invoke
(MBeanProxyExt.java:176)
at $Proxy278.start(Unknown Source)
at org.jboss.ejb.EjbModule.startService
(EjbModule.java:394)
at
org.jboss.system.ServiceMBeanSupport.jbossInternalStar
t(ServiceMBeanSupport.java:271)
at
org.jboss.system.ServiceMBeanSupport.jbossInternalLifec
ycle(ServiceMBeanSupport.java:221)
at
sun.reflect.GeneratedMethodAccessor82.invoke
(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke
(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke
(Method.java:585)
at
org.jboss.mx.interceptor.ReflectedDispatcher.invoke
(ReflectedDispatcher.java:141)
at org.jboss.mx.server.Invocation.dispatch
(Invocation.java:80)
at org.jboss.mx.server.Invocation.invoke
(Invocation.java:72)
at
org.jboss.mx.server.AbstractMBeanInvoker.invoke
(AbstractMBeanInvoker.java:242)
at org.jboss.mx.server.MBeanServerImpl.invoke
(MBeanServerImpl.java:642)
at
org.jboss.system.ServiceController$ServiceProxy.invoke
(ServiceController.java:891)
at $Proxy0.start(Unknown Source)
at org.jboss.system.ServiceController.start
(ServiceController.java:416)
at
sun.reflect.GeneratedMethodAccessor5.invoke(Unknown
Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke
(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke
(Method.java:585)
at
org.jboss.mx.interceptor.ReflectedDispatcher.invoke
(ReflectedDispatcher.java:141)
at org.jboss.mx.server.Invocation.dispatch
(Invocation.java:80)
at org.jboss.mx.server.Invocation.invoke
(Invocation.java:72)
at
org.jboss.mx.server.AbstractMBeanInvoker.invoke
(AbstractMBeanInvoker.java:242)
at org.jboss.mx.server.MBeanServerImpl.invoke
(MBeanServerImpl.java:642)
at org.jboss.mx.util.MBeanProxyExt.invoke
(MBeanProxyExt.java:176)
at $Proxy19.start(Unknown Source)
at org.jboss.ejb.EJBDeployer.start
(EJBDeployer.java:605)
at org.jboss.deployment.MainDeployer.start
(MainDeployer.java:935)
at org.jboss.deployment.MainDeployer.start
(MainDeployer.java:927)
at org.jboss.deployment.MainDeployer.deploy
(MainDeployer.java:746)
at org.jboss.deployment.MainDeployer.deploy
(MainDeployer.java:709)
at
sun.reflect.GeneratedMethodAccessor29.invoke
(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke
(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke
(Method.java:585)
at
org.jboss.mx.interceptor.ReflectedDispatcher.invoke
(ReflectedDispatcher.java:141)
at org.jboss.mx.server.Invocation.dispatch
(Invocation.java:80)
at
org.jboss.mx.interceptor.AbstractInterceptor.invoke
(AbstractInterceptor.java:119)
at org.jboss.mx.server.Invocation.invoke
(Invocation.java:74)
at
org.jboss.mx.interceptor.ModelMBeanOperationIntercepto
r.invoke(ModelMBeanOperationInterceptor.java:131)
at org.jboss.mx.server.Invocation.invoke
(Invocation.java:74)
at
org.jboss.mx.server.AbstractMBeanInvoker.invoke
(AbstractMBeanInvoker.java:242)
at org.jboss.mx.server.MBeanServerImpl.invoke
(MBeanServerImpl.java:642)
at org.jboss.mx.util.MBeanProxyExt.invoke
(MBeanProxyExt.java:176)
at $Proxy8.deploy(Unknown Source)
at
org.jboss.deployment.scanner.URLDeploymentScanner.de
ploy(URLDeploymentScanner.java:305)
at
org.jboss.deployment.scanner.URLDeploymentScanner.sc
an(URLDeploymentScanner.java:463)
at
org.jboss.deployment.scanner.AbstractDeploymentScann
er$ScannerThread.doScan
(AbstractDeploymentScanner.java:204)
at
org.jboss.deployment.scanner.AbstractDeploymentScann
er$ScannerThread.loop
(AbstractDeploymentScanner.java:215)
at
org.jboss.deployment.scanner.AbstractDeploymentScann
er$ScannerThread.run
(AbstractDeploymentScanner.java:194)
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 'SESSION` PRIMARY KEY (ID))' at
line 1"
at com.mysql.jdbc.MysqlIO.checkErrorPacket
(MysqlIO.java:1997)
at com.mysql.jdbc.MysqlIO.sendCommand
(MysqlIO.java:1167)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect
(MysqlIO.java:1278)
at com.mysql.jdbc.MysqlIO.sqlQuery
(MysqlIO.java:1224)
at com.mysql.jdbc.Connection.execSQL
(Connection.java:2244)
at com.mysql.jdbc.Connection.execSQL
(Connection.java:2192)
at com.mysql.jdbc.Statement.executeUpdate
(Statement.java:1259)
at com.mysql.jdbc.Statement.executeUpdate
(Statement.java:1205)
at
org.jboss.resource.adapter.jdbc.WrappedStatement.exec
uteUpdate(WrappedStatement.java:229)
at
org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.create
Table(JDBCStartCommand.java:527)
... 75 more
16:29:08,821 INFO [EJBDeployer] Deployed:
file:/C:/java/jboss-
4.0.0/server/default/tmp/deploy/tmp21076ear2.ear-
contents/ejb.jar
16:29:11,235 INFO [EARDeployer] Started J2EE
application: file:/C:/java/jboss-
4.0.0/server/default/deploy/ear2.ear