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

Solved [CS:GO] SQL_TQuery, script execution timed out


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
kadet.89
Veteran Member
Join Date: Nov 2012
Location: Serbia
Old 05-13-2021 , 13:15   [CS:GO] SQL_TQuery, script execution timed out
Reply With Quote #1

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.
Attached Files
File Type: sp Get Plugin or Get Source (test.sp - 40 views - 6.0 KB)
File Type: txt dmp.txt (108.5 KB, 34 views)

Last edited by kadet.89; 05-21-2021 at 16:41.
kadet.89 is offline
Send a message via Skype™ to kadet.89
Rohanlogs
Senior Member
Join Date: Nov 2015
Old 05-17-2021 , 16:42   Re: [CS:GO] SQL_TQuery, script execution timed out
Reply With Quote #2

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.
__________________

Last edited by Rohanlogs; 05-17-2021 at 17:01.
Rohanlogs is offline
Rohanlogs
Senior Member
Join Date: Nov 2015
Old 05-17-2021 , 19:26   Re: [CS:GO] SQL_TQuery, script execution timed out
Reply With Quote #3

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'
__________________
Rohanlogs is offline
kadet.89
Veteran Member
Join Date: Nov 2012
Location: Serbia
Old 05-21-2021 , 16:41   Re: [CS:GO] SQL_TQuery, script execution timed out
Reply With Quote #4

Rohanlogs, Thank you, your advice was helpful. The server doesn't crash anymore.
kadet.89 is offline
Send a message via Skype™ to kadet.89
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 11:02.


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