Error installing triggers for new mahara site in MySQL with dbprefix

Bug #1326205 reported by Ghada El-Zoghbi on 2014-06-04
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Mahara
Medium
Ghada El-Zoghbi

Bug Description

Version: Mahara 1.10.0.dev (master)
OS: Linux
db: MySql
Browser: Firefox

When creating a brand new Mahara install, getting the following error:

Component: artefact.file
Version: 1.2.2

Could not execute command: CREATE TRIGGER "testing_unmark_quota_exeed_notified_on_update_usr_setting_trigger" AFTER UPDATE ON "testing_usr" FOR EACH ROW BEGIN UPDATE "testing_usr_account_preference", "testing_artefact_config" SET "testing_usr_account_preference".value = 0 WHERE "testing_usr_account_preference".field = 'quota_exceeded_notified' AND "testing_usr_account_preference".usr = NEW.id AND "testing_artefact_config".plugin = 'file' AND "testing_artefact_config".field = 'quotanotifylimit' AND NEW.quotaused/NEW.quota < "testing_artefact_config".value/100; END
Call stack (most recent first):

    execute_sql(" CREATE TRIGGER {unmark_quota_exeed_no...") at /var/www/maharacode/mahara/htdocs/lib/dml.php:1794
    db_create_trigger("unmark_quota_exeed_notified_on_update_usr_setting", "AFTER", "UPDATE", "usr", " UPDATE {usr_account_preference}, ...") at /var/www/maharacode/mahara/htdocs/artefact/file/lib.php:167
    PluginArtefactFile::set_quota_triggers() at /var/www/maharacode/mahara/htdocs/artefact/file/lib.php:103
    PluginArtefactFile::postinst(0) at Unknown:0
    call_user_func_array(array(size 2), array(size 1)) at /var/www/maharacode/mahara/htdocs/lib/mahara.php:1562
    call_static_method("PluginArtefactFile", "postinst", 0) at /var/www/maharacode/mahara/htdocs/lib/upgrade.php:543
    upgrade_plugin(object(stdClass)) at /var/www/maharacode/mahara/htdocs/admin/upgrade.json.php:86

Ghada El-Zoghbi (ghada-z) wrote :
Ghada El-Zoghbi (ghada-z) wrote :

Please note: Postgres is fine. It's only an issue with MySql.

Ghada El-Zoghbi (ghada-z) wrote :

More information: When a db prefix is used (i.e. 'testing'), the MySql error is:

Error Code: 1059. Identifier name 'testing_unmark_quota_exeed_notified_on_update_usr_setting_trigger' is too long

Changed in mahara:
assignee: nobody → Ghada El-Zoghbi (ghada-z)
Aaron Wells (u-aaronw) wrote :

Marking as "medium" priority because dbprefix is not that commonly used anymore. On the other hand, most people who use it will be mysql users (on cheap shared hosting setups that only give them one database).

Definitely worth fixing, though.

tags: added: mysql
summary: - Error installing triggers for new mahara site
+ Error installing triggers for new mahara site in MySQL with dbprefix
Changed in mahara:
importance: Undecided → Medium
status: New → In Progress
milestone: none → 1.10.0
Ghada El-Zoghbi (ghada-z) wrote :

Reviewed: https://reviews.mahara.org/3405
Committed: http://gitorious.org/mahara/mahara/commit/5573da00efb053766119de274b8ee80abbabd127
Submitter: Son Nguyen (<email address hidden>)
Branch: master

commit 5573da00efb053766119de274b8ee80abbabd127
Author: Ghada El-Zoghbi <email address hidden>
Date: Wed Jun 4 15:52:07 2014 +1000

Bug 1326205: Error installing triggers for new mahara site in MySQL with dbprefix

MySql has a maximum of 64 characters for identifiers.
Shorten the names of the triggers to allow space for dbprefix and the '_trigger' suffix.
Apply the name change to both postgres and mysql to keep names aligned.

Change-Id: I729a5e5f4dd8b33dba1aa3f8ddeae92457c50c27
Signed-off-by: Ghada El-Zoghbi <email address hidden>

Aaron Wells (u-aaronw) wrote :

Doesn't affect 1.7, 1.8, or 1.9, because the triggers with the long names weren't added until 1.10dev

no longer affects: mahara/1.7
no longer affects: mahara/1.8
no longer affects: mahara/1.9
Changed in mahara:
status: In Progress → Fix Committed
Aaron Wells (u-aaronw) wrote :

This actually brings up the issue of what maximum length of dbprefix we should support anyway.

I've filed a separate bug for that: https://bugs.launchpad.net/mahara/+bug/1332373

Aaron Wells (u-aaronw) on 2014-10-21
Changed in mahara:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers