Duplicate Keys: Repairing The Sequences Table

Photo of Greg Harvey
Fri, 2008-05-02 16:39By greg

So normal service resumes. Well, just about, at any rate.

Just a quick entry today about the 'sequences' table in the Drupal database. If you've ever seen this sort of error and not known what to do, then you'll find this post extremely useful:


Duplicate entry '4' for key

Drupal doesn't use auto increment to increment the key id field in the tables for it's various bits and pieces. Instead it manages them itself. How? It stores the id of the last item created in a table called 'sequences'. Let's look at this website for a practical example. The 'sequences' table looks like this:

mysql> SELECT * FROM sequences;
+--------------------+----+
| name | id |
+--------------------+----+
| users_uid | 1 |
| menu_mid | 74 |
| node_nid | 34 |
| node_revisions_vid | 34 |
| vocabulary_vid | 1 |
| term_data_tid | 6 |
| view_view_vid | 1 |
+--------------------+----+
7 rows in set (0.00 sec)

To demonstrate how this works, let's take a peek at the last row in the 'node' table:

mysql> SELECT nid FROM node ORDER BY nid DESC LIMIT 1;
+-----+
| nid |
+-----+
| 34 |
+-----+
1 row in set (0.00 sec)

See how 'sequences.node_nid' and 'node.nid' match? Next time you add a node, Drupal checks the 'sequences' table and this is how it knows the next node id should be 35.

So if you ever see those nasty duplicate key warnings, chances are something has reset the designated row in the 'sequences' table and sent Drupal's numbering system haywire. Last time I saw this was just today - I uninstalled the Category module and it rather usefully set 'sequences.term_data_tid' back to 0. Chaos! How did I repair it? Simple:

mysql> SELECT tid FROM term_data ORDER BY tid DESC LIMIT 1;
+-----+
| tid |
+-----+
| 6 |
+-----+
1 row in set (0.00 sec)

mysql> UPDATE sequences SET id = 6 WHERE name = 'term_data_tid';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

Job done!