azerothcore
Urgent database integrity issue - smart_scripts
SMALL DESCRIPTION:
The smart_scripts table uses a funky key scheme that may be causing problems in related tables - creature_template, creature, gameobject_template, gameobject, etc.
EXPECTED BLIZZLIKE BEHAVIOUR:
Auto-generated or specified keys should be sequential (with no or extremely few gaps).
CURRENT BEHAVIOUR:
There are very large jumps in key values in several core tables. These excerpts are from the master branch SQL files.
creature_template.sql
...
(43280,0,0,0,0,0,169,11686,0,0,'Orb Rotation Focus (1)','','',0,80,80,0,14,0,2.2,0.785714,1,0,0,0,0,0,1,2000,2000,1,33554688,2048,0,0,0,0,0,0,0,0,0,10,1048576,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'',0,3,1,1,1,1,0,98,1,0,130,'',12340),
(43281,0,0,0,0,0,169,11686,0,0,'Orb Rotation Focus (2)','','',0,80,80,0,14,0,2.2,0.785714,1,0,0,0,0,0,1,2000,2000,1,33554688,2048,0,0,0,0,0,0,0,0,0,10,1048576,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'',0,3,1,1,1,1,0,118,1,0,130,'',12340),
(43282,0,0,0,0,0,169,11686,0,0,'Orb Rotation Focus (3)','','',0,80,80,0,14,0,2.2,0.785714,1,0,0,0,0,0,1,2000,2000,1,33554688,2048,0,0,0,0,0,0,0,0,0,10,1048576,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'',0,3,1,1,1,1,0,118,1,0,130,'',12340),
(70100,0,0,0,0,0,11686,0,0,0,'Conversing With the Depths Trigger','','',0,1,1,0,35,0,1,1.14286,1,0,1,1,0,0,1,0,0,1,6,2048,0,0,0,0,0,0,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'',0,3,1,1,1,1,0,0,1,0,130,'npc_conversing_with_the_depths_trigger',1),
(3460602,0,0,0,0,0,25144,0,0,0,'Frost Sphere (2)','','',0,79,80,2,1925,0,1,1.14286,1,0,422,586,0,0,1,0,0,1,0,2048,8,0,0,0,0,0,345,509,103,8,64,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'',0,5,1,0.238095,1,1,0,721,0,0,0,'',1),
(3460603,0,0,0,0,0,25144,0,0,0,'Frost Sphere (3)','','',0,79,80,2,1925,0,1,1.14286,1,0,422,586,0,0,1,0,0,1,0,2048,8,0,0,0,0,0,345,509,103,8,64,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'',0,5,1,0.238095,1,1,0,721,0,0,0,'',1);
/*!40000 ALTER TABLE `creature_template` ENABLE KEYS */;
UNLOCK TABLES;
...
creature.sql
...
(248592,25926,530,0,0,1,1,0,0,2286.71,6137.62,136.306,1.5708,120,0,0,1,0,0,0,0,0,'',0),
(248593,25913,530,0,0,1,1,0,0,3127.55,3743.15,141.771,6.16101,120,0,0,1,0,0,0,0,0,'',0),
(248594,25918,530,0,0,1,1,0,0,2917.22,3693.41,143.77,2.93215,120,0,0,1,0,0,0,0,0,'',0),
(1954836,22911,530,0,0,1,1,0,0,3279.68,4640.43,216.526,6.2009,600,0,0,97800,7196,0,0,0,0,'',0),
(1955014,30474,571,0,0,1,256,0,0,7975.86,-2724.91,1137.35,4.2892,60,0,0,63000,19970,0,0,0,0,'',0),
(1955077,30295,571,0,0,1,1,0,0,7761.1,-2160.86,1233.33,1.47162,300,0,0,13481,0,0,0,0,0,'',0),
...
The integrity problem will arise when creatures and objects with these higher entry and guid values create smart_scripts entries with timed action lists, which are defined as entryOrGuid * 100 (see smart_scripts | AzerothCore ).
My guess is that these timed action lists are confusing people or processes that are then somehow causing the next guid in creature or entry in creature_template to be the next available number after <key_value> * 100 in smart_scripts.
As an example, let's say we decide to add a smart script to that Frost Sphere (3) in the first listing - it will be smart_scripts.entryOrGuid = 3,460,603. If we then add a timed action list, it will be smart_scripts.entryOrGuid = 346,060,300 (3,460,603 * 100).
Since smart_scripts.entryOrGuid is a signed int field (negative for guid values and positive for entry values), the max positive value it can hold is 2,147,483,647. If a problem like this happens once more afterwards, it would push the key value over 34,606,030,000.
I think it would be a good idea to break out timed action scripts into their own table and restore the proper key value progressions in the affected tables. In the meantime, we should be very careful with smart_scripts and jumping key values.
STEPS TO REPRODUCE THE PROBLEM:
- Examine your local database or browse the master branch base SQL files
EXTRA NOTES:
BRANCH(ES):
master
AC HASH/COMMIT:
2ff5c5f8c78d5d369ea151d68859a881e3ab16ce
OPERATING SYSTEM:
All
MODULES:
No
OTHER CUSTOMIZATIONS:
No
@sluggor thanks for finding this out. Have you also investigated if on TC there is the same issue?
I just took a quick look, but I can't find their SQL data load files - are they on GitHub? There is a dev section with a revised smart_scripts table definition:
...
DROP TABLE IF EXISTS `smart_scripts`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `smart_scripts` (
`entryorguid` bigint(20) NOT NULL DEFAULT '0',
`source_type` tinyint(3) unsigned NOT NULL DEFAULT '0',
`id` smallint(5) unsigned NOT NULL DEFAULT '0',
`link` smallint(5) unsigned NOT NULL DEFAULT '0',
`event_type` tinyint(3) unsigned NOT NULL DEFAULT '0',
...
Redefining the key as a signed BIGINT will give them a max positive value of 9,223,372,036,854,775,807, so maybe they already ran into this at some point :).
Based on this update SQL file from a few hours ago, the TC creature IDs look good: https://github.com/TrinityCore/TrinityCore/blob/3.3.5/sql/updates/world/3.3.5/2019_06_06_10_world.sql
Does TC load a world database from a bunch of SQL files like AC does? I still can't find a set of loader files anywhere, just auth and character databases with minimal inserts...
TC 3.3.5 uses int(11) for entryorguid, just as AC does, see their latest world DB: https://github.com/TrinityCore/TrinityCore/releases/tag/TDB335.19051
I also don't think this is much of a problem, as you'll get an "out of range" error if the value is too big for the DB. We should just be careful about the IDs / GUIDs, they must not get any higher. But there are lots of big gaps available anyway if new IDs are necessary. Personally I'd say to stay below 190,000 for new IDs in "creature_template" and below 4,000,000 for GUIDs in "creature". The maximum entryorguid will then be 400,000,000 when using SAI timed action lists, which is perfectly in range.
@sluggor did you talk about this with @FrancescoBorzi ? is this issue still relevant?
