Help Ubuntu Mysql Too Many Connections after launching gameserver

[Horse]

Hi there,

 

My problem is: I can connect remotely to the database (using navicat) when the gameserver isn't running.

However after I launch the gameserver on my server machine and I try to connect remotely to the database I get the Too Many Connections error.

After I kill the gameserver I'm still unable to connect to the DB until i restart mysql.

 

And the same thing happens if I simply quit navicat while I'm logged in the DB without closing the connection first.

 

Does anyone have any ideas what may be the issue?

 

 

[Horse]

Meanwhile I realised that I had the max DB connection set to 40 in the server,properties and I had to set a value bigger than 40 in the mysql's .cnf file.

 

And now it seems to be working.

 

Does that mean that the amount of connections I set in the server.properties is automatically reserved on server startup?

 

Also, I would like to ask you guys: What would be the optimal DB connection amount for  (lets say 400-500 players) in server.properties / and in the mysql config.

 

Thanks in advance.

Edited November 9, 2018 by Horse

[Irelia#1]

1.Another Linux Ubuntu wannabe user :P

2. Still using Navicat FFS! Have you heard about HeidiSQL?

3. At for MySQL just keep the default 50 db connections otherwise with 200, 400 or even more you'll have big problems :))

..there is a reason in that keeping the "default" values. 

4. GL & HF! ;)

[Solomun]

2 hours ago, Irelia#1 said:

2. Still using Navicat FFS! Have you heard about HeidiSQL?

 

Navicat is actually very proper.

 

 

[Nightw0lf]

did you connect your website in your database? :D

if yes imagine a ddos attack in your site what damage can do in your game server, well something like that :)

[Rootware]

Connection pool size 400 continious connections? What the server you uses? 20000 players or all data locate in DB?

[Horse]

17 hours ago, Irelia#1 said:

1.Another Linux Ubuntu wannabe user :P

2. Still using Navicat FFS! Have you heard about HeidiSQL?

3. At for MySQL just keep the default 50 db connections otherwise with 200, 400 or even more you'll have big problems :))

..there is a reason in that keeping the "default" values. 

4. GL & HF! ;)

 My mistake, i meant to write 40 not 400. The default value was 100 which I actually lowered.

 

Although as far as i understand even that value is higher than it actually should be. Since it shouldn't be higher than core numbers * 2 + effective spindles.

 

And yet After browsing through many source files of other projects i see the following:  MaxDbConnections on L2j : 100, Mobius: 500, Tales:999 and so on all higher than 100 or even above 500. So, yea it left me perplexed.

And that is why I am asking if there's an optimal value, or I'm missing something and there's a reason these projects have very high dbconnection values.

 

As for HeidiSQL I used it before switching  to  Navicat Premium which seems far better in my humble opinion, but each to its own, i guess.

 

 

As for your sarcasm, I doubt any of us are given birth as Linux experts. I barely used linux before, so yea, obviously I ask to better understand it.

 

14 hours ago, Nightw0lf said:

did you connect your website in your database? :D

if yes imagine a ddos attack in your site what damage can do in your game server, well something like that :)

Naturally, I 'm not planning to expose my DB to that, so no.

 

 

 

Edited November 9, 2018 by Horse

[Nightw0lf]

Let me get this, so you "touch" the values without having backup now asking for help?

each project has its own customs inside (for me the more connections they use the worse they are)

 

just for you to know the obvious reason is the server as a cause

and the solution is

1) make bigger the pool

2) reduse server's pool

 

if you dont get that until now do a fresh install in your OS and start over (it helps).

[Horse]

26 minutes ago, Nightw0lf said:

Let me get this, so you "touch" the values without having backup now asking for help?

each project has its own customs inside (for me the more connections they use the worse they are)

 

just for you to know the obvious reason is the server as a cause

and the solution is

1) make bigger the pool

2) reduse server's pool

 

if you dont get that until now do a fresh install in your OS and start over (it helps).

 

It's a bit hard to understand what you are trying to say, but I take it you assume i'm talking about a live server while I'm not.

Therefore, talking about backup at this point seems irrelevant.

Regardless of defualt or non-default values, the problem is that I had to set the number of connections higher in the mysql settings compared to the servers settings in order to be able to connect to the DB remotely through navicat.

And I simply wish to properly understand why is that, and why many projects have such high maxdbconnection values while in theory it wouldn't be needed, that's all.

 

[Nightw0lf]

just go with l2off maybe java is not for you then

[Kara]

The stupidy in this topic is beyond. 

@Irelia#1 make me a favor and ban your self as if HeidiSQL is different or better. You're a classic fanboi who have no idea yet saying random stuff to impress. 

[Horse]

1 hour ago, Nightw0lf said:

just go with l2off maybe java is not for you then

No offense, but it seems you don't even understand what I'm asking, so I'd appreciate if you didn't post random,totally irrelevant suggestions out of the blue, if you can't answer my quiestion.

[Nightw0lf]

2 hours ago, Horse said:

Regardless of defualt or non-default values, the problem is that I had to set the number of connections higher in the mysql settings compared to the servers settings in order to be able to connect to the DB remotely through navicat. 

And I simply wish to properly understand why is that, and why many projects have such high maxdbconnection values while in theory it wouldn't be needed, that's all. 

you ask why projects need to many connections, if you cant handle that fact or work with it i gave you the solution, if you want to know why project creators did that or why ask them they are about 50-60 years old fellows now, hard to find them on the internet, but oh wait i answered that when i told you,

3 hours ago, Nightw0lf said:

each project has its own customs inside (for me the more connections they use the worse they are)

implying custom as custom code that uses database inserts/updates/deletes that count as a connection to database capish?

[Horse]

3 minutes ago, Nightw0lf said:

you ask why projects need to many connections, if you cant handle that fact or work with it i gave you the solution, if you want to know why project creators did that or why ask them they are about 50-60 years old fellows now, hard to find them on the internet, but oh wait i answered that when i told you,

 

 50 -60 years old fellows? the hell are you talking about? Clearly not about l2j projects. I think you have some serious misunderstandings here.

 

8 minutes ago, Nightw0lf said:

 

implying custom as custom code that uses database inserts/updates/deletes that count as a connection to database capish?

Thanks for stating the obvious...then again... it has hardly anything to do with my question ..lol.

[Nightw0lf]

ok i give up you cant even understand your problem at least check google, how mysql works, what keep alive connection is, how long it stays alive and specially if you KILL your server without proper close in order to close that connection pool.

[Horse]

54 minutes ago, Nightw0lf said:

ok i give up you cant even understand your problem at least check google, how mysql works, what keep alive connection is, how long it stays alive and specially if you KILL your server without proper close in order to close that connection pool.

The first thing that actually made some sense. Yes, one time I hadn't properly shut down theserver, and the connections were not properly closed.

 

 

Ofc my main question still stands, why do I need more mysql connections than server side maxdbconnections value.

 

The reason I am asking this is because I have never encountered such a problem when I was using windows or windows server.

Edited November 9, 2018 by Horse