[One] DB setup

[Alorain]

Hello.

I'm trying to get up and running with Mangos One (TBC). For the most part, I'm comfortable with the setup thus far, but when it comes to updating the databases, I am a bit confused. I've read the documentation about how to check what to upgrade, but I am confused with the tons of files that are in the mangos/sql/updates/ folder. It seems there's two or more styles of file labeling.

Can someone give me an exact place to start with a fresh clone of mangos one? What are the 0.5->0.12 branches?

Also, how does the "Database" repository fit in with the whole project?

Thanks for any help.

[Chunjee]

I'm having setup issues with MaNGOS-One as well.

As I understand it, The Database repo has many annoying separate .sql files you need to import into the mangos/world database.

I don't understand the update folder but there is a complicated README in there.

I had a problem with vmaps being assembled incorrectly. I finally figured that out and they seem ok now. My problem is that MaNGOS crashes on character creation (image). It seems to be creating the character but nothing appears in the SQL database. I don't think there is a permissions issue (using same user/pass for working trinitycore install that I am trying to replace) so I don't understand what the problem is. My only idea is that something of the initial items/spells of each class is incorrect in the mangos database. Is it possible someone could just post a SQL dump of a clean or updated MaNGOs-One install?

[X-Savior_]

You can also go to UDB FOrum and look at TBC-DB. The DB in the Mangos Repo IS TBC-DB but they break it down and put into individual tables and ect.

Go to Here: http://udb.no-ip.org/index.php/topic,12422.0.html

This is my installation instructions to get up and going with Mangos One and TBC-DB. If you have any questions just ask and I will try and help you through it.

I still need to talk with Mangos Dev's to come up with a better unified way of doing the database.

IN about 3 weeks I will be starting work again on TBC-DB as well and have some new fixes and content already done for it.

[Alorain]

Thanks for replying.

A couple questions regarding your instructions:

1) Your TBC-DB applies only to the actual "mangos" database, right? "Characters" "realmdb" and "scriptdev2" still have to be updated with the updates contained from the mangos-one repo (sql files in the mangos/sql/updates/), right?

2) Is ACID in TBC 1.2.0 FULL_DB?

[X-Savior_]

Yes, I guess my instructions could get a little more detailed. I will update the post on the forum tonight for more detail.

What you need to do is:

1) apply is the 1.2.0 Full DB to your MANGOS db

2) apply the 1.2.1 Core Patch Rollup to your MANGOS db

3) apply the 1.2.1 Update Pack to your MANGOS db and then apply any additional core updates that were released since last TBC-DB update int he Mangos One Repo

4) (if starting fresh realm) Apply the Characters.sql from Mangos One Repo to your CHARACTERS db

5) Apply your realmdb (cant remember exact SQL name) SQL from Mangos One Repo to your realm db

6) For SD2 portion refer to their instructions (You apply template to scriptdev 2 db and then the scriptdev2 data and then there is another file you apply to mangos db as well

7) for ACID apply the latest Mangos One ACID (2.0,6D I think is most up to date but 2.0.7 will be release VERY soon).

now you are pretty much setup, get your config files done up and edit your realm db for proper config.

Hope this helps (This is standard instructions for all Mangos Core)

[Chunjee]

Thanks for the extra instructions, but I'm confused what a "Core Patch Rollup" is and where to find version 1.2.1

7 ) ... then go to the Updates folder and apply the Core Patch rollup and then Update packs for Each Release Version (In That Order)

I'm assuming the updates folder is ~\\server\\sql\\updates of mangos-one database repo. But there is no file with a name like 'rollup'. Are we talking about every single file with mangos in it? example: s1540_11955_02_mangos_command.sql

I'm also confused if "Update packs" are the folders with dozens of .sql files. That will honestly take me all day. Is there some SQL software that can automate importing an entire folder?

Should I know about the point of 0.12 and 0.12_branch in the updates folder?

[Alorain]

Thanks X-Savior. My DB is golden now.

Chunjee -

The Core sets the structure of the database. The updates populate the database. So always apply a core patch, then an update patch.

Click on the magnos DB. Open the tables folder scroll to db_version. Click that and see the table data.

The first column should be "version", the second column "creature_ai_version", last column should be "required_SXXXX_#####_$$_mangos_something".

Whatever XXXX is, is the rev number you are currently at. You need to update with everything AFTER XXXX. And yes, it is a pain.

I think it would be great for mangos newcomers to just have an up-to-date DB ready to plug and play. That said, I realize it's a free project and someone would have to take the time to set that up.

But it really would remove a large hurdle to getting started.

[Chunjee]

Ok well I started with the updates from s1399 all the way up to s1592 one by one. Backup the database so I can try launching MaNGOS with that but still crash at character creation. So I revert to the backup and start on the updates. 0.12_branch start at 001 so I assume I don't need that. 1592 is higher. 0.12 folder starts at 2008, so I start doing those but quickly run into SQLyog spitting errors at me.

Am I doing something wrong? Can I just get a copy of your database Alorain? I'll offer FTP access if you have nowhere to upload it.

My trinitycore world DB is 52MB. As soon as I figure out this exercise I'm going to post it as a single file.

[X-Savior_]

Are you looking at the TBC-DB Repo for your Database files?

(NOT The Mangos GIT Database Section)

http://udb.no-ip.org/index.php/topic,12857.0.html

So what you do is apply the 1.2.0 Full release (What full release is, is a complete DB package). Then in the TBC-DB repo you will see a folder called updates and it hold the 1.2.1 update pack and core rollup package.

What a core rollup package has in it is ALL of the require Core Updates in sequential order to take your full release to the next required core stepping.

So as you see here:

https://tbc-db.svn.sourceforge.net/svnroot/tbc-db/

in 1.2.0 you will see "Full DB" and "Updates". You always start with the full db and then apply the update packs as required after.

1) Apply https://tbc-db.svn.sourceforge.net/svnroot/tbc-db/1.2.0/Full_DB/ to your Mangos DB. As you see in the file name:

TBCDB_1.2.0_mangos-one_s1409_SD2_2319.rar

This is Release 1.2.0 for Core Version s1409 and includes SD2 2319 in the Mangos DB.

So now we will look at update folder:

https://tbc-db.svn.sourceforge.net/svnroot/tbc-db/1.2.0/Updates/

right now we have 2 files:

1.2.1_corepatch_mangos_1410_to_1434.sql

1.2.1_updatepack.sql

So what you need to do is bring your core version up from 1409 (That is what last full version was from 1.2.0) to the new required version for the update pack (1434). So instead of applying all the individual files from mangos core repo you can just execute the single file located here that will bring the Mangos Db up from 1410 -> 1434 in a single step.

Now you are able to apply the update pack to the correct core version. Apply the 1.2.1 update pack to your mangos DB.

Now only thing to do left is apply your SD2 files if you compiled a newer SD2 then version 2319.

I can understand from a new person this is extremely confusing (I been there).

I suggest if you are still have problems come on to IRC and ask people for help so they can talk you through it step by step. There is MANY basic tutorials on UDB/TBC-DB/Mangos Forums outline this process as it is same process (just different files) for updating and setting up for all cores.

[shlainn]

Wow, what a wall of text. Let me present an alternative (The MaNGOS One Git Database):

1) Get the database from git (same procedure as getting mangos source, just different repo: https://github.com/mangos-one/database.git)

2) Right-click the folder you just dumped the DB into and select "Git Bash here..." (same as for extracting MoveMaps)

3) In the console type ./make_full_db.sh - This creates a file called full_db.sql

4) Apply this file to your database.

You are done.

[X-Savior_]

ahhh yes, I am not used to the make option.

I have never used this format myself but it sounds interesting.

[shlainn]

So can we work together again?

EDIT: The advantage of single tables is that we don't need UPDATE statements any more. If a table changes, it can be dumped and re-applied as full, which is less error-prone IMHO as the database dumps are made by a program, while UPDATE statements are written by hand.

Shlainn

[stfx_]

Wow, what a wall of text. Let me present an alternative (The MaNGOS One Git Database):

1) Get the database from git (same procedure as getting mangos source, just different repo: https://github.com/mangos-one/database.git)

2) Right-click the folder you just dumped the DB into and select "Git Bash here..." (same as for extracting MoveMaps)

3) In the console type ./make_full_db.sh - This creates a file called full_db.sql

4) Apply this file to your database.

You are done.

Don't forget that it doesn't include realm, characters, scripts dumps - those need to be taken from mangos-one sql/characters.sql, sql/realmd.sql and SD2-one sql/scriptdev2_script_full.sql, sql/mangos_scriptname_full.sql

[shlainn]

Don't forget that it doesn't include realm, characters, scripts dumps - those need to be taken from mangos-one sql/characters.sql, sql/realmd.sql and SD2-one sql/scriptdev2_script_full.sql, sql/mangos_scriptname_full.sql

Actually as far as I am aware, sql/mangos_scriptname_full.sql is already applied here, but of course you need to fill realm, characters and scriptdev2 databases

[Chunjee]

X-Savior:

I imported in this order:

TBCDB_1.2.0_mangos-one_s1409_SD2_2319.sql\\

1.2.1_corepatch_mangos_1410_to_1434.sql

1.2.1_updatepack.sql

and got db_version out of date errors. I guess "So what you need to do is bring your core version up from 1409 (That is what last full version was from 1.2.0) to the new required version for the update pack (1434)." means I need to manually apply some of those .sql files still.

Appreciate the new convenience script on the MaNGOS-One database repo, but I am still crashing at character creation. I have no idea what is causing this problem, please take a look at my new thread here: http://getmangos.eu/bb/topic/250/crash-on-character-creation/

my only clue is a database error

2012-08-15 11:53:54 Spell (id: 33824) have SPELL_EFFECT_QUEST_COMPLETE for quest 10162 , but quest does not have SpecialFlags QUEST_SPECIAL_FLAG_EXPLORATION_OR_EVENT (2) set. Quest SpecialFlags should be corrected to enable this objective.

[X-Savior_]

If you did what I said your DB should be at s1434 but of course mangos one repo is currently at s1628 so you look in the mangos/sql/updates folder from the mangos one repo and apply all of the applicable updates starting at 1435 (everything past our 1.2.1 core rollup that you already applied).

Dont get frusterated but I STRONGLY suggest if you still have trouble go on IRC and start asking for help step by step. Worse case is lookup on the ACID forums the ACID IRC channel and come there. I can help you understand more and slow it down to step by step. Once you do it once or twice it will all make sense.

The problem you need to understand is DB's are developed at time of release for the current core at that time. so you need to get a DB to the correct version to apply a DB update and then if you are using yet a newer core then when the DB was released then you just need to apply all the latest updates (Because development continues after a DB release).

What will happen is when next TBC-DB release occurs there will be ANOTHER new core rollup to bring a DB from 1435 -> Current core at that time of 1.2.2 Update Pack release. The rollups are a time savings and to make life easy for you. (Same as you applying all the individual updates in this example from 1410 -> 1434)

As for startup errors, if you only have a couple not a big deal... i think there are a few right now that will be fixed with next release. But if you get TONS AND TONS of startup errors then you screwed up on something.

[shlainn]

X-Savior, his problem is core, not DB at the moment. The DB was applied correctly either way

[tifalita]

Ok. Shlainn, I am a total noob here with the database stuff.

First I'll explain what i have, I have Navicat to access the database for our 2.4.3. I do not know how to tell what mangos revision we are on or how to even begin updating.

I managed (in Navicate) to "run" the sql file "full_db" and it added some tables and i guess modified others, but now when i load the server it says

Error in gameobject_template table, probably sql file format was updated (there should be 33 fields in sql).

Remember im a total NOOB when it comes to this. Sorry in advance.

Running on Windows 7 x64

[antz]

ok, easiest way to find our your current mangos revision..... start mangos in a console window, it will display the current revision.

Once we have that, we can work the rest out

[tifalita]

0.12.0 svn and revision 6360

Thank you for your quick reply.

[antz]

wow.... that's quite old... one quick question before we proceed... do you need to keep the accounts and characters that have been created ?

It makes a significant difference to how we proceed !

[tifalita]

No i do not, we are only level 20 and thats easily redoable.

[tifalita]

Yes we dont care about our current server, we can start over completely fresh. If we can do that without updating and just somewhere download the newest version of everything that would be great. Ive been at this for so long my NOOB brain is at its end.

Might sound cheesy but is there a download somewhere that just has everything all ready updated and ready to go? I can get the /maps no problem. TY ahead of time.

[antz]

ok, if you haven't already, grab the latest source files. gibhub.com/mangosone

the world database is under the database subproject - replace your mangos database with this new one

the scripts database is under the scripts subproject - same here replace your mangos database with this one

the characters database is located in the server subproject under server/sql - use the file characters.sql to recreate the characters db

for the realm database, I would first open this and make a note of the details in the realm table (ip address, realmname), then recreate the database using the file realmd.sql in server/sql as above

you will need to recreate accounts/characters, but should be ready to go.

[tifalita]

im still getting this when i start my sd.exe

Error in gameobject_template table, probably sql file format was updated (there should be 33 fields in sql).

also note. my cmd wind. still says 6360...