[GENERAL] Trivial question: item_instance guid overflow, the final death of a server?

[AlterEvo]

Hello everyone!

First of all, congrats for this great community!

I've followed the developing of mangos based cores for years and I've also collaborated to some private projects during these years. Based on AC wiki, mostly all tables of the char database are using a GUID INT(10), That, as stated here: http://www.bajb.net/2008/10/mysql-int1-or-int10/, can contain 4294967295 elements. I always asked to my self: Is it possible for such tables having an overflow of data?

There are some discussions on several forums talking about creature guid overflow: https://www.getmangos.eu/forums/topic/1895-efficient-guid-generation/?tab=comments#comment-51131

https://www.ownedcore.com/forums/world-of-warcraft/world-of-warcraft-emulator-servers/wow-emu-questions-requests/431702-creature-guid-overflow.html

However, I'm more afraid about the item_instance table instead. Because if you think about it, a single player could potentially create hundreds of thousands of item GUIDs inside that table in a single year of activity, and I'm not talking about the items that they own, but those ones that a player loots and then sells or destroys (creating huge GUID gaps inside the table itself).

A realistic case for medium-big international servers: Suppose that you've 2000 players online that are farming, they are looting and destroying 5 items each minute. So in a year:

2000 (average online players) x 5 (average items destroyed per player per minute) x 525600 (minutes in a year) = it's 5 billion ! OVERFLOW

You can play a bit with the numbers but it's still a realistic situation, so, 4 billions are not enough and your server with 2000 players is going to die after 1-2 years? Isn't it?

Am I missing something?

[Kitzunu]

I will copy my reply I gave in discord to here so it don't get lost :)

KitzunuToday at 4:58 PM
I dont know how the table works or what it does?
But if a player trades/sells/destroys, can't we delete the current entry in the table, and if trade add it to the new player?
"This table holds individual item instance information for all items currently equipped in some kind of character bag or bank, in auctionhouses, in guild banks or in mails."

If that description is what it is, then it seems useless to store data after an item is destroyed/sold or traded?

[AlterEvo]

@Kitzunu the GUID field is an auto_increment primary key, it means that when you create an item and you delete it later, the GUID of that item won't be reused again. These "gaps" are a known behaviour of auto_increment fields. That's why GUID + auto_increment shouldn't be used for user-generated content IMHO.

[AlterEvo]

the process of "filling" the gaps is called realignment but it can break associated data.

[BarbzYHOOL]

maybe check this and implement it globally for all guid auto increment tables?

#1820

[AlterEvo]

@BarbzYHOOL thank you for your reply :)

I think it's a good solution for certain cases, however, a GUID normally is not designed to be re-used for 2 reasons:

1. It should identify something with an UNIQUE way: for example, if you have any external dependency with such specific data (from another table, or a website, or a custom stuff etc..) the worst case should be that the resource "is not found". If you reuse it instead, external not-updated relations could definitely use not correct data of other people.

2. Eventually, but not suggested because of the point 1, the algorithm behind the GUID re-using must be well-engineered because of the INSERT query that must be always O(1) at GUID search (especially on frequently changed tables such as in char database).

It's weird that it's an INT(10) actually, should be a BIGINT or a VARCHAR, dunno if the client can support it or if is there any way to create a conversion from BIGINT <-> in-game GUID

[Yehonal]

I have a solution for it, I'll prepare the details and I'll show you them to discuss it together.

[Yehonal]

First of all, based on our code:

https://github.com/azerothcore/azerothcore-wotlk/blob/master/src/server/game/Entities/Object/ObjectDefines.h#L60

An in-game GUID is universal and used by all kind of entities and, as many of you already know, is an uint64 composed by 3 parts: high, middle and low part. The high part is used to identify the entity type (player,gobject,creature etc.), the mid part is the entry of the entity (used only for gobject), and the low part is the guid that we're currently saving to db, but it's essentially used in-game to globally identify an entity of the same kind.

For example, for an item owned by a player:

HIGH	MID	LOW
0x4000 (type: item)	0x0 (not used)	xxxxx (guid stored inside the db)

