01 May 2012, 21:41

Hi,

I've been running Mahara 1.4 on a development site hosted by Media Temple (yes, I know it is shared hosting, and I've read all the warnings, but, so far it has been working fine).

Today, I tried to upgrade to Mahara 1.5 but the upgrade failed because the script tried to create database trigger(s) which are not allowed on Media Temple. 

Are the triggers critical to Mahara Operation? is there any way to work around them with cron jobs? Does anyone else have this issue?

I realise that the stock answer would be to switch to a VPS and a host who supports triggers. Sadly that's not in my budget having just renewed my Media Temple hosting a month ago.

Thanks in advance for any suggestions.

05 May 2012, 23:36

Hello Fraser,

You might want to take a look at https://bugs.launchpad.net/mahara/+bug/990959 It seems like it's an issue with the provider.

Cheers

Kristina

06 May 2012, 22:42

Hi Fraser,

We actually agonised a bit over whether to use triggers or cron, so it's definitely possible to achieve the same thing using cron, but only if you're willing to do a bit of programming.  In the end we decided on triggers as the cleaner solution, but at the time we weren't aware how many shared hosts disabled them.  If you want to know what they're used for, the following is an email I sent to the other developers asking for their opinion.  Basically they're a workaround for some very slow postgres queries on larger sites.

---

When notification_internal_activity gets big, it's slow on postgres to
count the number of unread messages for a user the first time they
login.  This is one of the causes of slow logins on sites with lots of
notifications like mahara.org.

After a bit of messing around with a couple of other options, we
followed the advice of a colleague here who had a similar problem on a
really big site - and ended up deciding to store the unread message
count in the usr table along with every user.  This fixes the slow
count query completely.

Now we have two options for how to ensure these counts are correct.

The first is to use database triggers (when
notification_internal_activity is updated, also update the appropriate
field in the usr table).

See
https://reviews.mahara.org/#q,project:mahara+branch:master+topic:unread-count-trigger,n,z

The second is to use a daily cron job to count
notification_internal_activity records and reset all the values in the
usr table at once.

See
https://reviews.mahara.org/#q,project:mahara+branch:master+topic:unread-count-cron,n,z

Unfortunately, with both options, there is still the need for the
application to update counts every time the user reads or deletes a
notification.  With the trigger option, only the value stored in $USER
needs to be updated, but with the cron option, both $USER and the usr
table need to be updated.

The advantage of the trigger option is that there's no need for
another slow cron job, the counts will be more accurate, and there's
no need for the application to do separate db writes whenever users
read/delete notifications.  There's also no need to do a separate db
write when notifications are created.  The main disadvantage is that
it introduces a bunch of custom SQL, because we can't do the triggers
using the abstraction layer.

The advantage of the cron option is that it avoids the large chunks of
custom SQL, and everything will be more familiar for developers
because it doesn't depart from the usual Mahara way of doing these
things.  However, it requires explicit db writes to be added in the
application, as mentioned above.  It produces slightly less accurate
counts, but hopefully just when notification_internal_activity is
modified manually.

07 May 2012, 7:54

Thanks for the comprehensive reply Richard. As a database person, I totally support the decision to use Triggers, if only to keep all database related code within the scope of the database and not rely on external scripts etc.

It actually sounds like Media Temple's reasons for blocking Triggers is also justified if MySQL requires SuperUser privileges to create them. On a more positive side, MediaTemple do say that they are reviewing the decision so maybe one day MySQL will fix the issue and MediaTemple will relent.

I'm not keen to switch hosts at this stage because I have several other sites hosted on the same account so switching is not a trivial task.

I'll just have to return to the drawing board and rethink what I'm doing. It's a shame, because I really liked the sound of some of the 1.5 updates.

best regards

Fraser

07 May 2012, 16:56

The Godaddy support email on the bug report linked above says MySQL no longer requires superuser privileges for triggers in version 5.1, or at least later versions of 5.1.  So Media temple might be justified, depending on which version of MySQL they're offering.  But I think 5.0 is a bit old now; it already has an "End of Product Lifecycle" notice up on the manual page.