25 August 2015, 21:14

I am attempting to upgrade a Mahara 1.8.1 to 15.04.3

I got the following error message
Failed to get a recordset: postgres7 error: [-1: ERROR: null value in column "reportedtime" violates not-null constraint] in adodb_throw(INSERT INTO "objectionable" ("objecttype", "objectid", "reportedby", "report", "resolvedby", "resolvedtime") VALUES (?, ?, ?, ?, ?, ?), Array) Command was: INSERT INTO "objectionable" ("objecttype", "objectid", "reportedbycd ", "report", "resolvedby", "resolvedtime") VALUES (?, ?, ?, ?, ?, ?) and values was (view,33274,0,,0,2014-01-09 10:59:47)

I did some digging around and found that this seems to be caused by us having some null values in the ctime field of the view_access table. I did an insert of nonsense values into that table, i.e.

update view_access set ctime='1800-01-01' where ctime is null
And the upgrade completed without error and clicking around seems to indicate that the system is working.

My question is, does anyone know how that ctime is used and is there a chance that my bogus value might cause a problem? If I link the usr value in view_access to the usr table there are about 266 active users so it affects a reasonable number of users.

Looking back through Mahara release history (to around 1.4) it seems that the ctime value has always been not null and at some time it has been changed on our system, so there might be a good reason for it being changed (or possibly a bad reason).

10 September 2015, 1:32

Dear Marcus,

The leap from 1.8 to 15.04 is a big one. I had such a problem jumping from an old version of Mahara to a more recent one.

My problem was that the database schema has been altered and was not conform to what the new version of Mahara was requesting.

I had to use commercial database management tools to compare the database schema and put into conformity the old one.

Before doing this, I would advise to upgrade your Mahara by small leaps

1. to the latest 1.8.x version
2. then to the last 1.9 version
3. then to the last 1.10 version
4. then to the 15.04.3 version

You will then see whether (hope not) and where you get an error during the upgrade.

Of course before anything else : do a backup of your system (databases and files)

HTH

-dajan

10 September 2015, 18:48

Hello,

If you have a look on the bug report (https://bugs.launchpad.net/mahara/+bug/1494128/), it seems that Mahara's team is on this since you mentioned it here.

Follow this bug to be informed when the issue is solved.

Regards,

-dajan

10 September 2015, 19:26

Hi Marcus,

As dajan noticed, I took a look at this!

It looks like this is a problem that can happen when you're upgrading from a Mahara site that was initially installed as version 1.4 or earlier. It's caused by the fact that the Mahara 1.5 upgrade script created view_access.ctime as a null-able column, while the install.xml file used for a clean install creates it as a not-null column.

Then, in the Mahara 1.10 upgrade, we migrated the old "objectionable page" access records to a new table, and we used the view_access.ctime column on those as the report date for the new table. So if your Mahara has a some objectionable item reports with null access times (which they will if they were created in 1.4 or earlier), it errors out.

Back in Mahara 1.8.0 I tried to detect and fix all the schema drift caused by sloppy upgrade scripts in earlier releases, but I must have missed this view_access.ctime one.

Anyway, I've filed a bug report about this here: https://bugs.launchpad.net/mahara/+bug/1494152

And I've got a patch for it here, which should let you upgrade: https://reviews.mahara.org/#/c/5254/1

To the best of my knowledge, this is the only place we use view_access.ctime for anything, so if you've already filled it in with arbitrary values, you're probably fine. Looking at the git history, I see the column was added in 2011 for Mahara 1.5. But the commit messages don't explain why it was added, and this was before I instituted the policy of putting bug numbers in every commit message. I'd guess the column was probably just added for auditing reasons. It's often handy to be able to tell when a record was created.

Cheers,

Aaron