How to configure Quarkus to go beyond 20 Mysql connections?

Question

I am unable to go beyond 20 Mysql connections, while working with Quarkus.

Here is the content of the application.properties file that is used:

quarkus.datasource.url=jdbc:mysql://localhost:3306/testdb?zeroDateTimeBehavior=convertToNull
quarkus.datasource.driver=com.mysql.cj.jdbc.Driver
quarkus.datasource.username =root
quarkus.datasource.password =
quarkus.http.test-port=38030
quarkus.http.port=38030
quarkus.datasource.jdbc.initial-size=50
quarkus.datasource.jdbc.max-size=100

I am using the file persistence.xml below:

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence
             http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"
             version="2.1">

    <persistence-unit name="GenerateWorkerPU" transaction-type="RESOURCE_LOCAL">

        <description>Hibernate test case template Persistence Unit</description>

        <properties>
            <!-- intentionally using worse case so that we can optimise for this -->
            <property name="hibernate.archive.autodetection" value="class, hbm"/>

            <!-- Connection specific -->
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL8Dialect"/>
            <property name="hibernate.connection.pool_size" value="1000"/>

            <!-- Tuning and debugging -->
            <property name="hibernate.show_sql" value="false"/>
            <property name="hibernate.format_sql" value="false"/>

            <!--
                Optimistically create the tables;
                will cause background errors being logged if they already exist,
                but is practical to retain existing data across runs (or create as needed) 
                <property name="javax.persistence.schema-generation.database.action" value="drop-and-create"/>
                -->
            <!-- drop-and-create create-only -->


            <property name="javax.persistence.validation.mode" value="NONE"/>
        </properties>

    </persistence-unit>
</persistence>

And the following annotation:

@PersistenceUnit (unitName = "GenerateWorkerPU")
EntityManagerFactory emf;

These save me from having to modify in depth the code coming from wildfly 13.

The code is working fine but beyond 20 Mysql connections , I get the following error from the log:

2020-05-17 19:11:14,791 WARN  [org.hib.eng.jdb.spi.SqlExceptionHelper] (executor-thread-2) SQL Error: 0, SQLState: null
2020-05-17 19:11:14,791 ERROR [org.hib.eng.jdb.spi.SqlExceptionHelper] (executor-thread-2) Sorry, acquisition timeout!
2020-05-17 19:11:14,798 SEVERE [DAO.GenerateCertStatusDaoV2] (executor-thread-2) null: 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.internal.AbstractProducedQuery.getSingleResult(AbstractProducedQuery.java:1581)
        at DAO2.GenerateCertStatusDaoV2.findByName(GenerateCertStatusDaoV2.java:53)
        at threading2.SignatureProducerCV1.call(SignatureProducerCV1.java:87)
        at threading2.SignatureProducerCV1.call(SignatureProducerCV1.java:33)
        at io.smallrye.context.SmallRyeThreadContext$ContextualCallable.call(SmallRyeThreadContext.java:117)
        at io.quarkus.runtime.CleanableExecutor$CleaningCallable.call(CleanableExecutor.java:246)
        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
        at org.jboss.threads.ContextClassLoaderSavingRunnable.run(ContextClassLoaderSavingRunnable.java:35)
        at org.jboss.threads.EnhancedQueueExecutor.safeRun(EnhancedQueueExecutor.java:2046)
        at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.doRunTask(EnhancedQueueExecutor.java:1578)
        at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1426)
        at org.jboss.threads.DelegatingRunnable.run(DelegatingRunnable.java:29)
        at org.jboss.threads.ThreadLocalResettingRunnable.run(ThreadLocalResettingRunnable.java:29)
        at java.base/java.lang.Thread.run(Thread.java:834)
        at org.jboss.threads.JBossThread.run(JBossThread.java:479)
Caused by: org.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
        at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:107)
        at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:134)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.connection(StatementPreparerImpl.java:50)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:149)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:176)
        at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:151)
        at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:2104)
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2034)
        at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2012)
        at org.hibernate.loader.Loader.doQuery(Loader.java:953)
        at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354)
        at org.hibernate.loader.Loader.doList(Loader.java:2838)
        at org.hibernate.loader.Loader.doList(Loader.java:2820)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2652)
        at org.hibernate.loader.Loader.list(Loader.java:2647)
        at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:506)
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:396)
        at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219)
        at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1404)
        at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1565)
        at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1533)
        ... 15 more
Caused by: java.sql.SQLException: Sorry, acquisition timeout!
        at io.agroal.pool.ConnectionPool.handlerFromSharedCache(ConnectionPool.java:244)
        at io.agroal.pool.ConnectionPool.getConnection(ConnectionPool.java:173)
        at io.agroal.pool.DataSource.getConnection(DataSource.java:66)
        at io.quarkus.hibernate.orm.runtime.customized.QuarkusConnectionProvider.getConnection(QuarkusConnectionProvider.java:23)
        at org.hibernate.internal.NonContextualJdbcConnectionAccess.obtainConnection(NonContextualJdbcConnectionAccess.java:38)
        at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:104)
        ... 35 more

Answer 1

The quarkus.datasource.jdbc.max-size and quarkus.datasource.jdbc.initial-size properties have to be named quarkus.datasource.max-size and quarkus.datasource.initial-size, because otherwise the they apply to a named data source with the name jdbc, rather than to the global unnamed default data source, and I suppose you want to use that one. Your other data source properties apply to the unnamed data source. And the default max-size is 20, that's why you can't get over 20 connections.