Bug: No upgrade path for 5.5.0 to 5.6.0

[tmhorne]

Description

A clear and concise description of what the reported bug is:

• ChurchCRM version: 5.5.0, then unzip'ed 5.6.0 on top of it.
• PHP version the server running: 8.1.2-1ubuntu2.14
• DB Server and Version the server is running: mysql Ver 8.0.35-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))

Steps To Reproduce

Steps to reproduce the behavior:

ℹ️ As a bug reporter, the preferred mechanism to record steps is to download Cypress Recorder from https://chromewebstore.google.com/detail/cypress-recorder/glcapdcacdfkokcmicllhcjigeodacab?pli=1, record your interaction and copy the results here.

📋 Cypress Recorder Results:

cy.visit('http://churchcrm.horne.fam/SystemDBUpdate.php');
cy.get('.login-page').click();
cy.get('.login-page').click();
cy.get('#upgradeDatabase').click();
cy.url().should('contains', 'http://churchcrm.horne.fam/SystemDBUpdate.php');
cy.get('.login-page').click();
cy.get('#upgradeDatabase').click();
cy.url().should('contains', 'http://churchcrm.horne.fam/SystemDBUpdate.php');
cy.get('#upgradeDatabase').click();
cy.url().should('contains', 'http://churchcrm.horne.fam/SystemDBUpdate.php');
cy.get('#upgradeDatabase').click();
cy.url().should('contains', 'http://churchcrm.horne.fam/SystemDBUpdate.php');

⚠️ If you cannot do this for some reason, please document the steps to reproduce below.

(note: this flow is not recommend and will make it more difficult on the maintainers of ChurchCRM)

1. Go to main url: http://churchcrm.horne.fam
2. It redirects to http://churchcrm.horne.fam/SystemDBUpdate.php
3. Click the "Upgrade Database" button.
4. It reloads the same page.

Expected behavior

Click the "Upgrade database" button, it upgrades the database, and I can log in to churchcrm.

Screenshots and/or logs

📋 Relevant screenshots:

📋 Logs:

==> 2024-03-16-app.log <==
[2024-03-16T16:49:09.107650+00:00] defaultLogger.INFO: Beginning database upgrade [] {"url":"/SystemDBUpdate.php?upgrade=true","remote_ip":"192.168.0.42","correlation_id":"65f5cd851a487"}
[2024-03-16T16:49:09.107973+00:00] defaultLogger.INFO: Current Version: 5.5.0 {"dbVersion":"5.5.0","softwareInstalledVersion":"5.6.0"} {"url":"/SystemDBUpdate.php?upgrade=true","remote_ip":"192.168.0.42","correlation_id":"65f5cd851a487"}
[2024-03-16T16:49:09.109909+00:00] defaultLogger.WARNING: No upgrade path for 5.5.0 to 5.6.0 [] {"url":"/SystemDBUpdate.php?upgrade=true","remote_ip":"192.168.0.42","correlation_id":"65f5cd851a487"}
[2024-03-16T16:49:09.124977+00:00] defaultLogger.INFO: Complete database upgrade; redirecting to Main menu [] {"url":"/SystemDBUpdate.php?upgrade=true","remote_ip":"192.168.0.42","correlation_id":"65f5cd851a487"}
[2024-03-16T16:49:09.138226+00:00] defaultLogger.INFO: Database is not current, redirecting to SystemDBUpdate [] {"url":"/v2/dashboard","remote_ip":"192.168.0.42","correlation_id":"65f5cd8521bf6"}
[2024-03-16T16:49:29.164962+00:00] defaultLogger.INFO: Database is not current, redirecting to SystemDBUpdate [] {"url":"/","remote_ip":"192.168.0.42","correlation_id":"65f5cd9928467"}

Debugging Steps

ℹ️ If you need help debugging, please refer to the wiki for guidance: https://github.com/ChurchCRM/CRM/wiki/Logging-and-Diagnostics.

• What has been done to debug the issue so far?
  • I have looked in the logs and saw that it doesn't know how to update the database.ll
