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:
Code:
- On world bootup or standalone run of db_update.pl, users will be prompted with a simple menu that we will expand upon later:
============================================================
EQEmu: Automatic Database Upgrade Check
============================================================
Operating System is: MSWin32
(Windows) MySQL is in system path
Path = C:\Program Files\MariaDB 10.0\bin/mysql
============================================================
Binary Database Version: (9057)
Local Database Version: (9057)
Database up to Date: Continuing World Bootup...
============================================================
Retrieving latest database manifest...
URL: https://raw.githubusercontent.com/EQ...e_manifest.txt
Saved: db_update/db_update_manifest.txt
Database Management Menu (Please Select):
1) Backup Database - (Saves to Backups folder)
Ideal to perform before performing updates
2) Backup Database Compressed - (Saves to Backups folder)
Ideal to perform before performing updates
3) Check for pending Database updates
Stages updates for automatic upgrade...
0) Exit
When you select 3) it would stage to see which updates your database does not have.
You would see something like this:
Code:
Reading manifest...
Missing DB Update 9057 '2014_11_13_spells_new_updates.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_11_13_spells_new_updates.sql
Saved: db_update/2014_11_13_spells_new_updates.sql
Missing DB Update 9056 '2014_11_08_RaidMembers.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_11_08_RaidMembers.sql
Saved: db_update/2014_11_08_RaidMembers.sql
Missing DB Update 9055 '2014_10_30_special_abilities_null.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_10_30_special_abilities_null.sql
Saved: db_update/2014_10_30_special_abilities_null.sql
ERROR 1146 (42S02) at line 1: Table 'eoc.raid_leaders' doesn't exist
Missing DB Update 9054 '2014_10_19_raid_group_mentor.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_10_19_raid_group_mentor.sql
Saved: db_update/2014_10_19_raid_group_mentor.sql
Missing DB Update 9053 '2014_10_18_group_mentor.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_10_18_group_mentor.sql
Saved: db_update/2014_10_18_group_mentor.sql
Missing DB Update 9052 '2014_10_13_RaidLeadership.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_10_13_RaidLeadership.sql
Saved: db_update/2014_10_13_RaidLeadership.sql
Missing DB Update 9051 '2014_10_11_RaidMOTD.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_10_11_RaidMOTD.sql
Saved: db_update/2014_10_11_RaidMOTD.sql
Missing DB Update 9050 '2014_09_20_ban_messages.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_09_20_ban_messages.sql
Saved: db_update/2014_09_20_ban_messages.sql
Missing DB Update 9048 '2014_09_09_attack_delay.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_09_09_attack_delay.sql
Saved: db_update/2014_09_09_attack_delay.sql
Missing DB Update 9046 '2014_08_23_player_events_and_player_aa_rate_hourly.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_08_23_player_events_and_player_aa_rate_hourly.sql
Saved: db_update/2014_08_23_player_events_and_player_aa_rate_hourly.sql
Missing DB Update 9045 '2014_08_23_Complete_QueryServ_Table_Structures.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_08_23_Complete_QueryServ_Table_Structures.sql
Saved: db_update/2014_08_23_Complete_QueryServ_Table_Structures.sql
Missing DB Update 9044 '2014_08_20_merchantlist_probability.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_08_20_merchantlist_probability.sql
Saved: db_update/2014_08_20_merchantlist_probability.sql
Missing DB Update 9043 '2014_08_18_spells_new_update.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_08_18_spells_new_update.sql
Saved: db_update/2014_08_18_spells_new_update.sql
Missing DB Update 9042 '2014_08_12_NPC_raid_targets.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_08_12_NPC_raid_targets.sql
Saved: db_update/2014_08_12_NPC_raid_targets.sql
Missing DB Update 9041 '2014_08_02_spells_new.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_08_02_spells_new.sql
Saved: db_update/2014_08_02_spells_new.sql
Missing DB Update 9040 '2014_07_10_npc_spells.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_07_10_npc_spells.sql
Saved: db_update/2014_07_10_npc_spells.sql
Missing DB Update 9039 '2014_07_04_AA_Updates.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_07_04_AA_Updates.sql
Saved: db_update/2014_07_04_AA_Updates.sql
Missing DB Update 9038 '2014_06_25_AA_Updates.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_06_25_AA_Updates.sql
Saved: db_update/2014_06_25_AA_Updates.sql
Missing DB Update 9036 '2014_05_04_SlowMitigationFix.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_05_04_SlowMitigationFix.sql
Saved: db_update/2014_05_04_SlowMitigationFix.sql
Missing DB Update 9035 '2014_04_27_AISpellEffects.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_04_27_AISpellEffects.sql
Saved: db_update/2014_04_27_AISpellEffects.sql
Missing DB Update 9034 '2014_04_25_spawn_events.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_04_25_spawn_events.sql
Saved: db_update/2014_04_25_spawn_events.sql
Missing DB Update 9032 '2014_04_12_SlowMitigation.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_04_12_SlowMitigation.sql
Saved: db_update/2014_04_12_SlowMitigation.sql
Missing DB Update 9031 '2014_04_10_No_Target_With_Hotkey.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_04_10_No_Target_With_Hotkey.sql
Saved: db_update/2014_04_10_No_Target_With_Hotkey.sql
Missing DB Update 9030 '2014_04_04_PhysicalResist.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_04_04_PhysicalResist.sql
Saved: db_update/2014_04_04_PhysicalResist.sql
Missing DB Update 9029 '2014_02_26_virulentvenomAA.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_02_26_virulentvenomAA.sql
Saved: db_update/2014_02_26_virulentvenomAA.sql
Missing DB Update 9028 '2014_02_26_roambox_update.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_02_26_roambox_update.sql
Saved: db_update/2014_02_26_roambox_update.sql
Missing DB Update 9027 '2014_02_20_buff_update.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_02_20_buff_update.sql
Saved: db_update/2014_02_20_buff_update.sql
DB up to date with: 9026 '2014_02_13_spells_new_update.sql'
Missing DB Update 9025 '2014_02_13_Rename_instance_lockout_tables.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_02_13_Rename_instance_lockout_tables.sql
Saved: db_update/2014_02_13_Rename_instance_lockout_tables.sql
DB up to date with: 9024 '2014_02_02_SpellCriticalsAA.sql'
Missing DB Update 9023 '2014_01_27_CritcalMendAA.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_01_27_CritcalMendAA.sql
Saved: db_update/2014_01_27_CritcalMendAA.sql
Missing DB Update 9022 '2014_01_20_Weather.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_01_20_Weather.sql
Saved: db_update/2014_01_20_Weather.sql
Missing DB Update 9021 '2014_01_20_SpellCastingReinforcement.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_01_20_SpellCastingReinforcement.sql
Saved: db_update/2014_01_20_SpellCastingReinforcement.sql
DB up to date with: 9020 '2014_01_20_Not_Extendable.sql'
Missing DB Update 9019 '2014_01_20_MezMastery.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_01_20_MezMastery.sql
Saved: db_update/2014_01_20_MezMastery.sql
Missing DB Update 9018 '2014_01_09_PreservePetSize.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_01_09_PreservePetSize.sql
Saved: db_update/2014_01_09_PreservePetSize.sql
DB up to date with: 9017 '2014_01_08_SpellsNewAdditions.sql'
Missing DB Update 9016 '2014_01_04_SongModCapAAs.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2014_01_04_SongModCapAAs.sql
Saved: db_update/2014_01_04_SongModCapAAs.sql
Missing DB Update 9015 '2013_12_26_MerchantList_Class_Required.sql'
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/git/required/2013_12_26_MerchantList_Class_Required.sql
Saved: db_update/2013_12_26_MerchantList_Class_Required.sql
DB up to date with: 9014 '2013_11_18_AssistRadius.sql'
DB up to date with: 9013 '2013_11_13_Instrument_Singing_Mastery.sql'
DB up to date with: 9012 '2013_11_07_BaseData.sql'
DB up to date with: 9011 '2013_10_31_Recipe_disabling.sql'
DB up to date with: 9009 '2013_10_12_Merc_Special_Abilities.sql'
DB up to date with: 9008 '2013_07_11_NPC_Special_Abilities.sql'
DB up to date with: 9007 '2013_05_05_Item_Tick.sql'
DB up to date with: 9006 '2013_05_05_Account_Flags.sql'
DB up to date with: 9005 '2013_04_08_Salvage.sql'
DB up to date with: 9004 '2013_04_04_NaturesBounty.sql'
DB up to date with: 9002 '2013_03_1_Merc_Rules_and_Equipment.sql'
DB up to date with: 9001 '2013_02_25_Impr_HT_LT.sql'
DB up to date with: 9000 '2013_02_18_Merc_Rules_and_Tables.sql'
DB up to date with: 8000 'mercs.sql'
When this routine completes, you should see something that resembles the following:
Code:
Running Update: 9015 - 2013_12_26_MerchantList_Class_Required.sql
Running Update: 9016 - 2014_01_04_SongModCapAAs.sql
Running Update: 9018 - 2014_01_09_PreservePetSize.sql
Running Update: 9019 - 2014_01_20_MezMastery.sql
Running Update: 9021 - 2014_01_20_SpellCastingReinforcement.sql
Running Update: 9022 - 2014_01_20_Weather.sql
Running Update: 9023 - 2014_01_27_CritcalMendAA.sql
Running Update: 9025 - 2014_02_13_Rename_instance_lockout_tables.sql
Running Update: 9027 - 2014_02_20_buff_update.sql
ERROR 1146 (42S02) at line 4: Table 'eoc.botbuffs' doesn't exist
ERROR 1146 (42S02) at line 8: Table 'eoc.botbuffs' doesn't exist
ERROR 1146 (42S02) at line 12: Table 'eoc.botbuffs' doesn't exist
ERROR 1146 (42S02) at line 16: Table 'eoc.botbuffs' doesn't exist
ERROR 1146 (42S02) at line 20: Table 'eoc.botbuffs' doesn't exist
ERROR 1054 (42S22) at line 22: Unknown column 'not_reflectable' in 'spells_new'
Running Update: 9028 - 2014_02_26_roambox_update.sql
Running Update: 9029 - 2014_02_26_virulentvenomAA.sql
Running Update: 9030 - 2014_04_04_PhysicalResist.sql
Running Update: 9031 - 2014_04_10_No_Target_With_Hotkey.sql
Running Update: 9032 - 2014_04_12_SlowMitigation.sql
Running Update: 9034 - 2014_04_25_spawn_events.sql
Running Update: 9035 - 2014_04_27_AISpellEffects.sql
Running Update: 9036 - 2014_05_04_SlowMitigationFix.sql
Running Update: 9038 - 2014_06_25_AA_Updates.sql
Running Update: 9039 - 2014_07_04_AA_Updates.sql
Running Update: 9040 - 2014_07_10_npc_spells.sql
Running Update: 9041 - 2014_08_02_spells_new.sql
Running Update: 9042 - 2014_08_12_NPC_raid_targets.sql
Running Update: 9043 - 2014_08_18_spells_new_update.sql
Running Update: 9044 - 2014_08_20_merchantlist_probability.sql
Running Update: 9045 - 2014_08_23_Complete_QueryServ_Table_Structures.sql
Running Update: 9046 - 2014_08_23_player_events_and_player_aa_rate_hourly.sql
Running Update: 9048 - 2014_09_09_attack_delay.sql
Running Update: 9050 - 2014_09_20_ban_messages.sql
Running Update: 9051 - 2014_10_11_RaidMOTD.sql
Running Update: 9052 - 2014_10_13_RaidLeadership.sql
Running Update: 9053 - 2014_10_18_group_mentor.sql
Running Update: 9054 - 2014_10_19_raid_group_mentor.sql
Running Update: 9055 - 2014_10_30_special_abilities_null.sql
Running Update: 9056 - 2014_11_08_RaidMembers.sql
Running Update: 9057 - 2014_11_13_spells_new_updates.sql
Reading manifest...
DB up to date with: 9057 '2014_11_13_spells_new_updates.sql'
DB up to date with: 9056 '2014_11_08_RaidMembers.sql'
DB up to date with: 9055 '2014_10_30_special_abilities_null.sql'
DB up to date with: 9054 '2014_10_19_raid_group_mentor.sql'
DB up to date with: 9053 '2014_10_18_group_mentor.sql'
DB up to date with: 9052 '2014_10_13_RaidLeadership.sql'
DB up to date with: 9051 '2014_10_11_RaidMOTD.sql'
DB up to date with: 9050 '2014_09_20_ban_messages.sql'
DB up to date with: 9048 '2014_09_09_attack_delay.sql'
DB up to date with: 9046 '2014_08_23_player_events_and_player_aa_rate_hourly.sql'
DB up to date with: 9045 '2014_08_23_Complete_QueryServ_Table_Structures.sql'
DB up to date with: 9044 '2014_08_20_merchantlist_probability.sql'
DB up to date with: 9043 '2014_08_18_spells_new_update.sql'
DB up to date with: 9042 '2014_08_12_NPC_raid_targets.sql'
DB up to date with: 9041 '2014_08_02_spells_new.sql'
DB up to date with: 9040 '2014_07_10_npc_spells.sql'
DB up to date with: 9039 '2014_07_04_AA_Updates.sql'
DB up to date with: 9038 '2014_06_25_AA_Updates.sql'
DB up to date with: 9036 '2014_05_04_SlowMitigationFix.sql'
DB up to date with: 9035 '2014_04_27_AISpellEffects.sql'
DB up to date with: 9034 '2014_04_25_spawn_events.sql'
DB up to date with: 9032 '2014_04_12_SlowMitigation.sql'
DB up to date with: 9031 '2014_04_10_No_Target_With_Hotkey.sql'
DB up to date with: 9030 '2014_04_04_PhysicalResist.sql'
DB up to date with: 9029 '2014_02_26_virulentvenomAA.sql'
DB up to date with: 9028 '2014_02_26_roambox_update.sql'
DB up to date with: 9027 '2014_02_20_buff_update.sql'
DB up to date with: 9026 '2014_02_13_spells_new_update.sql'
DB up to date with: 9025 '2014_02_13_Rename_instance_lockout_tables.sql'
DB up to date with: 9024 '2014_02_02_SpellCriticalsAA.sql'
DB up to date with: 9023 '2014_01_27_CritcalMendAA.sql'
DB up to date with: 9022 '2014_01_20_Weather.sql'
DB up to date with: 9021 '2014_01_20_SpellCastingReinforcement.sql'
DB up to date with: 9020 '2014_01_20_Not_Extendable.sql'
DB up to date with: 9019 '2014_01_20_MezMastery.sql'
DB up to date with: 9018 '2014_01_09_PreservePetSize.sql'
DB up to date with: 9017 '2014_01_08_SpellsNewAdditions.sql'
DB up to date with: 9016 '2014_01_04_SongModCapAAs.sql'
DB up to date with: 9015 '2013_12_26_MerchantList_Class_Required.sql'
DB up to date with: 9014 '2013_11_18_AssistRadius.sql'
DB up to date with: 9013 '2013_11_13_Instrument_Singing_Mastery.sql'
DB up to date with: 9012 '2013_11_07_BaseData.sql'
DB up to date with: 9011 '2013_10_31_Recipe_disabling.sql'
DB up to date with: 9009 '2013_10_12_Merc_Special_Abilities.sql'
DB up to date with: 9008 '2013_07_11_NPC_Special_Abilities.sql'
DB up to date with: 9007 '2013_05_05_Item_Tick.sql'
DB up to date with: 9006 '2013_05_05_Account_Flags.sql'
DB up to date with: 9005 '2013_04_08_Salvage.sql'
DB up to date with: 9004 '2013_04_04_NaturesBounty.sql'
DB up to date with: 9002 '2013_03_1_Merc_Rules_and_Equipment.sql'
DB up to date with: 9001 '2013_02_25_Impr_HT_LT.sql'
DB up to date with: 9000 '2013_02_18_Merc_Rules_and_Tables.sql'
DB up to date with: 8000 'mercs.sql'
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:
Code:
Database Management Menu (Please Select):
1) Backup Database - (Saves to Backups folder)
Ideal to perform before performing updates
2) Backup Database Compressed - (Saves to Backups folder)
Ideal to perform before performing updates
3) Check for pending Database updates
Stages updates for automatic upgrade...
0) Exit
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.
Changelog Notes:
Code:
== 11/16/2014 ==
demonstar55: fix size issue with ControlBoat_Struct and exploit fix in OP_BoardBoat
Akkadius: Implemented Automatic Database update and versioning system
Akkadius: Created database revision define, this is located in version.h in common #define CURRENT_BINARY_DATABASE_VERSION 9057
- This revision define will need to be incremented each time a database update is made
- Along with a revision define increment, you will need to update the db_update manifest located in:
- https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/db_update_manifest.txt
- An entry needs to be made at the bottom of the manifest, the entry is quite simple
- Example: 9057|2014_11_13_spells_new_updates.sql|SHOW COLUMNS FROM `spells_new` LIKE 'disallow_sit'|empty|
- This latest example is checking to see if the spells_new table contains the column 'disallow_sit', if its empty, the update needs to be ran
- More examples of match types below:
# Example: Version|Filename.sql|Query_to_Check_Condition_For_Needed_Update|match type|text to match
# 0 = Database Version
# 1 = Filename.sql
# 2 = Query_to_Check_Condition_For_Needed_Update
# 3 = Match Type - If condition from match type to Value 4 is true, update will flag for needing to be ran
# contains = If query results contains text from 4th value
# match = If query results matches text from 4th value
# missing = If query result is missing text from 4th value
# empty = If the query results in no results
# not_empty = If the query is not empty
# 4 = Text to match
- The manifest contains all database updates 'Required' to be made to the schema, and it will contain a working backport all the way back to SVN -
currently it is tested and backported through the beginning of our Github repo
- On world bootup or standalone run of db_update.pl, users will be prompted with a simple menu that we will expand upon later:
============================================================
EQEmu: Automatic Database Upgrade Check
============================================================
Operating System is: MSWin32
(Windows) MySQL is in system path
Path = C:\Program Files\MariaDB 10.0\bin/mysql
============================================================
Binary Database Version: (9057)
Local Database Version: (9057)
Database up to Date: Continuing World Bootup...
============================================================
Retrieving latest database manifest...
URL: https://raw.githubusercontent.com/EQEmu/Server/master/utils/sql/db_update_manifest.txt
Saved: db_update/db_update_manifest.txt
Database Management Menu (Please Select):
1) Backup Database - (Saves to Backups folder)
Ideal to perform before performing updates
2) Backup Database Compressed - (Saves to Backups folder)
Ideal to perform before performing updates
3) Check for pending Database updates
Stages updates for automatic upgrade...
0) Exit
Akkadius: Created db_update.pl, placed in utils/scripts folder, used for the automatic database update routine (Linux/Windows)
- db_update.pl script created db_version table if not created, if old one is present it will remove it
Akkadius: Created db_dumper.pl, placed in utils/scripts folder, used for the automatic database update routine backups and standalone backups (Linux/Windows)
Akkadius: World will now check the db_update.pl script on bootup, if the db_update.pl script is not present, it will fetch it remotely before running -
when db_update.pl is done running, world will continue with bootup
Thanks,
Akka
Automatic Database Upgrade and Versioning
Threaded Mode
