Raised This Month: $12 Target: $400
 3% 

MySQL connection loss leads to crashes


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
kadet.89
Veteran Member
Join Date: Nov 2012
Location: Serbia
Old 07-08-2021 , 09:06   MySQL connection loss leads to crashes
Reply With Quote #1

I have a plugin which connects to a remote MySQL server and makes queries.
It works fine as long as there is no connection problem. When something happens on the MySQL server or between the server and the game server and the plugin can't connect to the mysql server, the game server crashes.
In the crash stack at the crash moment something happens in dbi.mysql.ext.so and then the server's watchdog kills the server. How can I rewrite the plugin to avoid this crash in future?
Attached Files
File Type: sp Get Plugin or Get Source (testDB.sp - 155 views - 862 Bytes)
File Type: txt crashdmp.txt (9.3 KB, 64 views)

Last edited by kadet.89; 07-08-2021 at 09:07.
kadet.89 is offline
Send a message via Skype™ to kadet.89
Bacardi
Veteran Member
Join Date: Jan 2010
Location: mom's basement
Old 07-08-2021 , 09:48   Re: MySQL connection loss leads to crashes
Reply With Quote #2

Which game ? MM:S and SM version ? OS ?

*edit
could you show databases.cfg also, without IP's and passwords.


*edit
You sure, you not have another plugin on server, which does not use threaded connection ?
__________________
Do not Private Message @me

Last edited by Bacardi; 07-08-2021 at 09:52.
Bacardi is offline
kadet.89
Veteran Member
Join Date: Nov 2012
Location: Serbia
Old 07-08-2021 , 10:16   Re: MySQL connection loss leads to crashes
Reply With Quote #3

Metamod:Source version 1.12.0-dev+1147
Plugin interface version: 16:14
SourceHook version: 5:5
Loaded As: Valve Server Plugin
Compiled on: Dec 27 2020 18:13:59
Built from: https://github.com/alliedmodders/met...commit/80d3f9c
Build ID: 1147:80d3f9c
http://www.metamodsource.net/
SourceMod Version: 1.11.0.6692
SourcePawn Engine: 1.11.0.6692, jit-x86 (build 1.11.0.6692)
SourcePawn API: v1 = 5, v2 = 13
Compiled on: Jun 3 2021 09:29:00
Built from: https://github.com/alliedmodders/sou...commit/d01c72f
Build ID: 6692:d01c72f
http://www.sourcemod.net/
Protocol version 13795 [1301/1301]
Exe version 1.37.9.5 (csgo)
Exe build: 03:24:05 Jul 3 2021 (8234) (730)

There are a lot of plugins, but I don't see any among them where non-threaded connection could be used. Is there a way to block such plugins or enable a warning or something to know if any of the plugins uses "not safe" API?

Should I check the plugins for SQL_Query and SQL_Connect only or there are other potentially dangerous functions?
Attached Files
File Type: cfg databases.cfg (1.3 KB, 69 views)

Last edited by kadet.89; 07-08-2021 at 10:49.
kadet.89 is offline
Send a message via Skype™ to kadet.89
Bacardi
Veteran Member
Join Date: Jan 2010
Location: mom's basement
Old 07-08-2021 , 11:27   Re: MySQL connection loss leads to crashes
Reply With Quote #4

well... if you say your test plugin cause a crash, at least unload other plugins to avoid confusion.

I quick test your test plugin, without any MySQL connection tough. It gave normal error message few seconds later. No crash.
Bacardi is offline
kadet.89
Veteran Member
Join Date: Nov 2012
Location: Serbia
Old 07-25-2021 , 08:12   Re: MySQL connection loss leads to crashes
Reply With Quote #5

I investigated the problem and found out that, the crashes happen after any of the plugins using MySQL are reloaded. Looks like after a plugin is reloaded, it can't any longer exectue SQL queries in a separate thread and all quaries are executed in the main thread.

I guess eather
PHP Code:
pPlugin->GetProperty("DisallowDBThreads"NULL
or
PHP Code:
!g_DBMan.AddToThreadQueue(opPrioQueue_High
returns true for reloaded plugins.

To test my assumption I created a reloader plugin. It reloads all plugins when map changes.
If there are no plugins using MySQL - everything is fine, but when I add one, the server goes to boot loop (if the MySQL server is switched off).

The problem might be in (DBManager::OnPluginWillUnload):
https://github.com/alliedmodders/sou...c/Database.cpp
Also it might switch unfinished threaded queries to non-threaded and block the main thread... not sure if it works this way though.

Is it a bug ?
Attached Files
File Type: sp Get Plugin or Get Source (reloader.sp - 126 views - 1.4 KB)

Last edited by kadet.89; 07-25-2021 at 08:53.
kadet.89 is offline
Send a message via Skype™ to kadet.89
Bacardi
Veteran Member
Join Date: Jan 2010
Location: mom's basement
Old 07-25-2021 , 08:41   Re: MySQL connection loss leads to crashes
Reply With Quote #6

I don't know, make sure there is no plugin which lock database and (hopefully) unlock (as well).


5.2 Locking
Bacardi is offline
kadet.89
Veteran Member
Join Date: Nov 2012
Location: Serbia
Old 07-25-2021 , 08:50   Re: MySQL connection loss leads to crashes
Reply With Quote #7

I run the test with only testDB.smx. On my local server it is enough to use only one MySQL-using plugin to cause this crash.
To make sure only testDB uses MySQL, I remove all plugins but testDB.smx and reloader.smx

Does it here "convert" not-yet sent threaded queries into non-threaded ones?
Quote:
/* Mark the plugin as being unloaded so future database calls will ignore threading... */
plugin->SetProperty("DisallowDBThreads", NULL);

/* Run all of the think operations.
* Unlike the driver unloading example, we'll let these calls go through,
* since a plugin unloading is far more normal.
*/

Last edited by kadet.89; 07-25-2021 at 10:04.
kadet.89 is offline
Send a message via Skype™ to kadet.89
pedrotski
Member
Join Date: Nov 2020
Old 07-26-2021 , 09:06   Re: MySQL connection loss leads to crashes
Reply With Quote #8

Having hibernation turned on causes all kinds of oddball mysql issues. Make sure its turned off.

Also make sure the latency between your mysql server and game server is as small as possible. Ideally have them in the same data center.
__________________
pedrotski is offline
kadet.89
Veteran Member
Join Date: Nov 2012
Location: Serbia
Old 07-26-2021 , 10:18   Re: MySQL connection loss leads to crashes
Reply With Quote #9

I doubt hibernattion has something to do with this problem, crash happens indepandently on player count. If I run the 2 plugins while beeing on the server, there is no hibernation and the crash still happens.
If MySQL server is switched off, or there is a network problem which makes it impossible to get information from the MySQL server, it leads to crashes in 100% cases.

It is enough to set some invalid ip address in database.cfg and run the 2 plugins to get boot loop.

As I see it now, it is a design issue, but I don't have enough expirience to understend how it works.

Most likely there is a lazy solution to handle unfinished threaded queries, which turns them into non-threaded when a plugin is about to be unloaded, instead of dropping the queries or postponing unloading.
kadet.89 is offline
Send a message via Skype™ to kadet.89
pedrotski
Member
Join Date: Nov 2020
Old 07-26-2021 , 11:37   Re: MySQL connection loss leads to crashes
Reply With Quote #10

Quote:
Originally Posted by kadet.89 View Post
I doubt hibernattion has something to do with this problem, crash happens indepandently on player count. If I run the 2 plugins while beeing on the server, there is no hibernation and the crash still happens.
If MySQL server is switched off, or there is a network problem which makes it impossible to get information from the MySQL server, it leads to crashes in 100% cases.

It is enough to set some invalid ip address in database.cfg and run the 2 plugins to get boot loop.

As I see it now, it is a design issue, but I don't have enough expirience to understend how it works.

Most likely there is a lazy solution to handle unfinished threaded queries, which turns them into non-threaded when a plugin is about to be unloaded, instead of dropping the queries or postponing unloading.
Trust me, Hibernation causes all kinds of oddball issues. So you should at least try it.

https://forums.alliedmods.net/showthread.php?t=332055

Here is an example of an issue I had because of hibernation. It would crash if no one joined the server. Most popular servers that actually get lots of people playing run with hibernation disabled for a reason.

Also, your mysql server should never be "off". If it has connection issues, there is a problem elsewhere in your setup.
__________________

Last edited by pedrotski; 07-26-2021 at 11:39.
pedrotski is offline
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 08:27.


Powered by vBulletin®
Copyright ©2000 - 2024, vBulletin Solutions, Inc.
Theme made by Freecode