hpgalvao 10 Posted May 1, 2023 Share Posted May 1, 2023 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! 2 Link to comment https://metin2.dev/topic/30777-finding-cloned-items-in-metin2-server-using-mysql-script/ Share on other sites More sharing options...
Mafuyu 54 Posted May 6, 2023 Share Posted May 6, 2023 everything in spanish dude cmon Link to comment https://metin2.dev/topic/30777-finding-cloned-items-in-metin2-server-using-mysql-script/#findComment-157441 Share on other sites More sharing options...
PetePeter 38 Posted May 6, 2023 Share Posted May 6, 2023 (edited) 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 2 1 Link to comment https://metin2.dev/topic/30777-finding-cloned-items-in-metin2-server-using-mysql-script/#findComment-157442 Share on other sites More sharing options...
hpgalvao 10 Posted September 21, 2023 Author Share Posted September 21, 2023 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! Link to comment https://metin2.dev/topic/30777-finding-cloned-items-in-metin2-server-using-mysql-script/#findComment-159704 Share on other sites More sharing options...
Recommended Posts
Don't use any images from : imgur, turkmmop, freakgamers, inforge, hizliresim... Or your content will be deleted without notice...
Use : https://metin2.download/media/add/
Please sign in to comment
You will be able to leave a comment after signing in
Sign In Now