01 November 2022, 23:07

Hi Just testing the latest upgrade and getting upgrade errors on the "Tidy up user chosen themes for portfolios" part of the upgrade script. It appears I have views that cannot be instantiated (https://github.com/MaharaProject/mahara/blob/651004442934136c32cadc4a4658405a7e24a865/htdocs/lib/db/upgrade.php#L2495).

[INF] c8 (admin/cli/upgrade.php:70) Upgrading Mahara
[INF] c8 (lib/mahara.php:242) Upgrading core
[WAR] c8 (lib/db/upgrade.php:2473) Anonymous registration data is now opt-out
[DBG] c8 (lib/db/upgrade.php:2489) Tidy up user chosen themes for portfolios
[DBG] c8 (lib/db/upgrade.php:2501) 100/7848
[DBG] c8 (lib/db/upgrade.php:2501) 200/7848
You tried to access a page that does not exist.

Anyone else hitting this. I'm assuming this is orphaned data. Any suggested work-around? I'm think about catching the exception and skipping.

But I also think the query in /lib/view.php is incorrect (https://github.com/MaharaProject/mahara/blob/651004442934136c32cadc4a4658405a7e24a865/htdocs/lib/view.php#L267).

Currently it has:

SELECT v.*
FROM {view} v LEFT JOIN {group} g ON v.group = g.id
WHERE v.id = ? AND (v.group IS NULL OR g.deleted = 0)

But having g.deleted on the WHERE statement when you're using a LEFT JOIN can't be right.

I think it should read:

SELECT v.*
FROM {view} v LEFT JOIN {group} g ON v.group = g.id AND (v.group IS NULL OR g.deleted = 0)
WHERE v.id = ? 

03 November 2022, 10:43

Hi Mark,

That does seem to a bizarre error - the upgrade step there selects a bunch of view ID from the database if they have the 'theme' column set to a value (so not null).

Then loops through the view ID and make a view object based on the ID so it would not make sense that it was orphaned data because we just fetched the ID from the same table we are now checking that the ID exists in.

Can I get you to do a check on your database to see if there is any odd data is present:

 SELECT * FROM view WHERE "group" IN (SELECT id FROM "group" WHERE deleted = 1);

This should return no rows as there shouldn't be any pages in a deleted group. If there are rows returned then that data needs to be fixed up.

As for the suggestion about the query change this sounds sensible as it should execute faster - though both options will return the same data (unless you have some odd data in database).

Cheers

Robert Lyon

28 March 2023, 0:10

Hi,

I ran into the same error when trying to upgrade from 22.04.2 to 22.04.3 (or 22.10.0 - makes no difference).

Running Roberts query against our database returns 46 entries. So how to "fix them up"? Is it safe to just delete these 46 views together with anything that needs to be deleted for keeping referential integrity intact? I found this to be the minimal set of instructions to clear our database from these views and be able to do the upgrade on a clone.

delete FROM `view_access` where view in (select id from view WHERE `group` IN (SELECT id FROM `group` WHERE deleted = 1));
delete FROM `view_rows_columns` where view in (select id from view WHERE `group` IN (SELECT id FROM `group` WHERE deleted = 1));
delete FROM `view_artefact` WHERE `block` in (SELECT id FROM `block_instance` where view in (select id from view WHERE `group` IN (SELECT id FROM `group` WHERE deleted = 1)));
delete FROM `block_instance` where view in (select id from view WHERE `group` IN (SELECT id FROM `group` WHERE deleted = 1));
delete FROM view WHERE `group` IN (SELECT id FROM `group` WHERE deleted = 1);

Is there any way to check the database for more orphans beyond what is already taken care of by referential integrity inside the database definition?

Kind regards

Stefan

28 March 2023, 9:32

Hi Stefan,

Those steps look to be the minimum of what is needed to fix that issue.

As for other potential places for orphans - a view can be owned by either a person (owner), group, or institution. So you could also check the following:

SELECT * FROM view WHERE "institution" NOT IN (SELECT name FROM "institution");
SELECT * FROM view WHERE "owner" IN (SELECT id FROM "usr" WHERE deleted = 1);

Cheers

Robert

10 May 2023, 3:15

Just to add I am getting error upgrading from 20.10.2 to 22.10.0

11 May 2023, 7:33

Hi Jackie,

What error do you get? Please check the error log as 'An unrecoverable error occurred...' is only the generic error message so details are not made public when they may contain sensitive information. Therefore, please check the error log.

Thank you

Kristina

11 May 2023, 20:34

Thanks for your reply Kristina

I have now managed to upgrade to 22.10.
It was as Robert suggested 
'That does seem to a bizarre error - the upgrade step there selects a bunch of view ID from the database 
if they have the 'theme' column set to a value (so not null).'

I did follow the deletes as Stefan suggested but even following the order I still came across foreign key constraint when deleting from 'view'.

 So I checked for any themes  when running

SELECT * FROM view WHERE "group" IN (SELECT id FROM "group" WHERE deleted = 1);

and set them as null.

This worked :)