I'm having problems dropping primary keys and fields (columns) in MySQL. I think it could be related to the following note I found in the 5.1 Reference Manual.

Error on rename the table occurs also when you try to drop a primary key from the InnoDB table that is referenced by other tables (i.e. there exists a foreign key constraint that references the primary key you want to drop.) It's a pity that mysql doesn't report this error in more user friendly way.

The table in question is the filter table when attempting to run filter_update_7004. If this is the case then other update functions could be affected as well.

Comments

sun’s picture

sun
Primary language German
Location Karlsruhe
commented
Component: filter.module » database system
Priority: Normal » Critical
Issue tags: +D7 upgrade path
damien tournoud’s picture

damien tournoud commented
Priority: Critical » Normal
Status: Active » Postponed (maintainer needs more info)

Waiting for someone to confirm the bug.

ctmattice1’s picture

ctmattice1 commented

I also found this in the MySQL manaual, which may be more applicable to the primary key issue i'm experiencing

Attempting to "ALTER TABLE ... DROP PRIMARY KEY" on a table when an AUTO_INCREMENT column exists in the key generates an error:
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key.
To make this work without erroring, drop and re-add the new primary key in a single statement, e.g.:
ALTER TABLE mytable DROP PRIMARY KEY, ADD PRIMARY KEY(col1,col2);
Crell’s picture

Crell commented

Ugh. We tried to add proper schema builders, but didn't make it in by the initial code freeze. D7 doesn't support multiple schema changes in one command. We may need to replicate the table and then rename it. Yuck.

damien tournoud’s picture

damien tournoud commented

Cannot reproduce on MySQL 5.0. What we are actually doing here is dropping a whole autoincrement column, not a primary key:

  db_drop_field('filter', 'fid');

Seems to work ok for me.

damien tournoud’s picture

damien tournoud commented

It would help if you could report the *exact and actual* error message you are experiencing.

ctmattice1’s picture

ctmattice1 commented

Wish I could Damien but the updates silently die and continues on with the next sequence.

Tables were not being changed. So I started to stepwise debug using print "pass check point";die(); That is how I found which update functions were dying on the vine. I've trace some of them down but will reinstall and run #734762: Upgrade path: tidy up the changes to filter and filter_format since that's where the update process was blowing up the worst.

damien tournoud’s picture

damien tournoud commented
Title: db_drop_primary_key doesn't always work » Investigating issues about the upgrade process (possibly MySQL 5.1 related)
Category: bug » support
Status: Postponed (maintainer needs more info) » Active

Ok, I'll convert this into a support request for now. Thanks for investigating that, it might be related to something specific about your environment (MySQL version, PHP version, etc.), so it might be useful to investigate all the possible causes.

At least, the two potential causes identified here (renaming a table pointed by a foreign key and removing a primary key that is an autoincrement) have been ruled out.

scor’s picture

scor commented

There is something I don't understand in filter_update_7004(): we do db_add_primary_key('filter', array('format', 'name')); without dropping the primary_key first. According to mysql/schema.inc, this should throw an exception:

    if ($this->indexExists($table, 'PRIMARY')) {
      throw new DatabaseSchemaObjectExistsException(t("Cannot add primary key to table %table: primary key already exists.", array('%table' => $table)));
    }
ctmattice1’s picture

ctmattice1 commented

From my understanding when you drop the auto increment field it removes the primary key as well.

kars-t’s picture

kars-t commented
Status: Active » Fixed

Hi

I am closing this issue to clean up the issue queue. Feel free to reopen the issue if there is new information and the problem still resides. If not please make sure you close your issues that you don't need any more.

Maybe you can get support from the local user group. Please take a look at this list at groups.drupal.org.

Status: Fixed » Closed (fixed)
Issue tags: -D7 upgrade path

Automatically closed -- issue fixed for 2 weeks with no activity.

Issue tags: +D7 upgrade path

Restoring issue tags, see #2125755: System messages removed all issue tags during D7 upgrade.