Azerothcore-wotlk: Wintergarde Mine is sad and lonely

[sluggor]

There are far too few mobs and nodes spawning in Wintergarde mine in Dragonblight.

SMALL DESCRIPTION:

Not enough mobs spawning to complete "Not In Our Mine" quest.

EXPECTED BLIZZLIKE BEHAVIOUR:

There should be many more Risen Wintergarde Miners and Defenders in the mine.

CURRENT BEHAVIOUR:

There are only about 10 mobs defined in the creature table for the mine at present.

STEPS TO REPRODUCE THE PROBLEM:

1. Go to Dragonblight
2. Go into Wintergarde Mine

EXTRA NOTES:

I have been looking into this issue, and I can provide the database changes necessary to make the mine look as Blizzlike as possible, but it's going to be a tedious process. I don't mind doing it, but I want to make sure that no one else is currently working on this - I don't want to duplicate work effort here.

BRANCH(ES):

master

AC HASH/COMMIT:

0b9a0d8ea63cec42df9e19d013ca37cd08cc9613

OPERATING SYSTEM:

Windows 10

MODULES:

No

OTHER CUSTOMIZATIONS:

None

[poszer]

If you are able to fix it, feel free to do it. Also, good catch :+1:

[sluggor]

OK, cool. If no one else is currently on it, I will take care of it.

[sluggor]

Hey guys, I need a little guidance on implementing this... I have everything cleaned up - nothing livens up an abandoned mine like a boatload of undead - but the SQL is fairly complex, especially the part that cleans up the waypoint_data, character_addon, and character tables for the existing mobs.

Because the key for the waypoint_data table is only found on the character_addon table (as far as I can tell), it has to be retrieved by coming from the character table, so the delete statement is doubly nested. If someone is a SQL rockstar and can make any suggestions on how to rewrite it more efficiently, please let me know. It works, but can take over a full minute to run the big delete statements.

DELETE FROM `waypoint_data` WHERE `id` IN (SELECT `path_id` FROM `creature_addon` WHERE `guid` IN (SELECT `guid` FROM `creature` WHERE `id` = 27284));
DELETE FROM `creature_addon` WHERE `guid` IN (SELECT `guid` FROM `creature` WHERE `id` = 27284);
DELETE FROM `creature` WHERE `id` = 27284;

DELETE FROM `waypoint_data` WHERE `id` IN (SELECT `path_id` FROM `creature_addon` WHERE `guid` IN (SELECT `guid` FROM `creature` WHERE `id` = 27401));
DELETE FROM `creature_addon` WHERE `guid` IN (SELECT `guid` FROM `creature` WHERE `id` = 27401);
DELETE FROM `creature` WHERE `id` = 27401;

The first sql script removes the old ore nodes and populates the new ones.

-- Define helper functions

DROP FUNCTION IF EXISTS InsertStrangeOreNode;

DELIMITER $$

CREATE FUNCTION InsertStrangeOreNode(position_x double, position_y double, position_z double)
RETURNS decimal
DETERMINISTIC
BEGIN
    SET @RND_ORIENTATION := (RAND() / 3.3) * 10;

    INSERT INTO `gameobject`(`id`, `map`, `zoneId`, `areaId`, `spawnMask`, `phaseMask`, `position_x`, `position_y`, `position_z`, `orientation`, `rotation0`, `rotation1`, `rotation2`, `rotation3`, `spawntimesecs`, `animprogress`, `state`, `ScriptName`, `VerifiedBuild`) VALUES
    (188699, 571, 0, 0, 1, 1, position_x, position_y, position_z, @RND_ORIENTATION, 0, 0, 0, 0, 300, 100, 1, '', 0);

    SET @NEW_GUID := LAST_INSERT_ID();

    RETURN @NEW_GUID;
END $$

DELIMITER ;

-- Remove old Strange Ore nodes

DELETE FROM `gameobject` where `id` = 188699 and `map` = 571;

-- Insert Strange Ore nodes

SET @rc := InsertStrangeOreNode(3898.41, -874.94, 109.69);
SET @rc := InsertStrangeOreNode(3910.97, -868.05, 107.67);
SET @rc := InsertStrangeOreNode(3939.70, -869.39, 104.37);
SET @rc := InsertStrangeOreNode(3956.76, -864.70, 104.16);
SET @rc := InsertStrangeOreNode(3971.40, -862.84, 104.68);
SET @rc := InsertStrangeOreNode(3967.61, -902.46, 104.68);
SET @rc := InsertStrangeOreNode(3969.14, -922.30, 106.98);
SET @rc := InsertStrangeOreNode(3987.90, -902.99, 107.33);
SET @rc := InsertStrangeOreNode(4007.40, -911.82, 107.20);
SET @rc := InsertStrangeOreNode(4016.82, -885.51, 110.17);
SET @rc := InsertStrangeOreNode(4059.49, -902.67, 116.14);
SET @rc := InsertStrangeOreNode(4078.81, -907.17, 115.57);
SET @rc := InsertStrangeOreNode(4070.35, -868.52, 114.24);
SET @rc := InsertStrangeOreNode(4078.17, -855.47, 112.71);
SET @rc := InsertStrangeOreNode(4103.27, -867.37, 113.54);
SET @rc := InsertStrangeOreNode(4110.56, -847.12, 116.59);
SET @rc := InsertStrangeOreNode(4038.53, -813.69, 123.35);
SET @rc := InsertStrangeOreNode(3979.26, -877.96, 119.18);
SET @rc := InsertStrangeOreNode(3957.79, -891.33, 119.49);
SET @rc := InsertStrangeOreNode(4004.26, -781.90, 118.49);
SET @rc := InsertStrangeOreNode(4017.86, -766.48, 119.13);
SET @rc := InsertStrangeOreNode(3993.02, -789.91, 122.71);
SET @rc := InsertStrangeOreNode(3979.85, -815.04, 124.89);
SET @rc := InsertStrangeOreNode(3956.10, -839.98, 122.27);
SET @rc := InsertStrangeOreNode(3945.32, -858.50, 122.96);
SET @rc := InsertStrangeOreNode(3926.84, -850.59, 123.78);
SET @rc := InsertStrangeOreNode(3926.00, -862.90, 122.24);
SET @rc := InsertStrangeOreNode(3906.27, -870.06, 122.12);
SET @rc := InsertStrangeOreNode(4001.21, -967.61, 104.52);
SET @rc := InsertStrangeOreNode(4000.94, -938.04, 105.72);

-- Clean up helper functions

DROP FUNCTION IF EXISTS InsertStrangeOreNode;

The second one removes any old Risen Miners or Defenders, including any of their old addon or waypoint data, then populates miners and defenders, who are mining, loitering, guarding, and patrolling.

-- Define helper functions

DROP FUNCTION IF EXISTS InsertLoiteringMiner;
DROP FUNCTION IF EXISTS InsertMiningMiner;
DROP FUNCTION IF EXISTS InsertLookoutDefender;
DROP FUNCTION IF EXISTS InsertPatrollingDefender;
DROP FUNCTION IF EXISTS InsertPatrollingDefenderWaypoint;

DELIMITER $$

CREATE FUNCTION InsertLoiteringMiner(modelid integer, position_x double, position_y double, position_z double, orientation double)
RETURNS decimal
DETERMINISTIC
BEGIN

    INSERT INTO `creature`(`id`, `map`, `zoneId`, `areaId`, `spawnMask`, `phaseMask`, `modelid`, `equipment_id`, `position_x`, `position_y`, `position_z`, `orientation`, `spawntimesecs`, `spawndist`, `currentwaypoint`, `curhealth`, `curmana`, `MovementType`, `npcflag`, `unit_flags`, `dynamicflags`, `ScriptName`, `VerifiedBuild`) VALUES
    (27401, 571, 0, 0, 1, 1, modelid, 1, position_x, position_y, position_z, orientation, 300, 3, 0, 1, 0, 1, 0, 0, 0, '', 0);

    SET @NEW_GUID := LAST_INSERT_ID();

    RETURN @NEW_GUID;
END $$

DELIMITER $$

CREATE FUNCTION InsertMiningMiner(modelid integer, position_x double, position_y double, position_z double, orientation double)
RETURNS decimal
DETERMINISTIC
BEGIN

    INSERT INTO `creature`(`id`, `map`, `zoneId`, `areaId`, `spawnMask`, `phaseMask`, `modelid`, `equipment_id`, `position_x`, `position_y`, `position_z`, `orientation`, `spawntimesecs`, `spawndist`, `currentwaypoint`, `curhealth`, `curmana`, `MovementType`, `npcflag`, `unit_flags`, `dynamicflags`, `ScriptName`, `VerifiedBuild`) VALUES
    (27401, 571, 0, 0, 1, 1, modelid, 1, position_x, position_y, position_z, orientation, 300, 0, 0, 1, 0, 0, 0, 0, 0, '', 0);

    SET @NEW_GUID := LAST_INSERT_ID();

    INSERT INTO `creature_addon`(`guid`, `path_id`, `mount`, `bytes1`, `bytes2`, `emote`, `auras`) VALUES
    (@NEW_GUID, 0, 0, 0, 0, 173, NULL);

    RETURN @NEW_GUID;
END $$

DELIMITER $$

CREATE FUNCTION InsertLookoutDefender(modelid integer, position_x double, position_y double, position_z double, orientation double)
RETURNS decimal
DETERMINISTIC
BEGIN

    INSERT INTO `creature`(`id`, `map`, `zoneId`, `areaId`, `spawnMask`, `phaseMask`, `modelid`, `equipment_id`, `position_x`, `position_y`, `position_z`, `orientation`, `spawntimesecs`, `spawndist`, `currentwaypoint`, `curhealth`, `curmana`, `MovementType`, `npcflag`, `unit_flags`, `dynamicflags`, `ScriptName`, `VerifiedBuild`) VALUES
    (27284, 571, 0, 0, 1, 1, modelid, 1, position_x, position_y, position_z, orientation, 300, 2, 0, 1, 0, 1, 0, 0, 0, '', 0);

    SET @NEW_GUID := LAST_INSERT_ID();

    RETURN @NEW_GUID;
END $$

DELIMITER $$

CREATE FUNCTION InsertPatrollingDefender(modelid integer, position_x double, position_y double, position_z double, orientation double)
RETURNS decimal
DETERMINISTIC
BEGIN

    INSERT INTO `creature`(`id`, `map`, `zoneId`, `areaId`, `spawnMask`, `phaseMask`, `modelid`, `equipment_id`, `position_x`, `position_y`, `position_z`, `orientation`, `spawntimesecs`, `spawndist`, `currentwaypoint`, `curhealth`, `curmana`, `MovementType`, `npcflag`, `unit_flags`, `dynamicflags`, `ScriptName`, `VerifiedBuild`) VALUES
    (27284, 571, 0, 0, 1, 1, modelid, 1, position_x, position_y, position_z, orientation, 300, 2, 1, 1, 0, 2, 0, 0, 0, '', 0);

    SET @NEW_GUID := LAST_INSERT_ID();

    SELECT MAX(`id`) INTO @NEW_PATHID FROM `waypoint_data`;

    SET @NEW_PATHID := @NEW_PATHID + 1;

    INSERT INTO `creature_addon`(`guid`, `path_id`, `mount`, `bytes1`, `bytes2`, `emote`, `auras`) VALUES
    (@NEW_GUID, @NEW_PATHID, 0, 0, 1, 0, NULL);

    RETURN @NEW_PATHID;
END $$

DELIMITER $$

CREATE FUNCTION InsertPatrollingDefenderWaypoint(path_id integer, delay integer, position_x double, position_y double, position_z double, orientation double)
RETURNS decimal
DETERMINISTIC
BEGIN
    SELECT MAX(`point`) INTO @NEW_POINT FROM `waypoint_data` WHERE `id` = path_id;

    IF @NEW_POINT IS NULL THEN
        SET @NEW_POINT := 0;
    END IF;

    SET @NEW_POINT := @NEW_POINT + 1;

    INSERT INTO `waypoint_data`(`id`, `point`, `position_x`, `position_y`, `position_z`, `orientation`, `delay`, `move_type`, `action`, `action_chance`, `wpguid`) VALUES
    (path_id, @NEW_POINT, position_x, position_y, position_z, orientation, delay, 0, 0, 100, 0);

    SET @NEW_ID := LAST_INSERT_ID();

    RETURN @NEW_ID;
END $$

DELIMITER ;

-- Remove old Risen Wintergarde Defender and Miner data

DELETE FROM `waypoint_data` WHERE `id` IN (SELECT `path_id` FROM `creature_addon` WHERE `guid` IN (SELECT `guid` FROM `creature` WHERE `id` = 27284));
DELETE FROM `creature_addon` WHERE `guid` IN (SELECT `guid` FROM `creature` WHERE `id` = 27284);
DELETE FROM `creature` WHERE `id` = 27284;

DELETE FROM `waypoint_data` WHERE `id` IN (SELECT `path_id` FROM `creature_addon` WHERE `guid` IN (SELECT `guid` FROM `creature` WHERE `id` = 27401));
DELETE FROM `creature_addon` WHERE `guid` IN (SELECT `guid` FROM `creature` WHERE `id` = 27401);
DELETE FROM `creature` WHERE `id` = 27401;


-- Insert Risen Wintergarde Miners who are loitering about

SET @rc := InsertLoiteringMiner(0, 3972.65, -818.15, 124.04, 0);
SET @rc := InsertLoiteringMiner(0, 4014.65, -809.00, 122.39, 0);
SET @rc := InsertLoiteringMiner(0, 4033.54, -842.57, 117.08, 0);
SET @rc := InsertLoiteringMiner(0, 4052.87, -879.22, 113.94, 0);
SET @rc := InsertLoiteringMiner(0, 4071.10, -909.27, 115.13, 0);

-- Insert Risen Wintergarde Miners who are actually working

SET @rc := InsertMiningMiner(0, 3897.89, -873.10, 109.21, 4.86);
SET @rc := InsertMiningMiner(0, 3910.60, -870.31, 107.37, 1.54);
SET @rc := InsertMiningMiner(0, 3938.72, -871.10, 103.57, 0.84);
SET @rc := InsertMiningMiner(0, 3971.04, -921.01, 106.30, 3.76);
SET @rc := InsertMiningMiner(0, 3998.63, -939.91, 105.56, 0.69);
SET @rc := InsertMiningMiner(0, 4001.73, -965.54, 104.77, 4.35);
SET @rc := InsertMiningMiner(0, 4009.08, -910.50, 106.71, 3.91);
SET @rc := InsertMiningMiner(0, 4077.82, -857.51, 111.64, 1.48);
SET @rc := InsertMiningMiner(0, 4102.12, -865.93, 112.80, 5.43);
SET @rc := InsertMiningMiner(0, 4108.65, -847.96, 115.29, 0.34);
SET @rc := InsertMiningMiner(0, 3979.84, -879.73, 118.40, 1.73);
SET @rc := InsertMiningMiner(0, 4037.43, -815.65, 122.41, 1.11);
SET @rc := InsertMiningMiner(0, 3957.36, -841.87, 121.36, 2.31);
SET @rc := InsertMiningMiner(0, 3925.18, -861.44, 121.77, 5.13);
SET @rc := InsertMiningMiner(0, 3924.98, -851.58, 122.35, 0.40);

-- Insert Risen Wintergarde Defenders who are on lookout

SET @rc := InsertLookoutDefender(0, 3878.03, -885.89, 119.57, 0);
SET @rc := InsertLookoutDefender(0, 3892.26, -901.20, 116.55, 0);
SET @rc := InsertLookoutDefender(0, 3998.27, -949.00, 105.44, 0);

-- Insert Risen Wintergarde Defenders who are on patrol

