Mysql Beginners Guide

[eyerobot]

Have you ever thought to yourself. I wish I knew how to fix Creatures, Objects, And Quests?
During an average day of playing, You probably encounter hundreds of bugs that could be fixed in seconds, If you only knew how.
But you never went to tech school, And most likely don't want to ask anyone for help, Because people are generally waiting for you to ask, So they can give some smug condescending answer that goes completely over your head, Just so they can feel superior.

But the truth is that you are just as capable of fixing bugs as anyone else. But where to begin? Right here that's where.

https://www.heidisql.com/download.php Heidisql.
You will need the latest version of Heidisql, Anything older is incapable of creating proper sql queries.

https://www.dropbox.com/s/thfwqcbxd3vtw76/Truice_v2.0.0.zip?dl=0Truice V2.0
Truice is useful for viewing the database in a graphical format. But THE EXECUTE BUTTON SHOULD NEVER BE USED. It can damage your database. Instead go into truice settings and set it to always create update queries, Then change something, Click the script tab, and you will see an update query that you can copy and paste into Heidisql.

Heidsql installs the same as any other program, Truice needs no installation. Just right click Truice.exe and send to desktop, So it's easier to find.

Heidsql
Start the program. The session manager will open first, And expects you to fill in the login info for your server. To make things simple make sure that you are running Heidsql on the server machine, And have your mysql server running as well. Otherwise Heidisql cannot log into your mysql server.
It is possible to create an account in mysql that is accessible from other machines. Just search "mysql create new user with all privs" in your browser.
Now, Mysql has finished starting up, And you just started Heidisql. It is asking for your login info. user root, most likely password is ascent, Unless you have changed it. and the address should be set to localhost, or 127.0.0.1.
When you are finished entering data hit connect. The window should open that shows you all the databases that are installed.
emucoach-auth, The login servers database.
emucoach-char The character database.
emucoach-V8-Vip Which is your world database.
The world database is where all quests, creatures, objects, spells, You name it, it's in there.

Select the small arrow next to the world database, It will open up to show you all the tables inside.
Selct a table, On the right you will see several tabs, Some show the makeup of the database table you selected, One is called data, This is all the data inside that table on display.
The last tab is called query, This is where you paste database fixes that you copy from http://www.emucoach.com/forumdisplay.php?81-4-3-4-Database-Fixes-EmuCoach-Repack-Unofficial-fixes.

So what do i do with it?
Let's start with a very recent fix submission by pedroevil51. ShadowGlen sentinel looking dead Fix .

UPDATE `creature_template` SET `Health_mod` = 1 WHERE `entry` = 12160;

Basically what has happened is the shadowglen sentinels were laying dead on the ground, When their supposed to be walking around.
After reading the creature template for these guys, He noticed that the health_mod for these creatures was set to some extremely high number.
So their basic health was multiplied by this huge number, And it gave a hp reading that was out of range for the server to display, So the server assumed them to be dead, And made them dead for real.

So we look up the creature shadowglen sentinel on wowhead to find out what their health actually should be.
At this point you should be wondering, Ok where do i type in the number for their health, Like it shows it on wowhead? The answer is you don't.
The dbc files that you installed into your server folder hold all of the data for creatures, And many other things. And the server reads these fields to determine their correct hp, mana, ect.
But, We can modify their hp with the health_mod multiplier.
So how do i know what number i'm multiplying? You could look up creature 12160 in the dbc files with a dbc editor, But an easier way is to set their health_mod to 1, Restart the server, And just go select the creature and read what it says. If it's too low you can change the health_mod to 1.5, 1.7, Or whatever setting makes their health show up correctly. If its too high go below zero, With .05, .07, Whatever works.
And once you have the correct health_mod setting, His hp shows up correctly, And he no longer lays around dead, Because the server can correctly read his settings.

