backup_migrate_update_7303() fails

I'm having the same issue from 7.x-2.8 to 7.x-3.9. In my case, I've updated only Backup and Migrate module. I have replaced the module folder with the new version (no 7.x-2.8 remains there). Then, I ran drush updb.

$ drush updb
 Backup_migrate  7300  Upgrade from Backup & Migrate 7.x-2.x.   - Uninstall the Backup Migrate Files module if it's installed.  - Uninstall the NodeSquirrel module if it's installed.  - 
                       Upgrade the configurations.                                                                                                                                        
 Backup_migrate  7301  Switch the cron switch to text.                                                                                                                                    
 Backup_migrate  7302  Add a second destination to schedules.                                                                                                                             
 Backup_migrate  7303  Add a serial id field to all tables to allow them to be ctools exportable.                                                                                         
 Backup_migrate  7304  Update all schedules to use the built in cron if none is specified.                                                                                                
 Backup_migrate  7305  Fix schema mismatch after upgrade.                                                                                                                                 
 Backup_migrate  7306  Leave a message to explain the mixup over the backup option.                                                                                                       
 Backup_migrate  7307  - 'backup_migrate_backup_memory_limit' vs 'backup_migrate_memory_limit'.                                                                                           
 Backup_migrate  7308  Update profiles table filter field to accommodate larger serialized strings.                                                                                       
 Backup_migrate  7309  NodeSquirrel support has been removed.                                                                                                                             
 Backup_migrate  7310  Disable e-mail destinations.                                                                                                                                       
 Backup_migrate  7311  Adjust the default performance settings.
Do you wish to run all pending updates? (y/n): y
Performed update: backup_migrate_update_7300                                                                                                                                        [ok]
Performed update: backup_migrate_update_7301                                                                                                                                        [ok]
Performed update: backup_migrate_update_7302                                                                                                                                        [ok]
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]
Performed update: backup_migrate_update_7303                                                                                                                                        [ok]
'all' cache was cleared.                                                                                                                                                            [success]
Finished performing updates.      

Then, I ran the dbup the second time. Now, the SQL error is the same as OP.

drush updb
 Backup_migrate  7303  Add a serial id field to all tables to allow them to be ctools exportable.    
 Backup_migrate  7304  Update all schedules to use the built in cron if none is specified.           
 Backup_migrate  7305  Fix schema mismatch after upgrade.                                            
 Backup_migrate  7306  Leave a message to explain the mixup over the backup option.                  
 Backup_migrate  7307  - 'backup_migrate_backup_memory_limit' vs 'backup_migrate_memory_limit'.      
 Backup_migrate  7308  Update profiles table filter field to accommodate larger serialized strings.  
 Backup_migrate  7309  NodeSquirrel support has been removed.                                        
 Backup_migrate  7310  Disable e-mail destinations.                                                  
 Backup_migrate  7311  Adjust the default performance settings.
Do you wish to run all pending updates? (y/n): y
SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'source_id' doesn't exist in table                                                                               [error]
Performed update: backup_migrate_update_7303                                                                                                                                        [ok]
'all' cache was cleared.                                                                                                                                                            [success]
Finished performing updates.                  

Appreciate if someone know any remediation.

That sucks, I'm sorry you've both run into this problem.

This is a bit tricky. When backup_migrate_update_7303() runs it should already have backup_migrate_sources.source_id available, but it seems like in certain circumstances this isn't true.

At the very least it should check the four tables have the appropriate field, and if not fail so it can be reran.

Please test this patch to see if it helps. Thank you.

You won't be able to do the upgrade using drush up backup_migrate, so you will have to download the module, apply the patch, then install/enable the module. The patch has not yet been applied to dev, so you need to download the module's latest release, then download the patch and apply it. See https://www.drupal.org/patch/apply

If you uninstall the module then it won't run the updates because they won't be needed. If you don't need to retain your B&M settings then this might be the simplest way of handling it.

If you do want to test the patch you can leave it disabled, apply the patch, run the updates again and see what happens.

Could everyone who running into this problem please let me know what version of the module you were using before you updated? That'll help work out a fix that works for all scenarios. Thank you.

Thank you, Damien. Your suggestion is the simplest way to upgrade! Haven't thought about it at first, but I've realized that I don't have complicated configuration. Appreciate your time for the patch. Let me know if it's worthwhile to test it in my environment, which is older PHP (5.x) and MySQL 5.7.

You're welcome.

Out of interest, what version of PHP 5?

The environment I tested was
PHP: 5.6.37
MySQL: 5.7.23 (utf8mb4 not enabled)

Thank you for your time and your support!
I've applied the patch and I'm now getting the error message that you added in the patch:

Unable to find the source_id column in the backup_migrate_sources table. This should not have happened, please try running the updates again.

I am using PHP 7.0.33-nmm1 and MySQL 5.7.28

Perhaps I could do the necessary changes by hand in MySQLAdmin? Well, that's not a solution for everybody, but that could solve it for me.

OK, now I did a pm-uninstall and reinstall.

There seem to be some rests of the old configuration. Especially, I still have a (greyed out) entry for NodeSquirrel under "Schedules".

When I try to configure things (eg add a Schedule) I get the following:

REFERRER /admin/config/system/backup_migrate/settings/destination/add/file
NACHRICHT Notice: Undefined index: subtype in backup_migrate_item->decode_db_row() (Zeile 545 von /d7/sites/all/modules/backup_migrate/includes/crud.inc).

Addition: It seems that updates 7303 and following didn't perform at all, but due to the reinstall, the update script doesn't know that. So, what to do now?

@kniekel: What version of B&M did you have installed before you updated?

You don't need to run updates 7303+ because when you uninstall the module completely and reinstall it, all of the schema & configuration changes are built using the latest APIs.

I am confused as to why it mentions Nodesquirrel - had you exported your site's B&M configuration with Features or something? There should be no mention of Nodesquirrel anywhere at this point.

@DamienMcKenna Well, that confuses me, too. I don't have anything like Features enabled. I did drush pm-disable, pm-uninstall, and then drush en. And of course drush cc all in between. I'll give it another try.

On the other side, it would be a bit of work to re-configure this for all my sites. I still have a bunch of sites running on D7 in a multisite installation. (Lots of work to do for D9 ...)

I'm afraid I can't tell which was the last version before the upgrade. Probably the last stable before. I run an update script (including drush up, drush updb and some other things) about once a week.

Edit: I tried uninstall/reinstall for a second time and now everything works fine. I don't know why.

I can confirm this problem still exists. Patch from #3 leads to:

Unable to find the source_id column in [error]
the backup_migrate_sources table. This
should not have happened, please try running the updates again.

and even if running this multiple times, it doesn't work. PHP 7.2

I haven't applied any patches but just updated two of my sites (nearly identical) from 7.x-2.8 to 7.x-3.4 (newest). One succeeded (image below) and one failed (same couple errors noted from #2).

First the one that succeeded:

Then the one that failed:

Update #7303

First error upon running update.php:

Failed: PDOException: 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: ALTER TABLE {backup_migrate_sources} ADD `source_id` INT unsigned NULL auto_increment DEFAULT NULL COMMENT 'Primary ID field for the table. Not used for anything except internal lookups.', ADD PRIMARY KEY (`source_id`); Array ( ) in db_add_field() (line 2900 of /includes/database/database.inc).

Now continuous error upon running update.php:

Failed: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'source_id' doesn't exist in table: ALTER TABLE {backup_migrate_sources} ADD INDEX `temp` (`source_id`); Array ( ) in db_add_index() (line 3000 of /includes/database/database.inc).

Help!

Just tried to update from 7.x-2.24 to 7.x-3.4 on another website and the same failures are happening.

I am having the exact same issue while trying to run database updates. Version: 7.x-3.9

Failed: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'source_id' doesn't exist in table: ALTER TABLE {backup_migrate_sources} ADD INDEX `temp` (`source_id`); Array ( ) in db_add_index() (line 3000 of /Users/ahmedraza/sites/drupal/includes/database/database.inc).

I just fixed it by uninstalling & reinstalling... be sure to backup your schedules / directories if you go this route.

I also fixed this by uninstalling & reinstalling.

I am having this same issue. Uninstalling & re-installing is not a good option for me because there are several modules in my site that have this module as a dependency. Can I add the column manually?

The site current has Backup and Migrate 7.x-3.9. I don't know what it had previously. I have inherited the site from someone else and apparently they upgraded the module code at some point but never successfully ran the database update.

I decided to uninstall and reinstall backup_migrate and the problem went away