{"action":"created","issue":{"url":"https://api.github.com/repos/brettwooldridge/HikariCP/issues/289","labels_url":"https://api.github.com/repos/brettwooldridge/HikariCP/issues/289/labels{/name}","comments_url":"https://api.github.com/repos/brettwooldridge/HikariCP/issues/289/comments","events_url":"https://api.github.com/repos/brettwooldridge/HikariCP/issues/289/events","html_url":"https://github.com/brettwooldridge/HikariCP/issues/289","id":63946486,"number":289,"title":"Idle connections not getting closed","user":{"login":"mpoornima","id":1185549,"avatar_url":"https://avatars.githubusercontent.com/u/1185549?v=3","gravatar_id":"","url":"https://api.github.com/users/mpoornima","html_url":"https://github.com/mpoornima","followers_url":"https://api.github.com/users/mpoornima/followers","following_url":"https://api.github.com/users/mpoornima/following{/other_user}","gists_url":"https://api.github.com/users/mpoornima/gists{/gist_id}","starred_url":"https://api.github.com/users/mpoornima/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mpoornima/subscriptions","organizations_url":"https://api.github.com/users/mpoornima/orgs","repos_url":"https://api.github.com/users/mpoornima/repos","events_url":"https://api.github.com/users/mpoornima/events{/privacy}","received_events_url":"https://api.github.com/users/mpoornima/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/brettwooldridge/HikariCP/labels/question","name":"question","color":"cc317c"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":28,"created_at":"2015-03-24T09:21:09Z","updated_at":"2015-03-30T10:03:43Z","closed_at":null,"body":"Hi,\r\n\r\nWe have recently started using HikariCP for pooling our database (replacing DBCP) connections. We have noticed that HikariCP is not closing some of the idle connections to database making the database close the connection after 8 hours (which is the mysql wait_timeout). Whenever the pool tries to use this connection, we get an error that looks something like this: \r\n\r\n*** The last packet successfully received from the server was 3,321,958,752 milliseconds ago.  The last packet sent successfully to the server was 3,321,958,756 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 3,321,958,752 milliseconds ago.****     \r\n\r\nFrom documentation of HikariCP, I see that the default idleTimeout is around 10 minutes and connections will be automatically closed if older than that time. \r\n\r\nWe have taken thread and heap dumps of the Java process during this time and found that there is an object of type PoolBagEntry containing a connection where isClosed = false though the FD it is holding is no longer in use (from lsof on that host) indicating that the TCP connection has been closed but the pool still considers the connection open.  This is happening quite frequently, at least 1 connection from the pool behaving this way (our pool size is 10). Do let us know if something needs to be changed in our configuration or the way we use.   \r\n\r\nPlease find below details on how we are using and version information.   \r\nSpring Configuration: \r\n<bean id=\"hikariConfig\" class=\"com.zaxxer.hikari.HikariConfig\">\r\n        <property name=\"dataSourceClassName\" value=\"com.mysql.jdbc.jdbc2.optional.MysqlDataSource\" />\r\n        <property name=\"maximumPoolSize\" value=\"${max_db_pool_size}\" />\r\n        <property name=\"dataSourceProperties\">\r\n            <props>\r\n                <prop key=\"url\">${jdbc_url}</prop>\r\n                <prop key=\"user\">${jdbc_username}</prop>\r\n                <prop key=\"password\">${jdbc_password}</prop>\r\n                <prop key=\"cachePrepStmts\">true</prop>\r\n                <prop key=\"prepStmtCacheSize\">250</prop>\r\n                <prop key=\"prepStmtCacheSqlLimit\">2048</prop>\r\n                <prop key=\"useServerPrepStmts\">true</prop>\r\n                <prop key=\"characterEncoding\">utf8</prop>\r\n                <prop key=\"useUnicode\">true</prop>\r\n            </props>\r\n        </property>\r\n    </bean>\r\n\r\nMysql Server Version: 5.5\r\nMysql JDBC Library Version: mysql-connector-java-5.1.34.jar\r\nHikariCP Library Version:  HikariCP-java6-2.3.2.jar\r\nJava Version: 1.7.0_60-b19\r\n"},"comment":{"url":"https://api.github.com/repos/brettwooldridge/HikariCP/issues/comments/87620020","html_url":"https://github.com/brettwooldridge/HikariCP/issues/289#issuecomment-87620020","issue_url":"https://api.github.com/repos/brettwooldridge/HikariCP/issues/289","id":87620020,"user":{"login":"godenji","id":238557,"avatar_url":"https://avatars.githubusercontent.com/u/238557?v=3","gravatar_id":"","url":"https://api.github.com/users/godenji","html_url":"https://github.com/godenji","followers_url":"https://api.github.com/users/godenji/followers","following_url":"https://api.github.com/users/godenji/following{/other_user}","gists_url":"https://api.github.com/users/godenji/gists{/gist_id}","starred_url":"https://api.github.com/users/godenji/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/godenji/subscriptions","organizations_url":"https://api.github.com/users/godenji/orgs","repos_url":"https://api.github.com/users/godenji/repos","events_url":"https://api.github.com/users/godenji/events{/privacy}","received_events_url":"https://api.github.com/users/godenji/received_events","type":"User","site_admin":false},"created_at":"2015-03-30T10:03:43Z","updated_at":"2015-03-30T10:03:43Z","body":"@brettwooldridge the common elements between me and @mpoornima is MySQL and statement caching. Already, after 2 hours in production I see statement caching slowly raising used heap despite moderate load on the server.\r\n\r\nI think Connector/J is the culprit here, not Hikari.\r\n\r\nAnyway, in the end with a MySQL backend there's not much win in statement caching. MySQL cannot yet cache the query plan so you only save on the statement parsing phase (and that only shaves off a few ms). Add to that the fact that statements are cached on a per connection basis, this feature then only really makes sense for heavily loaded servers where connections are almost never idle.\r\n\r\nOn the plus side the memory leak got me off of BoneCP and onto Hikari, so a net win ;-) Next stop Postgres, for a decent query optimizer..."}}