C.3.9. Triggers not firing correctly on Replica

Last Updated: 2013-11-01

Condition or Error

Newly created triggers are not firing when executed

Causes

  • If a new user (definer) was used to create the triggers, they may fail to be executed, raising the following warning in the logs:

    INFO | jvm 1 | 2013/10/16 04:21:33 | WARNING: Could not execute query » 
     org.drizzle.jdbc.internal.common.query.DrizzleQuery@60dc4c81: The » 
     MySQL server is running with the --read-only option so it cannot » 
     execute this statement 
    INFO | jvm 1 | 2013/10/16 04:21:33 | 2013-10-16 04:21:33,208 ERROR » 
     replicator.pipeline.SingleThreadStageTask [q-to-dbms] Event » 
     application failed: seqno=524545571 fragno=0 message=java.sql.SQLException: » 
     Statement failed on slave but succeeded on master 
    INFO | jvm 1 | 2013/10/16 04:21:33 | com.continuent.tungsten.replicator.applier.ApplierException: » 
     java.sql.SQLException: Statement failed on slave but succeeded on master

    This is an indication that the new definer does not have the required SUPER privilege and that a trigger is failing to run.

Rectifications

  • In order to fix this issue, the new definer should be given the SUPER privilege on each server and then replication should be restarted. The SUPER privilege allows the user to run a statement on a Replica server where the read_only flag has been turned on. If necessary, the scope of the privilege can be restricted to an individual schema. The GRANT statement should be done on every database server, while the shun and recover should only be done on the Replicas.

    mysql> grant SUPER on *.* to user;
    mysql> flush privileges;

    Within cctrl:

    cctrl> datasource hostname shun;
    cctrl> datasource hostname recover;

    You should continue to review the tungsten-replicator/log/trepsvc.log file to see what log messages are being written there. It appears that replication is still failing and it is probably related to the same issue. If you want us to review logs to interpret the results for you, you can upload the log file here and someone will look at it.

More Information

Section C.4.1, “Triggers”