2

I have stumbled upon an interesting MySQL Error message that I do not really know how to interpret.

The setup:

  • There are two tables A and B.
  • When data is written or updated in the table A, then a trigger is writing data to the table B.
  • Operations happen on a Master database
  • Data is replicated to a slave server

Now, whenever I decide to update data in table A, then it is updated and the corresponding log message is written to table B. MySQL, however, spawns the following error message:

Note: #1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.

Note: #1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly.

Note: #1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.

Nothing happy to the eye. How can I avoid these?

1 Answer 1

1

After a lot of playing with the data the answer turned out to be NOT to insert data within a trigger into a table with an auto_increment column. Once I have shifted the id to be generated via the UUID() function all warnings went away.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.