Upgrade to 1.10+ fails on Postgres <9.1

[Marcus Leonard]

Upgrading from 1.5.1 to versions higher than 1.9 (either directly or incrementally by version) fails on Postgres 8.4.
(And I'm assuming any version of Postgres prior to 9.1.)

Existing installation of Mahara 1.5.1.
Run through normal upgrade steps.
Expected result: Successful upgrade.
Actual result: upgrade screen reports this error in core upgrade section:

Could not execute command: UPDATE "artefact" SET path = CONCAT('/', id) WHERE id IN (SELECT id FROM "artefact" WHERE path IS NULL AND parent IS NULL LIMIT 200)
Call stack (most recent first):
   * execute_sql("UPDATE {artefact} SET path = CONCAT('/', id) WHERE...") at /srv/www/mahara/mahara-15.10.0/htdocs/lib/db/upgrade.php:3340
   * xmldb_core_upgrade("2012080604") at /srv/www/mahara/mahara-15.10.0/htdocs/lib/upgrade.php:359
   * upgrade_core(object(stdClass)) at /srv/www/mahara/mahara-15.10.0/htdocs/admin/upgrade.json.php:90

Errors from Apache logs:

[DBG] 25 (lib/db/upgrade.php:3274) Filling in parent artefact paths, referer: https://HOSTNAME/admin/upgrade.php
[Tue Nov 17 16:11:01 2015] [error] [client --removed--] [DBG] 25 (lib/dml.php:147) postgres7 error: [-1: ERROR: function concat(unknown, bigint) does not exist, referer: https://HOSTNAME/admin/upgrade.php
[Tue Nov 17 16:11:01 2015] [error] [client --removed--] [DBG] 25 (lib/dml.php:147) LINE 1: UPDATE "artefact" SET path = CONCAT('/', id) WHERE id IN (SE..., referer: https://HOSTNAME/admin/upgrade.php
[Tue Nov 17 16:11:01 2015] [error] [client --removed--] [DBG] 25 (lib/dml.php:147) ^, referer: https://HOSTNAME/admin/upgrade.php
[Tue Nov 17 16:11:01 2015] [error] [client --removed--] [DBG] 25 (lib/dml.php:147) HINT: No function matches the given name and argument types. You might need to add explicit type casts.] in adodb_throw(UPDATE "artefact" SET path = CONCAT('/', id) WHERE id IN (SELECT id FROM "artefact" WHERE path IS NULL AND parent IS NULL LIMIT 200), ), referer: https://HOSTNAME/admin/upgrade.php
[Tue Nov 17 16:11:01 2015] [error] [client --removed--] [DBG] 25 (lib/dml.php:147) Command was: UPDATE "artefact" SET path = CONCAT('/', id) WHERE id IN (SELECT id FROM "artefact" WHERE path IS NULL AND parent IS NULL LIMIT 200), referer: https://HOSTNAME/admin/upgrade.php
[Tue Nov 17 16:11:01 2015] [error] [client --removed--] [WAR] 25 (lib/errors.php:745) Could not execute command: UPDATE "artefact" SET path = CONCAT('/', id) WHERE id IN (SELECT id FROM "artefact" WHERE path IS NULL AND parent IS NULL LIMIT 200), referer: https://HOSTNAME/admin/upgrade.php
[Tue Nov 17 16:11:01 2015] [error] [client --removed--] Call stack (most recent first):, referer: https://HOSTNAME/admin/upgrade.php
[Tue Nov 17 16:11:01 2015] [error] [client --removed--] * log_message("Could not execute command: UPDATE "artefact" SET p...", 8, true, true) at /srv/www/mahara/mahara-1.10.7/htdocs/lib/errors.php:95, referer: https://HOSTNAME/admin/upgrade.php
[Tue Nov 17 16:11:01 2015] [error] [client --removed--] * log_warn("Could not execute command: UPDATE "artefact" SET p...") at /srv/www/mahara/mahara-1.10.7/htdocs/lib/errors.php:745, referer: https://HOSTNAME/admin/upgrade.php
[Tue Nov 17 16:11:01 2015] [error] [client --removed--] * SQLException->__construct("Could not execute command: UPDATE "artefact" SET p...") at /srv/www/mahara/mahara-1.10.7/htdocs/lib/dml.php:148, referer: https://HOSTNAME/admin/upgrade.php
[Tue Nov 17 16:11:01 2015] [error] [client --removed--] * execute_sql("UPDATE {artefact} SET path = CONCAT('/', id) WHERE...") at /srv/www/mahara/mahara-1.10.7/htdocs/lib/db/upgrade.php:3290, referer: https://HOSTNAME/admin/upgrade.php
[Tue Nov 17 16:11:01 2015] [error] [client --removed--] * xmldb_core_upgrade("2014032729") at /srv/www/mahara/mahara-1.10.7/htdocs/lib/upgrade.php:359, referer: https://HOSTNAME/admin/upgrade.php
[Tue Nov 17 16:11:01 2015] [error] [client --removed--] * upgrade_core(object(stdClass)) at /srv/www/mahara/mahara-1.10.7/htdocs/admin/upgrade.json.php:86, referer: https://HOSTNAME/admin/upgrade.php

(Why does it say "postgres7 error" above?)

It looks like it's asking Postgres to use the CONCAT function which, as far as I'm aware, did not exist in Postgres until 9.1.
http://www.postgresql.org/docs/8.4/static/functions-string.html
http://www.postgresql.org/docs/9.1/static/functions-string.html

Mahara explicitly states that it's supported on "PostgreSQL, all versions from 8.3" ( https://wiki.mahara.org/wiki/System_Administrator's_Guide/Requirements#Database_Server )

[Aaron Wells (u-aaronw)]

Hi Marcus,

Thanks for the bug report! We do indeed aim to support Postgres 8.3 & up, although due to limits on our resources we no longer actively test against the older versions. But we try to maintain compatibility with those versions, and will fix bugs when found. I'll see if I can put together a patch for this.

The "postgres7" is a message coming from our database abstraction library, ADODB. Prior to upgrading to ADODB 5.19 in Mahara 15.04 (see Bug 1384473) Mahara used the "postgres7" adodb driver. If you're upgrading to 15.04 and still seeing it, well, the "postgres8" driver we use now subclasses the postgres7 driver, so it's possible it still prints some of its error messages.

Cheers,
Aaron

[Mahara Bot (dev-mahara)]

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

[Aaron Wells (u-aaronw)]

To test:

0. Have a Postgres version prior to 9.1 installed.
1. Install a Mahara version prior to 1.10
2. Upgrade Mahara to 1.10 or later.

Expected result: Clean upgrade
Actual result: Error stack seen in the bug description above.

The artefact upgrade code will get exercised even when upgrading a clean Mahara install, because there are a few artefacts created by the system (like the admin user's firstname and lastname).

Cheers,
Aaron

[Aaron Wells (u-aaronw)]

Hi Marcus,

I've uploaded a patch for this issue: https://reviews.mahara.org/#/c/5741/

Can you try that out on your system and see if it resolves the problem?

Cheers,
Aaron

[Marcus Leonard (marcus-leonard)]

Hi Aaron,

Cripes that was quick. Setting the bar a bit high. :-)

That worked. All install steps completed successfully on the upgrade page.

I got some trouble when I tried to go to a page, but just had to set date.timezone in php.ini and now all good.

BTW this all took place on Red Hat Enterprise Linux Server release 6.7 which still ships with an aaaancient Postgres.

I'm handing this test upgrade back to the LMS support people to check over, but it seems fine to me.

Thanks for the quick turnaround.

Cheers,
Marcus

[Aaron Wells (u-aaronw)]

Hi Marcus,

Thanks for the data point about RHEL 6.7. And I see from this page that its official support lifetime from Redhad is until July 2017! https://access.redhat.com/support/policy/updates/errata

It seems there is a significant minority of universities that cannot upgrade to newer database or PHP versions, because they can't get approval to upgrade their OS, because the OS is still "under support" by the manufacturer, like this one. That's really the reason why we still try to maintain compatibility with these very old database systems and PHP 5.3.

Cheers,
Aaron

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/5741
Committed: https://git.mahara.org/mahara/mahara/commit/7a0fef3e41c5a99594b3005c403f95f56a7c1a61
Submitter: Robert Lyon (<email address hidden>)
Branch: master

commit 7a0fef3e41c5a99594b3005c403f95f56a7c1a61
Author: Aaron Wells <email address hidden>
Date: Thu Nov 19 18:03:09 2015 +1300

CONCAT() not supported in Postgres prior to 9.1

Bug 1517658

Change-Id: I531a1c95b94910e564e71e1f2dc26754eefa45d5
behatnotneeded: platform-specific bug

[Mahara Bot (dev-mahara)]

Patch for "15.10_STABLE" branch: https://reviews.mahara.org/5743

[Mahara Bot (dev-mahara)]

Patch for "15.04_STABLE" branch: https://reviews.mahara.org/5744

[Mahara Bot (dev-mahara)]

Patch for "1.10_STABLE" branch: https://reviews.mahara.org/5745

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/5745
Committed: https://git.mahara.org/mahara/mahara/commit/4dc7149d845bdd8bbd56c4b08e782e92845beddc
Submitter: Aaron Wells (<email address hidden>)
Branch: 1.10_STABLE

commit 4dc7149d845bdd8bbd56c4b08e782e92845beddc
Author: Aaron Wells <email address hidden>
Date: Thu Nov 19 18:03:09 2015 +1300

CONCAT() not supported in Postgres prior to 9.1

Bug 1517658

Change-Id: I531a1c95b94910e564e71e1f2dc26754eefa45d5
behatnotneeded: platform-specific bug
(cherry picked from commit 7a0fef3e41c5a99594b3005c403f95f56a7c1a61)

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/5743
Committed: https://git.mahara.org/mahara/mahara/commit/274c4f179ad6fb3153cbf8f67dfef06f13639f4f
Submitter: Robert Lyon (<email address hidden>)
Branch: 15.10_STABLE

commit 274c4f179ad6fb3153cbf8f67dfef06f13639f4f
Author: Aaron Wells <email address hidden>
Date: Thu Nov 19 18:03:09 2015 +1300

CONCAT() not supported in Postgres prior to 9.1

Bug 1517658

Change-Id: I531a1c95b94910e564e71e1f2dc26754eefa45d5
behatnotneeded: platform-specific bug
(cherry picked from commit 7a0fef3e41c5a99594b3005c403f95f56a7c1a61)

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/5744
Committed: https://git.mahara.org/mahara/mahara/commit/249868b554c114d0aa7e82cb87bbfdb56cb6a6d6
Submitter: Robert Lyon (<email address hidden>)
Branch: 15.04_STABLE

commit 249868b554c114d0aa7e82cb87bbfdb56cb6a6d6
Author: Aaron Wells <email address hidden>
Date: Thu Nov 19 18:03:09 2015 +1300

CONCAT() not supported in Postgres prior to 9.1

Bug 1517658

Change-Id: I531a1c95b94910e564e71e1f2dc26754eefa45d5
behatnotneeded: platform-specific bug
(cherry picked from commit 7a0fef3e41c5a99594b3005c403f95f56a7c1a61)

[Marcus Leonard (marcus-leonard)]

Hi Aaron,

No idea why Red Hat are so far behind. Officially, Postgres was end-of-life in July 2014 (http://www.postgresql.org/support/versioning/), which is way past use-by date.

It may not be much immediate use at your end but, when talking to people who say they're effectively version-locked because of the SOE, there is an official alternative. If you're doing the full Red Hat Enterprise thing, which many unis are because the academic pricing is nice, you should have access to "Red Hat Software Collections" (I think it's enabled by default for academic licensing), which adds Postgres 9.2. So they actually can be supported *and* current. (I discovered this by accident here, and had just installed these packages when I saw your response with the patch, so I immediately backed out to avoid even more upgrading.)

Anyway, thanks for the quick fix, made my life much easier.

Cheers,
Marcus