Corrupt installation during upgrade to 5.37.2 on Joomla 3.9.26

Question

I have run into some trouble trying to upgrade from and old version of Civi to version 5.37.2 on Joomla 3.9.26 running on PHP 7.3.27 and MySql 5.7.32-35.

The initial problem was that I could not get the upgrade file running. I manually deleted the folder administrator/components/com_civicrm/civicrm and I extracted the administrator/components/com_civicrm/civicrm.zip file. This created a new administrator/components/com_civicrm/civicrm folder.

I then rerun the Upgrade Database with https://domainname.com/administrator/index.php?option=com_civicrm&task=civicrm/upgrade&reset=1

and this time I was able to start the upgrade process.

Unfortunately in the middle of the upgrade I got the following error:

[Error: Update smart groups to rename filters on case_from and case_to to case_start_date and case_end_date]

CiviCRM_API3_Exception: "DB Error: no such field"

I have to specify, that at this point I was still able to access the Civi Admin interface on the website and I was also able to see all my Campaigns and Event Records, etc.

In trying to trouble shoot this issue, I landed on this post What can I do to fix my database that has been partially upgraded?. I was able to discover in the civicrm_domain table that the upgrade had stopped at 5.20.alpha1.upgrade.

I then proceeded to manually process all the updates in the Incremental/sql folder, which I was able to process successfully.

However when I now try to access the Civi Administrator backend, I am no longer able to as I get the following error:

DB Error: no such field

And when I look at the Log I get the following:

May 16 11:47:37  [error] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => exceptionHandler
        )

    [code] => -19
    [message] => DB Error: no such field
    [mode] => 16
    [debug_info] => SELECT `a`.`id` AS `id`, `a`.`name` AS `name`, `a`.`title` AS `title`, `a`.`help_pre` AS `help_pre`, `a`.`help_post` AS `help_post`, `a`.`extends` AS `extends`, `a`.`icon` AS `icon`
FROM civicrm_custom_group a
WHERE (`a`.`is_multiple` = "1") AND (`a`.`is_active` = "1")
 [nativecode=1054 ** Unknown column 'a.icon' in 'field list']
    [type] => DB_Error
    [user_info] => SELECT `a`.`id` AS `id`, `a`.`name` AS `name`, `a`.`title` AS `title`, `a`.`help_pre` AS `help_pre`, `a`.`help_post` AS `help_post`, `a`.`extends` AS `extends`, `a`.`icon` AS `icon`
FROM civicrm_custom_group a
WHERE (`a`.`is_multiple` = "1") AND (`a`.`is_active` = "1")
 [nativecode=1054 ** Unknown column 'a.icon' in 'field list']
    [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::exceptionHandler prefix="" info="SELECT `a`.`id` AS `id`, `a`.`name` AS `name`, `a`.`title` AS `title`, `a`.`help_pre` AS `help_pre`, `a`.`help_post` AS `help_post`, `a`.`extends` AS `extends`, `a`.`icon` AS `icon`
FROM civicrm_custom_group a
WHERE (`a`.`is_multiple` = "1") AND (`a`.`is_active` = "1")
 [nativecode=1054 ** Unknown column 'a.icon' in 'field list']"]
)

If you need the full log, let me know and I can send it.

I see from your original message that you also recommend running the php functions. As I have not done this, I am wondering first if I can assume that anything up to 5.20 (FiveTwenty.php) as already been run through the original upgrade process and secondly how do I actually run these php files? Can I just call them manually, one at a time, from the backend like this?

https://mydomain.com/administrator/components/com_civicrm/civicrm/CRM/Upgrade/Incremental/php/FiveTwenty.php https://mydomain.com/administrator/components/com_civicrm/civicrm/CRM/Upgrade/Incremental/php/FiveTwentyOne.php etc.

You also say, "Once you have done all the things the upgrade script would have done to get to the next point version you can alter the value in civicrm_domain.version to the next point version & remove the upgrade & then start the script running again".

I have now already adjusted the version number in civicrm_domain table to 5.37.alpha1.upgrade. Can you please explain how do I remove the upgrade and when you say start the script again, do you mean doing

https://domainname.com/administrator/index.php?option=com_civicrm&task=civicrm/upgrade&reset=1

If I try running the above now, I get the following:

Database check failed - the database looks to have been partially upgraded. You may want to reload the database with the backup and try the upgrade process again.

I would really appreciate your help getting this site back up and running.

Thank you in advance for all your help.

Regards, Adri

Answer 1

Here's a few pointers that might help:

• Make sure you have a database backup before doing any manual fiddling!
• For each version change both the relevant sql and php changes under CRM/Upgrade/Incremental must be applied. The sql files are applied by the 'runSql' task in php.
• .upgrade on the version string means it has started the upgrades for that version but not finished.
• The automatic upgrader aborts if it finds a .upgrade version when it starts.
• You need to figure out which steps have been done - the log file should record steps but also manually check the database and see what has been applied.
• Once you know what steps have not been done, you need to do those manually. You will probably hit some difficulties here - if it 'just worked' the automatic upgrade would not have failed in the first place.
• When you have completed those upgrade steps, update the version string in the civicrm_domain table to remove .upgrade - keep the rest of the version string the same.
• Run the automatic upgrader again and hopefully it continues on applying the remaining upgrades from 5.20.alpha1 to 5.37.2

If I understand correctly what you have done, then

1. You have applied sql/5.20.alpha1.mysql.tpl
2. You also need to apply the changes in php/FiveTwenty.php (check what if anything has already been applied)
3. You have changed the version string to 5.37.alpha1.upgrade but you should change it to 5.20.alpha1 after you have completed the previous steps.

The 'No such field' error is to be expected since your code base has been updated to 5.37.2 but the icon field on the civicrm_custom table was added in 5.28 and the upgrades for that have not been run yet.