I get the following errors after I've created the first new content type on my 6.0-RC1 site. They persist through all subsequent page views.

* warning: array_fill() [function.array-fill]: Number of elements must be positive in /Users/dankarran/Sites/drupal6/includes/database.inc on line 234.
* warning: implode() [function.implode]: Bad arguments. in /Users/dankarran/Sites/drupal6/includes/database.inc on line 234.
* warning: array_keys() [function.array-keys]: The first argument should be an array in /Users/dankarran/Sites/drupal6/modules/user/user.module on line 479.
* user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1 query: SELECT p.perm FROM role r INNER JOIN permission p ON p.rid = r.rid WHERE r.rid IN () in /Users/dankarran/Sites/drupal6/modules/user/user.module on line 479.

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

geodaniel’s picture

Status: Active » Closed (won't fix)

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

ximo’s picture

Title: Anonymous user id breaks on MySQL export/import » MySQL: auto_increment corrupts uid of anonymous user when importing tables
Priority: Critical » Normal

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:

    if (!user_access('post comments', user_load(array('uid' => 0)))) {
      $form['comment']['comment_anonymous']['#disabled'] = TRUE;
    }

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.

jbrown’s picture

Title: Errors after creation of first content types » MySQL: auto_increment corrupts uid of anonymous user when importing tables
Version: 6.0-rc1 » 6.x-dev
Component: node system » database system
Priority: Critical » Normal
Status: Closed (won't fix) » Active

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

Anonymous’s picture

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

Gábor Hojtsy’s picture

Title: MySQL: auto_increment corrupts uid of anonymous user when importing tables » Anonymous user id breaks on MySQL export/import
Priority: Normal » Critical

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.

soxofaan’s picture

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

ximo’s picture

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.

Gábor Hojtsy’s picture

Yep, changing to -1 is not workable now.

ximo’s picture

Title: MySQL: auto_increment corrupts uid of anonymous user when importing tables » Anonymous user id breaks on MySQL export/import
Priority: Normal » Critical

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.

jbrown’s picture

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.

Gábor Hojtsy’s picture

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.

ximo’s picture

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()...

jbrown’s picture

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.

soxofaan’s picture

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."?

Gábor Hojtsy’s picture

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

catch’s picture

Status: Active » Needs review
FileSize
2.08 KB

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.

chx’s picture

Status: Needs review » Active

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.

jbrown’s picture

#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.

chx’s picture

Status: Active » Needs review

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

catch’s picture

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?

jbrown’s picture

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?

catch’s picture

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

ximo’s picture

#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..

jbrown’s picture

#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.

Gábor Hojtsy’s picture

Status: Needs review » Active

#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.

chx’s picture

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?

Gábor Hojtsy’s picture

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.

chx’s picture

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

mysql> select * from users where uid = 0;
Empty set (0.00 sec)
chx’s picture

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

chx’s picture

Status: Active » Needs review
FileSize
1.92 KB

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.

ximo’s picture

FileSize
2.98 KB

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.

soxofaan’s picture

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)

chx’s picture

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 .

ximo’s picture

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.

chx’s picture

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.

Gábor Hojtsy’s picture

Status: Needs review » Fixed

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

ximo’s picture

Status: Fixed » Needs review

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):

#0  user_load(0) called at [/Users/joakim/Sites/drupal/modules/user/user.module:1112]
#1  user_current_load(0) called at [/Users/joakim/Sites/drupal/includes/menu.inc:405]
#2  _menu_load_objects(...) called at [/Users/joakim/Sites/drupal/includes/menu.inc:630]
#3  _menu_link_translate(...) called at [/Users/joakim/Sites/drupal/includes/menu.inc:942]
#4  _menu_tree_check_access(...) called at [/Users/joakim/Sites/drupal/includes/menu.inc:931]
#5  menu_tree_check_access(...) called at [/Users/joakim/Sites/drupal/includes/menu.inc:887]
#6  menu_tree_page_data(navigation) called at [/Users/joakim/Sites/drupal/includes/menu.inc:686]
#7  menu_tree() called at [/Users/joakim/Sites/drupal/modules/user/user.module:732]
#8  user_block(view, 1) called at [(null):0]
#9  call_user_func_array(user_block, Array ([2] => view,[3] => 1)) called at [/Users/joakim/Sites/drupal/includes/module.inc:450]
#10 module_invoke(user, block, view, 1) called at [/Users/joakim/Sites/drupal/modules/block/block.module:455]
#11 block_list(left) called at [/Users/joakim/Sites/drupal/includes/theme.inc:1515]
#12 theme_blocks(left) called at [(null):0]
#13 call_user_func_array(theme_blocks, Array ([0] => left)) called at [/Users/joakim/Sites/drupal/includes/theme.inc:591]
#14 theme(blocks, left) called at [/Users/joakim/Sites/drupal/includes/theme.inc:1709]
#15 template_preprocess_page(...) called at [(null):0]
#16 call_user_func_array(template_preprocess_page, ...) called at [/Users/joakim/Sites/drupal/includes/theme.inc:632]
#17 theme(page, ...) called at [/Users/joakim/Sites/drupal/index.php:36]

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?

