System context

  • Apache 2.2
  • PHP 5.3.3
  • MySQL 5.1
  • Drupal 7.31

Updating from 7.x-2.8 to 7.x-3.0 returns this message:

Update #7303
* Failed: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key: ALTER TABLE {backup_migrate_sources} DROP PRIMARY KEY; Array () in db_drop_primary_key() (line 2904 or /includes/database/database.inc).

CommentFileSizeAuthor
#19 2324949_export.sql_.txt5.41 KBNancyDru
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

hasecbinusr’s picture

I dropped the four backup_migrate tables, deleted the row out of the systems table, reactivated the module, and it worked fine. Perhaps it's the order of operations in update 7303 in backup_migrate.install?

ronan’s picture

Status: Active » Postponed (maintainer needs more info)

It sounds like you've fixed your issue. I'm not sure if this was just a one-off weirdness with your DB or a frequent problem. I'll leave this ticket open for a while in case others have the same issue.

theMusician’s picture

I just ran into the same issue going from 2.7 to 3.0.

It seems it does not like having the primary key dropped.

I poked around at the function but it all seems to make sense. The only part that MySQL yells about is that the primary key is gone, so perhaps the schema has to be updated first, and then the update would work?

theMusician’s picture

I did as hasecbinusr did but just through the GUI deactivating the module and then uninstalling the module. Once I re-enabled the module, all is well.

esolitos’s picture

Status: Postponed (maintainer needs more info) » Active

Same problem here, unable to update, of course removing all the tables and recreating them would work, but that's not a solution. :)

kbrinner’s picture

I am getting this error as well when trying to update from 2.7 to 3.0.

PHP 5.3.28
MySQL 5.5.34
Drupal 7.32

modulist’s picture

I just ran into the same error message. Fortunately, I can rebuild all of my backup and migrate settings in less time than it takes to write up an issue report.

I resolved the issue by disabling then uninstalling the module through drush:

drush dis backup_migrate
drush pm-uninstall backup_migrate_files
drush pm-uninstall backup_migrate

There is an alternate solution that involves manipulating the system table directly. It's posted here:
http://drupal.stackexchange.com/questions/127710/ghost-pending-update-ba...

mrjmd’s picture

Ran into this error during a deployment today. After doing some testing I realized something strange. If I ran backup_migrate_update_7303() as an isolated update, it seemed to work fine. If I ran it with other updates, it threw the error and then would not complete.

The order of operations theory above seems to fit with this, but I don't think it's the order of operations within this update, it may instead be the order of updates themselves that are at fault. I didn't bother to dig to the root since I found a workaround that was sufficient for me. During deployment I simply commented out this particular update script, run drush updb and updated everything else, then uncommented and ran it again.

Hope that helps someone else.

Poieo’s picture

Same issue going from 7.x-2.8 to 7.x-3.0. The 7303 update fails with the error mentioned in the opening issue.

stefan.r’s picture

Same issue here.

tanc’s picture

Another one with this issue.

NancyDru’s picture

Is anyone looking at this?

NancyDru’s picture

@mrjmd: Even isolated, it still fails for me. Guess I'll have to try to fix it myself.

ronan’s picture

Status: Active » Postponed (maintainer needs more info)

I was unable to reproduce this in my clean updates from the 2.x branch to the 3.x branch. However I did discover in my testing that I can reproduce the issue when one of the tables already has the new key structure. This happened for me because when I was restoring from a clean 2.x db to test the 'backup_migrate_sources' table was not being dropped and was therefore already fully updated. Is anybody who is having this issue maybe working with a db that's been downgraded via db restore?

Since the normal flow of upgrades works well I think the only way to fix this would be to inspect the DB tables before attempting to alter them. This seems like a huge pain so it would be good to try and figure out what path people are taking to get into this update situation.

ronan’s picture

Status: Postponed (maintainer needs more info) » Fixed

Ok some googling turns up this:

https://www.drupal.org/node/190027

