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

解决方案

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