Warning: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT

Question

Full error:

Warning: 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.

I only seem to get this error when trying to run raw SQL from Django. If I run the SQL from MySQL CLI client I do not get the same error. Here's the SQL:

UPDATE picture p
JOIN (
    SELECT @inc := @inc + 1 AS new_weight, id
    FROM (SELECT @inc := 0) temp, (
        SELECT id FROM picture
        WHERE album_id = 5
        ORDER BY taken_date ASC
    ) AS pw
) AS pw
ON p.id = pw.id
SET p.weight = pw.new_weight;

The purpose of this is to order the records, and apply a sequential number to the weight in order to persist this ordering in the database.

I've tried running this command within the client to see if I can replicate the issue however it still runs successfully:

mysql> SET GLOBAL binlog_format = 'STATEMENT';

Also, it's important that I either solve or rewrite the SQL to work with this constraint as the final application is almost certainly going to run across a master-slave database setup.

*EDIT: After reading a bit more binlog_format, it seems as though ROW or MIXED would be perfectly acceptable, however my main concern is not being able to replicate this issue in the MySQL CLI in order to test whether MIXED/ROW can infact solve this problem?

Hosbein

Answer 1

There is no way to rewrite the sql to not throw that error message. Any sort of order will throw that message as the slave might have different rows than the master. mixed will solve it, as it will only switch to row based replication when a statement is not safe to replicate with statement.

Sternutatory

Yes I think simply switching to mixed is the answer, and it solved the problem fine for me. Wish I could replicate on the CLI but it works now :) – Hosbein