Automatic Database Upgrade and Versioning
 

Just wanted to shoot out a quick update for this, had a code session grind last night and earlier today to finish this out. We've been needing this for quite a while. Thanks Demonstar55 for helping me test the Linux side of the updates.

What this solves:

• It will automatically go through and look at each update historically and compare it against your database, if your database is missing the update, it will continue and build a list of updates and apply them in a very quick fashion
• As people update their source, they no longer need to worry about upgrading their database as the process has become streamlined and completely automatic

How it Works:

• When World boots up, it will start this check process by pulling down scripts from Github if you don't have them, this process happens transparent to the user
• The script will load a manifest that we use to intelligently drive the script to know which updates need to be ran, I have the manifest cleanly working for old databases all the way back through our Github SQL updates about 2 years back. I have yet to go through SVN, but when I do it will work the same for people.

With this I was able to take a super old server database, and get it upgraded within minutes and boot it up. So it will be great to have for regular updates and folks who haven't upgraded in quite a while.

To run it manually, there is a db_update.pl in the server directory that you can run, to trigger other utilities such as the db_dumper.pl (Backup script) for pre-backup measures.

In the future:

• We can do things like pull down latest opcodes from Github
• Pull down latest PEQ AA Tables
• Pull down latest PEQ Mercenary tables
• Anything else that makes sense

Below is an example of what it would look like:

When you select 3) it would stage to see which updates your database does not have.

You would see something like this:

When this routine completes, you should see something that resembles the following:

Yes there were some errors, because I'm not using bots. But everything else went through smoothly.

And then a return back to the management menu:

When the database is up to date, world will not interrupt the bootup process. When versions match it will simply bootup as intended. When you have updates that need to be made, you probably shouldn't be booting up anyways.

If anyone has any questions, comments feel free. Enjoy this should be a huge step in keeping our server upgrade process streamlined.

http://i.imgur.com/lCVKRms.png

Changelog Notes:

Thanks,
Akka

Is there a parameter world can be passed to either make a users preferred choice and/or completely say "not right now go away" ?

Confused because you stated:
but then what reads to be a slight contradiction:
Interrupting the World process seems, well, a little pushy. <shrug> You asked for opinions, there ya go.

These are required database scheme updates and if you are updating your binaries you are also going to want your schematic to reflect so what you're saying doesn't make sense.

Adding a skip check parameter to world can be done for those who would like the choice. But the point is to have it so most users will notice that there is an upgrade needing to be done with the database side. It really takes seconds to do and you won't have to see it until you update your source with another schema change.

Also what you may not be seeing as well is that if your schema is up to date, world will still boot up uninterrupted. if world boots up and their binary DB version is greater than you local than you would want to update otherwise you could have major things flat out not work.

Anything can be added to it at this point it's pretty easy. Most of the heavy lifting is done and it was a lot of work in a short amount of time mainly because I've had a lot of time to think about it.

Halting the running of World waiting for user input was the gist of what I was making comment about.

Made sense to me. :)

Just to clarify; assuming that you are running a server and don't update the binaries and your database is 100% up to date with the binaries you are using, this script will determine that you are running the latest you need and basically do nothing and World will just run without any intervention. So in this case automated restarts will simply "just work".

However, if you update your binaries but NOT your database, then it will update, immediately after which I assume you would be doing a restart attended (or manually update the database).

Is that correct?

Making sure I understand this fully...

The script automatically does schema updates, NOT content updates. Correct?

So for instance it won't overwrite changes to spells, items, AAs, rules, etc?

Once again, required schema updates are things like adding a table, or a field or changing core mechanic data. Not content oriented data.

I hate people who wait until something is done and then come around to poke it and criticize. I apologize if I appear to be doing that. That isn't what is intended. I think this is a brilliant and useful feature!

That said, wouldn't it have been better in shared_memory instead of world? That way it could update the DB before writing out the tables. Tables affected, including those in shared memory would then be up-to-date before you got to world.

It seems to me that if you do it in world, and tables affected includes those in shared_memory then shared_memory could crash before ever getting to world or it would write old style tables to shared memory which would ultimately cause problems in world/zones.

I could of course be wrong.

I think people are severely missing the point here.

I did this for a mere quality of life benefit for the EQEmu community.

The complaints or suggestions I'm getting are one small fucking step in regards to quality of life as to how you open your server. Patch it, be done within seconds, never see it again until next binary update actually requires a database change.

If you are maintaining a server, updating binaries, you need to update your database, it's that simple. Otherwise, there is no change in the workflow.

No hate intended from me, just wanting to make sure I understand 100%. :-D

Appreciate it!

Oh my!! Going to test this out soon, awesomeness.

Here is a Wiki article that may explain things a little more clearly:

http://wiki.eqemulator.org/p?Automat...ystem&frm=Main

In the end, the system is super simple to use.

This seems cool ... I know U said u have it working for Linux ... is it available for others? If so how do I get it to work?

............What?

FYI this now optionally allow users to pull fresh PEQ AA tables:

http://i.imgur.com/OBrIOCM.png

This is pretty good stuff and will cut down on the errors people are encountering starting and updating a server. Thanks for putting in the time to make this process MUCH easier, especially for newbies or those who are not as familiar with how to script this kind of stuff.
I know I will be using it and will help me to update more frequently.

This is a really positive step in the right direction IMO. Anything that lowers or removes an entry barrier will hopefully result in more people trying it out and making cool things. Also less 'Hulp! It's broken!' posts heh

Just FYI, my old start script redirects the output of all the binaries (world, zone, etc) to a logfile. Turns out when this starts, it prompts for user input if there are updates.... and continues to prompt. 16GB logfile later I discovered this and killed it.

So anyone who redirects output to a logfile, beware.

