The first installed user from a fresh HEAD checkout a few minutes ago is uid = 2. This is a critical flaw because only uid = 1 has privileges to do anything serious on a fresh installation.

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

David Strauss’s picture

I'm still getting this on a fresh HEAD checkout.

PHP 5.2.3
MySQL 4.1.20 (using mysqli)

The autoincrement for {users} is set to 2 even before configuring the first user.

David Strauss’s picture

This is probably being caused by inserting the uid = 0 row for the anonymous user. MySQL does funky things when you manually specify the autoincrement value on an INSERT.

flk’s picture

strange, i have installed 2 checkouts under windows and debian, didnt seem to have this problem.

Linux:
MySQL(i) 5.0.32
PHP 5.2.0-8+etch1
Web server Apache/2.2.3

Windows
MySQL 5.0.18
PHP 5.1.2
Web server Apache/2.2.4

David Strauss’s picture

Running "ALTER TABLE {users} AUTO_INCREMENT = 1" after adding the anonymous user works on MyISAM tables, but fails on InnoDB tables. (Documented: http://dev.mysql.com/doc/refman/4.1/en/alter-table.html) So, that isn't a useful workaround.

David Strauss’s picture

FileSize
856 bytes

Here's a patch. It could theoretically enter a race condition, but the creation of the first user isn't really a critical path.

David Strauss’s picture

Assigned: Unassigned » David Strauss
Status: Active » Needs review
David Strauss’s picture

FileSize
1.06 KB

A better patch.

David Strauss’s picture

Title: First user is uid = 2 » First user installed as uid = 2 in MySQL 4.1
David Strauss’s picture

FileSize
1.22 KB

This update should prevent a regression in case MySQL did properly handle NO_AUTO_VALUE_ON_ZERO.

Dries’s picture

We have the good habit of not putting MySQL specific code in core. If this is a problem for MySQL, it might also be a problem for other database systems. They don't have the ability to modify user_save() ...

David Strauss’s picture

Well, if we want to do the ultimate "right thing," we should start using NULL for anonymous users instead of 0 so the entry into {users} for Anonymous becomes unnecessary. It's the right thing because NULL doesn't join with (or equal) NULL, and it makes sense to consider all anonymous users different because they're not authenticated. It's also convenient because we can eventually migrate to ON DELETE SET NULL for user deletions.

David Strauss’s picture

Status: Needs review » Active
Dries’s picture

Also, if you want to use master-master configurations, we'll want to support the auto_increment_offset setting in MySQL.

Multi-master replication is now easier because we no longer have self-generated indices. With auto-increment this becomes easier but we have to support MySQL's auto_increment_offset. (If master #1 and master #2 both insert an auto-incrementing key we'd get a conflict. The auto_increment_offset feature prevents that, by incrementing each index with a specified offset.)

This probably translates to: we can't make assumptions about certain objects having a specific ID, unless we force the correct ID at install time.

Food for thought ...

David Strauss’s picture

@Dries Yeah, that brings up another option: forget about treating uid = 1 as special and just add a superuser column that stores either 1 or 0. Then we can just not care that the first user created is uid = 2.

webchick’s picture

Or maybe add an admin role? http://drupal.org/node/1597 Old, old issue. ;)

Crell’s picture

-1 to not having uid 1 be "root". A "wheel group" is a separate issue. Saying "well, id 1 never actually exists, oh well" is not a solution. If it's happening to users, then it would happen to anything else, too, I'd think. That's just too weird.

David Strauss’s picture

@Crell This problem can't happen to anything but users. We don't insert a dummy row with the primary key being zero in any other tables.

stBorchert’s picture

jumping in from http://drupal.org/node/153065

WinXP SP2 + MySQL 4.1.11 + php4.3.11
=> first user has uid = 2

I noticed that db_query("SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO'"); did not have any affect.

Ubuntu 6 + MySQL 5.0.34 + php5.2.1
=> first user has uid = 1

Seems to be a mysql4.1 issue.

cburschka’s picture

Well, if we want to do the ultimate "right thing," we should start using NULL for anonymous users instead of 0 so the entry into {users} for Anonymous becomes unnecessary. It's the right thing because NULL doesn't join with (or equal) NULL, and it makes sense to consider all anonymous users different because they're not authenticated. It's also convenient because we can eventually migrate to ON DELETE SET NULL for user deletions.

Would also get rid of user 0 being editable/trackable/having OpenIDs/etc..

beginner’s picture

Same problem reported with mysql 4.1.21 with INNODB tables here http://drupal.org/node/152643 (duplicate).

chx’s picture

Assigned: David Strauss » chx
Status: Active » Needs review
FileSize
1.07 KB

Let's skip user 2 instead. That's not a biggie. Note that before this would not have been possible but the final config form makes this a feasible solution.

Dries’s picture

chx: can you better explain what you are saying and why this is better?

stBorchert’s picture

Status: Needs review » Reviewed & tested by the community

Ok, this patch solves the problem for me. First user has always uid = 1 and uid 2 (anonymous user) is blocked.

