Your v4 MySQL and You (EMU 4.3.4 Repack-Exporting/Importing to v6.1) w/o CharImporter

[raist77]

So I grabbed the v4 repack from a post from some other forums. Eventually I found my way here. Then I saw there were more current versions of the 4.3.4 repack, so I happily donated and got my hands on the latest release. Of course I wanted to copy existing characters from v4 to v6.1, but the CharImporter that's floating around did not work for me, as it crashed every time I tried to import. Some of you might not have this issue and congratulations if it works for you as that is surely the easiest way to migrate from v4 to v6.1. If the CharImporter does NOT work for you, keep reading.

You may set your work space up to your particular satisfaction. I had both my v4 and v6.1 dbs running side-by-side to make exporting/importing a bit easier. It doesn't matter if you do that or not. I used HeidiSQL for all my exports/imports.

1. Go into the _Server folder for your v4 repack and run the MySQL.bat. That starts your v4 db and allows you to access it via HeidiSQL.
2. Start HeidiSQL and either create a new session with the login and port information to connect to your v4 db or use your existing one.
3. Once connected, start with your AuthDB and select the Account table (You should click on the DATA tab on the right side pane towards the top to verify there IS data in the table before you do anything else).
4. Right-click on the Account table and select Export DB as SQL (the Table Tools window will open).
5. Inside the Table Tools window, on the left you should see the Account table is checked and on the right you should see two checks in both CREATE boxes, if not, make sure they are both checked.
6. Switch the DATA drop-down to INSERT (The default setting is INSERT IGNORE).
7. Switch the Output drop-down to Single .sql file.
8. Click the little diskette to save the exported file and give it a name, like: AuthAccount, CharactersCharacterInventory, etc (some tables have similar names in both the Auth and Characters dbs, so name the exports something that makes sense to you so you can keep track of your imports).
9. Click Export and the table will be exported into an SQL file, ready to be inserted into the new v6.1 db.

My server is small, 4 players and we just got started, so there wasn't a ton of information I needed to export/import. However, I will try to list all the pertinent tables. Please feel free to add any tables to the list that I missed. I used the naming convention I mentioned above; all tables coming from the AuthDB are named AuthAccount, AuthRealmList, etc and from the CharactersDB they are named CharactersAccountData, CharactersCharacters, CharactersCharacterPet, etc. Again...this is personal preference, but it is VERY easy to get tables confused if it's late and you're in a rush. Follow the previous steps to export each table in the list below into it's own SQL file.

NOTE: Combining multiple tables into one SQL file would most certainly save time, but it will create more headaches once you move into the next set of steps.

AUTHDB TABLE LIST
Account (AuthAccount.sql)
Account_Access (AuthAccountAccess.sql)
RealmCharacters (AuthRealmCharacters.sql)
RealmList (AuthRealmList.sql)


CHARACTERSDB TABLE LIST
Account_Data (CharactersAccountData.sql)
Account_Instance_Times (CharactersAccountInstanceTimes.sql)
Account_Tutorial (CharactersAccountTutorial.sql)
Characters (CharactersCharacters.sql)
Character_Account_Date (CharactersCharacterAccountData.sql) NOTE: We already have an AccountData.sql. Naming is important.
Character_Achievement (CharactersCharacterAchievement.sql)
Character_Achievement_Progress (CharactersCharacterAchivementProgress.sql)
Character_Action (CharactersCharacterAction.sql)
Character_Currency (CharactersCharacterCurrency.sql) NOTE: This table structure changed from v4 to v6.1
Character_Current_Artifacts (CharactersCharacterCurrentArtifacts.sql)
Character_Digsites (CharactersCharacterDigsites.sql)
Character_Glyphs (CharactersCharacterGlyphs.sql)
Character_Homebind (CharactersCharacterHomebind.sql)
Character_Instance (CharactersCharacterInstance.sql)
Character_Inventory (CharactersCharacterInventory.sql)
Character_Pet (CharactersCharacterPet.sql)
Character_QuestStatus (CharactersCharacterQuestStatus.sql)
Character_QuestStatus_Rewarded (CharactersCharacterQuestStatusRewarded.sql)
Character_Reputation (CharactersCharacterReputation.sql)
Character_Skills (CharactersCharacterSkills.sql)
Character_Spells (CharactersCharacterSpells.sql)
Character_Stats (CharactersCharacterStats.sql)
Character_Talent (CharactersCharacterTalent.sql)
Item_Instance (CharactersItemInstance.sql) NOTE: This table ties directly into the Character_Inventory table.
Mail (CharactersMail.sql)
Mail_Items (CharactersMailItems.sql)