These are the sorts of things that I need to fix and be brought to attention.

I'm assuming this is Linux.

If your database is up to date you won't have that issue since it will not wait for a prompt.

Can you reproduce to me what the contents of the log is? I'm going to guess its reprinting the menu because the start script is sending characters.

Had this same issue, so Akkadius was already aware and it will be resolved. Until it is fixed, you can get around this by starting world manually the first time after you update by doing the following:

./world

Then you can respond to the prompts, update your db and CTRL+C to end the process. Finally, use your startup script again as normal and you will be running without issues (at least until the next update).

Yeah, I realized the issue after waiting about half an hour and doing a df -h. And yes, linux.

Ultimately, I was bringing my source up to date and my database is all screwed up because I guess bots are screwed up right now? So it was never able to run all the updates properly.

Is this a bit like WSUS offline slipstream updates applied to EQemu? Basically take for example EQemu repack and ensure it stays up to date automatically as the peqDB changes in the future?

Another words combined together a slipstream installation and patching system. I kind of assume it was partly made both to update old DB's easily, but also to ensure your repack stays up to date and doesn't require constant maintenance for users?

No, its not supposed to be a WSUS type of setup. Most people upgrading a server are connected to the internet at the time because they need to pull down new source files to even compile and this script runs no differently.

This is for everyone, to keep databases consistent and also provide us a graceful medium to apply updates effectively.

Yeah sure its being but right in the middle of normal workflow, but if you're up to date you don't notice anything.

At its core its meant to keep a database update with the binary you are running, it fetches updates from github and runs whatever is appropriate.

With it, we also can start providing pre-patch options in it too. Like for example enabling bots at the database level or enabling mercenaries, turning on and off rules and such can be made plug and play with this as well. Lots to evolve with.

Are you running bots currently?

I was, but that was around 6 months ago. I had to rebuild my vps and am just getting around to getting eqemu back up now.

Here is a fix for linux users. Replace your start script as such:

http://wiki.eqemulator.org/i?Module=...Paste=xRJzfmq2

It will run the update routine before ever triggering world, which will all the user take care of updates, exit the program and then everything will continue to flow and start the server up every single time until you decide to upgrade.

Hmm I must be missing something ... how do I go about installing this?

What do you mean? Are you running Linux? Or are you talking about the system itself?

No I do not run linux ... I was talking about the system itself ... I like the idea of being able to quickly install updates .... Is this only for linux?

No being on Linux just means you have to change a little bit of your scripts to make it more friendly.

This explains how it works here, in the end its part of the binary and I've put a lot of effort in making it seamless.

http://wiki.eqemulator.org/p?Automat...-process-works

Hypopathetically speaking, what's the age limit on a database this will upgrade? I see your in your example pic it starts with updates from 2013, anyone run Zeb/Squeeker/older stuff through it for giggles?

Thanks for reminding me, I have the manifest built through all of Github updates. I need to configure the manifest to understand all of the upgrades through SVN.

So technically when I have that done, it should take a super old database all the way up to recent with ease.

Akka's Funhouse for example was just old enough to be a few months ahead of when we started with Git, so I took a 2 year old database and brought it to recent within seconds. Very very nice thing to have.

Just compiled and updated. This worked flawlessly, thank you for adding this feature!

I just wanted to mention that this works GREAT!

For around 20 total hours I tried every guide I could find, every list of steps, to update halls havoc database correctly. Everything I tried would have errors in various sql scripts. Then when it apparently worked, there were things obviously wrong. Things like inventory disappeared completely and all items seemed to be unavailable, yet the items table was full of items. I ended up resurrecting a an old database save from before I started trying to update, and then sourced the data then ran the server.

The automatic updater worked perfectly!


I think there is so much obsolete information out there now, that it is difficult to figure out exactly what to do. This auto-updater is a GREAT feature!

Excellent work, all!

Rick HallsHavoc

This only works if the database runs on the same host as the world server. Your script grabs host from the config file, but never actually uses the $host variable in your mysql commands.

Since it also auto-downloads db_update.pl I can't even fix the script as my changes get overwritten.

Run your updates on the world server?

The world server and the database server are not the same host. You should simply modify the script to add --host="$host" to the various mysql command lines and that would solve the issue.

As a work-around, I've simply set the MYSQL_HOST environment variable prior to running ./world

Even then, the script still doesn't fully update the database (coming from peqbeta_2014-10-11-02:01.sql) as apparently that database is missing some core tables like character_buffs so there are 8 required updates which fail to apply. Not really a problem with the script, per-se, although I'm surprised there isn't a simple empty schema included with the source that one could apply to build an empty, clean and current database so that this whole patch process wouldn't be necessary for a brand new installation. Relying on PEQ to provide the base schema seems silly.

But there are warnings all over the place not to use the October 2014 version of the database. You need to use the September version...

I have yet to see any of those warnings. Other than the one I posted after I figured it out the hard way.

Regardless, that has nothing to do with the fact that the update_db.pl script parses the configuration file for <host> and then ignores it, only working on localhost unless you set the MYSQL_HOST environment variable.

This is a bug in the script, regardless of which peq db one uses (or even if one doesn't use peq at all!)

Wouldn't necessarily call it a bug.

It pulls it down automatically because I've set it that way for a while because at one point the script wasn't capable of checking itself for updates by being passed parameters by the source, could probably change that soon.

Also, why are you using multiple hosts? It's far unecessary unless you actually have load to match the necessity.

98% of the folks who run the script aren't going to need a host option and would simply run it on their database server.

Updated, you're welcome.

https://github.com/EQEmu/Server/comm...a6e1e00cc91214

And

This has to do with PEQ dumps, the schema update does exactly what it needs to do and PEQ missing tables is another issue in itself. I will notify those who oversee those dumps.