Restore the anonymous (user ID 0) user record

Last updated February 23, 2013. Created by webchick on January 17, 2011.
Edited by LeeHunter, jenlampton, raincloud, greggles. Log in to edit this page.

Having the Anonymous user deleted in your site breaks Drupal in unexpected ways, including a message like:

"Could not login with user ID #0." error from Drush
"warning: Invalid argument supplied for foreach() in /includes/form.inc on line 1181." and various other strange errors from Drupal core

Fixing cases where you accidentally deleted the user

There are many ways to accidentally delete the uid 0 (Anonymous) user: a mistaken query that forgot to use "and uid != 0" or a Views Bulk Operations view that was a little too aggressive.

The technique to fix it depends on a few factors. Below are different techniques that work on Drupal 7 or Drupal 6.

Drupal 7

For Drupal 7, you can run two queries:

insert into users (name, pass, mail, theme, signature, language, init, timezone) values ('', '', '', '', '', '', '', '');
update users set uid = 0 where name = '';

This will insert a new row getting a random UID and then the update query fixes to get the right uid for the user.

For Drupal 6 using a slightly different technique

You can also insert directly with a specific value for the auto-incrementing UID if you tell your database to use a different SQL_MODE.

This pair of statements will bring the user back on Drupal 6:

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
INSERT INTO `users` (`uid`, `name`, `pass`, `mail`, `mode`, `sort`, `threshold`, `theme`, `signature`, `created`, `access`, `login`, `status`, `timezone`, `language`, `picture`, `init`, `data`,  `signature_format`) 
VALUES ('0', '', '', '', 0, 0, 0, '', '', 0, 0, 0, 0, NULL, '', '', '', NULL, 2);

Check again:
select name, uid from users where name = '';
Output should be like this:

-+------+-----+
-| name | uid |
-+------+-----+
-|      |   0 |
-+------+-----+
-1 row in set (0.00 sec)

Fixing cases where the import changed the uid values

Certain database dump programs, such as older versions of phpMyAdmin, do not respect MySQL's SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; directive. The result of this is when importing a faulty database dump, your anonymous user record gets imported as (highest user ID) +1 instead of user ID 0.

Should this happen to you, you can fix your database by issuing the following query:

UPDATE users SET uid = 0 WHERE name = '';

To check if this fix applies to you run:

select name, uid
from users
where name = '';

Output should be something like this:

-+------+-----+
-| name | uid |
-+------+-----+
-|      |  0 |
-+------+-----+
-1 row in set (0.00 sec)

Comments

Thanks a Lot!

Posted by purna_dey on June 5, 2012 at 10:01am

Hi Sir,

I am using Drupal 6.24 and Drush 5.x on windows 7
And this Solutions is working for me.

Thanks a lot!

Query failed because of db-prefix.

Posted by thomasvum on June 8, 2012 at 1:40pm

Be aware, that your table name user could have some prefix. Was the case in my database.

insert into DBPREFIX_users (name, pass, mail, theme, signature, language, init, timezone) values ('', '', '', '', '', '', '', '');
update DBPREFIX_users set uid = 0 where name = '';

Before I was aware of that, I logged in in my phpmysql and reset my uid by hand. Volià!

also for drupal 5, drush 4.6-dev

Posted by naught101 on June 11, 2012 at 3:34am

I hit this problem with drush 4.6-dev and drupal 5:

$ drush status
Could not login with user ID #0. This is typically caused by importing a MySQL database dump from a faulty tool which re-numbered   [error]
the anonymous user ID in the users table. See http://drupal.org/node/1029506 for help recovering from this situation.

This fixed it:

drush sqlc
insert into users (name) values ("");
update users set uid = 0 where name = "";

Of course, you then have to set the username and password, but there's a drush command for that.

Rebuilding search-index not working without user 0

Posted by majusz on October 10, 2012 at 10:17am

Thanks for this comment!
It just solved a problem I was trying to fix for the last two days.

I had this issue with a Drupal 6 installation that the search index wasn't properly rebuilt by the usual cron job. After researching a lot and not finding anything helpful, I installed Drush, and Drush told me that it couldn't login as user #0. So I followed the directions above, so that I could use Drush with user #0. After I did that, Drush AND the search indexing worked perfectly fine.

So for everyone having similar problems: the Drupal search index rebuilding engine seems to rely on the Anonymous user as well.

Drush issue

Posted by danieltome on April 18, 2013 at 2:09am

If you came to this issue because Drush sent you here:

Drush spits out:

Could not login with user ID #0. This is typically caused by importing a MySQL database dump [error]
from a faulty tool which re-numbered the anonymous user ID in the users table. See
http://drupal.org/node/1029506 for help recovering from this situation.

This was related to a site we built where we do not allow anonymous access. So the homepage displays access denied and the login screen.

Simple solution is to run drush as admin:

Eg clear cache:
drush -u 1 cc all

Danno
http://www.danieltome.com

Note that this can also

Posted by nicksanta on May 15, 2013 at 1:15pm

Note that this can also occasionally happen when User Revision module is enabled. To fix this particular instance I have used this query:

UPDATE user_revisions SET vid = 0 WHERE uid = 0 LIMIT 1

----------------------
Nick Santamaria - Freelance Drupal Developer and Consultant

Thanks - Restore UID 0 worked with Drupal 6.23

Posted by Bob Siegfried on May 19, 2013 at 6:53pm

Thanks, the Drupal 6.x query worked for me with Drupal 6.23: mystery solved, the problem resolved.

If that's reproducible, it

Posted by naught101 on May 21, 2013 at 11:22am

If that's reproducible, it should probably be submitted as a bug request on the User Revision issue queue.