Database update system by Naios · Pull Request #14139 · TrinityCore/TrinityCore

Naios · 2015-02-13T15:10:40Z

Idea:

This pullrequest implements a great idea of @DDuarte mentioned in this post.

The idea is very simple: let the world and authserver update its database tables itself so you don't need to care about updates anymore -> say bye to "Your database structure is not up to date...", you won't see this message anymore ,-)

The procedure is following:

At startup the update system creates a list of all sql updates in some directories (sql/old, sql/custom and sql/updates)...
... and compares it with a list stored in the database. (every database has its own list of applied updates - logical)
If needed the updater updates the database with all missing and changed files and synchronizes the list stored in the database.
The system detects changes on files since last startup through sha1 hashes.

How it works

The pullrequest adds the functionality to both servers to create, populate and update its databases automatically.

Configuration:

The update system uses in-compiled cmake variables to detect your source directory that contains updates and your mysql cli binary.
Thats why the update system works out of the box in most cases.

Create databases:

If the database doesn't exists it asks for permission to create it.

Populate databases:

If the database doesn't contain any table it auto populates it from base/fulldumps.
Auth, character and hotfix db works out of the box, world db requires the current tdb release in your worldserver directory.

Update databases

The system detects recently added updates and applies it to the database.
It also detects changes on that file through hashes and reapplies it if necessary.
Renames of files don't lead to an reapply if the content didn't change, that makes it possible to rename files (because of name conflicts or codestyle fix) without worrying about reapplies.
Location of updates: sql\updates & sql\custom, it searches directories listed in the updates_include table recursively for updates so subdirectories are possible.
Updates have 2 states: ARCHIVED and RELEASED: every update is released until it gets archived (applied in the full db). Archived updates are not checked for changes to increase startup speed but it also checks whether an update was applied or not so you don't miss any update through database releases.

ToDo:

Ready to test!

For testing purposes append this to your full database. Otherwise world population won't be supported!

Finished

Working location

DDuarte · 2015-02-13T15:13:20Z

See https://github.com/jackpoz/TC-SQL-AutoUpdater by @jackpoz, it detects file changes using libgit

Naios · 2015-02-13T15:27:38Z

The big question is: is it better to swap this in an external tool or include it in the world & bnetserver.

To include it in the servers would bring several advantages like:

you need no second install
the whole infrastructure is already implemented
you can rely on that old updates gets reapplied on change (this would make editing of old (bugged) updates possible),
probably it works out of the box if you use the CMAKE_SOURCE_DIR variable.
and if you really want to use an external tool you may still disable it in the config.

Shauren · 2015-02-13T15:33:02Z

You cannot simply load the entire file into one buffer and execute it as-is. You need to call mysql_set_server_option with MYSQL_OPTION_MULTI_STATEMENTS_ON (preferably opening a separate connection to database with this option for updating purposes)

DDuarte · 2015-02-13T15:33:55Z

Yeah, I agree with make the worldserver apply the SQLs instead of an external tool. I linked Jackpoz's proof of concept to show a way to detect changes (it doesn't have to be like this, CRC checks would probably be fine too).

Naios · 2015-02-13T15:36:33Z

@Shauren xD you are right, this sql option needs to be set.

And probably it would make sence to open a new connection with this special option, except to set it for all connections.

DDuarte · 2015-02-13T15:40:57Z

I need to ask Aokromes for the IRC logs of the discussion of this feature few years ago.

DDuarte · 2015-02-13T16:16:25Z

src/server/shared/Updater/DBUpdater.h

jackpoz · 2015-02-13T20:36:54Z

you can rely on that old updates gets reapplied on change (this would make editing of old (bugged) updates possible),

An update is bugged is it throws an error when executed, in any other case a new sql update script should be added instead of modifying the existing one so modified ones shouldn't be updated.
A thing to worry about is sql script renames.

Shauren · 2015-02-13T23:16:06Z

src/server/shared/Updater/UpdateFetcher.cpp

Utils.h -> ByteArrayToHexString

ghost · 2015-02-14T12:08:15Z

as far as i remember mangos used a kind of version check in each sql query. could this be also used in this case here ? well this would mean each sql correction must be pushed as a seperate sql file but this would prevent double batching

Naios · 2015-02-14T12:49:14Z

Its requested that queries are multi batchable.

danlapps · 2015-02-20T21:54:22Z

I can clear my DB and launch it couple time if needed, i dont mind resetting everything

Naios · 2015-03-18T11:16:32Z

Updated this to a4b5b41. Recent updates (2015_03_16_*) included.

jaredjones · 2015-03-20T01:33:00Z

Well this addition to the core works really well, any reason we're still waiting for it to be merged?

danlapps · 2015-03-20T04:18:08Z

Now time to be a PITA
My SQL server is set as following :
World DBs:
world6
world335
world434

same goes for auth and character so i can keep all 3 branches updated.

Would I be able to set those settings in the automation ?

Naios · 2015-03-20T09:26:40Z

@danlapps Sure you can, it updates the dbs that are written in worldserver.conf & authserver.conf/bnetserver.conf.
Normally you use differen't db names there aren't you?

@jaredjones Currently we are waiting to do some cherry-picks first so a new TDB can be released as soon as this gets merged (to avoid conflicts with old not updated TDB's).

Naios · 2015-03-20T11:19:54Z

Updated this to a30eb73.

Also added the possibility to define the databases you want to update through flags:
This makes is possible to update only a subset of all available dbs for example world and hotfixes:

#    Updates.EnableDatabases
#        Description: A mask that describes which databases shall be updated.
#
#        Following flags are available
#           DATABASE_LOGIN     = 1, // Auth database
#           DATABASE_CHARACTER = 2, // Character database
#           DATABASE_WORLD     = 4, // World database
#           DATABASE_HOTFIX    = 8, // Hotfixes database
#
#        Default:     15 - (All enabled)
#                     4  - (Enable world only)
#                     0  - (All Disabled)

Updates.EnableDatabases = 15

Also improved the DatabaseLoader which removes a lot of duplicated code in worldserver and bnetserver main.cpp (and adds the functionality to close databases correctly on failure).

    // Load databases
    DatabaseLoader loader("server.worldserver", DATABASE_MASK_ALL);
    loader
        .AddDatabase(HotfixDatabase, "Hotfix")
        .AddDatabase(WorldDatabase, "World")
        .AddDatabase(CharacterDatabase, "Character")
        .AddDatabase(LoginDatabase, "Login");

    if (!loader.Load())
        return false;

danlapps · 2015-03-20T13:18:20Z

Yes all DB uses different names, you are a Genius :)

ghost · 2015-03-20T19:00:49Z

Excellent idea, me gusto. 👍

DDuarte · 2015-03-20T20:32:17Z

LF @Naios @ irc

Automatically detects new and edited sql updates through file lists and hashing. Detects renames, deletes and is able to create and auto import full databases. * cleanups in main.cpp of world & bnetserver * refactoring in DatabaseWorkerPool.h & MySQLConnection.cpp Make sure you re-run cmake, because boost::iostreams was added as dependency. Maybe you need to install libboost-iostreams1.55-dev on unix as well. Import every update manual until (included) those INSERT IGNORE updates for each database. Thanks DDuarte and Shauren for your amazing ideas, help and advises. In hope that nobody gets a "Your database structure is not up to date..." anymore ,-) Signed-off-by: Naios <[email protected]>

DDuarte · 2015-03-21T15:52:16Z

Merged in 352012e

TODO: Port to 3.3.5

Closes #13816 Closes #13792 Ref #14139

@et65

* Also use proper errno instead of 0 when reconnecting. * Thanks @et65 for reporting * ref #14139

@et65

* Also use proper errno instead of 0 when reconnecting. * Thanks @et65 for reporting * ref #14139 (cherry picked from commit 4741015)

@et65

…ysql_init failed. * Thanks @et65 for noticing. * ref #14139

@et65

…ysql_init failed. * Thanks @et65 for noticing. * ref #14139 (cherry picked from commit 83bbe00)

DDuarte reviewed Feb 13, 2015
View reviewed changes

src/server/shared/Updater/DBUpdater.h Outdated

Copy link
Copy Markdown

Contributor

DDuarte Feb 13, 2015

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Update

Naios force-pushed the dbupdater branch from 564ce29 to 04eed52 Compare February 13, 2015 20:49

Shauren reviewed Feb 13, 2015
View reviewed changes

src/server/shared/Updater/UpdateFetcher.cpp Outdated

