By stormer on

Hi,

I was on the verge of launching my new site when the following problem cropped up:

user warning: Duplicate entry '0' for key 1 query: INSERT INTO users (name, mail, timezone, pass, init, status, created) VALUES ('langmeire', 'langmeire@gmail.com', 36000, 'e3a6432bdcd66517760f9957521ce560', 'xxx@gmail.com', 1, 1243564895) in /nfs/c02/h02/mnt/26164/domains/v2.xscriptorium.com/html/modules/user/user.module on line 327.
Error saving user account.

This occurs when visitors to the site try to register and also when Admin attempts to create a new user through the admin panel.

Line 327 in question above is $success = db_query('INSERT INTO {users} ('. implode(', ', $fields) .') VALUES ('. implode(', ', $s) .')', $values);.

Any help would be hugely appreciated.

⋅ Categories: Drupal 6.x

Comments

stormer’s picture

stormer commented

I've found a relationship to the problem. I went into the MySQL database and deleted all users except Admin. At this point I was able to create a new user via Drupal admin. Back in MySQL, that user is showing with the uid "0". However, when I now attempt to create a new user in the same way I get the same error message. So it would seem that Drupal is trying to generate each user with the ID "0". Of course this is just conjecture since I have virtually no experience with MySQL.

stormer’s picture

stormer commented

Further digging revealed that something, somewhere is not auto-incrementing - does anyone know where this would occur?

hychanhan’s picture

hychanhan commented

Someone know how to fix it?
When i start to register it always fail with error message appeared :

* user warning: Duplicate entry '0' for key 1 query: INSERT INTO khos_users (name, mail, timezone, pass, init, status, created) VALUES ('han0089', 'han0089@yahoo.com', 25200, '9cab589f3fe89130a17f9c7baa8123d8', 'han0089@yahoo.com', 1, 1249608555) in /home/virtualmin/wordforgefoundation.org/domains/khmeros.info/public_html/drupal612/modules/user/user.module on line 327.
* Error saving user account.

Please kindly help me now.

Thanks,
HY ChanHan

himagarwal’s picture

himagarwal commented

I'm having the same problem, can anyone please throw some light on this.

clivesj’s picture

clivesj commented

I had this same problem on my test-site which was a copy of my production-site.
By comparing the {user} from both sites I found the solution.
I don't know why, but some settings were corrupted on my test site.

For the user-table I had to change the settings for the column "uid".
The "Auto Increment" wasn't set,
Default value was set to "0", this should not be set.
The autoincrement value for the table was not set, I set this to the last uid-value of the last user,

and all was solved.

himagarwal’s picture

himagarwal commented

thank you clivesj, but I don't have any knowledge of sql.
I've attached the users database in tables. can you please let me know what change should i make there?

http://picturepush.com/public/2364349

clivesj’s picture

clivesj commented

Hello,
The part of the screen i need to see is not visible. It is hidden by the scroll.
I need to see the screen below the line "Print view" "Propose table structure".
There the keys of the table are listed with their parameters.
Maybe you can send the lower part of the screen.

WARNING: editing your SQL-table requires knowledge. Things can go very wrong :)

himagarwal’s picture

himagarwal commented

Here is the bottom part of the user table http://www2.picturepush.com/photo/a/2369250/img/Anonymous/database-user-...

I have deleted around 2000 users from my website which were actually spam bots. So currently there are 200+ users in my website and the latest user has 2417 as id.

clivesj’s picture

clivesj commented

It looks like after deletion of the spamusers the auto_increment value became corrupted.
The left lower box "Indexes" shows the keys and their values.
The "cardinality" is set to 211. You 'll have to google to see the meaning of this value. But is has something to do with the auoincrement.

It looks like creating a new user, creates an index (212??) that already excists.
there is a SQL to correct the auto_increment value, but I dont't use PHPMyAdmin al lot. So you better Google.

Also use the auto_increment is not set for "UID". You won't be able to switch this on in case there is a index-conflict. So you might try to focus on teh auto_increment value forst.
If it were a devolpement site I would try to set the cardinality to 3000 and use the "edit"link on the UID row to switch on auto_incerment (EXTRA) for that field.

I dont use PHPMy Admin but Navicat, this applicaties (GUI) gives me direct control over the auto_increment value......

I have attached a gif of a healty table for reference. This table contains 32 rows. Last uid is 34. (cardinality shows 33)
Rember the WARNING from the last post.
Good luck.
http://www1.picturepush.com/photo/a/2370109/img/2370109.gif

Maybe someone else can explain how "cardinality" is related to "auto_increment".