Finding Cloned Items in Metin2 Server using MySQL Script

[hpgalvao 8]

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 49]

everything in spanish dude cmon

[PetePeter 36]

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, 2023 by PetePeter

[hpgalvao 8]

That's great! but using "with" in your code can be a convenient approach when you want to vary the SQL query to retrieve different information from a database. This allows you to keep the code more flexible and adaptable to your needs in different situations. If you have more questions or need assistance with specific code, please feel free to ask. I'm here to help!