Pachno ~ [CLOSED] #2901 - Database errors when upgrading from 4.1.9 to 4.3.1

This issue has been closed with status "Closed" and resolution "Not determined".

Description

When upgrading from version 4.1.9 to 4.3.1 via CLI (web-based upgrades are still a big bugged-out), The Bug Genie reports that upgrade has been successful, but there are still errors listed on the console, and it looks like not all upgrades have been performed (see below the output in reproduction steps).

The issue tracker seems to be functional on the surface, however accessing the dashboard will present the user with errors, and things like recent activity will not be listed on any dashboard.

Upon further inspection, the problem comes from certain parts of upgrade being run prior to database schema being fully brought up-to-date. The upgrade code will invoke a number functions that will access the database, however these functions have been written with up-to-date (4.3.1) database schema in mind, so they will fail (in this particular case due to missing columns - log.project_id).

The offending code can be found under core/modules/installation/Upgrade.php:_upgradeFrom4dot1dot13() function:

        $milestones = tables\Milestones::getTable()->selectAll();
        foreach ($milestones as $milestone)
        {
            $milestone->updateStatus();
            $milestone->save();
        }

When the milestone is being saved, in its post-save phase it will attempt to generate log items, stored in the log table. Table log at this point in time still does not contain the project_id column (which gets created much later in transition to version 4.3.0). Therefore when the code attempts to save the log items to it, it will fail.

Long-term, looks like more thought needs to be paid to ordering of upgrade operations. Very likely the database schema has to be upgrade first, followed by any kind of logic that might be needed to bring the database into consistent state that might depend on the schema to be fully up-to-date, and maybe even on top of that some other operations may be required (maybe setting additional constraints or something similar).

Reproduction steps:

Install The Bug Genie version 4.1.9.
Populate instance with sample data.
Upgrade The Bug Genie to version 4.3.1 via CLI.
Log-in as an administrator, and access the user's dashboard page.

Expected results:

In step 3, no errors are reported during the upgrade.
In step 4, user is presented with complete dashboard, including the recent activity (the What you've done recently section).

Actual results:

In step 3, the upgrade script reports errors, and it looks like the upgrade is actually incomplete (last upgraded to version reported is 4.1.13):

Performing upgrade: 4.1.9 -> 4.3.1

Gathering information before upgrading...

We're continuously adjusting and improving user security. As a result, this version changes the way passwords are handled and stored.
All users will require password resets after the upgrade process, and application-specific passwords must be regenerated.

Because of the improved password handling, we need to set a password for the admin account administrator
New password for user with username administrator (min 8 characters): admin123 
Upgrading table thebuggenie\core\entities\tables\NotificationSettings
Successfully upgraded to version 4.1.10
Adding indexes for table thebuggenie\core\entities\tables\ScopeHostnames
Adding indexes for table thebuggenie\core\entities\tables\Notifications
Adding indexes for table thebuggenie\core\entities\tables\WorkflowTransitionValidationRules
Adding indexes for table thebuggenie\core\entities\tables\WorkflowTransitionActions
Adding indexes for table thebuggenie\core\entities\tables\WorkflowStepTransitions
Adding indexes for table thebuggenie\core\entities\tables\Links
Adding indexes for table thebuggenie\core\entities\tables\LogItems
Adding indexes for table thebuggenie\core\entities\tables\Teams
Adding indexes for table thebuggenie\core\entities\tables\IssueCustomFields
Adding indexes for table thebuggenie\core\entities\tables\ListTypes
Successfully upgraded to version 4.1.12
Successfully upgraded to version comments
Successfully upgraded to version 4.1.13
Creating table thebuggenie\core\entities\tables\UserSessions
Updating/fixing status of milestones.

---------------------
An error occured during the upgrade:
Unknown column 'log21.project_id' in 'field list'
---------------------

In step 4, user's recent activity is shown as empty, and the user is presented with the following error notification (or something roughly similar to it):

Unknown column 'log36.project_id' in 'field list'

If debug mode is enabled, the error notification is not shown, and instead a more detailed error traceback can be seen in the place where the user's recent activity should be shown.

Aug 12, 2021 (10:38)

This is an error in the upgrade routine. It can be fixed by manually adding the project_id column to the log table before running the upgrade:

(the following example assumes the default table prefix)

alter table tbg3_log add column project_id int(10) default 0;