User creation failing due to integrity constraint violation

Question

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] => [email protected]
    [:db_insert_placeholder_2] => $S$XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX.XXXXXXXXXXXXXXXX
    [:db_insert_placeholder_3] => [email protected]
    [: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] => [email protected]
    [:db_insert_placeholder_2] => $S$XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX.XXXXXXXXXXXXXXXX
    [:db_insert_placeholder_3] => [email protected]
    [: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] => [email protected]
    [:db_insert_placeholder_2] => $S$XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX.XXXXXXXXXXXXXXXX
    [:db_insert_placeholder_3] => [email protected]
    [: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.

Answer 1

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)

Answer 2

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

Answer 3

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.