Bringing extremely out-of-date servers forward

[Xenithar]

Okay, so I had a kid and my server was physically off for eight years. Luckily, a new CR2032 battery in the motherboard was all that it needed. Everything fired up and we're right where we left off. That said, I probably have a million or so updates to do. Not sure I want to try bringing my ancient box forward because I don't believe that it will be easy. That said, the other option is what I am leaning towards. New box, build current server, import old characters/accounts/etc.

I purchased a new Dell R820 system (Four Xeons, 40 cores, 80 threads, 512GB ECC RAM, two H730 PERC RAID cards and 16 SAS drives for them) that should run MaNGOS loaded without a hitch. I want to migrate to this box anyway, so how difficult would it be to export my custom teleport locations, accounts, characters, etc on the old box and import them onto an up-to-date MaNGOS server? I am assuming that this would be semi-involved also due to possible DB changes.

[Meltie]

Hello Xenithar,

It is highly possible to migrate your old server to a newer version of Mangos. Just need a few things from you below.
- Last commit hash of Mangos, if you have it
- Last database version, if you have it
- The expansion you were running for that server

The important points needed: (Needed to build migration scripts etc)
- Total Accounts
- Total Characters

Depending on the nature of the server, this might have to go into a DM over on Discord. If you don't want to post anything here, feel free to message me on here.

[Xenithar]

The database version info is as follows.

MariaDB [realms]> SELECT * FROM db_version;
+---------+-----------+---------+--------------------------+------------------------------------------------------------------------------------------+
| version | structure | content | description              | comment                                                                                  |
+---------+-----------+---------+--------------------------+------------------------------------------------------------------------------------------+
|      21 |         1 |       0 | revision_refactor        |                                                                                          |
|      21 |         1 |       1 | dbdocs update            | Added some dbdocs updates                                                                |
|      21 |         1 |       2 | dbdocs update            | Filled in some fields and added missing subtables back, as they had dropped out somehow. |
|      21 |         1 |       3 | Remove field from dbDocs | Remove unused field from dbDocs Subtable table                                           |
+---------+-----------+---------+--------------------------+------------------------------------------------------------------------------------------+

I am running this server as zero, classic, vanilla, whatever you want to call it.

We have twelve active accounts and thirty characters. I cannot figure out where to find the hash, but I have logs dating back to 2016. Thanks for the help and guidance.

[Meltie]

Thanks for the details, I see your account table (realms) has a possible commit hash of 03b9425c1db5db1693868f2bc28119659cf0d91b from September 11th, 2016.

Is it possible to do the same thing for characters and world databases?

[Xenithar]

The world database.

