{"action":"closed","issue":{"url":"https://api.github.com/repos/brettwooldridge/HikariCP/issues/257","labels_url":"https://api.github.com/repos/brettwooldridge/HikariCP/issues/257/labels{/name}","comments_url":"https://api.github.com/repos/brettwooldridge/HikariCP/issues/257/comments","events_url":"https://api.github.com/repos/brettwooldridge/HikariCP/issues/257/events","html_url":"https://github.com/brettwooldridge/HikariCP/issues/257","id":57059459,"number":257,"title":"SQLTimeoutException at  BaseHikariPool.getConnection(BaseHikariPool.java:228)","user":{"login":"nucleartip","id":5635390,"avatar_url":"https://avatars.githubusercontent.com/u/5635390?v=3","gravatar_id":"","url":"https://api.github.com/users/nucleartip","html_url":"https://github.com/nucleartip","followers_url":"https://api.github.com/users/nucleartip/followers","following_url":"https://api.github.com/users/nucleartip/following{/other_user}","gists_url":"https://api.github.com/users/nucleartip/gists{/gist_id}","starred_url":"https://api.github.com/users/nucleartip/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/nucleartip/subscriptions","organizations_url":"https://api.github.com/users/nucleartip/orgs","repos_url":"https://api.github.com/users/nucleartip/repos","events_url":"https://api.github.com/users/nucleartip/events{/privacy}","received_events_url":"https://api.github.com/users/nucleartip/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":1,"created_at":"2015-02-09T17:08:58Z","updated_at":"2015-02-10T14:13:45Z","closed_at":"2015-02-10T14:13:45Z","body":"Hi,\r\n\r\nam little new to web programming, am developing a mobile app for which I needed a small scale rest enabled web app with mysql db support.\r\n\r\nafter reading lots of review, I went configuring HikariCP for managing connection pool database, however even after spending couple of days with it, am not yet able to make it work.\r\n\r\nThis is where am doing all my initialization.\r\n\r\n// Init servlet\r\n  \r\npublic class CPInit extends HttpServlet implements ServletContextListener{\r\n\r\n\t/**\r\n\t * \r\n\t */\r\n\tprivate static final long serialVersionUID = 1L;\r\n\t\r\n\tprivate final static String TAG = CPInit.class.getSimpleName();\r\n\tprivate static HikariConfig mConfig;\r\n\tprivate static HikariDataSource mDataSource;\r\n\r\n\tpublic static String POOL_NAME = \"MyPool\";\r\n\tpublic static int POOL_SIZE = 50;\r\n\tpublic static int TIME_OUT = 2*30*1000;\r\n\tpublic static int MIN_IDLE_SIZE = 5;\r\n\tpublic static boolean isDebuggable;\r\n\r\n\tprivate static String PASSWORD = \"PASSWORD\";\r\n\r\n\r\n\t@Override\r\n\tprotected void doGet(HttpServletRequest req, HttpServletResponse resp)\r\n\t\t\tthrows ServletException, IOException {\r\n\t\tsuper.doGet(req, resp);\r\n\t\tresp.getWriter()\r\n\t\t.write(\"Not supported\");\r\n\r\n\t}\r\n\r\n\tpublic CPInit(){\r\n\t\tinitPool();\r\n\t}\r\n\r\n\tpublic static void initPool(){\r\n\r\n\t\t\r\n\t\tTLog.info(TAG, \"Password = \" + string);\r\n\t\tmConfig = new HikariConfig();\r\n\t\tmConfig.setDataSourceClassName(\"com.mysql.jdbc.jdbc2.optional.MysqlDataSource\");\r\n\t\tmConfig.addDataSourceProperty(\"url\", \"jdbc:mysql://127.0.0.1:3306/mydb\");\r\n\t\tmConfig.addDataSourceProperty(\"cachePrepStmts\", \"true\");\r\n\t\tmConfig.addDataSourceProperty(\"prepStmtCacheSize\", \"250\");\r\n\t\tmConfig.addDataSourceProperty(\"prepStmtCacheSqlLimit\", \"2048\");\r\n\t\tmConfig.addDataSourceProperty(\"useServerPrepStmts\", \"true\");\r\n\t\tmConfig.addDataSourceProperty(\"user\", \"root\");\r\n\t\tmConfig.addDataSourceProperty(\"password\", PASSWORD);\r\n\t\tmConfig.setMaximumPoolSize(POOL_SIZE);\r\n\t\tmConfig.setMinimumIdle(MIN_IDLE_SIZE);\r\n\t\t\r\n\t\tmConfig.setPoolName(POOL_NAME);\r\n\t\tmConfig.setConnectionTimeout(TIME_OUT);\r\n\t\tmConfig.setAutoCommit(true);\r\n\t\tmConfig.setInitializationFailFast(true);\r\n\t\tmConfig.setConnectionTestQuery(\"show tables\");\r\n\t\tmDataSource = new HikariDataSource(mConfig);\r\n\t\r\n\t}\r\n\r\n\t@Override\r\n\tpublic void contextDestroyed(ServletContextEvent arg0) {\r\n\t\tTLog.info(TAG, \"CP has being destroyed\");\r\n\t\tmDataSource.close();\r\n\t\tmDataSource.shutdown();\r\n\r\n\t}\r\n\r\n\r\n\tpublic static Connection getConnection() throws SQLException{\r\n\t\tTLog.info(TAG, \"get data connection\");\r\n\r\n\r\n\r\n\t\tConnection mConnection =  mDataSource.getConnection();\r\n\r\n\t\tif(mConnection == null || mConnection.isClosed()){\r\n\t\t\tinitPool();\r\n\t\t\tmConnection =  mDataSource.getConnection();\r\n\t\t}\r\n\r\n\t\treturn mConnection;\r\n\r\n\t}\r\n\r\n\t@Override\r\n\tpublic void contextInitialized(ServletContextEvent arg0) {\r\n\t\tTLog.info(TAG, \"CP has been initilizing\");\r\n\t\tString parm = arg0.getServletContext().getInitParameter(\"debugging\");\r\n\t\tisDebuggable = Boolean.valueOf(parm);\r\n\t}\r\n\r\n}\r\n\r\n// This is how am taking connection, and closing it in a data manager class\r\n\r\nclass DataManager{\r\n\r\n\tprivate Connection getConnection(){\r\n\t\ttry{\r\n\t\t\treturn CPInit.getConnection();\r\n\t\t}catch(SQLException e){\r\n\t\t\tTLog.error(TAG, \"Connection is null\");\r\n\t\t\te.printStackTrace();\r\n\t\t\tTLog.error(TAG, e.getMessage());\r\n\t\t\tTLog.error(TAG, e.getMessage());\r\n\t\t\treturn null;\r\n\t\t}\r\n\t}\r\n\tpublic int getServiceId(String number) throws SQLException{\r\n\r\n\t\tConnection mConn = getConnection();\r\n\t\tif(mConn == null){\r\n\t\t\tthrow new SQLException(\"Connection cannot be Initialized\");\r\n\t\t}\r\n        TLog.info(TAG, \"Looking up service id for = \" + number);\r\n\t\tPreparedStatement selectStmt = mConn.prepareStatement(SELECT_DEVICE_BY_NUMBER_ONLY);\r\n\t\tselectStmt.setString(1, number);\r\n\t\tResultSet set  = selectStmt.executeQuery();\r\n\t\tif(set != null){\r\n\t\t\tif(set.next()){\r\n\t\t\t\tint serviceId = set.getInt(Devices._ID);\r\n\t\t\t\tset.close();\r\n\t\t\t\tselectStmt.close();\r\n\t\t\t\tif(mConn != null){\r\n\t\t\t\t\tmConn.close();\r\n\t\t\t\t}\r\n\t\t\t\tTLog.info(TAG, \"service id found for = \" + number + \", service id = \" + serviceId);\r\n\t\t\t\treturn serviceId;\r\n\t\t\t}\r\n\t\t\tset.close();\r\n\t\t}\r\n\t\tselectStmt.close();\r\n\t\tif(mConn != null){\r\n\t\t\tmConn.close();\r\n\t\t}\r\n\t\t\r\n\t\tTLog.info(TAG, \"service id not found for = \" + number);\r\n\t\treturn -1;\r\n\t}\r\n}\r\n\r\n\r\nNow when am running query in bulk, after first few queries this is all am getting from connection call.\r\n\r\n[INFO ] 2015-02-09 22:24:17.243 [http-nio-8080-exec-5] TLog - CPInit--get data connection\r\n[ERROR] 2015-02-09 22:25:17.245 [http-nio-8080-exec-5] TLog - [DatabaseManager] --Connection is null\r\njava.sql.SQLTimeoutException: Timeout after 60001ms of waiting for a connection.\r\n\tat com.zaxxer.hikari.pool.BaseHikariPool.getConnection(BaseHikariPool.java:228)\r\n\tat com.zaxxer.hikari.pool.BaseHikariPool.getConnection(BaseHikariPool.java:183)\r\n\tat com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:91)\r\n\tat com.mkgcorp.travelweb.controller.CPInit.getConnection(CPInit.java:90)\r\n\tat com.mkgcorp.travelweb.data.Database.getConnection(Database.java:18)\r\n\tat com.mkgcorp.travelweb.data.DataManager.getConnection(DataManager.java:100)\r\n\tat com.mkgcorp.travelweb.data.DataManager.getServiceId(DataManager.java:200)\r\n\tat com.mkgcorp.travelweb.webservices.SyncResource.syncContacts(SyncResource.java:146)\r\n\tat sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\r\n\tat sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)\r\n\tat sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)\r\n\tat java.lang.reflect.Method.invoke(Unknown Source)\r\n\tat com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60)\r\n\tat com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$ResponseOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:205)\r\n\tat com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75)\r\n\tat com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302)\r\n\tat com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108)\r\n\tat com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)\r\n\tat com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84)\r\n\tat com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1542)\r\n\tat com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1473)\r\n\tat com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1419)\r\n\tat com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1409)\r\n\tat com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:409)\r\n\tat com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:540)\r\n\tat com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:715)\r\n\tat javax.servlet.http.HttpServlet.service(HttpServlet.java:725)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)\r\n\tat org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)\r\n\tat org.apache.logging.log4j.web.Log4jServletFilter.doFilter(Log4jServletFilter.java:71)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)\r\n\tat org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)\r\n\tat org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)\r\n\tat org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:501)\r\n\tat org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142)\r\n\tat org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)\r\n\tat org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:610)\r\n\tat org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)\r\n\tat org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:537)\r\n\tat org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1085)\r\n\tat org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:658)\r\n\tat org.apache.coyote.http11.Http11NioProtocol$Http11ConnectionHandler.process(Http11NioProtocol.java:222)\r\n\tat org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1556)\r\n\tat org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1513)\r\n\tat java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)\r\n\tat java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)\r\n\tat org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)\r\n\tat java.lang.Thread.run(Unknown Source)\r\n\r\n\r\n"}}