[in 330][SQL] update characters.data from 3.2.2 to 3.3.0

[Auntie Mangos]

Hello, I just want to share you SQL query to convert characters from 3.2.2 to 3.3.0

I hope it's accepted as welcomed (in dev)

http://getmangos.eu/community/showpost.php?p=95670&postcount=5

Please use it with caution and report any bugs (supposed in quest menu...)

UPDATE characters SET data = REPLACE(data,'  ',' ');
UPDATE characters SET data = CONCAT(TRIM(data),' ');

UPDATE `characters` SET `data` = CONCAT(
   SUBSTRING_INDEX(`data`, ' ', 161 + 1), ' ',
   '0 ',
   SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', 165 + 1), ' ', -165 + 162 - 1), ' ',
   '0 ',
   SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', 165 + 4 + 1), ' ', -165 - 4 + 162 + 4 - 1), ' ',
   '0 ',
   SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', 165 + 4*2 + 1), ' ', -165 - 4*2 + 162 + 4*2 - 1), ' ',
   '0 ',
   SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', 165 + 4*3 + 1), ' ', -165 - 4*3 + 162 + 4*3 - 1), ' ',
   '0 ',
   SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', 165 + 4*4 + 1), ' ', -165 - 4*4 + 162 + 4*4 - 1), ' ',
   '0 ',
   SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', 165 + 4*5 + 1), ' ', -165 - 4*5 + 162 + 4*5 - 1), ' ',
   '0 ',
   SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', 165 + 4*6 + 1), ' ', -165 - 4*6 + 162 + 4*6 - 1), ' ',
   '0 ',
   SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', 165 + 4*7 + 1), ' ', -165 - 4*7 + 162 + 4*7 - 1), ' ',
   '0 ',
   SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', 165 + 4*8 + 1), ' ', -165 - 4*8 + 162 + 4*8 - 1), ' ',
   '0 ',
   SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', 165 + 4*9 + 1), ' ', -165 - 4*9 + 162 + 4*9 - 1), ' ',
   '0 ',
   SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', 165 + 4*10 + 1), ' ', -165 - 4*10 + 162 + 4*10 - 1), ' ',
   '0 ',
   SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', 165 + 4*11 + 1), ' ', -165 - 4*11 + 162 + 4*11 - 1), ' ',
   '0 ',
   SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', 165 + 4*12 + 1), ' ', -165 - 4*12 + 162 + 4*12 - 1), ' ',
   '0 ',
   SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', 165 + 4*13 + 1), ' ', -165 - 4*13 + 162 + 4*13 - 1), ' ',
   '0 ',
   SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', 165 + 4*14 + 1), ' ', -165 - 4*14 + 162 + 4*14 - 1), ' ',
   '0 ',
   SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', 165 + 4*15 + 1), ' ', -165 - 4*15 + 162 + 4*15 - 1), ' ',
   '0 ',
   SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', 165 + 4*16 + 1), ' ', -165 - 4*16 + 162 + 4*16 - 1), ' ',
   '0 ',
   SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', 165 + 4*17 + 1), ' ', -165 - 4*17 + 162 + 4*17 - 1), ' ',
   '0 ',
   SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', 165 + 4*18 + 1), ' ', -165 - 4*18 + 162 + 4*18 - 1), ' ',
   '0 ',
   SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', 165 + 4*19 + 1), ' ', -165 - 4*19 + 162 + 4*19 - 1), ' ',
   '0 ',
   SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', 165 + 4*20 + 1), ' ', -165 - 4*20 + 162 + 4*20 - 1), ' ',
   '0 ',
   SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', 165 + 4*21 + 1), ' ', -165 - 4*21 + 162 + 4*21 - 1), ' ',
   '0 ',
   SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', 165 + 4*22 + 1), ' ', -165 - 4*22 + 162 + 4*22 - 1), ' ',
   '0 ',
   SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', 165 + 4*23 + 1), ' ', -165 - 4*23 + 162 + 4*23 - 1), ' ',
   '0 ',
   SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', 298 + 1), ' ', -298 + 162 + 4*24 - 1), ' ',
   '0 ',
   SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ', 1300 + 1), ' ', -1300 + 299 - 1), ' '    
   )
WHERE length(SUBSTRING_INDEX(data, ' ', 1300)) < length(data) and length(SUBSTRING_INDEX(data, ' ', 1300+1)) >= length(data);

UPDATE characters SET data = REPLACE(data,'  ',' ');
UPDATE characters SET data = CONCAT(TRIM(data),' ');

[shinra]

Thankyou for posting Do you have any issues with guild bank? I seem to get major issues when adding items into the bank

[guillaumederval]

It's a core bug, it's not in the db.

[djmagma59]

hum and for what a new character with new guild dont have this bug?

i'm sure one data is the cause but where ^^

[Christian Schamara]

maybe we can even do something to work with 3.3.0=) you can arrange, please email me if you want to help

[Shyke]

Any news on this?

The present copy breaks guild banks and a few other things.

[yad02]

I can not really find an issue...

If I can get data field from 2 characters with same "history and bank guild", I could looking that is added between 3.2.2 and 3.3.0

(So I need a 3.2.2 character and a 3.3.0 character (same class race skin etc...) data field with bank guild enable)

[Shyke]

http://pastebin.com/m5528b9ae

Your requested data can be found there.

[yad02]

Can you tell me more about "few other things." ?

(Bank guild doesn't be in updatefields...)

[DJNefast]

there is a bug with quests, can you correct the pb ? My players doesn't have all quest, quest appears etc... it this a pb of database ?

[yad02]

yes, something has changed with quests

they are 25 new updatefields for quest + 1 is "pad"

[DJNefast]

it's a problem with field database or core packets ?

EDIT: partially resolved with modify sources. Now only quest status is bugued.

[yad02]

About quests, I think it's a updatefield problem

About bank guild I think it's not related

[DJNefast]

bank guild its a packet probleme, there is a new uint32(0);

i've resolved the problem

[Morphau]

i've resolved the problem

How ?

Can you tell us how please ?

Sorry for my english ><

[DJNefast]

search in item show where add the data << uint32(0); gl & HF

[djmagma59]

it's already fixed by http://github.com/mangos/mangos/commit/11c3eb26a4969044933a0c93fed81e34a9d3ed7f

[kalometro]

Exelent !! Thanks

[VladimirMangos]

Added to 330 branch. Thank you