upgrade query takes too long in DB upgrade to 18.04

Bug #1841118 reported by Cecilia Vela Gurovic
This bug affects 2 people
Affects Status Importance Assigned to Milestone

Bug Description

When upgrading from 17.10 to 18.04, with MySQL 5.7 the upgrade script takes too long to upgrade the event_log table

In queries like

            log_debug('Adjust existing "event_log" data for "saveview" and "deleteview" events');
            $sql = "UPDATE {event_log} e
                    LEFT JOIN {view} v ON v.id = JSON_EXTRACT( CAST( e.data AS JSON ), '$.id')
                    SET e.resourceid = JSON_EXTRACT( CAST( e.data AS JSON ), '$.id'),
                        e.resourcetype = 'view',
                        e.ownerid = v.owner,
                        e.ownertype = CASE WHEN v.owner IS NULL THEN NULL ELSE 'view' END
                    WHERE e.event IN ('saveview', 'deleteview')";

if there are too many logs in the table, the sql query will take too long too run. This needs to be improved.

Changed in mahara:
status: New → Confirmed
importance: Undecided → High
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers