When writing hook_update_N() functions to create a new table, it seems natural and obvious to use the module's hook_schema() function to access the current definition of the table to avoid duplicating the table definition in the hook_update_N() function. However, you cannot safely use hook_schema() from within a hook_update_N() function.

Consider the following scenario: You created the module example. Initially, it has no tables, and its initial schema is empty. For version 6.x-1.5, you define a new table T. This means that you create the file M.install and, in it, create the function example_schema() which defines the table T:

function example_schema() {
  $schema['T'] = array(
    'fields' => array(
      'a' => array('type' => 'int'),
      'b' => array('type' => 'int'),
    )
  );
  return $schema;
}

You also write an update function to bring module M from version 6.x-1.0 to version 6.x-1.5 by creating table T:

function example_update_6100() {
  $schema = drupal_get_schema('M');
  $ret = array();
  db_create_table($ret, 'T', $schema['T']);  // DON'T DO THIS!
  return $ret;
}

For the 6.x-1.8 release, M no longer needs the field T.b. So, you update example_schema() to reflect the current schema and add an update function:

function example_schema() {
  $schema['T'] = array(
    'fields' => array(
      'a' => array('type' => 'int'),
      // NOTE: field 'b' has been removed
  ));
  return $schema;
}

function example_update_6100() {
  $schema = drupal_get_schema('M');
  $ret = array();
  db_create_table($ret, 'T', $schema['T']);  // DON'T DO THIS
  return $ret;
}

function example_update_6101() {
  $ret = array();
  db_drop_field($ret, 'T', 'b');
  return $ret;
}

Everything looks fine, but it isn't. The problem is that example_update_6101() cannot assume the field T.b exists. Dropping it can result in a failed SQL query. To understand why, consider two case histories:

1. User 1 installs version 6.x-1.0 of the module example, upgrades to version 6.x-1.5 when available, and upgrades to version 6.x-1.8 when available. This works fine.
2. User 2 installs version 6.x-1.0 of example.module, does not upgrade to version 6.x-1.5 when available, but does upgrade to version 6.x-1.8 when available. When user 2 upgrades to version 6.x-1.8 and runs update.php, example_update_6100() and example_update_6101() both run but example_schema() already contains the new structure. When example_update_6100() calls db_create_table($ret, $schema['T']), field T.b is not created. Therefore, when example_update_6101() tries to drop it, an error occurs.

The crux of the problem is that hook_schema() always defines the current schema for the module. However, hook_update_N() functions have to assume that the database matches the schema as it was when the hook_update_N() function was written.

The solution is simple. Do not refer to your own module's hook_schema() from within an update function. Instead, make everything explicit. In the above example, example_update_6100() has to specify the table structure itself, like this:

function example_update_6100() {
  $schema['T'] = array(
    'fields' => array(
      'a' => array('type' => 'int'),
      'b' => array('type' => 'int'),
  ));

  $ret = array();
  db_create_table($ret, 'T', $schema['T']);  // THIS IS SAFE BECAUSE $schema IS EXPLICIT
  return $ret;
}

This way, each function in the sequence of updates always knows exactly what it is doing. Sure, it looks redundant to include the definition of table T in both example_schema() and example_update_6100() but that is temporary. In the future when the definition of table T has radically changed, example_update_6100() will still contain its original definition and look nothing at all like the definition in example_schema().