<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<body link="#355491" alink="#4262a1" vlink="#355491" style="background: #e2e2e2; margin: 0; padding: 20px;">
<div>
<table cellpadding="0" bgcolor="#FFFFFF" border="0" cellspacing="0" style="border: 1px solid #dadada; margin-bottom: 30px; width: 100%; -moz-border-radius: 6px; -webkit-border-radius: 6px;">
<tbody>
<tr>
<td>
<table border="0" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF" style="border: solid 2px #ccc; background: #dadada; width: 100%; -moz-border-radius: 6px; -webkit-border-radius: 6px;">
<tbody>
<tr>
<td bgcolor="#000000" valign="middle" height="58px" style="border-bottom: 1px solid #ccc; padding: 20px; -moz-border-radius-topleft: 3px; -moz-border-radius-topright: 3px; -webkit-border-top-right-radius: 5px; -webkit-border-top-left-radius: 5px;">
<h1 style="color: #333333; font: bold 22px Arial, Helvetica, sans-serif; margin: 0; display: block !important;">
<!-- To have a header image/logo replace the name below with your img tag -->
<!-- Email clients will render the images when the message is read so any image -->
<!-- must be made available on a public server, so that all recipients can load the image. -->
<a href="http://community.jboss.org/index.jspa" style="text-decoration: none; color: #E1E1E1">JBoss Community</a></h1>
</td>
</tr>
<tr>
<td bgcolor="#FFFFFF" style="font: normal 12px Arial, Helvetica, sans-serif; color:#333333; padding: 20px; -moz-border-radius-bottomleft: 4px; -moz-border-radius-bottomright: 4px; -webkit-border-bottom-right-radius: 5px; -webkit-border-bottom-left-radius: 5px;"><h3 style="margin: 10px 0 5px; font-size: 17px; font-weight: normal;">
MySQL problem with multiple instances of JBOSS
</h3>
<span style="margin-bottom: 10px;">
created by <a href="http://community.jboss.org/people/unmesh_kulkarni">Unmesh Kulkarni</a> in <i>Datasource Configuration</i> - <a href="http://community.jboss.org/message/584318#584318">View the full discussion</a>
</span>
<hr style="margin: 20px 0; border: none; background-color: #dadada; height: 1px;">
<div class="jive-rendered-content"><p>I have setup two IPs on my machine.</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>eth0      Link encap:Ethernet  HWaddr 00:0C:29:B3:6B:25</p><p>          inet addr:192.168.6.128  Bcast:192.168.6.255  Mask:255.255.255.0</p><p>          inet6 addr: fe80::20c:29ff:feb3:6b25/64 Scope:Link</p><p>          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1</p><p>          RX packets:921323 errors:0 dropped:0 overruns:0 frame:0</p><p>          TX packets:127221 errors:0 dropped:0 overruns:0 carrier:0</p><p>          collisions:0 txqueuelen:1000</p><p>          RX bytes:721968842 (688.5 MiB)  TX bytes:12898679 (12.3 MiB)</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>eth0:1    Link encap:Ethernet  HWaddr 00:0C:29:B3:6B:25</p><p>          inet addr:192.168.6.129  Bcast:192.168.6.255  Mask:255.255.255.0</p><p>          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>I am starting up two instances of JBOSS AS 6.0.0 Final on the same server using -b option.</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>/etc/jboss-as/testenv1/bin/run.sh -c testenv1 -b 192.168.6.128</p><p>/etc/jboss-as/testenv2/bin/run.sh -c testenv2 -b 192.168.6.129</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>I have two databases, <strong>testenv1</strong> & <strong>testenv2</strong> on MySQL Server installed on same machine.</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>+--------------------+</p><p>| Database           |</p><p>+--------------------+</p><p>| information_schema |</p><p>| mysql              |</p><p>| test               |</p><p>| testenv1           |</p><p>| testenv2           |</p><p>+--------------------+</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>Also I have two dedicated users testenv1 and testenv2 for connecting those databases.</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>+----------+---------------+</p><p>| user     | host          |</p><p>+----------+---------------+</p><p>| admin    | %             |</p><p>| root     | 127.0.0.1     |</p><p>| <strong>testenv1 | 192.168.6.128 |</strong></p><p>| <strong>testenv2 | 192.168.6.129 |</strong></p><p>|          | JBOSS-SS      |</p><p>| root     | JBOSS-SS      |</p><p>|          | localhost     |</p><p>| root     | localhost     |</p><p>+----------+---------------+</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>They have been setup such a way that testenv1 user will have access to tesnv1 database from 192.168.6.128 and</p><p>testenv2 user will have access to testenv2 databse from 192.168.6.129.</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>I have attached datasource files which I am using.</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>Here is a code which i have deployed on both the servers for testing JDBC connectivity</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p> <%</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>  DataSource ds = null;</p><p>  Connection con = null; </p><p>  PreparedStatement pr = null; </p><p>  InitialContext ic; </p><p>  try {</p><p>  ic = new InitialContext();</p><p>  ds = (DataSource)ic.lookup( "java:/MySqlDS" );</p><p>  con = ds.getConnection(); </p><p>  pr = con.prepareStatement("SELECT * FROM jdbctest");</p><p>  ResultSet rs = pr.executeQuery();</p><p>  while (rs.next()) {</p><p>  out.println("<br> " +rs.getString("comp_inst_status")); </p><p>  }</p><p>  rs.close();</p><p>  pr.close();</p><p>  }catch(Exception e){</p><p>  out.println("Exception thrown " +e); </p><p>  }finally{</p><p>  if(con != null){</p><p>  con.close();</p><p> }      </p><p>} %> </p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>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</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p><strong>Caused by: java.sql.SQLException: Access denied for user 'testenv2'@'</strong><strong>192.168.6.128</strong><strong>' (using password: YES)</strong></p><p>        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073) [:]</p><p>        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593) [:]</p><p>        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525) [:]</p><p>        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:931) [:]</p><p>        at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:4031) [:]</p><p>        at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1296) [:]</p><p>        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2338) [:]</p><p>        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2371) [:]</p><p>        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2163) [:]</p><p>        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:794) [:]</p><p>        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47) [:]</p><p>        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) [:1.6.0_23]</p><p>        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) [:1.6.0_23]</p><p>        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) [:1.6.0_23]</p><p>        at java.lang.reflect.Constructor.newInstance(Constructor.java:513) [:1.6.0_23]</p><p>        at com.mysql.jdbc.Util.handleNewInstance(Util.java:407) [:]</p><p>        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:378) [:]</p><p>        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:305) [:]</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>As the user testenv2 is not having access rights from <strong>192.168.6.128 </strong>it throws exception.</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>But I have bound testenv2 instance on 192.168.6.129 but still it tries to access MySQL from 192.168.6.128.</p><p style="min-height: 8pt; height: 8pt; padding: 0px;"> </p><p>Any idea if I am missing anything in configuration?</p></div>
<div style="background-color: #f4f4f4; padding: 10px; margin-top: 20px;">
<p style="margin: 0;">Reply to this message by <a href="http://community.jboss.org/message/584318#584318">going to Community</a></p>
<p style="margin: 0;">Start a new discussion in Datasource Configuration at <a href="http://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2077">Community</a></p>
</div></td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>
</div>
</body>
</html>