Anonymous user id breaks on MySQL export/import

On a completely fresh install I can't replicate this problem. Must have been something else involved, but not sure what.

I got this error too, for me it appeared on admin/content/types/[type] and the following page after I submitted the form.

I tracked it down to the function user_access(), and further on to comment_form_alter(), where this line (545 in HEAD) breaks user_access() by passing it a FALSE in the second argument:

This is because user_load() returns FALSE if it doesn't find a user with uid '0', which is strange, because user 0 should always be there. When I looked at my users table in the DB, I was even more confused - the anonymous user had been given a uid of '3'!

It so happened that I had exported the DB tables from my development server onto my public server, and in the .sql file I had used, the auto_increment counter was specified, and it was '3'. So when MySQL parsed the file and tried to INSERT the anonymous user with uid '0', it parsed the value as a NULL and automatically gave it a new number using auto_increment.

This is a likely situation for those who develop their websites locally before uploading to a shared host, using export-import for transfer their database. I don't know the implications of not having an anonymous user other than these PHP warnings, but it can't be that good..

I don't know how we can fix this, as it really has to do with how MySQL interprets values. Turning off the unsigned switch on users.uid and setting anonymous' uid to '-1' will do it, don't know about any side effects though.. Turning off auto_increment doesn't seem like a good idea. Only warning users about this in the handbooks doesn't feel right either, I think a lot of people will experience this if it's not fixed. Maybe add some code to check if uid '0' exists, and add a new anonymous user if it's missing, preferably late instantiation-style. Could this be done in user_access()?

MySQL version where this occured was 5.0.51.

The anonymous uid should really be NULL. This would also help with db indexing.

Setting the anonymous uid to null would be a chore, it's checked to be 0 in quite a few places.

Retitled. From the MySQL docs:

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

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 the question is what MySQL versions do include NO_AUTO_VALUE_ON_ZERO by default, and how we can ensure people use that. (As shown a NULL user ID would just as well increment the counter badly, so it is no solution).

Marking this critical, since this can badly break sites exporting/importing MySQL data.

I did some testing
Setup: PHP 5.1.6, MySQL 5.0.24a, MySQL dump 10.10, phpMyAdmin 2.8.2
Table with an auto_increment field 'id', an entry with id 0 and some other entries with higher ids (highest id: 12)

• Export with command line mysqldump: dump contains 'NO_AUTO_VALUE_ON_ZERO' setting
  Import with command line mysql client: table correctly imported (id 0 is preserved)
• Export with phpMyAdmin export (default settings): dump contains no 'NO_AUTO_VALUE_ON_ZERO' setting
  Import with phpMyAdmin import: table import fails: (id 0 becomes 13)
• command line mysqldump + phpMyAdmin import: table import fails (id 0 becomes 13)
• phpMyAdmin export + command line mysql client import: table import fails (id 0 becomes 13)

In attachment: the dumps with mysqldump and phpMyAdmin

We could add a notice about this in relevant Handbook pages, but obviously everybody won't be reading it. Also, SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO" won't work in some versions of phpMyAdmin (or is it some versions of MySQL in general?), as reported here, so a lot of users would have to manually set the uid to 0 after import. I'd only consider that as our last resort..

Switching off unsigned and setting the anonymous user id to -1 would work, but uid 0 has been so incorporated that this would be a tough change to impose on Drupal.

Yep, changing to -1 is not workable now.

According to the MySQL 4.1 Reference Manual, NO_AUTO_VALUE_ON_ZERO was added in MySQL 4.1.1. Drupal 6 requires MySQL 4.1 or higher, so only 4.1.0 will be unable to import correctly.

So the problem here really has to do with the export. mysqldump 4.1.1 and higher should add the NO_AUTO_VALUE_ON_ZERO statement to its dump files, but there are plenty of tools out there that doesn't do this.

Please disregard my previous comment on 'uid should really be NULL'. Primary indexes can't have NULLs.

I can't determine which version of phpMyAdmin introduced NO_AUTO_VALUE_ON_ZERO, but 2.9.2 does not output it and 2.11.1 does.

This bug was fixed in phpMyAdmin 2.11.1.0, but it just relates to ensuring that NO_AUTO_VALUE_ON_ZERO is only outputted if the db can reimported it, i.e. it is mysql 4.1.1 .

Unfortunately, many hosting companies have old versions of phpMyAdmin.

This really is a quandary.

MySQL 4.1.1 should be made a requirement for D6.

