Updating mysql trigger via information_schema -- access denied?

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?

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

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysqldump -${MYSQL_CONN} -d -t --skip-routines --triggers foo > foo_make_triggers.sql

For clarification

• -d is the same as --no-data

• -t is the same as --no-create-info

STEP 02 : Create a script to drop all triggers

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
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

GIVE IT A TRY !!!

StackExchange Database Administrators Q#78268, answer score: 6