Now what do i do with Pedro's sql query that he posted?
Open Heidsql, Login to your server, Select the world database, And on the right open the query tab.
copy the query by holding and dragging your mouse over it, Then ctrl-c to copy it, Then on the query tab, ctrl-v to paste it in there.
At the top you will see what looks like a play button, This will run the query, Or just right click the query tab, and choose run.
It will finish extremely fast, And at the bottom you should get a message that says "1 affected rows" Or something similar. This means it worked, And you only need to restart your world server to see the changes ingame.

If you get a message that says "you have an error in your mysql code at line blah blah", Then you know that the query you tried to run either has a typo in it, Or is incompatible with your database.
Leaving the semicolon off of the end of that query will cause this problem, And is the number one mistake made by database programmers. Or the tools that they use to create queries.

That's all there is to it. With this little bit of information, You can install every database fix that you see posted on emucoach.

It should be noted that some fixes are not accepted for different reasons, Wether they are rejected because of wrong data, Or because a developer used sql queries that are dangerous to run, Meaning they could destroy your database. Or many other reasons.
But to be safe, You shouldn't run every fix that you see, Until it has been approved, Or until you are confident enough in your skills that you can determine if it is safe to run.

Also take note, That every database is different. Meaning that you can't take a database fix from another project, And run it on the emucoach server. They just aren't compatible, Because the data tables are most likely different in structure.

 

[eyerobot]

I get asked the same questions a lot.
Are you one of those people that have played retail for years, And memorized everything you saw?

No, In fact i didn't even finish the two week free trial, Retail was too laggy for me.
What I am is denoted by my name, Eyerobot. I research in the extreme. And even read all of the comments on sites like wowhead, To find out how a quest was supposed to work, Even if it never worked on retail. I watch as many different versions of a quest on youtube as i can find, Because quests change from one game version to the next. And I use quite a few different search queries in my browser in the hopes that someone has already written a particular quest, Or fixed a certain problem. And all i will need to do is convert it to work with emucoach.

Where can I learn to fix the server?
Well, You can start at trinitycore. https://trinitycore.atlassian.net/w...30108/smart+scripts#smart_scripts-target_type. On the left you will see the tables listed in your database. When you click one you will see an explanation of that table, And what it's used for.
The documentation is very basic for most things. But you from what you see there, And a little experimentation, You can quickly learn how things actually work in this server. Also you can use your browser to search for things. Such as SAI tutorial, Smart_scripts, or just type in any database table name that you need help with. Or just ask here on emucoach. There are a lot of talented people around, And some will take the time to answer serious questions.

Ok, I read about the database, But where do i find information that's missing from my data tables?
Well, One thing that I have, Is a copy of the latest trinitycore world database installed into mysql. Now if I find an npc, quest, ect, that is missing important information, I can search for it in that database using heidsql. This way, If anyone has updated that server with the correct information, I can get it easily.
Otherwise you can search for it online using search queries in your browser. For example if i'm working on the quest "ever so lonely", I can type every so lonely github. And if anyone has posted a fix for that on github, It will most likely show the sql queries that they used to fix it.
Also you can use just about any combination that you can think of. Such as quest ever so lonely, trinitycore every so lonely. Literally anything that you can think of that might make it show up. But don't be surprised if it doesn't work. Because most private server owners will not list their fixes publicly. For the simple reason that they hope to make money off of a game that they didnt create.
Sometimes I will search the C++ scripts of a server to find certain data that I need. You can find many online repositories listed for different server, If you browse into their src/server/scripts folder, Sometimes you can read the script for a certain thing, And it will tell you if it's phased, which spells were used, Which npc's ect. This information usually comes from sniffing tools used on retail to gather this data. So it is extremely valuable.

I managed to find the data to fix a certain quest, But it won't run in Heidisql.
This means that every single part of those queries has to be compared with our database structure, And converted if possible. And once the entire thing is converted, You have to test it on your database. If you can get it to work, Then you have to go ingame, and see if it actually works. If it works perfectly, Or at least very well, Then you can submit your converted queries to emucoach for everyone else to enjoy too.

But I don't know anything about mysql, And i don't know how to search for things in my database. I tried to read the documentation at mysql.com, But it's confusing. How am i supposed to figure this out?

You just need a few helpful queries to get you started is all.
In Heidisql you can load sql queries that will help you search for things. They are in a folder called snippets. But you have to put them there first, Either by writing them yourself, Of by downloading them, And placing them in C:\ProgramData\HeidiSQL\Snippets. Here are some that i use frequently. This is a copy of my snippets folder, And should be extracted over your snippets folder. https://www.dropbox.com/s/l91ky2m4deqskea/Snippets.zip?dl=0

This is not a conclusive list of how i find data, Far from it. But it gives you some pointers to begin with.

 

[Methusalem]

WOW! Mind read often? This pretty much summed me up, and also how I feel about asking. How did I miss this 2 days? lol I love my Navicat. I was actually able to learn a few things on my own on it, but was not enough sadly. Perhaps I can try to learn this method, and grow an extra brain cell "or 2" and learn the Navicat way to do similar later? I am sure it has to be similar. I would have tried to help on a not sick day had I knew how to, but few people would bother. But I DEFINATELY needed this! lol This is GREAT! Thank you for taking the time to do this, and share it.

I am not doing good the last few days, but WILL definately dig into this HARD when I am a bit more up to par! Bookmarked, favorited, chisled on a stone, etc.. won't lose it! In other words. lol

 

[Methusalem]

Disregard the Navicat exitement I had. I recently saw your warning about it. I am sad... but glad you told me! Thank you again. lol

 

[eyerobot]

Disregard the Navicat exitement I had. I recently saw your warning about it. I am sad... but glad you told me! Thank you again. lol

I remember i told you about navicat rounding off numbers when you move data around, but I forgot to add that navicat doesn't understand some mysql statements. Yeah I know it's supposed to but it doesn't. That's what originally got me looking for a better database editor. That and the fact that it literally destroyed my Tbc database after months of usage. I read someones warning about it, But figured it was just rumor, Turns out it wasn't.
So if you need help with Heidisql, Don't hesitate to pm me about it. I know it's a weird thing to move from navicat to Heidisql.

 

[Methusalem]

I remember i told you about navicat rounding off numbers when you move data around, but I forgot to add that navicat doesn't understand some mysql statements. Yeah I know it's supposed to but it doesn't. That's what originally got me looking for a better database editor. That and the fact that it literally destroyed my Tbc database after months of usage. I read someones warning about it, But figured it was just rumor, Turns out it wasn't.
So if you need help with Heidisql, Don't hesitate to pm me about it. I know it's a weird thing to move from navicat to Heidisql.

I sure do appreciate that. At present sadly, my health is prevnting the needed concentration to try it out. I redownloaded all off ExO's new post/links, and will try my best with your post to tackle it as soon as I am able. I won't trouble you unless I get stuck badly. I am deeply grateful for the HeidiSQL help post you left already. I will try hard as soon as I am a bit better. Thank you so much.

 

[djorn01]

I have been looking at trying to learn to edit/fix databases but there doesn't seem to be much of info on it. The Goblin start area is broken unable to be completed in the Emucaoch repack.. it worked a bit better in 9.1 then 10 or 10.1 but was still broken which means without console access you can't get you goblin character through it and into the rest of the game. I have been looking at the database from a quest i know works on one version with the same quest that is broken on the next version and i can't see what is broken :S must be missing it. I thought it would be a good way to get into learning how quests work in the database as you can compare a working one with one that doesn't .. that way you should be able to see where they went wrong. I have a program that allows you to compare files/txt and gives you a output with multiple windows with them aligned side by side so you can compare them line by line (works on executable files to). Maybe if i export the sql for this quest i can compare it with the program to make sure i didn't miss something, apart from that i'm not sure where to go from there. The initial quest i started to try and diagnose was the "The New You" quest in the goblin start area where you have to buy the clothes from the three vendors, the problem being that the quest items don't appear in the npcs inventories. I compared the quest itself (there are two parts 1409 and 14010 and 14014 or something for the quest tracking) and the relevant npc's involved. Is there something else related to the quests i should be looking at? once i can figure it out i should be able to get a general idea how to fix other database problems. I have done a lot of googling land haven't found any decent information or tutorials on getting into editing/fixing databases. If someone made a tutorial that took you through fixing a couple of problem types in a database, walk you through thinking process/reasons behind why/how you go about doing things so people can then go out and apply that to other problems (they could provide a practice database to fix problems in during the tutorial). This would help more people getting into fixing problems which they could then submit fixes for. Years ago i got into making trainers for games s well as using hex and asm to edit exe's, both single player and online and the tutorials written to help people get into these types of editing/debugging took people through practical examples of how to do something while explaining the reasoning for why/how you go about a certain process so you understanding the thinking/reasoning behind it.