OK, so if we make MySQL 4.1.1 a requirement, we can make sure Drupal 6 installs are ready to accept NO_AUTO_VALUE_ON_ZERO, but the exports can only be documented to use that (mysqldump does that I assume from 4.1.1, but phpMyAdmin users need to upgrade).

What about having a patch to elevate requirements to 4.1.1 and document this stuff as well in the handbooks.

I think a lot of users would still suffer from this issue, and get annoyed over this, until they finally find a solution in the handbooks. Remember, not everybody RTFM.

What about, like I suggested in my first comment, adding code to Drupal that checks if a uid of 0 is missing, and if it is, add an anonymous user. It could also search for the old anonymous user row that got its uid messed up, and remove it from the table. This could be done in user_load() or another appropriate place, so that Drupal only does it if a query for uid 0 is made. Could this be a solution?

Edit: I meant user_load(), not user_access()...

Why does there need to be a uid = 0 row in {users} anyway? For joins?

user_load() could handle loading uid = 0 as a special case.

I think a lot of users would still suffer from this issue, and get annoyed over this, until they finally find a solution in the handbooks. Remember, not everybody RTFM.

I agree. Note depending on NO_AUTO_VALUE_ON_ZERO would introduce a phpMyAdmin version requirement, while phpMyAdmin is not required for running drupal, just a standalone tool.

What about, like I suggested in my first comment, adding code to Drupal that checks if a uid of 0 is missing, and if it is, add an anonymous user. ....

What about a hook_requirements() entry that would check and resolve this, just like "Cron has not run recently. Click here to run cron manually."?

Yes, for joins, handing out permissions, data consistency / referential integrity (ie. columns referring a UID can contain 0).

Changing the requirements/handbook and adding some corrective code aren't mutually exclusive imo.

Here's a patch for the first, removed the version checking in database.mysql.inc altogether since it only makes sense if pre 4.1.x versions are used anyway.

We hardly need a patch to raise the requirement, the first production MySQL 4.1 was 4.1.5 and also note that for all practical purposes, everyone runs MySQL 5 by now because the active support for 4.1 has ended.

#14 - I don't think it would work in hook_requirements() as it needs to be fixed before a join to {users} occurs.

There needs to be a check for the uid = 0 row during drupal initialization that would recreate it and remove the row with name = '', i.e. the old uid = 0 row.

aw, crossposted. This looks fine to me... though, I wonder how far we want to go? We could add a single SQL statement to admin saying UPDATE users SET uid = uid - uid WHERE name = '' to reset...

also note that for all practical purposes, everyone runs MySQL 5 by now because the active support for 4.1 has ended.

http://www.google.co.uk/search?q=mysql+4.1+hosting

Only 18 months ago I had some shared hosting with WHB where they'd not upgraded from 4.0 yet.

UPDATE users SET uid = uid - uid WHERE name = '' on admin seems good. Are there any conceivable situations where name wouldn't be an empty string?

crossposted?

why not UPDATE {users} SET uid = 0 WHERE name = ''? Why the uid - uid?

What do you mean admin? it should probably go in user_init()

where in the codebase is the uid = 0 row created in the first place?

@jbrown: every non cached page request? For something that's likely to affect only a small set of users?

#21: See line ~372 in system.install, uid 0 is created halfway down system_install(). uid = uid - uid is because simply doing uid = 0 will invoke the auto_increment mechanism..

We could also catch the missing uid = 0 in user_load() and invoke a REQUIREMENT_ERROR message in admin/reports/status with a link to have Drupal fix the problem, much like "run cron manually". A simple SQL query like chx said is much less intrusive though..

#22 - you are right. Fixing it when the user goes to an admin page is a good compromise. The unwitting admin will start getting sql errors after the db import and will hopefully visit an admin page.

There should be a message saying what the problem was and that it has been fixed.

#16 looked good, so committed.

This still does not solve the issue if the import file did not have the NO_AUTO_VALUE_ON_ZERO flag, and the anonymous user ended up somewhere else. IMHO it is a good idea to add some quick self-solving code to Drupal, since this issue will come up often.

I would like to fix this but despite moving away the uid 0 entry my site did not break. As this user is created by drupal_anonymous_user that's no surprise. Anyone care to post reproduction instructions?

chx: export with anything which does not include NO_AUTO_VALUE_ON_ZERO (eg. an older phpMyAdmin). Then import that to the MySQL database with any tool, and boom, you have your anonymous user broken.

I did emulate that with changing the uid for uid 0. What do you mean by broken. I get no errors in the site despite

