Solved MySQL "Too many connections" error

I have a minigame server, and all of the data is stored in MySQL databases, which are hosted locally. Since there are 36 servers per minigame, there has to be a lot of connections to the database.

I connect to the server in my onEnable() method, and I disconnect in my onDisable() method.

Once I turn on the 32nd Server (around there) I get this error:

Code:

Encountered a com.mysql.jbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections"

That's the entire error message, nothing more. How could I fix this? Is it impossible to have so many connections on a MySQL database at the same time? Is there some kind of software I can use to make it so I have to only open one connection for all of the servers?

Thanks!

 

MySQL limits the connections, yes. I suggest opening the connection when you use it, and closing it when you're done. There's no need to keep it open.

 

Ok, but wouldn't that create lag? It takes about a half of a second to open a connection for me (which isn't much, but it would effect gameplay). Also, couldn't the problem still arise if somehow all of the servers happen to open a connection at the same time?

 

If all servers were to have the connection open at once, yes, the same problem would occur. The thing is, is you only use it for a few seconds, it's highly unlikely. About the lag issue; as long as it's localhost, don't worry about it. Make sure all connections are made to the localhost IP, and it'll take very little time (if your machine is okay).

 

True, but the problem with lag still arises... Are you sure there's no software like a connection handler that I could use so that I only have to have 1 connection open at a time?

 

Read my reply from two minutes ago. If you really wanted to, you could change it to accept more than 32 connections, but I honestly don't know how, or how difficult it is.

 

Ok, thanks... One database acts as a global database and stores ranks and everything like that, so it could have up to 500 connections from all of the servers. I could do all of the db stuff onEnable and onDisable, so is there any way to check how many connections a certain database has, because I could use delayed tasks and only connect when the database has less than 32 connections.

Also, do you know if there's any way to check the max connections?

 

Simple.
Login to your phpMyAdmin account.
Click the variables tab
Scroll down to find max connections
Change it to what you want

TIP: The more connections, the more RAM and CPU usage it will use.

 

So you just automatically assume everyone has PhPMyAdmin? I don't really need to download it because I already have the MySQL workbench and I don't really want extra stuff on the host server. how much CPU & RAM do you think ~135 connections would take up? If you know?

 

Yes because like 75% of server hosts use phpMyAdmin.. It's more preferable.
Well imo, phpMyAdmin is a better way of accessing your MySQL. I wouldn't say that using the MySQL workbench is a good idea if you're using it to locate data for a server I think it's harder to use.

Also just put the connections to 1000, it shouldn't use anymore than half a gb of ram. (My doesn't)

 

Ok, thanks. I'll try that!

I fixed it by setting the max connections to 2048 by running the query SET GLOBAL max_connections=2048;

Thanks Xyplo & TheMcScavenger!

EDIT by Moderator: merged posts, please use the edit button instead of double posting.