By stevenwhite on

Anyone know how to fix this?. This was the error message I received when I checked my Drupal database from CPanel on my host. "Repairing" the database did not do get rid of the error.

- Thanks, Steve

⋅ Categories: Drupal 6.x

Comments

stevenwhite’s picture

stevenwhite commented

Hi everyone

This error is still perplexing me and I've thought of everything I could possibly do to fix it with no luck so far. Here's the error I get when I do a mysql "check database" from within CPanel...

anxietyz_drpl1.users
warning : Found row where the auto_increment column has the value 0
status : OK
anxietyz_drpl1.users_roles OK

As you can see, the table's name (with the warning) is invisible but it is located between the tables "users" and "users"roles". How do I edit this table if I don't even know what it's name is?. I even checked ALL of the extra MODS beginning with the letter "U" I've installed since I've had Drupal thinking that perhaps I had installed a MOD which created a table but then deleted it but the table was still there but empty with a value of zero and I couldn't find any.

I'm pulling my hair out on this one. Please help if you can.

- Thanks, Steve

stevenwhite’s picture

stevenwhite commented

>warning : Found row where the auto_increment column has the value 0

I guess it gets down to this: If someone here can just tell me how I can find the table ROW or the name of the table ROW in the MySQL database where the auto_increment column has the value 0, then I can simply delete it. Problem solved! (hopefully).

Is there a command/query I can type in or something?.

I'm using CPanel/phpMyAdmin with Drupal v6.2 if that helps any.

I'm pulling my hair out by the roots and getting desperate now )-:

- Thanks so MUCH!, Steve

kayaker’s picture

kayaker commented

I think the message is telling you that users table generates the warning. Users->UID is an autoincrement column - see if it has a "0" row.

FWIW, my D5.7 Users table has an empty "0" row whereas neither of my D6.2 Users tables have an empty row.

Since it is only a warning, I don't know if it's worth messing with. I know you back up often, so I guess you could experiment.

__________________________________________________________________________________

Aweigh - not all who wander are lost.

stevenwhite’s picture

stevenwhite commented

Thanks a BUNCH!. That definitely helped me understand what is going on here. When I clicked on the USERS table and clicked on the "browse" icon, I could see a UID with a value of zero and there was no other information about it (ie; everything was empty in that area).

When I clicked on the "structure" icon, I could see the following at the very top...

Field: uid
Type: int(10)
Collation: (no info)
Attributes: UNSIGNED
Null: No
Default: (no info)
Extra: auto_increment
Action: (action icons)

I want to fix this error but not sure what exactly to delete or how many things to delete.

Also (just curious) could a user who signed up but never actually created any content (ie; a blog, book, etc) cause this type of error?.

UPDATE: I juct clicked on the "browse" icon, deleted the completely empty row, ran a DB repair, ran a DB check and the error was gone. Now that I think about it, when I first installed Drupal I created a test user and then...well...I don't remember all of the details but I kind of wondered what became of that "test user". Maybe that's the thing I just deleted.

Thanks again, Steve

Tommy Sundstrom’s picture

Tommy Sundstrom commented

Removing user 0 is not a good idea.

UID 0 is the "superuser" (at least on older versions of Drupal). Removing it may lock you out of the system, if something goes wrong with the permission settings for your other identities.

To use a 0 in an autoincrement column is not the best of ideas, but does no harm as far as I know. Anyhow, I'm glad to hear that it is changed in Drupal 6 - but if you are migrating from an earlier version, you probably still will find a user 0 in the database.

stevenwhite’s picture

stevenwhite commented

Hi, thanks for that. Is there a way to re-add user 0 and could you please tell me how to do that?. I started out with a fresh install of Drupal 6.1 and then upgraded to v6.2. Am I ok?. I'm new to Drupal (only been using it one week so I'm not really aware of what has changed or not changed between versions).

Incidentally, my admin node (where I login as admin) is /node/1. Do I still need the superuser/user 0?

- Regards, Steve

Tommy Sundstrom’s picture

Tommy Sundstrom commented

As far as I understand it (though my understanding of Drupal is quite shallow) Drupal 6 is using 1 instead of 0 for the superuser, if it is a fresh install. It's only if you have upgraded from earlier versions that you will find a user 0. So I don't think you have anything to worry about.

tjharman’s picture

tjharman commented

I keep getting this message every night (I run some tasks on the DB)

I removed the User ID of 0, thinking it'd be harmless. It wasn't, all of the anonymous comments on my site became hidden! Once I backed out the change, the comments reappeared.

deekayen’s picture

deekayen commented

Drupal 6 & 7 use 0 for anonymous and 1 for superuser.

kurokikaze’s picture

kurokikaze commented

Drupal 5 too.

Bèr Kessels’s picture

Bèr Kessels commented

Just for the record: UID 0 is not Superuser. It is the anonymous user. Superuser is the user with UID 1.

gaslkn’s picture

gaslkn commented

This is "anonymous user" pseudo-user record. I was surprised when discovered this, too. Here is the result of my research:
Drupal user 0 vs Mysql autoincrement

