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 10+.

Drupal 10+

For Drupal 10+, you can run the following queries:

INSERT INTO users (uid, langcode, uuid) VALUES (0, 'en', '');

INSERT INTO `users_field_data` (`uid`, `langcode`, `preferred_langcode`, `preferred_admin_langcode`, `name`, `pass`, `mail`, `timezone`, `status`, `created`, `changed`, `access`, `login`, `init`, `default_langcode`) VALUES (0, 'en', 'en', NULL, '', NULL, NULL, '', 0, 0, 0, 0, 0, NULL, 1);

e.g. via Drush:

drush -u 1 sql-query "INSERT INTO users (uid,  langcode, uuid) VALUES (0, 'en', '');"

drush -u 1 sql-query "INSERT INTO `users_field_data` (`uid`, `langcode`, `preferred_langcode`, `preferred_admin_langcode`, `name`, `pass`, `mail`, `timezone`, `status`, `created`, `changed`, `access`, `login`, `init`, `default_langcode`) VALUES (0, 'en', 'en', NULL, '', NULL, NULL, '', 0, 0, 0, 0, 0, NULL, 1);"

You can also do it in an update hook like:

function hook_update_n(&$sandbox) {
  $storage = \Drupal::entityTypeManager()->getStorage('user');
  // Insert a row for the anonymous user.
  $storage->create([
    'uid' => 0,
    'status' => 0,
    'name' => '',
  ])->save();
}

This will insert a new user with uid 0.

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

-+------+-----+
-| name | uid |
-+------+-----+
-|      |   0 |
-+------+-----+

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 = '';

e.g. via Drush:

drush -u 1 sql-query "INSERT INTO users (name, pass, mail, theme, signature, language, init, timezone) VALUES ('', '', '', '', '', '', '', '')"

drush -u 1 sql-query "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.

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

-+------+-----+
-| name | uid |
-+------+-----+
-|      |   0 |
-+------+-----+

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  |
-+------+-----+

Comments

majusz’s picture

majusz commented

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.

danieltome’s picture

danieltome commented

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

kraigory’s picture

kraigory commented

Running drush as user 1 worked wonderfully for me.

drush -u 1 cc all

Does anyone know if there are any downsides/dangers to running drush as admin user? If not, is there a way to tell drush to ALWAYS run as admin?

emcniece’s picture

emcniece commented

Man, I wish there was an upvote system. Nice tip!

dags’s picture

dags commented

This error suddenly started appearing while I was trying to debug migrations from the command line using XDebug + PHPStorm. The problem was that I had set a bad conditional breakpoint on a line in /includes/entity.inc -- I had used the assignment operator where I should have used the equality operator (ie. $ids=NULL instead of $ids==NULL). Big facepalm. Be careful when using debuggers!

m1r1k’s picture

m1r1k commented

Well, as now Drupal 7 has INNER JOIN to user_revision table, we have to keep connection between users and user_revision tables on vid, otherwise user_load won't return any user.

rooby’s picture

rooby commented

Only if you are using the user revision module.

highermath’s picture

highermath commented

This was exactly my issue. The VID for user 0 did not mach the one in the user table.

This might reflect a bug in the user revision module.

Thanks for this.

mohangupta’s picture

mohangupta commented

What is the remedy for Drupal 8?

wizbard’s picture

wizbard commented

I know very little PHP - just enough to be dangerous. I'm specifically trying to get Ubercart working, but after each completed transaction (membership registration), User 0 is re-numbered. Yes, I can go run the query in PHPMyAdmin manually, but in what file should I insert the snippet listed above: 

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

to restore User 0 after each new transaction? I'm using Drupal 7.

Many thanks for all the help I've received from these forums!

-wiz

goose2000’s picture

goose2000 commented

I just updated to Drupal 6.32 and this seems to have broke Drush now. I'm getting this error now.

svetlio’s picture

svetlio commented

You have uid0 and have this err?
(MYSQL related)

1. export your db_name.
2. drop your db_name.
3. create db_name with utf8 charset.
CREATE DATABASE `mydb` CHARACTER SET utf8 COLLATE utf8_general_ci;
4. import your db.

wizbard’s picture

wizbard commented

Thank you, svetlio. I just saw this response from you and will try it. But before I do (since I just crashed another site of mine because of a different problem relating to dropping the database) let me just confirm that you are basically just telling me to delete and then recreate the database (after backing it up) using the utf8 charset. Correct? Sorry, I just want to do it right the first time.

Thanks,

-wiz

svetlio’s picture

svetlio commented

That's right

One note from my experience:
One of my installation this was good effect, for another not. So this is not the 100% for all situations solution. But sometime work ;)

Wouter Waeytens’s picture

Wouter Waeytens commented

Worked for me thanks a lot! Saved me a lot of time :)

JurgenR’s picture

JurgenR commented

https://www.drupal.org/node/1029506#comment-7314538
Thnx, this comment saved my day!

Drupal backend developer since 2011.
Open Source Webdevelopment Coding Blog

sujann143’s picture

sujann143 commented

I didnt realize about it but while i was using Quiz module, i was not able to proceed to next question after i click next. I did mysql import on my local and while i went to do drush cc, it gave me message of missing uid 0 and that's how i realised the issue with it.

mariocantor’s picture

mariocantor commented