• Have you looked at the browser's developer tools to see if an error is thrown / network request failed?
  • n/a
• Are there any associated error logs on the backend that get emitted when you attempt to do this action?
  • Yes, see above.

Desktop (please complete the following information):

• Ubuntu mate 23.10
• Linux dauntless 6.5.0-25-generic Sort person search by first name  #25-Ubuntu SMP PREEMPT_DYNAMIC Wed Feb 7 14:58:39 UTC 2024 x86_64 x86_64 x86_64 GNU/Linux
• Firefox 123.0.1 (64-bit)
• Google Chrome Version 122.0.6261.94 (Official Build) (64-bit)

Smartphone (please complete the following information):

• Device: N/A

Additional context

Add any other context about the problem here.

[grayeul]

I may have seen the same/similar problem... but I don't have a 'live' site, I have just been testing various things -- and not sure what shape my 5.5.0 system was in... so in my case, I just wiped the DB and started over -- but I was curious if there was a real problem with the upgrade path. Will be interesting to see if others run into this as well...

[tmhorne]

Digging into this, I found that the log is coming from /ChurchCRM/Service/UpgradeService.php.

On line 41, there's this thing where it looks in /mysql/upgrade.json, which does not have anything listed for 5.6.0.

 41             $dbUpdatesFile = file_get_contents(SystemURLs::getDocumentRoot() . '/mysql/upgrade.json');

But it would seem to use some *.sql files in /mysql/upgrade/*sql to upgrade the database.

I don't know what database transforms are needed from the old to the new, but at least I think I've found the issue.

[DAcodedBEAT]

For reference, I did not have any issues when upgrading from 5.5.0 to 5.6.0 via /UpgradeCRM.php (and subsequently /SystemDBUpdate.php)

[tmhorne]

@DAcodedBEAT, Awesome. I was unaware of /UpgradeCRM.php.
I will give that a try.

[rclayworth]

I had a similar problem to that of @tmhorne. It appears that instead of updating the version_ver table with the new ver_version value it actually inserted a new version row with a ver_ID of 2. Later, the attempt to update the ver_version of the record with the most recent ver_update time stamps fails with:

[2024-03-22T22:38:54.453583+00:00] defaultLogger.ERROR: Failure executing upgrade script(s): Unable to execute INSERT statement [INSERT INTO version_ver (ver_ID, ver_version, ver_update_start, ver_update_end) VALUES (:p0, :p1, :p2, :p3)] {"exception":"[object] (Propel\Runtime\Exception\PropelException(code: 0): Unable to execute INSERT statement [INSERT INTO version_ver (ver_ID, ver_version, ver_update_start, ver_update_end) VALUES (:p0, :p1, :p2, :p3)] at /var/www/html/ChurchCRM/model/ChurchCRM/Base/Version.php:785)\n[previous exception] [object] (PDOException(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '5.6.0' for key 'ver_version' at /var/www/html/vendor/propel/propel/src/Propel/Runtime/Connection/StatementWrapper.php:204)","scriptName":null,"version":"5.6.0","numUpgradeScriptsExecuted":0} {"url":"/SystemDBUpdate.php?upgrade=true","remote_ip":"127.0.0.1","correlation_id":"65fe087e6d53a"}

[DawoudIO]

I was able to revert the system to 5.5.0 and upgrade to 5.6.0 without an issue. did you guys have issues with the 1st time upgrading and now you tried it again?

[DawoudIO]

I made some UI/UX updates so that users do not dbl click buttons in #6934

[tmhorne]

I downgraded to 5.5.0, updated all the ownership & permissions, just to be sure.
Then I manually upgraded to 5.6.0, updated all the ownership & permissions, just to be sure.
Then I went to the base url of the site (in this case, http://churchcrm.horne.fam/ ) and hit the upgrade button.
And it worked, so I have no idea what was wrong, but it's all working now.

[tmhorne]

So I would say it's resolved now, but I have no idea what was wrong.