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

    Problem connecting to MySql Data Source through ssh tunnel

    ben.wright
    ben.wright Nov 18, 2009 12:11 PM

      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

      • 3065 Views
      • Tags: