Fyre's How-To Videos for Database Fixes

[Fyre]

Hello everyone!

Over the past couple of weeks, I have begun to tackle some database fixes for the MaNGOS Three core (Cataclysm). I wanted to share with you the process in how each one was accomplished. Hopefully this helps you to learn some new techniques and can try applying them to improve the MaNGOS projects!

 

I'll post the link to the video, and a written description below for each one as well. Each week (hopefully), I'll upload another one, and continue to post them here.

 

I'm usually hanging around the MaNGOS Discord if you ever want to chat, work on something together, or get some ideas.

 

Thanks!
Fyre 🔥

Quest Fixes

• Sauranok Will Point The Way - Quest Text

 

NPC Fixes

• Auctioneer Drezmit's Gossip Window Fix
• Echo Island Tiki Targets Are Chasing Me!

Edited May 25, 2021 by Fyre

[Fyre]

Auctioneer Drezmit's Gossip Window Fix

When clicking on the auctioneers to open the auction house, Auctioneer Drezmit would open up a gossip dialog window.

Based on a comparison of him and his three neighbours (using the ingame chat command: .npc info), I determined that the NpcFlag was probably the culprit. I did a nice quick comparison in my mangos_world3 database.

SELECT `Entry`, `Name`, NpcFlags` FROM `creature_template` WHERE `Entry` IN (44865, 44866, 44867, 44868);

The output was as such:

+----------+---------------------+-----------+
|  Entry   |  Name               |  NpcFlags |
+----------+---------------------+-----------+
|  44865   |  Auctioneer Fazdran |  2097152  |
|  44866   |  Auctioneer Drezmit |  2097155  |
|  44867   |  Auctioneer Ralinza |  2097152  |
|  44868   |  Auctioneer Xifa    |  2097152  |
+----------+---------------------+-----------+

I used the creature template documentation (NpcFlags) to determine what the NpcFlags meant. The best way is to start with the largest value that fits into the flag.
With a flag of 2097152, there is a perfect fitting "2097152" flag which is for: UNIT_NPC_FLAG_AUCTIONEER. Well - that makes sense for Fazdran, Ralinza, and Xifa.

Drezmit however has some flags left over.
2097155 - 2097152 = 3
The next largest flag that fits into there is "2" which is for: UNIT_NPC_FLAG_QUESTGIVER, which, as I'm sure you have already guessed... is for quest givers.

That leaves us with: 3 - 2 = 1. And now we can fit one last flag into there with a value of "1": UNIT_NPC_FLAG_GOSSIP.

Well, I've never heard of auctioneers handling out quests or providing gossip. So I was pretty sure that I could change Drezmit's NpcFlags to 2097152.

"Fun" story about this one. I had originally submitted a fix to the GitHub repo which removed the extra NPCFlags for quest giver and gossip, only to have a recurring thought in the back of my head... "check the quests." So I needed to find a quest that was started by Drezmit in order to confirm this.

I used the quest_relations table to find this.

SELECT * FROM `quest_relations` WHERE `entry` = 44866;

The output was:

+-------+-------+-------+------+
| actor | entry | quest | role |
+-------+-------+-------+------+
|     0 | 44866 | 29416 |    1 |
|     0 | 44866 | 29425 |    0 |
+-------+-------+-------+------+

Role of 0 = starts a quest, Role of 1 = ends a quest. So we know that he starts quest #29425 and ends quest #29416. For the purpose of this tutorial, it doesn't really matter what those quests are - we just needed to know if he was Quest Giver or not.

So I had to provide a 2nd pull request on Github to fix this mistake.

My final SQL fix was:

UPDATE `creature_template` SET `NpcFlags` = 2097154 WHERE `Entry` = 44866;

NpcFlags value of 2097154 = Quest Giver + Auctioneer. So in the end, we just removed the Gossip flag.
I did go back and test a few things to make sure his behaviour was still correct when the quest was available and after it was completed. Things seemed appropriate, so I felt comfortable with that final fix.

[Fyre]

Echo Island Tiki Targets Are Chasing Me!

When attacking these Tiki Targets on Echo Isles, they were moving around towards the player. That's incorrect behaviour - they should remain stationary.

I began by using the ingame chat command: ".npc info" to get the creature Entry #38038.

Now I could go into my database and begin looking to see what the UnitFlags were (this is where I suspected I would need to apply my fix).

SELECT `Entry`, `Name`, `UnitFlags` FROM `creature_template` WHERE `Entry` = 38038;

The output was:

+-------+-------------+-----------+
| Entry | Name        | UnitFlags |
+-------+-------------+-----------+
| 38038 | Tiki Target |    393216 |
+-------+-------------+-----------+

Time to go to the creature template documentation for UnitFlags.
Starting with the UnitFlags value, let's subtract the largest possible flag.
393216 - 262144 (UNIT_FLAG_STUNNED) = 131072
131072 - 131072 (UNIT_FLAG_PACIFIED) = 0

Stunned and Pacified made sense to me. I did do some comparisons with other Target Dummies (who also stay stationary when attacked), they - most of the time - had the same flags. So I guess the flags weren't the problem this time.

I did do a lot of trial and error with numerous different strategies, values, etc. and finally the thing that worked was... AIName (see documentation here).

SELECT `Entry`, `Name`, `AIName` FROM `creature_template` WHERE `Entry` = 38038;

The output was: 

+-------+-------------+--------+
| Entry | Name        | AIName |
+-------+-------------+--------+
| 38038 | Tiki Target |        |
+-------+-------------+--------+

I didn't really like the description for any of the AIName values in the documentation. The one that sounded most correct was NullAI --> Do nothing. Same as empty string. 
> Do nothing... that sounds perfect!
> Same as empty string... but I already have an empty string?

I decided to try putting NullAI into there - couldn't hurt, right?

IT WORKED! It actually worked!

My final SQL fix for this was:

UPDATE `creature_template` SET `AIName` = "NullAI" WHERE `Entry` = 38038;

 

Edited May 25, 2021 by Fyre

[Fyre]

Sauranok Will Point The Way - Quest Text

When going in to complete this quest, Sauranok had very strange text. It only read: "mage."
It wasn't capitalized, it didn't have any structure to it, etc.

First I needed to find the quest id. I used the SQL 'LIKE' statement to search with wildcards - I could have just done it with an = and no %'s... but I wanted to show you some other methods. I could have gone LIKE "%Sauranok%", and it may have given me more options in the output that I would need to pick from.

SELECT `Entry`, `Title` FROM `quest_template` WHERE `Title` LIKE "%Sauranok Will Point The Way%";

The output was:

+-------+-----------------------------+
| Entry | Title                       |
+-------+-----------------------------+
| 28909 | Sauranok Will Point the Way |
+-------+-----------------------------+

Okay - so Quest ID/Entry = 28909.

Because I couldn't remember the correct column name for the part of the window that was showing me "mage.", I needed to do the following:

SELECT * FROM `quest_template` WHERE `Entry` = 28049;

I'm not going to post the output this time, because it would be very long and messy. But I noticed that OfferRewardText was equal to: "$c."

$c is a variable for "class", so... because I was playing a Mage, it displayed "mage."

I needed to find the correct text for this, so I went to Youtube and found a 10 year old video (around the time of actual Cataclysm). It showed me the following text:

<Sauranok nods toward you>
  
Paladin.

So, oddly enough the text quest was essentially correct - it's just the class name. However, there does seem to be an extra line above it, and the class name should be capitalised. The formatting for this uses "$B" to go to a new line. For more information on these variables, see here.

The final SQL fix for this one was:

UPDATE `quest_template` SET `OfferRewardText` = "<Sauranok nods toward you.>$B$B$C." WHERE `Entry` = 28909;