we currently have a major issue on our production server. We have a MySQL DB on one server (Ubuntu 14.04 and MySQL version 5.5.57).
We have another server with all the applications talking to the database on server one. For 5 days now we keep getting this error on the second server:
05-09-2017 00:00:01 WARN com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@623841b8 -- 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 attempts (30). Last acquisition attempt exception:
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
at sun.reflect.GeneratedConstructorAccessor32.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2395)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2316)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
at sun.reflect.GeneratedConstructorAccessor19.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:347)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:146)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:195)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:184)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:200)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1086)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1073)
at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:44)
at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1810)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:648)
Caused by: java.sql.SQLException: null, message from server: "Host 'IP_ADDRESS' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'"
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1114)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2493)
at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2334)
We have been trying for 5 days to resolve this, but every time we restart mysql its a couple of hours then the error starts again. I have upped the max_connect_errors from 10 to 50, but that just prolonged the error.
Then we have also adjusted the log level to 2, but the only thing we ever see in the error log is aborted connections, no 'denied' connections.
Is there any way I can see the actual connection errors? Please help to resolve this, I do not know how to resolve. We also don't have a DBA, so its just a dev team who are not database experts.
EDIT: The application is a Java maven application, using hibernate to connect to the database. Here is the hibernate config file:
<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.username">USERNAME_HERE</property>
<property name="hibernate.connection.password">PASSWORD_HERE</property>
<property name="hibernate.connection.url">jdbc:mysql://SERVER_HERE:3306/voda_soccer?zeroDateTimeBehavior=convertToNull</property>
<property name="hibernate.connection.autoReconnect">true</property>
<property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
<property name="hibernate.temp.use_jdbc_metadata_defaults">false</property>
<property name="show_sql">false</property>
<property name="hibernate.c3p0.min_size">5</property>
<property name="hibernate.c3p0.max_size">20</property>
<property name="hibernate.c3p0.timeout">300</property>
<property name="hibernate.c3p0.max_statements">50</property>
<property name="hibernate.c3p0.idle_test_period">3000</property>
<mapping class="com.myapp.sms.producer.entity.Customer"></mapping>
<mapping class="com.myapp.sms.producer.entity.Gifting"></mapping>
<mapping class="com.myapp.sms.producer.entity.BulkCampaign"></mapping>
</session-factory>
We always close connections in a finally block:
} finally {
if (session != null) {
try {
session.close();
} catch (Exception e) {}
}
session = null;
}
I know we can flush the hosts or restart MySQL, but I cannot do this all the time in production server. I want to find out WHY we are getting the connection errors and where to see them?
