2

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

harshavmb
  • 3,404
  • 3
  • 21
  • 55
Lezenford
  • 41
  • 1
  • 2
  • 7
  • 1
    Possible duplicate of [How to solve MySQL max\_user\_connections error](https://stackoverflow.com/questions/11183727/how-to-solve-mysql-max-user-connections-error) – Balasubramanian Feb 28 '18 at 07:36

4 Answers4

4

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
g00glen00b
  • 41,995
  • 13
  • 95
  • 133
0

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.

nightfury
  • 384
  • 6
  • 18
0

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>
SaravanaKumar KKB
  • 81
  • 1
  • 5
0

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.

Bergony B.
  • 1