Issue upgrading Mahara site to 17.04dev

[Kristina Hoeppner]

This issue arose from bug #1647885. The upgrade fails because the primary key is incorrect in the table artefact_file_mime_types

Robert sayd: Rather than dropping and re-creating the table, it would be better to truncate the table and change the primary key. That way, the table structure stays the same.

To replicate:

1. Install 16.04 and go through the regular setup steps.
2. Upgrade to master
Expected result: The upgrade completes. Actual result: Upgrade fails on artefact.file with the following error message, which can only be seen when you attempt to run the upgrade again:

[DBG] 0e (artefact/file/db/upgrade.php:446) Drop the old primary key constraint and add new id column from/to the table artefact_file_mime_types
[DBG] 0e (lib/dml.php:157) postgres8 error: [-1: ERROR: multiple primary keys for table "artefact_file_mime_types" are not allowed] in adodb_throw(ALTER TABLE "artefact_file_mime_types"
                            DROP CONSTRAINT IF EXISTS "artefilemimetype_mim_pk",
                            ADD COLUMN id SERIAL PRIMARY KEY, )
Command was: ALTER TABLE "artefact_file_mime_types"
                            DROP CONSTRAINT IF EXISTS "artefilemimetype_mim_pk",
                            ADD COLUMN id SERIAL PRIMARY KEY
[WAR] 0e (lib/errors.php:859) Could not execute command: ALTER TABLE "artefact_file_mime_types"
                            DROP CONSTRAINT IF EXISTS "artefilemimetype_mim_pk",
                            ADD COLUMN id SERIAL PRIMARY KEY
Call stack (most recent first):

    log_message(string(size 207), integer, true, true) at /home/kristina/code/mahara/htdocs/lib/errors.php:95
    log_warn(string(size 207)) at /home/kristina/code/mahara/htdocs/lib/errors.php:859
    SQLException->__construct(string(size 207)) at /home/kristina/code/mahara/htdocs/lib/dml.php:158
    execute_sql(string(size 180)) at /home/kristina/code/mahara/htdocs/artefact/file/db/upgrade.php:450
    xmldb_artefact_file_upgrade(string(size 10)) at /home/kristina/code/mahara/htdocs/lib/upgrade.php:423
    upgrade_plugin(object(stdClass)) at /home/kristina/code/mahara/htdocs/admin/upgrade.json.php:87

[Mahara Bot (dev-mahara)]

Patch for "16.10_STABLE" branch: https://reviews.mahara.org/7368

[Mahara Bot (dev-mahara)]

Patch for "master" branch: https://reviews.mahara.org/7369

[Dmitrii Metelkin (dmitriim)]

For the verifying its a good idea to test upgrade from 15.10, 16.04 to 16.10 and master.
Also test upgrade from 16.10.0, 16.10.1 to the latest 16.10 with the patch and to the master.

[Robert Lyon (robertl-9)]

Ok the problem here is in 16.10.0 there was a patch added (patch A) that altered the artefact_file_mime_types table and set a new primary key on a new id column - by first dropping the existing primary key based on the key name.

Later there was another patch (patch B) that was added after patch A that dropped the table and set a new primary key based on two columns.

Patch B was backported to earlier branches, eg 16.04.5

This meant if one now upgrades from 16.04.5 to 16.10.0 things will fail as the code to drop the primary key is now wrong and so is ignored and then tries to add a new primary key to the table, which is not allowed.

To stop this happening for subsequent 16.10 branch releases we need to remove the upgrade part for (patch A) as it is not needed any more as the upgrade for Patch B supersedes it.

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/7368
Committed: https://git.mahara.org/mahara/mahara/commit/1d0b47eff7d16053e6bd8117bba1fc8b45e9169d
Submitter: Robert Lyon (<email address hidden>)
Branch: 16.10_STABLE

commit 1d0b47eff7d16053e6bd8117bba1fc8b45e9169d
Author: Dmitrii Metelkin <email address hidden>
Date: Wed Dec 28 15:06:45 2016 +1100

Bug1651011: remove unnecessary update hook

Removing update hook to prevent failing upgrades from older versions
like 15.10, 16.04 and older 16.10. It's safe as the next hook will
recreate a table and resync filetypes.

behatnotneeded

Change-Id: I285a3f297a8459a8758b29ac0b9f7d4e97f38202

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/7369
Committed: https://git.mahara.org/mahara/mahara/commit/8770047332ecd246923a609ee5d37ac2a21f283d
Submitter: Robert Lyon (<email address hidden>)
Branch: master

commit 8770047332ecd246923a609ee5d37ac2a21f283d
Author: Dmitrii Metelkin <email address hidden>
Date: Wed Dec 28 15:06:45 2016 +1100

Bug1651011: Remove unnecessary update hook

Removing update hook to prevent failing upgrades from versions older
than 16.10. It's safe as the next hook will recreate a table and
resync file types.

behatnotneeded

Change-Id: I285a3f297a8459a8758b29ac0b9f7d4e97f38202