Can't upgrade to 1.8rc1 because block_instance has an index instead of a key

[Aaron Wells]

See https://mahara.org/interaction/forum/topic.php?id=5770

At least three sites have had fatal errors while attempting to upgrade to 1.8rc1, because their block_instance table does not have a key called blockinst_viecolord_uk. Instead, the table has a unique index called blockinst_viecolord_uix

This particular key/index is a uniqueness key on the block_instance table, across the (view, column, order) columns. In the code, it's referred to as "viewcolumnorderuk". As part of the flexible layouts update, we replace it with a key on (view, row, column, order). To do that, we call drop_key() on the original. Unfortunately, the drop_key() method throws a fatal error if the key is not present.

At least two of the three reporting sites have been around since before Mahara version 1.0. However, it's to be expected that many of the biggest and highest-profile Mahara sites will have been around for a long time, so we should provide them with a clean upgrade path where possible.

[Aaron Wells (u-aaronw)]

To replicate:

1. Install a copy of Mahara 1.1. (This can be obtained by checking out the head of the 1.1_STABLE branch from gerrit or gitorious. There was no admin password prompt in 1.1, instead the default username will be "admin" and password will be "mahara")

2. Upgrade to 1.8rc1

Expected result: It should upgrade cleanly to 1.8rc1
Actual result: Upgrade dies with this error stack:

[DBG] e3 (lib/dml.php:167) postgres7 error: [-18: ERROR: constraint "blocinst_viecolord_uk" of relation "block_instance" does not exist] in adodb_throw(ALTER TABLE block_instance DROP CONSTRAINT blocinst_viecolord_uk, )
[DBG] e3 (lib/dml.php:167) Command was: ALTER TABLE block_instance DROP CONSTRAINT blocinst_viecolord_uk
[WAR] e3 (lib/errors.php:752) Could not execute command: ALTER TABLE block_instance DROP CONSTRAINT blocinst_viecolord_uk
Call stack (most recent first):
  * log_message("Could not execute command: ALTER TABLE block_insta...", 8, true, true) at /home/aaronw/www/mahara/htdocs/lib/errors.php:109
  * log_warn("Could not execute command: ALTER TABLE block_insta...") at /home/aaronw/www/mahara/htdocs/lib/errors.php:752
  * SQLException->__construct("Could not execute command: ALTER TABLE block_insta...") at /home/aaronw/www/mahara/htdocs/lib/dml.php:169
  * execute_sql("ALTER TABLE block_instance DROP CONSTRAINT blocins...") at /home/aaronw/www/mahara/htdocs/lib/dml.php:1403
  * execute_sql_arr(array(size 1), true, true) at /home/aaronw/www/mahara/htdocs/lib/ddl.php:1135
  * drop_key(object(XMLDBTable), object(XMLDBKey)) at /home/aaronw/www/mahara/htdocs/lib/db/upgrade.php:3327
  * xmldb_core_upgrade("2009022627") at /home/aaronw/www/mahara/htdocs/lib/upgrade.php:311
  * upgrade_core(object(stdClass)) at /home/aaronw/www/mahara/htdocs/lib/mahara.php:265
  * upgrade_mahara(array(size 20)) at /home/aaronw/www/mahara/htdocs/admin/cli/upgrade.php:62

[WAR] e3 (lib/dml.php:169) Could not execute command: ALTER TABLE block_instance DROP CONSTRAINT blocinst_viecolord_uk
Call stack (most recent first):
  * execute_sql("ALTER TABLE block_instance DROP CONSTRAINT blocins...") at /home/aaronw/www/mahara/htdocs/lib/dml.php:1403
  * execute_sql_arr(array(size 1), true, true) at /home/aaronw/www/mahara/htdocs/lib/ddl.php:1135
  * drop_key(object(XMLDBTable), object(XMLDBKey)) at /home/aaronw/www/mahara/htdocs/lib/db/upgrade.php:3327
  * xmldb_core_upgrade("2009022627") at /home/aaronw/www/mahara/htdocs/lib/upgrade.php:311
  * upgrade_core(object(stdClass)) at /home/aaronw/www/mahara/htdocs/lib/mahara.php:265
  * upgrade_mahara(array(size 20)) at /home/aaronw/www/mahara/htdocs/admin/cli/upgrade.php:62

A nonrecoverable error occurred. This probably means you have encountered a bug in the system

[Aaron Wells (u-aaronw)]

Traced it to the commit 55fe25fe57c2e6e13889f7b66e748607d464911a, in 1.2.0alpha3_release

Prior to this, "unique keys" specified as such in an install.xml file were actually created as unique indexes. This commit added support for true unique keys.

That's something to look out for on other upgrades. In fact, I'll file a bug about seeing if we can find all those legacy indexes and convert them into keys.

[Aaron Wells (u-aaronw)]

https://bugs.launchpad.net/mahara/+bug/1238373 for the larger issue of pre-1.2 sites having unique indexes where there should be unique keys.

[Aaron Wells (u-aaronw)]

https://reviews.mahara.org/#/c/2603