skript MySQL database Deadlock error

Question

skript MySQL database Deadlock error

startree236 opened this issue 3 months ago · 8 comments

startree236 commented 3 months ago

Skript/Server Version

[Skript] Skript's aliases can be found here: https://github.com/SkriptLang/skript-aliases
[00:24:25 INFO]: [Skript] Skript's documentation can be found here: https://docs.skriptlang.org/
[00:24:25 INFO]: [Skript] Skript's tutorials can be found here: https://docs.skriptlang.org/tutorials
[00:24:25 INFO]: [Skript] Server Version: git-Paper-381 (MC: 1.20.4)
[00:24:25 INFO]: [Skript] Skript Version: 2.9.2 (skriptlang-github)
[00:24:25 INFO]: [Skript] Installed Skript Addons:
[00:24:25 INFO]: [Skript]  - skript-placeholders v1.5.2 (https://github.com/APickledWalrus/skript-placeholders)
[00:24:25 INFO]: [Skript]  - Parties v3.2.15 (https://alessiodp.com/parties)
[00:24:25 INFO]: [Skript]  - Skellett v2.0.9 (https://forums.skunity.com/resources/skellett.24/)
[00:24:25 INFO]: [Skript]  - HQGiftBoxSK v1.0.0
[00:24:25 INFO]: [Skript]  - skUtilities v0.9.2 (https://tim740.github.io/)
[00:24:25 INFO]: [Skript]  - skript-reflect v2.4 (https://github.com/SkriptLang/skript-reflect)
[00:24:25 INFO]: [Skript]  - CoroutineSK v1.0.1
[00:24:25 INFO]: [Skript]  - skRayFall v1.9.28 (https://sk.rayfall.net/)
[00:24:25 INFO]: [Skript]  - SkQuery v4.1.10
[00:24:25 INFO]: [Skript]  - SkBee v3.4.3 (https://github.com/ShaneBeee/SkBee)
[00:24:25 INFO]: [Skript] Installed dependencies:
[00:24:25 INFO]: [Skript]  - SQLibrary v7.1
[00:24:25 INFO]: [Skript]  - Vault v1.7.3-b131
[00:24:25 INFO]: [Skript]  - WorldGuard v7.0.9+5934e49

Bug Description

when I start the server, I see an error in the bucket saying "Cannot load variables from the database fast enough (loading took 1.779s, monitor interval = 1.0s). Please increase your monitor interval or reduce usage of variables. (this warning will be repeated at most once every 10 seconds)" and intermittently I see an error in the bucket saying "[Skript variable save thread for database 'MySQL'/INFO]: [Skript] database error: Deadlock found when trying to get lock; try restarting transaction".

Expected Behavior

I would like to avoid error messages in buckets.
This error seems like it will cause big problems someday.

Steps to Reproduce

"monitor interval" of "config" is set to 1 seconds. If it is not set to 1 seconds, data will not be loaded immediately when the server is moved.

Errors or Screenshots

when I start the server, I see an error in the bucket saying "Cannot load variables from the database fast enough (loading took 1.779s, monitor interval = 1.0s). Please increase your monitor interval or reduce usage of variables. (this warning will be repeated at most once every 10 seconds)" and intermittently I see an error in the bucket saying "[Skript variable save thread for database 'MySQL'/INFO]: [Skript] database error: Deadlock found when trying to get lock; try restarting transaction".

Other

Agreement

I have read the guidelines above and affirm I am following them with this report.

startree236 · Answer 1 · 2024-09-29T15:32:01.000Z

The config is as follows:

==== Variables ====

databases:

Databases to store variables in. These can either be used as a simple one-server-storage

where variables are written constantly but only read at server start,

or as a connection between multiple servers by monitoring the database(s) for changes.

You can define as many databases as you want, just make sure to choose a distinct name for each one, and don't forget to set all options correctly.

To be able to use a database you'll need to download the plugin 'SQLibrary' from https://dev.bukkit.org/projects/sqlibrary/files

and install it in your server's plugin directory like other plugins.

Please note that '/skript reload' will not reload this section, i.e. you'll have to restart Skript for changes to take effect.

Each database definition must be in a separate section. You can choose any name for the sections, as long as it's not already used.

database 1:

An example database to describe all possible options.

type: MySQL

The type of this database. Allowed values are 'CSV', 'SQLite', 'MySQL' and 'disabled'.

CSV uses a text file to store the variables, while SQLite and MySQL use databases, and 'disabled' makes Skript ignore the database as if it wasn't defined at all.

pattern: playerdata::.*

Defines which variables to save in this database.

This pattern uses Regex syntax, e.g. use 'db_.*' (without the quotes) to store all variables prefixed with 'db_' in this database,

or use '.*' (the default value) to store all variables here (recommended for the last database in this list, as otherwise some variables might not be saved).

Please note that variables are only stored in one database, and databases are checked from top to bottom,

e.g. if a variable matches the topmost database's pattern it will be saved there and nowhere else.

BTW: Patterns are checked in a separate thread, i.e. your server likely won't run slower when using complicated patterns.

monitor changes: true
monitor interval: 1 seconds

If 'monitor changes' is set to true, variables will repeatedly be checked for updates in the database (in intervals set in 'monitor interval').

! Please note that you should set 'pattern', 'monitor changes' and 'monitor interval' to the same values on all servers that access the same database!

== MySQL configuration ==

host: localhost# Where the database server is located at, e.g. 'example.com', 'localhost', or '192.168.1.100'
port: 3306# 3306 is MySQL's default port, i.e. you likely won't need to change this value
user: root
password: password
database: minecraft# The database to use, the table will be created in this database.
table: skript# The name of the table to create. 'variables21' is the default name, if this was to be omitted.

(If the table exists but is defined differently that how Skript expects it to be you'll get errors and no variables will be saved and/or loaded)

== SQLite/CSV configuration ==

file: ./plugins/Skript/variables.db

Where to save the variables to. For a CSV file, the file extension '.csv' is recommended, but not required, but SQLite database files must end in '.db' (SQLibrary forces this).

The file path can either be absolute (e.g. 'C:\whatever...' [Windows] or '/usr/whatever/...' [Unix]), or relative to the server directory (e.g. './plugins/Skript/...').

#table: variables21

The name of the table to create. 'variables21' is the default name, if this was to be omitted.

(If the table exists but is defined differently that how Skript expects it to be you'll get errors and no variables will be saved and/or loaded)

This is generally not required as the the .db file will only be used by Skript, unless you want to split different variables into different tables

backup interval: 2 hours

Creates a backup of the file every so often. This can be useful if you ever want to revert variables to an older state.

Variables are saved constantly no matter what is set here, thus a server crash will never make you loose any variables.

Set this to 0 to disable this feature.

erenkarakal · Answer 2 · 2024-09-29T15:35:22.000Z

Skript's SQL support is currently broken but there is a PR to fix it
You can download them below
Java 21: https://github.com/SkriptLang/Skript/actions/runs/10962024319
Java 17: https://github.com/SkriptLang/Skript/actions/runs/10962024301
Keep in mind that this is a nightly build so it might have issues.

startree236 · Answer 3 · 2024-09-30T16:19:30.000Z

Thank you very much!

startree236 · Answer 4 · 2024-09-30T15:10:04.000Z

@erenkarakal After replacing the script plugin, do I need to modify the config?

I still have the "Monitor Interval" set to 1 second.
And still when I start the server I get the error "[Skript] Cannot load variables from the database fast enough (loading took 1.043s, monitor interval = 1.0s). Please increase your monitor interval or reduce usage of variables. (this warning will be repeated at most once every 10 seconds)"

Can I ignore this error?

erenkarakal · Answer 5 · 2024-09-30T16:05:06.000Z

you can safely ignore it or even better increase the interval

erenkarakal · Answer 6 · 2024-12-19T12:46:14.000Z

@erenkarakal Could you please tell me how this feature stores and links data? Sometimes the data is saved and sometimes it isn't. It seems like this can only be fixed by shutting down the server and restarting it. Sorry for contacting you so late.

I'm not the one working on the feature but the way Skript saves variables is if there's been X variables changed (iirc 1000) or when a certain time has passed (iirc 5 minutes). This may or may not apply to SQL, not sure.
If you kill the server instead of running the stop command Skript won't get a chance to save the variables

startree236 · Answer 7 · 2024-12-19T12:25:55.000Z

@erenkarakal Could you please tell me how this feature stores and links data?
Sometimes the data is saved and sometimes it isn't.
It seems like this can only be fixed by shutting down the server and restarting it.
Sorry for contacting you so late.

startree236 · Answer 8 · 2024-12-19T13:31:45.000Z

@erenkarakal So, do you know what the "Monitor Interval" function does?

Share to

Skript/Server Version

Bug Description

Expected Behavior

Steps to Reproduce

Errors or Screenshots

Other

Agreement

==== Variables ====

Databases to store variables in. These can either be used as a simple one-server-storage

where variables are written constantly but only read at server start,

or as a connection between multiple servers by monitoring the database(s) for changes.

You can define as many databases as you want, just make sure to choose a distinct name for each one, and don't forget to set all options correctly.

To be able to use a database you'll need to download the plugin 'SQLibrary' from https://dev.bukkit.org/projects/sqlibrary/files

and install it in your server's plugin directory like other plugins.

Please note that '/skript reload' will not reload this section, i.e. you'll have to restart Skript for changes to take effect.

Each database definition must be in a separate section. You can choose any name for the sections, as long as it's not already used.

An example database to describe all possible options.

The type of this database. Allowed values are 'CSV', 'SQLite', 'MySQL' and 'disabled'.

CSV uses a text file to store the variables, while SQLite and MySQL use databases, and 'disabled' makes Skript ignore the database as if it wasn't defined at all.

Defines which variables to save in this database.

This pattern uses Regex syntax, e.g. use 'db_.*' (without the quotes) to store all variables prefixed with 'db_' in this database,

or use '.*' (the default value) to store all variables here (recommended for the last database in this list, as otherwise some variables might not be saved).

Please note that variables are only stored in one database, and databases are checked from top to bottom,

e.g. if a variable matches the topmost database's pattern it will be saved there and nowhere else.

BTW: Patterns are checked in a separate thread, i.e. your server likely won't run slower when using complicated patterns.

If 'monitor changes' is set to true, variables will repeatedly be checked for updates in the database (in intervals set in 'monitor interval').

! Please note that you should set 'pattern', 'monitor changes' and 'monitor interval' to the same values on all servers that access the same database!

== MySQL configuration ==

(If the table exists but is defined differently that how Skript expects it to be you'll get errors and no variables will be saved and/or loaded)

== SQLite/CSV configuration ==

Where to save the variables to. For a CSV file, the file extension '.csv' is recommended, but not required, but SQLite database files must end in '.db' (SQLibrary forces this).

The file path can either be absolute (e.g. 'C:\whatever...' [Windows] or '/usr/whatever/...' [Unix]), or relative to the server directory (e.g. './plugins/Skript/...').

The name of the table to create. 'variables21' is the default name, if this was to be omitted.

(If the table exists but is defined differently that how Skript expects it to be you'll get errors and no variables will be saved and/or loaded)

This is generally not required as the the .db file will only be used by Skript, unless you want to split different variables into different tables

Creates a backup of the file every so often. This can be useful if you ever want to revert variables to an older state.

Variables are saved constantly no matter what is set here, thus a server crash will never make you loose any variables.

Set this to 0 to disable this feature.