Logrise’s picture

Logrise commented

Have the same warning. I think this mistake having all drupal systems. This need to be fix in core.

Found row where the auto_increment column has the value 0

ericxb’s picture

ericxb commented

There is an explanation in the mysql docs: http://dev.mysql.com/doc/refman/5.0/en/create-table.html. The auto-increment attribute does just as you expect: it causes the value of a field to increment each time a new record is added. This is really handy for things like user id's. Both the starting value and the incremental amount are adjustable and can be set when you define the table. By default, mysql will start with the value 1 (hence our troubles) and increment the value by 1 each time there is a new record. Value zero is a special case. The special mysql variable auto_increment_offset (which is the starting point for any auto-increment field) is not allowed to be zero. I couldn't find out why exactly; but I suspect the decision was based on the fact that 0 is frequently "special." It is the case that you can configure mysql to skip the auto-increment when you supply a zero value (thereby allowing multiple records with the same value in a field which is normally a unique key); and, of course, zero == false in many languages. Because the Drupal install manually assigns the values of the first two users (anonymous and admin) as 0 and 1; auto-increment is not invoked. Unfortunately forever after, mysql will post a warning about the 0 value basically because it is outside of the pre-defined range.

While this is just a warning and the situation is harmless, it's easier to live with one's alarm system (I run a myisamchk on my tables every night) when it only squawks if there is real trouble. I think the only solution is to renumber uid 0 to some positive integer and update all the records in all the related tables that point to it. Not impossible; but a bit daunting unless the drupal coding conventions strongly suggest that all such related field are also named uid.

If I have time, I will track down where the initial users are created/inserted and see if it is practical to skip the zero uid and submit a patch. This is obviously not a critical bug; but it does tend to set off unnecessary alarms. Maybe we should all switch to postgresql?

flaviovs’s picture

flaviovs commented

(...)I think the only solution is to renumber uid 0 to some positive integer and update all the records in all the related tables that point to it(...)

As said previously, uid 0 is the standard anonymous "user". There are lots of lots of code in modules and perhaps in core that do things like:

if ( !$user->uid )
     drupal_set_message("Hi there Mr. Anonymous");

So reassigning uid of the anonymous user may not be an easy task. At least it cannot be done only in the database.

FVS - https://dmarcpal.com - https://send2t.com

Anonymous’s picture

Anonymous (not verified) commented

i found same observation when i checked my database via phpmyadmin.
dont know what long-term implication is.
my users are able to login/out and access the pages BTW.

*not a techie*

vicmara’s picture

vicmara commented

This is due to (IMO) very stupid and annoying MySQL feature of autofilling auto_increment values in case of "NULL" *AND* "0" values entered.

See here:
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_no_a...
for details.

You can change the SQL mode at runtime by using a "SET [GLOBAL|SESSION] sql_mode='NO_AUTO_VALUE_ON_ZERO' " statement to set the sql_mode system value.

If it is your server, you can define GLOBAL, but it is not recommended (one stupidness usually makes many followers).

However, you can just safely change YOUR SESSION sql mode... using mysql_query('SET SESSION sql_mode=\'NO_AUTO_VALUE_ON_ZERO\'');
I suggest this to be implemented into most scripts in order to avoid problems.

So to all of you, don't worry of this warning at all. That's the best you can do now (with the exception of spamming MySQL developers to deprecate this warning *OR* forking your own build of MySQL which is highly recommended against).

andrenoronha’s picture

andrenoronha commented

can someone tell me if this has something to do with this issue?

http://drupal.org/node/531400

hailaz’s picture

hailaz commented

Repairing the database worked for me, jfyi

ghmercado’s picture

ghmercado commented

I am experiencing an incredibly frustrating issue and this might be related to 'Found row where the auto_increment column has the value 0'

all of a sudden all content on my site is not visible to anonymous users except comments. Blogposts, cck content types, everything can only be viewed by auth users.

i upgraded to 6.24 and updated my varnish module. I thought it was a varnish issue but I checked the DB and saw the dreaded 'Found row where the auto_increment column has the value 0' notice. Repairing the DB did not help but it only went away after i optimized it in phpmyadmin.

i can login but every page i try to click requires me to login each and everytime. it is frustrating. been using drupal since 4.7 I've never encountered anything like this.

tjharman’s picture

tjharman commented

I'm sorry but this problem you're seeing is unrelated. It sounds like the permissions table on your site is messed up.

Have you tried to rebuild the permissions?

Visit

http:///admin/content/node-settings and if there's a Rebuild Permissions button (there might not be) click it!

ghmercado’s picture

ghmercado commented

yeah it turned out to be a simple permissions issue. It happened to coincide with the need to upgrade to drupal 6.24 and the fact that there was no pressflow 6.24 available, so the 'security upgrade required' message was still there. Am also having issues with varnish as well.

i also got rid of the ''Found row where the auto_increment column has the value 0'' issue by optimizing my db in phpmyadmin.

so the permissions issue is ok now. never had to do that before.