Crash in upgrade to 5.35.1 on task core-issue#365 - Add created_date to civicrm_action_schedule

Question

While upgrading several of my sites to 5.35.1 (from 5.28 or later) I experienced a nasty database crash in the step called core-issue#365 - Add created_date to civicrm_action_schedule

$Fatal Error Details = Array (
 [callback] => Array ( [0] => CRM_Core_Error [1] => exceptionHandler )
 [code] => -1
 [message] => DB Error: unknown error
 [mode] => 16
 [debug_info] =>
   ALTER TABLE `civicrm_action_schedule`
   ADD COLUMN `created_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP
               COMMENT 'When was the schedule reminder created.'
   [nativecode=1118 ** Row size too large. The maximum row size for the used
   table type, not counting BLOBs, is 8126. This includes storage overhead,
   check the manual. You have to change some columns to TEXT or BLOBs]

What can I do?

(Note in my case this is not related to Upgrade fails with Row size too large error)

Answer 1

Of course

• you ran this on a development copy of the site first, so you're not panicking, right?
• you took a backup before your production upgrade, and can roll back to that?
• you wished you had?

If you can, revert your database to a pre-upgrade backup

Then run this SQL on the CiviCRM database: OPTIMIZE TABLE civicrm_action_schedule;

Then repeat the upgrade.

If you didn't have a backup

Then run this SQL on the CiviCRM database: OPTIMIZE TABLE civicrm_action_schedule;

Then try to resume the upgrade procedure. If you're on the command line you can use cv upgrade:db which will probably suggest you run cv upgrade:db --retry.

Hopefully it runs through clean for you now.

Answer 2

This was reported for 5.34 and there was PR for the fix but was rejected in favour of environment issue.

Solution was, Before upgrading to 5.34 or later to convert all the tables in civicrm to make ROW_FORMAT=DYNAMIC.

Include the below config in mysql config file

innodb_large_prefix=true 
innodb_file_format=barracuda 
innodb_file_per_table=true

Answer 3

I do have a backup via drush ard.

However I tried the second of artfulrobot's options, as the steps for the restore are a bit convoluted.

drush civicrm-upgrade-db

ran with no output.

drush cache-clear civicrm

It appears things are ok.