Problem using MySQL as DefaultDS

[davout]

I'm trying to adapt my JBoss3.2.3 to use MySQL V4.0.16 as the default data source.

However, when I run JBoss I get the following errors...

13:56:48,522 INFO [TxConnectionManager] Started jboss.jca:service=TxCM,name=JmsXA

13:56:48,532 INFO [DefaultDS] Bound connection factory for resource adapter for ConnectionManager 'jboss.jca:service=LocalTxCM,name=DefaultDS to JNDI name 'java:/DefaultDS'

13:56:48,532 INFO [TxConnectionManager] Started jboss.jca:service=LocalTxCM,name=DefaultDS

13:56:49,213 ERROR [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:336)

 at org.jboss.mq.pm.jdbc2.PersistenceManager.startService(PersistenceManager.java:1356)

 at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)

 at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)

My changes to the related config files were as follows...

Copied 'mysql-ds.xm' into 'deploy' folder. Changed content to...

<datasources>
 <local-tx-datasource>
 <jndi-name>DefaultDS</jndi-name>
 <connection-url>jdbc:mysql://localhost/berthier1000</connection-url>
 <driver-class>com.mysql.jdbc.Driver</driver-class>
 <user-name>root</user-name>
 <password></password>
 </local-tx-datasource>

</datasources>

Changed file 'StandardJBoss-cmp.xml' to...

<jbosscmp-jdbc>

 <defaults>
 <datasource>java:/DefaultDS</datasource>
 <datasource-mapping>mySQL</datasource-mapping>

Changed 'standardjaws.xml' file...

<jaws>
 <datasource>java:/DefaultDS</datasource>
 <type-mapping>mySQL</type-mapping>
 <debug>false</debug>

Is this correct? Have I missed something?

[adrian.brock]

Why didn't you?

1) Use search or read the FAQ and stop wasting people's time with
questions that have been answered many times before.

2) Read the comments at the top of hsqldb-jdbc2-service.xml

http://www.jboss.org/wiki/Wiki.jsp?page=JBossForums

[davout]

From a support perspective it may be tedious answering the same questions time after time, but to stop this you have to expose the answers in a fashion where they may be easily located.

I've looked at both FAQ and the WIKI - and have even run a search - and can find no specific step by step instruction on how to change the Default DS to use MySQL. I would have expected to see a FAQ section entitled something like "Changing the default data source", with sub entries for each major RDBMS.

If you can point me at the past answers then I'll happily contribute a WIKI entry that provides just this!

[raja05]

Mysql doesnt support subqueries AFAIK. Try changing it to this in ur hsqldb-jdbc2-service.xml for DELETE_MARKED_MESSAGES_WITH_TX

DELETE JMS_MESSAGES FROM JMS_MESSAGES msg , JMS_TRANSACTIONS tran WHERE msg.TXID = tran.TXID AND TXOP = ?
This works for 4.0.x and there is a different syntx for 4.1.x. Lookup Mysql docs to find more.

[davout]

I following the previous correspondents instruction and copied the file 'mysql-jdbc2-service.xml' into the 'deploy/jms' folder (and removed the equivalent hsql file). This seems to have cured the first problem.

I now get the following (error?) messages at the end of the JBoss start up sequence... any idea what this means?

15:26:49,059 ERROR [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.mq:service=InvocationLayer,type=HTTP
 state: CONFIGURED
 I Depend On: jboss.mq:service=Invoker
 jboss.web:service=WebServer
 Depends On Me: , ObjectName: jboss.mq.destination:service=Topic,name=testTopic
 state: CONFIGURED
 I Depend On: jboss.mq:service=DestinationManager
 jboss.mq:service=SecurityManager
 Depends On Me: , ObjectName: jboss.mq.destination:service=Topic,name=securedTopic
 state: CONFIGURED
 I Depend On: jboss.mq:service=DestinationManager
 jboss.mq:service=SecurityManager
 Depends On Me: , ObjectName: jboss.mq.destination:service=Topic,name=testDurableTopic
 state: CONFIGURED
 I Depend On: jboss.mq:service=DestinationManager
 jboss.mq:service=SecurityManager
 Depends On Me: , ObjectName: jboss.mq.destination:service=Queue,name=testQueue
 state: CONFIGURED
 I Depend On: jboss.mq:service=DestinationManager
 jboss.mq:service=SecurityManager
 Depends On Me: , ObjectName: jboss.mq.destination:service=Queue,name=A
 state: CONFIGURED
 I Depend On: jboss.mq:service=DestinationManager
 Depends On Me: , ObjectName: jboss.mq.destination:service=Queue,name=B
 state: CONFIGURED
 I Depend On: jboss.mq:service=DestinationManager
 Depends On Me: , ObjectName: jboss.mq.destination:service=Queue,name=C
 state: CONFIGURED
 I Depend On: jboss.mq:service=DestinationManager
 Depends On Me: , ObjectName: jboss.mq.destination:service=Queue,name=D
 state: CONFIGURED
 I Depend On: jboss.mq:service=DestinationManager
 Depends On Me: , ObjectName: jboss.mq.destination:service=Queue,name=ex
 state: CONFIGURED
 I Depend On: jboss.mq:service=DestinationManager
 Depends On Me: , ObjectName: jboss.mq:service=Invoker
 state: CONFIGURED
 I Depend On: jboss.mq:service=TracingInterceptor
 Depends On Me: jboss.mq:service=InvocationLayer,type=HTTP
 jboss.mq:service=InvocationLayer,type=JVM
 jboss.mq:service=InvocationLayer,type=OIL
 jboss.mq:service=InvocationLayer,type=OIL2
 jboss.mq:service=InvocationLayer,type=RMI
 jboss.mq:service=InvocationLayer,type=UIL2
, ObjectName: jboss.mq:service=TracingInterceptor
 state: CONFIGURED
 I Depend On: jboss.mq:service=SecurityManager
 Depends On Me: jboss.mq:service=Invoker
, ObjectName: jboss.mq:service=SecurityManager
 state: CONFIGURED
 I Depend On: jboss.mq:service=DestinationManager
 Depends On Me: jboss.mq.destination:service=Topic,name=testTopic
 jboss.mq.destination:service=Topic,name=securedTopic
 jboss.mq.destination:service=Topic,name=testDurableTopic
 jboss.mq.destination:service=Queue,name=testQueue
 jboss.mq:service=TracingInterceptor
 jboss.mq.destination:service=Queue,name=DLQ
, ObjectName: jboss.mq.destination:service=Queue,name=DLQ
 state: CONFIGURED
 I Depend On: jboss.mq:service=DestinationManager
 jboss.mq:service=SecurityManager
 Depends On Me: , ObjectName: jboss.mq:service=InvocationLayer,type=JVM
 state: CONFIGURED
 I Depend On: jboss.mq:service=Invoker
 Depends On Me: , ObjectName: jboss.mq:service=DestinationManager
 state: CONFIGURED
 I Depend On: jboss.mq:service=MessageCache
 jboss.mq:service=PersistenceManager
 jboss.mq:service=StateManager
 Depends On Me: jboss.mq.destination:service=Topic,name=testTopic
 jboss.mq.destination:service=Topic,name=securedTopic
 jboss.mq.destination:service=Topic,name=testDurableTopic
 jboss.mq.destination:service=Queue,name=testQueue
 jboss.mq.destination:service=Queue,name=A
 jboss.mq.destination:service=Queue,name=B
 jboss.mq.destination:service=Queue,name=C
 jboss.mq.destination:service=Queue,name=D
 jboss.mq.destination:service=Queue,name=ex
 jboss.mq:service=SecurityManager
 jboss.mq.destination:service=Queue,name=DLQ
, ObjectName: jboss.mq:service=PersistenceManager
 state: CONFIGURED
 I Depend On: jboss.jca:service=LocalTxCM,name=MySqlDS
 Depends On Me: jboss.mq:service=DestinationManager
, ObjectName: jboss.mq:service=InvocationLayer,type=OIL
 state: CONFIGURED
 I Depend On: jboss.mq:service=Invoker
 Depends On Me: , ObjectName: jboss.mq:service=InvocationLayer,type=OIL2
 state: CONFIGURED
 I Depend On: jboss.mq:service=Invoker
 Depends On Me: , ObjectName: jboss.mq:service=InvocationLayer,type=RMI
 state: CONFIGURED
 I Depend On: jboss.mq:service=Invoker
 Depends On Me: , ObjectName: jboss.mq:service=InvocationLayer,type=UIL2
 state: CONFIGURED
 I Depend On: jboss.mq:service=Invoker
 Depends On Me: ]
