Adding a new column to users table: db_drop_field errror

Question

I'm trying to add a column to the 'users' table. When I install my module, the column gets created and when I uninstall it gets deleted.

The issue is that after I uninstalled, I run a 'drush cc all' and I receive this error:

WD php: PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown[error] column 'base.firstname' in 'field list': SELECT base.uid AS uid, base.name AS name, base.pass AS pass, base.mail AS mail, base.theme AS theme, base.signature AS signature, base.signature_format AS signature_format, base.created AS created, base.access AS access, base.login AS login, base.status AS status, base.timezone AS timezone, base.language AS language, base.picture AS picture, base.init AS init, base.data AS data, base.firstname AS firstname FROM {users} base WHERE (base.uid IN (:db_condition_placeholder_0)) ; Array ( [:db_condition_placeholder_0] => 0 ) in DrupalDefaultEntityController->load() (line 193 of /var/www/includes/entity.inc).

Here is my code inside the .install:

function mymodule_schema_alter(&$schema) {
  $schema['users']['fields']['firstname'] = array(
    'type' => 'varchar',
    'length' => 60,
    'not null' => FALSE,
    'default' => '',
    'description' => 'Field added by profile common module',
  );
}

function  mymodule_install() {
  $schema = drupal_get_schema('users');
  db_add_field('users', 'firstname', $schema['fields']['firstname']);
}

function  mymodule_uninstall() {
  db_drop_field('users', 'firstname');
}

Thanks.

Answer 1

There is the difference between hook_schema() and hook_schema_alter(): The first is used to create a database table when a module is installed, but the second is not used to alter an existing database table when a module is installed. hook_schema_alter() is used to alter the values returned from hook_schema() to let other modules know an existing database table (created from a module) has been altered (from another module).

What you need to to is:

• Implement hook_schema_alter() to make public the changes you made to a database table created from another module
• Make the changes in that database table, in hook_install() or hook_update_N()

The reason you need to alter what returned from hook_schema() is that there are functions that write on the database which use the information returned from hook_schema(), in primis drupal_write_record().

  $schema = drupal_get_schema($table);
  if (empty($schema)) {
    return FALSE;
  }

  $object = (object) $record;
  $fields = array();

  // Go through the schema to determine fields to write.
  foreach ($schema ['fields'] as $field => $info) {
    if ($info ['type'] == 'serial') {
      // Skip serial types if we are updating.
      if (!empty($primary_keys)) {
        continue;
      }
      // Track serial field so we can helpfully populate them after the query.
      // NOTE: Each table should come with one serial field only.
      $serial = $field;
    }

    // …
  } 

Without hook_schema_alter(), which is invoked from drupal_get_schema(), such functions would not be able to correctly write on the database tables.