Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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