CODEWITHSUNDEEP
javamysqltomcatspring-boot
Lezenford
Lezenford

Reputation: 43

SpringBoot MySQL JDBC Unable to create initial connections of pool

Good day!

I have a simple springboot application with mysql jdbc repository.

I have properties for connect to DB

spring.datasource.url=jdbc:mysql://*:3306/*?useSSL=false
spring.datasource.username=*
spring.datasource.password=*
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.initialize=true
spring.datasource.dbcp2.validation-query=SELECT 1
spring.datasource.dbcp2.max-total=1

My test DB has only max 10 connections for user. When I use console 

SHOW STATUS WHERE variable_name = 'threads_connected';

I can see that now DB has 5 connections only but when I try to start my application I get Exception

2018-02-28 10:26:24.115 ERROR 17360 --- [nio-8080-exec-3] o.a.tomcat.jdbc.pool.ConnectionPool : Unable to create initial connections of pool.

java.sql.SQLSyntaxErrorException: User '*' has exceeded the 'max_user_connections' resource (current value: 10)

How can I fix it? And why I get that Exception if I have 5 free connetion on DB and I need only 1 connection for pool from properties? I can't edit max connection on DB because use Heroku like testDB. I can edit only tomcat properties only

Upvotes: 2

Views: 30141

Answers (4)

Bergony B.
Bergony B.

Reputation: 1

Same problem here... try to change the version on parent like says SaravanaKumar but the project is to old and I get a lot of deprecated.

I Fixed by change only the database version

  <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.23</version> <!--Add that Line-->
      <scope>runtime</scope>
  </dependency>

Note: if update on parent all dependency get update.

Upvotes: 0

SaravanaKumar KKB
SaravanaKumar KKB

Reputation: 81

I got exactly same error and It worked for me after changing the spring boot version from 1.5.* to 2.1.8. 

<parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.8.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

Upvotes: 0

nightfury
nightfury

Reputation: 412

I was stuck in similar situation but the simple fix for this could be like the database that you are pointing in application.properties probably does not exist.

Or You can try to delete org.springframework.data folder from C:\Users\{yourusername}\.m2 folder and update Maven project.

Upvotes: 0

Dimitri Mestdagh
Dimitri Mestdagh

Reputation: 44665

You configured the following property:

spring.datasource.dbcp2.max-total=1

This indicates that you're trying to use the DBCP 2 connection pool. However, when you check the stacktrace, you can see the following:

o.a.tomcat.jdbc.pool.ConnectionPool : Unable to create initial connections of pool.

As the package of the ConnectionPool class is org.apache.tomcat, this indicates that you're actually using the default Tomcat connection pool. This means that your max-total poperty is not being picked up properly.

If you want to configure this for a Tomcat connection pool, you need to use the maxActive property:

spring.datasource.tomcat.max-active=1

Alternatively, if you don't want to use the Tomcat connection pool, you can add the DBCP 2 dependency using Maven/Gradle/... . If you exclude the default Tomcat connection pool, it will automatically pick up DBCP 2.

Another possibility is to configure it by using the spring.datasource.type property as mentioned by the documentation:

You can bypass that algorithm completely and specify the connection pool to use via the spring.datasource.type property. This is especially important if you are running your application in a Tomcat container as tomcat-jdbc is provided by default.

For example:

spring.datasource.type=org.apache.commons.dbcp2.BasicDataSource

Upvotes: 4

Related Questions