Page 1 of 1

Database Upgrade Failing

Posted: Mon Jun 15, 2020 7:17 am
by SleepIT1
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!!

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)

Re: Database Upgrade Failing

Posted: Mon Jun 15, 2020 8:03 am
by shaunw
v9.1.2 is much too old and upgrading from it is not officially supported anymore. Having said that you could try upgrading to v11.0.2 first, then try upgrading to v12.2.0. You can download v11.0.2 here:
https://www.timetrex.com/direct_downloa ... 11.0.2.zip

Re: Database Upgrade Failing

Posted: Mon Jun 15, 2020 8:25 am
by SleepIT1
Thank you! Will test this now.

Re: Database Upgrade Failing

Posted: Tue Jun 16, 2020 7:47 am
by SleepIT1
Worked great stepping it up. Thanks!
SleepIT1 wrote: Mon Jun 15, 2020 8:25 am Thank you! Will test this now.