How do you add an incrementing primary key to an existing table? 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' =&gt; array(
      'uid' =&gt; array(
        'type' =&gt; 'int',
        'not null' =&gt; TRUE,
        'description' =&gt; 'uid of user.',
      ),
      'login' =&gt; array(
        'type' =&gt; 'int',
        'not null' =&gt; TRUE,
        'description' =&gt; "Timestamp for user's login.",
      ),
      'hostname' =&gt; array(
        'type' =&gt; 'varchar',
        'length' =&gt; 128,
        'not null' =&gt; TRUE,
        'default' =&gt; '',
        'description' =&gt; "The user's host name.",
      ),
      'one_time' =&gt; array(
        'type' =&gt; 'int',
        'unsigned' =&gt; TRUE,
        'not null' =&gt; TRUE,
        'default' =&gt; 0,
        'size' =&gt; 'tiny',
        'description' =&gt; 'Indicates whether the login was from a one-time login link (e.g. password reset).',
      ),
      'user_agent' =&gt; array(
        'type' =&gt; 'varchar',
        'length' =&gt; 256,
        'not null' =&gt; TRUE,
        'default' =&gt; '',
        'description' =&gt; 'User agent (i.e. browser) of the device used during the login.',
      ),
    ),
    'indexes' =&gt; array(
      'login_history_uid' =&gt; array('uid'),
      'login_history_onetime' =&gt; array('one_time'),
      'login_history_uid_host' =&gt; 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' =&gt; 'The primary identifier for a login.',
        'type' =&gt; 'serial',
        'unsigned' =&gt; TRUE,
        'not null' =&gt; TRUE,
      ),
      array('primary key' =&gt; 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.
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.