The way we add new columns to tables can cause db to lock up

Bug #1450676 reported by Robert Lyon on 2015-05-01
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Mahara
Medium
Unassigned

Bug Description

Currently when we want to add a column to an existing table we do something like this in lib/db/upgrade.php

$table = new XMLDBTable('group');
$field = new XMLDBField('allowarchives');
$field->setAttributes(XMLDB_TYPE_INTEGER, 1, null, XMLDB_NOTNULL, null, null, null, 0);
add_field($table, $field);

The problem is if the database is big it can cause things to lock up

Changing the above to a more direct:
execute_sql('ALTER TABLE {group} ADD COLUMN allowarchives SMALLINT NOT NULL DEFAULT 0');

seems to avoid this problem.

So this bug is here so we can find out why the current way is so resource intensive and/or what dangers there are doing it the more direct way.

Robert Lyon (robertl-9) on 2015-05-01
Changed in mahara:
status: New → Confirmed
Aaron Wells (u-aaronw) wrote :

The current method programattically generates SQL statements, and then executes them.

So the very first step on this, should be to determine exactly what SQL statements it is generating. Then we can compare them with your raw SQL and see why the performance is so much worse.

Cheers,
Aaron

tags: added: performance
Changed in mahara:
importance: Undecided → Medium
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers