[fix][7084][bug] 3.0.3 convert item with multi gems

[Philkiller]

Hi, they are a problem with item with multi gems, the first gems has good place but the next is not in the gems emplacement, he placed after the case.

Rev:6939

[Philkiller]

i'm alone ?

[kozelo]

Confirmed

You are not alone.

[NoFantasy]

I can confirm there is a problem with gems, but you really need to explain more in detail for it to make any sense.

If you are not able to, here are some:

Case: character from 2.4.3 exist, having entries in item_instance. Run 2008_12_22_19_characters_item_instance.sql will update the data fields for items. This seem to be ok, however:

After logging in, your itemX (let's say item_template has 2 sockets, socket1=red, socket2=yellow) will have socket1 filled with the red, socket2 empty and socket3 filled with the yellow. ItemX does not really have a socket3, but in both database and client side this will still show _and_ be applied to the stats.

Also it should be noted that you are able to fill socket2 wih a new gem, without loosing the one in socket3.

[NoFantasy]

Grab your backup and try replace the related SQL in 2008_12_22_19_characters_item_instance.sql with:

UPDATE item_instance SET data= CONCAT(
 SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',54),' ',-54),' 0 0 0 ',
 SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',60),' ',-60+54),' 0 ')
WHERE SUBSTRING_INDEX(data,' ',60) = data AND SUBSTRING_INDEX(data,' ',60-1) <> data;

I can not verify it's correct, however i see for my test characters the problem is gone regarding gems/sockets. Some more tests will perhaps tell what would be a better way to solve it.

[SharkPD]

Grab your backup and try replace the related SQL in 2008_12_22_19_characters_item_instance.sql with:

UPDATE item_instance SET data= CONCAT(
 SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',54),' ',-54),' 0 0 0 ',
 SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',60),' ',-60+54),' 0 ')
WHERE SUBSTRING_INDEX(data,' ',60) = data AND SUBSTRING_INDEX(data,' ',60-1) <> data;

I can not verify it's correct, however i see for my test characters the problem is gone regarding gems/sockets. Some more tests will perhaps tell what would be a better way to solve it.

Is not working with my revision: UDB 0.11. revision 7072

[NoFantasy]

...and what does "Is not working" mean? Socket/gems still messed up? Explain what you did. If you already ran the original SQL in 2008_12_22_19_characters_item_instance.sql then of course the SQL i posted "Is not working". That's why i said "grab your backup"

[Athor]

thank you NoFantasy it works fine

did on backup from core 6888 - last patch included 2008_12_03_01_character_guild_member.sql

step 1)

* run core 6888

* go ingame

* create hunter char

* learn Mail .learn 8737

* .additemset 651 http://www.wowhead.com/?itemset=651

* add gems

2 red i33133 i33134

2 blue i34256 i33137

2 yellow i33143 i33144

1 meta 25901

*put in socket, log out, restart server still on 6888 no pb, gems correctly show etc...

step 2)

*upgrade char db to 7067_03_characters_character_spell with normal core sql files

*run it with core 7067_03_sd2_838 gems messed up and not show in correct socket etc... bug confirmed

reistall fresh char db backup & redo full step 1)

on step 2) edit 2008_12_22_19_characters_item_instance.sql and replace :

ALTER TABLE character_db_version CHANGE COLUMN required_2008_12_22_18_characters_characters required_2008_12_22_19_characters_item_instance bit;

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

