That big select (multitable, group etc.) on my home PC, 29.000 items + 5000 depot items + 4000 house items:
0.0762 sec
Delete duplicated items from players table, 29000 items, 109 potential duplicates (in 'IN ( ... )'):
0.0525 sec
How to detect which items are cloned
- Thread starter LucasFerraz
- Start date
0.0762 sec
Delete duplicated items from players table, 29000 items, 109 potential duplicates (in 'IN ( ... )'):
0.0525 sec
But your PC has a good processor/ram or something? I want to make something that works for everyone
2x3GHz, 4GB ram, Intel and OCZ SSD discs, but I did test on XAMPP with default config. People with bigger databases can configure MySQL to work faster.
It's perfect! I tested it in a table with 16.000 items and it tooks 0.0388 seg
- - - Updated - - -
I've tested a new AntiDupe created by me, with queries from Gesior, and it has been working fine. It uses a new field in MySQL called 'serial', then when server starts it checks for duplicated serials (Thanks to Gesior for the query), then check for items with that serial and deletes it. Also, it creates a log at data/logs with info about the deleted item.
OP
OP
LucasFerraz
Systems Analyst
Gesior.pl can you please post the globalevent that check duplicated serials?
So i can track down disappearing items?
OP
OP
LucasFerraz
Systems Analyst
If its disappeared its not in database lol
I found one big problem. I did check in engine (0.4 rev 3777, but I think it's same in all versions):
otserv.cpp:
[CPP]if(!g_game.loadMap(g_config.getString(ConfigManager::MAP_NAME))) // load map from file AND ITEMS FROM DATABASE!
...
g_game.setGameState(GAMESTATE_INIT); // it executes 'startup' events[/CPP]
onStartup event can't remove items from table `tile_items` [it can remove it from database, but items are already in game and server will save them in database again], as they are already loaded
I think that code that removes duplicated items should be in PHP or other script language and placed in auto-restarter. Something like:
[BASH]#!/bin/bash
i=1
while [ $i -le 5000 ]
do
php -f removeDuplicated.php
./tfs
i=`expr $i + 1`
done
[/BASH]
and PHP script should be something like:
PHP:
<?PHP
$dsn = 'mysql:dbname=theforgottenserver;host=127.0.0.1';
$user = 'root';
$password = 'secretPassword';
try
{
$SQL = new PDO($dsn, $user, $password);
$result = $SQL->query('SELECT unitedItems.serial, COUNT(1) AS duplicatesCount FROM (SELECT substring(CONVERT(`attributes` USING latin1) , LOCATE(\'serial\', CONVERT(`attributes` USING latin1))+11, 29) AS serial FROM `player_items` WHERE LOCATE(\'serial\', CONVERT(`attributes` USING latin1)) != 0
UNION ALL
SELECT substring(CONVERT(`attributes` USING latin1) , LOCATE(\'serial\', CONVERT(`attributes` USING latin1))+11, 29) AS serial FROM `player_depotitems` WHERE LOCATE(\'serial\', CONVERT(`attributes` USING latin1)) != 0
UNION ALL
SELECT substring(CONVERT(`attributes` USING latin1) , LOCATE(\'serial\', CONVERT(`attributes` USING latin1))+11, 29) AS serial FROM `tile_items` WHERE LOCATE(\'serial\', CONVERT(`attributes` USING latin1)) != 0) unitedItems GROUP BY unitedItems.serial HAVING COUNT(1) > 1;')->fetchAll();
if(count($result) > 0) // there are some duplicated
{
$duplicatedSerials = array();
$duplicatedSerialsClear = array();
$duplicatedCount = 0;
foreach($result as $row)
{
$duplicatedSerials[] = $SQL->quote($row['serial']);
$duplicatedSerialsClear[] = $row['serial'];
$duplicatedCount += $row['duplicatesCount'];
}
$duplicatedString = implode(', ', $duplicatedSerials);
$SQL->query('DELETE FROM `player_items` WHERE LOCATE(\'serial\', CONVERT(`attributes` USING latin1)) != 0 AND substring(CONVERT(`attributes` USING latin1) , LOCATE(\'serial\', CONVERT(`attributes` USING latin1))+11, 29) IN (' . $duplicatedString . ');');
$SQL->query('DELETE FROM `player_depotitems` WHERE LOCATE(\'serial\', CONVERT(`attributes` USING latin1)) != 0 AND substring(CONVERT(`attributes` USING latin1) , LOCATE(\'serial\', CONVERT(`attributes` USING latin1))+11, 29) IN (' . $duplicatedString . ');');
$SQL->query('DELETE FROM `tile_items` WHERE LOCATE(\'serial\', CONVERT(`attributes` USING latin1)) != 0 AND substring(CONVERT(`attributes` USING latin1) , LOCATE(\'serial\', CONVERT(`attributes` USING latin1))+11, 29) IN (' . $duplicatedString . ');');
file_put_contents('data/log/duplicates.txt', "\n" . date(DATE_RFC822) . " - " . $duplicatedCount . " duplicated items found. Serials:\n" . implode("\n", $duplicatedSerialsClear) . "\n", FILE_APPEND);
}
else
{
file_put_contents('data/log/duplicates.txt', "\n" . date(DATE_RFC822) . " - 0 duplicated items found.\n", FILE_APPEND);
}
}
catch (PDOException $e)
{
echo 'Connection failed: ' . $e->getMessage();
}
?>data/log/duplicates.txt must be writeable file.
EDIT:
NO. There is no tree/array with all players items and depot items. Of course you can make C++ script to load from database all players items to some variable, but it can be tooo big (+very slow). Do not try to make better search engine then used in databases!
Last edited:
OP
OP
LucasFerraz
Systems Analyst
Duplicated itens must be deleted only if server is offline right?
OP
OP
LucasFerraz
Systems Analyst
Isn't better do everything in C++ Before server loads?
OP
OP
LucasFerraz
Systems Analyst
OMG, Amazing, will u share it for free or paid?
Share please
OP
OP
LucasFerraz
Systems Analyst
I can help you testing if you want
It is much harder to track deleted items, as duplicated items both exist you can do check on them.Thank you Darkhaos & Gesior
You would have to add parameters to the removeItem function or we. to check for which reason it was removed. For example throwing it in bin, water or whether it was a lua call etc.