Take the 2-minute tour ×
Drupal Answers is a question and answer site for Drupal developers and administrators. It's 100% free, no registration required.

For reasons I can't understand, I am unable to create users in my Drupal installation (which wasn't a problem last time I tried).

If I try in the UI (at /admin/people/create) or if I try with drush user-create, in both cases the error message I get reads like:

WD rules: Unable to modify data "account:field-user-image:alt": Invalid data value given.
↪  Be sure it matches [warning] the required data type and format.
WD rules: Unable to evaluate action data_set.
WD user: PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate
↪ entry '187810' for key[error] 'PRIMARY': INSERT INTO {users} (uid, name, pass, mail,
↪ created, access, status, data) VALUES (:db_insert_placeholder_0,
↪ :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3,
↪ :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6,
↪ :db_insert_placeholder_7);
Array
(
    [:db_insert_placeholder_0] => 187810
    [:db_insert_placeholder_1] => xxxxxxx@xxxxxxx.com
    [:db_insert_placeholder_2] => $S$XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX.XXXXXXXXXXXXXXXX
    [:db_insert_placeholder_3] => xxxxxxx@xxxxxxx.com
    [:db_insert_placeholder_4] => 1329491489
    [:db_insert_placeholder_5] => 0
    [:db_insert_placeholder_6] => 1
    [:db_insert_placeholder_7] => a:1:{s:34:"user_relationships_ui_auto_approve";a:0:{}}
)
 in drupal_write_record() (line 6975 of /var/www/html/includes/common.inc).
WD php: PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate
↪ entry '187810' for key[error] 'PRIMARY': INSERT INTO {users} (uid, name, pass, mail,
↪ created, access, status, data) VALUES (:db_insert_placeholder_0,
↪ :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3,
↪ :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6,
↪ :db_insert_placeholder_7);
Array
(
    [:db_insert_placeholder_0] => 187810
    [:db_insert_placeholder_1] => xxxxxxx@xxxxxxx.com
    [:db_insert_placeholder_2] => $S$XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX.XXXXXXXXXXXXXXXX
    [:db_insert_placeholder_3] => xxxxxxx@xxxxxxx.com
    [:db_insert_placeholder_4] => 1329491489
    [:db_insert_placeholder_5] => 0
    [:db_insert_placeholder_6] => 1
    [:db_insert_placeholder_7] => a:1:{s:34:"user_relationships_ui_auto_approve";a:0:{}}
)
 in drupal_write_record() (line 6975 of /var/www/html/includes/common.inc).
PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry
↪ '187810' for key 'PRIMARY': INSERT INTO {users} (uid, name,
↪ pass, mail, created, access, status, data) VALUES (:db_insert_placeholder_0,
↪ :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3,
↪ :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6,
↪ :db_insert_placeholder_7); Array
(
    [:db_insert_placeholder_0] => 187810
    [:db_insert_placeholder_1] => xxxxxxx@xxxxxxx.com
    [:db_insert_placeholder_2] => $S$XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX.XXXXXXXXXXXXXXXX
    [:db_insert_placeholder_3] => xxxxxxx@xxxxxxx.com
    [:db_insert_placeholder_4] => 1329491489
    [:db_insert_placeholder_5] => 0
    [:db_insert_placeholder_6] => 1
    [:db_insert_placeholder_7] => a:1:{s:34:"user_relationships_ui_auto_approve";a:0:{}}
)
 in drupal_write_record() (line 6975 of /var/www/html/includes/common.inc).
Drush command terminated abnormally due to an unrecoverable error.

(where indicates that I've wrapped lines for legibility).

Now I can see that this is because it is trying to enter a duplicate users.uid into the database, but I can't work out why that would be the case. At no point have we manually entered users.uid values, so the autoincrement should just be doing its thing.

Is there something I'm missing? Is there some way for me to reset something so that Drupal (or mySQL) just resets its autoincrement value and tries to do the insert with a valid UID?

Alternately, should I just mess with the database manually? It'd be awkward to do, as there are a surprising number of fields to have to mess with, but I could do that if it's easier?

Update: A friend suggested running mysqlcheck, but that is passing ok. Also, Clive's suggestion of resetting the autoincrement for the table doesn't seem to have resolved the problem.

share|improve this question
    
What's the very first line doing? Do you have Rules triggers on user creation? Try disabling rules. –  Letharion Feb 17 '12 at 15:50
    
As far as I am aware, the first line is just a validation error message for one of the fields I'm not specifying. –  Owen Blacker Feb 17 '12 at 16:10
add comment

3 Answers 3

up vote 3 down vote accepted

After a really long time getting really frustrated with this, I've just fixed it with one line of SQL. (Some days I really hate computers.)

The database table sequences contains one row and one column. This sequences.value is the autoincrement ID that is used for (amongst other things) new user accounts.

I don't know why it was wrong or how it got wrong. But

mysql> update sequences set value = 200000;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

and suddenly user account creation works again.

Did I mention that some days I really hate computers? ;o)

share|improve this answer
    
Except that our daily "make sure the user database is up-to-date with the third party it comes from" script has run and now it doesn't work again. sequences.value is 200083 and the ID that user creation is trying with is 200004. Stupid autoincrement-misuse! –  Owen Blacker Mar 1 '12 at 12:30
add comment

I encountered a similar problem and traced it to Rules. A thread about rules causing this error can be found here: http://drupal.org/node/1169800

share|improve this answer
    
Fixing the rules problem at the start of the error log still leaves me with the PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '200015' for key 'PRIMARY' message afterwards. Which is all the more confusing because sequences.value is 200266, the same as the value I get from select max(uid) from users;. –  Owen Blacker Mar 5 '12 at 14:53
add comment

You can reset the auto increment value fairly easily:

ALTER TABLE users AUTO_INCREMENT = 187811

It is a bit strange though, when you add a user the uid is determined with the line of code:

$account->uid = db_next_id(db_query('SELECT MAX(uid) FROM {users}')->fetchField());

As that grabs the maximum user id out of the database and uses PDO to get the next available ID I'm not sure if the auto increment field actually comes into it.

Either way it's worth running the alter query against the table, it might do the trick.

share|improve this answer
    
As you feared, it is still grabbing a (different) uid from the table. I reset with alter table users auto_increment = 200000; (as the max(uid) was 190018), but trying again and it's now trying a uid of 187818. –  Owen Blacker Feb 17 '12 at 16:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.