Answers.Body: <p>Here's a few pointers that might help:</p>
  <ul>
  <li>Make sure you have a database backup before doing any manual fiddling!</li>
  <li>For each version change both the relevant sql and php changes under <code>CRM/Upgrade/Incremental</code> must be applied. The sql files are applied by the 'runSql' task in php.</li>
  <li><code>.upgrade</code> on the version string means it has started the upgrades for that version but not finished.</li>
  <li>The automatic upgrader aborts if it finds a <code>.upgrade</code> version when it starts.</li>
  <li>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.</li>
  <li>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.</li>
  <li>When you have completed those upgrade steps, update the version string in the <code>civicrm_domain</code> table to remove <code>.upgrade</code> - keep the rest of the version string the same.</li>
  <li>Run the automatic upgrader again and hopefully it continues on applying the remaining upgrades from 5.20.alpha1 to 5.37.2</li>
  </ul>
  <p>If I understand correctly what you have done, then</p>
  <ol>
  <li>You have applied <code>sql/5.20.alpha1.mysql.tpl</code></li>
  <li>You also need to apply the changes in <code>php/FiveTwenty.php</code> (check what if anything has already been applied)</li>
  <li>You have changed the version string to <code>5.37.alpha1.upgrade</code> but you should change it to <code>5.20.alpha1</code> after you have completed the previous steps.</li>
  </ol>
  <p>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 <code>civicrm_custom</code> table was added in 5.28 and the upgrades for that have not been run yet.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-05-18T12:07:15.690Z