21	1	0	revision_refactor	
21	1	1	Fix Dire Maul creature spawn	Fix rare creatures spawn in Dire Maul arena
21	1	2	Fix startup errors	
21	1	3	strings for new gobj commands	strings for new gobj commands: state, lootstate
21	1	4	delete doubled MC runes	delete doubled molten core rune spawns
21	1	5	remove doubled lothos gossip	delete doubled gossip_menu_option for the teleport to molten core
21	1	6	list_aura_command	core adds another parameter to be printed out: aura stack number
21	1	7	remove_unused_tables	remove_unused_tables - pvpstats_battlegrounds.and players
21	1	8	update_dbdocs_entries	update_dbdocs_entries - for recently changed tables
21	1	9	Backports part2	Second Set of Backports
21	1	10	Clean up server startup	Clean up server startup
21	1	11	Fix last startup errors	server - fix last startup errors
21	1	12	clearcasting_proc	Strings for added gobj commands: state, lootstate
21	1	13	item_template_dbdocs_update	New subtables added, and Notes and field comments added to the item_template table
21	1	14	Presence_of_Mind	Better proc condition for mages Presence of Mind
21	1	15	Improved_Sap	Rogue - Improved Sap
21	1	16	improved_trap_radius	improved_trap_radius
21	1	17	Warlock_Shadow_Vulnerability	Warlock Shadow Vulnerability: proc on shadow dmg 
21	1	18	enchanted_moonstalker	enchanted_moonstalker_cloak
21	1	19	SD3_Updates	Updates to SD3
21	1	20	No_XP_Rate	Set NO_XP_ON_KILL Flag for Flesh Eating Worm, Anvilrage Reservist, Rotting Worm
21	1	21	dbdocs update for custom_texts	New subtables added, and Notes and field comments added to the custom_texts table, plus corrections made to 2 item_template submodule tables
21	1	22	dbdocs updates	Added New subtables and replace missing subtable
21	1	23	dbdocs updates	Updates made to dbdocs table, subtables, and fields
21	1	24	dbdocs updates	New subtables added, Notes and field comments added, up to and including the quest_template table
21	1	25	dbdocs updates	New subtables added, Notes and field comments added. Updates and fixes completed at this point for the World database.
21	1	26	quest_level_fixes	Corrected the minimum and maximum level values for multiple quests.
21	2	1	Added disables table	Added disables table.
21	2	2	NPC 10578 gossip fix	NPC 10578 gossip fix
21	2	3	NPC 15213 event_ai fix	Add missing spells 9672, 11831, 12058 and 13339
21	3	1	Fix paladin SoR	Update the wrong version of SoR at char creation
21	3	2	Fix Random MMGen Scripts	Update the wrong values for SCRIPT_COMMAND_MOVEMENT
21	4	1	Refactor areatrigger_teleport	Implement condition system AT
21	4	2	many, many updates	many, many updates
21	5	1	creature_template addition	Add 2 extra model fields in creature_template
21	5	2	BG template addition	Prevent exiting starting area in BG
21	5	3	BRD_Ring_Of_Law	BRD Ring Of Law fix
21	5	4	MC_Updates	a variety of fixes for MC
21	5	5	mass updates	mass updates and fixes
21	5	6	quest Items of Power	nonzero trap radius allows the chest to spawn
21	5	7	Adjust Fireball spell	Adjust Low-level spells cast by high-level players
21	6	1	ticket_commands_update	GM_ticket_commands_update
21	7	1	dbscripts_refactor	dbscripts_refactor
21	7	2	Forsaken Courier drop fix	Quest: Hint of a new plague fix
21	7	3	Creature curhealth fix	Creature:Some Curhealth error fix
21	8	1	warden_refactor	warden_refactor
21	8	2	Paladin Spell bonus fix	Paladin Spell bonus fix
21	9	1	warden_keys_adjust	warden_keys_adjust
21	10	1	warden_table_redone	warden_table_redone_including_check_grouping
21	10	2	Morbent_Fel_fix	Weakened_Morbent_Fel_fix
21	10	3	startup_error_fixes	fix_several_startup_errors
21	11	1	quest_relations	modify_quest_relations_tables
21	11	2	dbdocs_cleanup_pt1	dbdocs_cleanup_pt1_db_scripts
21	11	3	additional_warden	additional_warden_updates
21	11	4	warden_dbdocs	warden_dbdocs_updates
21	11	5	relations_cleanup	remove_old_relations_tables
21	11	6	dbdocs_quest_relations	dbdocs_quest_relations
21	11	7	fix_bg_buffs	fix_battleground_buffs
21	11	8	various_fixes	various_fixed
21	11	9	start up fixes	server start up fixes
21	11	10	frostwhisper door fix	Door of the Ras Frostwhisper room 
21	11	11	fix various issues	Fix various issues on the tracker
21	11	12	fix quest 1222	Fix quest 1222 - stinkys escape
21	11	13	Blackrock fixes	Some Blackrock fixes and Fixed quest 2936
21	11	14	Fixed req. of PvP items	Fixed faction requirements of PvP items
21	11	15	Taelan_fixes	Fixed missing Taelan waypoint
21	11	16	Warlord Krom'zar_fixes	Fixed stats for Warlord Krom'zar
21	11	17	Warlord Krom'zar_weapon_fix	Fixed weapon for Warlord Krom'zar
21	11	18	loot_table_fixes	Fixed Loot Table for several bosses
21	11	19	respawn_time_fixes	Fixed Loot Table for several bosses
21	11	20	dragonkin_eggs_fixes	dragonkin_eggs_fixes
21	11	21	fuelwood_quests_fixes	fuelwood repeatable quests
21	11	22	felwood_quests_fixes	felwood repeatable quests
21	11	23	dungeon_boss_dmg_fix	Fix damage of instance bosses
21	11	24	fix_dungeon_boss_control	Fix control of instance bosses
21	11	25	quest_item_drop_fixes	quest_item_drop_fixes
21	11	26	improved_fixes	improved_fixes
21	11	27	dbdocs_update_dbscripts	dbdocs_update_dbscripts
21	11	28	creature_update	creature_update
21	11	29	Rare_drops_fix	Rare_drops_fix
21	11	30	BOE_Drop_Chance	BOE_Drop_Chance
21	11	31	Fix_SD3_Texts	Fix_SD3_Texts
21	11	32	Various_fixes_and_reverts	Various_fixes_and_reverts
21	11	33	dragonkin_eggs_fixes	dragonkin_eggs_fixes
21	11	34	Adjust_Onyxia_and_Reg_Windsor	Adjust_Onyxia_and_Reginald_Windsor
21	11	35	[DBDOCS] Updated some fields	[DBDOCS] Updated some field descriptions
21	11	36	Adjust_Demon_Portal_Time	Adjust_Demon_Portal_Time
21	11	37	Quest_4224_Update	Quest_4224_Update
21	11	38	Update_Quests_4001_and_4342	Update_Quests_4001_and_4342
21	11	39	Homing_robots_speed	Homing_robots_speed
21	11	40	Fixed_quests_4736_and_4739	Fixed_quests_4736_and_4739
21	11	41	Fix_Quest_2986	Fix_Quest_2986
21	11	42	Fix_Kroshius	Fix_Kroshius
21	11	43	various_quest_fixes	various_quest_fixes
21	11	44	Quest 27469	Quest 27469 Friends on The Other Side
21	11	45	Update_DBScript_Type	Update_DBScript_Type
21	11	46	Grimm Guzzler	Grimm Guzzler updates from cmangos
21	11	47	Remove field from dbDocs	Remove unused field from dbDocs Subtable table

And for the characters.

21	1	0	revision_refactor	
21	2	0	Honor Counting	Fix the counting of honor
21	2	1	Characters dbdocs update	Characters database dbdocs update to the fields and new tables added
21	3	1	Fix SoR paladin	Remove Judgement spell and wrong version of SoR
21	4	1	match_client_limits	match_client_limits
21	5	1	characters_pvpstats	characters_pvpstats

 

[Meltie]

This is possible to do the migration without much issues. Can't promise all talents, auction house and mail data will survive the migration. But accounts and characters shouldn't be affected much.

 

There is a 97% chance of success for this.

[Xenithar]

So what is involved in this? I am thinking export each account and associated character, pet, inventory, bank, etc. Setup the new stuff and either import or modify the exported SQL to add or remove columns as needed, then import.

[Meltie]

I'll be comparing your version's structure in the database and making a migration script to port the data over to the new databases. Manually editing and/or removing data can cause some data loss. With the scripts, I can ensure data integrity moving the data from one database to another. Column count per table is key here. 

[Xenithar]

Sorry for the delay in my response. I came down with something heavy on the first. Fever, nausea, congestion, the whole nine yards. I can get up and move today but I'm still fighting an on/off fever. I can get you actual data if you need it.

[Meltie]

Yeah, you can sent me all the data and will migrate your stuff to the latest version. I have a few days coming up for days off from work and can start working on it. Just send me the files in private messaging on here.

[Xenithar]

I have an issue I'm trying to work around. This server is ancient. It runs MariaDB 5.6. I cannot use mariadb-dump in DBeaver to dump the databases to my rig. I can dump them on the server, but it's virtualized inside of XenServer. I've spent an hour trying to get mount to mount one of my Windows shares and it is plagued by the "Host is offline" bug. Can't mount the share no matter what version of SMB is specified. This means no easy access to my dumps. Pain in the rear! I mean, it was turned off for nearly a decade.

Would it be easier to work on SQL or something that I can run to bring it forward while I work out the issue with SMB? The new server (Dell R820) will likely be running Server 2022 and Hyper-V with 2022 VMs. SQLServer for the DB.

[Meltie]

@Xenithar Have you tried using HeidiSQL to connect to your MySQL server and dump it from there? Just wondering.

[Xenithar]

No. I dumped MySQL years ago due to security and performance issues and switched to MariaDB. My MariaDB install was originally managed by MySQL Workbench. Been using DBeaver ever since. I might give it a whirl, but I am asking my members to vote on either doing this massive upgrade or just starting fresh on the current build.