Mangos One Database trouble

[extralismizer]

Hey guys,

I was running a Mangos 2.4.3 server earlier on Windows.

At the moment I'm trying to get this server running on Ubuntu. I finished compiling (thanks to this forum!!!) and seems like the server works fine.

But the Problem: I had to use a new database and couldn't use my old one. Some tables show a different sturcture. The realmd-tables were mostly the same so I could easily convert by hand, but some of the characters-tables are totally different or even new.

My questions:

1) Is it possible, in general, to convert tables (and their content!) from old versions to new ones?

2) If yes, how

3) How can I find the version of my tables? At the NEW versionI still found in characters/characters_db-version anything like 1699, but of the OLD version I don't have any idea.

the characters/characters of the OLD version contains a column called "data" which has 50million (estimated) numbers inside, seperated by <space>. I remember this data contained the money. In hope it helps i post the structure of the OLD characters/characters.

-- ----------------------------
-- Table structure for `characters`
-- ----------------------------
DROP TABLE IF EXISTS `characters`;
CREATE TABLE `characters` (
 `guid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
 `account` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Account Identifier',
 `data` longtext,
 `name` varchar(12) NOT NULL DEFAULT '',
 `race` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `class` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `position_x` float NOT NULL DEFAULT '0',
 `position_y` float NOT NULL DEFAULT '0',
 `position_z` float NOT NULL DEFAULT '0',
 `map` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Map Identifier',
 `dungeon_difficulty` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `orientation` float NOT NULL DEFAULT '0',
 `taximask` longtext,
 `online` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `cinematic` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `totaltime` int(11) unsigned NOT NULL DEFAULT '0',
 `leveltime` int(11) unsigned NOT NULL DEFAULT '0',
 `logout_time` bigint(20) unsigned NOT NULL DEFAULT '0',
 `is_logout_resting` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `rest_bonus` float NOT NULL DEFAULT '0',
 `resettalents_cost` int(11) unsigned NOT NULL DEFAULT '0',
 `resettalents_time` bigint(20) unsigned NOT NULL DEFAULT '0',
 `trans_x` float NOT NULL DEFAULT '0',
 `trans_y` float NOT NULL DEFAULT '0',
 `trans_z` float NOT NULL DEFAULT '0',
 `trans_o` float NOT NULL DEFAULT '0',
 `transguid` bigint(20) unsigned NOT NULL DEFAULT '0',
 `gmstate` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `stable_slots` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `at_login` int(11) unsigned NOT NULL DEFAULT '0',
 `zone` int(11) unsigned NOT NULL DEFAULT '0',
 `death_expire_time` bigint(20) unsigned NOT NULL DEFAULT '0',
 `taxi_path` text,
 PRIMARY KEY (`guid`),
 KEY `idx_account` (`account`),
 KEY `idx_online` (`online`),
 KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Player System';

I hope anyone can help me. Thanks a lot!

extra

[antz]

yes its possible to upgrade the db, but its slow going.

Here how you would do it, take a look at the database you have, the table you need is in the characters db, called db_version (or something like it)

There is a revision number in this table, make a note of this number.

Look in the mangos source folder Mangos\\sql\\updates and find the matching number for characters

i.e. s0384_10664_01_characters_arena_team_stats.sql

You need to apply all the character sql changes up to the version you want to run

hope this help

[evilatwow]

It could be that very old MaNGOS versions didn't have this "db version" in place. At least I think I remember this was introduced. I'm not sure if the SVN versions already had it.

Anyway, not all is lost. If your old databases was indeed MaNGOS (and not some other project), Antz is correct and applying a bunch of updates will get you where you need to be. The only difficulty then is to figure out where to start updating.

Can you still start the server you were running on Windows? If so, just start it once, and check the versions it mentions at startup. That will tell you what MaNGOS revision you were using, and give you the starting point for updates (ie you then need all updates after that revision that apply to the characters DB, up to and including your new server revision)

[extralismizer]

in characters/character_db_version i found

required_s1699_xxxxx_01_characters_characters bit(1)

which (I guess) tells me that my NEW version is 1699 (??).

The OLD database doesn't show anything like that.

Can you still start the server you were running on Windows?

Unluckily no. My Windows PC may rest in peace... I did backups of the database, but if I don't find anything else this weekend - this is everything I have left.

Is there no way to get the db_version?

If it helps: I installed the old MaNGOS server in autumn 2010 and (I think) I was using MaNGOS-files and not others. (At least both database structures seem to be nearly equal.)

Thanks a lot

extra

[evilatwow]

in characters/character_db_version i found

required_s1699_xxxxx_01_characters_characters bit(1)

which (I guess) tells me that my NEW version is 1699 (??).

Well, kind of, as long as you remember this is a version of the database structure, not the core itself. So no worries, all is fine. But I understand your confusion

What this says is that the last change done to the character database structure was done in core revision s1699. If the current revision is s1749, and it still says that, then it simply means that at least all core versions between s1699 and s1749 would use the same structure. In other words: if you would have an earlier revision than s1699 of the core running, and one day you want to update to s1699 or a more recent version, then you would have to do the associated update to your characters database to make the core happy.

So this is fine, since it's still what the last revision uses.

Is there no way to get the db_version?

If it helps: I installed the old MaNGOS server in autumn 2010 and (I think) I was using MaNGOS-files and not others. (At least both database structures seem to be nearly equal.)

I just did some checking, and as I suspected, the old SVN versions didn't have the db_version stuff. I found the initial commit, apparently done just after the move to git, on Nov 07, 2008: https://github.com/mangos-one/server/commit/a1953fe67e414a3f8652c8caca3ee7b008b4cf7b.

This was well before your autumn 2010 install, so unless you installed a two year old version then (which I can't imagine) there should be a table 'character_db_version' in your character database really...

I'm starting to fear your backup only contains the 'character' table, so it's missing quite some data. The current character database contains like 60 tables, and most of those were around in 2010. Tables like 'mail', 'guild', 'character_queststatus', 'character_spell', etc. Can you verify that you do have more than just the character table?

[extralismizer]

Thanks evil@wow!

No, the backup files contain the full database. Means, that the characters.sql contains in total 44 tables (starting from "arena_team" and ending with "petition_sign") but it unluckily doesn't contain the characters_db_version table... (I checked 1000x in the hope of finding it finally), so seems like I was running my old MaNGOS with a database from the dark age.

Is it possible to convert the old SVN versions into one of the newer versions? I coulnd't find any update from the dark age...

extra

[evilatwow]

No, the backup files contain the full database. Means, that the characters.sql contains in total 44 tables (starting from "arena_team" and ending with "petition_sign").

That's good news, because it means you have all data then And no worries, even if that databases comes from the dark ages, you can convert it to a recent version. So, let's try to find out what you actually have then...

It was a good idea to mention that you have 44 tables, because I noticed that the very first revision in git has 46 tables in the characters database (and shorty after that, the character_db_version table was added as the 47th). Since you have 44, it does indeed seem to be the case that you have a database from the dark SVN days; one without the db_version stuff

I quickly checked the history here, and the good news is that there are only a handful of changes back from the end of the SVN days to the point where new tables were added (less updates to do is good hehe). One of the last changes to the character database structure adds two new tables: 'character_declinedname' and 'character_pet_declinedname'. If those are indeed 45 and 46, I expect that you don't have either of those two in your backup. Even better news is that only three more changes back, another two tables were added (say 43 and 44): 'group_instance' and 'instance_reset'. So I would expect that you do have those two.

If that's indeed the case, we can continue because we know you have one of three versions, and you have at least r6362 in that case. Now also check the following, in this order:

• Does the 'instance' table have a 'difficulty' column? If the answer is no, then stop checking because you have r6362. Otherwise continue with the next check.
  
• Does the 'character_ticket' table have a 'ticket_category' column? If the answer is yes, then you have r6366. If you don't have the column, then you have r6387.
  

I still have old updates around, so while you're checking, I'll try to put them in a few bigger SQLs so you don't have to run a gazillion updates

[extralismizer]

'group_instance' and 'instance_reset'. So I would expect that you do have those two.

1000 Points for evil@wow!!!

And like the other answers are yes and yes it seems like I was the owner of a beautiful r6366...

WOW (And I don't mean the game...) !!! That would be very kind from you.

Thanks!!!

extra

[evilatwow]

It seems like I was the owner of a beautiful r6366...

Good. If that's true, then the following updates should succeed and bring you back to modern times

Just copy/paste the code in a file first (or use your favorite MySQL tool).

I also found the old SVN updates back in case you want to verify things or do want separate updates: https://github.com/VladimirMangos/mangos-svn/tree/master/trunk/sql/updates. You're looking for X_characters_<description>.sql files where X > 6366; just apply them in order. But here is their content in one blob:

-- 6387
ALTER TABLE `character_ticket`
 DROP `ticket_category`;

-- 6412
DROP TABLE IF EXISTS `character_declinedname`;
CREATE TABLE `character_declinedname` (
 `guid` int(11) unsigned NOT NULL default '0' COMMENT 'Global Unique Identifier',
 `genitive` varchar(12) NOT NULL default '',
 `dative` varchar(12) NOT NULL default '',
 `accusative` varchar(12) NOT NULL default '',
 `instrumental` varchar(12) NOT NULL default '',
 `prepositional` varchar(12) NOT NULL default '',
 PRIMARY KEY  (`guid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
DROP TABLE IF EXISTS `character_pet_declinedname`;
CREATE TABLE `character_pet_declinedname` (
 `id` int(11) unsigned NOT NULL default '0',
 `owner` int(11) unsigned NOT NULL default '0',
 `genitive` varchar(12) NOT NULL default '',
 `dative` varchar(12) NOT NULL default '',
 `accusative` varchar(12) NOT NULL default '',
 `instrumental` varchar(12) NOT NULL default '',
 `prepositional` varchar(12) NOT NULL default '',
 PRIMARY KEY  (`id`),
 KEY owner_key (`owner`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;de]

-- 6596_characters_arena_team.sql
ALTER TABLE arena_team CHANGE EmblemStyle _BackgroundColor int(10) unsigned NOT NULL default '0';
ALTER TABLE arena_team CHANGE EmblemColor _EmblemStyle int(10) unsigned NOT NULL default '0';
ALTER TABLE arena_team CHANGE BorderStyle _EmblemColor int(10) unsigned NOT NULL default '0';
ALTER TABLE arena_team CHANGE BorderColor _BorderStyle int(10) unsigned NOT NULL default '0';
ALTER TABLE arena_team CHANGE BackgroundColor _BorderColor int(10) unsigned NOT NULL default '0';
ALTER TABLE arena_team CHANGE _BackgroundColor BackgroundColor int(10) unsigned NOT NULL default '0';
ALTER TABLE arena_team CHANGE _EmblemStyle EmblemStyle int(10) unsigned NOT NULL default '0';
ALTER TABLE arena_team CHANGE _EmblemColor EmblemColor int(10) unsigned NOT NULL default '0';
ALTER TABLE arena_team CHANGE _BorderStyle BorderStyle int(10) unsigned NOT NULL default '0';
ALTER TABLE arena_team CHANGE _BorderColor BorderColor int(10) unsigned NOT NULL default '0';

-- 6598_character_spell.sql
ALTER TABLE `character_spell`
   ADD COLUMN `disabled` tinyint(3) unsigned NOT NULL default '0';

-- 6642_characters_declinedname.sql
ALTER TABLE character_declinedname
 MODIFY COLUMN genitive varchar(15) NOT NULL default '',
 MODIFY COLUMN dative varchar(15) NOT NULL default '',
 MODIFY COLUMN accusative varchar(15) NOT NULL default '',
 MODIFY COLUMN instrumental varchar(15) NOT NULL default '',
 MODIFY COLUMN prepositional varchar(15) NOT NULL default '';

-- 6698
DROP TABLE IF EXISTS `character_tutorial`;
CREATE TABLE `character_tutorial` (
 `account` bigint(20) unsigned NOT NULL auto_increment COMMENT 'Account Identifier',
 `realmid` int(11) unsigned NOT NULL default '0' COMMENT 'Realm Identifier',
 `tut0` int(11) unsigned NOT NULL default '0',
 `tut1` int(11) unsigned NOT NULL default '0',
 `tut2` int(11) unsigned NOT NULL default '0',
 `tut3` int(11) unsigned NOT NULL default '0',
 `tut4` int(11) unsigned NOT NULL default '0',
 `tut5` int(11) unsigned NOT NULL default '0',
 `tut6` int(11) unsigned NOT NULL default '0',
 `tut7` int(11) unsigned NOT NULL default '0',
 PRIMARY KEY  (`account`,`realmid`),
 KEY acc_key (`account`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Player System';

-- 6740
ALTER TABLE characters
 CHANGE COLUMN gmstate gmstate int(11) unsigned NOT NULL default '0';

Those should get you out of the SVN era and bring you to the git era. You would then continue to apply updates, still in order, from the "sql/updates/0.12" folder (0.12 was the tag for 2.4.3 aka TBC). Unfortunately for you, those were deleted a few months ago (see https://github.com/mangos-one/server/commit/e391c9ecfb7dfe27ab64a116c8241a1f5a5042b9) and so you would have to go back to an old commit in git to actually get them again on disk. As if that's not enough trouble, the file name format was also changed for some time, to a date based format <date>_<sequence>_characters_<description>.sql. For instance, the first update after the above would be '2008_10_18_01_characters_characters.sql'. The good news there is that after like two or three updates, you finally get the character_db_version table which is a very nice safety to avoid that you run the wrong updates (because you get an error then).

Now, I've done all the required work myself a few years ago for my own server updates, and fortunately I still have my combined file, so I could as well post the next batch here.

One important remark though: I added a few statements of my own to the updates back in the days (search for 'evil' in the SQL) because one of the updates dropped the 'guild_eventlog' and 'guild_bank_eventlog' tables before changing them. I preferred not to loose my guild (bank) history though, and I checked that in my data there were no duplicate keys, so it was safe for me to back them up and reinsert my data so I wouldn't loose anything. If you wouldn't be so lucky and get errors, it might be a good idea to remove the statements I added (marked with 'evil'). If all goes fine, you don't loose any history and can safely drop the two backup tables I created: guild_eventlog_backup and guild_bank_eventlog_backup.

-- 18.10
ALTER TABLE characters
 CHANGE COLUMN gmstate extra_flags int(11) unsigned NOT NULL default '0';

-- 7.11
DROP TABLE IF EXISTS `character_db_version`;
CREATE TABLE `character_db_version` (
 `required_2008_11_07_01_characters_character_db_version` bit(1) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Last applied sql update to DB';
LOCK TABLES `character_db_version` WRITE;
/*!40000 ALTER TABLE `character_db_version` DISABLE KEYS */;
INSERT INTO `character_db_version` VALUES
(NULL);
/*!40000 ALTER TABLE `character_db_version` ENABLE KEYS */;
UNLOCK TABLES;

ALTER TABLE character_db_version CHANGE COLUMN required_2008_11_07_01_characters_character_db_version required_2008_11_07_03_characters_guild_bank_tab bit;
ALTER TABLE `guild_bank_tab`
 CHANGE COLUMN `TabText` `TabText` text;

-- 12.11
ALTER TABLE character_db_version CHANGE COLUMN required_2008_11_07_03_characters_guild_bank_tab required_2008_11_12_01_character_character_aura bit;
ALTER TABLE `character_aura` ADD `stackcount` INT NOT NULL DEFAULT '1' AFTER `effect_index` ;
ALTER TABLE `pet_aura` ADD `stackcount` INT NOT NULL DEFAULT '1' AFTER `effect_index` ;

-- 3.12
ALTER TABLE character_db_version CHANGE COLUMN required_2008_11_12_01_character_character_aura required_2008_12_03_01_character_guild_member bit;
ALTER TABLE `guild_member` DROP INDEX `guid_key` ,
ADD UNIQUE `guid_key` ( `guid` );

-- 12_15_01
ALTER TABLE character_db_version CHANGE COLUMN required_2008_12_03_01_character_guild_member required_2008_12_15_01_character_arenas bit;
CREATE TABLE `saved_variables` (
   `NextArenaPointDistributionTime` bigint(40) UNSIGNED NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Variable Saves';
ALTER TABLE `arena_team_member` ADD COLUMN `personal_rating` int(10) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE `characters` ADD COLUMN `arena_pending_points` int(10) UNSIGNED NOT NULL default '0';
UPDATE guild_rank SET rights = rights & ~0x00020000;
ALTER TABLE character_db_version CHANGE COLUMN required_2008_12_15_01_character_arenas required_032_7059_01_characters_character_spell bit;

ALTER TABLE character_spell
   DROP slot;
ALTER TABLE character_db_version CHANGE COLUMN required_032_7059_01_characters_character_spell required_032_7059_02_characters_pet_spell bit;

ALTER TABLE pet_spell
   DROP slot;
ALTER TABLE character_db_version CHANGE COLUMN required_032_7059_02_characters_pet_spell required_041_7903_01_characters_character_pet bit;

UPDATE character_pet
 SET abdata = CONCAT(REPLACE(TRIM(abdata),'  ',' '),' ');

UPDATE character_pet
 SET abdata = SUBSTRING_INDEX(SUBSTRING_INDEX(abdata,' ',(10-3)*2),' ',-(10-3-3)*2)
 WHERE length(SUBSTRING_INDEX(abdata, ' ', 20)) < length(abdata) and length(SUBSTRING_INDEX(abdata, ' ', 21)) >= length(abdata);
ALTER TABLE character_db_version CHANGE COLUMN required_041_7903_01_characters_character_pet required_045_8098_01_characters_character_action bit;

ALTER TABLE character_action
 CHANGE COLUMN action action int(11) unsigned NOT NULL default '0';

UPDATE character_action
 SET action = action | ( misc << 16 );

ALTER TABLE character_action
   DROP COLUMN misc;
ALTER TABLE character_db_version CHANGE COLUMN required_045_8098_01_characters_character_action required_045_8098_03_characters_character_pet bit;

UPDATE character_pet
  SET abdata = CONCAT(
    (SUBSTRING(abdata, 1, length(SUBSTRING_INDEX(abdata, ' ', 1))) >> 8),' ',
    SUBSTRING(abdata, length(SUBSTRING_INDEX(abdata, ' ', 1))+2, length(SUBSTRING_INDEX(abdata, ' ', 2))-length(SUBSTRING_INDEX(abdata, ' ', 1))-1),' ',
    (SUBSTRING(abdata, length(SUBSTRING_INDEX(abdata, ' ', 2))+2, length(SUBSTRING_INDEX(abdata, ' ', 3))-length(SUBSTRING_INDEX(abdata, ' ', 2))-1) >> 8),' ',
    SUBSTRING(abdata, length(SUBSTRING_INDEX(abdata, ' ', 3))+2, length(SUBSTRING_INDEX(abdata, ' ', 4))-length(SUBSTRING_INDEX(abdata, ' ', 3))-1),' ',
    (SUBSTRING(abdata, length(SUBSTRING_INDEX(abdata, ' ', 4))+2, length(SUBSTRING_INDEX(abdata, ' ', 5))-length(SUBSTRING_INDEX(abdata, ' ', 4))-1) >> 8),' ',
    SUBSTRING(abdata, length(SUBSTRING_INDEX(abdata, ' ', 5))+2, length(SUBSTRING_INDEX(abdata, ' ', 6))-length(SUBSTRING_INDEX(abdata, ' ', 5))-1),' ',
    (SUBSTRING(abdata, length(SUBSTRING_INDEX(abdata, ' ', 6))+2, length(SUBSTRING_INDEX(abdata, ' ', 7))-length(SUBSTRING_INDEX(abdata, ' ', 6))-1) >> 8),' ',
    SUBSTRING(abdata, length(SUBSTRING_INDEX(abdata, ' ', 7))+2, length(SUBSTRING_INDEX(abdata, ' ', 8))-length(SUBSTRING_INDEX(abdata, ' ', 7))-1),' '
 );
ALTER TABLE character_db_version CHANGE COLUMN required_045_8098_03_characters_character_pet required_045_8098_04_characters_pet_spell bit;

UPDATE pet_spell
  SET active = ( active >> 8);
ALTER TABLE character_db_version CHANGE COLUMN required_045_8098_04_characters_pet_spell required_052_8072_01_characters_characters bit;

ALTER TABLE characters
ADD gender TINYINT UNSIGNED NOT NULL default '0' AFTER class,
ADD level TINYINT UNSIGNED NOT NULL default '0' AFTER gender,
ADD xp INT UNSIGNED NOT NULL default '0' AFTER level,
ADD money INT UNSIGNED NOT NULL default '0' AFTER xp,
ADD playerBytes INT UNSIGNED NOT NULL default '0' AFTER money,
ADD playerBytes2 INT UNSIGNED NOT NULL default '0' AFTER playerBytes,
ADD playerFlags INT UNSIGNED NOT NULL default '0' AFTER playerBytes2;
UPDATE characters SET
gender = (CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(data, ' ', 37), ' ', -1) AS UNSIGNED) & 0xFF0000) >> 16,
level = CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(data, ' ', 35), ' ', -1) AS UNSIGNED),
xp = CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(data, ' ', 927), ' ', -1) AS UNSIGNED),
money = CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(data, ' ', 1462), ' ', -1) AS UNSIGNED),
playerBytes = CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(data, ' ', 240), ' ', -1) AS UNSIGNED),
playerBytes2 = CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(data, ' ', 241), ' ', -1) AS UNSIGNED),
playerFlags = CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(data, ' ', 237), ' ', -1) AS UNSIGNED)
WHERE LENGTH(SUBSTRING_INDEX(data, ' ', 1592)) < LENGTH(data) && LENGTH(data) <= LENGTH(SUBSTRING_INDEX(data, ' ', 1593));
ALTER TABLE character_db_version CHANGE COLUMN required_052_8072_01_characters_characters required_061_7067_03_characters_character_spell bit;

DELETE FROM `character_spell` WHERE `spell` IN (7376,3025,5419,5421,21156,7381,1178,21178,9635,21178,24905,5420,34123,33948,34764,40121,40122);
DELETE FROM character_spell WHERE spell in (
1178,  /* Bear Form (Passive) */
3025,  /* Cat Form (Passive) */
5419,  /* Travel Form (Passive) */
5420,  /* Tree of Life _passive_ */
5421,  /* Aquatic Form (Passive) */
7376,  /* Defensive Stance Passive */
7381,  /* Berserker Stance Passive */
9635,  /* Dire Bear Form (Passive) */
21156, /* Battle Stance Passive */
21178, /* Bear Form (Passive2) */
24905, /* Moonkin Form (Passive) */
34123, /* Tree of Life _pasive_ */
33948, /* Flight Form (Passive) */
34764, /* Flight Form (Passive) */
40121, /* Swift Flight Form (Passive) */
40122  /* Swift Flight Form (Passive) */
);
ALTER TABLE character_db_version CHANGE COLUMN required_061_7067_03_characters_character_spell required_066_8402_01_characters_guild_eventlog bit;


-- evil
RENAME TABLE `guild_eventlog` TO `guild_eventlog_backup`;


-- THIS SCRIPT DELETES table `guild_eventlog` - MAKE BACKUP, if you need it.

