Error in creation af table 'Users'

Subscribing. I stumbled across this issue while working on a Demo.module issue.

Drupal 5 didn't have auto_increment set on the uid column, that's why exporting/importing database dumps did work.

Setting to critical, since creating a 1:1 database backup seems to be not possible anymore with Drupal 6, thanks to super-duper coding tricks in system.install:

  // Inserting uid 0 here confuses MySQL -- the next user might be created as
  // uid 2 which is not what we want. So we insert the first user here, the
  // anonymous user. uid is 1 here for now, but very soon it will be changed
  // to 0.
  db_query("INSERT INTO {users} (name, mail) VALUES('%s', '%s')", '', '');
  // We need some placeholders here as name and mail are uniques and data is
  // presumed to be a serialized array. Install will change uid 1 immediately
  // anyways. So we insert the superuser here, the uid is 2 here for now, but
  // very soon it will be changed to 1.
  db_query("INSERT INTO {users} (name, mail, created, data) VALUES('%s', '%s', %d, '%s')", 'placeholder-for-uid-1', 'placeholder-for-uid-1', time(), serialize(array()));
  // This sets the above two users uid 0 (anonymous). We avoid an explicit 0
  // otherwise MySQL might insert the next auto_increment value.
  db_query("UPDATE {users} SET uid = uid - uid WHERE name = '%s'", '');
  // This sets uid 1 (superuser). We skip uid 2 but that's not a big problem.
  db_query("UPDATE {users} SET uid = 1 WHERE name = '%s'", 'placeholder-for-uid-1');

Two ways to reproduce:

1. Create a database dump using phpMyAdmin but don't add AUTO_INCREMENT values to the CREATE TABLE statement: the import will break because the first row (uid 0) will be assigned uid 1, which is already taken (duplicate key).
2. Create a database dump with AUTO_INCREMENT values: the first row will be assigned the next free id. This works, but will leave you with a system that doesn't have the anonymous user anymore.

Drupal does not "make database copies for another site", as the reporter mentioned, so the bug manifests itself out of Drupal. It is a bad MySQL export behavior and we already over analyzed it here: http://drupal.org/node/204411 Basically, if you tell MySQL to use NO_AUTO_VALUE_ON_ZERO mode, it is fine. If not, we added a workaround to the status page, which tries to fix it for you after the database dump is imported.

Hi,

I am not sure if this is the right place to post this, but I really need help. I have inserted users through the database in user table, but know when a new user try to register the system gives an error because gives to this new user already taken id, and I don't know how to make the system give id number from the last one in the database. Does anyone have an idea how to fix this?