15:26:49,129 INFO [URLDeploymentScanner] Started jboss.deployment:type=DeploymentScanner,flavor=URL
15:26:49,180 INFO [MainDeployer] Deployed package: file:/C:/jlibs/jboss-3.2.3/server/default/conf/jboss-service.xml
15:26:49,210 INFO [Server] JBoss (MX MicroKernel) [3.2.3 (build: CVSTag=JBoss_3_2_3 date=200311301445)] Started in 22s:603ms
15:26:50,261 INFO [Engine] StandardHost[localhost]: MAPPING configuration error for request URI /
15:26:50,261 INFO [Engine] StandardHost[localhost]: MAPPING configuration error for request URI /
15:26:52,334 INFO [Engine] StandardHost[localhost]: MAPPING configuration error for request URI /
15:26:52,334 INFO [Engine] StandardHost[localhost]: MAPPING configuration error for request URI /
15:26:54,447 INFO [Engine] StandardHost[localhost]: MAPPING configuration error for request URI /
15:26:54,447 INFO [Engine] StandardHost[localhost]: MAPPING configuration error for request URI /
15:26:56,570 INFO [Engine] StandardHost[localhost]: MAPPING configuration error for request URI /
15:26:56,570 INFO [Engine] StandardHost[localhost]: MAPPING configuration error for request URI /

[adrian.brock]

It is answered on the JMS FAQ
http://www.jboss.org/wiki/Wiki.jsp?page=FAQJBossMQ

Of course since you didn't bother to use search, you never got that far down the page.
Which is why this post is in the wrong forum. It should be in the JCA
(datasource) or JMS forum.

What is annoying is not the repeated questions, it that the lazy people who post these
repeated questions without using search, are always the moaners about lack of documentation and then don't understand the answers when it is served to them on a plate.

I bet you never even read the link I posted about using the forums and why your post
wastes peoples time (not just mine) and pollutes the search index.
Remember this post is sent to the jboss-user mailing list which has thousands of
subscribers.

I Depend On: jboss.jca:service=LocalTxCM,name=MySqlDS

You have changed your MySQL datasource jndi binding to DefaultDS not MySQLDS

[adrian.brock]

I can expect you to get it wrong if you just try it and see, but I would expect
you make an effort to find out what you did wrong, rather than
just dumping every stacktrace into the forums for us to answer while you
play solataire or talk to your work mate about last night's baseball game.

[davout]

Found the answer...

The 'mysql-jdbc2-service.xml' file had to be changed to use the 'DefaultDS' name within the mbean definition.

Seems to me that JBoss would be best advised to create a set of XML config files for each RDBMS already defined for use as 'DefaultDS'. Then have a batch file that simply copies the required files into place.

[adrian.brock]

"davout" wrote:

Seems to me that JBoss would be best advised to create a set of XML config files for each RDBMS already defined for use as 'DefaultDS'. Then have a batch file that simply copies the required files into place.

That is not how it works. It is open source.
You want it, you do it, then contribute it back as a patch or the on the WIKI.
My guess is that you don't, leaving the next person to go through the same
headless chicken behaviour.