8 Replies Latest reply on May 27, 2004 11:37 AM by Adrian Brock

    Problem using MySQL as DefaultDS

    davout Newbie

      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?





        • 1. Re: Problem using MySQL as DefaultDS
          Adrian Brock Master

          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

          • 2. Re: Problem using MySQL as DefaultDS
            davout Newbie

            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!

            • 3. Re: Problem using MySQL as DefaultDS
              Raja Master

              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.

              • 4. Re: Problem using MySQL as DefaultDS
                davout Newbie

                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 /
                



                • 5. Re: Problem using MySQL as DefaultDS
                  Adrian Brock Master

                  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

                  • 6. Re: Problem using MySQL as DefaultDS
                    Adrian Brock Master

                    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.

                    • 7. Re: Problem using MySQL as DefaultDS
                      davout Newbie

                      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.

                      • 8. Re: Problem using MySQL as DefaultDS
                        Adrian Brock Master

                         

                        "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.