resouce config problem

[Jim Babcock]

Hello,

I have two jdbc resources that I'm trying to run in a connection pool. The local on works and the second one doesn't. I don't know what the issue is... Can anyone spot anything?

In context.xml I have:

  
<!-- The contents of this file will be loaded for each web application -->
<Context>
 
    <!-- Default set of monitored resources -->
    <WatchedResource>WEB-INF/web.xml</WatchedResource>
 
    <!-- Uncomment this to disable session persistence across Tomcat restarts -->
    <!--
    <Manager pathname="" />
    -->
 
  <Resource name="jdbc/apps" auth="Container" type="javax.sql.DataSource"
             maxActive="100" maxIdle="30" maxWait="10000"
             username="xxxxxx" password="xxxxxxx" driverClassName="com.mysql.jdbc.Driver"
             url="jdbc:mysql://localhost:3306/beta?autoReconnect=true"/>
 
  <Resource name="jdbc/reports" auth="Container" type="javax.sql.DataSource"
             maxActive="100" maxIdle="30" maxWait="10000"
             username="xxxxxx" password="xxxxx" driverClassName="com.mysql.jdbc.Driver"
             url="jdbc:mysql://10.32.1.38/reports?autoReconnect=true"/>
 
</Context>

In webapp/ROOT/WEB-INF/web.xml I have:

 
  <resource-ref>
      <description>Local utilitiy DB Connection</description>
      <res-ref-name>jdbc/apps</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
  </resource-ref>
 
  <resource-ref>
      <description>Reports DB Connection</description>
      <res-ref-name>jdbc/reports</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
  </resource-ref>

I'm calling it thusly in my jsp file:

ResultSet dayRS = null;
String sqlDay = "select date_format(tstamp,'%Y%m') d,date_format(tstamp,'%M %Y') num, round(avg(total),1),max(total),sum(op100),sum(op700),sum(total) from wsb_counts group by num order by d";
 
InitialContext initContext = new InitialContext();
DataSource ds  = (DataSource)initContext.lookup("java:/comp/env/jdbc/reports");
Connection dbConnection = ds.getConnection();
Statement stmt = dbConnection.createStatement();
dayRS = stmt.executeQuery(sqlDay);

I'd been using beans to make my SQL connections and the reports server was successfully connecting with the following bean code:

 private String dbURL =
       "jdbc:mysql://10.32.16.38/reports?user=xxxxxx&password=xxxxxx";
 private String dbDriver = "com.mysql.jdbc.Driver";
 private Connection dbConn;
 Statement dbStmt;
 ResultSet RS = null;
 
 public ResultSet sqlExec(String inStr)
 {
   System.out.println("jimbus");
   ResultSet RS = null;
   try
   {
     Class.forName(dbDriver);
     dbConn = DriverManager.getConnection (dbURL);
     dbStmt = dbConn.createStatement();

When I execuite the page I get this error:

HTTP Status 500 - 
 
--------------------------------------------------------------------------------
 
type Exception report
 
message 
 
description The server encountered an internal error () that prevented it from fulfilling this request.
 
exception 
 
javax.servlet.ServletException: Cannot create PoolableConnectionFactory (Server connection failure during transaction. Due to underlying exception: 'java.net.ConnectException: Operation timed out'.
 
** BEGIN NESTED EXCEPTION ** 
 
java.net.ConnectException
MESSAGE: Operation timed out
 
STACKTRACE:
 
java.net.ConnectException: Operation timed out
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:305)
at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:171)
at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:158)
at java.net.Socket.connect(Socket.java:452)
at java.net.Socket.connect(Socket.java:402)
at java.net.Socket.<init>(Socket.java:309)
at java.net.Socket.<init>(Socket.java:124)
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:124)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:225)
at com.mysql.jdbc.Connection.createNewIO(Connection.java:1899)
at com.mysql.jdbc.Connection.<init>(Connection.java:450)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:411)
at org.apache.tomcat.dbcp.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:37)
at org.apache.tomcat.dbcp.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:290)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:877)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:851)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540)
at org.apache.jsp.wsb.index_jsp._jspService(org.apache.jsp.wsb.index_jsp:75)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:291)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:407)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:534)
 
 
** END NESTED EXCEPTION **
 
 
Attempted reconnect 3 times. Giving up.)
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:848)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:781)
org.apache.jsp.wsb.index_jsp._jspService(org.apache.jsp.wsb.index_jsp:154)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:291)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
 
 
root cause 
 
org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Server connection failure during transaction. Due to underlying exception: 'java.net.ConnectException: Operation timed out'.
 
** BEGIN NESTED EXCEPTION ** 
 
java.net.ConnectException
MESSAGE: Operation timed out
 
STACKTRACE:
 
java.net.ConnectException: Operation timed out
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:305)
at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:171)
at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:158)
at java.net.Socket.connect(Socket.java:452)
at java.net.Socket.connect(Socket.java:402)
at java.net.Socket.<init>(Socket.java:309)
at java.net.Socket.<init>(Socket.java:124)
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:124)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:225)
at com.mysql.jdbc.Connection.createNewIO(Connection.java:1899)
at com.mysql.jdbc.Connection.<init>(Connection.java:450)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:411)
at org.apache.tomcat.dbcp.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:37)
at org.apache.tomcat.dbcp.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:290)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:877)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:851)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540)
at org.apache.jsp.wsb.index_jsp._jspService(org.apache.jsp.wsb.index_jsp:75)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:291)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:407)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Thread.java:534)
 
 
** END NESTED EXCEPTION **
 
 
Attempted reconnect 3 times. Giving up.)
org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:855)
org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540)
org.apache.jsp.wsb.index_jsp._jspService(org.apache.jsp.wsb.index_jsp:75)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:291)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
 
 
note The full stack trace of the root cause is available in the Apache Tomcat/5.5.9 logs.
 
 
--------------------------------------------------------------------------------
 
Apache Tomcat/5.5.9

[ July 13, 2005: Message edited by: Bear Bibeault ]

[Bear Bibeault]

Dude, were those the real usernames and passwords in your post? I blanked them out just in case!

[craig a chapman]

You haven't specified the port number in the second url.

[Jim Babcock]

I had it there, but took it out because the bean code that does work, doesn't have it. *shrug*

[craig a chapman]

Does the remote database have connections via TCP/IP enabled? You can change this setting through MySQL administrator -> Startup Variables -> General Parameters. I think it's disabled by default.

[craig a chapman]

Scrap that last comment. I just remembered you have managed to connect with your bean.

[Roger Chung-Wee]

I don't know if this is the cause of your problem, but shouldn't your JDBC environment subcontext be:

DataSource ds  = (DataSource)initContext.lookup("[B]java:comp/env/jdbc/reports[/B]");

[Jim Babcock]

I tried it, but it didn't change things. I didn't think it would, becuase I'd fat fingered the IP and it complained and went back to this error when I fixed it.

[Jim Babcock]

Excuse me while I punch myself in the head... I had the IP number wrong all this time. Apparently the one I used is a valid machine, because as I said above, I'd fat fingered the last octet as 384 and it didn't work. The thing is we have a million and six IP ranges internally, and the 3rd octet should have been 16 not 1. I vi'ed and stared at content.xml one more time and it just jumped out at me.

*whump* *whump* *whump*

Jimbus