Why do node IDs increment by more than one?

Issue

Can you explain why node IDs increment by more than one?

Resolution

Drupal, by default, auto-increments the node ID (nid) and other IDs by one digit for each piece of content you create. You can see this if you visit your site by the node ID path:

http://www.example.com/node/1http://www.example.com/node/2http://www.example.com/node/3

However, sites that use database replication as a backup strategy often will have non-sequential ids, meaning that you could see nids jump by two or more digits.

http://www.example.com/node/10http://www.example.com/node/15http://www.example.com/node/20

The MySQL auto_increment_increment variable determines how many digits to jump for each successive auto_increment value. By default, Acquia Cloud customers see numbers jump by five digits for each new node.

The second database server differs by setting the auto_increment_offset to a different number. Acquia Cloud Enterprise database servers use offset values of 1 and 2 for the active and passive database servers.

This behavior is completely normal. Databases that are set up to fail over in the event of a problem are often set up to skip node IDs. When (or if) the active database switches, the secondary database server has an auto_increment_offset value of 2. Therefore, nodes are assigned numbers that cannot be used by the other database servers.

This 'jump' in an ID number is not limited to node IDs. Any column that uses auto-increment will experience this type of non-sequential ID numbering.

In the post, Module authors: In SQL, don't assume INCREMENT(1) == 2:

Never assume an INSERT query into an auto-increment column will produce a known value. If you need a value in an auto-increment column to be a fixed value, use the db_last_insert_id() function (in Drupal 6) or the return value of $query->execute() (in Drupal 7) to access the actual auto-increment value inserted, and update it explicitly.

In short, be careful in making assumptions about auto-incrementing values.

Problems with auto-increment

On rare occasions, we have seen instances where auto-increment code appears to have not worked properly, or has reused numbers. This is generally caused by code interacting with the auto-increment table, not the auto-increment function itself.

If you believe your auto-increment is reusing numbers, there are a few things you can look for:

• A record was updated, not deleted

  This suggests that a record was overwritten instead of inserted. This is a code issue as new records should never use UPDATE or REPLACE.

• Manually inserted auto_increment values

  You can manually insert auto_increment values as long as they don't already exist in the table. For example, a row with the value 1284381 was deleted and then reinserted, the system would accept that increment. Auto_increment only kicks in if the column is not specified on INSERT or is inserted as NULL or 0. This is also a code issue.

• The entire table was deleted, truncated or even dropped and created

  The auto_increment will get reset on a truncate or drop/create, and sometimes on a delete of the entire table.