I'm having a little trouble with my MySQL- Connection- Pooling.
This is the case:
Different jobs are scheduled via Quartz. All jobs connect to different databases which works fine the whole day while the nightly scheduled jobs fail with a CommunicationsException...
Quartz-Jobs:
Job1 runs 0 0 6,10,14,18 * * ?
Job2 runs 0 30 10,18 * * ?
Job3 runs 0 0 5 * * ?
As you can see the last job runs at 18 taking about 1 hour to run.
The first job at 5am is the one that fails. I already tried all kinds of parameter-combinations in my resource config this is the one I am running right now:
<!-- Database 1 (MySQL) -->
<Resource
auth="Container"
driverClassName="com.mysql.jdbc.Driver"
maxActive="100"
maxIdle="30"
maxWait="10000"
removeAbandoned="true"
removeAbandonedTimeout="60"
logAbandoned="true"
type="javax.sql.DataSource"
name="jdbc/appDbProd"
username="****"
password="****"
url="jdbc:mysql://127.0.0.1:3306/appDbProd?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8"
testWhileIdle="true"
testOnBorrow="true"
testOnReturn="true"
validationQuery="SELECT 1"
timeBetweenEvictionRunsMillis="1800000"
/>
<!-- Database 2 (MySQL) -->
<Resource
auth="Container"
driverClassName="com.mysql.jdbc.Driver"
maxActive="100"
maxIdle="30"
maxWait="10000"
removeAbandoned="true"
removeAbandonedTimeout="60"
logAbandoned="true"
type="javax.sql.DataSource"
name="jdbc/prodDbCopy"
username="****"
password="****"
url="jdbc:mysql://127.0.0.1:3306/prodDbCopy?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8"
testWhileIdle="true"
testOnBorrow="true"
testOnReturn="true"
validationQuery="SELECT 1"
timeBetweenEvictionRunsMillis="1800000"
/>
<!-- Database 3 (MSSQL)-->
<Resource
auth="Container"
driverClassName="net.sourceforge.jtds.jdbc.Driver"
maxActive="30"
maxIdle="30"
maxWait="100"
removeAbandoned="true"
removeAbandonedTimeout="60"
logAbandoned="true"
name="jdbc/catalogDb"
username="****"
password="****"
type="javax.sql.DataSource"
url="jdbc:jtds:sqlserver://127.0.0.1:1433;databaseName=catalog;useNdTLMv2=false"
testWhileIdle="true"
testOnBorrow="true"
testOnReturn="true"
validationQuery="SELECT 1"
timeBetweenEvictionRunsMillis="1800000"
/>
For obvious reasons I changed IPs, Usernames and Passwords but they can be assumed to be correct, seeing that the application runs successfully the whole day.
The most annoying thing is:
The first job that runs first queries Database2 successfully but fails to query Database1 for some reason (CommunicationsException):
Caused by:
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:
The last packet successfully received
from the server was 39,376,539
milliseconds ago. The last packet
sent successfully to the server was
39,376,539 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.
Any ideas? Thanks!
