Java – Tomcat configuration using DBCP

After a few seconds, we receive a communicationsexception (from DBCP) The error message (in the exception) is the end of the problem – but I don't see any wait defined in the configuration file_ timeout. (where should we look? Somewhere in the Tomcat / conf directory?)

Secondly, as suggested by exception, where is the "connector / J connection attribute" autoreconnect = true "? This is the resource definition setting in the file conf / context.xml in Tomcat:

<Resource name="jdbc/TomcatResourceName" auth="Container" type="javax.sql.DataSource"
           maxActive="100" maxIdle="30" maxWait="10000"
           removeAbandoned="true" removeAbandonedTimeout="60" logAbandoned="true"
           username="xxxx" password="yyyy"
           driverClassName="com.MysqL.jdbc.Driver"
           url="jdbc:MysqL://127.0.0.1:3306/dbname?autoReconnect=true"/>

Third, why does the JVM wait to call executeQuery () to throw an exception? If the connection times out, the getconnection method should throw an exception, shouldn't it? This is the source code part I'm talking about:

try {
                conn = getConnection (true);
                stmt = conn.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
                rset = stmt.executeQuery (bQuery);
                while (rset.next()) {
                     ....

Finally, here is the first line of the stack trace

com.MysqL.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 84,160,724 milliseconds ago.  The last packet sent successfully to the server was 84,848 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.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:532)
at com.MysqL.jdbc.Util.handleNewInstance(Util.java:406)
at com.MysqL.jdbc.sqlError.createCommunicationsException(sqlError.java:1074)
at com.MysqL.jdbc.MysqLIO.send(MysqLIO.java:3291)
at com.MysqL.jdbc.MysqLIO.sendCommand(MysqLIO.java:1938)
at com.MysqL.jdbc.MysqLIO.sqlQueryDirect(MysqLIO.java:2107)
at com.MysqL.jdbc.ConnectionImpl.execsql(ConnectionImpl.java:2642)
at com.MysqL.jdbc.ConnectionImpl.execsql(ConnectionImpl.java:2571)
at com.MysqL.jdbc.StatementImpl.executeQuery(StatementImpl.java:1451)
at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)

These are the reasons why some of us think "forget DBCP", which may depend on ide configuration and magic under the engine, drivermanager Getconnection (...) may be more reliable ". What's your opinion? Thank you for your opinion – Ms

Solution

Because DBCP keeps the returned MySQL connection open and opens the upcoming connection request, they become victims of MySQL server timeout

DBCP has many helpful functions (you can start with Tomcat 5.5 IIRC)

validationQuery="SELECT 1"
testOnBorrow="true"

Validation ensures that the connection is valid before returning to the webapp that executes the "borrow" method The flag can of course enable this function

If the timeout (8 hours, I believe) has passed and the connection is dead, a new connection will be tested (if not, it will be created) and provided to webapp

Other possible methods:

>Use testwhiteidle = "true" DBCP in resource settings, and check idle connections before detecting valid requests. > Use 'connectionproperties' to harden your MySQL connection (e.g. autoreconnect / autoreconnectforpools = true)