Ah, http://localhost/main/admin/content/types/blog failed. OK, that's something I can work from.

to test , run update users set uid = 1000000 where uid  = 0; and then visit the status page, it should be restored. There is another bug, namely comment module issuing a user_load against anonymous instead of using a function call.

The patch works, except for a small typo in comment.module that I had to fix. But I noticed the user has to go to admin/reports/status for it to fix the uid, and it would do it every time you access that page.

I've added an ifelse clause to user_load() instead, that runs the same query to fix the uid but only when it's actually needed (when uid 0 is not found). Since Drupal calls user_load(array('uid' => 0)); for every page served to the anonymous user, doing it like this will fix the wrong uid hopefully before it can cause any trouble.

FYI, some other issues due to a lost uid 0:
http://drupal.org/node/179915 (Resolved: Captchas don't work on second site (was missing user UID 0 in database)
http://drupal.org/node/137468 (captcha fails when uid=0 not first row in user table)

Nope. Drupal does not call user_load for most pages with uid 0. drupal_anonymous_user is used. That's why I was unable to repro at first. I had a similar patch but then I have not submitted it. Calling user_load on anonymous should not happen. We might want to add this to the doc. The problems mostly occur on JOIN against users. After import, I guess it's fairly expected to check the status page for everything OK. We can move the query to every admin page if we want to. So the patch to review and discuss is #30 .

Hm, I guess I assumed Drupal calls user_load() on all pages, because inserting a simple print $array['uid']; on line 150 in user_load() will produce 0 on top of all pages when not logged in (clean install). See for yourself..

So what could be calling user_load() with uid 0? If it should not happen, at least it should be fixed.

I agree that one would normally check the status page after an import, so your patch will do the job. But if we actually end up calling user_load() once for anonymous users, I'd prefer to check there.

what about you put in a if (isset($array['uid']) && empty($array['uid'])) var_export(debug_backtrace()); in there and check what calls that user_load? As I stated more than once, I don't get an error on every page. I might miss something, obviously.

Since user_load() is not invoked for anonymous users and it should not be invoked either, chx's solution is a better possibility. We already use the module form to "heal" cache problems, so using the status page to "heal" mysql problems is just another magic behavior introduced. I'd welcome some simple troubleshooter in Drupal 7 if we have a list of such issues, which are odd to solve otherwise.

Committed a slightly modified patch of #30, with the parse error fixed: http://drupal.org/cvs?commit=97451

I don't get an error on every page either, but drupal_load() does get called for anonymous users on every page I've tried.

debug_print_backtrace() gave me this result (trimmed down the arguments a bit):

With the user blocks disabled, menu_tree_page_data(navigation) will still get called from menu_set_active_trail().. Is the problem in user_current_load(), or in the menu system?

Sorry, cross posted..

I've opened a new issue to deal with user_load() being called for the anonymous user, specifically from user_current_load().

Automatically closed -- issue fixed for two weeks with no activity.

I just wanted to add one more case where this have bitten: #243423: User with uid=0 will change uid when copied/inserted to {users} table with MySQL and MyISAM table, breaks anonymous postings

,but I would like to object against quirky solution -
db_query("UPDATE {users} SET uid = uid - uid WHERE name = '' AND pass = '' AND status = 0");
seems to me like relying on undocumented behaviour and it is a question of time when will MySQL team fix this bug in MySQL.

Correct solution would be setting session mode correctly and not relying on hacks, even if that would mean requirement of MySQL 4.1.1 instead of 4.1.0:

What do you think?

Actually the MySQL requirement is already 4.1.1 http://api.drupal.org/api/constant/DRUPAL_MINIMUM_MYSQL/6.

Your suggestion seems like a sensible improvement, provided that the SET SESSION thing always works, but probably not worth doing in 6.x since nothing is actually broken.

IMO what would be more useful is a dsm() if the UPDATE was actually necessary (as suggested at #24).

That is nice.

According to MySQL manual http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html ,"Any client can change its own session sql_mode value at any time.", so it should be OK.

PS: What is dsm()? I see it used only in Devel.module, and it just converts the call to dpm, which isn't in the API manual either.

Abbreviation for drupal_set_message().

While the above solutions may patch the problem somewhat, the fact still remains that drupal 6's core is relying on behavior that is not default for mysql.

Yes, you could do some hacks to automatically insert a zero user if one is not present. However, If the average user tries to export/import or even copy an existing database they are going to have issues. The entire import will fail because you will wind up with duplicate primary keys in the the users table ( user 0 becomes user 1 on insert, and you get a duplicate key on user 1 when the admin account is inserted which kills the rest of the import). This is a huge data integrity problem, making it impossible to copy/export a database and re-import it elsewhere. Obviously if you understand the problem you can modify the sql and get it working, but the vast majority of people wont understand what the issue is.

The drupal core needs to change so that it does not rely on non-default mysql behavior. The only 2 ways i see this happening are:

A) The core needs to change so no entry is required in the user table for anonymous users
or
B) Anonymous users need to become uid 2 instead of uid 0

