How to configure connection pool c3p0 hibernate most efficiently for remote connection mySql

Question

I use hibernate for a desktop application and the database server is in another country.

Unfortunately, connection problems are very common at the moment.

These are excerpts from the log file on the database server:

1. 2024-03-19 14:08:42 2378 [Warning] Aborted connection 2378 to db: 'CMS_DB' user: 'JOHN' host: 'bba-83-130-102-145.alshamil.net.ae' ( Got an error reading communication packets)
2. 2024-03-19 13:44:45 1803 [Warning] Aborted connection 1803 to db: 'CMS_DB' user: 'REMA' host: '188.137.160.92' (Got timeout reading communication packets)
3. 2024-03-19 11:51:08 1526 [Warning] Aborted connection 1526 to db: 'unconnected' user: 'unauthenticated' host: '92.216.164.102' (Got an error reading packet communications)
4. 2024-03-19 11:51:08 1526 [Warning] Aborted connection 1526 to db: 'unconnected' user: 'unauthenticated' host: '92.216.164.102' (This connection closed normally without authentication)
5. 2024-03-19 11:55:26 1545 [Warning] IP address '94.202.229.78' could not be resolved: No such host is known.

In addition, these error messages often appear on the client-side:

javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154) at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1542) at org.hibernate.query.Query.getResultList(Query.java:165) at

Also this:

Caused by: java.sql.SQLTransactionRollbackException: (conn=9398) Deadlock found when trying to get lock; try restarting transaction at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:76) at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:153) at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:274) at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:229) at org.mariadb.jdbc.ClientSidePreparedStatement.execute(ClientSidePreparedStatement.java:149) at org.mariadb.jdbc.ClientSidePreparedStatement.executeUpdate(ClientSidePreparedStatement.java:181) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:1502) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197) ... 41 more Caused by: org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException: Deadlock found when trying to get lock; try restarting transaction at org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException.of(MariaDbSqlException.java:34) at

So far I had the following c3p0 configuration in my hibernate.cfg.xml.

        <!-- Related to the connection START -->
        <property name="connection.driver_class">org.mariadb.jdbc.Driver</property>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>

                <!-- Related to Hibernate properties START -->
        <property name="hibernate.connection.driver_class">org.mariadb.jdbc.Driver</property>
        <property name="hibernate.show_sql">false</property>
        <property name="hibernate.format_sql">false</property>
        <property name="hibernate.current_session_context_class">thread</property>
        <property name="hibernate.temp.use_jdbc_metadata_defaults">false</property>
        <property name="hibernate.generate_statistics">true</property>
        <property name="hibernate.enable_lazy_load_no_trans">true</property>

        <!-- c3p0 Setting -->
        <property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
        <property name="hibernate.c3p0.min_size">4</property>
        <property name="hibernate.c3p0.max_size">15</property>
        <property name="hibernate.c3p0.timeout">300</property>
        <property name="hibernate.c3p0.max_statements">20</property>
        <property name="hibernate.c3p0.acquire_increment">3</property>
        <property name="hibernate.c3p0.idle_test_period">100</property>
        <property name="hibernate.c3p0.testConnectionOnCheckout">true</property>
        
        <property name="hibernate.c3p0.unreturnedConnectionTimeout">30</property>
        <property name="hibernate.c3p0.debugUnreturnedConnectionStackTraces">true</property>

Can someone look into whether the values for the remote connection make sense? Any change recommendation is warmly welcomed!

Thanks in advance!