Server crash 3.3.5-solocraft - Error while parsing SQL

[kruchor]

Description:

I usually keep my server up-to-date with the 3.3.5-solocraft branch + Trinity-Bots (https://github.com/trickerer/Trinity-Bots)/ on a monthly basis. Everything is fine but I have server crash since recent updates (I don't know precisely since when), some SQL syntax errors. I thought it was due to NPCBots, that's why I've reinstalled a fresh Solocraft TC core. The issue still happens, so it seems to be related to 3.3.5-solocraft branch. It seems some SQL query is wrong and/or SQL table structure.

I'll also try to see on my side if I'm able to resolve the issue.

Thx !

Current behaviour:

Log in server : OK Log into world : OK but character can't move and client is disconnected

Debug log

TC> [1064] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Full: 0x0000000000000001 Type: Player Low: 1' at line 1
Error while parsing SQL. Core fix required.

/home/wotlk/TC_3.3.5-solocraft/src/server/database/Database/MySQLConnection.cpp:610 in _HandleMySQLErrno ABORTED.
Segmentation fault

https://gist.github.com/kruchor/6521cff092a8624148c963b85332561c

Expected behaviour:

Disconnection should not happen.

Steps to reproduce the problem:

1. git clone -b 3.3.5-solocraft https://github.com/TrinityCore/TrinityCoreCustomChanges.git TC_3.3.5-solocraft/
2. compile
3. create fresh account / character
4. login into world => crash

Branch(es):

TrinityCore rev. bdab574c5079 2024-08-19 01:28:18 +0000 (3.3.5-solocraft branch) (Unix, Debug, Static) (worldserver-daemon) ready... TC> server debug TC> TrinityCore rev. bdab574c5079 2024-08-19 01:28:18 +0000 (3.3.5-solocraft branch) (Unix, Debug, Static) Using SSL version: OpenSSL 3.0.13 30 Jan 2024 (library: OpenSSL 3.0.13 30 Jan 2024) Using Boost version: 1.74.0 Using MySQL version: 101106 Using CMake version: 3.25.1 Compiled on: Linux 6.1.0-21-amd64 Automatic database updates are enabled for the following databases: Auth, Characters, World Worldserver listening connections on port {} Realmlist (Realm Id: 1) configured in port 8085 VMAPs status: Enabled. LineOfSight: 1, getHeight: 1, indoorCheck: 1 MMAPs status: Enabled maps directory located in ../../data/maps. Total size: 291014951 bytes vmaps directory located in ../../data/vmaps. Total size: 1326890216 bytes mmaps directory located in ../../data/mmaps. Total size: 2019979420 bytes Using enUS DBC Locale as default. All available DBC locales: enUS Using World DB: TDB 335.24081 LoginDatabase queue size: 0 CharacterDatabase queue size: 0 WorldDatabase queue size: 0

TC rev. hash/commit:

TrinityCore rev. bdab574c5079 2024-08-19 01:28:18 +0000 (3.3.5-solocraft branch) (Unix, Debug, Static) (worldserver-daemon)

TDB version:

TDB335.24081

Operating system:

root@wotlkVM-dev:~# cat /etc/debian_version ; echo ;dpkg -l|grep mariadb-server|grep -v rc 12.5

ii mariadb-server 1:10.11.6-0+deb12u1 amd64 MariaDB database server binaries ii mariadb-server-core 1:10.11.6-0+deb12u1 amd64 MariaDB database core server files

[acidmanifesto]

Apply the sql to the character database https://github.com/TrinityCore/TrinityCoreCustomChanges/blob/12b30b99f9b5f5c30a1779beacd96e5ddeb5d4df/sql/custom/characters/characters.solocraft.sql

[kruchor]

Hello,

This is weird as I've already applied this SQL :

MariaDB [characters2]> desc custom_solocraft_character_stats ;
+------------+---------------------+------+-----+---------+-------+
| Field      | Type                | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+-------+
| GUID       | bigint(20) unsigned | NO   | PRI | NULL    |       |
| Difficulty | float               | NO   |     | NULL    |       |
| GroupSize  | int(11)             | NO   |     | NULL    |       |
| SpellPower | int(10) unsigned    | NO   |     | 0       |       |
| Stats      | float               | NO   |     | 100     |       |
+------------+---------------------+------+-----+---------+-------+
5 rows in set (0.001 sec)

If I run the SQL provided in the dumpfile , I got the same error than the one throwed out by worldserver :

MariaDB [characters2]> SELECT `GUID`, `Difficulty`, `GroupSize`, `SpellPower`, `Stats` FROM `custom_solocraft_character_stats` WHERE GUID = "GUID Full: 0x", '0' <repeats 15 times>, "1 Type: Player Low: 1" ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' '0' <repeats 15 times>, "1 Type: Player Low: 1"' at line 1

[acidmanifesto]

I knkw mariadb is not supported on tc and i have no issues in my end testing it with mysql8.

[kruchor]

I tried with MySQL 8, I have the same issue :

root@testmysql8:/tmp# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.39 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use characters2 ;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT `GUID`, `Difficulty`, `GroupSize`, `SpellPower`, `Stats` FROM `custom_solocraft_character_stats` WHERE GUID = "GUID Full: 0x", '0' <repeats 15 times>, "1 Type: Player Low: 1" ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', '0' <repeats 15 times>, "1 Type: Player Low: 1"' at line 1

[kruchor]

I have a working trinitycore solocraft server with the following :

commit fdc6401f8bdc6ea9842bb94cbc2e585d4423810b (HEAD -> 3.3.5-solocraft, origin/3.3.5-solocraft)
Merge: 656fbaad83 45c579eb9d
Author: Github Actions <github.actions@build.bot>
Date:   Mon May 27 01:20:46 2024 +0000

    Merge 3.3.5 to 3.3.5-solocraft

TC> TC> server debug
TC> TrinityCore rev. fdc6401f8bdc+ 2024-05-27 01:20:46 +0000 (3.3.5-solocraft branch) (Unix, RelWithDebInfo, Static)
Using SSL version: OpenSSL 3.0.11 19 Sep 2023 (library: OpenSSL 3.0.11 19 Sep 2023)
Using Boost version: 1.74.0
Using MySQL version: 101106
Using CMake version: 3.25.1
Compiled on: Linux 6.1.0-21-amd64
Automatic database updates are enabled for the following databases: Auth, Characters, World
Worldserver listening connections on port {}
Realmlist (Realm Id: 1) configured in port 8085
VMAPs status: Enabled. LineOfSight: 1, getHeight: 1, indoorCheck: 1
MMAPs status: Enabled
maps directory located in ../../data/maps. Total size: 291014951 bytes
vmaps directory located in ../../data/vmaps. Total size: 1326890216 bytes
mmaps directory located in ../../data/mmaps. Total size: 2019979420 bytes
Using enUS DBC Locale as default. All available DBC locales: enUS
Using World DB: TDB 335.24041
LoginDatabase queue size: 0
CharacterDatabase queue size: 0
WorldDatabase queue size: 0

I try to determine since which commit the issue appeared ...

[kruchor]

I built a fresh 3.3.5-solocraft server with MySQL 8. Now I don't have any error on login, but I got one on disconnect :

TC> TrinityCore rev. 252f70f9f590 2024-08-26 01:27:15 +0000 (3.3.5-solocraft branch) (Unix, RelWithDebInfo, Static)
Online players: 1 (max: 1)
Active connections: 1 (max: 1) Queued connections: 0 (max: 0)
Server uptime: 23 Seconds.
Update time diff: 1.
TC>
TC>
TC> [1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Full: 0x0000000000000001 Type: Player Low: 1' at line 1
Error while parsing SQL. Core fix required.

TC>
TC>
TC>
/home/trinity/3.3.5-solocraft/src/server/database/Database/MySQLConnection.cpp:610 in _HandleMySQLErrno ABORTED.
Segmentation fault

[kruchor]

I try to determine since which commit the issue appeared ...

The error appears since :

TrinityCore rev. 9215cc40b78f 2024-08-12 01:27:30

commit 9215cc40b78fac52adaea5b7d164052a828b585e (HEAD -> 3.3.5-solocraft)
Merge: bbb99d1f81 5f58619c7b
Author: Github Actions <github.actions@build.bot>
Date:   Mon Aug 12 01:27:30 2024 +0000

    Merge 3.3.5 to 3.3.5-solocraft

[Aokromes]

i don't see nothing around that date that made the query invalid.

[kruchor]

Neither do I ...

I might have something fancy in my setup but I doubt it ... I'll make some further tests.

[kruchor]

The issue might be in the output returned by these queries :

trinity@wotlkVM-dev:~/TC_3.3.5-solocraft$ grep SELECT ./src/server/scripts/Custom/Solocraft.cpp
                QueryResult result = CharacterDatabase.PQuery("SELECT `GUID`, `Difficulty`, `GroupSize`, `SpellPower`, `Stats` FROM `custom_solocraft_character_stats` WHERE GUID = {}", player->GetGUID());
                    QueryResult result = CharacterDatabase.PQuery("SELECT `GUID`, `Difficulty`, `GroupSize` FROM `custom_solocraft_character_stats` WHERE GUID = {}", itr->guid);
        QueryResult result = CharacterDatabase.PQuery("SELECT `GUID`, `Difficulty`, `GroupSize`, `SpellPower`, `Stats` FROM `custom_solocraft_character_stats` WHERE GUID = {}", player->GetGUID());

Something may have to be updated ?

I've performed an other fresh install with latest 3.3.5-solocraft and I still can't figure out why I run into the issue :/

[kruchor]

Thank you, I was just on that lead too, you were quicker/sharper than I !

I'm compiling right now.

[kruchor]

Compilation OK, log in OK.

Getting in instance (in a group) lead to crash. Being solo is OK. I think there might be a modification missing on line 528 in Solocraft.cpp?

    526             {
    527                 //Exclude player from the tally because the player is the one entering the dungeon
    528                 if (itr->guid != player->GetGUID)
    529                 {
    530                     //Database query to find difficulty for each group member that is currently in an instance
    531                     QueryResult result = CharacterDatabase.PQuery("SELECT `GUID`, `Difficulty`, `GroupSize` FROM `custom_solocraft_character_stats` WHERE GUID = {}", itr->guid);

[acidmanifesto]

Compilation OK, log in OK.

Getting in instance (in a group) lead to crash. Being solo is OK. I think there might be a modification missing on line 528 in Solocraft.cpp?

    526             {
    527                 //Exclude player from the tally because the player is the one entering the dungeon
    528                 if (itr->guid != player->GetGUID)
    529                 {
    530                     //Database query to find difficulty for each group member that is currently in an instance
    531                     QueryResult result = CharacterDatabase.PQuery("SELECT `GUID`, `Difficulty`, `GroupSize` FROM `custom_solocraft_character_stats` WHERE GUID = {}", itr->guid);

Was discussed briefly. Not missing, not needed. Actually i misread. Let me go back and check

[jackpoz]

https://github.com/TrinityCore/TrinityCoreCustomChanges/pull/141