db_change_field on a serial column in the primary key

I think the same problem comes up whenever you do anything to a serial column, even if it's still serial after db_change_field().

Maybe instead of calling db_drop_primary_key() beforehand, we should let db_change_field() decide whether it needs to use $new_keys. The MySQL version of db_change_field() can check the schema for the primary key in $new_keys and does nothing if it finds it. The Postgres version would just always add the key.

A potential problem is that you might have to inspect the database directly for the primary key instead of the schema definitions, and I don't know how much that would affect performance. Database updates shouldn't happen too often, though, so it's probably not a big deal.

I just encountered this very situation in one module.

Here is my update code:

  db_drop_primary_key($ret, 'arv_node'); // --> I removed this line in my second attempt (see below).
  db_change_field($ret, 'arv_node', 'meta_event_id', 'metaevent_id',
    array('type' => 'serial', 'not null' => TRUE),
    array('primary key' => array('metaevent_id')));

For the sake of the search engines, here is the error message I got when updating:

user warning: Incorrect table definition; there can be only one auto column and it must be defined as a key query: ALTER TABLE arv_node DROP PRIMARY KEY in drupal-6/includes/database.mysql-common.inc on line 386.
user warning: Multiple primary key defined query: ALTER TABLE arv_node CHANGE meta_event_id `metaevent_id` INT NOT NULL auto_increment, ADD PRIMARY KEY (metaevent_id) in drupal-6/includes/database.mysql-common.inc on line 520.

The first solution offered above didn't work, because the field I am trying to update is the primary key itself!
In my code above, I removed the first line, with db_drop_primary_key().
I got this error:

user warning: Multiple primary key defined query: ALTER TABLE arv_node CHANGE meta_event_id `metaevent_id` INT NOT NULL auto_increment, ADD PRIMARY KEY (metaevent_id) in drupal-6/includes/database.mysql-common.inc on line 520.

As advised in the second solution, this works:

  db_add_index($ret, 'arv_node', 'meta_event_id', array('meta_event_id'));
  db_drop_primary_key($ret, 'arv_node');
  db_change_field($ret, 'arv_node', 'meta_event_id', 'metaevent_id',
    array('type' => 'serial', 'not null' => TRUE),
    array('primary key' => array('metaevent_id')));
  db_drop_index($ret, 'arv_node_metaevent', 'meta_event_id');

This should perhaps be rolled into the API, so that when changing a serial field the system itself does the disable-change-enable logic for us. Anyone want to make a patch? :-)

Got the same issue, when I'd like to change a serial to unsigned.

Is anyone going to pick this up? Otherwise my inclination is to leave it until D8 when we need to refactor schema API anyway to allow multiple changes in one query, and this problem goes away. #432440: Update Schema API to make it more usable

> so that when changing a serial field the system itself does the disable-change-enable logic for us

Wouldn't that become a bit of a DX WTF, if *some* types of field handle that logic, while others require it to be done manually?

Confirming the fix in #4 works on D7.

Moving this to documentation so we can at least document the workaround for the time being.

Here's my code that's working for flag module:

  // A serial field must be defined as a key, so make a temporary index on
  // 'fcid' so we can safely drop the primary key.
  db_add_index('flagging', 'temp', array('fcid'));
  // Drop the primary key so we can rename the field.
  db_drop_primary_key('flagging');

  // Change field 'fcid' to 'flagging_id'.
  db_change_field('flagging', 'fcid', 'flagging_id',
    // Spec of the field. Identical to our current hook_schema(): we're not
    // changing anything except the name.
    array(
      'description' => 'The unique ID for this particular tag.',
      'type' => 'serial',
      'unsigned' => TRUE,
      'not null' => TRUE,
    ),
    // Keys spec. Identical to current hook_schema().
    array(
      'primary key' => array('flagging_id'),
    )
  );
  // Drop our temporary index.
  db_drop_index('flagging', 'temp');

Where are you proposing we document this, and what are you proposing should be added to that documentation? I'm just wondering if this is enough of a common case to warrant putting it into the db_change_field() docs, or whether (since it involves a bunch of functions) it would be better to put it somewhere in the on-line docs instead?

I would say it needs to be put in db_change_field(), if only as a link to a more detailed explanation. I for one lost a fair bit of time trying to figure out why changing my field was going wrong despite following all the instructions.

OK. Let's put a brief note in db_change_field() then, and a more detailed explanation can go on ... maybe here?
http://drupal.org/node/150215
There actually isn't anything on that page about updating fields at all, and it seems like there should be?

I will try to roll a patch for both D7/8 later for MySQL, so handle these logic in db_change_field() internally ;-)

(The technique with the indexes fixed this error for file entity update #2065213: DB update 7215 fails on upgrade to dev version from alpha)

I just ran into this issue when upgrading Flag Page to support Flag 3.x.
A serial column `content_id` needs to be renamed to `entity_id`.

It would be nice if at least an example of how to rename a serial column is added to the documentation of db_change_field.

<?php
db_add_field('spaces_overrides', 'sid', array(
'type' => 'int',
'not null' => TRUE,
'default' => 0,
));
db_drop_primary_key('spaces_overrides');
db_change_field('spaces_overrides', 'sid', 'sid', array(
'type' => 'serial',
'not null' => TRUE,
'unsigned' => TRUE,
'description' => 'Holds the identifier of the override.',
),
array('primary key' => array('sid'))
);

This will add identifier key to existing schema.