The truice v2 you linked has errors when trying to connect to the emucoach database (the 2.1.5 one dose to). This has a few issues atm but looks promising https://github.com/Palabola/Keira2Paladium XD

 

[killmaine]

Thank you for the SQL guide,

 

[aldavin]

Thanks for the guide!

 

[Coso]

Toal n00b here, but the guide made it sound easy ! I'm going to copy and save it in my wow folder just in case
Started playing with the awesome free cata repack and now I feel like I can also play with it in a more "technical" way , thanks !

 

[Blackvision]

Well written guide ??

 

[Giuseppe360]

thanks

 

[TheGofa]

This has been very helpful for someone as non tech as I am. It explained a good enough amount to get my little 3.3.5 server up and running, thank you !!

 

[Fleshpound]

Is there a modifier for creature's damage, same as health_mod / mana_mod? Couldn't find one at provided trinitycore site.

 

[Suzakiro]

Have you ever thought to yourself. I wish I knew how to fix Creatures, Objects, And Quests?
During an average day of playing, You probably encounter hundreds of bugs that could be fixed in seconds, If you only knew how.
But you never went to tech school, And most likely don't want to ask anyone for help, Because people are generally waiting for you to ask, So they can give some smug condescending answer that goes completely over your head, Just so they can feel superior.

But the truth is that you are just as capable of fixing bugs as anyone else. But where to begin? Right here that's where.

https://www.heidisql.com/download.php Heidisql.
You will need the latest version of Heidisql, Anything older is incapable of creating proper sql queries.

https://www.dropbox.com/s/thfwqcbxd3vtw76/Truice_v2.0.0.zip?dl=0Truice V2.0
Truice is useful for viewing the database in a graphical format. But THE EXECUTE BUTTON SHOULD NEVER BE USED. It can damage your database. Instead go into truice settings and set it to always create update queries, Then change something, Click the script tab, and you will see an update query that you can copy and paste into Heidisql.

Heidsql installs the same as any other program, Truice needs no installation. Just right click Truice.exe and send to desktop, So it's easier to find.

Heidsql
Start the program. The session manager will open first, And expects you to fill in the login info for your server. To make things simple make sure that you are running Heidsql on the server machine, And have your mysql server running as well. Otherwise Heidisql cannot log into your mysql server.
It is possible to create an account in mysql that is accessible from other machines. Just search "mysql create new user with all privs" in your browser.
Now, Mysql has finished starting up, And you just started Heidisql. It is asking for your login info. user root, most likely password is ascent, Unless you have changed it. and the address should be set to localhost, or 127.0.0.1.
When you are finished entering data hit connect. The window should open that shows you all the databases that are installed.
emucoach-auth, The login servers database.
emucoach-char The character database.
emucoach-V8-Vip Which is your world database.
The world database is where all quests, creatures, objects, spells, You name it, it's in there.

Select the small arrow next to the world database, It will open up to show you all the tables inside.
Selct a table, On the right you will see several tabs, Some show the makeup of the database table you selected, One is called data, This is all the data inside that table on display.
The last tab is called query, This is where you paste database fixes that you copy from http://www.emucoach.com/forumdisplay.php?81-4-3-4-Database-Fixes-EmuCoach-Repack-Unofficial-fixes.

So what do i do with it?
Let's start with a very recent fix submission by pedroevil51. ShadowGlen sentinel looking dead Fix .

UPDATE `creature_template` SET `Health_mod` = 1 WHERE `entry` = 12160;

Basically what has happened is the shadowglen sentinels were laying dead on the ground, When their supposed to be walking around.
After reading the creature template for these guys, He noticed that the health_mod for these creatures was set to some extremely high number.
So their basic health was multiplied by this huge number, And it gave a hp reading that was out of range for the server to display, So the server assumed them to be dead, And made them dead for real.

So we look up the creature shadowglen sentinel on wowhead to find out what their health actually should be.
At this point you should be wondering, Ok where do i type in the number for their health, Like it shows it on wowhead? The answer is you don't.
The dbc files that you installed into your server folder hold all of the data for creatures, And many other things. And the server reads these fields to determine their correct hp, mana, ect.
But, We can modify their hp with the health_mod multiplier.
So how do i know what number i'm multiplying? You could look up creature 12160 in the dbc files with a dbc editor, But an easier way is to set their health_mod to 1, Restart the server, And just go select the creature and read what it says. If it's too low you can change the health_mod to 1.5, 1.7, Or whatever setting makes their health show up correctly. If its too high go below zero, With .05, .07, Whatever works.
And once you have the correct health_mod setting, His hp shows up correctly, And he no longer lays around dead, Because the server can correctly read his settings.

Now what do i do with Pedro's sql query that he posted?
Open Heidsql, Login to your server, Select the world database, And on the right open the query tab.
copy the query by holding and dragging your mouse over it, Then ctrl-c to copy it, Then on the query tab, ctrl-v to paste it in there.
At the top you will see what looks like a play button, This will run the query, Or just right click the query tab, and choose run.
It will finish extremely fast, And at the bottom you should get a message that says "1 affected rows" Or something similar. This means it worked, And you only need to restart your world server to see the changes ingame.

If you get a message that says "you have an error in your mysql code at line blah blah", Then you know that the query you tried to run either has a typo in it, Or is incompatible with your database.
Leaving the semicolon off of the end of that query will cause this problem, And is the number one mistake made by database programmers. Or the tools that they use to create queries.

That's all there is to it. With this little bit of information, You can install every database fix that you see posted on emucoach.

It should be noted that some fixes are not accepted for different reasons, Wether they are rejected because of wrong data, Or because a developer used sql queries that are dangerous to run, Meaning they could destroy your database. Or many other reasons.
But to be safe, You shouldn't run every fix that you see, Until it has been approved, Or until you are confident enough in your skills that you can determine if it is safe to run.

Also take note, That every database is different. Meaning that you can't take a database fix from another project, And run it on the emucoach server. They just aren't compatible, Because the data tables are most likely different in structure.

Very nice !

 

[XeclipseX]

Have you ever thought to yourself. I wish I knew how to fix Creatures, Objects, And Quests?
During an average day of playing, You probably encounter hundreds of bugs that could be fixed in seconds, If you only knew how.
But you never went to tech school, And most likely don't want to ask anyone for help, Because people are generally waiting for you to ask, So they can give some smug condescending answer that goes completely over your head, Just so they can feel superior.

But the truth is that you are just as capable of fixing bugs as anyone else. But where to begin? Right here that's where.

https://www.heidisql.com/download.php Heidisql.
You will need the latest version of Heidisql, Anything older is incapable of creating proper sql queries.

https://www.dropbox.com/s/thfwqcbxd3vtw76/Truice_v2.0.0.zip?dl=0Truice V2.0
Truice is useful for viewing the database in a graphical format. But THE EXECUTE BUTTON SHOULD NEVER BE USED. It can damage your database. Instead go into truice settings and set it to always create update queries, Then change something, Click the script tab, and you will see an update query that you can copy and paste into Heidisql.

Heidsql installs the same as any other program, Truice needs no installation. Just right click Truice.exe and send to desktop, So it's easier to find.

