MySQL connection problem with OSRMT

Users
Kay G
2009-06-29
2013-05-01
  • Kay G
    Kay G
    2009-06-29

    Hello,

    I installed OSRMT and MySQL on a HP-UX server. If I run the 2 tier client (run.sh), it accesses MySQL through JDBC. It works fine.

    Then I move to 3 tier client still on the same machine.

    I copied connection.3teirj2ee.xml to connection.xml, updated dd/client/jndi.properties points to the //localhost:1099. I even modified server/jboss-4.0.3/bin/connection.xml as indicated by a thread in this forum.

    <?xml version="1.0" encoding="UTF-8"?>
    <java version="1.5.0_04" class="java.beans.XMLDecoder">
    <object class="com.osframework.datalibrary.common.ConnectionProperty">
      <void property="accessSequence">
       <int>0</int>
      </void>
      <void property="active">
       <boolean>true</boolean>
      </void>
      <void property="connectionType">
       <string>J2ee</string>
      </void>
      <void property="connectToURL">
       <boolean>false</boolean>
      </void>
      <void property="driverClass">
       <string>com.mysql.jdbc.Driver</string>
      </void>
      <void property="environment">
       <string>mysql</string>
      </void>
      <void property="url">
       <string>jdbc:mysql://localhost:3306/osrmt</string>
      </void>
      <void property="unicodeConnection">
       <boolean>true</boolean>
      </void>
      <void property="username">
       <string>osrmt</string>
      </void>
      <void property="password">
       <string>osrmt</string>
      </void>
    </object>
    </java>

    But I always get the following error.

    ------->

    com.osframework.datalibrary.common.DataAccessException: com.osframework.datalibrary.common.DataAccessException: java.sql.SQLException: No suitable driver
        at com.osframework.datalibrary.reference.security.ApplicationControlDbAdapter.getApplicationControl(ApplicationControlDbAdapter.java:343)
        at com.osframework.datalibrary.reference.security.ApplicationControlDataAdapter.getControlsByApplication(ApplicationControlDataAdapter.java:52)
        at com.osframework.ejb.reference.security.SecurityBean.getAppControls(SecurityBean.java:408)
        at com.osframework.ejb.reference.security.SecurityBean.getAppControls(SecurityBean.java:396)
        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:585)
        at org.jboss.invocation.Invocation.performCall(Invocation.java:345)
        at org.jboss.ejb.EntityContainer$ContainerInterceptor.invoke(EntityContainer.java:1174)
        at org.jboss.ejb.plugins.EntitySynchronizationInterceptor.invoke(EntitySynchronizationInterceptor.java:270)
        at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:149)
        at org.jboss.ejb.plugins.EntityReentranceInterceptor.invoke(EntityReentranceInterceptor.java:111)
        at org.jboss.ejb.plugins.EntityInstanceInterceptor.invoke(EntityInstanceInterceptor.java:261)
        at org.jboss.ejb.plugins.EntityLockInterceptor.invoke(EntityLockInterceptor.java:89)
        at org.jboss.ejb.plugins.EntityCreationInterceptor.invoke(EntityCreationInterceptor.java:53)
        at org.jboss.ejb.plugins.CallValidationInterceptor.invoke(CallValidationInterceptor.java:48)
        at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:106)
        at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:335)
        at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:166)
        at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:153)
        at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:192)
        at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:122)
        at org.jboss.ejb.EntityContainer.internalInvoke(EntityContainer.java:514)
        at org.jboss.ejb.Container.invoke(Container.java:873)
        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: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:245)
        at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644)
        at org.jboss.invocation.jrmp.server.JRMPInvoker$MBeanServerAction.invoke(JRMPInvoker.java:805)
        at org.jboss.invocation.jrmp.server.JRMPInvoker.invoke(JRMPInvoker.java:406)
        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:585)
        at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:294)
        at sun.rmi.transport.Transport$1.run(Transport.java:153)
        at java.security.AccessController.doPrivileged(Native Method)
        at sun.rmi.transport.Transport.serviceCall(Transport.java:149)
        at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:466)
        at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:707)
        at java.lang.Thread.run(Thread.java:595)
        at sun.rmi.transport.StreamRemoteCall.exceptionReceivedFromServer(StreamRemoteCall.java:247)
        at sun.rmi.transport.StreamRemoteCall.executeCall(StreamRemoteCall.java:223)
        at sun.rmi.server.UnicastRef.invoke(UnicastRef.java:126)
        at org.jboss.invocation.jrmp.server.JRMPInvoker_Stub.invoke(Unknown Source)
        at org.jboss.invocation.jrmp.interfaces.JRMPInvokerProxy.invoke(JRMPInvokerProxy.java:119)
        at org.jboss.invocation.InvokerInterceptor.invokeInvoker(InvokerInterceptor.java:227)
        at org.jboss.invocation.MarshallingInvokerInterceptor.invoke(MarshallingInvokerInterceptor.java:37)
        at org.jboss.proxy.TransactionInterceptor.invoke(TransactionInterceptor.java:46)
        at org.jboss.proxy.SecurityInterceptor.invoke(SecurityInterceptor.java:55)
        at org.jboss.proxy.ejb.EntityInterceptor.invoke(EntityInterceptor.java:97)
        at org.jboss.proxy.ClientContainer.invoke(ClientContainer.java:86)
        at $Proxy3.getAppControls(Unknown Source)
        at com.osframework.appclient.services.SecurityServices.getAppControls(SecurityServices.java:176)
        at com.osframework.appclient.services.SecurityServices.getAppControls(SecurityServices.java:158)
        at com.osrmt.appclient.system.LoginController.start(LoginController.java:62)
        at com.osrmt.appclient.reqmanager.RequirementManagerController.start(RequirementManagerController.java:101)
        at com.osrmt.appclient.reqmanager.RequirementManagerController.main(RequirementManagerController.java:89)
    Caused by: com.osframework.datalibrary.common.DataAccessException: java.sql.SQLException: No suitable driver
        at com.osframework.datalibrary.reference.security.ApplicationControlDbAdapter.getApplicationControl(ApplicationControlDbAdapter.java:369)
        at com.osframework.datalibrary.reference.security.ApplicationControlDbAdapter.getApplicationControl(ApplicationControlDbAdapter.java:341)
        at com.osframework.datalibrary.reference.security.ApplicationControlDataAdapter.getControlsByApplication(ApplicationControlDataAdapter.java:52)
        at com.osframework.ejb.reference.security.SecurityBean.getAppControls(SecurityBean.java:408)
        at com.osframework.ejb.reference.security.SecurityBean.getAppControls(SecurityBean.java:396)
        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:585)
        at org.jboss.invocation.Invocation.performCall(Invocation.java:345)
        at org.jboss.ejb.EntityContainer$ContainerInterceptor.invoke(EntityContainer.java:1174)
        at org.jboss.ejb.plugins.EntitySynchronizationInterceptor.invoke(EntitySynchronizationInterceptor.java:270)
        at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:149)
        at org.jboss.ejb.plugins.EntityReentranceInterceptor.invoke(EntityReentranceInterceptor.java:111)
        at org.jboss.ejb.plugins.EntityInstanceInterceptor.invoke(EntityInstanceInterceptor.java:261)
        at org.jboss.ejb.plugins.EntityLockInterceptor.invoke(EntityLockInterceptor.java:89)
        at org.jboss.ejb.plugins.EntityCreationInterceptor.invoke(EntityCreationInterceptor.java:53)
        at org.jboss.ejb.plugins.CallValidationInterceptor.invoke(CallValidationInterceptor.java:48)
        at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:106)
        at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:335)
        at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:166)
        at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:153)
        at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:192)
        at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:122)
        at org.jboss.ejb.EntityContainer.internalInvoke(EntityContainer.java:514)
        at org.jboss.ejb.Container.invoke(Container.java:873)
        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: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:245)
        at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644)
        at org.jboss.invocation.jrmp.server.JRMPInvoker$MBeanServerAction.invoke(JRMPInvoker.java:805)
        at org.jboss.invocation.jrmp.server.JRMPInvoker.invoke(JRMPInvoker.java:406)
        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:585)
        at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:294)
        at sun.rmi.transport.Transport$1.run(Transport.java:153)
        at java.security.AccessController.doPrivileged(Native Method)
        at sun.rmi.transport.Transport.serviceCall(Transport.java:149)
        at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:466)
        at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:707)
        at java.lang.Thread.run(Thread.java:595)
    Caused by: java.sql.SQLException: No suitable driver
        at java.sql.DriverManager.getConnection(DriverManager.java:545)
        at java.sql.DriverManager.getConnection(DriverManager.java:171)
        at com.osframework.datalibrary.common.Access.getConnection(Access.java:333)
        at com.osframework.datalibrary.common.DbConnection.getConnection(DbConnection.java:18)
        at com.osframework.datalibrary.common.Access.reopenConnection(Access.java:28)
        at com.osframework.datalibrary.common.Access.executeQuery(Access.java:64)
        at com.osframework.datalibrary.reference.security.ApplicationControlDbAdapter.getApplicationControl(ApplicationControlDbAdapter.java:358)
        ... 47 more

    <------------

    mysql-connector-java-3.1.11-bin.jar is in both ./server/jboss-4.0.3/server/default/lib and ./server/jboss-4.0.3/lib/ directories, but they can't be picked up.

    I even tried to add a mysql-ds.xml file under server/jboss-4.0.3/server/default/deploy directory, but still I get this 'No suitable driver' error.

    Can anyone post a detailed and correct procedure for 3 tier client (./run3tier.sh) connecting OSRMT with MySQL on the same machine? The manual doesn't mention any configuration related to JBOSS if you don't use the default Access database.

    By the way, if I run the 3 tier client from another machine which has a firewall between these two machines, I get connect refused error even though I have 1099, 1098, 8080 ports open on the firewall.

    Thank you very much for your help.

    Regards,

    Kay

     
    • Armin
      Armin
      2009-08-14

      I have also got an MySql conenction Problem with the 2 tier OSRMT Application:

      ---
      java.lang.ClassNotFoundException: sun.jdbc.odbc.JdbcOdbcDriver
          at java.net.URLClassLoader$1.run(URLClassLoader.java:200)
          at java.security.AccessController.doPrivileged(Native Method)
          at java.net.URLClassLoader.findClass(URLClassLoader.java:188)
          at java.lang.ClassLoader.loadClass(ClassLoader.java:316)
          at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:280)
          at java.lang.ClassLoader.loadClass(ClassLoader.java:251)
          at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:374)
          at java.lang.Class.forName0(Native Method)
          at java.lang.Class.forName(Class.java:164)
          at com.osframework.datalibrary.common.Access.connect(Access.java:348)
          at com.osframework.datalibrary.common.Db.getAccess(Db.java:121)
          at com.osframework.datalibrary.common.Db.countTable(Db.java:88)
          at com.osframework.ejb.reference.common.ReferenceMapBean.<init>(ReferenceMapBean.java:63)
          at com.osframework.ejb.reference.common.ReferenceMapBean.get2TierInstance(ReferenceMapBean.java:75)
          at com.osframework.ejb.reference.common.ReferenceMapUtil.getReferenceMap(ReferenceMapUtil.java:70)
          at com.osframework.appclient.services.ReferenceServices.getReference(ReferenceServices.java:230)
      [...]
      ---

      MySQL Server is running on localhost. User osrmt database, user and password exist with the necessary grants.

      Hope somebode can help me. Thanks al lot in advance!

      With best regards,

      AP

       
      • Mike Crowe
        Mike Crowe
        2009-08-14

        Can you post your connection.xml?

         
        • Armin
          Armin
          2009-08-14

          ?xml version="1.0" encoding="UTF-8"?>
          <java version="1.5.0_04" class="java.beans.XMLDecoder">
          <object class="com.osframework.datalibrary.common.ConnectionProperty">
            <void property="accessSequence">
             <int>0</int>
            </void>
            <void property="active">
             <boolean>true</boolean>
            </void>
            <void property="connectionType">
             <string>Jdbc</string>
            </void>
            <void property="connectToURL">
             <boolean>false</boolean>
            </void>
            <void property="driverClass">
             <string>sun.jdbc.odbc.JdbcOdbcDriver</string>
            </void>
            <void property="environment">
             <string>access</string>
            </void>
            <void property="url">
             <string>jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb)};DBQ=osrmt.mdb</string>
            </void>
            <void property="unicodeConnection">
             <boolean>false</boolean>
            </void>
            <void property="username">
             <string>admin</string>
            </void>
            <void property="password">
             <string></string>
            </void>
          </object>
          </java>

           
          • Mike Crowe
            Mike Crowe
            2009-08-14

            In the client directory, copy connection.mysql.xml to connection.xml.  Then, edit connection.xml with the username/password/database information.

            Make sure you initialize the structure of the database in client/schema/mysql*. 

            Note:  When you configure the view for the first time, you must remove the DROP statements or your SQL won't work.

             
            • Armin
              Armin
              2009-08-14

              My new connection.xml:

              ---
              <?xml version="1.0" encoding="UTF-8"?>

              <java version="1.5.0_04" class="java.beans.XMLDecoder">

              <object class="com.osframework.datalibrary.common.ConnectionProperty">

                <void property="accessSequence">

                 <int>0</int>

                </void>

                <void property="active">

                 <boolean>true</boolean>

                </void>

                <void property="connectionType">

                 <string>Jdbc</string>

                </void>

                <void property="connectToURL">

                 <boolean>false</boolean>

                </void>

                <void property="driverClass">

                 <string>com.mysql.jdbc.Driver</string>

                </void>

                <void property="environment">

                 <string>mysql</string>

                </void>

                <void property="url">

                 <string>jdbc:mysql://localhost/osrmt</string>

                </void>

                <void property="unicodeConnection">

                 <boolean>true</boolean>

                </void>

                <void property="username">

                 <string>osrmt</string>

                </void>

                <void property="password">

                 <string>osrmt</string>

                </void>

              </object>

              </java>
              ---

              Database schema and views for mysql are created sucessfully.

              But when loggin on to OSRMT with username 'osrmt' and password 'osrmt'  I get a 'Invalid username  'osrmt' ' message.

              Once more thanks a lot for your help in advance!

              With best regards,
              AP

               
              • Mike Crowe
                Mike Crowe
                2009-08-14

                Have you created that user in MySQL?  At the end of osrmt_installation.pdf (in doc/), it gives you a good walkthough of how to configure the database.

                 
  • KH Huang
    KH Huang
    2009-10-29

    Mike

    I wonder if you can help me with a mysql server connection problem. It seems I can connect to the mysql server using mysql administrator (via either localhost or remote), but every time I ran the osrmt client I get the same error:

    &quot;com.osframework.datalibrary.common.DataAccessException: com.osframework.datalibrary.common.DataAccessException: java.sql.SQLException: Cannot connect to MySQL server on localhost:3306. Is there a MySQL server running on the machine/port you are trying to connect to?&quot;

    Here's the content of my connection.xml:

    ?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt;

    java version=&quot;1.5.0_04&quot; class=&quot;java.beans.XMLDecoder&quot;&gt;

    object class=&quot;com.osframework.datalibrary.common.ConnectionProperty&quot;&gt;

      void property=&quot;accessSequence&quot;&gt;

       int&gt;0&lt;/int&gt;

      /void&gt;

      void property=&quot;active&quot;&gt;

       boolean&gt;true/boolean&gt;

      /void&gt;

      void property=&quot;connectionType&quot;&gt;

       string&gt;Jdbc/string&gt;

      /void&gt;

      void property=&quot;connectToURL&quot;&gt;

       boolean&gt;false/boolean&gt;

      /void&gt;

      void property=&quot;driverClass&quot;&gt;

       string&gt;com.mysql.jdbc.Driver/string&gt;

      /void&gt;

      void property=&quot;environment&quot;&gt;

       string&gt;mysql/string&gt;

      /void&gt;

      void property=&quot;url&quot;&gt;

       string&gt;jdbc:mysql://localhost/osrmt/string&gt;

      /void&gt;

      void property=&quot;unicodeConnection&quot;&gt;

       boolean&gt;true/boolean&gt;

      /void&gt;

      void property=&quot;username&quot;&gt;

       string&gt;osrmt/string&gt;

      /void&gt;

      void property=&quot;password&quot;&gt;

       string&gt;osrmt/string&gt;

      /void&gt;

    /object&gt;

    /java&gt;

    _

    From reading some threads I made sure I use the same jdbc connector version (mysql-connector-java-3.1.11-bin.jar) in both osrmt client run.bat and jre, but that didn't make any difference.

    Any clue what is going on?

    Thanks,
    kh_