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:

Comments

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.

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. Seehttp://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.

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!

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:

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.