DROP TABLE IF EXISTS `guild_eventlog`;
CREATE TABLE `guild_eventlog` (
 `guildid` int(11) NOT NULL COMMENT 'Guild Identificator',
 `LogGuid` int(11) NOT NULL COMMENT 'Log record identificator - auxiliary column',
 `EventType` tinyint(1) NOT NULL COMMENT 'Event type',
 `PlayerGuid1` int(11) NOT NULL COMMENT 'Player 1',
 `PlayerGuid2` int(11) NOT NULL COMMENT 'Player 2',
 `NewRank` tinyint(2) NOT NULL COMMENT 'New rank(in case promotion/demotion)',
 `TimeStamp` bigint(20) NOT NULL COMMENT 'Event UNIX time',
 PRIMARY KEY (`guildid`, `LogGuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT 'Guild Eventlog';


-- evil
INSERT INTO guild_eventlog (guildid, LogGuid, EventType, PlayerGuid1, PlayerGuid2, NewRank, TimeStamp) (SELECT guildid, @N := @N +1 as logguid, EventType, PlayerGuid1, PlayerGuid2, NewRank, TimeStamp FROM guild_eventlog_backup, (select @N := -1) dummy);


-- The reason i decided for such dramatic change is that old guild_eventlog table didn't have Primary key and
-- used LogGuids from 0 to infinity
-- New system uses LogGuids from 0 to number defined in config.
ALTER TABLE character_db_version CHANGE COLUMN required_066_8402_01_characters_guild_eventlog required_066_8402_02_characters_guild_bank_eventlog bit;


-- evil
RENAME TABLE `guild_bank_eventlog` TO `guild_bank_eventlog_backup`;


-- THIS SCRIPT DELETES table `guild_bank_eventlog` - MAKE BACKUP, if you need it.

DROP TABLE IF EXISTS `guild_bank_eventlog`;
CREATE TABLE `guild_bank_eventlog` (
 `guildid` int(11) unsigned NOT NULL default '0' COMMENT 'Guild Identificator',
 `LogGuid` int(11) unsigned NOT NULL default '0' COMMENT 'Log record identificator - auxiliary column',
 `TabId` tinyint(3) unsigned NOT NULL default '0' COMMENT 'Guild bank TabId',
 `EventType` tinyint(3) unsigned NOT NULL default '0' COMMENT 'Event type',
 `PlayerGuid` int(11) unsigned NOT NULL default '0',
 `ItemOrMoney` int(11) unsigned NOT NULL default '0',
 `ItemStackCount` tinyint(3) unsigned NOT NULL default '0',
 `DestTabId` tinyint(1) unsigned NOT NULL default '0' COMMENT 'Destination Tab Id',
 `TimeStamp` bigint(20) unsigned NOT NULL default '0' COMMENT 'Event UNIX time',
 PRIMARY KEY  (`guildid`,`LogGuid`,`TabId`),
 KEY `guildid_key` (`guildid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- evil
INSERT INTO guild_bank_eventlog (guildid,LogGuid,TabId,EventType,PlayerGuid,ItemOrMoney,ItemStackCount,DestTabId,TimeStamp) (select guildid, @N := @N +1 as logguid, TabId, LogEntry as EventType, PlayerGuid, ItemOrMoney, ItemStackCount, DestTabId, TimeStamp from guild_bank_eventlog_backup, (select @N := -1) dummy);

-- The reason i decided for such dramatic change is that old guild_bank_eventlog table used `TabId` = 0 for Money events and
-- used `LogGuid` from 0 to infinity
-- New system uses `LogGuid` from 0 to number defined in config.
ALTER TABLE character_db_version CHANGE COLUMN required_066_8402_02_characters_guild_bank_eventlog required_066_8409_01_characters_guild bit;


-- Change createdate column type from datetime to bigint(20)

-- add temporary column
ALTER TABLE guild ADD COLUMN created_temp bigint(20) default '0';
-- update temporary columns data
UPDATE guild SET created_temp = UNIX_TIMESTAMP(createdate);
-- drop current column
ALTER TABLE guild DROP COLUMN createdate;
-- create new column with correct type
ALTER TABLE guild ADD COLUMN createdate bigint(20) NOT NULL default '0' AFTER motd;
-- copy data to new column
UPDATE guild set createdate = created_temp;
-- remove old column
ALTER TABLE guild DROP COLUMN created_temp;

--
UPDATE character_reputation SET standing = 0 WHERE faction IN (729, 730) AND standing < 0;

--
UPDATE guild_rank SET BankMoneyPerDay = 4294967295 WHERE rid = 0;

--
ALTER TABLE character_db_version CHANGE COLUMN required_066_8409_01_characters_guild required_091_9339_01_characters_group bit;

ALTER TABLE groups
 ADD COLUMN `groupId` int(11) unsigned NOT NULL FIRST;

UPDATE groups
 SET `groupId` = `leaderGuid`;

ALTER TABLE groups
 DROP PRIMARY KEY,
 ADD PRIMARY KEY (`groupId`),
 ADD UNIQUE KEY (`leaderGuid`);

ALTER TABLE group_member
 CHANGE COLUMN `leaderGuid` `groupId` int(11) unsigned NOT NULL;

--
ALTER TABLE character_db_version CHANGE COLUMN required_091_9339_01_characters_group required_097_9611_01_characters bit;

ALTER TABLE `group_member`            ADD INDEX `Idx_memberGuid`(`memberGuid`);
ALTER TABLE `guild_eventlog`          ADD INDEX `Idx_PlayerGuid1`(`PlayerGuid1`);
ALTER TABLE `guild_eventlog`          ADD INDEX `Idx_PlayerGuid2`(`PlayerGuid2`);
ALTER TABLE `guild_bank_eventlog`     ADD INDEX `Idx_PlayerGuid`(`PlayerGuid`);
ALTER TABLE `petition_sign`           ADD INDEX `Idx_playerguid`(`playerguid`);
ALTER TABLE `petition_sign`           ADD INDEX `Idx_ownerguid`(`ownerguid`);
ALTER TABLE `guild_eventlog`          ADD INDEX `Idx_LogGuid`(`LogGuid`);
ALTER TABLE `guild_bank_eventlog`     ADD INDEX `Idx_LogGuid`(`LogGuid`);
ALTER TABLE `guild_bank_item`         ADD INDEX `Idx_item_guid`(`item_guid`);
ALTER TABLE `corpse`                  ADD INDEX `Idx_player`(`player`);
ALTER TABLE `corpse`                  ADD INDEX `Idx_time`(`time`);
ALTER TABLE `guild_rank`              ADD INDEX `Idx_rid`(`rid`);

--
ALTER TABLE character_db_version CHANGE COLUMN required_097_9611_01_characters required_102_9687_01_characters_character_queststatus_daily bit;

ALTER TABLE `character_queststatus_daily`
 DROP COLUMN `time`;

ALTER TABLE `saved_variables`
 ADD COLUMN `NextDailyQuestResetTime` bigint(40) unsigned NOT NULL default '0' AFTER `NextArenaPointDistributionTime`;



ALTER TABLE character_db_version CHANGE COLUMN required_102_9687_01_characters_character_queststatus_daily required_105_9680_01_characters_character_stats bit;

DROP TABLE IF EXISTS `character_stats`;
CREATE TABLE `character_stats` (
 `guid` int(11) unsigned NOT NULL default '0' COMMENT 'Global Unique Identifier, Low part',
 `maxhealth` int(10) UNSIGNED NOT NULL default '0',
 `maxpower1` int(10) UNSIGNED NOT NULL default '0',
 `maxpower2` int(10) UNSIGNED NOT NULL default '0',
 `maxpower3` int(10) UNSIGNED NOT NULL default '0',
 `maxpower4` int(10) UNSIGNED NOT NULL default '0',
 `maxpower5` int(10) UNSIGNED NOT NULL default '0',
 `maxpower6` int(10) UNSIGNED NOT NULL default '0',
 `maxpower7` int(10) UNSIGNED NOT NULL default '0',
 `strength` int(10) UNSIGNED NOT NULL default '0',
 `agility` int(10) UNSIGNED NOT NULL default '0',
 `stamina` int(10) UNSIGNED NOT NULL default '0',
 `intellect` int(10) UNSIGNED NOT NULL default '0',
 `spirit` int(10) UNSIGNED NOT NULL default '0',
 `armor` int(10) UNSIGNED NOT NULL default '0',
 `resHoly` int(10) UNSIGNED NOT NULL default '0',
 `resFire` int(10) UNSIGNED NOT NULL default '0',
 `resNature` int(10) UNSIGNED NOT NULL default '0',
 `resFrost` int(10) UNSIGNED NOT NULL default '0',
 `resShadow` int(10) UNSIGNED NOT NULL default '0',
 `resArcane` int(10) UNSIGNED NOT NULL default '0',
 `blockPct` float UNSIGNED NOT NULL default '0',
 `dodgePct` float UNSIGNED NOT NULL default '0',
 `parryPct` float UNSIGNED NOT NULL default '0',
 `critPct` float UNSIGNED NOT NULL default '0',
 `rangedCritPct` float UNSIGNED NOT NULL default '0',
 `spellCritPct` float UNSIGNED NOT NULL default '0',
 `attackPower` int(10) UNSIGNED NOT NULL default '0',
 `rangedAttackPower` int(10) UNSIGNED NOT NULL default '0',
 `spellPower` int(10) UNSIGNED NOT NULL default '0',
 PRIMARY KEY  (`guid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
ALTER TABLE character_db_version CHANGE COLUMN required_105_9680_01_characters_character_stats required_106_9751_01_characters bit;

ALTER TABLE `character_spell` ADD KEY `Idx_spell` (`spell`);

--
ALTER TABLE character_db_version CHANGE COLUMN required_106_9751_01_characters required_111_9767_03_characters_characters bit;

ALTER TABLE `characters`
  ADD COLUMN `deleteInfos_Account` int(11) UNSIGNED default NULL AFTER arena_pending_points,
  ADD COLUMN `deleteInfos_Name` varchar(12) default NULL AFTER deleteInfos_Account,
  ADD COLUMN `deleteDate` bigint(20) default NULL AFTER deleteInfos_Name;
ALTER TABLE character_db_version CHANGE COLUMN required_111_9767_03_characters_characters required_112_8874_01_characters_character_skills bit;

DROP TABLE IF EXISTS `character_skills`;
CREATE TABLE `character_skills` (
 `guid` int(11) unsigned NOT NULL COMMENT 'Global Unique Identifier',
 `skill` mediumint(9) unsigned NOT NULL,
 `value` int(11) unsigned NOT NULL,
 `max` mediumint(9) unsigned NOT NULL,
 i mediumint(9),
 PRIMARY KEY  (`guid`,`skill`,`i`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Player System';

DROP TABLE IF EXISTS temp_skills;
CREATE TABLE temp_skills (
 i int(11) unsigned NOT NULL,
 PRIMARY KEY (i)
);

INSERT INTO temp_skills VALUES
( 0),
( 1),
( 2),
( 3),
( 4),
( 5),
( 6),
( 7),
( 8),
( 9),
(10),
(11),
(12),
(13),
(14),
(15),
(16),
(17),
(18),
(19),
(20),
(21),
(22),
(23),
(24),
(25),
(26),
(27),
(28),
(29),
(30),
(31),
(32),
(33),
(34),
(35),
(36),
(37),
(38),
(39),
(40),
(41),
(42),
(43),
(44),
(45),
(46),
(47),
(48),
(49),
(50),
(51),
(52),
(53),
(54),
(55),
(56),
(57),
(58),
(59),
(60),
(61),
(62),
(63),
(64),
(65),
(66),
(67),
(68),
(69),
(70),
(71),
(72),
(73),
(74),
(75),
(76),
(77),
(78),
(79),
(80),
(81),
(82),
(83),
(84),
(85),
(86),
(87),
(88),
(89),
(90),
(91),
(92),
(93),
(94),
(95),
(96),
(97),
(98),
(99),
(100),
(101),
(102),
(103),
(104),
(105),
(106),
(107),
(108),
(109),
(110),
(111),
(112),
(113),
(114),
(115),
(116),
(117),
(118),
(119),
(120),
(121),
(122),
(123),
(124),
(125),
(126),
(127);

INSERT INTO character_skills SELECT
guid,
((SUBSTRING(data, length(SUBSTRING_INDEX(data, ' ', 928+3*i))+2, length(SUBSTRING_INDEX(data, ' ', 928+3*i+1))- length(SUBSTRING_INDEX(data, ' ', 928+3*i)) - 1)) & 0xFFFF) as skill,
(SUBSTRING(data, length(SUBSTRING_INDEX(data, ' ', 928+3*i+1))+2, length(SUBSTRING_INDEX(data, ' ', 928+3*i+2))- length(SUBSTRING_INDEX(data, ' ', 928+3*i+1)) - 1)) as value,
(0) as max,
i
FROM characters, temp_skills;

DELETE FROM character_skills WHERE skill = 0;
DROP TABLE IF EXISTS temp_skills;

UPDATE character_skills
 SET max = ((value & 0xFFFF0000) >> 16);

UPDATE character_skills
 SET value = (value & 0xFFFF);

ALTER IGNORE TABLE character_skills
 CHANGE COLUMN value value mediumint(9) unsigned NOT NULL,
 DROP PRIMARY KEY,
 ADD  PRIMARY KEY (guid,skill),
 DROP COLUMN i;
ALTER TABLE character_db_version CHANGE COLUMN required_112_8874_01_characters_character_skills required_114_9849_01_characters_saved_variables bit;

ALTER TABLE saved_variables ADD cleaning_flags int(11) unsigned NOT NULL default '0' AFTER NextDailyQuestResetTime;
UPDATE saved_variables SET cleaning_flags = 0xF;
ALTER TABLE character_db_version CHANGE COLUMN required_114_9849_01_characters_saved_variables required_s0041_10254_01_characters_auctionhouse bit;

ALTER TABLE auctionhouse
 ADD COLUMN houseid int(11) unsigned NOT NULL default '0' AFTER id;

UPDATE auctionhouse SET houseid = location;

ALTER TABLE auctionhouse
 DROP COLUMN auctioneerguid,
 DROP COLUMN location;

DROP TABLE IF EXISTS auction;
RENAME TABLE auctionhouse TO auction;
ALTER TABLE character_db_version CHANGE COLUMN required_s0041_10254_01_characters_auctionhouse required_s0087_10312_01_characters_character_aura bit;

ALTER TABLE `character_aura` DROP PRIMARY KEY;
ALTER TABLE `character_aura` ADD PRIMARY KEY  (`guid`,`caster_guid`,`spell`,`effect_index`);
ALTER TABLE character_db_version CHANGE COLUMN required_s0087_10312_01_characters_character_aura required_s0087_10312_02_characters_pet_aura bit;

ALTER TABLE `pet_aura` DROP PRIMARY KEY;
ALTER TABLE `pet_aura` ADD PRIMARY KEY (`guid`,`caster_guid`,`spell`,`effect_index`);
ALTER TABLE character_db_version CHANGE COLUMN required_s0087_10312_02_characters_pet_aura required_s0099_10332_01_characters_character_aura bit;

ALTER TABLE `character_aura`
 ADD COLUMN `item_guid` int(11) unsigned NOT NULL default '0' AFTER `caster_guid`,
 DROP PRIMARY KEY,
 ADD PRIMARY KEY  (`guid`,`caster_guid`,`item_guid`,`spell`,`effect_index`);

ALTER TABLE character_db_version CHANGE COLUMN required_s0099_10332_01_characters_character_aura required_s0099_10332_02_characters_pet_aura bit;

ALTER TABLE `pet_aura`
 ADD COLUMN `item_guid` int(11) unsigned NOT NULL default '0' AFTER `caster_guid`,
 DROP PRIMARY KEY,
 ADD PRIMARY KEY  (`guid`,`caster_guid`,`item_guid`,`spell`,`effect_index`);

ALTER TABLE character_db_version CHANGE COLUMN required_s0099_10332_02_characters_pet_aura required_s0114_09136_01_characters_character_ticket bit;

alter table `character_ticket`
   add column `response_text` text CHARSET utf8 COLLATE utf8_general_ci NULL after `ticket_text`;
ALTER TABLE character_db_version CHANGE COLUMN required_s0114_09136_01_characters_character_ticket required_s0201_7255_01_characters_characters bit;

ALTER TABLE `characters`
 ADD COLUMN `bgid` int(10) unsigned NOT NULL default '0' AFTER `arena_pending_points`,
 ADD COLUMN `bgteam` int(10) unsigned NOT NULL default '0' AFTER `bgid`,
 ADD COLUMN `bgmap` int(10) unsigned NOT NULL default '0' AFTER `bgteam`,
 ADD COLUMN `bgx` float NOT NULL default '0' AFTER `bgmap`,
 ADD COLUMN `bgy` float NOT NULL default '0' AFTER `bgx`,
 ADD COLUMN `bgz` float NOT NULL default '0' AFTER `bgy`,
 ADD COLUMN `bgo` float NOT NULL default '0' AFTER `bgz`;

ALTER TABLE character_db_version CHANGE COLUMN required_s0201_7255_01_characters_characters required_s0216_7307_01_characters_arena_team_member bit;

ALTER TABLE arena_team_member
 ADD PRIMARY KEY  (arenateamid,guid);
ALTER TABLE character_db_version CHANGE COLUMN required_s0216_7307_01_characters_arena_team_member required_s0224_10503_01_characters_creature_respawn bit;

DROP TABLE IF EXISTS `creature_respawn`;
CREATE TABLE `creature_respawn` (
 `guid` int(10) unsigned NOT NULL default '0' COMMENT 'Global Unique Identifier',
 `respawntime` bigint(20) NOT NULL default '0',
 `instance` mediumint(8) unsigned NOT NULL default '0',
 PRIMARY KEY  (`guid`,`instance`),
 KEY `instance` (`instance`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Grid Loading System';
ALTER TABLE character_db_version CHANGE COLUMN required_s0224_10503_01_characters_creature_respawn required_s0224_10503_02_characters_gameobject_respawn bit;

DROP TABLE IF EXISTS `gameobject_respawn`;
CREATE TABLE `gameobject_respawn` (
 `guid` int(10) unsigned NOT NULL default '0' COMMENT 'Global Unique Identifier',
 `respawntime` bigint(20) NOT NULL default '0',
 `instance` mediumint(8) unsigned NOT NULL default '0',
 PRIMARY KEY  (`guid`,`instance`),
 KEY `instance` (`instance`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Grid Loading System';


-- NEW UPDATES HERE

ALTER TABLE character_db_version CHANGE COLUMN required_s0224_10503_02_characters_gameobject_respawn required_s0384_10664_01_characters_arena_team_stats bit;

ALTER TABLE arena_team_stats
 CHANGE COLUMN games  games_week   int(10) unsigned NOT NULL default '0',
 CHANGE COLUMN wins   wins_week    int(10) unsigned NOT NULL default '0',
 CHANGE COLUMN played games_season int(10) unsigned NOT NULL default '0',
 CHANGE COLUMN wins2  wins_season  int(10) unsigned NOT NULL default '0';
ALTER TABLE character_db_version CHANGE COLUMN required_s0384_10664_01_characters_arena_team_stats required_s0386_10662_01_characters_item_loot bit;

DROP TABLE IF EXISTS `item_loot`;
CREATE TABLE `item_loot` (
 `guid` int(11) unsigned NOT NULL default '0',
 `owner_guid` int(11) unsigned NOT NULL default '0',
 `itemid` int(11) unsigned NOT NULL default '0',
 `amount` int(11) unsigned NOT NULL default '0',
 `suffix` int(11) unsigned NOT NULL default '0',
 `property` int(11) NOT NULL default '0',
 PRIMARY KEY  (`guid`,`itemid`),
 KEY `idx_owner_guid` (`owner_guid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Item System';
ALTER TABLE character_db_version CHANGE COLUMN required_s0386_10662_01_characters_item_loot required_s0507_8339_01_characters_characters bit;

ALTER TABLE characters DROP COLUMN bgid;
ALTER TABLE characters DROP COLUMN bgteam;
ALTER TABLE characters DROP COLUMN bgmap;
ALTER TABLE characters DROP COLUMN bgx;
ALTER TABLE characters DROP COLUMN bgy;
ALTER TABLE characters DROP COLUMN bgz;
ALTER TABLE characters DROP COLUMN bgo;
ALTER TABLE character_db_version CHANGE COLUMN required_s0507_8339_01_characters_characters required_s0507_8339_02_characters_character_battleground_data bit;

DROP TABLE IF EXISTS `character_battleground_data`;
CREATE TABLE `character_battleground_data` (
 `guid` int(11) unsigned NOT NULL default '0' COMMENT 'Global Unique Identifier',
 `instance_id` int(11) unsigned NOT NULL default '0',
 `team` int(11) unsigned NOT NULL default '0',
 `join_x` float NOT NULL default '0',
 `join_y` float NOT NULL default '0',
 `join_z` float NOT NULL default '0',
 `join_o` float NOT NULL default '0',
 `join_map` int(11) NOT NULL default '0',
 PRIMARY KEY  (`guid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Player System';
ALTER TABLE character_db_version CHANGE COLUMN required_s0507_8339_02_characters_character_battleground_data required_s0531_8596_01_characters_bugreport bit;

ALTER TABLE `bugreport` CHANGE `type` `type` LONGTEXT NOT NULL;
ALTER TABLE `bugreport` CHANGE `content` `content` LONGTEXT NOT NULL;
ALTER TABLE character_db_version CHANGE COLUMN required_s0531_8596_01_characters_bugreport required_s0540_8469_01_characters_character_spell bit;

DELETE FROM character_spell WHERE spell in (
1178,  /* Bear Form (Passive) */
3025,  /* Cat Form (Passive) */
5419,  /* Travel Form (Passive) */
5420,  /* Tree of Life _passive_ */
5421,  /* Aquatic Form (Passive) */
7376,  /* Defensive Stance Passive */
7381,  /* Berserker Stance Passive */
9635,  /* Dire Bear Form (Passive) */
21156, /* Battle Stance Passive */
21178, /* Bear Form (Passive2) */
24905, /* Moonkin Form (Passive) */
34123, /* Tree of Life _pasive_ */
33948, /* Flight Form (Passive) */
34764, /* Flight Form (Passive) */
40121, /* Swift Flight Form (Passive) */
40122  /* Swift Flight Form (Passive) */
);
ALTER TABLE character_db_version CHANGE COLUMN required_s0540_8469_01_characters_character_spell required_s0636_10973_02_characters_game_event_status bit;

DROP TABLE IF EXISTS `game_event_status`;
CREATE TABLE `game_event_status` (
 `event` smallint(6) unsigned NOT NULL default '0',
 PRIMARY KEY  (`event`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED COMMENT='Game event system';
ALTER TABLE character_db_version CHANGE COLUMN required_s0636_10973_02_characters_game_event_status required_s0738_9246_01_characters_character bit;

ALTER TABLE characters
 ADD COLUMN `arenaPoints` int(10) UNSIGNED NOT NULL default '0' AFTER arena_pending_points,
 ADD COLUMN `totalHonorPoints` int(10) UNSIGNED NOT NULL default '0' AFTER arenaPoints,
 ADD COLUMN `todayHonorPoints` int(10) UNSIGNED NOT NULL default '0' AFTER totalHonorPoints,
 ADD COLUMN `yesterdayHonorPoints` int(10) UNSIGNED NOT NULL default '0' AFTER todayHonorPoints,
 ADD COLUMN `totalKills` int(10) UNSIGNED NOT NULL default '0' AFTER yesterdayHonorPoints,
 ADD COLUMN `todayKills` smallint(5) UNSIGNED NOT NULL default '0' AFTER totalKills,
 ADD COLUMN `yesterdayKills` smallint(5) UNSIGNED NOT NULL default '0' AFTER todayKills,
 ADD COLUMN `chosenTitle` int(10) UNSIGNED NOT NULL default '0' AFTER yesterdayKills,
 ADD COLUMN `watchedFaction` int(10) UNSIGNED NOT NULL default '0' AFTER chosenTitle,
 ADD COLUMN `drunk` smallint(5) UNSIGNED NOT NULL default '0' AFTER watchedFaction,
 ADD COLUMN `health` int(10) UNSIGNED NOT NULL default '0' AFTER drunk,
 ADD COLUMN `power1` int(10) UNSIGNED NOT NULL default '0' AFTER health,
 ADD COLUMN `power2` int(10) UNSIGNED NOT NULL default '0' AFTER power1,
 ADD COLUMN `power3` int(10) UNSIGNED NOT NULL default '0' AFTER power2,
 ADD COLUMN `power4` int(10) UNSIGNED NOT NULL default '0' AFTER power3,
 ADD COLUMN `power5` int(10) UNSIGNED NOT NULL default '0' AFTER power4;

UPDATE IGNORE characters SET
 arenaPoints = arena_pending_points +
   SUBSTRING(data, length(SUBSTRING_INDEX(data, ' ', 1563))+2, length(SUBSTRING_INDEX(data, ' ', 1563+1))- length(SUBSTRING_INDEX(data, ' ', 1563)) - 1),
 totalHonorPoints =
   SUBSTRING(data, length(SUBSTRING_INDEX(data, ' ', 1562))+2, length(SUBSTRING_INDEX(data, ' ', 1562+1))- length(SUBSTRING_INDEX(data, ' ', 1562)) - 1),
 todayHonorPoints =
   SUBSTRING(data, length(SUBSTRING_INDEX(data, ' ', 1515))+2, length(SUBSTRING_INDEX(data, ' ', 1515+1))- length(SUBSTRING_INDEX(data, ' ', 1515)) - 1),
 yesterdayHonorPoints =
   SUBSTRING(data, length(SUBSTRING_INDEX(data, ' ', 1516))+2, length(SUBSTRING_INDEX(data, ' ', 1516+1))- length(SUBSTRING_INDEX(data, ' ', 1516)) - 1),
 totalKills =
   SUBSTRING(data, length(SUBSTRING_INDEX(data, ' ', 1517))+2, length(SUBSTRING_INDEX(data, ' ', 1517+1))- length(SUBSTRING_INDEX(data, ' ', 1517)) - 1),
 todayKills =
   SUBSTRING(data, length(SUBSTRING_INDEX(data, ' ', 1514))+2, length(SUBSTRING_INDEX(data, ' ', 1514+1))- length(SUBSTRING_INDEX(data, ' ', 1514)) - 1) & 0x0000FFFF,
 yesterdayKills =
   (SUBSTRING(data, length(SUBSTRING_INDEX(data, ' ', 1514))+2, length(SUBSTRING_INDEX(data, ' ', 1514+1))- length(SUBSTRING_INDEX(data, ' ', 1514)) - 1) & 0xFFFF0000) >> 16,
 chosenTitle =
   SUBSTRING(data, length(SUBSTRING_INDEX(data, ' ',  648))+2, length(SUBSTRING_INDEX(data, ' ',  648+1))- length(SUBSTRING_INDEX(data, ' ',  648)) - 1),
 watchedFaction =
   SUBSTRING(data, length(SUBSTRING_INDEX(data, ' ', 1519))+2, length(SUBSTRING_INDEX(data, ' ', 1519+1))- length(SUBSTRING_INDEX(data, ' ', 1519)) - 1),
 drunk =
   SUBSTRING(data, length(SUBSTRING_INDEX(data, ' ',  241))+2, length(SUBSTRING_INDEX(data, ' ',  241+1))- length(SUBSTRING_INDEX(data, ' ',  241)) - 1) & 0xFFFE,
 health =
   SUBSTRING(data, length(SUBSTRING_INDEX(data, ' ',   22))+2, length(SUBSTRING_INDEX(data, ' ',   22+1))- length(SUBSTRING_INDEX(data, ' ',   22)) - 1),
 power1 =
   SUBSTRING(data, length(SUBSTRING_INDEX(data, ' ',   23))+2, length(SUBSTRING_INDEX(data, ' ',   23+1))- length(SUBSTRING_INDEX(data, ' ',   23)) - 1),
 power2 =
   SUBSTRING(data, length(SUBSTRING_INDEX(data, ' ',   24))+2, length(SUBSTRING_INDEX(data, ' ',   24+1))- length(SUBSTRING_INDEX(data, ' ',   24)) - 1),
 power3 =
   SUBSTRING(data, length(SUBSTRING_INDEX(data, ' ',   25))+2, length(SUBSTRING_INDEX(data, ' ',   25+1))- length(SUBSTRING_INDEX(data, ' ',   25)) - 1),
 power4 =
   SUBSTRING(data, length(SUBSTRING_INDEX(data, ' ',   26))+2, length(SUBSTRING_INDEX(data, ' ',   26+1))- length(SUBSTRING_INDEX(data, ' ',   26)) - 1),
 power5 =
   SUBSTRING(data, length(SUBSTRING_INDEX(data, ' ',   27))+2, length(SUBSTRING_INDEX(data, ' ',   27+1))- length(SUBSTRING_INDEX(data, ' ',   27)) - 1);

ALTER TABLE characters
 DROP COLUMN arena_pending_points;
ALTER TABLE character_db_version CHANGE COLUMN required_s0738_9246_01_characters_character required_s0817_11117_02_characters_world bit;

--
-- Table structure for table `world`
--

DROP TABLE IF EXISTS `world`;
CREATE TABLE `world` (
 `map` int(11) unsigned NOT NULL default '0',
 `data` longtext,
 PRIMARY KEY  (`map`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE character_db_version CHANGE COLUMN required_s0817_11117_02_characters_world required_s0858_xxxxx_01_characters_characters bit;

ALTER TABLE characters
 ADD COLUMN `exploredZones` longtext AFTER power5,
 ADD COLUMN `equipmentCache` longtext AFTER exploredZones,
 ADD COLUMN `ammoId` int(10) UNSIGNED NOT NULL default '0' AFTER equipmentCache,
 ADD COLUMN `knownTitles` longtext AFTER ammoId,
 ADD COLUMN `actionBars` tinyint(3) UNSIGNED NOT NULL default '0' AFTER knownTitles;

UPDATE characters SET
exploredZones = SUBSTRING(data,
 length(SUBSTRING_INDEX(data, ' ', 1332))+2,
 length(SUBSTRING_INDEX(data, ' ', 1459+1))- length(SUBSTRING_INDEX(data, ' ', 1332)) - 1),
equipmentCache = '0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ',
ammoId = SUBSTRING(data,
 length(SUBSTRING_INDEX(data, ' ', 1487))+2,
 length(SUBSTRING_INDEX(data, ' ', 1487+1))- length(SUBSTRING_INDEX(data, ' ', 1487)) - 1),
knownTitles = SUBSTRING(data,
 length(SUBSTRING_INDEX(data, ' ', 924))+2,
 length(SUBSTRING_INDEX(data, ' ', 925+1))- length(SUBSTRING_INDEX(data, ' ', 924)) - 1),
actionBars = ((SUBSTRING(data,
 length(SUBSTRING_INDEX(data, ' ', 1486))+2,
length(SUBSTRING_INDEX(data, ' ', 1486+1))- length(SUBSTRING_INDEX(data, ' ', 1486)) - 1) & 0xFF0000) >> 16);

-- evil
UPDATE characters SET
equipmentCache = CONCAT(CONCAT_WS(' ',SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 346))+2,length(SUBSTRING_INDEX(data, ' ', 346+1))-length(SUBSTRING_INDEX(data, ' ', 346))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 346+12))+2,length(SUBSTRING_INDEX(data, ' ', 346+12+1))-length(SUBSTRING_INDEX(data, ' ', 346+12))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 362))+2,length(SUBSTRING_INDEX(data, ' ', 362+1))-length(SUBSTRING_INDEX(data, ' ', 362))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 362+12))+2,length(SUBSTRING_INDEX(data, ' ', 362+12+1))-length(SUBSTRING_INDEX(data, ' ', 362+12))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 378))+2,length(SUBSTRING_INDEX(data, ' ', 378+1))-length(SUBSTRING_INDEX(data, ' ', 378))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 378+12))+2,length(SUBSTRING_INDEX(data, ' ', 378+12+1))-length(SUBSTRING_INDEX(data, ' ', 378+12))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 394))+2,length(SUBSTRING_INDEX(data, ' ', 394+1))-length(SUBSTRING_INDEX(data, ' ', 394))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 394+12))+2,length(SUBSTRING_INDEX(data, ' ', 394+12+1))-length(SUBSTRING_INDEX(data, ' ', 394+12))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 410))+2,length(SUBSTRING_INDEX(data, ' ', 410+1))-length(SUBSTRING_INDEX(data, ' ', 410))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 410+12))+2,length(SUBSTRING_INDEX(data, ' ', 410+12+1))-length(SUBSTRING_INDEX(data, ' ', 410+12))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 426))+2,length(SUBSTRING_INDEX(data, ' ', 426+1))-length(SUBSTRING_INDEX(data, ' ', 426))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 426+12))+2,length(SUBSTRING_INDEX(data, ' ', 426+12+1))-length(SUBSTRING_INDEX(data, ' ', 426+12))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 442))+2,length(SUBSTRING_INDEX(data, ' ', 442+1))-length(SUBSTRING_INDEX(data, ' ', 442))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 442+12))+2,length(SUBSTRING_INDEX(data, ' ', 442+12+1))-length(SUBSTRING_INDEX(data, ' ', 442+12))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 458))+2,length(SUBSTRING_INDEX(data, ' ', 458+1))-length(SUBSTRING_INDEX(data, ' ', 458))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 458+12))+2,length(SUBSTRING_INDEX(data, ' ', 458+12+1))-length(SUBSTRING_INDEX(data, ' ', 458+12))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 474))+2,length(SUBSTRING_INDEX(data, ' ', 474+1))-length(SUBSTRING_INDEX(data, ' ', 474))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 474+12))+2,length(SUBSTRING_INDEX(data, ' ', 474+12+1))-length(SUBSTRING_INDEX(data, ' ', 474+12))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 490))+2,length(SUBSTRING_INDEX(data, ' ', 490+1))-length(SUBSTRING_INDEX(data, ' ', 490))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 490+12))+2,length(SUBSTRING_INDEX(data, ' ', 490+12+1))-length(SUBSTRING_INDEX(data, ' ', 490+12))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 506))+2,length(SUBSTRING_INDEX(data, ' ', 506+1))-length(SUBSTRING_INDEX(data, ' ', 506))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 506+12))+2,length(SUBSTRING_INDEX(data, ' ', 506+12+1))-length(SUBSTRING_INDEX(data, ' ', 506+12))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 522))+2,length(SUBSTRING_INDEX(data, ' ', 522+1))-length(SUBSTRING_INDEX(data, ' ', 522))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 522+12))+2,length(SUBSTRING_INDEX(data, ' ', 522+12+1))-length(SUBSTRING_INDEX(data, ' ', 522+12))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 538))+2,length(SUBSTRING_INDEX(data, ' ', 538+1))-length(SUBSTRING_INDEX(data, ' ', 538))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 538+12))+2,length(SUBSTRING_INDEX(data, ' ', 538+12+1))-length(SUBSTRING_INDEX(data, ' ', 538+12))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 554))+2,length(SUBSTRING_INDEX(data, ' ', 554+1))-length(SUBSTRING_INDEX(data, ' ', 554))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 554+12))+2,length(SUBSTRING_INDEX(data, ' ', 554+12+1))-length(SUBSTRING_INDEX(data, ' ', 554+12))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 570))+2,length(SUBSTRING_INDEX(data, ' ', 570+1))-length(SUBSTRING_INDEX(data, ' ', 570))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 570+12))+2,length(SUBSTRING_INDEX(data, ' ', 570+12+1))-length(SUBSTRING_INDEX(data, ' ', 570+12))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 586))+2,length(SUBSTRING_INDEX(data, ' ', 586+1))-length(SUBSTRING_INDEX(data, ' ', 586))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 586+12))+2,length(SUBSTRING_INDEX(data, ' ', 586+12+1))-length(SUBSTRING_INDEX(data, ' ', 586+12))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 602))+2,length(SUBSTRING_INDEX(data, ' ', 602+1))-length(SUBSTRING_INDEX(data, ' ', 602))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 602+12))+2,length(SUBSTRING_INDEX(data, ' ', 602+12+1))-length(SUBSTRING_INDEX(data, ' ', 602+12))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 618))+2,length(SUBSTRING_INDEX(data, ' ', 618+1))-length(SUBSTRING_INDEX(data, ' ', 618))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 618+12))+2,length(SUBSTRING_INDEX(data, ' ', 618+12+1))-length(SUBSTRING_INDEX(data, ' ', 618+12))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 634))+2,length(SUBSTRING_INDEX(data, ' ', 634+1))-length(SUBSTRING_INDEX(data, ' ', 634))-1),
SUBSTRING(data,length(SUBSTRING_INDEX(data, ' ', 634+12))+2,length(SUBSTRING_INDEX(data, ' ', 634+12+1))-length(SUBSTRING_INDEX(data, ' ', 634+12))-1)),' ');


CREATE TABLE `data_backup` (
 `guid` int(11) unsigned NOT NULL default '0' COMMENT 'Global Unique Identifier',
 `data` longtext,
 PRIMARY KEY  (`guid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO data_backup (guid, data)  (SELECT guid, data FROM characters);


ALTER TABLE characters
 DROP COLUMN data;
ALTER TABLE character_db_version CHANGE COLUMN required_s0858_xxxxx_01_characters_characters required_s0860_9634_01_characters_corpse bit;

ALTER TABLE corpse
 DROP COLUMN data,
 DROP COLUMN zone;
ALTER TABLE character_db_version CHANGE COLUMN required_s0860_9634_01_characters_corpse required_s0863_xxxxx_01_characters_characters bit;


UPDATE characters SET exploredZones = CONCAT(TRIM(exploredZones),' ');

UPDATE characters
  SET exploredZones = CONCAT(exploredZones,'0 ')
WHERE length(SUBSTRING_INDEX(exploredZones, ' ', 127)) < length(exploredZones) and length(SUBSTRING_INDEX(exploredZones, ' ', 127+1)) >= length(exploredZones);

/* use this version instead if you have data_backup table in characters DB
UPDATE characters, data_backup SET
 exploredZones = SUBSTRING(data,
 length(SUBSTRING_INDEX(data, ' ', 1332))+2,
 length(SUBSTRING_INDEX(data, ' ', 1459+1))- length(SUBSTRING_INDEX(data, ' ', 1332)) - 1)
 WHERE characters.guid = data_backup.guid;
*/ALTER TABLE character_db_version CHANGE COLUMN required_s0863_xxxxx_01_characters_characters required_s0963_10156_01_characters_character_aura bit;

DROP TABLE IF EXISTS `character_aura`;
CREATE TABLE `character_aura` (
 `guid` int(11) unsigned NOT NULL default '0' COMMENT 'Global Unique Identifier',
 `caster_guid` bigint(20) unsigned NOT NULL default '0' COMMENT 'Full Global Unique Identifier',
 `item_guid` int(11) unsigned NOT NULL default '0',
 `spell` int(11) unsigned NOT NULL default '0',
 `stackcount` int(11) NOT NULL default '1',
 `remaincharges` int(11) NOT NULL default '0',
 `basepoints0` INT(11) NOT NULL DEFAULT '0',
 `basepoints1` INT(11) NOT NULL DEFAULT '0',
 `basepoints2` INT(11) NOT NULL DEFAULT '0',
 `maxduration0` INT(11) NOT NULL DEFAULT '0',
 `maxduration1` INT(11) NOT NULL DEFAULT '0',
 `maxduration2` INT(11) NOT NULL DEFAULT '0',
 `remaintime0` INT(11) NOT NULL DEFAULT '0',
 `remaintime1` INT(11) NOT NULL DEFAULT '0',
 `remaintime2` INT(11) NOT NULL DEFAULT '0',
 `effIndexMask` INT(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`guid`,`caster_guid`,`item_guid`,`spell`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Player System';
ALTER TABLE character_db_version CHANGE COLUMN required_s0963_10156_01_characters_character_aura required_s0963_10156_02_characters_pet_aura bit;

DROP TABLE IF EXISTS `pet_aura`;
CREATE TABLE `pet_aura` (
 `guid` int(11) unsigned NOT NULL default '0' COMMENT 'Global Unique Identifier',
 `caster_guid` bigint(20) unsigned NOT NULL default '0' COMMENT 'Full Global Unique Identifier',
 `item_guid` int(11) unsigned NOT NULL default '0',
 `spell` int(11) unsigned NOT NULL default '0',
 `stackcount` int(11) NOT NULL default '1',
 `remaincharges` int(11) NOT NULL default '0',
 `basepoints0` INT(11) NOT NULL DEFAULT '0',
 `basepoints1` INT(11) NOT NULL DEFAULT '0',
 `basepoints2` INT(11) NOT NULL DEFAULT '0',
 `maxduration0` INT(11) NOT NULL DEFAULT '0',
 `maxduration1` INT(11) NOT NULL DEFAULT '0',
 `maxduration2` INT(11) NOT NULL DEFAULT '0',
 `remaintime0` INT(11) NOT NULL DEFAULT '0',
 `remaintime1` INT(11) NOT NULL DEFAULT '0',
 `remaintime2` INT(11) NOT NULL DEFAULT '0',
 `effIndexMask` INT(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`guid`,`caster_guid`,`item_guid`,`spell`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='Pet System';

--
ALTER TABLE character_db_version CHANGE COLUMN required_s0963_10156_02_characters_pet_aura required_s0999_10568_01_characters_character_tutorial bit;

DELETE FROM `character_tutorial`; -- for avoid duplication errors and other problems.

ALTER TABLE `character_tutorial` DROP PRIMARY KEY;
ALTER TABLE `character_tutorial` DROP COLUMN `realmid`;
ALTER TABLE `character_tutorial` ADD PRIMARY KEY (`account`);
ALTER TABLE `character_tutorial` DROP KEY `acc_key`;

--
ALTER TABLE character_db_version CHANGE COLUMN required_s0999_10568_01_characters_character_tutorial required_s1057_10862_01_characters_mail bit;

UPDATE mail_items, mail
 SET mail_items.receiver = mail.receiver WHERE mail.id = mail_items.mail_id;

DROP TABLE IF EXISTS item_test;
CREATE TABLE item_test
SELECT mi.mail_id, mi.item_guid FROM mail_items as mi WHERE mi.mail_id NOT IN (SELECT id FROM mail);

DELETE item_instance FROM item_instance, item_test WHERE item_instance.guid = item_test.item_guid;
DELETE mail_items FROM mail_items, item_test WHERE mail_items.mail_id = item_test.mail_id;
DROP TABLE IF EXISTS item_test;
ALTER TABLE character_db_version CHANGE COLUMN required_s1057_10862_01_characters_mail required_s1099_11299_01_characters_character_aura bit;

TRUNCATE TABLE character_aura;
ALTER TABLE character_aura
 CHANGE COLUMN `maxduration0` `maxduration` INT(11) NOT NULL DEFAULT '0',
 CHANGE COLUMN `remaintime0`  `remaintime`  INT(11) NOT NULL DEFAULT '0',
 ADD COLUMN `periodictime0` INT(11) UNSIGNED NOT NULL DEFAULT '0' AFTER `basepoints2`,
 ADD COLUMN `periodictime1` INT(11) UNSIGNED NOT NULL DEFAULT '0' AFTER `periodictime0`,
 ADD COLUMN `periodictime2` INT(11) UNSIGNED NOT NULL DEFAULT '0' AFTER `periodictime1`,
 DROP COLUMN `maxduration1`,
 DROP COLUMN `maxduration2`,
 DROP COLUMN `remaintime1`,
 DROP COLUMN `remaintime2`,
 CHANGE COLUMN `stackcount` `stackcount` INT(11) UNSIGNED NOT NULL DEFAULT '1',
 CHANGE COLUMN `remaincharges` `remaincharges` INT(11) UNSIGNED NOT NULL DEFAULT '0',
 CHANGE COLUMN `effIndexMask` `effIndexMask` INT(11) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE character_db_version CHANGE COLUMN required_s1099_11299_01_characters_character_aura required_s1099_11299_02_characters_pet_aura bit;

TRUNCATE TABLE pet_aura;
ALTER TABLE pet_aura
 CHANGE COLUMN `maxduration0` `maxduration` INT(11) NOT NULL DEFAULT '0',
 CHANGE COLUMN `remaintime0`  `remaintime`  INT(11) NOT NULL DEFAULT '0',
 ADD COLUMN `periodictime0` INT(11) UNSIGNED NOT NULL DEFAULT '0' AFTER `basepoints2`,
 ADD COLUMN `periodictime1` INT(11) UNSIGNED NOT NULL DEFAULT '0' AFTER `periodictime0`,
 ADD COLUMN `periodictime2` INT(11) UNSIGNED NOT NULL DEFAULT '0' AFTER `periodictime1`,
 DROP COLUMN `maxduration1`,
 DROP COLUMN `maxduration2`,
 DROP COLUMN `remaintime1`,
 DROP COLUMN `remaintime2`,
 CHANGE COLUMN `stackcount` `stackcount` INT(11) UNSIGNED NOT NULL DEFAULT '1',
 CHANGE COLUMN `remaincharges` `remaincharges` INT(11) UNSIGNED NOT NULL DEFAULT '0',
 CHANGE COLUMN `effIndexMask` `effIndexMask` INT(11) UNSIGNED NOT NULL DEFAULT '0';

After this, you should have character_db_version at required_s1099_11299_02_characters_pet_aura. There are still a few updates to apply for you (I still run an older revision of the core), but you can find those in your current git repository in sql/updates. You want to apply the last few updates where the first number is > 1099 and the first word is characters (not mangos, those are updates for your mangos database). If you sort the files by name, they are already in the oder you need to apply them

Good luck!

[extralismizer]

Thanks a lot evil@wow! Tomorrow i will apply the updates which should complete my server

Feel invited for a Keg of Thunderbrew Lager for your work

Best regards

extra

[extralismizer]

Just for your information: Everything worked fine! Database is at the requested version and server is running

Thanks a lot! Guess this topic can be closed...

[evilatwow]

Glad it worked out perfectly for you too! Enjoy the TBC content!

And don't worry about that topic; it will silently die anyway when we stop posting