Error installing triggers for new mahara site in MySQL with dbprefix

[Ghada El-Zoghbi]

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)]

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

[Ghada El-Zoghbi (ghada-z)]

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

[Aaron Wells (u-aaronw)]

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.

[Ghada El-Zoghbi (ghada-z)]

Submitted for review: https://reviews.mahara.org/#/c/3405/

[Mahara Bot (dev-mahara)]

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)]

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

[Aaron Wells (u-aaronw)]

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