Copy link
Copy Markdown

Member

Shauren Feb 13, 2015

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Utils.h -> ByteArrayToHexString

Naios force-pushed the dbupdater branch from e510b74 to 3b515db Compare February 14, 2015 03:06

Naios force-pushed the dbupdater branch from eea3043 to 129a759 Compare February 17, 2015 00:51

DDuarte added Branch-master Branch-3.3.5a Comp-Core Comp-Database Feedback-PatchFix Priority-FutureFeatureRequest labels Feb 17, 2015

Naios force-pushed the dbupdater branch 4 times, most recently from 1d2ae5e to cc49777 Compare February 20, 2015 17:52

Naios force-pushed the dbupdater branch from 14ad961 to 2de91f7 Compare February 20, 2015 22:08

Naios force-pushed the dbupdater branch from caa479e to afad4c2 Compare March 18, 2015 11:15

Naios force-pushed the dbupdater branch from afad4c2 to bd438e1 Compare March 18, 2015 12:20

Naios force-pushed the dbupdater branch 2 times, most recently from 6732a8f to 55154d3 Compare March 20, 2015 11:18

Naios force-pushed the dbupdater branch 3 times, most recently from 6dda07c to ba719fc Compare March 20, 2015 12:12

Naios force-pushed the dbupdater branch 3 times, most recently from a105c81 to b923397 Compare March 20, 2015 23:19

Naios force-pushed the dbupdater branch from b923397 to 71e3c56 Compare March 20, 2015 23:25

DDuarte closed this Mar 21, 2015

DDuarte added a commit that referenced this pull request Mar 21, 2015

TDB 6.0.1 - 2015/03/21

5be0cf7

Closes #13816 Closes #13792 Ref #14139

Naios added a commit that referenced this pull request Mar 29, 2015

Core/Database: Fix a mistake in 54ee526

4741015

* Also use proper errno instead of 0 when reconnecting. * Thanks @et65 for reporting * ref #14139

Naios added a commit that referenced this pull request Mar 29, 2015

Core/Database: Fix a mistake in 54ee526

6486827

* Also use proper errno instead of 0 when reconnecting. * Thanks @et65 for reporting * ref #14139 (cherry picked from commit 4741015)

Naios added a commit that referenced this pull request Mar 29, 2015

Core/Database: Return an unknown error code instead of false (0) if m…

83bbe00

…ysql_init failed. * Thanks @et65 for noticing. * ref #14139

Naios added a commit that referenced this pull request Mar 29, 2015

Core/Database: Return an unknown error code instead of false (0) if m…

c61fac8

…ysql_init failed. * Thanks @et65 for noticing. * ref #14139 (cherry picked from commit 83bbe00)

Aokromes removed the Branch-3.3.5a label Aug 16, 2016

Aokromes removed Priority-FutureFeatureRequest labels Apr 10, 2019

Conversation

Naios commented Feb 13, 2015

Idea:

How it works

Configuration:

Create databases:

Populate databases:

Update databases

ToDo:

Ready to test!

Finished

Uh oh!

DDuarte commented Feb 13, 2015

Uh oh!

Naios commented Feb 13, 2015

Uh oh!

Shauren commented Feb 13, 2015

Uh oh!

DDuarte commented Feb 13, 2015

Uh oh!

Naios commented Feb 13, 2015

Uh oh!

DDuarte commented Feb 13, 2015

Uh oh!

DDuarte Feb 13, 2015

Choose a reason for hiding this comment

Uh oh!

jackpoz commented Feb 13, 2015

Uh oh!

Shauren Feb 13, 2015

Choose a reason for hiding this comment

Uh oh!

ghost commented Feb 14, 2015

Uh oh!

Naios commented Feb 14, 2015

Uh oh!

danlapps commented Feb 20, 2015

Uh oh!

Naios commented Mar 18, 2015

Uh oh!

jaredjones commented Mar 20, 2015

Uh oh!

danlapps commented Mar 20, 2015

Uh oh!

Naios commented Mar 20, 2015

Uh oh!

Naios commented Mar 20, 2015

Uh oh!

danlapps commented Mar 20, 2015

Uh oh!

ghost commented Mar 20, 2015

Uh oh!

DDuarte commented Mar 20, 2015

Uh oh!

DDuarte commented Mar 21, 2015

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

9 participants