Yes that’s what you should do. The Characters DB revision working with this converter is 10475.
So you must apply the converter to copy your characters, then update you characters DB to the last revision
Yeah but i don’t know where to get the Char DB rev 10475 structure, do you know where to get it?? Also, is this 10475 the Trinity rev number or mangos rev number??
EDIT—
Ok I think I got the main struc, trinity doesn’t have a mangos like char_db_version table?? It is pretty difficult to know in what rev are we now.
Yesterday I rewrote converter from mangos to trinity and tested it a bit(mainly checked errors, items, mails, structure and data). Here is working sql converter from mangos 10905 to trinity commit 6dbae56f5774568365ac62c087fe397d16d68036.
WARNING! BACKUP EVERYTHING BEFORE APPLY!
Convert realmd > auth:
[SPOILER]
[CODE]USE auth;
INSERT INTO account (id,username,sha_pass_hash,email,joindate,last_ip,failed_logins,locked,last_login,expansion,mutetime,locale) SELECT id,username,sha_pass_hash,email,joindate,last_ip,failed_logins,locked,last_login,expansion,mutetime,locale FROM realmd.account;
INSERT INTO realmcharacters SELECT * FROM realmd.realmcharacters;
INSERT INTO account_access (id,gmlevel,RealmID) SELECT id,gmlevel,active_realm_id FROM realmd.account;
UPDATE account_access SET RealmID = -1;
INSERT INTO realmlist (id,name,address,port,icon,timezone,allowedSecurityLevel,population,gamebuild) SELECT id,name,address,port,icon,timezone,allowedSecurityLevel,population,realmbuilds FROM realmd.realmlist;
INSERT INTO account_banned SELECT * FROM realmd.account_banned;
INSERT INTO ip_banned SELECT * FROM realmd.ip_banned;
INSERT INTO uptime (realmid,starttime,startstring,uptime,maxplayers) SELECT realmid,starttime,startstring,uptime,maxplayers FROM realmd.uptime;[/CODE][/SPOILER]
Convert main part of characters_mangos > characters_trinity:
[SPOILER]
[CODE]USE characters_trinity;
INSERT INTO account_data SELECT * FROM characters_mangos.account_data;
INSERT INTO arena_team SELECT * FROM characters_mangos.arena_team;
INSERT INTO arena_team_member SELECT arenateamid,guid,played_week,wons_week,played_season,wons_season FROM characters_mangos.arena_team_member;
INSERT INTO arena_team_stats SELECT * FROM characters_mangos.arena_team_stats;
INSERT INTO auctionhouse SELECT id,houseid,itemguid,itemowner,buyoutprice,time,buyguid,lastbid,startbid,deposit FROM characters_mangos.auction;
UPDATE auctionhouse SET auctioneerguid = 23442;
INSERT INTO character_account_data SELECT * FROM characters_mangos.character_account_data;
INSERT INTO character_achievement SELECT * FROM characters_mangos.character_achievement;
INSERT INTO character_achievement_progress SELECT * FROM characters_mangos.character_achievement_progress;
INSERT INTO character_action SELECT * FROM characters_mangos.character_action;
INSERT INTO character_battleground_data SELECT * FROM characters_mangos.character_battleground_data;
INSERT INTO character_battleground_random SELECT * FROM characters_mangos.character_battleground_random;
INSERT INTO character_declinedname SELECT * FROM characters_mangos.character_declinedname;
INSERT INTO character_equipmentsets SELECT * FROM characters_mangos.character_equipmentsets;
INSERT INTO character_gifts SELECT * FROM characters_mangos.character_gifts;
INSERT INTO character_homebind SELECT * FROM characters_mangos.character_homebind;
INSERT INTO character_instance SELECT * FROM characters_mangos.character_instance;
INSERT INTO character_inventory SELECT guid,bag,slot,item FROM characters_mangos.character_inventory;
INSERT INTO character_pet SELECT * FROM characters_mangos.character_pet;
INSERT INTO character_queststatus SELECT guid,quest,status,explored,timer,mobcount1,mobcount2,mobcount3,mobcount4,itemcount1,itemcount2,itemcount3,itemcount4 FROM characters_mangos.character_queststatus;
INSERT INTO character_queststatus_daily (guid,quest) SELECT * FROM characters_mangos.character_queststatus_daily;
INSERT INTO character_queststatus_weekly SELECT * FROM characters_mangos.character_queststatus_weekly;
INSERT INTO character_reputation SELECT * FROM characters_mangos.character_reputation;
INSERT INTO character_skills SELECT * FROM characters_mangos.character_skills;
INSERT INTO character_social SELECT * FROM characters_mangos.character_social;
INSERT INTO character_spell SELECT * FROM characters_mangos.character_spell;
INSERT INTO character_stats SELECT * FROM characters_mangos.character_stats;
INSERT INTO character_tutorial SELECT * FROM characters_mangos.character_tutorial;
INSERT INTO guild SELECT * FROM characters_mangos.guild;
INSERT INTO guild_bank_eventlog SELECT * FROM characters_mangos.guild_bank_eventlog;
INSERT INTO guild_bank_item SELECT guildid,TabId,SlotId,item_guid FROM characters_mangos.guild_bank_item;
INSERT INTO guild_bank_right SELECT * FROM characters_mangos.guild_bank_right;
INSERT INTO guild_bank_tab SELECT * FROM characters_mangos.guild_bank_tab;
INSERT INTO guild_eventlog SELECT * FROM characters_mangos.guild_eventlog;
INSERT INTO guild_member SELECT * FROM characters_mangos.guild_member;
INSERT INTO guild_rank SELECT * FROM characters_mangos.guild_rank;
INSERT INTO mail SELECT * FROM characters_mangos.mail;
INSERT INTO mail_items SELECT mail_id,item_guid,receiver FROM characters_mangos.mail_items;
INSERT INTO pet_spell SELECT * FROM characters_mangos.pet_spell;
INSERT INTO petition SELECT * FROM characters_mangos.petition;
INSERT INTO petition_sign SELECT * FROM characters_mangos.petition_sign;
INSERT INTO reserved_name SELECT * FROM mangos.reserved_name;
UPDATE worldstates set value=(SELECT NextArenaPointDistributionTime from characters_mangos.saved_variables) where entry=20001;
UPDATE worldstates set value=(SELECT NextWeeklyQuestResetTime from characters_mangos.saved_variables) where entry=20002;
UPDATE worldstates set value=(SELECT NextRandomBGResetTime from characters_mangos.saved_variables) where entry=20003;
INSERT INTO characters (guid,account,name,race,class,gender,level,xp,money,playerBytes,playerBytes2,playerFlags,position_x,position_y,position_z,map,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,zone,death_expire_time,taxi_path,arenaPoints,totalHonorPoints,todayHonorPoints,yesterdayHonorPoints,totalKills,todayKills,yesterdayKills,chosenTitle,knownCurrencies,watchedFaction,drunk,health,power1,power2,power3,power4,power5,power6,power7,specCount,activeSpec,exploredZones,equipmentCache,ammoId,knownTitles,actionBars) SELECT guid,account,name,race,class,gender,level,xp,money,playerBytes,playerBytes2,playerFlags,position_x,position_y,position_z,map,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,zone,death_expire_time,taxi_path,arenaPoints,totalHonorPoints,todayHonorPoints,yesterdayHonorPoints,totalKills,todayKills,yesterdayKills,chosenTitle,knownCurrencies,watchedFaction,drunk,health,power1,power2,power3,power4,power5,power6,power7,specCount,activeSpec,exploredZones,equipmentCache,ammoId,knownTitles,actionBars FROM characters_mangos.characters;[/CODE][/SPOILER]
Convert glyphs:
[SPOILER]
[CODE]USE characters_trinity;
INSERT INTO character_glyphs (guid,spec) SELECT DISTINCT guid,spec FROM characters_mangos.character_glyphs;
UPDATE character_glyphs a SET glyph1 = (SELECT glyph FROM characters_mangos.character_glyphs WHERE guid = a.guid AND spec = a.spec AND slot = ‘0’);
UPDATE character_glyphs a SET glyph2 = (SELECT glyph FROM characters_mangos.character_glyphs WHERE guid = a.guid AND spec = a.spec AND slot = ‘1’);
UPDATE character_glyphs a SET glyph3 = (SELECT glyph FROM characters_mangos.character_glyphs WHERE guid = a.guid AND spec = a.spec AND slot = ‘2’);
UPDATE character_glyphs a SET glyph4 = (SELECT glyph FROM characters_mangos.character_glyphs WHERE guid = a.guid AND spec = a.spec AND slot = ‘3’);
UPDATE character_glyphs a SET glyph5 = (SELECT glyph FROM characters_mangos.character_glyphs WHERE guid = a.guid AND spec = a.spec AND slot = ‘4’);
UPDATE character_glyphs a SET glyph6 = (SELECT glyph FROM characters_mangos.character_glyphs WHERE guid = a.guid AND spec = a.spec AND slot = ‘5’);[/CODE][/SPOILER]
Last part of convertion - item_instance table:
[CODE]USE characters_trinity;
/* DROP Trinity table and use Mangos one */
DROP TABLE IF EXISTS item_instance;
CREATE TABLE item_instance (
guid INT(11) UNSIGNED NOT NULL DEFAULT ‘0’,
owner_guid INT(11) UNSIGNED NOT NULL DEFAULT ‘0’,
data LONGTEXT,
text TEXT,
PRIMARY KEY (guid),
KEY idx_owner_guid (owner_guid)
) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT=‘Item System’;
/* Copy Mangos data */
INSERT INTO item_instance SELECT * FROM characters_mangos.item_instance;
/* Execute 07893_characters_item_instance.sql */
UPDATE item_instance SET DATA = REPLACE(DATA,’ ‘,’ ');
UPDATE item_instance SET DATA = CONCAT(TRIM(DATA),’ ');
UPDATE item_instance SET data = CONCAT(
SUBSTRING_INDEX(data, ’ ', 59 + 1), ’ ',
SUBSTRING_INDEX(data, ’ ', -3 -1), '0 ')
WHERE LENGTH(SUBSTRING_INDEX(DATA, ’ ', 64)) < LENGTH(DATA) AND LENGTH(SUBSTRING_INDEX(DATA, ’ ', 64+1)) >= LENGTH(DATA);
UPDATE item_instance SET DATA = REPLACE(DATA,’ ‘,’ ');
UPDATE item_instance SET DATA = CONCAT(TRIM(DATA),’ ');
/* Don’t Execute 07904_characters_item_instance.sql coz mangos already has this field */
/* Execute 09090_characters_item_instance.sql */
– Add new fields
ALTER TABLE item_instance
ADD itemEntry MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT ‘0’ AFTER guid,
ADD creatorGuid INT(10) UNSIGNED NOT NULL DEFAULT ‘0’ AFTER owner_guid,
ADD giftCreatorGuid INT(10) UNSIGNED NOT NULL DEFAULT ‘0’ AFTER creatorGuid,
ADD count INT(10) UNSIGNED NOT NULL DEFAULT ‘1’ AFTER giftCreatorGuid,
ADD duration INT(10) UNSIGNED NOT NULL DEFAULT ‘0’ AFTER count,
ADD charges TINYTEXT AFTER duration,
ADD flags MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT ‘0’ AFTER charges,
ADD enchantments TEXT NOT NULL AFTER flags,
ADD randomPropertyId SMALLINT(5) NOT NULL DEFAULT ‘0’ AFTER enchantments,
ADD durability SMALLINT(5) UNSIGNED NOT NULL DEFAULT ‘0’ AFTER randomPropertyId,
ADD playedTime INT(10) UNSIGNED NOT NULL DEFAULT ‘0’ AFTER durability;
– Temporarily change delimiter to prevent SQL syntax errors
DELIMITER ||
– Function to convert ints from unsigned to signed
DROP FUNCTION IF EXISTS uint32toint32||
CREATE FUNCTION uint32toint32(input INT(10) UNSIGNED) RETURNS INT(11) SIGNED DETERMINISTIC
BEGIN
RETURN input;
END||
– Restore original delimiter
DELIMITER ;
– Move data to new fields
UPDATE item_instance SET
itemEntry = SUBSTRING(data,
LENGTH(SUBSTRING_INDEX(data,’ ',3))+2,
LENGTH(SUBSTRING_INDEX(data,’ ‘,3+1))-LENGTH(SUBSTRING_INDEX(DATA,’ ',3))-1),
creatorGuid = SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',10))+2,
length(SUBSTRING_INDEX(data,’ ‘,10+1))-length(SUBSTRING_INDEX(data,’ ',10))-1),
giftCreatorGuid = SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',12))+2,
length(SUBSTRING_INDEX(data,’ ‘,12+1))-length(SUBSTRING_INDEX(data,’ ',12))-1),
count = SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',14))+2,
length(SUBSTRING_INDEX(data,’ ‘,14+1))-length(SUBSTRING_INDEX(data,’ ',14))-1),
duration = SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',15))+2,
length(SUBSTRING_INDEX(data,’ ‘,15+1))-length(SUBSTRING_INDEX(data,’ ',15))-1),
charges = CONCAT_WS(’ ',
uint32toint32(SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',16))+2,
length(SUBSTRING_INDEX(data,’ ‘,16+1))-length(SUBSTRING_INDEX(data,’ ',16))-1)),
uint32toint32(SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',17))+2,
length(SUBSTRING_INDEX(data,’ ‘,17+1))-length(SUBSTRING_INDEX(data,’ ',17))-1)),
uint32toint32(SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',18))+2,
length(SUBSTRING_INDEX(data,’ ‘,18+1))-length(SUBSTRING_INDEX(data,’ ',18))-1)),
uint32toint32(SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',19))+2,
length(SUBSTRING_INDEX(data,’ ‘,19+1))-length(SUBSTRING_INDEX(data,’ ',19))-1)),
uint32toint32(SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',20))+2,
length(SUBSTRING_INDEX(data,’ ‘,20+1))-length(SUBSTRING_INDEX(data,’ ',20))-1))),
flags = SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',21))+2,
length(SUBSTRING_INDEX(data,’ ‘,21+1))-length(SUBSTRING_INDEX(data,’ ',21))-1),
enchantments = SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',22))+2,
length(SUBSTRING_INDEX(data,’ ‘,57+1))-length(SUBSTRING_INDEX(data,’ ',22))-1),
randomPropertyId = uint32toint32(SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',59))+2,
length(SUBSTRING_INDEX(data,’ ‘,59+1))-length(SUBSTRING_INDEX(data,’ ',59))-1)),
durability = SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',60))+2,
length(SUBSTRING_INDEX(data,’ ‘,60+1))-length(SUBSTRING_INDEX(data,’ ',60))-1),
playedTime = SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',62))+2,
length(SUBSTRING_INDEX(data,’ ‘,62+1))-length(SUBSTRING_INDEX(data,’ ',62))-1);
– Drop function
DROP FUNCTION IF EXISTS uint32toint32;
– Fix heroic item flag
UPDATE item_instance SET flags=flags&~0x8 WHERE
SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',3))+2,
length(SUBSTRING_INDEX(data,’ ‘,3+1))-length(SUBSTRING_INDEX(data,’ ',3))-1)
NOT IN (5043,5044,17302,17305,17308,21831);
– Drop old field
ALTER TABLE item_instance DROP data;
/* Execute 09160_characters_item_instance.sql */
SET @allowedFlags := 0x00000001 | 0x00000008 | 0x00000200 | 0x00001000 | 0x00008000 | 0x00010000;
UPDATE item_instance SET flags = (flags & @allowedFlags);[/CODE]
WARNING! BACKUP EVERYTHING BEFORE APPLY!
thanks
convert characters mangos - > trinity
mangos v 11716
trinity v 11.05.06
USE characters_trinity;
INSERT INTO account_data SELECT * FROM characters_mangos.account_data;
INSERT INTO arena_team SELECT * FROM characters_mangos.arena_team;
INSERT INTO arena_team (arenateamid,NAME,captainguid,TYPE,BackgroundColor,EmblemStyle,EmblemColor,BorderStyle,BorderColor) SELECT arenaTeamId,NAME,captainGuid,TYPE,backgroundColor,emblemStyle,emblemColor,borderStyle,borderColor FROM characters_mangos.arena_team;
UPDATE arena_team a SET weekGames = (SELECT games_week FROM characters_mangos.arena_team_stats WHERE arenateamid = a.arenaTeamId);
UPDATE arena_team a SET weekWins = (SELECT wins_week FROM characters_mangos.arena_team_stats WHERE arenateamid = a.arenaTeamId);
UPDATE arena_team a SET seasonGames = (SELECT games_season FROM characters_mangos.arena_team_stats WHERE arenateamid = a.arenaTeamId);
UPDATE arena_team a SET seasonWins = (SELECT wins_season FROM characters_mangos.arena_team_stats WHERE arenateamid = a.arenaTeamId);
UPDATE arena_team a SET rank = (SELECT rank FROM characters_mangos.arena_team_stats WHERE arenateamid = a.arenaTeamId);
INSERT INTO arena_team_member (arenaTeamId,guid,personalRating,weekGames,weekWins,seasonGames,seasonWins)SELECT arenateamid,guid,personal_rating,played_week,wons_week,played_season,wons_season FROM characters_mangos.arena_team_member;
INSERT INTO auctionhouse SELECT id,houseid,itemguid,itemowner,buyoutprice,time,buyguid,lastbid,startbid,deposit FROM characters_mangos.auction;
UPDATE auctionhouse SET auctioneerguid = 23442;
INSERT INTO character_account_data SELECT * FROM characters_mangos.character_account_data;
INSERT INTO character_achievement SELECT * FROM characters_mangos.character_achievement;
INSERT INTO character_achievement_progress SELECT * FROM characters_mangos.character_achievement_progress;
INSERT INTO character_action SELECT * FROM characters_mangos.character_action;
INSERT INTO character_battleground_data SELECT * FROM characters_mangos.character_battleground_data;
INSERT INTO character_battleground_random SELECT * FROM characters_mangos.character_battleground_random;
INSERT INTO character_declinedname SELECT * FROM characters_mangos.character_declinedname;
INSERT INTO character_equipmentsets (guid,setguid,setindex,NAME,iconname,item0,item1,item2,item3,item4,item5,item6,item7,item8,item9,item10,item11,item12,item13,item14,item15,item16,item17,item18) SELECT guid,setguid,setindex,NAME,iconname,item0,item1,item2,item3,item4,item5,item6,item7,item8,item9,item10,item11,item12,item13,item14,item15,item16,item17,item18 FROM characters_mangos.character_equipmentsets;
INSERT INTO character_gifts SELECT * FROM characters_mangos.character_gifts;
INSERT INTO character_homebind SELECT * FROM characters_mangos.character_homebind;
INSERT INTO character_instance (guid,instance,permanent) SELECT guid,instance,permanent FROM characters_mangos.character_instance;
DELETE FROM character_inventory;
--удалить индексы кроме item, Guid
INSERT INTO character_inventory (guid,bag,slot,item) SELECT guid,bag,slot,item FROM characters_mangos.character_inventory;
INSERT INTO character_pet SELECT * FROM characters_mangos.character_pet;
INSERT INTO character_queststatus SELECT guid,quest,status,explored,timer,mobcount1,mobcount2,mobcount3,mobcount4,itemcount1,itemcount2,itemcount3,itemcount4 FROM characters_mangos.character_queststatus;
INSERT INTO character_queststatus_daily (guid,quest) SELECT * FROM characters_mangos.character_queststatus_daily;
INSERT INTO character_queststatus_weekly SELECT * FROM characters_mangos.character_queststatus_weekly;
INSERT INTO character_reputation SELECT * FROM characters_mangos.character_reputation;
INSERT INTO character_skills SELECT * FROM characters_mangos.character_skills;
INSERT INTO character_social SELECT * FROM characters_mangos.character_social;
INSERT INTO character_spell SELECT * FROM characters_mangos.character_spell;
INSERT INTO character_stats(guid,maxhealth,maxpower1,maxpower2,maxpower3,maxpower4,maxpower5,maxpower6,maxpower7,strength,agility,stamina,intellect,spirit,armor,resHoly,resFire,resNature,resFrost,resShadow,resArcane,blockPct,dodgePct,parryPct,critPct,rangedCritPct,spellCritPct,attackPower,rangedAttackPower,spellPower) SELECT guid,maxhealth,maxpower1,maxpower2,maxpower3,maxpower4,maxpower5,maxpower6,maxpower7,strength,agility,stamina,intellect,spirit,armor,resHoly,resFire,resNature,resFrost,resShadow,resArcane,blockPct,dodgePct,parryPct,critPct,rangedCritPct,spellCritPct,attackPower,rangedAttackPower,spellPower FROM characters_mangos.character_stats;
ALTER TABLE `guild` MODIFY COLUMN `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' AFTER `guildid`;
ALTER TABLE `guild` MODIFY COLUMN `motd` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' AFTER `info`;
INSERT INTO guild SELECT * FROM characters_mangos.guild;
INSERT INTO guild_bank_eventlog SELECT * FROM characters_mangos.guild_bank_eventlog;
INSERT INTO guild_bank_item SELECT guildid,TabId,SlotId,item_guid FROM characters_mangos.guild_bank_item;
INSERT INTO guild_bank_right SELECT * FROM characters_mangos.guild_bank_right;
INSERT INTO guild_bank_tab SELECT * FROM characters_mangos.guild_bank_tab;
INSERT INTO guild_eventlog SELECT * FROM characters_mangos.guild_eventlog;
INSERT INTO guild_member SELECT * FROM characters_mangos.guild_member;
INSERT INTO guild_rank SELECT * FROM characters_mangos.guild_rank;
INSERT INTO mail SELECT * FROM characters_mangos.mail;
INSERT INTO mail_items SELECT mail_id,item_guid,receiver FROM characters_mangos.mail_items;
INSERT INTO characters_trinity.pet_spell (guid,spell,active) SELECT DISTINCT * FROM (SELECT DISTINCT guid,spell,active FROM characters_mangos.pet_spell ) AS q1;;
INSERT INTO petition SELECT * FROM characters_mangos.petition;
INSERT INTO petition_sign SELECT * FROM characters_mangos.petition_sign;
UPDATE worldstates set value=(SELECT NextArenaPointDistributionTime from characters_mangos.saved_variables) where entry=20001;
UPDATE worldstates set value=(SELECT NextWeeklyQuestResetTime from characters_mangos.saved_variables) where entry=20002;
UPDATE worldstates set value=(SELECT NextRandomBGResetTime from characters_mangos.saved_variables) where entry=20003;
INSERT INTO characters (guid,account,name,race,class,gender,level,xp,money,playerBytes,playerBytes2,playerFlags,position_x,position_y,position_z,map,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,zone,death_expire_time,taxi_path,arenaPoints,totalHonorPoints,todayHonorPoints,yesterdayHonorPoints,totalKills,todayKills,yesterdayKills,chosenTitle,knownCurrencies,watchedFaction,drunk,health,power1,power2,power3,power4,power5,power6,power7,specCount,activeSpec,exploredZones,equipmentCache,ammoId,knownTitles,actionBars) SELECT guid,account,name,race,class,gender,level,xp,money,playerBytes,playerBytes2,playerFlags,position_x,position_y,position_z,map,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,zone,death_expire_time,taxi_path,arenaPoints,totalHonorPoints,todayHonorPoints,yesterdayHonorPoints,totalKills,todayKills,yesterdayKills,chosenTitle,knownCurrencies,watchedFaction,drunk,health,power1,power2,power3,power4,power5,power6,power7,specCount,activeSpec,exploredZones,equipmentCache,ammoId,knownTitles,actionBars FROM characters_mangos.characters;
USE characters_trinity;
INSERT INTO character_glyphs (guid,spec) SELECT DISTINCT guid,spec FROM characters_mangos.character_glyphs;
UPDATE character_glyphs a SET glyph1 = (SELECT glyph FROM characters_mangos.character_glyphs WHERE guid = a.guid AND spec = a.spec AND slot = '0');
UPDATE character_glyphs a SET glyph2 = (SELECT glyph FROM characters_mangos.character_glyphs WHERE guid = a.guid AND spec = a.spec AND slot = '1');
UPDATE character_glyphs a SET glyph3 = (SELECT glyph FROM characters_mangos.character_glyphs WHERE guid = a.guid AND spec = a.spec AND slot = '2');
UPDATE character_glyphs a SET glyph4 = (SELECT glyph FROM characters_mangos.character_glyphs WHERE guid = a.guid AND spec = a.spec AND slot = '3');
UPDATE character_glyphs a SET glyph5 = (SELECT glyph FROM characters_mangos.character_glyphs WHERE guid = a.guid AND spec = a.spec AND slot = '4');
UPDATE character_glyphs a SET glyph6 = (SELECT glyph FROM characters_mangos.character_glyphs WHERE guid = a.guid AND spec = a.spec AND slot = '5');
USE characters_trinity;
/* DROP Trinity table and use Mangos one */
DROP TABLE IF EXISTS `item_instance`;
CREATE TABLE `item_instance` (
`guid` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`owner_guid` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`data` LONGTEXT,
`text` TEXT,
PRIMARY KEY (`guid`),
KEY `idx_owner_guid` (`owner_guid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Item System';
/* Copy Mangos data */
INSERT INTO item_instance SELECT * FROM characters_mangos.item_instance;
/* Execute 07893_characters_item_instance.sql */
UPDATE item_instance SET DATA = REPLACE(DATA,' ',' ');
UPDATE item_instance SET DATA = CONCAT(TRIM(DATA),' ');
UPDATE `item_instance` SET `data` = CONCAT(
SUBSTRING_INDEX(`data`, ' ', 59 + 1), ' ',
SUBSTRING_INDEX(`data`, ' ', -3 -1), '0 ')
WHERE LENGTH(SUBSTRING_INDEX(DATA, ' ', 64)) < LENGTH(DATA) AND LENGTH(SUBSTRING_INDEX(DATA, ' ', 64+1)) >= LENGTH(DATA);
UPDATE item_instance SET DATA = REPLACE(DATA,' ',' ');
UPDATE item_instance SET DATA = CONCAT(TRIM(DATA),' ');
/* Don't Execute 07904_characters_item_instance.sql coz mangos already has this field */
/* Execute 09090_characters_item_instance.sql */
-- Add new fields
ALTER TABLE `item_instance`
ADD `itemEntry` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0' AFTER `guid`,
ADD `creatorGuid` INT(10) UNSIGNED NOT NULL DEFAULT '0' AFTER `owner_guid`,
ADD `giftCreatorGuid` INT(15) UNSIGNED NOT NULL DEFAULT '0' AFTER `creatorGuid`,
ADD `count` INT(10) UNSIGNED NOT NULL DEFAULT '1' AFTER `giftCreatorGuid`,
ADD `duration` INT(10) UNSIGNED NOT NULL DEFAULT '0' AFTER `count`,
ADD `charges` TINYTEXT AFTER `duration`,
ADD `flags` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0' AFTER `charges`,
ADD `enchantments` TEXT NOT NULL AFTER `flags`,
ADD `randomPropertyId` SMALLINT(5) NOT NULL DEFAULT '0' AFTER `enchantments`,
ADD `durability` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0' AFTER `randomPropertyId`,
ADD `playedTime` INT(10) UNSIGNED NOT NULL DEFAULT '0' AFTER `durability`;
-- Temporarily change delimiter to prevent SQL syntax errors
DELIMITER ||
-- Function to convert ints from unsigned to signed
DROP FUNCTION IF EXISTS `uint32toint32`||
CREATE FUNCTION `uint32toint32`(input INT(10) UNSIGNED) RETURNS INT(11) SIGNED DETERMINISTIC
BEGIN
RETURN input;
END||
-- Restore original delimiter
DELIMITER ;
-- Move data to new fields
UPDATE `item_instance` SET
`itemEntry` = SUBSTRING(`data`,
LENGTH(SUBSTRING_INDEX(`data`,' ',3))+2,
LENGTH(SUBSTRING_INDEX(`data`,' ',3+1))-LENGTH(SUBSTRING_INDEX(DATA,' ',3))-1),
`creatorGuid` = SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',10))+2,
length(SUBSTRING_INDEX(`data`,' ',10+1))-length(SUBSTRING_INDEX(data,' ',10))-1),
`giftCreatorGuid` = SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',12))+2,
length(SUBSTRING_INDEX(`data`,' ',12+1))-length(SUBSTRING_INDEX(data,' ',12))-1),
`count` = SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',14))+2,
length(SUBSTRING_INDEX(`data`,' ',14+1))-length(SUBSTRING_INDEX(data,' ',14))-1),
`duration` = SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',15))+2,
length(SUBSTRING_INDEX(`data`,' ',15+1))-length(SUBSTRING_INDEX(data,' ',15))-1),
`charges` = CONCAT_WS(' ',
uint32toint32(SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',16))+2,
length(SUBSTRING_INDEX(`data`,' ',16+1))-length(SUBSTRING_INDEX(data,' ',16))-1)),
uint32toint32(SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',17))+2,
length(SUBSTRING_INDEX(`data`,' ',17+1))-length(SUBSTRING_INDEX(data,' ',17))-1)),
uint32toint32(SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',18))+2,
length(SUBSTRING_INDEX(`data`,' ',18+1))-length(SUBSTRING_INDEX(data,' ',18))-1)),
uint32toint32(SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',19))+2,
length(SUBSTRING_INDEX(`data`,' ',19+1))-length(SUBSTRING_INDEX(data,' ',19))-1)),
uint32toint32(SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',20))+2,
length(SUBSTRING_INDEX(`data`,' ',20+1))-length(SUBSTRING_INDEX(data,' ',20))-1))),
`flags` = SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',21))+2,
length(SUBSTRING_INDEX(`data`,' ',21+1))-length(SUBSTRING_INDEX(data,' ',21))-1),
`enchantments` = SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',22))+2,
length(SUBSTRING_INDEX(`data`,' ',57+1))-length(SUBSTRING_INDEX(data,' ',22))-1),
`randomPropertyId` = uint32toint32(SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',59))+2,
length(SUBSTRING_INDEX(`data`,' ',59+1))-length(SUBSTRING_INDEX(data,' ',59))-1)),
`durability` = SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',60))+2,
length(SUBSTRING_INDEX(`data`,' ',60+1))-length(SUBSTRING_INDEX(data,' ',60))-1),
`playedTime` = SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',62))+2,
length(SUBSTRING_INDEX(`data`,' ',62+1))-length(SUBSTRING_INDEX(data,' ',62))-1);
-- Drop function
DROP FUNCTION IF EXISTS `uint32toint32`;
-- Fix heroic item flag
UPDATE `item_instance` SET `flags`=`flags`&~0x8 WHERE
SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',3))+2,
length(SUBSTRING_INDEX(`data`,' ',3+1))-length(SUBSTRING_INDEX(data,' ',3))-1)
NOT IN (5043,5044,17302,17305,17308,21831);
-- Drop old field
ALTER TABLE `item_instance` DROP `data`;
/* Execute 09160_characters_item_instance.sql */
SET @allowedFlags := 0x00000001 | 0x00000008 | 0x00000200 | 0x00001000 | 0x00008000 | 0x00010000;
UPDATE `item_instance` SET `flags` = (`flags` & @allowedFlags);
-fix kwests
insert into character_queststatus_rewarded (guid,quest) select guid,quest from character_queststatus where status = 1;
DELETE FROM character_queststatus WHERE STATUS=1;
Anyone have update please ?
At the day 1 March 2012, for the characters data the updated work from killradio is this one:
USE trin_characters;
INSERT INTO account_data SELECT * FROM characters.account_data;
INSERT INTO arena_team (arenateamid, name, captainguid, type, backgroundcolor, emblemstyle, emblemcolor, borderstyle, bordercolor) SELECT * FROM characters.arena_team;
UPDATE arena_team as A, (SELECT * FROM characters.arena_team_stats) AS B SET A.rating=B.rating, A.weekGames=B.games_week, A.weekWins=B.wins_week, A.seasonGames=B.games_season, A.seasonWins=B.wins_season, A.rank=B.rank WHERE A.arenateamid=B.arenateamid;
INSERT INTO arena_team_member SELECT arenateamid,guid,played_week,wons_week,played_season,wons_season,personal_rating FROM characters.arena_team_member;
INSERT INTO auctionhouse SELECT id,houseid,itemguid,itemowner,buyoutprice,time,buyguid,lastbid,startbid,deposit FROM characters.auction;
UPDATE auctionhouse SET auctioneerguid = 23442;
INSERT INTO character_account_data SELECT * FROM characters.character_account_data;
INSERT INTO character_achievement SELECT * FROM characters.character_achievement;
INSERT INTO character_achievement_progress SELECT * FROM characters.character_achievement_progress;
INSERT INTO character_action SELECT * FROM characters.character_action;
INSERT INTO character_battleground_data SELECT * FROM characters.character_battleground_data;
INSERT INTO character_battleground_random SELECT * FROM characters.character_battleground_random;
INSERT INTO character_declinedname SELECT * FROM characters.character_declinedname;
INSERT INTO character_equipmentsets SELECT guid, setguid, setindex, name, iconname, item0, item1, item2, item3, item4, item5, item6, item7, item8, item9, item10, item11, item12, item13, item14, item15, item16, item17, item18 FROM characters.character_equipmentsets;
INSERT INTO character_gifts SELECT * FROM characters.character_gifts;
INSERT INTO character_homebind SELECT * FROM characters.character_homebind;
INSERT INTO character_instance SELECT guid, instance, permanent FROM characters.character_instance;
INSERT INTO character_inventory SELECT guid,bag,slot,item FROM characters.character_inventory;
INSERT INTO character_pet SELECT * FROM characters.character_pet;
INSERT INTO character_queststatus SELECT guid,quest,status,explored,timer,mobcount1,mobcount2,mobcount3,mobcount4,itemcount1,itemcount2,itemcount3,itemcount4,1 FROM characters.character_queststatus;
INSERT INTO character_queststatus_daily (guid,quest) SELECT * FROM characters.character_queststatus_daily;
INSERT INTO character_queststatus_weekly SELECT * FROM characters.character_queststatus_weekly;
INSERT INTO character_reputation SELECT * FROM characters.character_reputation;
INSERT INTO character_skills SELECT * FROM characters.character_skills;
INSERT INTO character_social SELECT * FROM characters.character_social;
INSERT INTO character_spell SELECT * FROM characters.character_spell;
INSERT INTO guild SELECT * FROM characters.guild;
INSERT INTO guild_bank_eventlog SELECT * FROM characters.guild_bank_eventlog;
INSERT INTO guild_bank_item SELECT guildid,TabId,SlotId,item_guid FROM characters.guild_bank_item;
INSERT INTO guild_bank_right SELECT * FROM characters.guild_bank_right;
INSERT INTO guild_bank_tab SELECT * FROM characters.guild_bank_tab;
INSERT INTO guild_eventlog SELECT * FROM characters.guild_eventlog;
INSERT INTO guild_member SELECT * FROM characters.guild_member;
INSERT INTO guild_rank SELECT * FROM characters.guild_rank;
INSERT INTO mail SELECT * FROM characters.mail;
INSERT INTO mail_items SELECT mail_id,item_guid,receiver FROM characters.mail_items;
INSERT INTO pet_spell SELECT guid, spell, active FROM characters.pet_spell;
INSERT INTO petition SELECT * FROM characters.petition;
INSERT INTO petition_sign SELECT * FROM characters.petition_sign;
INSERT INTO reserved_name SELECT * FROM mangos.reserved_name;
UPDATE worldstates set value=(SELECT NextArenaPointDistributionTime from characters.saved_variables) where entry=20001;
UPDATE worldstates set value=(SELECT NextWeeklyQuestResetTime from characters.saved_variables) where entry=20002;
UPDATE worldstates set value=(SELECT NextRandomBGResetTime from characters.saved_variables) where entry=20003;
INSERT INTO characters (guid,account,name,race,class,gender,level,xp,money,playerBytes,playerBytes2,playerFlags,position_x,position_y,position_z,map,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,zone,death_expire_time,taxi_path,arenaPoints,totalHonorPoints,todayHonorPoints,yesterdayHonorPoints,totalKills,todayKills,yesterdayKills,chosenTitle,knownCurrencies,watchedFaction,drunk,health,power1,power2,power3,power4,power5,power6,power7,specCount,activeSpec,exploredZones,equipmentCache,ammoId,knownTitles,actionBars) SELECT guid,account,name,race,class,gender,level,xp,money,playerBytes,playerBytes2,playerFlags,position_x,position_y,position_z,map,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,zone,death_expire_time,taxi_path,arenaPoints,totalHonorPoints,todayHonorPoints,yesterdayHonorPoints,totalKills,todayKills,yesterdayKills,chosenTitle,knownCurrencies,watchedFaction,drunk,health,power1,power2,power3,power4,power5,power6,power7,specCount,activeSpec,exploredZones,equipmentCache,ammoId,knownTitles,actionBars FROM characters.characters;
Then you can use the rework from andalit copy paste:
USE characters_trinity;
INSERT INTO character_glyphs (guid,spec) SELECT DISTINCT guid,spec FROM characters_mangos.character_glyphs;
UPDATE character_glyphs a SET glyph1 = (SELECT glyph FROM characters_mangos.character_glyphs WHERE guid = a.guid AND spec = a.spec AND slot = '0');
UPDATE character_glyphs a SET glyph2 = (SELECT glyph FROM characters_mangos.character_glyphs WHERE guid = a.guid AND spec = a.spec AND slot = '1');
UPDATE character_glyphs a SET glyph3 = (SELECT glyph FROM characters_mangos.character_glyphs WHERE guid = a.guid AND spec = a.spec AND slot = '2');
UPDATE character_glyphs a SET glyph4 = (SELECT glyph FROM characters_mangos.character_glyphs WHERE guid = a.guid AND spec = a.spec AND slot = '3');
UPDATE character_glyphs a SET glyph5 = (SELECT glyph FROM characters_mangos.character_glyphs WHERE guid = a.guid AND spec = a.spec AND slot = '4');
UPDATE character_glyphs a SET glyph6 = (SELECT glyph FROM characters_mangos.character_glyphs WHERE guid = a.guid AND spec = a.spec AND slot = '5');
USE characters_trinity;
/* DROP Trinity table and use mangos one */
DROP TABLE IF EXISTS `item_instance`;
CREATE TABLE `item_instance` (
`guid` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`owner_guid` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`data` LONGTEXT,
`text` TEXT,
PRIMARY KEY (`guid`),
KEY `idx_owner_guid` (`owner_guid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Item System';
/* Copy mangos data */
INSERT INTO item_instance SELECT * FROM characters_mangos.item_instance;
/* Execute 07893_characters_item_instance.sql */
UPDATE item_instance SET DATA = REPLACE(DATA,' ',' ');
UPDATE item_instance SET DATA = CONCAT(TRIM(DATA),' ');
UPDATE `item_instance` SET `data` = CONCAT(
SUBSTRING_INDEX(`data`, ' ', 59 + 1), ' ',
SUBSTRING_INDEX(`data`, ' ', -3 -1), '0 ')
WHERE LENGTH(SUBSTRING_INDEX(DATA, ' ', 64)) < LENGTH(DATA) AND LENGTH(SUBSTRING_INDEX(DATA, ' ', 64+1)) >= LENGTH(DATA);
UPDATE item_instance SET DATA = REPLACE(DATA,' ',' ');
UPDATE item_instance SET DATA = CONCAT(TRIM(DATA),' ');
/* Don't Execute 07904_characters_item_instance.sql coz mangos already has this field */
/* Execute 09090_characters_item_instance.sql */
-- Add new fields
ALTER TABLE `item_instance`
ADD `itemEntry` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0' AFTER `guid`,
ADD `creatorGuid` INT(10) UNSIGNED NOT NULL DEFAULT '0' AFTER `owner_guid`,
ADD `giftCreatorGuid` INT(15) UNSIGNED NOT NULL DEFAULT '0' AFTER `creatorGuid`,
ADD `count` INT(10) UNSIGNED NOT NULL DEFAULT '1' AFTER `giftCreatorGuid`,
ADD `duration` INT(10) UNSIGNED NOT NULL DEFAULT '0' AFTER `count`,
ADD `charges` TINYTEXT AFTER `duration`,
ADD `flags` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0' AFTER `charges`,
ADD `enchantments` TEXT NOT NULL AFTER `flags`,
ADD `randomPropertyId` SMALLINT(5) NOT NULL DEFAULT '0' AFTER `enchantments`,
ADD `durability` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0' AFTER `randomPropertyId`,
ADD `playedTime` INT(10) UNSIGNED NOT NULL DEFAULT '0' AFTER `durability`;
-- Temporarily change delimiter to prevent SQL syntax errors
DELIMITER ||
-- Function to convert ints from unsigned to signed
DROP FUNCTION IF EXISTS `uint32toint32`||
CREATE FUNCTION `uint32toint32`(input INT(10) UNSIGNED) RETURNS INT(11) SIGNED DETERMINISTIC
BEGIN
RETURN input;
END||
-- Restore original delimiter
DELIMITER ;
-- Move data to new fields
UPDATE `item_instance` SET
`itemEntry` = SUBSTRING(`data`,
LENGTH(SUBSTRING_INDEX(`data`,' ',3))+2,
LENGTH(SUBSTRING_INDEX(`data`,' ',3+1))-LENGTH(SUBSTRING_INDEX(DATA,' ',3))-1),
`creatorGuid` = SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',10))+2,
length(SUBSTRING_INDEX(`data`,' ',10+1))-length(SUBSTRING_INDEX(data,' ',10))-1),
`giftCreatorGuid` = SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',12))+2,
length(SUBSTRING_INDEX(`data`,' ',12+1))-length(SUBSTRING_INDEX(data,' ',12))-1),
`count` = SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',14))+2,
length(SUBSTRING_INDEX(`data`,' ',14+1))-length(SUBSTRING_INDEX(data,' ',14))-1),
`duration` = SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',15))+2,
length(SUBSTRING_INDEX(`data`,' ',15+1))-length(SUBSTRING_INDEX(data,' ',15))-1),
`charges` = CONCAT_WS(' ',
uint32toint32(SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',16))+2,
length(SUBSTRING_INDEX(`data`,' ',16+1))-length(SUBSTRING_INDEX(data,' ',16))-1)),
uint32toint32(SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',17))+2,
length(SUBSTRING_INDEX(`data`,' ',17+1))-length(SUBSTRING_INDEX(data,' ',17))-1)),
uint32toint32(SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',18))+2,
length(SUBSTRING_INDEX(`data`,' ',18+1))-length(SUBSTRING_INDEX(data,' ',18))-1)),
uint32toint32(SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',19))+2,
length(SUBSTRING_INDEX(`data`,' ',19+1))-length(SUBSTRING_INDEX(data,' ',19))-1)),
uint32toint32(SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',20))+2,
length(SUBSTRING_INDEX(`data`,' ',20+1))-length(SUBSTRING_INDEX(data,' ',20))-1))),
`flags` = SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',21))+2,
length(SUBSTRING_INDEX(`data`,' ',21+1))-length(SUBSTRING_INDEX(data,' ',21))-1),
`enchantments` = SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',22))+2,
length(SUBSTRING_INDEX(`data`,' ',57+1))-length(SUBSTRING_INDEX(data,' ',22))-1),
`randomPropertyId` = uint32toint32(SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',59))+2,
length(SUBSTRING_INDEX(`data`,' ',59+1))-length(SUBSTRING_INDEX(data,' ',59))-1)),
`durability` = SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',60))+2,
length(SUBSTRING_INDEX(`data`,' ',60+1))-length(SUBSTRING_INDEX(data,' ',60))-1),
`playedTime` = SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',62))+2,
length(SUBSTRING_INDEX(`data`,' ',62+1))-length(SUBSTRING_INDEX(data,' ',62))-1);
-- Drop function
DROP FUNCTION IF EXISTS `uint32toint32`;
-- Fix heroic item flag
UPDATE `item_instance` SET `flags`=`flags`&~0x8 WHERE
SUBSTRING(`data`,
length(SUBSTRING_INDEX(`data`,' ',3))+2,
length(SUBSTRING_INDEX(`data`,' ',3+1))-length(SUBSTRING_INDEX(data,' ',3))-1)
NOT IN (5043,5044,17302,17305,17308,21831);
-- Drop old field
ALTER TABLE `item_instance` DROP `data`;
/* Execute 09160_characters_item_instance.sql */
SET @allowedFlags := 0x00000001 | 0x00000008 | 0x00000200 | 0x00001000 | 0x00008000 | 0x00010000;
UPDATE `item_instance` SET `flags` = (`flags` & @allowedFlags);
--fix kwests
insert into character_queststatus_rewarded (guid,quest) select guid,quest from character_queststatus where status = 1;
DELETE FROM character_queststatus WHERE STATUS=1;
If you have duplicated primary key in character_inventory you can use this query on your mangos character_inventory database… DO A BKUP before it…
This is only needed if you have problems migrating data from character_inventory table
DELETE character_inventory AS A FROM `character_inventory` AS A INNER JOIN character_inventory AS B INNER JOIN character_inventory AS C
WHERE A.guid = B.guid
AND A.bag = B.bag
AND A.slot = B.slot
AND A.item_template <> B.item_template
AND A.bag=C.bag
AND A.slot=C.slot
AND A.guid=C.guid
AND A.item=C.item
This will fix some corrupted data in your character inventory table, buttt it will delete one aleatory item from the inventory with problems…
I have to use this in my server becouse my table does not have the correct primary key, but i dont know if this is only on my db…
Hello the character_inventory works not to converter to 2 März 2012
[SIZE=14px][FONT=arial]If[/FONT][/SIZE][SIZE=14px][FONT=arial] [/FONT][/SIZE][SIZE=14px][FONT=arial]I copy the[/FONT][/SIZE][SIZE=14px][FONT=arial] [/FONT][/SIZE][SIZE=14px][FONT=arial]tables[/FONT][/SIZE][SIZE=14px][FONT=arial],[/FONT][/SIZE][SIZE=14px][FONT=arial] [/FONT][/SIZE][SIZE=14px][FONT=arial]then[/FONT][/SIZE][SIZE=14px][FONT=arial] [/FONT][/SIZE][SIZE=14px][FONT=arial]it[/FONT][/SIZE][SIZE=14px][FONT=arial] [/FONT][/SIZE][SIZE=14px][FONT=arial]is[/FONT][/SIZE][SIZE=14px][FONT=arial] [/FONT][/SIZE][SIZE=14px][FONT=arial]always empty[/FONT][/SIZE][SIZE=14px][FONT=arial] [/FONT][/SIZE][SIZE=14px][FONT=arial]after reboot[/FONT][/SIZE]
[SIZE=14px][FONT=arial]How[/FONT][/SIZE][SIZE=14px][FONT=arial] [/FONT][/SIZE][SIZE=14px][FONT=arial]can I[/FONT][/SIZE][SIZE=14px][FONT=arial] [/FONT][/SIZE][SIZE=14px][FONT=arial]do it?[/FONT][/SIZE][SIZE=14px][FONT=arial] [/FONT][/SIZE][SIZE=14px][FONT=arial]Since[/FONT][/SIZE][SIZE=14px][FONT=arial] [/FONT][/SIZE][SIZE=14px][FONT=arial]no other[/FONT][/SIZE][SIZE=14px][FONT=arial] [/FONT][/SIZE][SIZE=14px][FONT=arial]chars[/FONT][/SIZE][SIZE=14px][FONT=arial] [/FONT][/SIZE][SIZE=14px][FONT=arial]have[/FONT][/SIZE][SIZE=14px][FONT=arial] [/FONT][/SIZE][SIZE=14px][FONT=arial]more[/FONT][/SIZE][SIZE=14px][FONT=arial] [/FONT][/SIZE][SIZE=14px][FONT=arial]items[/FONT][/SIZE][SIZE=14px][FONT=arial] [/FONT][/SIZE][SIZE=14px][FONT=arial]and[/FONT][/SIZE][SIZE=14px][FONT=arial] [/FONT][/SIZE][SIZE=14px][FONT=arial]thus[/FONT][/SIZE][SIZE=14px][FONT=arial] [/FONT][/SIZE][SIZE=14px][FONT=arial]are[/FONT][/SIZE][SIZE=14px][FONT=arial] [/FONT][/SIZE][SIZE=14px][FONT=arial]useless[/FONT][/SIZE]
I mean i can not take the character_inventory because the error of dublications
Even the tip from 02 March 2012 Kill or just does not work, because he always brings aSyntex error
Sorry the Edit Funktion not work
I use the code from the post March 12, 2012 to prevent the duplicates
He then says always all are OK, but when I want it einpatchen then comes the error due toa duplicate
How hard is it to realize that all you have to do is convert it, then apply updates that were added after March 1st (or whatever the latest converter converts to)?
Of course, if the problem is changes on Mangos’ side, I guess you just have to wait for someone to update the converter.
Always keep in mind that the only people getting paid for any of this are the scummy private server admins that take money from their users, and most of them have no clue how to do anything like this.
I need a Update from this Code.
Thanks 4 Help
updated the queries that are needed to transfer from mangos 11896 (its not the latest mangos but i havent updated my server for ages) to trinity b32878f02f801feb0db0a60d528322740d16c01c from 4th October 2012. Everything you might is written at the top. Queries are better than the previous ones so for example quests and some spells that are obtained via talents are handled correctly too (talents are reset, but spells obtained via talents are deleted too, unlike with previous queries). Things that are wiped in the process are written in the header too.
[CODE]
– 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
– go through the queries to know whats happening and edit them accordingly, dont just run them blindly.
– wiped are player/pet auras, talents, instance IDs, corpses, groups, arena stats/teams, GM tickets
– 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”
– character_glyphs and item_instance convertion taken from trinity forums, made up to date + adding other tables done by me
– convert_mg_characters, convert_mg_auth - origin databases
– convert_tc_characters, convert_tc_auth - destination databases
– USE AT OWN RISK!, make sure you have backup
###################
AUTH DATABASE
###################
USE convert_tc_auth;
– format: table name: columns left to default
– account: email, online, os, recruiter
DELETE FROM account;
INSERT INTO account (id, username, sha_pass_hash, sessionkey, v, s, joindate, last_ip, failed_logins, locked, last_login, expansion, mutetime, locale)
SELECT id, username, sha_pass_hash, sessionkey, v, s, joindate, last_ip, failed_logins, locked, last_login, expansion, mutetime, locale FROM convert_mg_auth.account;
– account_access: RealmID
DELETE FROM account_access;
INSERT INTO account_access (id, gmlevel)
SELECT id, gmlevel FROM convert_mg_auth.account;
– account_banned
DELETE FROM account_banned;
INSERT INTO account_banned SELECT * FROM convert_mg_auth.account_banned;
– ip_banned
DELETE FROM ip_banned;
INSERT INTO ip_banned SELECT * FROM convert_mg_auth.ip_banned;
– logs
DELETE FROM logs;
– realmcharacters
DELETE FROM realmcharacters;
INSERT INTO realmcharacters SELECT * FROM convert_mg_auth.realmcharacters;
– realmlist
DELETE FROM realmlist;
INSERT INTO realmlist SELECT * FROM convert_mg_auth.realmlist;
#########################
CHARACTERS DATABASE
#########################
USE convert_tc_characters;
– account_data
DELETE FROM account_data;
INSERT INTO account_data SELECT * FROM convert_mg_characters.account_data;
– account_instance_times
DELETE FROM account_instance_times;
– account_tutorial
DELETE FROM account_tutorial;
INSERT INTO account_tutorial SELECT * FROM convert_mg_characters.character_tutorial;
– addons
DELETE FROM addons;
– arena_logs
DELETE FROM arena_logs;
– arena_team
DELETE FROM arena_team;
– arena_team_member
DELETE FROM arena_team_member;
– auctionhouse
– delete weird items and items put by AHbot
DELETE FROM convert_mg_characters.auction WHERE itemguid = 0 OR itemowner = 0;
DELETE FROM auctionhouse;
INSERT INTO auctionhouse (id, auctioneerguid, itemguid, itemowner, buyoutprice, time, buyguid, lastbid, startbid, deposit)
SELECT id, houseid, itemguid, itemowner, buyoutprice, time, buyguid, lastbid, startbid, deposit FROM convert_mg_characters.auction;
– bugreport
DELETE FROM bugreport;
– corpse
DELETE FROM corpse;
– creature_respawn
DELETE FROM creature_respawn;
– game_event_condition_save
DELETE FROM game_event_condition_save;
– game_event_save
DELETE FROM game_event_save;
– gameobject_respawn
DELETE FROM gameobject_respawn;
– gm_subsurveys
DELETE FROM gm_surveys;
– gm_surveys
DELETE FROM gm_surveys;
– gm_tickets
DELETE FROM gm_tickets;
– group_instance
DELETE FROM group_instance;
– group_member
DELETE FROM group_member;
– groups
DELETE FROM groups;
– guild
DELETE FROM guild;
INSERT INTO guild SELECT * FROM convert_mg_characters.guild;
– guild_bank_eventlog
DELETE FROM guild_bank_eventlog;
INSERT INTO guild_bank_eventlog SELECT * FROM convert_mg_characters.guild_bank_eventlog;
– guild_bank_item
DELETE FROM guild_bank_item;
INSERT INTO guild_bank_item SELECT guildid, TabId, SlotId, item_guid FROM convert_mg_characters.guild_bank_item;
– guild_bank_right
DELETE FROM guild_bank_right;
INSERT INTO guild_bank_right SELECT * FROM convert_mg_characters.guild_bank_right;
– guild_bank_tab
DELETE FROM guild_bank_tab;
INSERT INTO guild_bank_tab SELECT * FROM convert_mg_characters.guild_bank_tab;
– guild_eventlog
DELETE FROM guild_eventlog;
INSERT INTO guild_eventlog SELECT * FROM convert_mg_characters.guild_eventlog;
– guild_member
DELETE FROM guild_member;
INSERT INTO guild_member SELECT * FROM convert_mg_characters.guild_member;
– guild_rank
DELETE FROM guild_rank;
INSERT INTO guild_rank SELECT * FROM convert_mg_characters.guild_rank;
– channels
DELETE FROM channels;
– character_account_data
DELETE FROM character_account_data;
INSERT INTO character_account_data SELECT * FROM convert_mg_characters.character_account_data;
– character_action
DELETE FROM character_action;
INSERT INTO character_action SELECT * FROM convert_mg_characters.character_action;
– character_achievement
DELETE FROM character_achievement;
INSERT INTO character_achievement SELECT * FROM convert_mg_characters.character_achievement;
– character_achievement_progress
DELETE FROM character_achievement_progress;
INSERT INTO character_achievement_progress SELECT * FROM convert_mg_characters.character_achievement_progress;
– character_arena_stats
DELETE FROM character_arena_stats;
– character_aura
DELETE FROM character_aura;
– character_banned
DELETE FROM character_banned;
– character_battleground_data
DELETE FROM character_battleground_data;
INSERT INTO character_battleground_data SELECT * FROM convert_mg_characters.character_battleground_data;
– character_battleground_random
DELETE FROM character_battleground_random;
INSERT INTO character_battleground_random SELECT * FROM convert_mg_characters.character_battleground_random;
– character_declinedname
DELETE FROM character_declinedname;
INSERT INTO character_declinedname SELECT * FROM convert_mg_characters.character_declinedname;
– character_equipmentsets
DELETE FROM character_equipmentsets;
INSERT INTO character_equipmentsets SELECT * FROM convert_mg_characters.character_equipmentsets;
– character_gifts
DELETE FROM character_gifts;
INSERT INTO character_gifts SELECT * FROM convert_mg_characters.character_gifts;
– character_glyphs
DELETE FROM character_glyphs;
INSERT INTO character_glyphs (guid,spec) SELECT DISTINCT guid,spec FROM convert_mg_characters.character_glyphs;
UPDATE character_glyphs A SET glyph1 = (SELECT glyph FROM convert_mg_characters.character_glyphs WHERE guid = A.guid AND spec = A.spec AND slot = ‘0’);
UPDATE character_glyphs A SET glyph2 = (SELECT glyph FROM convert_mg_characters.character_glyphs WHERE guid = A.guid AND spec = A.spec AND slot = ‘1’);
UPDATE character_glyphs A SET glyph3 = (SELECT glyph FROM convert_mg_characters.character_glyphs WHERE guid = A.guid AND spec = A.spec AND slot = ‘2’);
UPDATE character_glyphs A SET glyph4 = (SELECT glyph FROM convert_mg_characters.character_glyphs WHERE guid = A.guid AND spec = A.spec AND slot = ‘3’);
UPDATE character_glyphs A SET glyph5 = (SELECT glyph FROM convert_mg_characters.character_glyphs WHERE guid = A.guid AND spec = A.spec AND slot = ‘4’);
UPDATE character_glyphs A SET glyph6 = (SELECT glyph FROM convert_mg_characters.character_glyphs WHERE guid = A.guid AND spec = A.spec AND slot = ‘5’);
– character_homebind
DELETE FROM character_homebind;
INSERT INTO character_homebind SELECT * FROM convert_mg_characters.character_homebind;
– character_instance
DELETE FROM character_instance;
– character_inventory
– in case of conflicts here check the queries at the bottom of this file
DELETE FROM character_inventory;
INSERT INTO character_inventory SELECT guid, bag, slot, item FROM convert_mg_characters.character_inventory;
– character_pet
– convert only tamed (hunter) pets, not summoned ones
DELETE FROM character_pet;
INSERT INTO character_pet SELECT * FROM convert_mg_characters.character_pet AS c WHERE c.PetType = 1;
– character_pet_declinedname
DELETE FROM character_pet_declinedname;
INSERT INTO character_pet_declinedname SELECT * FROM convert_mg_characters.character_pet_declinedname;
– character_queststatus
DELETE FROM character_queststatus;
INSERT INTO character_queststatus
SELECT guid, quest, status, explored, timer, mobcount1, mobcount2, mobcount3, mobcount4, itemcount1, itemcount2, itemcount3, itemcount4, 0 FROM convert_mg_characters.character_queststatus;
– character_queststatus_daily
DELETE FROM character_queststatus_daily;
INSERT INTO character_queststatus_daily (guid, quest) SELECT * FROM convert_mg_characters.character_queststatus_daily;
– character_queststatus_rewarded
DELETE FROM character_queststatus_rewarded;
INSERT INTO character_queststatus_rewarded SELECT c.guid, c.quest FROM convert_mg_characters.character_queststatus c WHERE c.status = 1 AND c.rewarded = 1;
– character_queststatus_seasonal
DELETE FROM character_queststatus_seasonal;
– character_queststatus_weekly
DELETE FROM character_queststatus_weekly;
– character_reputation
DELETE FROM character_reputation;
INSERT INTO character_reputation SELECT * FROM convert_mg_characters.character_reputation;
– character_skills
DELETE FROM character_skills;
INSERT INTO character_skills SELECT * FROM convert_mg_characters.character_skills;
– character_social
DELETE FROM character_social;
INSERT INTO character_social SELECT * FROM convert_mg_characters.character_social;
– character_spell
DELETE FROM character_spell;
INSERT INTO character_spell SELECT * FROM convert_mg_characters.character_spell;
– character_spell_cooldown
DELETE FROM character_spell_cooldown;
– character_stats
DELETE FROM character_stats;
– character_talent
DELETE FROM character_talent;
– characters
DELETE FROM characters;
INSERT 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,
zone, 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)
SELECT 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,
zone, 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;
– even tho people dont have talents, we have to reset them at login to clean up spells they shouldnt have
UPDATE characters SET at_login = at_login | 4 | 16;
– instance
DELETE FROM instance;
– instance_reset
DELETE FROM instance_reset;
– item_instance
– DROP Trinity table and use mangos one
DROP TABLE IF EXISTS item_instance;
CREATE TABLE item_instance (
guid int(11) unsigned NOT NULL default ‘0’,
owner_guid int(11) unsigned NOT NULL default ‘0’,
data longtext,
text longtext,
PRIMARY KEY (guid),
KEY idx_owner_guid (owner_guid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT=‘Item System’;
– Copy mangos data
INSERT INTO item_instance SELECT * FROM convert_mg_characters.item_instance;
UPDATE item_instance SET data = REPLACE(data,’ ‘,’ ');
UPDATE item_instance SET data = CONCAT(TRIM(data),’ ');
UPDATE item_instance SET data = CONCAT(
SUBSTRING_INDEX(data, ’ ', 59 + 1), ’ ',
SUBSTRING_INDEX(data, ’ ', -3 -1), '0 ')
WHERE length(SUBSTRING_INDEX(data, ’ ', 64)) < length(data) and length(SUBSTRING_INDEX(data, ’ ', 64+1)) >= length(data);
UPDATE item_instance SET data = REPLACE(data,’ ‘,’ ');
UPDATE item_instance SET data = CONCAT(TRIM(data),’ ');
– Add new fields
ALTER TABLE item_instance
ADD itemEntry mediumint(8) unsigned NOT NULL DEFAULT ‘0’ AFTER guid,
ADD creatorGuid int(10) unsigned NOT NULL default ‘0’ AFTER owner_guid,
ADD giftCreatorGuid int(10) unsigned NOT NULL default ‘0’ AFTER creatorGuid,
ADD count int(10) unsigned NOT NULL default ‘1’ AFTER giftCreatorGuid,
ADD duration int(10) unsigned NOT NULL AFTER count,
ADD charges text NOT NULL AFTER duration,
ADD flags int(10) unsigned NOT NULL default ‘0’ AFTER charges,
ADD enchantments text NOT NULL AFTER flags,
ADD randomPropertyId bigint(20) NOT NULL default ‘0’ AFTER enchantments,
ADD durability smallint(5) unsigned NOT NULL default ‘0’ AFTER randomPropertyId,
ADD playedTime int(10) unsigned NOT NULL default ‘0’ AFTER durability;
– Temporarily change delimiter to prevent SQL syntax errors
DELIMITER ||
– Function to convert ints from unsigned to signed
DROP FUNCTION IF EXISTS uint32toint32||
CREATE FUNCTION uint32toint32(input INT(10) UNSIGNED) RETURNS BIGINT(20) SIGNED DETERMINISTIC
BEGIN
RETURN input;
END||
– Restore original delimiter
DELIMITER ;
– Move data to new fields
UPDATE item_instance SET
itemEntry = SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',3))+2,
length(SUBSTRING_INDEX(data,’ ‘,3+1))-length(SUBSTRING_INDEX(data,’ ',3))-1),
creatorGuid = SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',10))+2,
length(SUBSTRING_INDEX(data,’ ‘,10+1))-length(SUBSTRING_INDEX(data,’ ',10))-1),
giftCreatorGuid = SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',12))+2,
length(SUBSTRING_INDEX(data,’ ‘,12+1))-length(SUBSTRING_INDEX(data,’ ',12))-1),
count = SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',14))+2,
length(SUBSTRING_INDEX(data,’ ‘,14+1))-length(SUBSTRING_INDEX(data,’ ',14))-1),
duration = SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',15))+2,
length(SUBSTRING_INDEX(data,’ ‘,15+1))-length(SUBSTRING_INDEX(data,’ ',15))-1),
charges = CONCAT_WS(’ ',
uint32toint32(SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',16))+2,
length(SUBSTRING_INDEX(data,’ ‘,16+1))-length(SUBSTRING_INDEX(data,’ ',16))-1)),
uint32toint32(SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',17))+2,
length(SUBSTRING_INDEX(data,’ ‘,17+1))-length(SUBSTRING_INDEX(data,’ ',17))-1)),
uint32toint32(SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',18))+2,
length(SUBSTRING_INDEX(data,’ ‘,18+1))-length(SUBSTRING_INDEX(data,’ ',18))-1)),
uint32toint32(SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',19))+2,
length(SUBSTRING_INDEX(data,’ ‘,19+1))-length(SUBSTRING_INDEX(data,’ ',19))-1)),
uint32toint32(SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',20))+2,
length(SUBSTRING_INDEX(data,’ ‘,20+1))-length(SUBSTRING_INDEX(data,’ ',20))-1))),
flags = SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',21))+2,
length(SUBSTRING_INDEX(data,’ ‘,21+1))-length(SUBSTRING_INDEX(data,’ ',21))-1),
enchantments = SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',22))+2,
length(SUBSTRING_INDEX(data,’ ‘,57+1))-length(SUBSTRING_INDEX(data,’ ',22))-1),
randomPropertyId = uint32toint32(SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',59))+2,
length(SUBSTRING_INDEX(data,’ ‘,59+1))-length(SUBSTRING_INDEX(data,’ ',59))-1)),
durability = SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',60))+2,
length(SUBSTRING_INDEX(data,’ ‘,60+1))-length(SUBSTRING_INDEX(data,’ ',60))-1),
playedTime = SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',62))+2,
length(SUBSTRING_INDEX(data,’ ‘,62+1))-length(SUBSTRING_INDEX(data,’ ',62))-1);
– Drop function
DROP FUNCTION IF EXISTS uint32toint32;
– Fix heroic item flag
UPDATE item_instance SET flags=flags&~0x8 WHERE
SUBSTRING(data,
length(SUBSTRING_INDEX(data,’ ',3))+2,
length(SUBSTRING_INDEX(data,’ ‘,3+1))-length(SUBSTRING_INDEX(data,’ ',3))-1)
NOT IN (5043,5044,17302,17305,17308,21831);
– Drop old field
ALTER TABLE item_instance DROP data;
– Execute 09160_characters_item_instance.sql
SET @allowedFlags := 0x00000001 | 0x00000008 | 0x00000200 | 0x00001000 | 0x00008000 | 0x00010000;
UPDATE item_instance SET flags = (flags & @allowedFlags);
– item_refund_instance
DELETE FROM item_refund_instance;
– item_soulbound_trade_data
DELETE FROM item_soulbound_trade_data;
INSERT INTO item_soulbound_trade_data SELECT * FROM convert_mg_characters.item_soulbound_trade_data;
– lag_reports
DELETE FROM lag_reports;
– lfg_data
DELETE FROM lfg_data;
DELETE FROM mail;
INSERT INTO mail SELECT * FROM convert_mg_characters.mail;
– mail_items
DELETE FROM mail_items;
INSERT INTO mail_items SELECT mail_id, item_guid, receiver FROM convert_mg_characters.mail_items;
– pet_aura
DELETE FROM pet_aura;
– pet_spell
DELETE FROM pet_spell;
INSERT INTO pet_spell SELECT guid, spell, active FROM convert_mg_characters.pet_spell;
– cleanup spells of deleted pets
– 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);
DELETE 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);
– pet_spell_cooldown
DELETE FROM pet_spell_cooldown;
– petition
DELETE FROM petition;
INSERT INTO petition SELECT * FROM convert_mg_characters.petition;
– petition_sign
DELETE FROM petition_sign;
INSERT INTO petition_sign SELECT * FROM convert_mg_characters.petition_sign;
– pool_quest_save
DELETE FROM pool_quest_save;
– reserved_name
DELETE FROM reserved_name;
– warden_action
DELETE FROM warden_action;
– worldstates
DELETE FROM worldstates;
– checks for case of problems with character_inventory, handle it manually
– my advise is leaving only the item with the highest guid at the conflicting slot
– dont forget deleting the item from item_instance too
/*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
WHERE A.guid = B.guid
AND A.bag = B.bag
AND A.slot = B.slot
AND A.item_template <> B.item_template
AND A.bag=C.bag
AND A.slot=C.slot
AND A.guid=C.guid
AND A.item=C.item
ORDER BY A.guid, A.bag, A.slot, A.item, A.item_template*/
/*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
WHERE A.guid = B.guid
AND A.bag = B.bag
AND A.slot = B.slot
AND A.bag = C.bag
AND A.slot = C.slot
AND A.guid = C.guid
AND A.item <> C.item
ORDER BY A.guid, A.bag, A.slot, A.item, A.item_template*/
– cleaning up old (completed and rewarded) quests from quest log
– 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;
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);[/CODE]