Finding Cloned Items in Metin2 Server using MySQL Script

[hpgalvao 7]

dae guys!

I'll show you a cool MySQL script that can search for clone items in your server's database. This script will help you find and remove any clone items.

* just arms with magic damager and natural damanger!

WITH itens AS (
SELECT *,
md5(CONCAT_WS(',',attrtype0, attrvalue0, attrtype1, attrvalue1, attrtype2, attrvalue2, attrtype3, attrvalue3, attrtype4, attrvalue4)) chaveunica
FROM player.item
WHERE attrvalue2>0 AND attrvalue3>0 
AND `window` not IN ('SAFEBOX','MALL') 
AND attrtype0 IN (71,72)
),

duplicados AS (
SELECT 
chaveunica,COUNT(*) clones, attrtype0, attrvalue0, attrtype1, attrvalue1, attrtype2, attrvalue2, attrtype3, attrvalue3, attrtype4, attrvalue4 
FROM itens 
GROUP BY attrtype0, attrvalue0, attrtype1, attrvalue1, attrtype2, attrvalue2, attrtype3, attrvalue3, attrtype4, attrvalue4
HAVING COUNT(*)>1
)
,donos AS (
SELECT 
a.login,p.last_play,p.name nome,
ii.empire,g.name guilda,gm.is_general,pp.locale_name,i.chaveunica hash_,d.clones iguais,
i.* FROM itens i 
LEFT JOIN player.item_proto pp ON pp.vnum=i.vnum
LEFT JOIN player.player p ON p.id=i.owner_id
LEFT JOIN duplicados d ON d.chaveunica = i.chaveunica
LEFT JOIN player.guild_member gm ON gm.pid = p.id
LEFT JOIN player.guild g ON g.id = gm.guild_id
LEFT JOIN `account`.account a ON a.id = p.account_id
LEFT JOIN player.player_index ii ON ii.id = a.id
WHERE i.chaveunica IN (SELECT chaveunica FROM duplicados)
)
SELECT * FROM donos;

AND attrtype0 IN (71,72) is the line that specifies the type of item you're searching for.

good lock!

 

[Mafuyu 42]

everything in spanish dude cmon

[PetePeter 32]

56 minutes ago, Mafuyu said:

everything in spanish dude cmon

WITH items AS (
SELECT *,
md5(CONCAT_WS(',',attrtype0, attrvalue0, attrtype1, attrvalue1, attrtype2, attrvalue2, attrtype3, attrvalue3, attrtype4, attrvalue4)) unique_key
FROM player.item
WHERE attrvalue2>0 AND attrvalue3>0 
AND `window` not IN ('SAFEBOX','MALL') 
AND attrtype0 IN (71,72)
),

duplicates AS (
SELECT 
unique_key, COUNT(*) clones
FROM items 
GROUP BY unique_key
HAVING COUNT(*)>1
),

owners AS (
SELECT 
a.login, p.last_play, p.name player_name,
ii.empire, g.name guild, gm.is_general, pp.locale_name, i.unique_key hash_, d.clones duplicates,
i.* 
FROM items i 
INNER JOIN player.item_proto pp ON pp.vnum = i.vnum
INNER JOIN player.player p ON p.id = i.owner_id
INNER JOIN duplicates d ON d.unique_key = i.unique_key
INNER JOIN player.guild_member gm ON gm.pid = p.id
INNER JOIN player.guild g ON g.id = gm.guild_id
INNER JOIN `account`.account a ON a.id = p.account_id
INNER JOIN player.player_index ii ON ii.id = a.id
)

SELECT * FROM owners;

 

 

But I suggest something like that instead:

 

SELECT 
a.login, p.last_play, p.name player_name,
ii.empire, g.name guild, gm.is_general, pp.locale_name, i.unique_key hash_, d.clones duplicates,
i.* 
FROM (
  SELECT *,
  md5(CONCAT_WS(',', attrtype0, attrvalue0, attrtype1, attrvalue1, attrtype2, attrvalue2, attrtype3, attrvalue3, attrtype4, attrvalue4)) unique_key
  FROM player.item
  WHERE attrvalue2 > 0 AND attrvalue3 > 0 
  AND `window` NOT IN ('SAFEBOX', 'MALL') 
  AND attrtype0 IN (71, 72)
) i
INNER JOIN player.item_proto pp ON pp.vnum = i.vnum
INNER JOIN player.player p ON p.id = i.owner_id
INNER JOIN (
  SELECT unique_key, COUNT(*) clones
  FROM (
    SELECT 
    md5(CONCAT_WS(',', attrtype0, attrvalue0, attrtype1, attrvalue1, attrtype2, attrvalue2, attrtype3, attrvalue3, attrtype4, attrvalue4)) unique_key
    FROM player.item
    WHERE attrvalue2 > 0 AND attrvalue3 > 0 
    AND `window` NOT IN ('SAFEBOX', 'MALL') 
    AND attrtype0 IN (71, 72)
  ) tmp
  GROUP BY unique_key
  HAVING COUNT(*) > 1
) d ON d.unique_key = i.unique_key
INNER JOIN player.guild_member gm ON gm.pid = p.id
INNER JOIN player.guild g ON g.id = gm.guild_id
INNER JOIN `account`.account a ON a.id = p.account_id
INNER JOIN player.player_index ii ON ii.id = a.id;

 

Edited May 6 by PetePeter