SpringBoot MySQL JDBC无法创建池的初始连接 [英] SpringBoot MySQL JDBC Unable to create initial connections of pool
问题描述
美好的一天！
我有一个带有mysql jdbc存储库的简单springboot应用程序.
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
我的测试数据库最多只能有10个用户连接.当我使用控制台
My test DB has only max 10 connections for user. When I use console
SHOW STATUS WHERE variable_name = 'threads_connected';
我可以看到现在数据库只有5个连接，但是当我尝试启动我的应用程序时会出现
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错误17360 --- [nio-8080-exec-3] o.a.tomcat.jdbc.pool.ConnectionPool:无法创建初始 池的连接.
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:用户'*'超出了 'max_user_connections'资源(当前值:10)
java.sql.SQLSyntaxErrorException: User '*' has exceeded the 'max_user_connections' resource (current value: 10)
我该如何解决?如果我在DB上有5个免费连接并且从属性池中仅需要1个连接，为什么还要得到该异常?我无法在DB上编辑最大连接，因为像testDB一样使用Heroku.我只能编辑tomcat属性
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
推荐答案
您配置了以下属性:
spring.datasource.dbcp2.max-total=1
这表明您正在尝试使用 DBCP 2 连接池.但是，当您检查stacktrace时，可以看到以下内容:
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.
由于ConnectionPool类的软件包是org.apache.tomcat，所以这表明您实际上正在使用默认的Tomcat连接池.这意味着您的max-total人口没有得到适当的补偿.
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.
如果要为Tomcat连接池配置它，则需要使用maxActive属性:
If you want to configure this for a Tomcat connection pool, you need to use the maxActive property:
spring.datasource.tomcat.max-active=1
或者，如果您不想使用Tomcat连接池，则可以使用Maven/Gradle/...添加DBCP 2依赖项.如果排除默认的Tomcat连接池，它将自动选择DBCP2.
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.
