Replication and Triggers

Known issue: In MySQL 5.0.17, the syntax for CREATE TRIGGER changed to include a DEFINER clause for specifying which access privileges to check at trigger invocation time. (See Section 13.1.11, “CREATE TRIGGER Syntax”, for more information.) However, if you attempt to replicate from a master server older than MySQL 5.0.17 to a slave running MySQL 5.0.17 through 5.0.19, replication of CREATE TRIGGER statements fails on the slave with a Definer not fully qualified error. A workaround is to create triggers on the master using a version-specific comment embedded in each CREATE TRIGGER statement:

CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER ... ;

CREATE TRIGGER statements written this way will replicate to newer slaves, which pick up the DEFINER clause from the comment and execute successfully.

This slave problem is fixed as of MySQL 5.0.20.

User Comments
Sign Up Login You must be logged in to post a comment.