Reset maximum uid
Hi,
I have a db with just over 400 users. But any new user gets a very high integer for uid (4294967295) which seems to be the highest possible value for the field. That means no new users can be created. How can I reset the next integer Drupal should take for inserting new ids?
I think the reason why this is happening is because when making backups with phpmyadmin I'd have to change the 0 in user ids to something else or the process would crash. I think a user created an account before I reset the number back to 0 and now I seem to be stuck with it.
Here is the actual error :
* user warning: Duplicate entry '4294967295' for key 1 query: INSERT INTO users (name, mail, pass, status, language, init, created, access) VALUES ('a3', 'a3@sdfvsdfv.vasdvsd', '9d607a663f3e9b0a90c3c8d4426640dc', 1, 'en', 'a3@sdfvsdfv.vasdvsd', 1259185287, 1259185287) in /modules/user/user.module on line 327.
* Error saving user account.
I think that my tables last_insert_id() is broken. When calling the function from the console I get 0.
Thanks for any insight,
JM
EDIT
I'm going to track here anything I find which seems relevant to the issue
http://drupal.org/node/428318
Lots of links here
http://www.drupalwebsitecookbook.com/content/mysql-user-0-becomes-user-3...
First of all try repairing
First of all try repairing the table.
Having done that, what are the maximum 2 or 3 values of uid in the table? uid is an autoincrement field.
Sounds like you already have a user with uid 4294967295. If you change it to something more sane you should really modify any associated data in the database. See http://www.typo.co.il/~mooffie/tmp/schemagraph/output/schemagraph-2007-1... (link may not always work..)
gpk
----
www.alexoria.co.uk
Thanks for the help.
Thanks for the help, but this does not solve my problem.
This looks like a problem with the 0 uid.
I have created a new table from the result of "SHOW CREATE TABLE users" which I named users2.
With a php routine I selected each row from users, and inserted one by one into the new table users2.
The uid field is always set to NOT NULL whatever I do and I still get the same problem.
Hi, Yes the uid=0 does cause
Hi,
Yes the uid=0 does cause problems on some server configs. There is a Mysql option somewhere that makes it behave a bit better.
However I could probably be more helpful if I understood exactly what you are trying to do, or if you responded to my original questions ;)
>When calling the function from the console I get 0.
From http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#functi...
gpk
----
www.alexoria.co.uk
OK, thanks. I've run repair
OK, thanks.
I've run repair table.
If I understand your question correctly, the greatest uid values for the table are 403, 404, 405.
Adding a new record through the user creation process adds 4294967295 as the uid value.
At this point it becomes impossible to add another user - that's the max value for the field, I guess.
What I understand that you suggest I do is change this uid value from 4294967295 to, say, 406.
I've tried that, and once that's done, I can add a new user again. Unfortunately, the UID for the new user is 4294967295.
So it seems that is the only value the table will accept on INSERT.
I've tried running the default INSERT from the console "INSERT INTO users () VALUES ()" and I get the same result.
Very odd. Sounds as though
Very odd. Sounds as though the autoincrement on the uid field has gone barmy.
Anyway I think you need to get rid of the uid of 4294967295 and also reset the autoincrement value .. seems like MySQL will remember this. Have a look here for some suggestions: http://www.google.co.uk/#hl=en&source=hp&q=mysql+reset+auto+increment&bt...
gpk
----
www.alexoria.co.uk
Yes that did the trick,
Yes that did the trick, thanks for all your precious help.
When I ran show create table again on users I noticed after all the fields data :
ENGINE=MyISAM AUTO_INCREMENT=4294967296 DEFAULT CHARSET=utf8
So when I recreated my table I recreated the same.
The following query solved the issue :
ALTER TABLE users AUTO_INCREMENT = $last_uid
Where $last_uid is equal to the biggest valid uid in the table.
Everything seems to be back to normal!
:-)
:-)
gpk
----
www.alexoria.co.uk