Upgrade from version 16.1.3 to current Fails

[s980845]

We recently started getting emails from you stating that our version is severly out of date and calculations are incorrect.
I attempted to update today, and when trying to upgrade the database, it fails with error 500.

Checking logs show function max() accruals does not exist.

Can we still upgrade, or is my version too old at this point?

[mikeb]

Can you paste the full and exact error message here please? It might be helpful to include 50-100 lines before and after the error too.

[s980845]

This is the only error that is showing.
Previous errors were from yesterday's attempt to upgrade so I didn't paste them here as its a repeat of this one.

Code: Select all

04-Jan-2024 18:00:59 America/Los_Angeles] PHP Fatal error:  Uncaught ADODB_Exception: postgres9 error: [-1: ERROR:  function max(uuid) does not exist
LINE 1: select max(id) from accrual
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.] in EXECUTE("select max(id) from accrual") in /home/twolifes/domains/timetrex.twolifestyles.org/public_html/classes/adodb/adodb-exceptions.inc.php:89
Stack trace:
#0 /home/twolifes/domains/timetrex.twolifestyles.org/public_html/classes/adodb/adodb.inc.php(1676): adodb_throw()
#1 /home/twolifes/domains/timetrex.twolifestyles.org/public_html/classes/adodb/adodb.inc.php(1642): ADOConnection->_Execute()
#2 /home/twolifes/domains/timetrex.twolifestyles.org/public_html/classes/adodb/adodb.inc.php(2194): ADOConnection->Execute()
#3 /home/twolifes/domains/timetrex.twolifestyles.org/public_html/classes/modules/install/Install.class.php(931): ADOConnection->GetOne()
#4 /home/twolifes/domains/timetrex.twolifestyles.org/public_html/classes/modules/install/Install.class.php(975): Install->initializeSequence()
#5 /home/twolifes/domains/timetrex.twolifestyles.org/public_html/classes/modules/install/Install.class.php(776): Install->initializeSequences()
#6 /home/twolifes/domains/timetrex.twolifestyles.org/public_html/classes/modules/api/core/APIInstall.class.php(533): Install->createSchemaRange()
#7 /home/twolifes/domains/timetrex.twolifestyles.org/public_html/api/json/api.php(139): APIInstall->setDatabaseSchema()
#8 /home/twolifes/domains/timetrex.twolifestyles.org/public_html/api/json/api.php(199): invokeService()
#9 /home/twolifes/domains/timetrex.twolifestyles.org/public_html/api/json/api.php(411): unauthenticatedInvokeService()
#10 {main}
  thrown in /home/twolifes/domains/timetrex.twolifestyles.org/public_html/classes/adodb/adodb-exceptions.inc.php on line 89

[mikeb]

It appears to be severely confused, as it should never be attempting to run that SQL query with your version of TimeTrex. So there is likely a much bigger problem occurring.

My guess is you somehow got duplicate records in at least one of your critical database tables. Have you done any manual database manipulations or any SQL dump restores by chance?

In your PostgreSQL console (psql), please run the following query and paste the full and complete output here:

Code: Select all

select * from system_setting;

[s980845]

I believe when we first installed this system, we did do a dump/restore.
It was originally hosted on windows, but we migrated it to Linux since windows itself seemed to be unstable.

Here is the output from phpPgAdmin.

Code: Select all

05f1f4eb-9ef8-6579-b904-6d21b7248844	auto_upgrade_failed	1
05f1f4eb-9edd-e7c8-b0aa-6d21b7248844	new_version	1
05b829c1-a75b-a26c-09d0-1de36ea99d45	update_notify	1
05b829c1-a7ac-0cd7-90cc-1de36ea99d45	anonymous_update_notify	0
05ca7285-bc93-1a95-14f4-32b61161b347	last_notification_sync_time	1629968102
05b82abb-9c5f-bb3b-e83d-1de36ea99d45	valid_install_requirements	1
05b829c1-22a5-d048-7a52-1de36ea99d45	system_version	16.1.3
05b829c1-22c1-2c2f-ec33-1de36ea99d45	system_version_install_date	1667561643
05b82abb-9c71-68fc-22ff-1de36ea99d45	auto_upgrade_failed	1
1de36ea9-9d86-c582-615d-000000000002	schema_version_group_A	1137A
05b82abb-9c41-b831-2885-1de36ea99d45	new_version	1
05b829c1-206d-2c75-b323-1de36ea99d45	tax_data_version	20221001
05b829c1-209c-5439-eb59-1de36ea99d45	tax_engine_version	1.0.61
05f1fb19-12a4-3af9-70b0-6d21b7248844	registration_key	9a07c40587c34a369e418c66b9281191
6d21b724-88f3-7d80-e506-000000000002	schema_version_group_A	1137A
05f1f4e1-d98c-fcdf-2268-6d21b7248844	tax_data_version	20221001
05f1f4e1-d995-2def-339b-6d21b7248844	tax_engine_version	1.0.61
05f1f4e1-d9cf-6874-63e1-6d21b7248844	system_version	16.1.3
05f1f4e1-d9d7-1d0a-9e84-6d21b7248844	system_version_install_date	1673408947
05f1f4e2-0925-4674-bcf6-6d21b7248844	update_notify	1
05f1f4e2-0938-aaa2-21be-6d21b7248844	anonymous_update_notify	0
05f1f4eb-9eee-ca73-32bd-6d21b7248844	valid_install_requirements	1
05f1f4ec-57f1-133c-8f53-6d21b7248844	last_notification_sync_time	1673409123

[mikeb]

Ya, every record in the "system_setting" table is duplicated, and that is causing it to get confused. Its possible other tables are similarly corrupted as well, though I'm not sure what actually occurred to determine this for sure.

You could start by trying to delete the duplicate records by name, as long as the value is the same. If the value is not the same, you need to determine which value is the "correct" one, otherwise you can further corrupt your database. Any of the "*_date" records it doesn't matter that much though, they will be updated to the latest value automatically.

Definitely make sure you have a good backup before making any changes.

[s980845]

Got it.
I'll run a pg_dump and then try to get this fixed.
Thank You!