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;
Result:

id
1

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;
Result:

id
0

Now, try to copy the table:

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

id
1

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;
Result:

id
0
1

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.

  • Print this article!
  • Digg
  • del.icio.us
  • Facebook
  • Mixx
  • Google Bookmarks
  • E-mail this story to a friend!
  • LinkArena
  • LinkedIn
  • MisterWong
  • StumbleUpon
  • Technorati
  • Twitter