[Feature Request] DB Queues

[BlackYuzia]

System

OS: Ubuntu 16.04, 20.04 SM: 1.10 MM: latest

Description

Hi there.

How we know, we have 2 database drivers (SQLite and MYSQL). In 1.11+ we also have third driver - PostgreSQL. How I know, they work in one (or single) queue.

If they have one queue, that also give us a bug / feature / side effect: when mysql disconnect by some reasons, I suppose, what MYSQL driver just block all queue by connection(s) and, maybe, queries. Because we have one queue for MYSQL and SQLite, all SQLIte queries all stop and wait until MYSQL complete they connections and etc. If MYSQL can't connecct, that repeat infinity times...

Reproduce

To reproduce:

1. You should have a MYSQL and SQLite databases (plugins what get / set data inside and from SQLIte and MYSQL databases)
2. When your server works and have connection with MYSQL database, then database (host) should drop / lost connection and do not connect again never (by some reasons, in my case - too bad ethernet or lost him on database host by WAR in Ukraine, maybe you could get the same result?)
3. MYSQL should send many try connections to database and block queue.
4. SQLIte would just wait until he can get data, but he don't get data until MYSQL driver get connect from database.
5. Success! MYSQL driver was block queue and SQLIte driver can't get any data because queue is blocked by MYSQL!

Feature Request

As I say, with single databases drivers queue we have bug(s), so my request: Add one queue per one driver (SQLite and MYSQL).

That should fix a bug with blocking queue by MYSQL Driver.

[rm-veil]

I have the same problem on my project.

[asherkin]

See previously: #1076 (which looks like it was closed accidentally.)

Thread-per-driver is a slightly odd way to split this up, I'd previously looked at options around thread-per-connection (or per-plugin) but touching the DBI threading model is extremely brittle until the work outlined in #1207 is complete - which likely opens us up to solving the underlying problem better by keeping the single worker thread and switching to async APIs under the hood..

[Alexeyt89]

I have the same problem, when I reload a plugin which has unfinished threaded queries, the queries are turned into non-threaded and if the connection to the MySQL server is not stable, the plugin locks the thread which is eventually killed by watchdog.

/* 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.
*/

I see two ways here - detache all pending queries from the plugin and keep them threaded, or drop them. The information in the queries can't be more important than server stability.

Crashstack: Crash reason: SIGABRT 0 linux-gate.so + 0xb49 1 dbi.mysql.ext.so + 0x6535f vio_read_buff 2 dbi.mysql.ext.so + 0x550eb my_real_read 3 dbi.mysql.ext.so + 0x5550f my_net_read 4 dbi.mysql.ext.so + 0x50e4f cli_safe_read 5 dbi.mysql.ext.so + 0x5156f cli_read_query_result 6 dbi.mysql.ext.so + 0x4e986 mysql_real_query 7 dbi.mysql.ext.so + 0x48bea MyDatabase::DoQuery 8 sourcemod.logic.so + 0x8b025

As a non-breaking fix a cvar can be added which, if it is set to true, would make all threaded queries automatically drop when the plugin, the queries belong to, is about to be unloaded. Another way is to add a function SQL_TDropQueries which could be executed in OnPluginEnd.