Heidsql
Start the program. The session manager will open first, And expects you to fill in the login info for your server. To make things simple make sure that you are running Heidsql on the server machine, And have your mysql server running as well. Otherwise Heidisql cannot log into your mysql server.
It is possible to create an account in mysql that is accessible from other machines. Just search "mysql create new user with all privs" in your browser.
Now, Mysql has finished starting up, And you just started Heidisql. It is asking for your login info. user root, most likely password is ascent, Unless you have changed it. and the address should be set to localhost, or 127.0.0.1.
When you are finished entering data hit connect. The window should open that shows you all the databases that are installed.
emucoach-auth, The login servers database.
emucoach-char The character database.
emucoach-V8-Vip Which is your world database.
The world database is where all quests, creatures, objects, spells, You name it, it's in there.

Select the small arrow next to the world database, It will open up to show you all the tables inside.
Selct a table, On the right you will see several tabs, Some show the makeup of the database table you selected, One is called data, This is all the data inside that table on display.
The last tab is called query, This is where you paste database fixes that you copy from http://www.emucoach.com/forumdisplay.php?81-4-3-4-Database-Fixes-EmuCoach-Repack-Unofficial-fixes.

So what do i do with it?
Let's start with a very recent fix submission by pedroevil51. ShadowGlen sentinel looking dead Fix .

UPDATE `creature_template` SET `Health_mod` = 1 WHERE `entry` = 12160;

Basically what has happened is the shadowglen sentinels were laying dead on the ground, When their supposed to be walking around.
After reading the creature template for these guys, He noticed that the health_mod for these creatures was set to some extremely high number.
So their basic health was multiplied by this huge number, And it gave a hp reading that was out of range for the server to display, So the server assumed them to be dead, And made them dead for real.

So we look up the creature shadowglen sentinel on wowhead to find out what their health actually should be.
At this point you should be wondering, Ok where do i type in the number for their health, Like it shows it on wowhead? The answer is you don't.
The dbc files that you installed into your server folder hold all of the data for creatures, And many other things. And the server reads these fields to determine their correct hp, mana, ect.
But, We can modify their hp with the health_mod multiplier.
So how do i know what number i'm multiplying? You could look up creature 12160 in the dbc files with a dbc editor, But an easier way is to set their health_mod to 1, Restart the server, And just go select the creature and read what it says. If it's too low you can change the health_mod to 1.5, 1.7, Or whatever setting makes their health show up correctly. If its too high go below zero, With .05, .07, Whatever works.
And once you have the correct health_mod setting, His hp shows up correctly, And he no longer lays around dead, Because the server can correctly read his settings.

Now what do i do with Pedro's sql query that he posted?
Open Heidsql, Login to your server, Select the world database, And on the right open the query tab.
copy the query by holding and dragging your mouse over it, Then ctrl-c to copy it, Then on the query tab, ctrl-v to paste it in there.
At the top you will see what looks like a play button, This will run the query, Or just right click the query tab, and choose run.
It will finish extremely fast, And at the bottom you should get a message that says "1 affected rows" Or something similar. This means it worked, And you only need to restart your world server to see the changes ingame.

If you get a message that says "you have an error in your mysql code at line blah blah", Then you know that the query you tried to run either has a typo in it, Or is incompatible with your database.
Leaving the semicolon off of the end of that query will cause this problem, And is the number one mistake made by database programmers. Or the tools that they use to create queries.

That's all there is to it. With this little bit of information, You can install every database fix that you see posted on emucoach.

It should be noted that some fixes are not accepted for different reasons, Wether they are rejected because of wrong data, Or because a developer used sql queries that are dangerous to run, Meaning they could destroy your database. Or many other reasons.
But to be safe, You shouldn't run every fix that you see, Until it has been approved, Or until you are confident enough in your skills that you can determine if it is safe to run.

Also take note, That every database is different. Meaning that you can't take a database fix from another project, And run it on the emucoach server. They just aren't compatible, Because the data tables are most likely different in structure.

How do you even get the MySQL to start? mine keeps saying that it cannot be started. Its beyond rediculous!!

 

[MadMunky]

Did you solve your issue? Anytime that's happened to me I just go into Windows Task Manager, locate the MySql app that's running (sometimes two of them from memory?) and RIGHT CLICK END TASK. Then try again.