Answers.Text: 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:
Answers.UserDisplayName: Adri Cist
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-05-18T12:26:24.277Z
Answers.Text: 1) I have applied all the sql/xxx.tpl updates, right down to 5.37.alpha1.mysql.tpl
Answers.UserDisplayName: Adri Cist
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-05-18T12:26:55.260Z
Answers.Text: 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!
Answers.UserDisplayName: Adri Cist
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-05-18T12:27:00.167Z
Answers.Text: 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.
Answers.UserDisplayName: Adri Cist
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-05-18T12:27:30.477Z
Answers.Text: Finally, so what is my best way forward? Can I 1) just run the remaining SQL changes not done in php/FiveTwenty.php manually;
Answers.UserDisplayName: Adri Cist
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-05-18T12:27:42.497Z
Answers.Text: 2) adjust the civicrm_domain field value to 5.20.alpha1; 3) try to rerun the upgrade and see what happens? Even though I have already run all the SQL .tpl manually into the database? Or is there a way to manually run all the remaining PHP files from FiveTwenty.php onwards, by placing them in a special directory where the required classes will be available? I really appreciate you taking the time to help me out with this one, it is greatly appreciated. Thanks, Adri
Answers.UserDisplayName: Adri Cist
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-05-18T19:05:37.187Z
Answers.Text: From a quick look, it seems all the sql files could be run again without problem.  So do 2 & 3 then run the automatic upgrader.  I haven't found an easy way to run a single upgrade function so I usually read the php tasks and do them manually.  You're unlucky that there are lots of upgrade tasks in FiveTwenty.php! You could create a php script and copy in the upgrade functions that are used.  Hopefully someone else has a better way!
Answers.UserDisplayName: Aidan
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-05-19T01:27:50.820Z
Answers.Text: Thank you Aidan, I will try over the weekend and report back.
Answers.UserDisplayName: Adri Cist
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-05-18T10:22:02.087Z
Answers.LastActivityDate: 2021-05-19T08:11:15.127Z
Answers.LastEditDate: 2021-05-19T08:11:15.127Z
Answers.UserDisplayName: Aidan
Body: <p>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.</p>
  <p>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.</p>
  <p>I then rerun the Upgrade Database with <a href="https://domainname.com/administrator/index.php?option=com_civicrm&amp;task=civicrm/upgrade&amp;reset=1" rel="nofollow noreferrer">https://domainname.com/administrator/index.php?option=com_civicrm&amp;task=civicrm/upgrade&amp;reset=1</a></p>
  <p>and this time I was able to start the upgrade process.</p>
  <p>Unfortunately in the middle of the upgrade I got the following error:</p>
  <p>[Error: Update smart groups to rename filters on case_from and case_to to case_start_date and case_end_date]</p>
  <p>CiviCRM_API3_Exception: &quot;DB Error: no such field&quot;</p>
  <p>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.</p>
  <p>In trying to trouble shoot this issue, I landed on this post <a href="https://civicrm.stackexchange.com/questions/4147/what-can-i-do-to-fix-my-database-that-has-been-partially-upgraded/39564#39564">What can I do to fix my database that has been partially upgraded?</a>. I was able to discover in the civicrm_domain table that the upgrade had stopped at 5.20.alpha1.upgrade.</p>
  <p>I then proceeded to manually process all the updates in the Incremental/sql folder, which I was able to process successfully.</p>
  <p>However when I now try to access the Civi Administrator backend, I am no longer able to as I get the following error:</p>
  <p>DB Error: no such field</p>
  <p>And when I look at the Log I get the following:</p>
  <pre><code>May 16 11:47:37  [error] $Fatal Error Details = Array
  (
      [callback] =&gt; Array
          (
              [0] =&gt; CRM_Core_Error
              [1] =&gt; exceptionHandler
          )
  
      [code] =&gt; -19
      [message] =&gt; DB Error: no such field
      [mode] =&gt; 16
      [debug_info] =&gt; 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` = &quot;1&quot;) AND (`a`.`is_active` = &quot;1&quot;)
   [nativecode=1054 ** Unknown column 'a.icon' in 'field list']
      [type] =&gt; DB_Error
      [user_info] =&gt; 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` = &quot;1&quot;) AND (`a`.`is_active` = &quot;1&quot;)
   [nativecode=1054 ** Unknown column 'a.icon' in 'field list']
      [to_string] =&gt; [db_error: message=&quot;DB Error: no such field&quot; code=-19 mode=callback callback=CRM_Core_Error::exceptionHandler prefix=&quot;&quot; info=&quot;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` = &quot;1&quot;) AND (`a`.`is_active` = &quot;1&quot;)
   [nativecode=1054 ** Unknown column 'a.icon' in 'field list']&quot;]
  )
  </code></pre>
  <p>If you need the full log, let me know and I can send it.</p>
  <p>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?</p>
  <p><a href="https://mydomain.com/administrator/components/com_civicrm/civicrm/CRM/Upgrade/Incremental/php/FiveTwenty.php" rel="nofollow noreferrer">https://mydomain.com/administrator/components/com_civicrm/civicrm/CRM/Upgrade/Incremental/php/FiveTwenty.php</a>
  <a href="https://mydomain.com/administrator/components/com_civicrm/civicrm/CRM/Upgrade/Incremental/php/FiveTwentyOne.php" rel="nofollow noreferrer">https://mydomain.com/administrator/components/com_civicrm/civicrm/CRM/Upgrade/Incremental/php/FiveTwentyOne.php</a>
  etc.</p>
  <p>You also say, &quot;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 &amp; remove the upgrade &amp; then start the script running again&quot;.</p>
  <p>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</p>
  <p><a href="https://domainname.com/administrator/index.php?option=com_civicrm&amp;task=civicrm/upgrade&amp;reset=1" rel="nofollow noreferrer">https://domainname.com/administrator/index.php?option=com_civicrm&amp;task=civicrm/upgrade&amp;reset=1</a></p>
  <p>If I try running the above now, I get the following:</p>
  <p>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.</p>
  <p>I would really appreciate your help getting this site back up and running.</p>
  <p>Thank you in advance for all your help.</p>
  <p>Regards,
  Adri</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-05-17T11:28:11.107Z
Comments.Text: 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
Comments.UserDisplayName: Pradeep Nayak
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2021-05-18T12:36:21.193Z
Comments.Text: 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
Comments.UserDisplayName: Adri Cist
Tags: <joomla><upgrade><error><database><installation>
Title: Corrupt installation during upgrade to 5.37.2 on Joomla 3.9.26
ThreadText: # Corrupt installation during upgrade to 5.37.2 on Joomla 3.9.26
  
  (Asked by: username_0 on 2021-05-17)
  
  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
  
  **Tags:** joomla, upgrade, error, database, installation
  
  > **Comments:**
  > **username_1**: 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  
  > **username_0**: 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  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_2 on 2021-05-18. Score: 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.
  
  > **Comments:**
  > **username_0**: username_2, 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:  
  > **username_0**: 1) I have applied all the sql/xxx.tpl updates, right down to 5.37.alpha1.mysql.tpl  
  > **username_0**: 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!  
  > **username_0**: 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.  
  > **username_0**: Finally, so what is my best way forward? Can I 1) just run the remaining SQL changes not done in php/FiveTwenty.php manually;  
  > **username_0**: 2) adjust the civicrm_domain field value to 5.20.alpha1; 3) try to rerun the upgrade and see what happens? Even though I have already run all the SQL .tpl manually into the database? Or is there a way to manually run all the remaining PHP files from FiveTwenty.php onwards, by placing them in a special directory where the required classes will be available? I really appreciate you taking the time to help me out with this one, it is greatly appreciated. Thanks, Adri  
  > **username_2**: From a quick look, it seems all the sql files could be run again without problem.  So do 2 & 3 then run the automatic upgrader.  I haven't found an easy way to run a single upgrade function so I usually read the php tasks and do them manually.  You're unlucky that there are lots of upgrade tasks in FiveTwenty.php! You could create a php script and copy in the upgrade functions that are used.  Hopefully someone else has a better way!  
  > **username_0**: Thank you username_2, I will try over the weekend and report back.  
  
  ---
  
  