Thanks chx.

Dries’s picture

Don't commit this yet please -- I want to investigate it closer.

webchick’s picture

Dries, the old way had system.install inserting the uid 0 before uid 1 was created. uid 0 counts as autoincrement "1" in databases that are not properly supporting the NO_AUTO_VALUE_ON_ZERO flag, so uid 1 would actually be uid 2, which would never work, since we have hard-coded checks for uid = 1 at various places.

chx's patch solves this by moving the creation of the uid 0 record to *after* the uid 1 record. So uid 1 is always the first record created. On buggy databases that don't support the NO_AUTO_VALUE_ON_ZERO, when uid 0 is inserted, it'll auto-increment the uid field to 2, which means that the second user created will actually have uid 3, third user created will have uid 4, etc. But, that's rather immaterial compared to the superuser getting the wrong ID.

I don't have one of the buggy versions of MySQL to test against, but the patch seems very sane to me, and much saner than the other ideas we were throwing around.

chx’s picture

To test, just disable NO_AUTO_VALUE_ON_ZERO .

chx’s picture

Also, Drupal needs an anonymous user to just navigate around, all sorts of weirdness is going to happen if you do not have one. So, earlier you needed to visit the front page and then user/register to register uid 1 but not that happens at the end of install so we are happy without having an anonymous user.

Crell’s picture

So let me make sure I understand the net effect here.

- On a small subset of MySQL 4.1 releases from late last year, uid 1 will be the god account and uid 2 will be anonymous.

- On everything else, uid 1 will be the god account and uid 0 will be anonymous.

And somehow everything will still work. Right?

I have to ask, what's wrong with just inserting both uid 0 and uid 1 with explicit ids during the install process (since uid 1 is now created by the install script)? Then uid 1 and uid 0 will have consistent meanings on all systems, and on the buggy subset the "everyone else" users will just start at a higher number, where they don't actually matter.

vlad.leo’s picture

I have same problem.
I try to install Drupal 6dev to my hosting (Linux, Apache 1.3.37 with SMTP, php 5.2.3, Drupal 6.dev, MySQL 4.1.21) all work fine

But on my local comp (Win Xp Sp2, Apache 1.3.37 php 5.2.3, Drupal 6.dev, MySQl 4.1.22-community-nt) without smtp and have that problem.

conclusion:

Drupal installation complete
warning: mail() [function.mail]: Failed to connect to mailserver at "localhost" port 25, verify your "SMTP" and "smtp_port" setting in php.ini or use ini_set() in D:\Apache\Apache\htdocs\dru6\includes\mail.inc on line 183.

When server have not ability to send mail, Drupal set wrong uid = 2 to first user. It is not mistake of MySql 4.1 this is problem of drupal install system

See mor info http://drupal.org/node/169340 but main page is here.

webchick’s picture

Crell, no...

- On a small subset of MySQL 4.1 releases from late last year, uid 1 will be the god account, uid 0 will be anonymous, and uid 2 will never be created. Future users will be uid 3, 4... This is because the insert statement that adds uid 0 mistakenly increments the next autoincrement ID.
- On everything else, there will be no changes.

@vlad.leo This bug has nothing to do with SMTP. It's likely the MySQL version run by your local server with the SMTP server has this bug.

David Strauss’s picture

I wouldn't call the default version of MySQL bundled with RHEL4 a "small subset." Some of the largest web data centers in the world have most of their servers running on it. In any case, I think this is a good fix. I'd test it, but I've already upgraded the server I had the problem on to the latest MySQL 5 release.

Crell’s picture

@webchick: Ah, thanks, that makes it clearer. I have no problem with that policy, as it has no effect on functionality and only affects a small number of versions.

@David: I think webchick meant a small number of version releases. Since skipping uid 2 at that point becomes just a visual change, essentially, with no functional impact I have no problem with telling people with those versions "sorry, you just don't get #2". That it's in RHEL 4 doesn't really matter, honestly, since Red Hat has a reputation for patching their software without changing version numbers so I don't trust their version numbers most of the time anyway. :-) Still, at worst it's something to document as "yes we know about it, no it's not actually a problem, carry on".

I agree with the concept, but also do not have access to an affected server so can't test it.

chx’s picture

Dries committed this partially in http://drupal.org/cvs?commit=79441 which does not smell good to me.

Dries’s picture

I had a conflict with one of the patches committed by Gabor. Fixed that now.

Dries’s picture

Status: Reviewed & tested by the community » Fixed
chx’s picture

Status: Fixed » Reviewed & tested by the community
FileSize
726 bytes
Dries’s picture

Status: Reviewed & tested by the community » Fixed

Getting it right ... committed.

Anonymous’s picture

Status: Fixed » Closed (fixed)
David_Rothstein’s picture

Just for the record, it looks like the "uid=2 not being created" side effect is not just for MySQL 4.1. I was doing some 6.x testing on a Debian Etch system (with MySQL 5.0.32) and noticed that uid=2 was never created.

I almost filed a bug report until I found this. As stated above, of course, it's not a big deal either way ;)