Upgrade from 1.1 to 1.7 fails, complaining about primary keys [#597980]

Hi,
I guess I've been away from this for too long to solve that myself :-) I upgraded from -1.1 to -1.7 and got these messages:

user warning: Incorrect table definition; there can be only one auto column and it must be defined as a key query: ALTER TABLE biblio_contributor_data DROP PRIMARY KEY in /usr/share/drupal/includes/database.mysql-common.inc on line 386.
user warning: Multiple primary key defined query: ALTER TABLE biblio_contributor_data ADD PRIMARY KEY (cid, aka) in /usr/share/drupal/includes/database.mysql-common.inc on line 374.

I have an idea what it means, but not how to fix this now. Anything I can type into my mysql prompt to correct the tables?
I'm not sure if I can edit or add any entries before this is fixed...

cu,
Frank

Comments

Comment #1

Frank Steiner commented 7 October 2009 at 09:41

I got a little help from a mysql expert. He told me that the primary key could not be deleted because cid has "auto_increment" set.

He proposed to remove that extra, then delete the PRI from cid, then add it for (cid,aka) and re-add the auto_increment.

I don't see auto_increment anywhere in the scheme definition in biblio.install, but after deleting biblio (including the removal of all tables) and reinstalling it, the table looks like this

| cid         | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| aka         | int(10) unsigned | NO   | PRI | 0       |                | 
...

so I guess the above steps would be ok?

Comment #2

rjerome commented 7 October 2009 at 12:52

Hi Frank, long time no hear...

In "Drupal Schema" speak, auto_increment is "serial"...

  $schema['biblio_contributor_data'] = array(
    'description' =>'Contains Author information for each publication',
    'fields' => array(
      'cid' => array(
        'type' => 'serial',
        'not null' => TRUE,
        'unsigned' => TRUE,
        'description' =>'Primary Key: Author ID',
        ),

Ron.

Comment #3

Frank Steiner commented 8 October 2009 at 10:25

Yes, our drupal installation was sitting asleep ;-) Now I need to upgrade some stuff.

Ok, I understand that I can fix the stuff with the steps above. But I guess the update code should remove the autoincrement first to avoid this bug, right?