MySQL problem with multiple instances of JBOSS
unmesh_kulkarni Jan 31, 2011 7:43 AMI 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?
-
mysql-ds2.xml 1.5 KB
-
mysql-ds1.xml 1.5 KB