Indicating that you have to remove auto-index before removing a primary key (but only on MySQL, not on PGSql)

No idea why this wouldn't happen every time but there's a work around on that thread that seems to do the trick without hurting the regular upgrade progress.

I've pushed that to the module. Hopefully this helps those who have been having trouble.

  • ronan committed b591f91 on 7.x-3.x
    Issue #2324949: Update #7303 Failing
    
NancyDru’s picture

Still failed for me.
Failed: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key: ALTER TABLE {backup_migrate_sources} DROP INDEX `temp`; Array ( ) in db_drop_index() (line 2956 of C:\wamp\www\Core7\includes\database\database.inc).

Drupal 7.37

ronan’s picture

Status: Fixed » Postponed (maintainer needs more info)

@NancyDru Really? Damnit. Would it be possible to have you send me the backup_migrate_* tables from your install from BEFORE that update attempt? If there is private info in any of them like passwords you can just send the structure. That would help me reproduce this consistently and hopefully fix it.

NancyDru’s picture

FileSize
5.41 KB

Here are the 7.x-2.8 tables before going to 7.x-3.x. (Of course, remove the .txt suffix.)

ronan’s picture

Thanks! Unfortunately I am unable to reproduce these problems even with those tables installed locally. Can you tell me what the value is for the 'schema_version' in the system table for Backup and Migrate in before you attempt the update?

NancyDru’s picture

7205

NancyDru’s picture

It suddenly applied, so somehow it got fixed for me.

Jukebox’s picture

I also ran into this issue. It was easier to run the following than investigate what the problem is, since I don't have any data other than the defaults in my database:

drush dis -y backup_migrate && drush pm-uninstall -y backup_migrate && drush en -y backup_migrate

Steve -cc’s picture

I'm having a similar problem.

Update #7303 Failed: DatabaseSchemaObjectExistsException:Cannot add index temp to table backup_migrate_sources:index already exists in . . .

Mine was an update to 3.1 from 7.x-1.x-dev

I'm thinking of doing same as #1 https://www.drupal.org/node/2324949#comment-9074163

Just installed B&M module. The site was created using backup & migrate, then the module was removed for awhile as the site was modified and updated. After installing it and enabling it got message that I needed to run update script and started getting this error. Ran update several times - no change.

Is it cool to do the following:

  • Disable Backup & Migrate Module
  • Drop the four backup_migrate tables
  • Delete the rows out of the system table - backup_migrate and backup_migrate_files
  • Reactivate the module

Any problems with this?

Steve

Steve -cc’s picture

Status: Postponed (maintainer needs more info) » Active

Making it active - Added a comment / question.

Steve -cc’s picture

I did it and it worked fine of course now I am having the issue with emails not sending - https://www.drupal.org/node/2507495

couturier’s picture

Status: Active » Postponed (maintainer needs more info)

Is this still an issue with 7.x-3.2?

couturier’s picture

Status: Postponed (maintainer needs more info) » Closed (outdated)

Closing after more than two weeks with no activity.

NancyDru’s picture

Status: Closed (outdated) » Active

Yes, I saw it happen on a site that is being moved to a new server and upgraded to 7.x-3.2.

Leeteq’s picture

So it seems like the status for "path of least resistance" is to disable + uninstall the 2.x version of this module before upgrading, and then install 3.x "as new" after the upgrade.

I have used the 2.8-dev version of this module on several sites for long time because of a bug in the 3.x version which caused it to not store the tables exception list (as the 2.8-dev version does). But that bug was just fixed in 3.x, so now we can move on :-) (ref. https://www.drupal.org/node/2290707 )

couturier’s picture

Status: Active » Closed (duplicate)
Related issues: +#2912440: Verify 7.x-2.x -to- 7.x-3.x upgrade path, mark 7.x-2.x as unsupported

It almost sounds to me like this is a problem relating to not having an upgrade path from 7.x-2.x to 7.x-3.x, and there is already another issue opened to address that.