Java Application Faces Database Connection Errors After MySQL Upgrade to Version 8

Question

I'm encountering an issue in a complex situation. I had MySQL 5 and recently upgraded it to MySQL 8. Since then, my Java application doesn't work properly when initially running on Tomcat.

However, after updating the MySQL dependency in the pom.xml file, the issue persists. from

<dependency>
    <groupId>org.mariadb.jdbc</groupId>
    <artifactId>mariadb-java-client</artifactId>
    <version>1.7.3</version>
</dependency>

<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
</dependency>

to

<dependency>
   <groupId>org.mariadb.jdbc</groupId>
   <artifactId>mariadb-java-client</artifactId>
   <version>1.8.0</version>
</dependency>
<dependency>
   <groupId>com.mysql</groupId>
   <artifactId>mysql-connector-j</artifactId>
   <version>8.0.33</version>
</dependency>

And now it's functional, but I'm encountering an issue with the database. I'm consistently getting 'too many connection' errors. When I execute the query SHOW PROCESSLIST;, I receive the following result:

I have around 200 lines similar to this user1 in my result. and ialready set max_connexion=200;

When I searched for 'database1' within my codebase, I found it referenced in the persistence.xml file with the following configuration:

<properties>
        
            <property name="hibernate.connection.driver_class" value="org.mariadb.jdbc.Driver" />
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect" />
            <property name="hibernate.cache.use_second_level_cache" value="true" />
            <property name="hibernate.cache.use_query_cache" value="true" />
            <property name="hibernate.cache.region.factory_class" value="org.hibernate.cache.ehcache.EhCacheRegionFactory"/>
            <property name="hibernate.cache.provider_class"  value="org.hibernate.cache.EhCacheProvider"  />
            <property name="hibernate.show_sql" value="false" />
            <property name="hibernate.format_sql" value="false" />
            <property name="hibernate.generate_statistics" value="true" />
            <property name="hibernate.connection.release_mode" value="auto" />
            <property name="hibernate.archive.autodetection" value="class, hbm"/>
            <property name="hibernate.bytecode.use_reflection_optimizer" value="true"/>
            <property name="hibernate.bytecode.provider" value="javassist"/>


            <!-- create database schema at startup -->
            <!-- not for real application -->
            <!-- <property name="hibernate.hbm2ddl.auto" value="create" /> -->


            <property name="connection.provider_class"  value="org.hibernate.connection.C3P0ConnectionProvider"/>
            <property name="hibernate.c3p0.min_size" value="5" />
            <property name="hibernate.c3p0.max_size" value="20" />
            <property name="hibernate.c3p0.timeout" value="3600" /> <!-- <property name="hibernate.c3p0.timeout" value="1800" /> -->
            <property name="hibernate.c3p0.idle_test_period" value="3600"/> <!-- idle_test_period < timeout -->
            <property name="hibernate.c3p0.max_statements" value="50" />
            <property name="hibernate.c3p0.preferredTestQuery" value="SELECT 1" />
            <property name="hibernate.c3p0.testConnectionOnCheckin" value="true" />
            <property name="hibernate.c3p0.testConnectionOnCheckout" value="false" /> <!-- pas recommandé : http://www.mchange.com/projects/c3p0/ --><!-- <property name="c3p0.testConnectionOnCheckout" value="true" /> -->
 
             <property name="hibernate.connection.url" value="jdbc:mysql://${serverNameBD}:${portNumberBD}/${databaseNameBD}"/>
             <property name="hibernate.connection.driver_class" value="org.mariadb.jdbc.Driver" />
             <property name="hibernate.connection.password" value="${passwordBD}" />
              <property name="hibernate.connection.username" value="${userBD}" />


            <property name="hibernate.connection.autoReconnect" value="true" />
            <property name="hibernate.connection.autoReconnectForPools" value="true" />
            <property name="hibernate.connection.is-connection-validation-required" value="true" />

In addition, for further information, in my hibernate.properties file, I have the following settings:

hibernate.connection.driver_class=com.mysql.jdbc.Driver
hibernate.connection.url=jdbc:mysql://ip:3306/database100(not database1)
hibernate.connection.username=user1
hibernate.connection.password=passwrod
hibernate.dialect=org.hibernate.dialect.MySQLInnoDBDialect
hibernate.show_sql=true
jpa.dialect=org.springframework.orm.jpa.vendor.HibernateJpaDialect
jpa.vendor.adapter=HibernateJpaVendorAdapter