Support tool flagging DB errors when there are none

[Adriano Faria]

Working on a resource that adds 33 columns of core_groups table on INSTALL version. OK. For the 1.0.0 final version, I added one TEXT column and saved all my info in a JSON format and dropped all those columns in the upgrade.php of the final version:

            $toDrop = array(
                'g_school_comission_sale', 'g_can_act_as_dean', 'g_can_view_history', 'g_can_delete_schools', 'g_can_view_closed_schools', 'g_can_edit_schools', 'g_can_feature_schools', 'g_can_unfeature_schools', 'g_change_owner', 'g_can_create_schools', 'g_nr_schools', 'g_can_link_unlink_club', 'g_can_change_school_status', 'g_can_move_courses', 'g_can_assign_instructors', 'g_bulk_message_school', 'g_bulk_message_school', 'g_can_create_courses', 'g_can_create_module', 'g_can_create_lesson', 'g_can_create_courses', 'g_bulk_message', 'g_can_mark_lesson_complete', 'g_can_add_students_manually', 'g_can_feature_courses', 'g_can_customize_schools', 'g_can_instructor', 'g_can_create_affiliates', 'g_can_create_quiz', 'g_can_add_lesson_notes', 'g_can_unfeature_courses', 'g_view_history'
            );

            foreach( $toDrop as $column )
            {
                try
                {
                    \IPS\Db::i()->dropColumn( 'core_groups', $column );
                }
                catch( \IPS\Db\Exception $e ){}
            }
        }

So now the user goes to the Support Tool and runs it and it says:

Quote

Find a solution
×
There are some problems with your database. Normally it is safe to try to fix these problems automatically however if your community is large, you may want to run the necessary queries manually. If so, the queries to run are:
ALTER TABLE `lms_schools` ADD COLUMN `school_comission` TINYINT (1) UNSIGNED NOT NULL DEFAULT 10 ;
ALTER TABLE `core_groups` ADD COLUMN `g_nr_schools` SMALLINT (5) UNSIGNED NOT NULL DEFAULT 0;
...
...

 

All the 33 fields are there and the system is requesting the user to recreate them by click in the FIX button.

So, is this right? The columns were intentionally removed in the upgrade. So why the system is trying to make the user recreate them? The app is running fine... they're not required anymore.

 

[teraßyte]

I believe the support tool compares the database and the schema.json file in your application's data folder. Is that file updated too with the columns removed?

 

EDIT

Btw, instead of dropping each column by itself, why not just drop them all at once? The function supports also an array as column value:

$toDrop = array( 'g_school_comission_sale', 'g_can_act_as_dean', 'g_can_view_history', 'g_can_delete_schools', 'g_can_view_closed_schools', 'g_can_edit_schools', 'g_can_feature_schools', 'g_can_unfeature_schools', 'g_change_owner', 'g_can_create_schools', 'g_nr_schools', 'g_can_link_unlink_club', 'g_can_change_school_status', 'g_can_move_courses', 'g_can_assign_instructors', 'g_bulk_message_school', 'g_bulk_message_school', 'g_can_create_courses', 'g_can_create_module', 'g_can_create_lesson', 'g_can_create_courses', 'g_bulk_message', 'g_can_mark_lesson_complete', 'g_can_add_students_manually', 'g_can_feature_courses', 'g_can_customize_schools', 'g_can_instructor', 'g_can_create_affiliates', 'g_can_create_quiz', 'g_can_add_lesson_notes', 'g_can_unfeature_courses', 'g_view_history' );

try
{
	\IPS\Db::i()->dropColumn( 'core_groups', $toDrop );
}
catch( \IPS\Db\Exception $e ){}

 

[Adriano Faria]

No, schema.json has only the tables. Most of all those columns were in the setup/version_Install json files and others in the next versions. 

Anyway, only 4 users were upgrading it since Alpha stage, so removed all the versions files and added the text column on core_groups on install. That will fix it as I cannot wait.

But I would like to know why.

EDIT: yeah, could drop them all too but still wouldn’t change the issue. Dropping one by one, it would leave behind that one that wasn’t dropped for some reason.

[Martin A.]

If you drop columns that were added in the install routine in a future update, you need to remove those from install/queries.json too. None of the upgrade routines runs when you do a fresh install, then you'll end up with all of those 33 columns, and not the new text column you created.

The database checker will include install/queries.json too, in addition to those mentioned by tera. It does not know that you dropped those columns in an update, hence why it think it's a problem.

[Adriano Faria]

Tks. That’s what I did: removed from install version and left only the TEXT one, which is storing all the others. ?