Why do IDs of deleted nodes remain in the database?

[Luxus]

Each node has a unique ID assigned to it, but if I delete a node I expect the node ID to be deleted too, but it's not. For example if you have 10 nodes and delete all of them, then create another node, this node will get the node ID of 11.

Any reason for this?

 

[Chris D]

This is just how MySQL works.

The node ID is an auto increment value. The index of these values remain, even after its associated record is removed.

I believe the only way to resolve this is to somehow remove the index.

I believe truncating a table will do this (as well as removing all the data from it) so not really useful.

 

[Brandon Sheley]

they are likely UID's
http://en.wikipedia.org/wiki/Unique_identifier

 

[Chris D]

Yeah basically.

MySQL docs describes the AUTO_INCREMENT attribute as "a unique identity for new rows".

So for it to remain unique it mustn't be reused.

 

[HWS]

If you delete a node, not only it's ID will stay in the database...

 

[Syndol]

If you are absolutely sure that your last node ID was not referenced anywhere, you could reset the auto increment thus:
ALTER TABLE xf_node AUTO_INCREMENT = X;
Where X will be the ID of the next node you insert.

 

[HWS]

If you are absolutely sure that your last node ID was not referenced anywhere, you could reset the auto increment thus:
ALTER TABLE xf_node AUTO_INCREMENT = X;
Where X will be the ID of the next node you insert.

IF you do that you will wonder why so many threads appear in your brand new forum node after you set it up.

 

[Syndol]

IF you do that you will wonder why so many threads appear in your brand new forum node after you set it up.

Yes we know about that bug
Hence:

If you are absolutely sure that your last node ID was not referenced anywhere...