How do you add an incrementing primary key to an existing table?

Question

I'm working on the contributed Login History module which has an existing table that does not have a primary key. I'd like to add a primary key to the table, but am struggle with the db_add_field syntax for upgrades. The existing table is:

/**
 * Implements hook_schema().
 */
function login_history_schema() {
  $schema['login_history'] = array(
    'fields' => array(
      'uid' => array(
        'type' => 'int',
        'not null' => TRUE,
        'description' => 'uid of user.',
      ),
      'login' => array(
        'type' => 'int',
        'not null' => TRUE,
        'description' => "Timestamp for user's login.",
      ),
      'hostname' => array(
        'type' => 'varchar',
        'length' => 128,
        'not null' => TRUE,
        'default' => '',
        'description' => "The user's host name.",
      ),
      'one_time' => array(
        'type' => 'int',
        'unsigned' => TRUE,
        'not null' => TRUE,
        'default' => 0,
        'size' => 'tiny',
        'description' => 'Indicates whether the login was from a one-time login link (e.g. password reset).',
      ),
      'user_agent' => array(
        'type' => 'varchar',
        'length' => 256,
        'not null' => TRUE,
        'default' => '',
        'description' => 'User agent (i.e. browser) of the device used during the login.',
      ),
    ),
    'indexes' => array(
      'login_history_uid' => array('uid'),
      'login_history_onetime' => array('one_time'),
      'login_history_uid_host' => array('uid', 'hostname'),
    ),
  );
  return $schema;
}

And what I think should work to add a primary key is a hook_update like this:

/**
 * Add device_id and login_id columns.
 */
function login_history_update_7106() {
  if (!db_field_exists('login_history', 'login_id')) {
    db_add_field(
      'login_history',
      'login_id',
      array(
        'description' => 'The primary identifier for a login.',
        'type' => 'serial',
        'unsigned' => TRUE,
        'not null' => TRUE,
      ),
      array('primary key' => array('login_id')));
  }
}

However, that results in this error:

SQLSTATE[42000]: Syntax error or access violation: 1171 All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead                                                                                                     [error]

I've tried:

• Reading and re-reading the docs for db_add_field and db_change_field
• Using db_add_field to add an empty int field and then using db_change_field to make it a serial primary key.
• Using db_add_field without the 4th parameter that sets the new primary keys and then trying to use db_add_primary_key('login_history', array('login_id')) but that gives an error before the field is even added saying that auto_increment fields have to be part of a unique key.

I've grepped core and some contrib looking for instances of adding an auto increment primary key and can't find any for Drupal 7.

Answer 1

This actually seems to be an issue in Drupal Core where creating a serial field on an existing table is not possible in MySQL 5.7+ without first creating an int field and converting that to autoincrement (this issue does not show up in MySQL 5.6 and below). Please see https://www.drupal.org/node/2615496 for a patch that fixes this issue for new releases of MySQL. It might be worthwhile to update that patch to be rolled into 8.x so it will allow us to patch 7.x.

Answer 2

I believe the problem here is that db_add_field() doesn't insert any data in the new column unless you provide a 'default' or 'initial' key in the spec parameter. But those only take a single value, which is inappropriate for a serial column.

You'll have to accomplish this in a couple of steps: create a new integer field without an index, insert unique data in it (doesn't have to be strictly auto-incremented, but that looks nicer), and then use db_change_field() to make it serial (including the necessary index).

If that doesn't work for some reason, you can create the entire table anew with the auto-increment column, then copy the data to the new table so that it generates your primary key data automatically. Depending on how big {login_history} is, that might be something you do in a batch.

Answer 3

This will add an auto increment PRIMARY field to your table with incremental values againt existin records.

/**
 * Add device_id and login_id columns.
 */
function login_history_update_7106() {
  db_query('ALTER TABLE tbl ADD id INT PRIMARY KEY AUTO_INCREMENT');
}

Answer 4

The previous answers already describe the reason for the issue - you are not allowed to add a serial field, because db_add_field cannot add the index at the same time. The suggested patch is not likely to make it into core in the foreseeable future. That is why you need to add the field as int first, and then change it to serial and add the primary key.

Changing the empty int field into serial will automatically fill it with values, but they may not match the order you expect.

Filling the values may be challenging if you do not have proper unique keys to sort by. Using a temporal table for filling the values with the correct order is a neat trick.

Assuming that you have updated hook_schema() with the new serial field and primary key definition, here is a code snippet for D8:

use Drupal\Core\Database\Database;

/**
 * Add login_id serial column.
 */
function login_history_update_8106() {
  $db = Database::getConnection();
  $schema = $db->schema();
  $spec = login_history_schema();
  $table_definition = $spec['login_history'];

  if (!$schema->fieldExists('login_history', 'login_id')) {
    // Fill-in the auto-increment values in a temp table.
    $schema->createTable('login_history_tmp', $table_definition);
    $db->query("INSERT INTO {login_history_tmp} (<all fields here except for login_id>)
      SELECT <all fields here except for login_id>
        FROM {login_history}
        ORDER BY created");
    // Rename the temp table as primary.
    $schema->dropTable('login_history');
    $schema->renameTable('login_history_tmp', 'login_history');
  }
}