notification.internal trigger upgrade and MySQL

Bug #967001 reported by Ruslan Kabalin
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Mahara
Fix Released
Medium
Richard Mansfield

Bug Description

If mahara user in mysql has been created using mahara wiki guide (grant all on (databasename).* to 'username'@'localhost' identified by 'password';) everything should work fine, however if some external tool like 'mysql administrator' has been used to create mahara user initially, TRIGGER permission might not be granted, which will result in the error similar to below:

 Could not execute command: CREATE TRIGGER "update_unread_insert_trigger" AFTER INSERT ON "notification_internal_activity" FOR EACH ROW BEGIN IF NEW.read = 0 THEN UPDATE "usr" SET unread = unread + 1 WHERE id = NEW.usr; END IF; END
Call stack (most recent first):
execute_sql(" CREATE TRIGGER {update_unread_insert_...") at /home/ruslan/git/services/vle/mahara/htdocs/lib/dml.php:1757
db_create_trigger("update_unread_insert", "AFTER", "INSERT", "notification_internal_activity", " IF NEW.read = 0 THEN ...") at /home/ruslan/git/services/vle/mahara/htdocs/notification/internal/db/upgrade.php:64
xmldb_notification_internal_upgrade("2009012700") at /home/ruslan/git/services/vle/mahara/htdocs/lib/upgrade.php:374
upgrade_plugin(object(stdClass)) at /home/ruslan/git/services/vle/mahara/htdocs/admin/upgrade.json.php:94

I think upgrade process should be more intuitive and in case of error, it should provide information on permission-related problem along with possible solution (e.g. Your database user might not have been granted TRIGGER permission, please run GRANT TRIGGER on ... ).

Changed in mahara:
milestone: none → 1.5.0
importance: Undecided → Low
Changed in mahara:
status: New → Triaged
importance: Low → Medium
assignee: nobody → Richard Mansfield (richard-mansfield)
Revision history for this message
Ruslan Kabalin (rkabalin) wrote :

As Andrew Nicols pointed out, this could be an issue for postgresql too.

Melissa Draper (melissa)
Changed in mahara:
status: Triaged → In Progress
summary: - notification.internal trugger upgrade and MySQL
+ notification.internal trigger upgrade and MySQL
Revision history for this message
Mahara Bot (dev-mahara) wrote : A change has been merged

Reviewed: https://reviews.mahara.org/1128
Committed: http://gitorious.org/mahara/mahara/commit/73822ab6895b5f36b68ba541299d08bfb99ded04
Submitter: Francois Marier (<email address hidden>)
Branch: 1.5_STABLE

commit 73822ab6895b5f36b68ba541299d08bfb99ded04
Author: Richard Mansfield <email address hidden>
Date: Thu Mar 29 15:29:44 2012 +1300

    Show useful message on install/upgrade without triggers (bug #967001)

    If the MySQL user doesn't have trigger permission, an SQLException was
    thrown along with an unhelpful generic error message. This privilege
    is now checked before beginning the install/upgrade, and the message
    is now informative.

    Change-Id: If35750610cdc84e97bf8f2974d496dea43b8b991
    Signed-off-by: Richard Mansfield <email address hidden>

Changed in mahara:
milestone: 1.5.0 → none
status: In Progress → Fix Released
Revision history for this message
Mahara Bot (dev-mahara) wrote :

Reviewed: https://reviews.mahara.org/1129
Committed: http://gitorious.org/mahara/mahara/commit/c3038f1ab836b76d3abc21f1739432531002a869
Submitter: Francois Marier (<email address hidden>)
Branch: master

commit c3038f1ab836b76d3abc21f1739432531002a869
Author: Richard Mansfield <email address hidden>
Date: Thu Mar 29 15:29:44 2012 +1300

    Show useful message on install/upgrade without triggers (bug #967001)

    If the MySQL user doesn't have trigger permission, an SQLException was
    thrown along with an unhelpful generic error message. This privilege
    is now checked before beginning the install/upgrade, and the message
    is now informative.

    Change-Id: If35750610cdc84e97bf8f2974d496dea43b8b991
    Signed-off-by: Richard Mansfield <email address hidden>

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.