@45: The comments above make no mention of a duplicate key error.

If I dump the user table I get
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
at the top of the dump. This is presumably a MySQL config option set on my server. So when I reimport (using phpMyAdmin) --> no problems.

If I remove that line then when the table is imported, the 0 is not turned into 1 (clashing with the real uid 1) but into the next hightest uid for the whole table. Presumably because one INSERT is used to generate the data for the whole table.

So I can't get a duplicate key error to occur.

Related: #243423: User with uid=0 will change uid when copied/inserted to {users} table with MySQL and MyISAM table, breaks anonymous postings.

Odd. When the following query is run on our production MySQL 5 server we get the duplicate key issue.

(usernames emails and password hashes have been replaced with XXX here for security reasons)

You are right though, the anonymous user should get bumped to 5, not 1. Maybe it is some setting on our production server to ignore auto increment values on import? I'll have to look into that.

The version of phpMyAdmin we are using doesn't insert SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; automatically for me - though we are using a pretty old build of it so that may be our fault for not updating. I'm not sure when that statement was added to the phpmyadmin export. Maybe this is a moot point now since there doesn't seem to be a lot of people experiencing this problem.

Still, it would be good to move away from the magic numbers 0 and 1 and instead define constants ADMIN_USER and ANONYMOUS_USER. At the same time, the entries in the users table could be easily updated to work regardless of the mysql settings / phpmyadmin version

I'm using phpMyAdmin 2.11.6. On the export page there is an option in the Data secion "Extended inserts". This is checked by default, and causes the multiple INSERTs for each table to be combined into one (extended) INSERT for the table, which causes the "bumping" effect you describe when NO_AUTO_VALUE_ON_ZERO mode is not active. In your case I can see why you get a duplicate key error since the INSERTs are processed separately.

Guessing, but the NO_AUTO_VALUE_ON_ZERO mode (which was introduced in MySQL 4.1.1) may not be so much a property of phpMyAdmin as of MySQL itself. On the other hand it could be that later versions of phpMyAdmin put this in anyway for safety's sake.

Indeed constants ADMIN_USER and ANONYMOUS_USER sounds sensible. Pontificating, a proper solution is certainly needed for 7.x. For 6.x I'm not sure what the solution is. If Drupal had an option for DB export then it could at least control the format of the dump file and thereby prevent problems on import.

I want to note a couple of cases where this causes a problem that can't easily be worked around too.

I'm using phpMyAdmin 2.11.9.1. When I upgrade from one version of Drupal to another, I use it as root to copy over the entire db to a new fresh copy (from the Operations tab) and point the new installation to the new db. This makes it easy to revert back if needed, and don't need to worry about broken export files. I've used this method from D5.1 through D5.10 and it works very nicely.

Unfortunately this now breaks with D6. phpMyAdmin will copy the entire db up to the users table, and then error out with a duplicate record (uid 0 gets bumped to uid 1 in the new copy, and then it tries to insert the next record with uid 1 also). I have no known means to make it insert a NO_AUTO_VALUE_ON_ZERO setting (as there are no files exported or imported to edit) short of changing the *global* sql_mode for the entire server, not just the Drupal db. Which I'd rather not do of course as I have no idea what else that might affect.

The other case is simply using phpMyAdmin in the same fashion, but copying just the users table on its own (to a new db or new table name) also produces the duplicate error.

For me, the work around I did meant that when I wanted to upgrade my test D6.4 site to D6.5, I ended up having to delete uid 0 from the users table so I could copy the db. It didn't seem to break anything, but I see from here there will be JOIN cases where that'll be an issue, so I'll insert it back in now.

Now if someone knows how to force phpMyAdmin to do NO_AUTO_VALUE_ON_ZERO on a full db copy, that'd probably solve my issue at least. My internet searches for a solution only find people doing exports and imports.

Though fundamentally, it seems like we shouldn't be having a key of 0 for a table with an auto-inc key.