[LithuanianJack]

I'm saving data to MySQL and when there are a lot of people in my server (for example 25), I see error from server:

PHP Code:

[crx_ranksystem.amxx] [SQL Error] Connection failed (1203): User user_ranking already has more than 'max_user_connections' active connections 


With other plugins who is connecting to SQL, everything is fine. I do not need to increase max_user_connections limit because problem is in plugin.

[OciXCrom]

You're using other SQL plugins, but are those plugins reading data for all players at once? This one does, so you should try increasing the limit. Also try changing line 1115:

PHP Code:

new Handle:iQuery = SQL_PrepareQuery(iSqlConnection, "SELECT * FROM %s WHERE Player = '%s';", g_eSettings[SQL_TABLE], szPlayer) 


To this:

PHP Code:

new Handle:iQuery = SQL_PrepareQuery(iSqlConnection, "SELECT XP FROM %s WHERE Player = '%s';", g_eSettings[SQL_TABLE], szPlayer) 


[totopizza]

https://dev.mysql.com/doc/refman/8.0...resources.html

[LithuanianJack]

Originally Posted by OciXCrom You're using other SQL plugins, but are those plugins reading data for all players at once? This one does, so you should try increasing the limit. Also try changing line 1115: PHP Code: new Handle:iQuery = SQL_PrepareQuery(iSqlConnection, "SELECT * FROM %s WHERE Player = '%s';", g_eSettings[SQL_TABLE], szPlayer)  To this: PHP Code: new Handle:iQuery = SQL_PrepareQuery(iSqlConnection, "SELECT XP FROM %s WHERE Player = '%s';", g_eSettings[SQL_TABLE], szPlayer)

Originally Posted by totopizza https://dev.mysql.com/doc/refman/8.0...resources.html

My max_user_connections limit is 100 and I am not allowed to increase this value because of hosting provider. :/

I was using another plugin which selected and inserted players data at the real time and then I reached this connections limit but then I changed the code and saved data only at death event and everything was fine but in your plugin I do not know what to change to reach the result..

I tried to change this line but nothing changes.

PHP Code:

new Handle:iQuery = SQL_PrepareQuery(iSqlConnection, "SELECT XP FROM %s WHERE Player = '%s';", g_eSettings[SQL_TABLE], szPlayer) 


[OciXCrom]

Data is saved when the player disconnects. Saving it at death is redundant since it will get saved multiple times per game, instead of only once. Of course, when the map changes, it will save the data for all players at once since they all get disconnected from the server.

[LithuanianJack]

My hosting provider told me that possible problem is in plugin. Maybe inactive SQL connections is not closed by MySQL_close function and accumulates inactive (sleep) connections to database, finally max_user_connections limit is reached.

[OciXCrom]

There's no "MySQL_close" function in AMXX. Google tells me there was at once point, probably in some very old AMXX version (10+ years ago).

[LithuanianJack]

You forgot to add SQL_FreeHandle(g_iSqlTuple) function at plugin_end(), and that's not enough, I still didn't solved this problem.

When player disconnects, sleepy (inactive) connection is still there - it should not be so.

This error appears when player disconnects >100 times at the same map. Inactive connections are reason why I reach max_user_connections limit.

[DruGzOG]

Originally Posted by LithuanianJack You forgot to add SQL_FreeHandle(g_iSqlTuple) function at plugin_end(), and that's not enough, I still didn't solved this problem. When player disconnects, sleepy (inactive) connection is still there - it should not be so. This error appears when player disconnects >100 times at the same map. Inactive connections are reason why I reach max_user_connections limit.

He's spot on about this. Running the plugin and having the same issue to the point that there are idle connections as well.

PHP Code:

L 03/16/2019 - 13:32:38: [crx_ranksystem.amxx] Too many connections
L 03/16/2019 - 13:33:09: [crx_ranksystem.amxx] Too many connections
L 03/16/2019 - 13:33:11: [crx_ranksystem.amxx] [SQL Error] Connection failed (1040): Too many connections 


Max connections is around 450. Running this and admin_sql.amxx. Nothing else.
Looking at the code, there isn't anything really closing the sql connections, is there?

Edit: Looks like its a default amxx problem ~

[totopizza]

Try this, reduce the number of times a connection is created, now only create once:

Test it (I did not try it, but it should work):