2 Replies Latest reply on Dec 14, 2004 1:18 PM by George Berish

    Unsolvable MySql Configuration.

    George Berish Novice

      I configured my local 3.2.3 JBoss server to use MySql with no problem.

      I tired to do that same with my 3.2.6 JBoss server that is hosted on the web. And I get an error I cannot get a handle on. (Note: I tried uninstalling jboss, deleting the residual jboss file, reinstalling (got a clean startup) and then I tried to configue mysql.)

      The relevant part of the error message is:

      2004-12-13 18:28:03,288 INFO [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.MySqlDS] Bound connection factory for resource adapter for ConnectionManager 'jboss.jca:service=LocalTxCM,name=MySqlDS to JNDI name 'java:/MySqlDS'
      2004-12-13 18:28:03,859 ERROR [org.jboss.mq.pm.jdbc2.PersistenceManager] Starting failed 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 near 'JMS_MESSAGES FROM JMS_MESSAGES, JMS_TRANSACTIONS WHERE JMS_MESSAGES.TXID = JMS_T' at line 1")
       at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(PersistenceManager.java:391)

      Note the text shown points to mysql-jdbc2-service.xml below that I copied without modification from docs/examples/jms to default/deploy/jms

      The code used to econfigure jboss to use MySql is:

      In standardjbosscmp-jdbc.xml
      <jbosscmp-jdbc>
       <defaults>
       <datasource>java:/MySqlDS</datasource>
       <datasource-mapping>mySQL</datasource-mapping>
       ...
      

      In mysql-ds.xml:
      <datasources>
       <local-tx-datasource>
       <jndi-name>MySqlDS</jndi-name>
       <connection-url>jdbc:mysql://America-3.org:3306/tapp</connection-url>
       <driver-class>org.gjt.mm.mysql.Driver</driver-class>
       <user-name>user1</user-name>
       <password>testpw</password>
       </local-tx-datasource>
      </datasources>

      The relevant code in mysql-jdbc2-service.xml :
      <!-- The PersistenceManager is used to store messages to disk. -->
       <!--
       | The jdbc2 PersistenceManager is the new improved JDBC implementation.
       | This implementation allows you to control how messages are stored in
       | the database.
       |
       | This jdbc2 PM configuration has was supplied by Stephane Nicoll in the forums as an example for MySQL
       -->
       <mbean code="org.jboss.mq.pm.jdbc2.PersistenceManager"
       name="jboss.mq:service=PersistenceManager">
       <depends optional-attribute-name="ConnectionManager">jboss.jca:service=LocalTxCM,name=MySqlDS</depends>
       <attribute name="SqlProperties">
       BLOB_TYPE=BYTES_BLOB
       INSERT_TX = INSERT INTO JMS_TRANSACTIONS (TXID) values(?)
       INSERT_MESSAGE = INSERT INTO JMS_MESSAGES (MESSAGEID, DESTINATION, MESSAGEBLOB, TXID, TXOP) VALUES(?,?,?,?,?)
       SELECT_ALL_UNCOMMITED_TXS = SELECT TXID FROM JMS_TRANSACTIONS
       SELECT_MAX_TX = SELECT MAX(TXID) FROM JMS_MESSAGES
       SELECT_MESSAGES_IN_DEST = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE DESTINATION=?
       SELECT_MESSAGE = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?


      I truely need help of at least a pointer to where I should go.

      I have to belive I have an access and not a syntax problem, because the error points to mysql-jdbc2-service.xml and I just copy that from jboss's own library of examples without modification.

      Thanks,
      George







        • 1. Re: Unsolvable MySql Configuration.
          Darran Lofthouse Master

          Did you remove the tables from the MySQL database when you moved from 3.2.3 to 3.2.6?

          • 2. Re: Unsolvable MySql Configuration.
            George Berish Novice

            I am not sure I explained my question fully.

            I have jboss and a mysql server installed on my local computer. Everything works there.

            I have jboss and a mysql server installed by a company called eApps who is my webb host. That is were the problem is.

            I am pretty certain that the remote mysql server is ok, because I wrote a simple local java client that has no problem connecting to the remote database and using JDBC to exectue a SELECT command to reading the table there.

            Its just that on start up of the remote jboss, I get that error message.

            Then when I go into the JMX Console, I find that the default Topic (testTopic) didn't instantiate correctly so I cannot test JMS from my local client.

            I hope that makes sense.