C3P0 related bug resolution for database connection pool

Several common bugs for database connection pooling:

1. Warning: com[email protected]76c7022e--apparent DEADLOCK!!! Creating Emergency threads for unassigned pending tasks!

October 01, 2016 6:28:24 PM Com.mchange.v2.async.threadpoolasynchronousrunner$deadlockdetector Run

More than 80% is due to similar problems such as data connection configuration or databases .

There is a problem with the configuration in ①jdbc.properties

Driverclassname

Url

② does not have a corresponding database.

Check discovery:The port of the URL is wrong!!!

url = jdbc:mysql://127.0.0.1:3306/tt; (TT is the database name)

Note that the port number of MySQL must be 3306, not 8080! 8080 is the port number of the server.

2. Prompted not to find the jar package file:

Exception java.io.filenotfoundexception:e:\apache-tomcat-6.0.30\apache-tomcat-6.0.30\apache-tomcat-6.0.30\ Webapps\web-inf\lib\c3p0-0.9.1.2.jar (the system cannot find the file specified.) )
Java.io.FileInputStream.open (Native Method)
java.io.fileinputstream.<init> (fileinputstream.java:106)
com.chdmy.sysmgr.fill.action.FillAqAction.downFile (fillaqaction.java:957)
Sun.reflect.GeneratedMethodAccessor374.invoke (Unknown Source)
Sun.reflect.DelegatingMethodAccessorImpl.invoke (delegatingmethodaccessorimpl.java:25)
Java.lang.reflect.Method.invoke (method.java:597)
Org.apache.struts.actions.DispatchAction.dispatchMethod (dispatchaction.java:274)
Org.apache.struts.actions.DispatchAction.execute (dispatchaction.java:194)
Org.springframework.web.struts.DelegatingActionProxy.execute (delegatingactionproxy.java:110)
org.apache.struts.action.RequestProcessor.processActionPerform (requestprocessor.java:419)
org.apache.struts.action.RequestProcessor.process (requestprocessor.java:224)
org.apache.struts.action.ActionServlet.process (actionservlet.java:1194)
org.apache.struts.action.ActionServlet.doPost (actionservlet.java:432)
Javax.servlet.http.HttpServlet.service (httpservlet.java:637)
Javax.servlet.http.HttpServlet.service (httpservlet.java:717)
Org.extremecomponents.table.filter.AbstractExportFilter.doFilter (abstractexportfilter.java:49)
Com.chdmy.filter.SetCharacterEncodingFilter.doFilter (setcharacterencodingfilter.java:57)
Com.chdmy.filter.SessionCheck.doFilter (sessioncheck.java:96)

Symptom reason: Obviously in Web-inf\lib Directory added C3p0-0.9.1.2.jar package, but need to add Mchange-common.jar package

Workaround:

3. Error connection cannot be accessed

Error

Java.sql.SQLException:Connections could not being acquired from the underlying database! [See nested exception:java.sql.SQLException:Connections could not being acquired from the underlying database!]]
At org.quartz.impl.jdbcjobstore.JobStoreSupport.schedulerStarted (jobstoresupport.java:693)
At Org.quartz.core.QuartzScheduler.start (quartzscheduler.java:580)
At Org.quartz.impl.StdScheduler.start (stdscheduler.java:142)
At org.quartz.ee.servlet.QuartzInitializerListener.contextInitialized (quartzinitializerlistener.java:198)
At Org.apache.catalina.core.StandardContext.listenerStart (standardcontext.java:3830)
At Org.apache.catalina.core.StandardContext.start (standardcontext.java:4337)
At Org.apache.catalina.core.ContainerBase.start (containerbase.java:1045)
At Org.apache.catalina.core.StandardHost.start (standardhost.java:719)
At Org.apache.catalina.core.ContainerBase.start (containerbase.java:1045)
At Org.apache.catalina.core.StandardEngine.start (standardengine.java:443)
At Org.apache.catalina.core.StandardService.start (standardservice.java:516)
At Org.apache.catalina.core.StandardServer.start (standardserver.java:710)
At Org.apache.catalina.startup.Catalina.start (catalina.java:566)
At sun.reflect.NativeMethodAccessorImpl.invoke0 (Native Method)
At Sun.reflect.NativeMethodAccessorImpl.invoke (nativemethodaccessorimpl.java:39)
At Sun.reflect.DelegatingMethodAccessorImpl.invoke (delegatingmethodaccessorimpl.java:25)
At Java.lang.reflect.Method.invoke (method.java:597)
At Org.apache.catalina.startup.Bootstrap.start (bootstrap.java:288)
At Org.apache.catalina.startup.Bootstrap.main (bootstrap.java:413)
caused by:org.quartz.JobPersistenceException:Failed to obtain DB connection from data source ' MyDS ': java.s Ql. Sqlexception:connections could not being acquired from the underlying database! [See nested exception:java.sql.SQLException:Connections could not being acquired from the underlying database!]
At org.quartz.impl.jdbcjobstore.JobStoreSupport.getConnection (jobstoresupport.java:777)
At org.quartz.impl.jdbcjobstore.JobStoreTX.getNonManagedTXConnection (jobstoretx.java:71)
At Org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInNonManagedTXLock (jobstoresupport.java:3774)
At Org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInNonManagedTXLock (jobstoresupport.java:3739)
At org.quartz.impl.jdbcjobstore.JobStoreSupport.recoverJobs (jobstoresupport.java:833)
At org.quartz.impl.jdbcjobstore.JobStoreSupport.schedulerStarted (jobstoresupport.java:691)
 ... more
caused by:java.sql.SQLException:Connections could not being acquired from the underlying database!
At com.mchange.v2.sql.SqlUtils.toSQLException (sqlutils.java:106)
At com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection (C3p0pooledconnectionpool.java : 529)
At com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection (Abstractpoolbackeddatasource.java : +)
At org.quartz.utils.PoolingConnectionProvider.getConnection (poolingconnectionprovider.java:247)
At org.quartz.utils.DBConnectionManager.getConnection (dbconnectionmanager.java:108)
At org.quartz.impl.jdbcjobstore.JobStoreSupport.getConnection (jobstoresupport.java:774)
 ... more
caused by:com.mchange.v2.resourcepool.cannotacquireresourceexception:a Resourcepool could not acquire A Resource from its primary factory or source.
At com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable (basicresourcepool.java:1319)
At Com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource (basicresourcepool.java:557)
At Com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource (basicresourcepool.java:477)
At com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection (C3p0pooledconnectionpool.java : 525)
 ... more

The above problems may be caused by the following reasons:

① Driver Configuration error: Driver=com.mysql.jdbc.driver

② Database connection Address error: URL=JDBC:MYSQL://LOCALHOST:3306/TT

③ database Password or account number is incorrect

④ database is not started or is not authorized to access

⑤ Project did not introduce the corresponding database driver jar package: Mysql-connector-java-5.1.6-bin.jar

⑥mysql Root does not have remote access permissions, you need to increase permissions, the steps to increase permissions are as follows:

Go to MySQL database:
Grant all privileges on * * to ' root ' @ '% ' identified by ' root ' with GRANT option;
Flush privileges;

(I made one of the two errors: 1. Not included in the Mysql-connector-java-5.1.6-bin.jar package

2. Database connection port is wrong: written jdbc:mysql://localhost:8080/tt, Port should be 3036)

4. Although the program runs successfully, the data can be checked out, but the controller will still appear the following warning:

Warning:

Com[email protected]6ff9129c--acquisition attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition at tempts (30). Last acquisition attempt exception:
java.sql.SQLException:The server time zone value is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the Servertimezone configuration) to use a more specifc Time zone value if you want to utilize time zone support.
At com.mysql.cj.jdbc.exceptions.SQLError.createSQLException (sqlerror.java:695)
At com.mysql.cj.jdbc.exceptions.SQLError.createSQLException (sqlerror.java:663)
At com.mysql.cj.jdbc.exceptions.SQLError.createSQLException (sqlerror.java:653)
At com.mysql.cj.jdbc.exceptions.SQLError.createSQLException (sqlerror.java:638)
At com.mysql.cj.jdbc.exceptions.SQLError.createSQLException (sqlerror.java:606)
At com.mysql.cj.jdbc.exceptions.SQLError.createSQLException (sqlerror.java:624)
At com.mysql.cj.jdbc.exceptions.SQLError.createSQLException (sqlerror.java:620)
At com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException (sqlexceptionsmapping.java:68)
At Com.mysql.cj.jdbc.ConnectionImpl.createNewIO (connectionimpl.java:1683)
At com.mysql.cj.jdbc.connectionimpl.<init> (connectionimpl.java:656)
At com.mysql.cj.jdbc.ConnectionImpl.getInstance (connectionimpl.java:349)
At Com.mysql.cj.jdbc.NonRegisteringDriver.connect (nonregisteringdriver.java:221)

Workaround:

Add &SERVERTIMEZONE=UTC to the Connecturl configuration of C3p0-config.xml

Example: <property name= "Jdbcurl" >jdbc:mysql://127.0.0.1:8080/tt&servertimezone=utc</proper ty>

Thank you: http://jyao.iteye.com/blog/1915561 and http://www.cnblogs.com/God-/p/5845301.html share.

C3P0 related bug resolution for database connection pool