0tMEjqK2

1-- Queries for converting auth and characters databases from mangos to trinity. Tested with mangos rev 11896 + lot of custom things and trinity rev b32878f02f801feb0db0a60d528322740d16c01c  from 4th October 2012
2-- go through the queries to know whats happening and edit them accordingly, dont just run them blindly.
3-- wiped are player/pet auras, talents, instance IDs, corpses, groups, arena stats/teams, GM tickets
4-- you might have to split some queries in more parts by guid, or increase innodb_buffer_pool_size in case of error "The total number of locks exceeds the lock table size"
5-- character_glyphs and item_instance convertion taken from trinity forums, made up to date + adding other tables done by me
6-- convert_mg_characters, convert_mg_auth - origin databases
7-- convert_tc_characters, convert_tc_auth - destination databases
8-- USE AT OWN RISK!, make sure you have backup
9
10###################
11## AUTH DATABASE ##
12###################
13USE convert_tc_auth;
14-- format:  table name: columns left to default
15
16-- account: email, online, os, recruiter
17DELETE FROM account;
18INSERT INTO account (id, username, sha_pass_hash, sessionkey, v, s, joindate, last_ip, failed_logins, locked, last_login, expansion, mutetime, locale)
19SELECT               id, username, sha_pass_hash, sessionkey, v, s, joindate, last_ip, failed_logins, locked, last_login, expansion, mutetime, locale FROM convert_mg_auth.account;
20
21-- account_access: RealmID
22DELETE FROM account_access;
23INSERT INTO account_access (id, gmlevel) 
24SELECT                      id, gmlevel FROM convert_mg_auth.account;
25
26-- account_banned
27DELETE FROM account_banned;
28INSERT INTO account_banned SELECT * FROM convert_mg_auth.account_banned;
29
30-- ip_banned
31DELETE FROM ip_banned;
32INSERT INTO ip_banned SELECT * FROM convert_mg_auth.ip_banned;
33
34-- logs
35
36-- realmcharacters
37DELETE FROM realmcharacters;
38INSERT INTO realmcharacters SELECT * FROM convert_mg_auth.realmcharacters;
39
40-- realmlist
41DELETE FROM realmlist;
42INSERT INTO realmlist SELECT * FROM convert_mg_auth.realmlist;
43
44#########################
45## CHARACTERS DATABASE ##
46#########################
47USE convert_tc_characters;
48
49-- account_data
50DELETE FROM account_data;
51INSERT INTO account_data SELECT * FROM convert_mg_characters.account_data;
52
53-- account_instance_times
54
55-- account_tutorial
56DELETE FROM account_tutorial;
57INSERT INTO account_tutorial SELECT * FROM convert_mg_characters.character_tutorial;
58
59-- addons
60
61-- auctionhouse
62-- delete weird items and items put by AHbot
63DELETE FROM convert_mg_characters.auction WHERE itemguid = 0 OR itemowner = 0;
64DELETE FROM auctionhouse;
65INSERT INTO auctionhouse (id, auctioneerguid, itemguid, itemowner, buyoutprice, time, buyguid, lastbid, startbid, deposit)
66SELECT                    id, houseid,        itemguid, itemowner, buyoutprice, time, buyguid, lastbid, startbid, deposit FROM convert_mg_characters.auction;
67
68-- bugreport
69-- corpse
70-- creature_respawn
71-- game_event_condition_save
72-- game_event_save
73-- gameobject_respawn
74-- gm_subsurveys
75-- gm_surveys
76-- gm_tickets
77-- group_instance
78-- group_member
79-- groups
80
81-- guild
82DELETE FROM guild;
83INSERT INTO guild SELECT * FROM convert_mg_characters.guild;
84
85-- guild_bank_eventlog
86DELETE FROM guild_bank_eventlog;
87INSERT INTO guild_bank_eventlog SELECT * FROM convert_mg_characters.guild_bank_eventlog;
88
89-- guild_bank_item
90DELETE FROM guild_bank_item;
91INSERT INTO guild_bank_item SELECT guildid, TabId, SlotId, item_guid FROM convert_mg_characters.guild_bank_item;
92
93-- guild_bank_right
94DELETE FROM guild_bank_right;
95INSERT INTO guild_bank_right SELECT * FROM convert_mg_characters.guild_bank_right;
96
97-- guild_bank_tab
98DELETE FROM guild_bank_tab;
99INSERT INTO guild_bank_tab SELECT * FROM convert_mg_characters.guild_bank_tab;
100
101-- guild_eventlog
102DELETE FROM guild_eventlog;
103INSERT INTO guild_eventlog SELECT * FROM convert_mg_characters.guild_eventlog;
104
105-- guild_member
106DELETE FROM guild_member;
107INSERT INTO guild_member SELECT * FROM convert_mg_characters.guild_member;
108
109-- guild_rank
110DELETE FROM guild_rank;
111INSERT INTO guild_rank SELECT * FROM convert_mg_characters.guild_rank;
112
113-- channels
114
115-- character_account_data
116DELETE FROM character_account_data;
117INSERT INTO character_account_data SELECT * FROM convert_mg_characters.character_account_data;
118
119-- character_action
120DELETE FROM character_action;
121INSERT INTO character_action SELECT * FROM convert_mg_characters.character_action;
122
123-- character_achievement
124DELETE FROM character_achievement;
125INSERT INTO character_achievement SELECT * FROM convert_mg_characters.character_achievement;
126
127-- character_achievement_progress
128DELETE FROM character_achievement_progress;
129INSERT INTO character_achievement_progress SELECT * FROM convert_mg_characters.character_achievement_progress;
130
131-- character_arena_stats
132-- character_aura
133-- character_banned
134
135-- character_battleground_data
136DELETE FROM character_battleground_data;
137INSERT INTO character_battleground_data SELECT * FROM convert_mg_characters.character_battleground_data;
138
139-- character_battleground_random
140DELETE FROM character_battleground_random;
141INSERT INTO character_battleground_random SELECT * FROM convert_mg_characters.character_battleground_random;
142
143-- character_declinedname
144DELETE FROM character_declinedname;
145INSERT INTO character_declinedname SELECT * FROM convert_mg_characters.character_declinedname;
146
147-- character_equipmentsets
148DELETE FROM character_equipmentsets;
149INSERT INTO character_equipmentsets SELECT * FROM convert_mg_characters.character_equipmentsets;
150
151-- character_gifts
152DELETE FROM character_gifts;
153INSERT INTO character_gifts SELECT * FROM convert_mg_characters.character_gifts;
154
155-- character_glyphs
156DELETE FROM character_glyphs;
157INSERT INTO character_glyphs (guid,spec) SELECT DISTINCT guid,spec FROM convert_mg_characters.character_glyphs;
158UPDATE character_glyphs A SET glyph1 = (SELECT glyph FROM convert_mg_characters.character_glyphs WHERE guid = A.guid AND spec = A.spec AND slot = '0');
159UPDATE character_glyphs A SET glyph2 = (SELECT glyph FROM convert_mg_characters.character_glyphs WHERE guid = A.guid AND spec = A.spec AND slot = '1');
160UPDATE character_glyphs A SET glyph3 = (SELECT glyph FROM convert_mg_characters.character_glyphs WHERE guid = A.guid AND spec = A.spec AND slot = '2');
161UPDATE character_glyphs A SET glyph4 = (SELECT glyph FROM convert_mg_characters.character_glyphs WHERE guid = A.guid AND spec = A.spec AND slot = '3');
162UPDATE character_glyphs A SET glyph5 = (SELECT glyph FROM convert_mg_characters.character_glyphs WHERE guid = A.guid AND spec = A.spec AND slot = '4');
163UPDATE character_glyphs A SET glyph6 = (SELECT glyph FROM convert_mg_characters.character_glyphs WHERE guid = A.guid AND spec = A.spec AND slot = '5');
164
165-- character_homebind
166DELETE FROM character_homebind;
167INSERT INTO character_homebind SELECT * FROM convert_mg_characters.character_homebind;
168
169-- character_instance
170
171-- character_inventory
172-- in case of conflicts here check the queries at the bottom of this file
173DELETE FROM character_inventory;
174INSERT INTO character_inventory SELECT guid, bag, slot, item FROM convert_mg_characters.character_inventory;
175
176-- character_pet
177-- convert only tamed (hunter) pets, not summoned ones
178DELETE FROM character_pet;
179INSERT INTO character_pet SELECT * FROM convert_mg_characters.character_pet AS c WHERE c.PetType = 1;
180
181-- character_pet_declinedname
182DELETE FROM character_pet_declinedname;
183INSERT INTO character_pet_declinedname SELECT * FROM convert_mg_characters.character_pet_declinedname;
184
185-- character_queststatus
186DELETE FROM character_queststatus;
187INSERT INTO character_queststatus 
188SELECT guid, quest, status, explored, timer, mobcount1, mobcount2, mobcount3, mobcount4, itemcount1, itemcount2, itemcount3, itemcount4, 0 FROM convert_mg_characters.character_queststatus;
189
190-- character_queststatus_daily
191DELETE FROM character_queststatus_daily;
192INSERT INTO character_queststatus_daily (guid, quest) SELECT * FROM convert_mg_characters.character_queststatus_daily;
193
194-- character_queststatus_rewarded
195DELETE FROM character_queststatus_rewarded;
196INSERT INTO character_queststatus_rewarded SELECT c.guid, c.quest FROM convert_mg_characters.character_queststatus c WHERE c.status = 1 AND c.rewarded = 1;
197
198-- character_queststatus_seasonal
199-- character_queststatus_weekly
200
201-- character_reputation
202DELETE FROM character_reputation;
203INSERT INTO character_reputation SELECT * FROM convert_mg_characters.character_reputation;
204
205-- character_skills
206DELETE FROM character_skills;
207INSERT INTO character_skills SELECT * FROM convert_mg_characters.character_skills;
208
209-- character_social
210DELETE FROM character_social;
211INSERT INTO character_social SELECT * FROM convert_mg_characters.character_social;
212
213-- character_spell
214DELETE FROM character_spell;
215INSERT INTO character_spell SELECT * FROM convert_mg_characters.character_spell;
216
217-- character_spell_cooldown
218-- character_stats
219-- character_talent
220
221-- characters
222DELETE FROM characters;
223INSERT INTO characters (guid, account, name, race, class, gender, level, xp, money, playerBytes, playerBytes2, playerFlags, position_x, position_y, position_z, map, instance_id, instance_mode_mask, orientation, taximask, online, cinematic, totaltime, leveltime, logout_time, is_logout_resting, rest_bonus, resettalents_cost, resettalents_time, trans_x, trans_y, trans_z, trans_o, transguid, extra_flags, stable_slots, at_login, 
224zone, death_expire_time, taxi_path, arenaPoints, totalHonorPoints, todayHonorPoints, yesterdayHonorPoints, totalKills, todayKills, yesterdayKills, chosenTitle, knownCurrencies, watchedFaction, drunk, health, power1, power2, power3, power4, power5, power6, power7, latency, speccount, activespec, exploredZones, equipmentCache, ammoId, knownTitles, actionBars, grantableLevels, deleteInfos_Account, deleteInfos_Name, deleteDate)
225SELECT                  guid, account, name, race, class, gender, level, xp, money, playerBytes, playerBytes2, playerFlags, position_x, position_y, position_z, map, 0,           dungeon_difficulty, orientation, taximask, online, cinematic, totaltime, leveltime, logout_time, is_logout_resting, rest_bonus, resettalents_cost, resettalents_time, trans_x, trans_y, trans_z, trans_o, transguid, extra_flags, stable_slots, at_login,
226zone, death_expire_time, taxi_path, arenaPoints, totalHonorPoints, todayHonorPoints, yesterdayHonorPoints, totalKills, todayKills, yesterdayKills, chosenTitle, knownCurrencies, watchedFaction, drunk, health, power1, power2, power3, power4, power5, power6, power7, 0, speccount, activespec, exploredZones, equipmentCache, ammoId, knownTitles, actionBars, grantableLevels, deleteInfos_Account, deleteInfos_Name, deleteDate FROM convert_mg_characters.characters;
227-- even tho people dont have talents, we have to reset them at login to clean up spells they shouldnt have
228UPDATE characters SET at_login = at_login | 4;
229
230-- instance
231-- instance_reset
232
233-- item_instance
234-- DROP Trinity table and use mangos one
235DROP TABLE IF EXISTS `item_instance`;
236CREATE TABLE `item_instance` (
237  `guid` int(11) unsigned NOT NULL default '0',
238  `owner_guid` int(11) unsigned NOT NULL default '0',
239  `data` longtext,
240  `text` longtext,
241  PRIMARY KEY  (`guid`),
242  KEY `idx_owner_guid` (`owner_guid`)
243) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Item System';
244
245-- Copy mangos data
246INSERT INTO item_instance SELECT * FROM convert_mg_characters.item_instance;
247
248UPDATE item_instance SET data = REPLACE(data,'  ',' ');
249UPDATE item_instance SET data = CONCAT(TRIM(data),' ');
250
251UPDATE `item_instance` SET `data` = CONCAT(
252SUBSTRING_INDEX(`data`, ' ', 59 + 1), ' ',
253SUBSTRING_INDEX(`data`, ' ', -3 -1), '0 ')
254WHERE length(SUBSTRING_INDEX(data, ' ', 64)) < length(data) and length(SUBSTRING_INDEX(data, ' ', 64+1)) >= length(data);
255
256UPDATE item_instance SET data = REPLACE(data,'  ',' ');
257UPDATE item_instance SET data = CONCAT(TRIM(data),' ');
258
259-- Add new fields
260ALTER TABLE `item_instance`
261ADD `itemEntry` mediumint(8) unsigned NOT NULL DEFAULT '0' AFTER `guid`,
262ADD `creatorGuid` int(10) unsigned NOT NULL default '0' AFTER `owner_guid`,
263ADD `giftCreatorGuid` int(10) unsigned NOT NULL default '0' AFTER `creatorGuid`,
264ADD `count` int(10) unsigned NOT NULL default '1' AFTER `giftCreatorGuid`,
265ADD `duration` int(10) unsigned NOT NULL AFTER `count`,
266ADD `charges` text NOT NULL AFTER `duration`,
267ADD `flags` int(10) unsigned NOT NULL default '0' AFTER `charges`,
268ADD `enchantments` text NOT NULL AFTER `flags`,
269ADD `randomPropertyId` bigint(20) NOT NULL default '0' AFTER `enchantments`,
270ADD `durability` int(10) unsigned NOT NULL default '0' AFTER `randomPropertyId`,
271ADD `playedTime` int(10) unsigned NOT NULL default '0' AFTER `durability`;
272
273-- Temporarily change delimiter to prevent SQL syntax errors
274DELIMITER ||
275
276-- Function to convert ints from unsigned to signed
277DROP FUNCTION IF EXISTS `uint32toint32`||
278CREATE FUNCTION `uint32toint32`(input INT(10) UNSIGNED) RETURNS BIGINT(20) SIGNED DETERMINISTIC
279BEGIN
280  RETURN input;
281END||
282
283-- Restore original delimiter
284DELIMITER ;
285
286-- Move data to new fields
287UPDATE `item_instance` SET
288`itemEntry` = SUBSTRING(`data`,
289length(SUBSTRING_INDEX(`data`,' ',3))+2,
290length(SUBSTRING_INDEX(`data`,' ',3+1))-length(SUBSTRING_INDEX(data,' ',3))-1),
291
292`creatorGuid` = SUBSTRING(`data`,
293length(SUBSTRING_INDEX(`data`,' ',10))+2,
294length(SUBSTRING_INDEX(`data`,' ',10+1))-length(SUBSTRING_INDEX(data,' ',10))-1),
295
296`giftCreatorGuid` = SUBSTRING(`data`,
297length(SUBSTRING_INDEX(`data`,' ',12))+2,
298length(SUBSTRING_INDEX(`data`,' ',12+1))-length(SUBSTRING_INDEX(data,' ',12))-1),
299
300`count` = SUBSTRING(`data`,
301length(SUBSTRING_INDEX(`data`,' ',14))+2,
302length(SUBSTRING_INDEX(`data`,' ',14+1))-length(SUBSTRING_INDEX(data,' ',14))-1),
303
304`duration` = SUBSTRING(`data`,
305length(SUBSTRING_INDEX(`data`,' ',15))+2,
306length(SUBSTRING_INDEX(`data`,' ',15+1))-length(SUBSTRING_INDEX(data,' ',15))-1),
307
308`charges` = CONCAT_WS(' ',
309uint32toint32(SUBSTRING(`data`,
310length(SUBSTRING_INDEX(`data`,' ',16))+2,
311length(SUBSTRING_INDEX(`data`,' ',16+1))-length(SUBSTRING_INDEX(data,' ',16))-1)),
312uint32toint32(SUBSTRING(`data`,
313length(SUBSTRING_INDEX(`data`,' ',17))+2,
314length(SUBSTRING_INDEX(`data`,' ',17+1))-length(SUBSTRING_INDEX(data,' ',17))-1)),
315uint32toint32(SUBSTRING(`data`,
316length(SUBSTRING_INDEX(`data`,' ',18))+2,
317length(SUBSTRING_INDEX(`data`,' ',18+1))-length(SUBSTRING_INDEX(data,' ',18))-1)),
318uint32toint32(SUBSTRING(`data`,
319length(SUBSTRING_INDEX(`data`,' ',19))+2,
320length(SUBSTRING_INDEX(`data`,' ',19+1))-length(SUBSTRING_INDEX(data,' ',19))-1)),
321uint32toint32(SUBSTRING(`data`,
322length(SUBSTRING_INDEX(`data`,' ',20))+2,
323length(SUBSTRING_INDEX(`data`,' ',20+1))-length(SUBSTRING_INDEX(data,' ',20))-1))),
324
325`flags` = SUBSTRING(`data`,
326length(SUBSTRING_INDEX(`data`,' ',21))+2,
327length(SUBSTRING_INDEX(`data`,' ',21+1))-length(SUBSTRING_INDEX(data,' ',21))-1),
328
329`enchantments` = SUBSTRING(`data`,
330length(SUBSTRING_INDEX(`data`,' ',22))+2,
331length(SUBSTRING_INDEX(`data`,' ',57+1))-length(SUBSTRING_INDEX(data,' ',22))-1),
332
333`randomPropertyId` = uint32toint32(SUBSTRING(`data`,
334length(SUBSTRING_INDEX(`data`,' ',59))+2,
335length(SUBSTRING_INDEX(`data`,' ',59+1))-length(SUBSTRING_INDEX(data,' ',59))-1)),
336
337`durability` = SUBSTRING(`data`,
338length(SUBSTRING_INDEX(`data`,' ',60))+2,
339length(SUBSTRING_INDEX(`data`,' ',60+1))-length(SUBSTRING_INDEX(data,' ',60))-1),
340
341`playedTime` = SUBSTRING(`data`,
342length(SUBSTRING_INDEX(`data`,' ',62))+2,
343length(SUBSTRING_INDEX(`data`,' ',62+1))-length(SUBSTRING_INDEX(data,' ',62))-1);
344
345-- Drop function
346DROP FUNCTION IF EXISTS `uint32toint32`;
347
348-- Fix heroic item flag
349UPDATE `item_instance` SET `flags`=`flags`&~0x8 WHERE
350SUBSTRING(`data`,
351length(SUBSTRING_INDEX(`data`,' ',3))+2,
352length(SUBSTRING_INDEX(`data`,' ',3+1))-length(SUBSTRING_INDEX(data,' ',3))-1)
353NOT IN (5043,5044,17302,17305,17308,21831);
354
355-- Drop old field
356ALTER TABLE `item_instance` DROP `data`;
357
358-- Execute 09160_characters_item_instance.sql
359SET @allowedFlags := 0x00000001 | 0x00000008 | 0x00000200 | 0x00001000 | 0x00008000 | 0x00010000;
360UPDATE `item_instance` SET `flags` = (`flags` & @allowedFlags);
361
362-- item_refund_instance
363
364-- item_soulbound_trade_data
365DELETE FROM item_soulbound_trade_data;
366INSERT INTO item_soulbound_trade_data SELECT * FROM convert_mg_characters.item_soulbound_trade_data;
367
368-- lag_reports
369-- lfg_data
370
371-- mail
372DELETE FROM mail;
373INSERT INTO mail SELECT * FROM convert_mg_characters.mail;
374
375-- mail_items
376DELETE FROM mail_items;
377INSERT INTO mail_items SELECT mail_id, item_guid, receiver FROM convert_mg_characters.mail_items;
378
379-- pet_aura
380
381-- pet_spell
382DELETE FROM pet_spell;
383INSERT INTO pet_spell SELECT guid, spell, active FROM convert_mg_characters.pet_spell;
384-- cleanup spells of deleted pets
385-- SELECT A.* FROM pet_spell A LEFT JOIN character_pet B ON A.guid = B.id WHERE A.guid NOT IN (SELECT id FROM character_pet);
386DELETE A FROM pet_spell A LEFT JOIN character_pet B ON A.guid = B.id WHERE A.guid NOT IN (SELECT id FROM character_pet);
387
388-- pet_spell_cooldown
389
390-- petition
391DELETE FROM petition;
392INSERT INTO petition SELECT * FROM convert_mg_characters.petition;
393
394-- petition_sign
395DELETE FROM petition_sign;
396INSERT INTO petition_sign SELECT * FROM convert_mg_characters.petition_sign;
397
398-- pool_quest_save
399-- reserved_name
400-- warden_action
401-- worldstates
402
403-- checks for case of problems with character_inventory, handle it manually
404-- my advise is leaving only the item with the highest guid at the conflicting slot
405-- dont forget deleting the item from item_instance too
406/*SELECT A.guid, A.bag, A.slot, A.item, A.item_template  FROM character_inventory AS A INNER JOIN character_inventory AS B INNER JOIN character_inventory AS C
407WHERE A.guid = B.guid
408AND A.bag = B.bag
409AND A.slot = B.slot
410AND A.item_template <> B.item_template
411AND A.bag=C.bag
412AND A.slot=C.slot
413AND A.guid=C.guid
414AND A.item=C.item
415order by A.guid, A.bag, A.slot, A.item, A.item_template*/
416
417/*SELECT A.guid, A.bag, A.slot, A.item, A.item_template  FROM character_inventory AS A INNER JOIN character_inventory AS B INNER JOIN character_inventory AS C
418WHERE A.guid = B.guid
419AND A.bag = B.bag
420AND A.slot = B.slot
421AND A.bag = C.bag
422AND A.slot = C.slot
423AND A.guid = C.guid
424AND A.item <> C.item
425order by A.guid, A.bag, A.slot, A.item, A.item_template*/
426
427-- cleaning up old (completed and rewarded) quests from quest log
428-- SELECT A.* FROM character_queststatus AS A INNER JOIN character_queststatus_rewarded AS B ON A.guid = B.guid WHERE A.guid < 70000 AND A.status = 1 AND A.quest IN (SELECT quest FROM character_queststatus_rewarded WHERE guid = A.guid) GROUP BY A.guid, A.quest;
429-- DELETE A FROM character_queststatus AS A LEFT JOIN character_queststatus_rewarded AS B ON A.guid = B.guid WHERE A.status = 1 AND A.quest IN (SELECT quest FROM character_queststatus_rewarded WHERE guid = A.guid);