“creatures” table ‘item_instance’ guid go up to 4294967295
and players cant take any more items.
so what than??
is it core automaticli reset those numbers or what??
And how to reset countdown if i reogranize DB to pull back numbers?? how to reset countdown in game??
Sry for my bad English /emoticons/default_tongue.png
Currently there’s no in-core guid remap functionality available. Once the limit has been reached (highly unlikely) the core will automatically shut down. At this point you would want to look at gaps you have in the index range and remap them.
As Machiavelli said, it will just crash when it reaches that limit, but, seriously, not even the biggest server in the whole world will get to have ((2^31)-1)*2 items at the same time
You may want to read Machiavelli’s post.
The problem is not having that many items at the same time.
The problem is that there are lots of “gaps”, that is unused item GUIDs which cannot be re-used because of the incrementing nature of that field.
So if you have enough players, eventually you will reach the max value, unless as Machiavelli said you remap the GUIDs.
i dont think that the limit can be reached without own interventions
Each time any creature is made (snake traps’s snakes, someone resummons pet, mirror images) it’s increase in creature guid for currently running server.
If you add npc to world (.npc add) you get lastest guid, witch is next guid after last creature created and it’s saved to DB with this guid = gap.
If you consider that engineering dragon spam, then huge numbers are really easy to achieve
[CODE]“count(guid)”;“max(guid)”;“avg(guid)”
“1799”;“3171423”;“34641.4319”[/CODE]My creature table. As you can see- last added npc has guid 3kk over average
This is for older revision, but you will get the idea:
[CODE]DROP TABLE IF EXISTS creature_overflow_backup;
ALTER TABLE creature RENAME TO creature_overflow_backup;
CREATE TABLE creature LIKE creature_overflow_backup;
INSERT INTO creature (id, map, spawnMask, phaseMask, modelid, equipment_id, position_x, position_y, position_z, orientation, spawntimesecs, spawndist, currentwaypoint, curhealth, curmana, DeathState, MovementType, npcflag, unit_flags, dynamicflags) SELECT id, map, spawnMask, phaseMask, modelid, equipment_id, position_x, position_y, position_z, orientation, spawntimesecs, spawndist, currentwaypoint, curhealth, curmana, DeathState, MovementType, npcflag, unit_flags, dynamicflags FROM creature_overflow_backup;[/CODE]Server, of course, must be offline
P.S.
Should implement overflow protection: on server startup check max(guid) in all those tables (creature, item_instance, gameobject) and if it’s bigger than 0x0FFFFFFF or so, then pack it back together
Thing is, the requests to check for gaps are extremely slow to execute, and they’ll take more time to execute as the table expands.
Besides, the item_instance.guid is referenced in character_inventory.item
The simplest way would be to make a loop, but this would make the server lagging like hell at startup.
Problem is with guid design. Not blizzlike.
Anyways, why not merging character_inventory and item_instance ? Same would go for guild_bank_item … There, we would have the possibility to have more items in the game, since there’d be 4294967295 * 2 items in the database. More items, and away the fear of overflow.
SELECT max(guid) FROM creature/item_instance/gameobjectSuch query is not slow.
Of course, all needed tables need to be updated after guid reassignment
SELECT max(guid), count(guid) FROM xIf they are not equal, then there are gaps
This will only tell you the max. And yes, it is slow with huge tables.
Again, this only check for gaps.
I was talking about FINDING the gaps. A little more complicated than your little sql snippets /emoticons/default_tongue.png
[CODE]select max(guid) from item_instance; – 306354850
/* 0 rows affected, 1 rows found. Duration for 1 query: 0,063 sec. */
select count(guid) from item_instance; – 25069457
/* 0 rows affected, 1 rows found. Duration for 1 query: 4,337 sec. */
select avg(guid) from item_instance; – 240395608,5314
/* 0 rows affected, 1 rows found. Duration for 1 query: 5,570 sec. */[/CODE]As I said. Selecting max value of primary field is fast.
[CODE]if (max(guid) > 0x0FFFFFFF)
{
if (count(guid) < 0x0FFFFFFF)
{
drop table if exists item_instance_overflow_backup;
alter table item_instance rename to item_instance_overflow_backup;
create table item_instance like item_instance_overflow_backup;
insert into item_instance (all fields except guid) values select all fields except guid from item_instance_overflow_backup;
}
else
we are fucked.
}
else
all is well[/CODE]
The 0x0FFFFFFF could be something else (0x7FFFFFFF) to get as close as possible to 0xFFFFFFFF, yet not close enough)
This code: http://paste2.org/p/1680045 can be used to find the gaps, but its not optimized and can be pretty slow, anyhow, it works
I appreciate a lot
http://paste2.org/p/1680178 bit optimized version of subv’s php
Using a loop may crash the SQL server by making it treating too much requests.
Try this to get every unused GUID using TWO sql query:
[CODE]SELECT MAX(guid) FROM item_instance;
SELECT guid FROM (SELECT 1 AS guid) q1 WHERE NOT EXISTS (SELECT 1 FROM item_instance WHERE guid = 1) UNION ALL SELECT * FROM (SELECT guid + 1 FROM item_instance t WHERE NOT EXISTS (SELECT 1 FROM item_instance ti WHERE ti.guid = t.guid + 1) ORDER BY guid LIMIT @LIM) q2 ORDER BY guid LIMIT @LIM[/sql]
Replace @LIM by the value you get.
I personnaly did get:
(2527761 row(s)returned)
(17067 ms taken)
Which is everything but fast.
[/CODE]
One possible solution would be to add tables that hold previously used GUIDs based on entity:
guid_recycler_npcs
guid_recycler_items
guid_recycler_objects
Each time an entity is deleted and a GUID becomes available, it gets INSERT into the appropriate table
Each time an entity is created the core first tries to SELECT a GUID from the appropriate table. If a vaild GUID is returned, it is used in the creation of the entity and DELETE from the recycler table. If it comes back 0 then it just increments the max(guid) in the appropriate table and creates the entity.
Nice idea, probably the fastest one, but this’d double the quantity of queries =/
Yup! A good idea indeed. Well… if we can have a fast ordering doing this with 100 or more query’s instead of a slower one with only 1… i prefere the 1st option. /emoticons/default_wink.png
Note: creature/gameobject reordering SHOULD NOT change TDB spawns guids: