1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Why do IDs of deleted nodes remain in the database?

Discussion in 'XenForo Questions and Support' started by Luxus, Nov 26, 2012.

  1. Luxus

    Luxus Well-Known Member

    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?
     
  2. Chris D

    Chris D XenForo Developer Staff Member

    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.
     
    Jake Bunce and Luxus like this.
  3. Brandon Sheley

    Brandon Sheley Well-Known Member

  4. Chris D

    Chris D XenForo Developer Staff Member

    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.
     
    Jake Bunce likes this.
  5. HWS

    HWS Well-Known Member

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

    Syndol Guest

    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.
     
    Jake Bunce likes this.
  7. HWS

    HWS Well-Known Member

    IF you do that you will wonder why so many threads appear in your brand new forum node after you set it up. :D
     
    Jake Bunce likes this.
  8. Syndol

    Syndol Guest

    Yes we know about that bug :censored:
    Hence:
     
    Jake Bunce and HWS like this.

Share This Page