1 Reply Latest reply on Nov 19, 2009 3:49 AM by Vicky Kak

    Problem connecting to MySql Data Source through ssh tunnel

    Ben Wright Newbie

      I'm trying to connect to a MySQL database that is not directly accessible as a datasource. Specifically I want to ingest data from a public mysql database onto a private network using JBoss ESB.

      I've got the JBoss ESB parts all squared away, the part I can't seem to get working is the datasource.

      I setup a very controlled test to see if there is some issue with the way jboss is connecting to the database and perhaps I just don't understand what's happening under the hood, but here's what I'm observing:

      Computer A & B are both on a private network.
      Computer A is running JBoss AS 5.1 and MySQL on port 3306
      Computer B is running MySQL on port 3306

      JBoss can be configured to use Computer B's MySQL as a datasource via:

      <datasources>
      
       <local-tx-datasource>
      
       <jndi-name>DirectDS</jndi-name>
      
       <connection-url>jdbc:mysql://ComputerB:3306/database</connection-url>
       <driver-class>com.mysql.jdbc.Driver</driver-class>
       <user-name>username</user-name>
       <password>password</password>
      
       <min-pool-size>5</min-pool-size>
       <max-pool-size>20</max-pool-size>
       <idle-timeout-minutes>5</idle-timeout-minutes>
       <exception-sorter-class-name>
       com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter
       </exception-sorter-class-name>
       <valid-connection-checker-class-name>
       com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker
       </valid-connection-checker-class-name>
      
       </local-tx-datasource>
      
      </datasources>


      With this configuration everything works great.

      Now, I setup an SSH tunnel mapping port 13306 on Computer A to port 3306 on Computer B

      Computer B console:
      ssh -R 13306/localhost/3306 ComputerA


      I verify that this tunnel works by connecting from Computer A with command

      Computer A console:
      mysql --port=13306 --database=dbname --host=127.0.0.1 --user=username --password


      and checking the mysql version (A runs 4.x.x, B runs 5.x.x)

      Knowing that the tunnel is active, I configure the following Data Source:
      <datasources>
      
       <local-tx-datasource>
      
       <jndi-name>TunnelDS</jndi-name>
      
       <connection-url>jdbc:mysql://127.0.0.1:13306/database</connection-url>
       <driver-class>com.mysql.jdbc.Driver</driver-class>
       <user-name>username</user-name>
       <password>password</password>
      
       <min-pool-size>5</min-pool-size>
       <max-pool-size>20</max-pool-size>
       <idle-timeout-minutes>5</idle-timeout-minutes>
       <exception-sorter-class-name>
       com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter
       </exception-sorter-class-name>
       <valid-connection-checker-class-name>
       com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker
       </valid-connection-checker-class-name>
      
       </local-tx-datasource>
      
      </datasources>


      Binding of this datasource does not result in any visible errors, but also does not work! Attempting to use the datasource results in:
      Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown database 'database'

      The best I can figure is that JBoss AS 5.1 or MySQL Connector/J (5.1.9) is ignoring the port in the JDBC URL in the datasource configuration:
      jdbc:mysql://127.0.0.1:13306/database

      and connecting to the MySQL on Computer A port 3306 instead of the one mapped from Computer B onto port 13306.

      Am I crazy?? Do I need to implement my own "com.mysql.jdbc.SocketFactory" (see http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configuration-properties.html) to workaround this problem???

      Any help would be much appreciated.

      -Benjamin Wright