Make mysql_has_trigger_privilege() work better / more robust

[Robert Lyon]

Currently the function mysql_has_trigger_privilege() checks if we can make triggers on the database but if we have a problem during upgrade we can get in the state where the 'testtable' exists (not being cleaned up)

Also mysql_has_trigger_privilege() gets checked on every step of db upgrade

We need to do 2 things

1) Make the need to run mysql_has_trigger_privilege() more infrequent by setting a flag in the db config table if the check passes
2) Make the check more robust so that if the 'testtable' already exists (was not cleaned up) we handle that in a better manner, eg drop trigger if exists then try adding trigger again

[Yaju Mahida (yvm)]

https://mahara.org/interaction/forum/topic.php?id=8305

Hi Robert,

If binary logging is enabled, this requires either SUPER privileges or enabling the global log_bin_trust_function_creators system variable for creating a trigger and this applies to both MySQL 5.7 & latest MySQL 8.0.

Having the SUPER privileges all the time is not a good practice, so enable this during an upgrade and revoke it as soon it finishes would be ideal practice or enable global variable log_bin_trust_function_creators permanently. Both of these steps are manual at this stage which requires a DBA for each upgrade being a larger organization unless Mahara states log_bin_trust_function_creators as a mandatory requirement when binary logging is enabled - wink ;).

For this scenario where binary logging is enabled and want to avoid SUPER privileges/log_bin_trust_function_creators permanently, the check should happen each time whenever an upgrade is invoked (htdocs/admin/upgrade.php - the function ensure_upgrade_sanity is already doing this). To minimize the impact this routine should be removed from init.php.

Kind regards,
Yaju Mahida

[Mahara Bot (dev-mahara)]

Patch for "master" branch: https://reviews.mahara.org/8988

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/8988
Committed: https://git.mahara.org/mahara/mahara/commit/6307cc7fdfbf8c824bc7637d90317198242217ab
Submitter: Cecilia Vela Gurovic (<email address hidden>)
Branch: master

commit 6307cc7fdfbf8c824bc7637d90317198242217ab
Author: Yaju Mahida <email address hidden>
Date: Mon Jul 2 13:21:39 2018 +1000

Bug 1779049: Make mysql_has_trigger_privilege() work better / more robust

behatnotneeded

Author: Yaju Mahida <email address hidden>

Change-Id: I4a2c51a4a7af0842cc37ba710cdaec6c2d053d75