[AC] [Bug] [Core] Data table storage error occurred after updating the latest version

[moncat2005]

DO NOT REMOVE OR SKIP THE ISSUE TEMPLATE

• I understand that my issue may get closed without notice if I intentionally remove or skip any mandatory* field

Current behaviour

After updating to the latest version, some players have reported that their progress in the character game cannot be saved, resulting in lost items.
Upon checking the server errorlog , the following error was found:
2024-11-28 00:23:49 %-5s [sql.sql] [1452] Cannot add or update a child row: a foreign key constraint fails (acore_characters.characters_npcbot_gear_storage, CONSTRAINT characters_npcbot_gear_storage_ibfk_1 FOREIGN KEY (item_guid) REFERENCES item_instance (guid) ON DELETE CASCADE ON UPDAT)
2024-11-28 00:23:49 %-5s [sql.sql] Unhandled MySQL errno 1452. Unexpected behaviour possible.

Perhaps it's related to the recently updated maximum limit for equipment banks?
Could it be an error caused by the player's gear bank having exceeded the maximum configured number of items?

Expected behaviour

No response

Crashlog

No response

Steps to reproduce the problem

No response

TrinityCore or AzerothCore

AzerothCore

Core rev. hash/commit

AzerothCore rev. a88b9b5 2024-11-26 15:39:27 +0800 (npcbots_3.35 branch)

Operating system

ubuntu 22.04

[trickerer]

No, it's not related. Gear storage capacity doesn't affect the data stored in DB tables.
This error means there is an entry in characters_npcbot_gear_storage with item guid not present in item_instance table which is not possible without manual modification.
The only thing that can be done is to remove invalid entries.

DELETE FROM characters_npcbot_gear_storage WHERE item_guid NOT IN (SELECT guid FROM item_instance);

[moncat2005]

I tried executing the SQL command
SELECT * FROM characters_npcbot_gear_storage WHERE item_guid NOT IN (SELECT guid FROM item_instance);
The result returns 0

[trickerer]

Do you have more details on what exact values were passed in the SQL statement? If not, try setting your Appender.DBErrors to debug level (5) as well as your Logger.sql.sql to info level (4) and make sure it uses DBErrors appender so before the SQL error you also have the errorring SQL statement displayed like this:

SQL: INSERT INTO characters_npcbot_gear_storage (guid, item_guid) VALUES (4, 422)
[1452] Cannot add or update a child row: a foreign key constraint fails (`characters`.`characters_npcbot_gear_storage`, CONSTRAINT `characters_npcbot_gear_storage_ibfk_1` FOREIGN KEY (`item_guid`) REFERENCES `item_instance` (`guid`) ON DELETE CASCADE ON UPDAT)
Unhandled MySQL errno 1452. Unexpected behaviour possible.
Transaction aborted. 8 queries not executed.

Example config

Appender.DBErrors=2,5,0,DBErrors.log
Logger.sql.sql=4,Console DBErrors

Now after you get the proper output check if item_guid is garbage (no near guids present in item_instance table, value way higher than any existing ones)

The problem here is that items gets deleted while in bot gear storage so at the point of saving it no longer exists in item_instance table. What I need to know is if it's memory was freed or not