Recently I have been trying to duplicate Drupal’s database using MySQL queries. To copy MySQL table, it is enough to execute the following queries:

CREATE TABLE table_new LIKE table_old;
INSERT INTO table_new SELECT * FROM table_old;

These queries will preserve original table structure and indexes. Everything looks OK until you try to copy Drupal’s “users” table.
This table has the “uid PRIMARY KEY AUTO_INCREMENT” field, but, unfortunately, Drupal developers decided to use special UID=0 for anonymous pseudo-user that must exist in this table. When copying that table with value 0 in auto-increment field using queries above, one will get “duplicate key” errors because copying a table by “INSERT INTO .. SELECT FROM …” will result in assignment of new auto-incremented value to the autoinc field instead of zero!

It is easy to reproduce:

CREATE TABLE t (id INT PRIMARY KEY AUTO_INCREMENT);
INSERT INTO t (id) VALUES (0); -- 1 rows affected
SELECT * FROM t;

I got 1 instead of 0, but It’s OK, I can update later , like Drupal does:

UPDATE t SET id=0;
SELECT * FROM t;

Now, try to copy the table:

CREATE TABLE t2 LIKE t;
INSERT INTO t2 SELECT * FROM t;
SELECT * FROM t2;

So I get 1 instead of original 0. My data has been screwed completely (whole table contained single record with value 0, now the value is 1, data is lost ). Things go worse when there are multiple records in source table:

INSERT INTO t (id) VALUES (1);
SELECT * FROM t;

Now duplicate the “strange” table with values 0 and 1 in AUTO_INCREMENT field:

DROP TABLE IF EXISTS t2; -- drop the existing t2, I dont need it
CREATE TABLE t2 LIKE t;
INSERT INTO t2 SELECT * FROM t; -- produces error: Duplicate entry '1' for key 'PRIMARY'
SELECT * FROM t2; -- empty table

The stupid MySQL “feature” to replace explicitly specified “0″ with auto-incremented value meets here the stupid decision to use special “zero user” in Drupal database! OMFG…

Well, having discovered this mess, I took a look at Drupal source code where that zero user is getting created (modules/system/system.install file, Drupal 6.15):

  // 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'", '');

So these guys knew about the MySQL f__king behavior. They decided to “overcome” the problem by forcing 0 value in the autoincrement field by updating it after INSERT.

Now in order to copy the “users” table I had to write the following tricky piece of code:

  db_query("CREATE TABLE $tableTo LIKE $tableFrom");
  $where = '';
  if($tableFrom == "users") { // Handle the drupal zero user >:(
    $where = ' WHERE uid > 0';
  }
  db_query("INSERT INTO $tableTo SELECT * FROM $tableFrom $where");
  if($tableFrom == "users") { // Handle the drupal zero user
    db_query("INSERT INTO {$targetPrefix}users (name, pass, mail) VALUES('', '', '')");
    db_query("UPDATE {$targetPrefix}users SET uid = uid - uid WHERE name='' AND pass='' AND mail=''");
  }

This special zero-user and MySQL autoincrement issue together, I believe, are reason for many broken backups made by software, that is unaware of the problem. As of Drupal, IMO, usage of such bad practice is simply unacceptable.
Well, I hope that either MySQL or Drupal way of handling the above stuff will change. This will simplify things a lot.