ximo’s picture

Status: Needs review » Fixed

Sorry, cross posted..

ximo’s picture

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

Anonymous’s picture

Status: Fixed » Closed (fixed)

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

keff’s picture

Status: Closed (fixed) » Active

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:

  db_query("SET SESSION sql_mode='NO_AUTO_VALUE_ON_ZERO'");
  db_query("UPDATE {users} SET uid = 0 WHERE name = '' AND pass = '' AND status = 0");

What do you think?

gpk’s picture

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).

keff’s picture

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.

gpk’s picture

Abbreviation for drupal_set_message().

jakraska’s picture

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

gpk’s picture

@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.

jakraska’s picture

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)

CREATE TABLE `users` (
  `uid` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(60) NOT NULL default '',
  `pass` varchar(32) NOT NULL default '',
  `mail` varchar(64) default '',
  `mode` tinyint(4) NOT NULL default '0',
  `sort` tinyint(4) default '0',
  `threshold` tinyint(4) default '0',
  `theme` varchar(255) NOT NULL default '',
  `signature` varchar(255) NOT NULL default '',
  `created` int(11) NOT NULL default '0',
  `access` int(11) NOT NULL default '0',
  `login` int(11) NOT NULL default '0',
  `status` tinyint(4) NOT NULL default '0',
  `timezone` varchar(8) default NULL,
  `language` varchar(12) NOT NULL default '',
  `picture` varchar(255) NOT NULL default '',
  `init` varchar(64) default '',
  `data` longtext,
  PRIMARY KEY  (`uid`),
  UNIQUE KEY `name` (`name`),
  KEY `access` (`access`),
  KEY `created` (`created`),
  KEY `mail` (`mail`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` VALUES (0, '', '', '', 0, 0, 0, '', '', 0, 0, 0, 0, NULL, '', '', '', NULL);
INSERT INTO `users` VALUES (1, 'XXX', 'XXX', 'XXX', 0, 0, 0, '', '', 12
18449654, 1218727402, 1218637193, 1, NULL, '', '', 'XXX', 'a:0:{}');
INSERT INTO `users` VALUES (4, 'XXX', 'XXX', 'XXX', 0, 0, 0, '', '', 12
18713157, 1218728815, 1218727482, 1, '-14400', '', '', 'XXX', 'a:1:{s:13:"form_build_id";s:37:"form-864d37
a001dcd406b8f79b53ae56f2ec";}');

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

gpk’s picture

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.

Squirrelly’s picture

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.

kay_v’s picture

subscribe

Crell’s picture

This is a problem for users of Navicat, too, not just phpMyAdmin. At least now I know what the deal is. :-)

It is still a problem, however, and we should try to come up with a better solution if we can. (aka Subscribe)

AltaVida’s picture

I've experienced this same problem with Navicat and PHPMyAdmin.

Subscribing.

alexanderpas’s picture

this problem is actually not in drupal, but in the (vendor-specific) implementation and weaktyping of auto-increment in MYSQL.

the only thing we could do, is to solve the most common problems, but we never be able to fix it completely by ourself.

0 (zero) is a valid positive/unsigned integer, and therefor should not be handled as NULL (missing)

I reallly hate '', 0, NULL and FALSE all evaluating to the same (in PHP, and in even more in MYSQL).

created a bug report for mysql: NO_AUTO_VALUE_ON_ZERO should be on by default.

anonymous user as 0 is the closest we have "SET and N/A" where NULL means "UNSET/MISSING", which then get's set by autoincrement.

In databases a field can have a null value. This is equivalent to the field not having a value. For numeric fields it is not the value zero. For text fields this is not blank nor the empty string.

http://en.wikipedia.org/wiki/0_(number)#Null_value

webchick’s picture

Since 7.x is in "anything goes" code thaw mode, I would love to see some patches that fix this properly, which sounds like it would be making uid 0 NULL or -1 or something similar.

alexanderpas’s picture

@webchick: won't work.
'', 0, -1, NULL and FALSE all trigger auto-increment

gpk’s picture

Version: 6.x-dev » 7.x-dev

Is there a way of simulating the autoincrement (which was in any case only added in 6.x) on the uid field, using DBTNG?

chx’s picture

Write a new sequences API. postgresql, native sequences, mysql just one table with autoincrement. Use this for user uid, simpletest id and actions id.

chx’s picture

Dave Reid’s picture

Issue tags: +anonymous users
AltaVida’s picture

An interesting thing I noticed about PHPMyAdmin and NO_AUTO_VALUE_ON_ZERO:

As mentioned, more recent versions of PHPMyAdmin add SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO" to the beginning of a dump file.

However if your database is large, on import you can use the "Partial Import" option which is described as "Allow interrupt of import in case script detects it is close to time limit. This might be good way to import large files, however it can break transactions."

This allows you to perform an import over multiple requests in case the PHP time limit is reached before the import completes. However, since it works by re-uploading the dump file each time (until the import is complete) and "resuming" the import from the last line processed in your dump file, your SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO" statement at the beginning of the file will get skipped after the first request and therefore you might see this problem (even though you might think you shouldn't).

I'm using PHPMyAdmin version 2.11.8.1.

Crell’s picture

I've found that Navicat will let you set that flag on a connection, but only if you have the necessary perms on the server. If you don't then, um, I'm not sure if you can setup an auto-execute query to run for the connection. Navicat support says not at this time.

Michael-IDA’s picture

cPanel hosing comments when doing a database copy is still a problem with:

MySQL version: 5.0.81-community
PHP version: 5.2.9
Kernel version: 2.6.18-128.1.10.el5
cPanel Version: 11.24.4-STABLE_36281

After manually reseting the first user in the "users" table to `uid` = '0' comments started showing back up.* The first user in my table has no name/pass/mail/etc. Hopefully that doesn't break anything else....

Sam

*For those unfamiliar with this:
cPanel => phpMyAdmin => click your database => click "users" => click "Browse" => on the first row (or user with no info) click the pencil (edit icon) => change "uid" to "0" (zero) => click "Go"

Edit:
I'll second what #45, jakraska - August 16, 2008 - 23:15 said:

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

My preference is make Anonymous users uid 2 ... Yeah a global find/replace for the hardcoded "if uid = 0" sucks, but this is multiple years old, still biting people with enough skill to track the problem down, and most likely driving people completely off of Drupal who don't have the skill to do more than point and click.

chx’s picture

Michael-IDA’s picture

Hi chx,

Works for me. Just need something that works, so that (I'm not using the word Stupid) Users don't drop and badmouth Drupal...

Sam

Dave Reid’s picture

Damien Tournoud’s picture

Status: Active » Closed (won't fix)

For D7, maybe we can do better on a few fronts: (1) run MySQL in NO_AUTO_VALUE_ON_ZERO (which would make its behavior more consistent with other database engines), and (2) re-implement a database engine-agnostic sequences API (see #356074: Provide a sequences API), and (3) remove this magic 0 line (see #350407: Anonymous should not appear in the users table at all)...

... but this is *definitely* not a Drupal bug.

Michael-IDA’s picture

Title: Anonymous user id breaks on MySQL export/import » Anonymous user id breaks on MySQL export/import (xID of Zero get's auto-incremented on external xSQL events)
Priority: Critical » Normal
Status: Closed (won't fix) » Active

Hi Damien,

(Without reading every comment on this page) No one says it's a problem with Drupal. It is definitively an SQL database engine issue, but, given that Drupal is using a known to auto-increment ID number, ultimately, it's in the Drupal realm to 'fix.'

(1) isn't the problem. It's not that Drupal is doing it, it is that external uses of MySQL are doing it on database copies, etc.
(2) would fix the problem, as long as there are no UID, NID, xID... fields that can be set to null/zero/(or anything an external use of xSQL will auto-increment)
(3) UID (xID) shouldn't be NULL either as that will create the same problems. (I'm told)

Personally I'm against 'homebuilt' sequences APIs, as there is no need to re-write what the database will do for us. My suggestion is still to eliminate Anon UID = Zero (and any other incrementable field from using NULL/Zero). But, from a 'user' standpoint, as long as I don't have to untangle the spaghetti, I don't care that much either.*

Updated Title to identify problem better. Changing priority from Critical to Normal, as there is a manual solution. Placing the status back to Active, as this still needs to be resolved in some fashion. Please don't close as "won't fix," at best, close with duplicate and point to a more inclusive bug.

Best all,
Sam

*Yup, I'm only in this thread, cause I had to "fix" this for one of my clients.

Damien Tournoud’s picture

Category: bug » support
Status: Active » Closed (works as designed)

Again, that's not a Drupal bug, so there is no point in keeping this open.

Michael-IDA’s picture

Status: Closed (works as designed) » Active

ultimately, it's in the Drupal realm to 'fix.'

gpk’s picture

@69, 62: you should find that simply visiting the status report page fixes the problem if present (http://api.drupal.org/api/function/system_status/6).

However one doesn't necessarily know to visit that page.. :P i.e. at present we have at best a partial solution.

Crell’s picture

Honestly I'm more than happy to just not use primary keys of 0 as magic values. That's a silly idea anyway, IMO.

alexanderpas’s picture

posted an upstream progress report request on http://bugs.mysql.com/bug.php?id=41848
It is currently classified as S2 (Serious, means Represents a severe loss of service, significant functionality is missing; but a workaround is available.) D5 (Feature Request)

also, this still counts if :

anonymous user as 0 is the closest we have "SET and N/A" where NULL means "UNSET/MISSING", which then get's set by autoincrement.

In databases a field can have a null value. This is equivalent to the field not having a value. For numeric fields it is not the value zero. For text fields this is not blank nor the empty string.

http://en.wikipedia.org/wiki/0_(number)#Null_value

also, having 0 preforming this function is very important when working with referential integrity!

I think the only solution we currently have, is to SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO" before running any query on a mysqldatabase.

hgmichna’s picture

Maybe you'll find this entertaining or funny, but here's a little story.

Yesterday I looked into the database to do some content fixing there. When I looked at the users table, I saw this mostly empty uid = 0 line and was so sure that it must be a defect that I instantly deleted it. It was obviously (for me) not a valid entry in the table, because essential, actually nearly all, field contents were missing.

Today I updated an access control module and found that the "rebuild permissions" function would hang. This led me to a search of drupal.org, where I found this discussion.

Of course I immediately reentered the user 0 record, but I found that the auto-increment function always bumps the uid up to the highest value plus 1. I first thought I would be unable to recreate the missing record.

Then I found that I can enter such a record, then later change the uid to 0. This also solved my rebuild-permissions problem. After I had re-added the uid = 0 record, I could rebuild the permissions within seconds.

Do not put this away as another semi-stupid operator's action. I've had to delve into the database more often than once to fix defects caused by some module, some external disturbance, and by Drupal itself or the interaction of Drupal with the underlying and changing systems. Nobody can say with a good conscience that a Drupal operator must never go into the database and fix things.

My personal thought is that that uid = 0 record is not a good idea. If anybody can change Drupal in this respect, avoid the zero and give the record some sensible content, so everybody looking into the database table understands what the record is good for.

TC44’s picture

Subscribing - same problem here (I use Navicat).

Crell’s picture

Version: 7.x-dev » 6.x-dev

This was fixed in an odd way for D7 by moving uid from an auto-inc to a manual serial: #356074: Provide a sequences API.

It's up to Gabor if he wants to do anything about it in Drupal 6. Not sure if there's anything to be done, but this issue should no longer exist in Drupal 7.

matthieu@ripr’s picture

Comment deleted because stupid

kenorb’s picture

nullpainter’s picture

Title: Anonymous user id breaks on MySQL export/import (xID of Zero get's auto-incremented on external xSQL events) » Fundamental point being missed

I am speaking as someone who is primarily an enterprise Java developer and SQL Server user, so my view on the world may be slightly different (I know that it certainly is for the Drupal team's laughably out-of-date views on OO), but why in God's name does Drupal apply business logic to a primary key? This is a absurd design decision. A primary key is set by the database for purposes of joins, unique row identification, indices etcetera. It should NEVER be used to assign business meaning (i.e., user with uid = 1 is administrator, user with uid = 0 is anonymous).

I realise I'm most likely pissing into the wind here, but this design decision, combined with the 'bug' (FYI, I have never seen a primary key of 0 before in a database so I'm not surprised that MySQL often has issues) has cost me time and cost my employer money, and I know has cost other people time and money.

The fundamental issue is nothing to do with database behaviour. The fundamental issue is that Drupal misuses the very purpose of primary keys. If you need to determine if user X is an administrator or user Y is anonymous, add another column. Problem solved.

M

PS. Sorry for inadvertently changing the title for all subsequent posts (!) - was certainly not intentional.

Mark Trapp’s picture

Title: Fundamental point being missed » Anonymous user id breaks on MySQL export/import (xID of Zero get's auto-incremented on external xSQL events)
chx’s picture

Status: Active » Closed (won't fix)

This issue is over. D7 has a sort of a solution, D6 can solve this by UPDATE uid = uid - uid WHERE name = '' after import, D8 will probably handle anonymous users differently. Or not :) but anyways, #78 was a very "useful" lecture by painting Drupal developers as patently stupid and not offering a resolution for either D6 or D7 both of which are schema frozen...

Dave Reid’s picture

@nullpainter: Please go back to Java. Thanks.

hgmichna’s picture

Dave, don't dismiss his message outright. I think he is basically correct.

Whether or not Drupal's design should be changed now is another question, namely whether it is worth the effort to make a rather fundamental change to the database. Maybe, maybe not.