MySQL database trigger owners and what not ..

[ZabbixSeeker]

So I have a vague memory of moving a database from one place to another. And things stopped working - some kind of mysql triggers weren't working. The database user Zabbix didn't have access to them, or something.

Does this ring a bell?

I'm considering renaming a database, and thus I need to create a new zabbix user probably with right to that database. But there will be database triggers and what not in that database that might break if I create a new zabbix database user or change the name of the database?

There are traps here, are there not?

[ZabbixSeeker]

What about stored procedures etc etc?

[ZabbixSeeker]

Here's part of the answer. If you run SQL command

show triggers from zabbix_server;

then you'll see that a specific user (the one you used to create the database) is the definer of the Zabbix database mysql triggers.

what this means, is that the database schema is tied to a user that you initialized the database with in the past. Those mysql triggers are part of that user - the definer of these triggers determines the permissions of those triggers.

So, if you think that you can just add another user, beyond 'zabbix'@'localhost', e.g. 'zabbix'@'%' that can connect from another host - you're screwed.

What other gotchas are there? Feels like this isn't the end of the story.

[ZabbixSeeker]

How do I Un-foobar this? I wanna get rid of the zabbix_server@10.. user.
Is it safe to drop all these triggers.. and re-define them with a sane user by using the init script for the version of this database?
How will these "definer" values affect upgrades?


mysql> SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER
-> FROM information_schema.TRIGGERS;
+-----------------+----------------------------+--------------------------+
| TRIGGER_SCHEMA | TRIGGER_NAME | DEFINER |
+-----------------+----------------------------+--------------------------+
| sys | sys_config_insert_set_user | mysql.sys@localhost |
| sys | sys_config_update_set_user | mysql.sys@localhost |
| zabbix_server | connector_insert | [email protected].% |
| zabbix_server | connector_update | [email protected].% |
| zabbix_server | connector_delete | [email protected].% |
| zabbix_server | proxy_group_insert | [email protected].% |
| zabbix_server | proxy_group_update | [email protected].% |
| zabbix_server | proxy_group_delete | [email protected].% |
.....

[ZabbixSeeker]

Why thought of the clever idea of using triggers in mysql instead of doing proper transactions - for this application? I bet a billion things have broken because of this.

Users bound to specific hosts are hardcoded into the triggers, forever, unless you do some kind of surgery on the database.