see http://drupal.org/node/239150:

MySQL with MyISQM tables expects the autoincrement column to begin from 1 and for id=0 the behaviour isn't well defined:
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

"Note MySQL's AUTO_INCREMENT behavior for ID=0 cases. (for MyISAM tables, at least).

* Autoincrement starts at 1 by default
* Autoincrement, if applied to a table with existing data (using the ALTER statement) will attempt to overwrite the record with ID=0 and assign that columns value the next AUTO_INCREMENT value."

So, when moving/exporting compete drupal installation eg. from localhost to real host, depending on used tools, user with uid=0 can get different uid (in my case it is the value of autoincrement counter, eg. 44), and consequently, comment_render() in comment.module will not return comments written by anonymous users (because INNER JOIN will not pair comments with uid = 0 with user.uid = 44).
In the end, anonymous comments (and maybe some other anonymous stuff) will not be displayed anywhere in drupal, not even in admin interface. (the only exception is 'recent comments' block because it doesn't look up comment's author).

The solution is to look up anonymous user on the server and change its uid back to 0.

I think this should definitely be mentioned explicitly somewhere in drupal guide, the bug has cost me about 5 hours to track, and I am still quite lucky. Also, this happens to every installation where anonymous user can post content and MySQL uses only MyISAM tables!

Proposed fix: Many modules rely on fact that anonymous user has uid=0, we can't change that, so I would suggest that Drupal would check whether user with uid=0 exists and if not, it would report the error and solution at admin/reports/status page.

What do you think?

Comments

keff’s picture

More on this from MySQL authors here:
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

NO_AUTO_VALUE_ON_ZERO

NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number.

This mode can be useful if 0 has been stored in a table's AUTO_INCREMENT column. (Storing 0 is not a recommended practice, by the way.) For example, if you dump the table with mysqldump and then reload it, MySQL normally generates new sequence numbers when it encounters the 0 values, resulting in a table with contents different from the one that was dumped. Enabling NO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem. mysqldump now automatically includes in its output a statement that enables NO_AUTO_VALUE_ON_ZERO, to avoid this problem.

So, Drupal's part of the error can be resolved by SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';, but that won't do anything with third party apps used to transfer drupal installations, so I still think Drupal should check for anonymous user and warn admin.

keff’s picture

Another problem is that without NO_AUTO_VALUE_ON_ZERO mode, MySQL will change uid with first ALTER statement as well, so any module that will alter a set of users that contains uid=0 will break anonymous comments (and that is probably what happened to me when i encountered the bug - I think it was one of built in modules).

keff’s picture

Version: 6.1 » 6.2
Priority: Normal » Critical

OK, the bug still persists in 6.2.

What do you think about marking it as critical - in a real world use, all content submitted by anonymous users will disappear once any plugin modifies a set of users containing the anonymous user.

I don't have the knowledge of Drupal to fix it, but i suggested the quick fix - add check whether anonymous user has uid equal to 0, and if not, display error box in a reports section, so taht user at least knows about the problem.

boydjd’s picture

Priority: Critical » Normal

Drupal takes this into account during the install process.
This is not critical, as it will only happen if you're migrating in an odd way.
To fix this issue, when you dump your mysql files, you're going to have to modify the create table statement to start auto_increment at 0.
As for your second post, this is just not true. The only time mysql will change the 0 is if you're altering the uid key.

keff’s picture

>Drupal takes this into account during the install process.
Thanks, I didn't know that.

>This is not critical, as it will only happen if you're migrating in an odd way.
>To fix this issue, when you dump your mysql files, you're going to have to modify the create table statement to start auto_increment at 0.
Well, yes, but it happened to me and many other people as well, and all of us had no idea why it is not working (for example here: http://drupal.org/node/11021 and lot of others). I think it would be really nice if Drupal would check the existence of user with uid=0 in cron and displayed a red field in a report list, so that user will know what happened.

>As for your second post, this is just not true. The only time mysql will change the 0 is if you're altering the uid key.
I have just tried it and you are right - I must have been altering the primary key when testing it. Thank you and sorry for confusion.

keff’s picture

OK, I made a simple module that implements hook_requirements, checks for user with uid=0, and if it doesn't exist, tries to repair the situation (and adds a warning to admin/reports/status). If the repair doesn't help, it displays error in admin/reports/status.

Until I will get CVS access, you can download it at http://sklad.tomaskafka.com/php/drupal6_checkuid0.zip

I thought developing for drupal is harder, well done API designers :)).

nicholasThompson’s picture

I have just noticed this "error" too - I have our system do a daily database check/optimize and everyday the users table it flagged as a "warning" level error.

I understand its not critical and it doesn't appear to break anything - but surely its bad practice to release a system/not consider a bug critical if its throwing these kinds of errors? Does Drupal aim to be PHP_ALL compliant? Assuming it does - should it not also aim to be "MYSQL_ALL" (made up - but you get my point) compliant?

Just out of interest - why is there a UID 0? Would it not be more efficient for Drupal to generate the anonymous UID without "wasting" a DB hit like this?

keff’s picture

I have reopened #204411: Anonymous user id breaks on MySQL export/import (xID of Zero get's auto-incremented on external xSQL events) - setting SET SESSION sql_mode='NO_AUTO_VALUE_ON_ZERO' during the session should tell MySQL that zero in autoincremented column is OK.

Having anonymous user as uid=0 actually saves DB requests, because modules don't need to have a special case for anonymous content: for example, comment module uses comments JOIN users on comment.uid=users.uid, and so the anonymous comments just work naturally.

francismak’s picture

When working with ubercart, I was wondering why the shopping cart doesn't work. At last found my user '0' changed to another number.

According to the comments from boydjd:

Drupal takes this into account during the install process.
This is not critical, as it will only happen if you're migrating in an odd way.

I totally disagree. As for my case as an example, we install Drupal in staging server for development purpose. Then we dump the SQL file and import to the production server when it is ready for launch.

It is a very common practice.

During the importing, the UID 0 will get lost...

Dave Reid’s picture

fishfilet’s picture

I just had this problem when copying a database using phpmyadmin. I wish I would have searched soon because I waisted about 5 hours worth of work as well. However I am glad it is working now and I know this is an old thread but It helped my out a lot. Thanks

mot’s picture

To re-create the user from hand, I added a new one and changed the UID value to 0 afterwards.

I had the following scenario:

I wanted to copy the database over to another database. I did this with phpmyadmin and I got the following error for table drupal_users:

#1062 - Duplicate entry '1' for key 'PRIMARY'

The key 'PRIMARY' is defined as the following:

Keyname: PRIMARY
Type: PRIMARY
Field: uid

Because of that I made the mistake to delete the entry with UID 0 in the original table.

The site did only do 404s all over the place.

Re-recreating the user with UID 0 did help. I did it this way:

a) Create a new user row with phpmyadmin and not entering any values.
b) Locating that new user row and then changing UID to 0 (it's set to some auto-increment value)

Done. Problem solved (at least on the one page).

Now I need to find out how to properly copy over the database.

Lukas B.’s picture

Seems like #6 solved the problem for me! Thanks for your effort keff! For your detailed documentation and your patch!

Lukas