5 Replies Latest reply on Feb 3, 2011 2:02 AM by unmesh_kulkarni

    MySQL problem with multiple instances of JBOSS

    unmesh_kulkarni

      I have setup two IPs on my machine.

       

      eth0      Link encap:Ethernet  HWaddr 00:0C:29:B3:6B:25

                inet addr:192.168.6.128  Bcast:192.168.6.255  Mask:255.255.255.0

                inet6 addr: fe80::20c:29ff:feb3:6b25/64 Scope:Link

                UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

                RX packets:921323 errors:0 dropped:0 overruns:0 frame:0

                TX packets:127221 errors:0 dropped:0 overruns:0 carrier:0

                collisions:0 txqueuelen:1000

                RX bytes:721968842 (688.5 MiB)  TX bytes:12898679 (12.3 MiB)

       

      eth0:1    Link encap:Ethernet  HWaddr 00:0C:29:B3:6B:25

                inet addr:192.168.6.129  Bcast:192.168.6.255  Mask:255.255.255.0

                UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

       

      I am starting up two instances of JBOSS AS 6.0.0 Final on the same server using -b option.

       

      /etc/jboss-as/testenv1/bin/run.sh -c testenv1 -b 192.168.6.128

      /etc/jboss-as/testenv2/bin/run.sh -c testenv2 -b 192.168.6.129

       

      I have two databases, testenv1 & testenv2 on MySQL Server installed on same machine.

       

      +--------------------+

      | Database           |

      +--------------------+

      | information_schema |

      | mysql              |

      | test               |

      | testenv1           |

      | testenv2           |

      +--------------------+

       

      Also I have two dedicated users testenv1 and testenv2 for connecting those databases.

       

      +----------+---------------+

      | user     | host          |

      +----------+---------------+

      | admin    | %             |

      | root     | 127.0.0.1     |

      | testenv1 | 192.168.6.128 |

      | testenv2 | 192.168.6.129 |

      |          | JBOSS-SS      |

      | root     | JBOSS-SS      |

      |          | localhost     |

      | root     | localhost     |

      +----------+---------------+

       

       

      They have been setup such a way that testenv1 user will have access to tesnv1 database from 192.168.6.128 and

      testenv2 user will have access to testenv2 databse from 192.168.6.129.

       

      I have attached datasource files which I am using.

       

      Here is a code which i have deployed on both the servers for testing JDBC connectivity

       

      <%

       

        DataSource ds = null;

        Connection con = null;

        PreparedStatement pr = null;

        InitialContext ic;

        try {

        ic = new InitialContext();

        ds = (DataSource)ic.lookup( "java:/MySqlDS" );

        con = ds.getConnection();

        pr = con.prepareStatement("SELECT * FROM jdbctest");

        ResultSet rs = pr.executeQuery();

        while (rs.next()) {

        out.println("<br> " +rs.getString("comp_inst_status"));

        }

        rs.close();

        pr.close();

        }catch(Exception e){

        out.println("Exception thrown " +e);

        }finally{

        if(con != null){

        con.close();

      }     

      } %>

       

       

      When I access my JSP from first instance (i.e. 192.168.6.128 ) it works fine but when I access it from other server it fails. It gives following exception

       

       

      Caused by: java.sql.SQLException: Access denied for user 'testenv2'@'192.168.6.128' (using password: YES)

              at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073) [:]

              at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593) [:]

              at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525) [:]

              at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:931) [:]

              at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:4031) [:]

              at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1296) [:]

              at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2338) [:]

              at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2371) [:]

              at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2163) [:]

              at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:794) [:]

              at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47) [:]

              at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) [:1.6.0_23]

              at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) [:1.6.0_23]

              at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) [:1.6.0_23]

              at java.lang.reflect.Constructor.newInstance(Constructor.java:513) [:1.6.0_23]

              at com.mysql.jdbc.Util.handleNewInstance(Util.java:407) [:]

              at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:378) [:]

              at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:305) [:]

       

       

      As the user testenv2 is not having access rights from 192.168.6.128 it throws exception.

       

      But I have bound testenv2 instance on 192.168.6.129 but still it tries to access MySQL from 192.168.6.128.

       

      Any idea if I am missing anything in configuration?

        • 1. MySQL problem with multiple instances of JBOSS
          wdfink

          As I can see ds1 and ds2 connect the same IP but the different user.

          Is it a simple typo?

          • 2. MySQL problem with multiple instances of JBOSS
            unmesh_kulkarni

            Hi Wolf-Dieter Fink,

             

            Thanks for your reply.

             

            No... Its not typo.

             

            I am starting MySQL Server on 192.168.6.128 on port 3306.

             

            There are two databases testenv1 & testenv2 on MySQL server.

             

            testenv1 user is having all rights on testenv1 database

            testenv2 user is having all rights on testenv2 database

             

            One thing I noticed.......

             

            I removed MySQL server binding on 192.168.6.128 ( so that it will get bound on 0.0.0.0 by default ) and changed connection-url jdbc:mysql://192.168.6.129:3306/testenv2

             

            After that connection was successful.

             

            Not very sure why it fails if I bind MySQL server on one IP

            • 3. MySQL problem with multiple instances of JBOSS
              unmesh_kulkarni

              One strange thing is happening.

               

              If I keep URL jdbc:mysql://192.168.6.128:3306/testenv1 in ds1.xml then connection is successful but if I change it to

              jdbc:mysql://192.168.6.129:3306/testenv1 then connection is failing.

               

              Same way,

               

              If I keep URL jdbc:mysql://192.168.6.129:3306/testenv2 in ds2.xml then connection is successful but if I change it to

              jdbc:mysql://192.168.6.128:3306/testenv2 then connection is failing.

               

              So looks like MySQL is available on both the IPs (192.168.6.128 & 192.168.6.129) but if I make connection from 

              JDBC client running on one IP to MySQL server available on same server then only connection is possible

               

              In simple words

               

              128 (JDBC client ) to 128 ( MySQL Server) ==== > Pass

              129 (JDBC client ) to 129 ( MySQL Server) ==== > Pass

               

              128 (JDBC client ) to 129 ( MySQL Server) ==== > Fail

              129 (JDBC client ) to 128 ( MySQL Server) ==== > Fail

               

              Has anyone noticed something similar to this?

              • 4. MySQL problem with multiple instances of JBOSS
                jaikiran

                What does the following command:

                 

                telnet 192.168.6.128 3306

                 

                from 192.168.6.129 system give?

                • 5. MySQL problem with multiple instances of JBOSS
                  unmesh_kulkarni

                  Hi Jaikiran,

                   

                  Thanks for your reply....

                  Here is the output from terminal 192.168.6.129

                   

                  [root@JBOSS-SS ~]# telnet 192.168.6.128 3306

                  Trying 192.168.6.128...

                  Connected to 192.168.6.128.

                  Escape character is '^]'.

                  8

                  5.1.47-lo+{KCPI\Qw%N<!/iCQOyConnection closed by foreign host.