Remote db connection limits and MySQL Workbench

Question

I have an app using a remote MySQL service and has max_user_connections set on the plans. For my pricing level, it's 30 connections. My app functions fine; all operations work.

However, while MySQL Workbench is open (for manual db imports) if I attempt to reboot my remote app I get an error:

[2022-02-04T22:24:05.313Z] error Error: ER_USER_LIMIT_REACHED: User '************' has exceeded the 'max_user_connections' resource (current value: 30)

Upon closing MYSQL Workbench, my remote app reboots fine.

My app has max connections set to 30, although just to be safe I made it 29 in an attempt to reserve 1 connection for MWB.

Do I need to reserve even more for MWB? How many connections would it need? Can I limit MWB on my local machine (Ubuntu) so it uses less (how about just one?) connections?

To avoid this in the future, what would be best? I suppose shut my app down for maintenance at 3am and run a DB job then.

Answer 1

max_connections limits the number of simultaneous connections.

max_user_connections limits the number of simultaneous connections from any one user.

A single program, including a single instance of Workbench, should (normally) make only one connection.

Some novice programmers make a connection inside a subroutine and fail to disconnect. What they should do is have a single connection for all database activity in the program.

"Reserving 1 for MWB" -- Let me ramble on for a moment before addressing this.

"Applications" and "users" should each have their own login. 30 users should be using more than a single login. Otherwise, there is no hope of tracking them.

No "user" and no "application" should connect as the SUPER user (typically called 'root' or 'admin') except when doing admin stuff.

Your use of Workbench may qualify for "admin stuff", so you could be justified in connecting as 'root'.

MySQL reserves 1 connection (of the max_connections) for 'root'. Actually, if max_connections is 100, and you connect as root, then you can see 101 connections in the extreme case.

There is only 1 'free pass', so don't load up MWB twice if all the 'users' are 'root', too.

Summary

Configure Workbench to log in as user 'root; give everyone else a non-super login.