Error on configure Jboss 3.2.1 to Mysql 4.0.14b
panco Sep 21, 2003 8:50 PMwhat I am trying to do is to use mySQL as the Default DataSource, but it fails to create the JMS_TRANSACTION table.... I can't figure out what's wrong with the setting, can anyone help?
I am using Windows XP and software version that I used are:
jsdk = 1.4.1_05
j2ee = 1.4 beta2
jboss = 3.2.1 with Tomcat 4.1.24
mysql = 4.0.14b
DataSource Configuration that i made:
--------------------------
mysql configure
---------------
1. enable transaction support
[WinMySQLadmin]
Server=C:/mysql/bin/mysqld-max-nt.exe
2. create JBOSSDB
create database jbossdb;
grant all privileges on jbossdb.* to admin@localhost identified by '11223344';
grant all privileges on jbossdb.* to admin identified by '11223344';
jboss configure
---------------
1. remove Hypersonic DataSource by deleting .\server\default\deploy\hsqldb-ds.xml
2. copy .\docs\examples\jca\mysql-ds.xml to .\server\default\deploy
3. edit mysql-ds.xml
<?xml version="1.0" encoding="UTF-8"?>
<local-tx-datasource>
<jndi-name>DefaultDS</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/jbossdb</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>admin</user-name>
11223344
</local-tx-datasource>
4. copy mysql-connector-java-3.0.8-stable-bin.jar to .\server\default\lib
5. Edit server\default\conf\standardjbosscmp-jdbc.xml
5a. Change the datasource-mapping from Hypersonic SQL to mySQL.
5b. Change the value of fk-constraint to true .
java:/DefaultDS
<datasource-mapping>mySQL</datasource-mapping>
:
(skipped)
:
<fk-constraint>true</fk-constraint>
:
(skipped)
6. Edit server\default\conf\standardjaws.xml
6a. Change the type-mapping from Hypersonic SQL to mySQL.
java:/DefaultDS
<type-mapping>mySQL</type-mapping>
:
(skipped)
======================================
Error in .\server\default\log
2003-09-22 10:04:25,718 INFO [org.jboss.mq.pm.jdbc2.PersistenceManager] Starting
2003-09-22 10:04:25,718 DEBUG [org.jboss.mq.pm.jdbc2.PersistenceManager] Resolving uncommited TXS
2003-09-22 10:04:25,765 DEBUG [org.jboss.resource.connectionmanager.IdleRemover] internalRegisterPool: registering pool with interval 900000 old interval: 9223372036854775807
2003-09-22 10:04:25,765 DEBUG [org.jboss.resource.connectionmanager.IdleRemover] internalRegisterPool: about to notify thread: old next: 1064196715765, new next: 1064196715765
2003-09-22 10:04:25,765 DEBUG [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory] Using properties: {user=admin, password=11223344}
2003-09-22 10:04:25,765 DEBUG [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory] Checking driver for URL: jdbc:mysql://localhost:3306/jbossdb
2003-09-22 10:04:25,765 DEBUG [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory] Driver not yet registered for url: jdbc:mysql://localhost:3306/jbossdb
2003-09-22 10:04:25,765 DEBUG [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory] Driver not yet registered for url: jdbc:mysql://localhost:3306/jbossdb
2003-09-22 10:04:25,765 DEBUG [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory] Driver already registered for url: jdbc:mysql://localhost:3306/jbossdb
2003-09-22 10:04:26,015 DEBUG [org.jboss.mq.pm.jdbc2.PersistenceManager] Could not create table with SQL: CREATE TABLE JMS_MESSAGES ( MESSAGEID INTEGER NOT NULL, DESTINATION VARCHAR(255) NOT NULL, TXID INTEGER, TXOP CHAR(1), MESSAGEBLOB OBJECT, PRIMARY KEY (MESSAGEID, DESTINATION) ), got : 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 'OBJECT, PRIMARY KEY (MESSAGEID, DESTINATION) )' at line 1"
2003-09-22 10:04:26,031 DEBUG [org.jboss.util.NestedThrowable] org.jboss.util.NestedThrowable.parentTraceEnabled=true
2003-09-22 10:04:26,046 DEBUG [org.jboss.util.NestedThrowable] org.jboss.util.NestedThrowable.nestedTraceEnabled=false
2003-09-22 10:04:26,046 DEBUG [org.jboss.util.NestedThrowable] org.jboss.util.NestedThrowable.detectDuplicateNesting=true
2003-09-22 10:04:26,046 WARN [org.jboss.resource.adapter.jdbc.WrappedConnection] Closing a statement you left open, please do your own housekeeping
2003-09-22 10:04:26,046 ERROR [org.jboss.mq.pm.jdbc2.PersistenceManager] Starting failed
org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Message recovery may not be accurate; - 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 'SELECT TXID FROM JMS_TRANSACTIONS) AND TXOP='A'' at line 1")
at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(PersistenceManager.java:276)
at org.jboss.mq.pm.jdbc2.PersistenceManager.startService(PersistenceManager.java:1299)
at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
at sun.reflect.GeneratedMethodAccessor56.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:549)
at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:966)
at $Proxy11.start(Unknown Source)
at org.jboss.system.ServiceController.start(ServiceController.java:392)
at org.jboss.system.ServiceController.start(ServiceController.java:408)
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:549)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
at $Proxy5.start(Unknown Source)
at org.jboss.deployment.SARDeployer.start(SARDeployer.java:226)
at org.jboss.deployment.MainDeployer.start(MainDeployer.java:832)
at org.jboss.deployment.MainDeployer.start(MainDeployer.java:824)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:640)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:613)
at sun.reflect.GeneratedMethodAccessor36.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:549)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
at $Proxy7.deploy(Unknown Source)
at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:302)
at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:476)
at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(AbstractDeploymentScanner.java:200)
at org.jboss.deployment.scanner.AbstractDeploymentScanner.startService(AbstractDeploymentScanner.java:273)
at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
at sun.reflect.GeneratedMethodAccessor7.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:549)
at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:966)
at $Proxy0.start(Unknown Source)
at org.jboss.system.ServiceController.start(ServiceController.java:392)
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:549)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
at $Proxy5.start(Unknown Source)
at org.jboss.deployment.SARDeployer.start(SARDeployer.java:226)
at org.jboss.deployment.MainDeployer.start(MainDeployer.java:832)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:640)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:613)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:597)
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:549)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
at $Proxy6.deploy(Unknown Source)
at org.jboss.system.server.ServerImpl.doStart(ServerImpl.java:365)
at org.jboss.system.server.ServerImpl.start(ServerImpl.java:272)
at org.jboss.Main.boot(Main.java:150)
at org.jboss.Main$1.run(Main.java:388)
at java.lang.Thread.run(Thread.java:536)
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 'SELECT TXID FROM JMS_TRANSACTIONS) AND TXOP='A'' at line 1"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956)
at com.mysql.jdbc.Connection.execSQL(Connection.java:1874)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1700)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1569)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:308)
at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(PersistenceManager.java:249)
... 68 more
2003-09-22 10:04:26,187 WARN [org.jboss.system.ServiceController] Problem starting service jboss.mq:service=PersistenceManager
org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Message recovery may not be accurate; - 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 'SELECT TXID FROM JMS_TRANSACTIONS) AND TXOP='A'' at line 1")
at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(PersistenceManager.java:276)
at org.jboss.mq.pm.jdbc2.PersistenceManager.startService(PersistenceManager.java:1299)
at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
at sun.reflect.GeneratedMethodAccessor56.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:549)
at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:966)
at $Proxy11.start(Unknown Source)
at org.jboss.system.ServiceController.start(ServiceController.java:392)
at org.jboss.system.ServiceController.start(ServiceController.java:408)
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:549)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
at $Proxy5.start(Unknown Source)
at org.jboss.deployment.SARDeployer.start(SARDeployer.java:226)
at org.jboss.deployment.MainDeployer.start(MainDeployer.java:832)
at org.jboss.deployment.MainDeployer.start(MainDeployer.java:824)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:640)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:613)
at sun.reflect.GeneratedMethodAccessor36.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:549)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
at $Proxy7.deploy(Unknown Source)
at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:302)
at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:476)
at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(AbstractDeploymentScanner.java:200)
at org.jboss.deployment.scanner.AbstractDeploymentScanner.startService(AbstractDeploymentScanner.java:273)
at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
at sun.reflect.GeneratedMethodAccessor7.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:549)
at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:966)
at $Proxy0.start(Unknown Source)
at org.jboss.system.ServiceController.start(ServiceController.java:392)
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:549)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
at $Proxy5.start(Unknown Source)
at org.jboss.deployment.SARDeployer.start(SARDeployer.java:226)
at org.jboss.deployment.MainDeployer.start(MainDeployer.java:832)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:640)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:613)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:597)
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:549)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
at $Proxy6.deploy(Unknown Source)
at org.jboss.system.server.ServerImpl.doStart(ServerImpl.java:365)
at org.jboss.system.server.ServerImpl.start(ServerImpl.java:272)
at org.jboss.Main.boot(Main.java:150)
at org.jboss.Main$1.run(Main.java:388)
at java.lang.Thread.run(Thread.java:536)
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 'SELECT TXID FROM JMS_TRANSACTIONS) AND TXOP='A'' at line 1"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956)
at com.mysql.jdbc.Connection.execSQL(Connection.java:1874)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1700)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1569)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:308)
at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(PersistenceManager.java:249)
... 68 more