It is very possible that you might need to grab more table information. As I said, my server is small (family only) and just getting started. Look through the other tables in the CharactersDB and export accordingly if they contain data.

Now comes the "fun" part. Even though we exported the above tables into SQL files, we can't directly import them to populate the tables in our v6.1 DB. We have to edit the SQL files first. Here is an example of an SQL file that was exported:

-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               5.5.9-log - MySQL Community Server (GPL)
-- Server OS:                    Win32
-- HeidiSQL Version:             8.3.0.4694
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

-- Dumping database structure for auth
CREATE DATABASE IF NOT EXISTS `auth` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `auth`;


-- Dumping structure for table auth.realmcharacters
CREATE TABLE IF NOT EXISTS `realmcharacters` (
  `realmid` int(10) unsigned NOT NULL DEFAULT '0',
  `acctid` int(10) unsigned NOT NULL,
  `numchars` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`realmid`,`acctid`),
  KEY `acctid` (`acctid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Realm Character Tracker';

-- Dumping data for table auth.realmcharacters: ~8 rows (approximately)
/*!40000 ALTER TABLE `realmcharacters` DISABLE KEYS */;
INSERT INTO `realmcharacters` (`realmid`, `acctid`, `numchars`) VALUES
	(1, 1, 0),
	(1, 2, 2),
	(1, 3, 2),
	(1, 4, 1),
	(1, 5, 1),
	(2, 3, 0),
	(2, 4, 0),
	(2, 5, 0);
/*!40000 ALTER TABLE `realmcharacters` ENABLE KEYS */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

There is a lot of junk in there that needs to be removed in order to properly import the SQL files. You basically want to remove everything EXCEPT the INSERT INTO section. So it would look like this:

INSERT INTO `realmcharacters` (`realmid`, `acctid`, `numchars`) VALUES
	(1, 1, 0),
	(1, 2, 2),
	(1, 3, 2),
	(1, 4, 1),
	(1, 5, 1),
	(2, 3, 0),
	(2, 4, 0),
	(2, 5, 0);

Big difference, but that's the ONLY way I could get the files to import without errors. So you'll need to go through and edit ALL the SQL files you just exported and remove the junk. Leaving ONLY the INSERT section for each one.

Now that you have cleaned up all the SQL files, the next step is to get them loaded into your new v6.1 DB.

1. Start your v6.1 DB if you haven't already and connect to it with HeidiSQL.
2. Begin with the AuthDB and select Account.
3. View the DATA tab on the right and if the table contains data, right-click on the table name and select Empty Table(s). DO NOT DROP THE TABLE.
4. Once the table has been emptied, make sure that table is still selected and go to the File menu and select Load SQL File.
5. In the Open window, navigate to the folder you saved all your exported v4 tables and select the AuthAccount (or whichever table you are importing to).
6. You will see the SQL file get loaded into the Query tab in HeidiSQL.
7. Click on the BLUE PLAY button to execute the SQL statement (inserting all the data into the empty table).

Ta Da! You have just exported and imported to the new database.
Rinse and repeat until you have migrated all the tables into the new DB.

***IMPORTANT***
When you are done importing all the tables, open your WoW 4.3.4 Client folder and DELETE the Cache folder.

NOTE: I removed the AHBOT character, but left the AHBOT Account. After all my accounts and characters (with all their data) were imported, I added the AHBOT back into the Characters-Characters table. When you add the AHBOT back, DO NOT FORGET TO CHANGE YOUR WORLDSERVER AHBOT SETTINGS.

If anyone has anything to add to this, feel free. This was shooting from the hip late last night. Thanks to ALL the EMU staff for their hardwork! Thanks to Iroflu for trying to help me out

Happy gaming!

 

[Mr. Satan]

Nice tutorial! This is definitely very helpful!

+rep!

 

[znzz]

Wow great tutorial ! Will prolly use this in future , gj!

 

[skaleo]

Thanks.
Short simple to understad guide.

If Someone Has guild then Do These Tables to

Guild (CharactersGuild.sql)
Guild_Achievement (CharactersGuildAchievement.sql)
Guild_Achievement_Progress (CharactersGuildAchievementProgress.sql)
Guild_Bank_Item (CharactersGuildBankItem.sql)
Guild_Bank_Right (CharactersGuildBankRight.sql)
Guild_Bank_Tab (CharactersGuildBanktab.sql)
Guild_Eventlog (CharactersGuildEventlog.sql)
Guild_Member (CharactersGuildMember.sql)
Guild_Newslog (CharactersGuildNewslog.sql)
Guild_Rank (CharactersGuildRank.sql)