UPDATE item_instance SET data= CONCAT(
 SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',30),' ',-30),' 0 0 0 ',
 SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',60),' ',-60+30),' 0 ')
WHERE SUBSTRING_INDEX(data,' ',60) = data AND SUBSTRING_INDEX(data,' ',60-1) <> data;

UPDATE item_instance SET data= CONCAT(
 SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',30),' ',-30),' 0 0 0 ',
 SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',60),' ',-60+30),' 0 ',
 SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',134),' ',-134+60))
WHERE SUBSTRING_INDEX(data,' ',134) = data AND SUBSTRING_INDEX(data,' ',134-1) <> data;

by

ALTER TABLE character_db_version CHANGE COLUMN required_2008_12_22_18_characters_characters required_2008_12_22_19_characters_item_instance bit;

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

UPDATE item_instance SET data= CONCAT(
 SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',54),' ',-54),' 0 0 0 ',
 SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',60),' ',-60+54),' 0 ')
WHERE SUBSTRING_INDEX(data,' ',60) = data AND SUBSTRING_INDEX(data,' ',60-1) <> data;

UPDATE item_instance SET data= CONCAT(
 SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',30),' ',-30),' 0 0 0 ',
 SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',60),' ',-60+30),' 0 ',
 SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',134),' ',-134+60))
WHERE SUBSTRING_INDEX(data,' ',134) = data AND SUBSTRING_INDEX(data,' ',134-1) <> data;

*run it with core 7067_03_sd2_838 gems correctly match with socket.

[Alex]

If someone has no backup from 2.4.3, he may try:

UPDATE `item_instance` 
SET `data` = CONCAT(
 SUBSTRING_INDEX(SUBSTRING_INDEX(`data`,' ',31),' ',-31), ' ',
 SUBSTRING_INDEX(SUBSTRING_INDEX(`data`,' ',56),' ',-56+34), ' 0 0 0 ',
 SUBSTRING_INDEX(SUBSTRING_INDEX(`data`,' ',64),' ',-64+56), ' ')
WHERE SUBSTRING_INDEX(data,' ',64) = data AND SUBSTRING_INDEX(data,' ',64-1) <> data;

UPDATE `item_instance` 
SET `data` = CONCAT(
 SUBSTRING_INDEX(SUBSTRING_INDEX(`data`,' ',31),' ',-31), ' ',
 SUBSTRING_INDEX(SUBSTRING_INDEX(`data`,' ',56),' ',-56+34), ' 0 0 0 ',
 SUBSTRING_INDEX(SUBSTRING_INDEX(`data`,' ',138),' ',-138+56), ' ')
WHERE SUBSTRING_INDEX(data,' ',138) = data AND SUBSTRING_INDEX(data,' ',138-1) <> data;

This will destroy existing socket #2 contents though (replacing them with old socket #2 contents that are in socket #3 wrongly) and may do some damage to enchant #12. Backup before testing, worked for me.

[VladimirMangos]

Fix applied to conversion query in [7984]

Thanks to author for fix and testers

[nanounico]

this

UPDATE item_instance SET data = REPLACE(data,' ',' ');

UPDATE item_instance SET data = CONCAT(TRIM(data),' ');

UPDATE item_instance SET data= CONCAT(

SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',54),' ',-54),' 0 0 0 ',

SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',60),' ',-60+54),' 0 ')

WHERE SUBSTRING_INDEX(data,' ',60) = data AND SUBSTRING_INDEX(data,' ',60-1) <> data;

UPDATE item_instance SET data= CONCAT(

SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',30),' ',-30),' 0 0 0 ',

SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',60),' ',-60+30),' 0 ',

SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',134),' ',-134+60))

WHERE SUBSTRING_INDEX(data,' ',134) = data AND SUBSTRING_INDEX(data,' ',134-1) <> data;

dont work for me look.

My Char DB is from 2.4.3

maybe whith this. but i dont know

UPDATE `item_instance`

SET `data` = CONCAT(

SUBSTRING_INDEX(SUBSTRING_INDEX(`data`,' ',31),' ',-31), ' ',

SUBSTRING_INDEX(SUBSTRING_INDEX(`data`,' ',56),' ',-56+34), ' 0 0 0 ',

SUBSTRING_INDEX(SUBSTRING_INDEX(`data`,' ',64),' ',-64+56), ' ')

WHERE SUBSTRING_INDEX(data,' ',64) = data AND SUBSTRING_INDEX(data,' ',64-1) <> data;

UPDATE `item_instance`

SET `data` = CONCAT(

SUBSTRING_INDEX(SUBSTRING_INDEX(`data`,' ',31),' ',-31), ' ',

SUBSTRING_INDEX(SUBSTRING_INDEX(`data`,' ',56),' ',-56+34), ' 0 0 0 ',

SUBSTRING_INDEX(SUBSTRING_INDEX(`data`,' ',138),' ',-138+56), ' ')

WHERE SUBSTRING_INDEX(data,' ',138) = data AND SUBSTRING_INDEX(data,' ',138-1) <> data;

[Rhyll]

Well I didn't have any problems.

[Dietrich]

Tried Alex's sql fix as I don't have a 2.4.3 backup and it ruins up the data field of the items : / Any other proposals of fixes?

[shnappie]

migrated about a week ago from 2.4.3 release of Mangos 6766 to 3.0.9 release of Mangos 7681.

Applied all updates on the characters database.. but now turns out that some items have to many gems like discribed above.... is there any fix, or clearing of all gems, possible. Since the solution posted by Alex does not work.

[Mogale]

Can also confirm the problem, shnappie.

[shnappie]

fix :

update item_instance
set `data` = CONCAT(
 SUBSTRING_INDEX(SUBSTRING_INDEX(`data`,' ',31),' ',-31), ' ',
 SUBSTRING_INDEX(SUBSTRING_INDEX(`data`,' ',56),' ',-56+34), ' 0 0 0 ',
 SUBSTRING_INDEX(SUBSTRING_INDEX(`data`,' ',64),' ',-64+56), ' ')
WHERE SUBSTRING_INDEX(data,' ',64) = data AND SUBSTRING_INDEX(data,' ',64-1) <> data
and ((SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',31), ' ', -1) <> 0) or (SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',35), ' ', -1) <> 0) or (SUBSTRING_INDEX(SUBSTRING_INDEX(data,' ',41), ' ', -1) <> 0))

repairs items who have "ghost"gems on 3.0.9