img

MariaDB and granting rights

A trigger needed to be changed, and while the change was trivial, getting it done was not.
We got the following error: 
One or more errors have occurred while processing your request:
The following query has failed: "CREATE TRIGGER `test_trigger` BEFORE INSERT ON `test_table` FOR EACH ROW BEGIN IF (NEW.a = 0) OR NEW.b is null THEN SET NEW.c = 6.6; end if; END"

MySQL said: #1419 - You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
and strangely enough GRANTing TRIGGER rights to the poweruser did not work.
grant trigger on *.* to 'poweruser'@'10.10.10.10';
Same error. According to comments on this page: https://mariadb.com/kb/en/drop-trigger/ this is an old MySql bug that has landed into modern MariaDB versions, this bug causes that the TRIGGER privilege is not enough to create/drop triggers. Unfortunately it's also present in MariaDB 10.1.11.

There are 2 solutions:
1: do the trigger-edit as a superuser (e.g. database-root) 
grant super on *.* to 'poweruser'@'10.10.10.10';

(make the changes)

revoke super on *.* from 'poweruser'@'10.10.10.10';
yuck! no, that is just... disturbing!

2: set log_bin_trust_function_creators in the config file:
vi /etc/mysql/my.cnf
after [mysqld] add 
# nice if powerusers can not only edit tables, but also create triggers.
log_bin_trust_function_creators = 1
restart the database and Bob's your uncle.

Ellert van Koperen, October 2020.