AlliedModders

AlliedModders (https://forums.alliedmods.net/index.php)
-   General (https://forums.alliedmods.net/forumdisplay.php?f=58)
-   -   Solved [CS:GO] SQL_TQuery, script execution timed out (https://forums.alliedmods.net/showthread.php?t=332449)

kadet.89 05-13-2021 14:15

[CS:GO] SQL_TQuery, script execution timed out
 
2 Attachment(s)
I experience "Script execution timed out" on my server every day. Most often it ends up in a sequence of server crashes with "SIGABRT" reason (usually about 3-5 crashes in a row).

Here is an example of console messages right before a crash:
Code:

L 05/13/2021 - 17:41:37: [plugin.smx] ALTER TABLE table1 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
L 05/13/2021 - 17:41:37: [SM] Exception reported: Script execution timed out
L 05/13/2021 - 17:41:37: [SM] Blaming: plugin.smx
L 05/13/2021 - 17:41:37: [SM] Call stack trace:
L 05/13/2021 - 17:41:37: [SM]  [0] SQL_TQuery
L 05/13/2021 - 17:41:37: [SM]  [1] Line 14, plugin::GotDatabase
L 05/13/2021 - 17:41:37: [plugin.smx] SQL ERROR
L 05/13/2021 - 17:41:37: [plugin.smx] Multiple primary key defined
#
#Console initialized.
#Using breakpad minidump system 740/13790.1272.DC
#Loading VPK file hashes for pure server operation.
#Loading VPK file hashes for pure server operation.
#Loading VPK file hashes for pure server operation.
#Loading VPK file hashes for pure server operation.

This query is executed on every map change and sometimes I get such exceptions. Sometimes the server crashes after printing the ALTER TABLE message without printing anything else.
It seems that some blocking situation happens there from time to time. Usually execution of this query takes much less than 1 second. But sometimes much more time is requried.
Is there a way to avoid server crashes when execution takes a lot of time? Is there a way to make execution of the query non-blocking? The plugin is in the attachment.
Also there is a crashstack, which has little data though. I think the reason for the crash might be the server's watchdog.

Rohanlogs 05-17-2021 17:42

Re: [CS:GO] SQL_TQuery, script execution timed out
 
Hello there!
I checked the code, and you are running ALTER TABLE everytime you're connecting to SQL.
This becomes a problem because you're now attempting to create a new primary key everytime (line #114), and there can be only 1 primary key.

You can run line #114 only once when the table is first created when it didn't exist.
If you run it multiple times you're going to have problems, this causes the error 'Multiple primary key defined'.

Solution:
Just comment the line #114 if you have already created the table, or preferred way: move the primary and unique key into the CREATE TABLE statement (line #112).
Then it'll only be executed once when the table doesn't exist.

Edit:
Like this
PHP Code:

CREATE TABLE IF NOT EXISTS `table22` (`param1VARCHAR(32), `param2VARCHAR(32), `clientpasswordVARCHAR(64), PRIMARY KEY (`param2`), UNIQUE KEY `uc` (`param1`) ); 

Now you can remove line 114.

Rohanlogs 05-17-2021 20:26

Re: [CS:GO] SQL_TQuery, script execution timed out
 
Also adding to this, you have several ALTER TABLE table1 CONVERT TO CHARACTER SET executions on the connect callback too.
Using any ALTER commands on your connect callback is a very bad idea. Especially if the table has many records, it can lead to 'Script execution timed out' and crash the server depending on what you're doing.
You can instead add COLLATE='utf8mb4_unicode_ci' at the end of your table creation statements and remove all the ALTER TABLE commands.

So your table22 creation would finally look like this, with the keys and unicode collation:
PHP Code:

CREATE TABLE IF NOT EXISTS `table22` ( `param1VARCHAR(32), `param2VARCHAR(32), `clientpasswordVARCHAR(64), PRIMARY KEY (`param2`), UNIQUE KEY `uc` (`param1`) ) COLLATE='utf8mb4_unicode_ci'


kadet.89 05-21-2021 17:41

Re: [CS:GO] SQL_TQuery, script execution timed out
 
Rohanlogs, Thank you, your advice was helpful. The server doesn't crash anymore.


All times are GMT -4. The time now is 07:06.

Powered by vBulletin®
Copyright ©2000 - 2021, vBulletin Solutions, Inc.