In my Drupal 7 the SQL command did not worked, so I removed fields (mode`, `sort`, `threshold`) and their values. and the query worked perfectly.

divined’s picture

divined commented

Most value question: Why its happens?

Why UID 0 is removed from the database once a month?

drugan’s picture

drugan commented

This is what happened with my drupal 8. Making some developing I've accidentally commented out a line of drupal core code and after refreshing a page everything was gone away. Nothing on the page, even bartik theme had disappeared, just black footer background and this message: You are not authorized to access this page..

I've tryed to go to <DrupalRoot>/user but mod_rewrite changed it for <DrupalRoot>/user/1 which is expected because I was logged in at the moment as the user 1. Then I've tryed this <DrupalRoot>/user/login, that gave me a bare html form to insert name and password but again, no avail.

Sorry, unrecognized username or password. Have you forgotten your password?

Go further, I've tryed drush cc all. This gave "Could not login with user ID #0." which lead me on this thread. Checked database user tables, everything is OK. Tryed reinstall site, deleting settings.php at first. Nothing helped — "Drupal site is already installed, go to main page".

What is saved me is git reset HEAD. After resetting working directory my site again worked perfectly. The internal reason for this kind of crashes is that Drupal always has at any its run time at least two users. One is you, as the Drupal site user, may be authenticated or anonymous, and the other user with uid == 1, which is the Drupal itself! It works for you on the backgroung, executing scripts and serving pages. You may ask but how about physical user with uid == 1, who is s/he? To understand this you need to recall about Linux superuser, which works on the background even if it is not created(!), and you as administrator, installing for example software on the system on behalf of a superuser using sudo <command>. That is, when we logged in the User One, we are actually an ordinary user having the same permissions as the great and mighty Drupal :)

drupalinthailand’s picture

drupalinthailand commented

Thank you so much, I have an anonymous user but this anonymous user does not have a login record, so it is still not recognized by drupal, do you know what I can do ?

I compared with other users, even on other drupal sites, and the only difference is the LOGIN column in the users table that is set to zero only on 1 websites having issues with anonymous user.

Any idea about how I could fill this login column ?

Thanks again.

rooby’s picture

rooby commented

By definition, an anonymous user is a not logged in user. The anonymous user will never be logged in so it is normal that the login timestamp be zero for the anonymous user (uid = 0).

Also, any user having a login value of zero will not break anything, so if you are having problems that isn't the cause.

drupalinthailand’s picture

drupalinthailand commented

Sorry but this is wrong, all my websites anonymous users have a login value that is not 0

Only 1 website that has problem and anonymous user with 0 in login column

nevets’s picture

nevets commented

I just checked a test site (Drupal 7) and for user 0, most columns/fields are 0 or null, including login being 0.

drupalinthailand’s picture

drupalinthailand commented

sorry i am on drupal 6

rooby’s picture

rooby commented

As you can see from the Drupal 6 example above for adding the anonymous user to the database, it is ok to have zero for the login column, even in D6.

I suspect that whatever your problem is is not related to the anonymous user login value being zero.

Also, extended conversations relating to bug troubleshooting are not really on topic for comments on documentation.

I would recommend posting a question asking for help fixing whatever your problem is.
Include as much detail as possible about what the symptoms of the problem are. So far you have said you have a problem but not said what it is.

I recommend posting it either to http://drupal.stackexchange.com/ or https://www.drupal.org/forum/22

harivenuv’s picture

harivenuv
Location India
commented

drush -u 1 sql-query "INSERT INTO users (uid, langcode, uuid) VALUES (0, "en", "");" this is not working the below is working

drush -u 1 sql-query "INSERT INTO `users_field_data` (`uid`, `langcode`, `preferred_langcode`, `preferred_admin_langcode`, `name`, `pass`, `mail`, `timezone`, `status`, `created`, `changed`, `access`, `login`, `init`, `default_langcode`) VALUES ('0', 'en', 'en', NULL, '', NULL, NULL, 'UTC', '0', '', '', '0', '0', NULL, '1');"

imlweb’s picture

imlweb commented

I resolved this issue by:

- Creating a new user on the admin side
- Went to the database and changed that user ID to 0
- Made all the other columns match values of the SQL (VALUES ('0', '', '', '', 0, 0, 0, '', '', 0, 0, 0, 0, NULL, '', '', '', NULL);
Thereafter the drush status resolved without the "Could not login with user ID #0." error

rblackmore’s picture

rblackmore commented

In Drupal 9 I had to do this: 

    $insert = \Drupal::database()->insert('users')
      ->fields([
      'uid'      => 0,
      'uuid'     => '02f0530c-d31d-4f34-bcdf-62fe93f70de9', //This is from /config/sync/user.role.anonymous.yml
      'langcode' => 'en',
    ]);
    $result = $insert->execute(); //This will return an auto_increment UID value. Use it run a merge query to actually set the UID to 0
    $merge = \Drupal::database()->merge('users')
      ->condition("uid", $result)
      ->fields([
        'uid'      => "0",
        'uuid'     => '02f0530c-d31d-4f34-bcdf-62fe93f70de9',
        'langcode' => 'en',
      ])
      ->execute();
jeremypeter’s picture

jeremypeter commented

Took @rblackmore's code and modified it for drush:

drush ev '
$uuid = \Drupal::config("user.role.anonymous")->get("uuid");
$result = \Drupal::database()->insert("users")
          ->fields([
            "uid"      => 0,
            "uuid"     => $uuid,
            "langcode" => "en",
          ])
          ->execute();

$merge = \Drupal::database()->merge("users")
          ->condition("uid", $result)
          ->fields([
            "uid"      => "0",
            "uuid"     => $uuid,
            "langcode" => "en",
          ])
          ->execute();
'
Summit’s picture

Summit commented

Hi, And what to do in Drupal 10 please?
I did this 

INSERT INTO users (uid, `language`) VALUES (0, "en");

Is this ok?

Jay.Chen’s picture

Jay.Chen commented

This won't work. You have to insert the uuid as well.
jeremypeter's code should work. https://www.drupal.org/node/1029506#comment-15159060