0

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

2
  • From what version of Civi are you trying upgrade? Looks like your upgrade failed at some point to add a field to a table hence it failed on latest version – Pradeep Nayak May 17 at 11:28
  • Hi Pradeep, and thank you for your help. The update failed on line 19 of the php/FiveTwenty.php The error was [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". Unfortunately I did not jot down what the version was, but it was quite old. I do have a backup of before the upgrade and can try to work it out, if it is very important to know. Thanks again for your help, Adri – Adri Cist May 18 at 12:36
0

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.

8
  • Aidan, thank you so much for all your feedback and input, there is lots of useful tips in there. What I have done is the following: – Adri Cist May 18 at 12:07
  • 1) I have applied all the sql/xxx.tpl updates, right down to 5.37.alpha1.mysql.tpl – Adri Cist May 18 at 12:26
  • 2) I know exactly where in php/FiveTwenty.php the script failed, however I am not sure I to run this php file in isolation, when I try it complains about a missing class that is referenced at the beginning of the file. I suppose I can manually apply all the changes to the database that are listed in this file, but what about all the other PHP files that have not been run? Applying all of these changes manually would take me forever! – Adri Cist May 18 at 12:26
  • 3) This is why I adjusted the civicrm_domain to 5.37.alpha1.upgrade, as I thought that all the database changes had been done, but I know see that the sql .tpl files were only a small portion of the SQL updates and many more are in the PHP files. – Adri Cist May 18 at 12:27
  • Finally, so what is my best way forward? Can I 1) just run the remaining SQL changes not done in php/FiveTwenty.php manually; – Adri Cist May 18 at 12:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.