Updating mysql trigger via information_schema — access denied?

Posted on

Question :

I have some wrong “definer”s for some triggers in MySQL (actually MariaDB). I figured the easiest way of correcting them would be some version of:

use information_schema;
update triggers set definer=current_user() where trigger_schema='foobar';

It produces:

ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

In the same session, I can drop and re-create the triggers with “drop trigger” and “create trigger”, so I’m not understanding the permissions issue. Is there some privilege that my user needs that it doesn’t have?

Answer :

What you are doing is impossible because the INFORMATION_SCHEMA database is a database made up of in-memory read-only temporary tables (See my post How is INFORMATION_SCHEMA implemented in MySQL?)

You are going to have to drop the triggers and recreate them

I wrote a post on that before (Oct 02, 2011 : Can mysqldump dump triggers and procedures?)

Here is what you can do for all triggers in the foo database

STEP 01 : mysqldump the triggers

mysqldump -${MYSQL_CONN} -d -t --skip-routines --triggers foo > foo_make_triggers.sql

For clarification

STEP 02 : Create a script to drop all triggers

SQL="SELECT CONCAT('DROP TRIGGER ',trigger_name,';')"
SQL="${SQL} FROM information_schema.triggers"
SQL="${SQL} WHERE trigger_schema = 'foo'"
mysql ${MYSQL_CONN} -ANe"${SQL}" > foo_drop_triggers.sql

STEP 03 : Verify Contents of Each File

vi -R foo_drop_triggers.sql
vi -R foo_make_triggers.sql

STEP 04 : Edit all the definers in foo_make_triggers.sql as you know how

STEP 05 : Login to mysql and run this

use foo
source foo_drop_triggers.sql
source foo_make_triggers.sql

Test this on a staging server please


Leave a Reply

Your email address will not be published. Required fields are marked *