SET @NEW_PATHID := InsertPatrollingDefender(0, 3925.01, -855.59, 121.03, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 5000, 3925.01, -855.59, 121.03, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 0, 3942.16, -854.97, 122.45, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 0, 3958.25, -847.39, 120.89, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 5000, 3967.60, -836.33, 122.32, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 0, 3958.25, -847.39, 120.89, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 0, 3942.16, -854.97, 122.45, 0);

SET @NEW_PATHID := InsertPatrollingDefender(0, 3978.23, -805.63, 123.69, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 5000, 3978.23, -805.63, 123.69, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 0, 3995.50, -797.23, 122.35, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 0, 4006.58, -790.32, 118.38, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 5000, 4014.53, -772.76, 118.77, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 0, 4006.58, -790.32, 118.38, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 0, 3995.50, -797.23, 122.35, 0);

SET @NEW_PATHID := InsertPatrollingDefender(0, 4025.68, -843.86, 116.91, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 5000, 4025.68, -843.86, 116.91, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 0, 4018.41, -854.82, 117.35, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 0, 4007.88, -868.25, 117.17, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 5000, 3990.07, -879.86, 118.23, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 0, 4007.88, -868.25, 117.17, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 0, 4018.41, -854.82, 117.35, 0);

SET @NEW_PATHID := InsertPatrollingDefender(0, 4051.13, -866.23, 113.16, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 5000, 4051.13, -866.23, 113.16, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 0, 4070.88, -860.97, 111.96, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 0, 4084.76, -861.35, 111.93, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 5000, 4093.75, -857.18, 113.17, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 0, 4084.76, -861.35, 111.93, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 0, 4070.88, -860.97, 111.96, 0);

SET @NEW_PATHID := InsertPatrollingDefender(0, 3895.75, -869.76, 108.80, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 5000, 3895.75, -869.76, 108.80, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 0, 3904.82, -871.95, 108.41, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 0, 3922.05, -872.61, 105.45, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 0, 3936.30, -870.95, 103.44, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 0, 3951.62, -879.64, 104.64, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 5000, 3963.98, -868.77, 103.98, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 0, 3951.62, -879.64, 104.64, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 0, 3936.30, -870.95, 103.44, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 0, 3922.05, -872.61, 105.45, 0);
SET @NEW_ID := InsertPatrollingDefenderWaypoint(@NEW_PATHID, 0, 3904.82, -871.95, 108.41, 0);

-- Clean up helper functions

DROP FUNCTION IF EXISTS InsertLoiteringMiner;
DROP FUNCTION IF EXISTS InsertMiningMiner;
DROP FUNCTION IF EXISTS InsertLookoutDefender;
DROP FUNCTION IF EXISTS InsertPatrollingDefender;
DROP FUNCTION IF EXISTS InsertPatrollingDefenderWaypoint;

I've tested it by doing the the quests "The Bleeding Ore" (collecting ore from the new nodes) and "Not In Our Mine" (collect miner cards from miner mobs), and everything looks good.

Is there a checklist or guide somewhere that talks about how to get SQL scripts checked in?

Sorry for being a noob, but I definitely don't want to break anything!

[deadmanevil]

--
UPDATE creature SET MovementType=0, spawndist=0 WHERE id IN (27401,27403);
DELETE FROM creature_template_addon WHERE entry=27401;
INSERT creature_template_addon (entry,path_id,mount,bytes1,bytes2,emote,auras) VALUES (27401,0,0,0,1,233 , '');

[deadmanevil]

can this other sql also be needed

[sluggor]

No, because we want different instances of creatures doing different things.

27401 is the Risen Wintergarde Miner, and the InsertMiningMiner() function is taking care of their addon tables. The other 27401 instances are roaming in their spawn radii, so the InsertLoiteringMiner() function doesn't need to worry about addon emotes.

27403 is a "Strange Ore Target", which I think is incorrect - it should really be gameobject 188699.

27284, which is the Risen Wintergarde Defender, is either roaming within a spawn radius or patrolling a path defined in the waypoint_data table.

Each of the helper functions is populating the tables relevant the the creature instance.