The S-Files: When node_load won't load, and the anonymous user has vanished

Tech Support Case Studies Use these two SQL commands to repair your Drupal 6 site to restore the anonymous user database record:
INSERT INTO users (name, mail) VALUES ('', '');
UPDATE users SET uid=0 WHERE name='';
The symptoms you'll see that tip you off that this is needed:
  1. node_load() is not loading nodes that you can prove are in the node table.
  2. node_delete() is not deleting nodes that you can prove are in the node table.
  3. The query SELECT * FROM users WHERE uid=0; returns zero rows.
The reason you have to INSERT INTO and then UPDATE is because of the autoincrement functionality on the users table. If you don't believe me, try this experiment:
# delete user 0
mysql> DELETE FROM users WHERE uid=0;
Query OK, 1 row affected (0.00 sec)

# try to restore user 0 directly
mysql> INSERT INTO users (uid, name, mail) VALUES (0, '', '');
Query OK, 1 row affected (0.00 sec)

# see that it didn't work
mysql> SELECT uid FROM users WHERE name='';
+-------+
| uid   |
+-------+
| 19611 |
+-------+
Note that if you get a Duplicate entry error like the following, you need to delete the spurious rows prior to running the corrective queries I list at the beginning of the post.
# If you see this....
mysql> INSERT INTO users (name, mail) VALUES ('', '');
ERROR 1062 (23000): Duplicate entry '' for key 2

# Do this.
mysql> DELETE FROM users WHERE name='';

Comments

Posted on by Robert Douglass.

PS I discovered that the site I was working on had the missing uid=0 problem because we're automatically importing lots of nodes. The nodes may already exist in the system, in which case the site is supposed to skip them, but instead it was making multiple copies of each one, over and over. This got tracked down to the failing node_load(), and the missing uid=0.

Robert Douglass
Senior Drupal Advisor, Acquia

Posted on by agentrickard (not verified).

For what it's worth, the missing user 0 issue is usually the result of doing a MySQL dump and transfer from one database to another. This happens because {users}.uid is set to autoincrement, and the MySQL importer can't insert row 0.

When doing a database transfer, check the {users} table. Row 0 is normally transformed into row N (where N is the next autoincrement value). You may not want to insert a new row, but instead set that existing row to uid = 0.

This can possibly be prevented if you can use SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO when doing the import.

Posted on by Robert Douglass.

Brilliant feedback, Ken!

Robert Douglass
Senior Drupal Advisor, Acquia

Posted on by slantview (not verified).

The other things that can tip you off about this is that you can no longer store $_SESSION information for anonymous users. I just had to deal with this exact same issue.