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  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.