After some research I've noticed that the client doesn't save anything inside the cache about this GUID. You can check it here: https://wowdev.wiki/WDB

It means that the in-game GUID is used essentially to identify an entity between the temporary memory space of the client and of course of the server. But it doesn't mean that must be also the same GUID saved inside the DB.

The solution to any kind of overflow would be the usage of a GUID inside the DB that can be bigger that the one used by the server/client (BIG INT, VARCHAR etc.). Generally, an UUID/GUID should be a VARCHAR https://www.guidgenerator.com/ . In fact, all services that manage user-contents use a 128 bit GUID to identify all their resources (photo, users, documents etc.), such as google, facebook, firebase ...

This GUID saved inside the db can be easily associated to an uint32 low-guid generated at server runtime that is not needed to be stored anywhere.

And if you think about it, it makes sense and could be the blizzlike way to handle it, because of the following reasons:

1. the retail realms are running for 15 years and it's irrealistic to think that they are using a 32bit space to store billions of data they have stored since then. Of course, the @AlterEvo is another example inside the private context, however, I don't think it's so easy to reach that value in 1 or 2 years.

2. Considering that it's quite impossible having 4 billions of item/characters/creature etc. managed at runtime, and that blizzard restarts server once a week (?). 32 bit GUIDs are lighter than 128 bit, allowing to save space on network requests and inside the client memory (that is a 32 bit application and memory allocation higher than 32bit must be emulated)

That's why we can create something like this:

UUID (DB)	uin32Guid (server RAM)
91f628c65b104c3b9d7dd281c0fc21cf	1
dd9093846c114c98b86944ca2ea7cf9f	2

NOTES:

• changing the field type shouldn't break the previously saved GUID.
• we could remove the hack to reuse creature guid to avoid the overflow.

Let me know what you think about it :)

[AlterEvo]

Thank you for your reply! Very complete and satisfying :)

I've asked the same on cmangos forum (and on trinity too but the forum is private) and apparently the common solution is to "manually fix the GUIDs" periodically. @Warlockbugs said that on retail happens the same: cmangos/issues#2169 (comment)

However, thinking of external resource such as a web app (I.E. armory), if you cache the item GUIDs to speed up the website, or even worst, save them somewhere. The re-arranging of the db guids could break everything on those external apps.

If your solution really works @Yehonal, would be great!

[BarbzYHOOL]

i didn't get everything but did you suggest to store the guid in varchar??? What about speed?

[Yehonal]

in our case BIGINT maybe it's better, but VARCHAR is more common for GUID because generally they are alphanumeric values. However, you can use any kind of field type that is not affected by overflow, because in the end it will be associated to an uint32 value when loaded from the server.

[Yehonal]

i didn't get everything but did you suggest to store the guid in varchar??? What about speed?

Speed? What kind of speed? In game you won't see any differences because uint32 will be still used internally. About queries, you can have some drawbacks on:

1. SELECT by UUID, but in terms of nanosecond i guess and the selection query by ID normally is used only when you load a resource from DB the first time, then it will be stored in memory with the uint32 identifier.

2. when you create the UUID (it's not as fast as incrementing a number) but it's not a very frequent operation because you need to create it only when you've to store it into db, but it's async and doesn't affect the performances. However, in our case, we can use BIG INT and just go with auto_increment as usual

[BarbzYHOOL]

yes, so just change to big int, right?

[Yehonal]

Uhm, I deleted the @AlterEvo. Just noticed. I was editing it. Sorry @AlterEvo

However, he replied to @BarbzYHOOL saying that we should change the field and implement the previously explained feature inside the server ofc.

[BarbzYHOOL]

on an almost similar subject, for the account IDs, they are stored in "int unsigned" in mysql, but when the core tries to insert "-1", the function guid_lopart() turns that -1 into a 16777215

Now let's have a look at https://dev.mysql.com/doc/refman/8.0/en/integer-types.html
That number 16777215 is equal to the mediumint limit. So the account limit seems to be mediumint in the CPP and int in MYSQL (which will never happen because of the cpp).
Mediumint SIGNED limit being 8 millions i don't know what should we do, hence why I post here and hope someone can find the right thing to do