Database Upgrade Failing
Posted: Mon Jun 15, 2020 7:17 am
1. I am attempting to migrate our installation to a new server (Linux to Linux). It fails during upgrading the database with, "TimeTrex is unable to connect to its database, please make sure that the database service on your own local TimeTrex server has been started and is running. If you are unsure, try rebooting your server."
2. I followed the CentOS installation instructions but before starting the web install (Step 9) I import the database and input the salt in the ini file. System checks pass, database 'Test Connection' works, Get the warning that it might take 10 minutes, etc., click Yes, goes to the Upgrading database, please wait screen for a few seconds then the above error dialog pops up, I can immediately refresh the page and do the same steps again (Database connection test works and everything) with the same result.
3. Going from version 9.1.2 to 12.2.0
4. CentOS8
5. Installed using .ZIP
-1. PHP is version 7.3.19
-2. PostgreSQL is version 10.6
-3. Apache (httpd) 2.4.37
As a sanity check, I attempted an in-place upgrade on a clone of the original server (Debian Stretch, PHP v7.0.33, PostgreSQL v9.6.17) with the same results.
Instruction corrections for CentOS. Need to add --permanent to the firewall commands otherwise the rules are deleted on reboot. Also need to add "firewall-cmd --reload" to make them active immediately.
Thank you for any help!!
2. I followed the CentOS installation instructions but before starting the web install (Step 9) I import the database and input the salt in the ini file. System checks pass, database 'Test Connection' works, Get the warning that it might take 10 minutes, etc., click Yes, goes to the Upgrading database, please wait screen for a few seconds then the above error dialog pops up, I can immediately refresh the page and do the same steps again (Database connection test works and everything) with the same result.
3. Going from version 9.1.2 to 12.2.0
4. CentOS8
5. Installed using .ZIP
-1. PHP is version 7.3.19
-2. PostgreSQL is version 10.6
-3. Apache (httpd) 2.4.37
As a sanity check, I attempted an in-place upgrade on a clone of the original server (Debian Stretch, PHP v7.0.33, PostgreSQL v9.6.17) with the same results.
Instruction corrections for CentOS. Need to add --permanent to the firewall commands otherwise the rules are deleted on reboot. Also need to add "firewall-cmd --reload" to make them active immediately.
Thank you for any help!!
Code: Select all
#17.unauthenticatedInvokeService(APIInstall, setDatabaseSchema, Array(1), 11eaaf0a-3e4d-07c4-bccc-567584267543, Object(APIAuthentication)) /var/www/timetrex/api/json/api.php:264
"
DEBUG [L0085] [04266ms]: DBError::__construct(): Code: DBError(-1) Message: postgres8 error: [-1: ERROR: column a.terminated_permission_control_id does not exist
DEBUG [L0085] [04266ms]: DBError::__construct(): LINE 44: ...rol as pcf ON ( a.company_id = pcf.company_id AND a.terminat...
DEBUG [L0085] [04266ms]: DBError::__construct(): ^] in adodb_throw(SELECT COUNT(*) FROM (
DEBUG [L0085] [04266ms]: DBError::__construct(): SELECT
DEBUG [L0085] [04266ms]: DBError::__construct(): a.*,
DEBUG [L0085] [04266ms]: DBError::__construct(): compf.name as company,
DEBUG [L0085] [04266ms]: DBError::__construct(): b.name as default_branch,
DEBUG [L0085] [04266ms]: DBError::__construct(): b.manual_id as default_branch_manual_id,
DEBUG [L0085] [04266ms]: DBError::__construct(): c.name as default_department,
DEBUG [L0085] [04266ms]: DBError::__construct(): c.manual_id as default_department_manual_id,
DEBUG [L0085] [04266ms]: DBError::__construct(): d.name as user_group,
DEBUG [L0085] [04266ms]: DBError::__construct(): e.name as title,
DEBUG [L0085] [04266ms]: DBError::__construct(): f.name as currency,
DEBUG [L0085] [04266ms]: DBError::__construct(): f.conversion_rate as currency_rate,
DEBUG [L0085] [04266ms]: DBError::__construct(): g.id as permission_control_id,
DEBUG [L0085] [04266ms]: DBError::__construct(): g.name as permission_control,
DEBUG [L0085] [04266ms]: DBError::__construct(): pcf.name as terminated_permission_control,
DEBUG [L0085] [04266ms]: DBError::__construct(): h.id as pay_period_schedule_id,
DEBUG [L0085] [04266ms]: DBError::__construct(): h.name as pay_period_schedule,
DEBUG [L0085] [04266ms]: DBError::__construct(): i.id as policy_group_id,
DEBUG [L0085] [04266ms]: DBError::__construct(): i.name as policy_group,
DEBUG [L0085] [04266ms]: DBError::__construct(): lef.legal_name,
DEBUG [L0085] [04266ms]: DBError::__construct(): lef.trade_name,
DEBUG [L0085] [04266ms]: DBError::__construct(): egf.name as ethnic_group,
DEBUG [L0085] [04266ms]: DBError::__construct(): CASE WHEN phc.is_child is NOT NULL THEN 1 ELSE 0 END as is_child, CASE WHEN a.id = '0' THEN 1 ELSE 0 END as is_owner, y.first_name as created_by_first_name,
DEBUG [L0085] [04266ms]: DBError::__construct(): y.middle_name as created_by_middle_name,
DEBUG [L0085] [04266ms]: DBError::__construct(): y.last_name as created_by_last_name,
DEBUG [L0085] [04266ms]: DBError::__construct(): z.first_name as updated_by_first_name,
DEBUG [L0085] [04266ms]: DBError::__construct(): z.middle_name as updated_by_middle_name,
DEBUG [L0085] [04266ms]: DBError::__construct(): z.last_name as updated_by_last_name
DEBUG [L0085] [04266ms]: DBError::__construct(): FROM users as a
DEBUG [L0085] [04266ms]: DBError::__construct(): LEFT JOIN company as compf ON ( a.company_id = compf.id AND compf.deleted = 0)
DEBUG [L0085] [04266ms]: DBError::__construct(): LEFT JOIN branch as b ON ( a.company_id = b.company_id AND a.default_branch_id = b.id AND b.deleted = 0)
DEBUG [L0085] [04266ms]: DBError::__construct(): LEFT JOIN department as c ON ( a.company_id = c.company_id AND a.default_department_id = c.id AND c.deleted = 0)
DEBUG [L0085] [04266ms]: DBError::__construct(): LEFT JOIN user_group as d ON ( a.company_id = d.company_id AND a.group_id = d.id AND d.deleted = 0 )
DEBUG [L0085] [04266ms]: DBError::__construct(): LEFT JOIN user_title as e ON ( a.company_id = e.company_id AND a.title_id = e.id AND e.deleted = 0 )
DEBUG [L0085] [04266ms]: DBError::__construct(): LEFT JOIN currency as f ON ( a.company_id = f.company_id AND a.currency_id = f.id AND f.deleted = 0 )
DEBUG [L0085] [04266ms]: DBError::__construct(): LEFT JOIN legal_entity as lef ON ( a.company_id = lef.company_id AND a.legal_entity_id = lef.id AND lef.deleted = 0 )
DEBUG [L0085] [04266ms]: DBError::__construct(): LEFT JOIN ethnic_group as egf ON ( a.company_id = egf.company_id AND a.ethnic_group_id = egf.id AND egf.deleted = 0 )
DEBUG [L0085] [04266ms]: DBError::__construct(): LEFT JOIN
DEBUG [L0085] [04266ms]: DBError::__construct(): (
DEBUG [L0085] [04266ms]: DBError::__construct(): SELECT g2.*, g1.user_id
DEBUG [L0085] [04266ms]: DBError::__construct(): FROM permission_user as g1, permission_control as g2
DEBUG [L0085] [04266ms]: DBError::__construct(): WHERE ( g1.permission_control_id = g2.id AND g2.deleted = 0)
DEBUG [L0085] [04266ms]: DBError::__construct(): ) as g ON ( a.id = g.user_id )
DEBUG [L0085] [04266ms]: DBError::__construct(): LEFT JOIN permission_control as pcf ON ( a.company_id = pcf.company_id AND a.terminated_permission_control_id = pcf.id AND pcf.deleted = 0 )
DEBUG [L0085] [04266ms]: DBError::__construct(): LEFT JOIN
DEBUG [L0085] [04266ms]: DBError::__construct(): (
DEBUG [L0085] [04266ms]: DBError::__construct(): SELECT h2.*, h1.user_id
DEBUG [L0085] [04266ms]: DBError::__construct(): FROM pay_period_schedule_user as h1, pay_period_schedule as h2
DEBUG [L0085] [04266ms]: DBError::__construct(): WHERE ( h1.pay_period_schedule_id = h2.id AND h2.deleted = 0)
DEBUG [L0085] [04266ms]: DBError::__construct(): ) as h ON ( a.id = h.user_id )
DEBUG [L0085] [04266ms]: DBError::__construct(): LEFT JOIN
DEBUG [L0085] [04266ms]: DBError::__construct(): (
DEBUG [L0085] [04266ms]: DBError::__construct(): SELECT i2.*, i1.user_id
DEBUG [L0085] [04266ms]: DBError::__construct(): FROM policy_group_user as i1, policy_group as i2
DEBUG [L0085] [04266ms]: DBError::__construct(): WHERE ( i1.policy_group_id = i2.id AND i2.deleted = 0)
DEBUG [L0085] [04266ms]: DBError::__construct(): ) as i ON ( a.id = i.user_id )
DEBUG [L0085] [04266ms]: DBError::__construct():
DEBUG [L0085] [04266ms]: DBError::__construct(): LEFT JOIN (
DEBUG [L0085] [04266ms]: DBError::__construct(): select phc_huf.user_id as user_id, 1 as is_child
DEBUG [L0085] [04266ms]: DBError::__construct(): from hierarchy_user as phc_huf
DEBUG [L0085] [04266ms]: DBError::__construct(): LEFT JOIN hierarchy_level as phc_hlf ON phc_huf.hierarchy_control_id = phc_hlf.hierarchy_control_id
DEBUG [L0085] [04266ms]: DBError::__construct(): LEFT JOIN hierarchy_object_type as phc_hotf ON phc_huf.hierarchy_control_id = phc_hotf.hierarchy_control_id
DEBUG [L0085] [04266ms]: DBError::__construct(): LEFT JOIN hierarchy_control as phc_hcf ON phc_huf.hierarchy_control_id = phc_hcf.id
DEBUG [L0085] [04266ms]: DBError::__construct(): WHERE
DEBUG [L0085] [04266ms]: DBError::__construct(): phc_hlf.user_id = '0'
DEBUG [L0085] [04266ms]: DBError::__construct(): AND phc_hcf.company_id = '1'
DEBUG [L0085] [04266ms]: DBError::__construct(): AND phc_hotf.object_type_id = 100
DEBUG [L0085] [04266ms]: DBError::__construct(): AND phc_huf.user_id != phc_hlf.user_id
DEBUG [L0085] [04266ms]: DBError::__construct(): AND ( phc_hlf.deleted = 0 AND phc_hcf.deleted = 0 )
DEBUG [L0085] [04266ms]: DBError::__construct(): ) as phc ON a.id = phc.user_id
DEBUG [L0085] [04266ms]: DBError::__construct():
DEBUG [L0085] [04266ms]: DBError::__construct(): LEFT JOIN users as y ON ( a.company_id = y.company_id AND a.created_by = y.id AND y.deleted = 0 )
DEBUG [L0085] [04266ms]: DBError::__construct(): LEFT JOIN users as z ON ( a.company_id = z.company_id AND a.updated_by = z.id AND z.deleted = 0 )
DEBUG [L0085] [04266ms]: DBError::__construct(): WHERE a.company_id = ?
DEBUG [L0085] [04266ms]: DBError::__construct():
DEBUG [L0085] [04266ms]: DBError::__construct(): AND ( a.deleted = 0 )
DEBUG [L0085] [04266ms]: DBError::__construct(): ) _ADODB_ALIAS_, Array)
DEBUG [L0092] [04266ms] Array: DBError::__construct(): Exception...
string(3768) " ADOConnection._Execute(SELECT COUNT(*) FROM ( SELECT a.*, compf.name as company, b.name as default_branch, b.manual_id..., Array[1])%
line 1282, file: /var/www/timetrex/classes/adodb/adodb.inc.php
ADOConnection.Execute(SELECT COUNT(*) FROM ( SELECT a.*, compf.name as company, b.name as default_branch, b.manual_id..., Array[1])% line 1770, file:
/var/www/timetrex/classes/adodb/adodb.inc.php
ADOConnection.GetOne(SELECT COUNT(*) FROM ( SELECT a.*, compf.name as company, b.name as default_branch, b.manual_id..., Array[1])% line 449, file: /
var/www/timetrex/classes/adodb/adodb-lib.inc.php
_adodb_getcount(Object:ADODB_postgres8, SELECT a.*, compf.name as company, b.name as default_branch, b.manual_id as default_branch_man..., Array[1], 0
)% line 507, file: /var/www/timetrex/classes/adodb/adodb-lib.inc.php
_adodb_pageexecute_all_rows(Object:ADODB_postgres8, SELECT a.*, compf.name as company, b.name as default_branch, b.manual_id as default_branch_man...,
1, 0, Array[1], 0)% line 3120, file: /var/www/timetrex/classes/adodb/adodb.inc.php
ADOConnection.PageExecute( SELECT a.*, compf.name as company, b.name as default_branch, b.manual_id as default_branch_man..., 1, 0, Array[1])% line 2540,
file: /var/www/timetrex/classes/modules/core/Factory.class.php
Factory.ExecuteSQL( SELECT a.*, compf.name as company, b.name as default_branch, b.manual_id as default_branch_man..., Array[1], 1, null)% line 2015, file
: /var/www/timetrex/classes/modules/users/UserListFactory.class.php
UserListFactory.getAPISearchByCompanyIdAndArrayCriteria(1, Array[0], 1, null, null, Array[4])% line 1740, file: /var/www/timetrex/classes/modules/company/PayrollRemittanceAgencyEventFactor
y.class.php
PayrollRemittanceAgencyEventFactory.calculateNextDate()% line 1061, file: /var/www/timetrex/classes/modules/company/PayrollRemittanceAgencyEventFactory.class.php
PayrollRemittanceAgencyEventFactory.preSave()% line 2746, file: /var/www/timetrex/classes/modules/core/Factory.class.php
Factory.Save(true, true)% line 2744, file: /var/www/timetrex/classes/modules/company/SetupPresets.class.php
SetupPresets.createPayrollRemittanceAgencyEvent(Array[8])% line 2800, file: /var/www/timetrex/classes/modules/company/SetupPresets.class.php
SetupPresets.createRemittanceAgencyEvents(5)% line 2719, file: /var/www/timetrex/classes/modules/company/SetupPresets.class.php
SetupPresets.PayrollRemittanceAgencys(us, tx)% line 501, file: /var/www/timetrex/classes/modules/install/InstallSchema_1093A.class.php
InstallSchema_1093A.postInstall()% line 294, file: /var/www/timetrex/classes/modules/install/InstallSchema_Base.class.php
InstallSchema_Base.InstallSchema()% line 131, file: /var/www/timetrex/classes/modules/install/InstallSchema.class.php
InstallSchema.__call(InstallSchema, Array[0])% line 853, file: /var/www/timetrex/classes/modules/install/Install.class.php
Install.createSchema(1093A)% line 751, file: /var/www/timetrex/classes/modules/install/Install.class.php
Install.createSchemaRange(null, null)% line 549, file: /var/www/timetrex/classes/modules/api/core/APIInstall.class.php
APIInstall.setDatabaseSchema(0)% line 60, file: /var/www/timetrex/api/json/api.php
unauthenticatedInvokeService(APIInstall, setDatabaseSchema, Array[1], 11eaaf0a-3e4d-07c4-bccc-567584267543, Object:APIAuthentication)% line 264, file: /var/www/timetrex/api/json/api.php
"
DEBUG [L0095] [04267ms]: DBError::__construct(): End Exception...
DEBUG [L0288] [04284ms] Array: TTMail::Send(): Attempting to send email To:
string(19) "errors@timetrex.com"
DEBUG [L0302] [04284ms]: TTMail::Send(): Sending Email: Body Size: 31072 Method: soap To:
DEBUG [L0345] [04284ms]: TTMail::Send(): 0. Recipient: errors@timetrex.com
DEBUG [L2548] [04286ms]: Factory::ExecuteSQL(): WARNING: Rethrowing Serialization Exception so it can be caught in an outside TRY block...
DEBUG [L2363] [04286ms]: Factory::FailTransaction(): FailTransaction(): Transaction: Count: 1 Off: 6 OK: 0
DEBUG [L0236] [04286ms]: TimeTrexSoapClient::getPrimaryCompanyData(): ERROR: Cant get company data for downloading upgrade file, database is likely down...
DEBUG [L0411] [04286ms] Array: TTMail::Send(): Send Email Failed To: errors@timetrex.com
bool(false)