How i can fix a 'max_user_connections' reached problem in MySQL?

Question

I use a deployment platform named Mochahost, and I usually put my war file on the server to deploy my Spring Boot App, like I always do.

But recently, I got this error during the deployment :

[mysqluser] already has more than 'max_user_connections' active connections

Is there somebody who knows what happen at deploying App, why there is so much connections at the same time, whereas I'm just deploying an app and not inserting datas ?

The project is a SparingBoot App so I use HikariCP by default for connection pool. I'm the only user to connect to the database.

I didn't declare some JPA persistence unit.

Here is the log on mvn clean install

2021-02-21 14:50:40.153 INFO 4792 --- [ task-1] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...

2021-02-21 14:50:42.173 INFO 4792 --- [ task-1] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.

2021-02-21 14:50:42.235 INFO 4792 --- [ task-1] org.hibernate.dialect.Dialect : HHH000400: Using dialect: org.hibernate.dialect.MySQL5Dialect

2021-02-21 14:50:42.306 INFO 4792 --- [ main] DeferredRepositoryInitializationListener : Triggering deferred initialization of Spring Data repositories?

2021-02-21 14:50:49.147 INFO 4792 --- [ task-1] o.h.e.t.j.p.i.JtaPlatformInitiator : HHH000490: Using JtaPlatform implementation : [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform]

2021-02-21 14:50:49.226 INFO 4792 --- [ task-1] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default' 2021-02-21

14:50:50.349 INFO 4792 --- [ main] DeferredRepositoryInitializationListener : Spring Data repositories initialized!

2021-02-21 14:50:50.382 INFO 4792 --- [ main] c.d.q.q.QuizzJavaSpringApplicationTests : Started QuizzJavaSpringApplicationTests in 15.982 seconds (JVM running for 17.371) [INFO] Tests run: 1, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 17.176 s - in com.douineau.qjsbr.quizzjavaspring.QuizzJavaSpringApplicationTests

2021-02-21 14:50:51.047 INFO 4792 --- [extShutdownHook] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'

2021-02-21 14:50:51.058 INFO 4792 --- [extShutdownHook] o.s.s.concurrent.ThreadPoolTaskExecutor : Shutting down ExecutorService 'applicationTaskExecutor'

2021-02-21 14:50:51.061 INFO 4792 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated...

2021-02-21 14:50:51.109 INFO 4792 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed.

Thank you in advance for your support.

Answer 1

max_user_connections means that there are too many "users"* trying to connect to your mysql server.

While it is possible to configure your server to allow more connections as described here: https://dev.mysql.com/doc/refman/8.0/en/user-resources.html I'd checked on what settings do you use in your clients(your java code and possibly channel pool(CP) configuration, very likely in *.properties file). Your CP can be configured to have more connections that in reality are not needed(connections is not the same as RPS), so one option would be to reduce number fo connections for your client if possible. The other reason why you may see this error this time while you haven't seen it in past is horizontal scalability? Was it that in past you ran your code on N servers and now you're using N+M servers? Total number of connections from java-client to your mysql server will go up in this case and again, you have 2 options: increase max allowed number of connections to server, OR, if reasonable, decrease number of connections your client(java app) code makes to mysql.

*"users" - you can have several users that can connect to mysql. The fact that you saturated max connections for userA does not means that userB won't be able to connect. So in case you're reusing the same mysql user from different apps to connect to to mysql, you can just have a dedicated user per app.

UPDATE:

from mysql documentation:

The maximum number of simultaneous connections permitted to any given MySQL user account. A value of 0 (the default) means “no limit.” This variable has a global value that can be set at server startup or runtime. It also has a read-only session value that indicates the effective simultaneous-connection limit that applies to the account associated with the current session. The session value is initialized as follows: If the user account has a nonzero MAX_USER_CONNECTIONS resource limit, the session max_user_connections value is set to that limit. Otherwise, the session max_user_connections value is set to the global value. Account resource limits are specified using the CREATE USER or ALTER USER statement. See Section 6.2.20, “Setting Account Resource Limits”.