GUID Reorder

Hi,

On our servers, we are trying to reorder item’s GUID for players, because with the auto-incrementation the value of Max-GUID is more than that the client support.

For the moment, at WorldServer loading, we have this code :

QueryResult itemsGUID = CharacterDatabase.PQuery(“SELECT guid FROM item_instance ORDER BY GUID”);
if (itemsGUID)
{
uint32 predGUID = 0;

do // Assuming GUIDs are ordered.
{
    Field* item = itemsGUID->Fetch();

    if (item[0].GetUInt32() - predGUID > 1) // More than one Guid of difference, so there are some free Guids !
    {
        for (uint32 i = predGUID + 1; i < item[0].GetUInt32(); ++i)
            freeItemsGUID.add(i);
    }

    predGUID = item[0].GetUInt32();
}
while (itemsGUID->NextRow());

}

freeItemsGUID is declared like that :

ACE_Based::LockedQueue<uint32, ACE_Thread_Mutex> freeItemsGUID;

After that, at each player login we do that :

// Convert all players items to lower guid
QueryResult items = CharacterDatabase.PQuery(“SELECT guid FROM item_instance WHERE owner_guid = %u”, GUID_LOPART(playerGuid));
if (items)
{
bool reordered = false;

do
{
    reordered = true;
    Field* item = items->Fetch();
    uint32 guid = item[0].GetUInt32();
    uint32 newGuid = sWorld->GetNextFreeItemGuid();

    if (newGuid && guid > 0x1312D00)
    {
        CharacterDatabase.PQuery("DELETE FROM auctionhouse WHERE itemguid = %u", newGuid);
        CharacterDatabase.PQuery("DELETE FROM character_gifts WHERE item_guid = %u", newGuid);
        CharacterDatabase.PQuery("DELETE FROM guild_bank_item WHERE item_guid = %u", newGuid);
        CharacterDatabase.PQuery("DELETE FROM character_inventory WHERE item = %u", newGuid);
        CharacterDatabase.PQuery("DELETE FROM character_inventory WHERE bag = %u", newGuid);
        CharacterDatabase.PQuery("DELETE FROM item_instance WHERE guid = %u", newGuid);
        CharacterDatabase.PQuery("DELETE FROM item_loot_items WHERE container_id = %u", newGuid);
        CharacterDatabase.PQuery("DELETE FROM item_loot_money WHERE container_id = %u", newGuid);
        CharacterDatabase.PQuery("DELETE FROM item_refund_instance WHERE item_guid = %u", newGuid);
        CharacterDatabase.PQuery("DELETE FROM item_soulbound_trade_data WHERE itemGuid = %u", newGuid);
        CharacterDatabase.PQuery("DELETE FROM mail_items WHERE item_guid = %u", newGuid);
        CharacterDatabase.PQuery("DELETE FROM petition WHERE charterguid = %u", newGuid);

        CharacterDatabase.PQuery("UPDATE auctionhouse set itemguid = %u WHERE itemguid = %u", newGuid, guid);
        CharacterDatabase.PQuery("UPDATE character_gifts set item_guid = %u WHERE item_guid = %u", newGuid, guid);
        CharacterDatabase.PQuery("UPDATE character_inventory set item = %u WHERE item = %u", newGuid, guid);
        CharacterDatabase.PQuery("UPDATE character_inventory set bag = %u WHERE bag = %u", newGuid, guid);
        CharacterDatabase.PQuery("UPDATE guild_bank_item set item_guid = %u WHERE item_guid = %u", newGuid, guid);
        CharacterDatabase.PQuery("UPDATE item_instance set guid = %u WHERE guid = %u", newGuid, guid);
        CharacterDatabase.PQuery("UPDATE item_loot_items set container_id = %u WHERE container_id = %u", newGuid, guid);
        CharacterDatabase.PQuery("UPDATE item_loot_money set container_id = %u WHERE container_id = %u", newGuid, guid);
        CharacterDatabase.PQuery("UPDATE item_refund_instance set item_guid = %u WHERE item_guid = %u", newGuid, guid);
        CharacterDatabase.PQuery("UPDATE item_soulbound_trade_data set itemGuid = %u WHERE itemGuid = %u", newGuid, guid);
        CharacterDatabase.PQuery("UPDATE mail_items set item_guid = %u WHERE item_guid = %u", newGuid, guid);
        CharacterDatabase.PQuery("UPDATE petition set charterguid = %u WHERE charterguid = %u", newGuid, guid);
    }
}
while (items->NextRow());

}

if (reordered)
{
KickPlayer(); // Freed access to other players
m_playerLoading = false;
return;
}
But we have some problems :

  • With direct PQuery, the reorder of a player, lasts ~1h, while that, nobody can connect to the server, so for approximatively 150.000 characters, it will take more than 17 years to reorder … ;

  • With PreparedStatements, the player is kicked before the end of the reorder, so queries are locked and other players are bloqued to char enum ;

  • With DELETE queries at WorldServer loading, in the case of we have 1.000.000 of free Guids in Database, the WorldServer will take 70 days to load ;

  • For each items, it takes ~7s for delete, ~3s for update, so ~10s per items, per characters.

PS : Sorry if my English is bad, I’m French.

French :

Je pense que dans tous les cas, c’est un problème de performance de côté DB. J’imagine que tu tournes sur MySQL.

Tu as aussi PostgreSQL, dont on m’a toujours dit bien plus performant que MySQL.

English :

I think that in all cases, it’s a matter of perf from MySQL.

You should try to use PostgreSQL.

http://www.trinitycore.org/f/topic/104-sql-authcharacters-database-cleanup/

We have found a other solution, use a map of <uint32, uint32>, each item GUID assigned to a “fake” guid beggining from 1.

We want to send the “fake” guid to the client, with this solution the principal problem will no longer appears … I guess.