`Could not open connection` issue despite using c3p0

Question

I've been using c3p0 for connection pooling in my Spring Boot Application for a few months now. Everything was fine until about 2 weeks ago when I started experiencing connection issues especially in the morning. Every morning when I try to log-in to my application, it would throw a Could not open connection error. I would then restart my application in order to remove the problem. I am unable to figure out the root cause of the problem.

Here's my hibernate.cfg.xml:

hibernate-configuration>
<session-factory>
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/mydb?autoReconnect=true</property>
    <property name="hibernate.connection.username">root</property>
    <property name="hibernate.connection.password">abc123</property>
    <property name="hibernate.dialect">config.CustomDialect</property>

    <property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
    <property name="hibernate.c3p0.initialPoolSize">5</property>
    <property name="hibernate.c3p0.minPoolSize">5</property>
    <property name="hibernate.c3p0.maxPoolSize">100</property>
    <property name="hibernate.c3p0.checkoutTimeout">3000</property>
    <property name="hibernate.c3p0.maxStatementsPerConnection">30</property>
    <property name="hibernate.c3p0.unreturnedConnectionTimeout">3000</property>
    <property name="hibernate.c3p0.debugUnreturnedConnectionStackTraces">true</property>

    <property name="show_sql">true</property>
    <property name="format_sql">true</property>
    <property name="hbm2ddl.auto">update</property>

...
POJO mappings
</session-factory>
</hibernate-configuration>

Here's my HibernateUtil Class:

public class HibernateUtil {

private static final SessionFactory sessionFactory;
static {
    try {
        Configuration configuration = new Configuration().configure();
        StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder()
                .applySettings(configuration.getProperties());
        sessionFactory = configuration.buildSessionFactory(builder.build());

    } catch (Exception ex) {
        throw new ExceptionInInitializerError(ex);
    }
}

public Session openSession() {
    return sessionFactory.openSession();
}
}

I've added c3p0 debug configuration to my application to cull unreturned connections(in-case of a memory leak) and generate a stack-trace for it but nothing shows up in the logs.

Here's some of the logs from this morning :

https://pastebin.com/MGb4Miau

Can anyone here help me figure where the problem lies?

EDIT: CustomDialect Class:

public class CustomDialect extends MySQL5InnoDBDialect {
public String getTableTypeString() {
    return " ENGINE=InnoDB DEFAULT CHARSET=utf8";
    }
}

Answer 1

I finally figured out a solution to the problem. I made the following changes to my hibernate.cfg.xml:

 <property name="hibernate.c3p0.maxIdleTime">10800</property>
 <property name="hibernate.c3p0.maxIdleTimeExcessConnections">600</property>

And it works! The maxIdleTime property removes connections that have been idle from the pool for more than the specified time period(in seconds) which ensures that my connections are refreshed from time-to-time and maxIdleTimeExcessConnections lets me cull connections from the pool in excess of minPoolSize that have been idle for more than the specified time period(in seconds). This way I ensure that I don't have too many connections in the pool and that they all are fresh.