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
mysql>user
;flush privileges;
Within cctrl:
cctrl>datasource
cctrl>hostname
shun;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