mysql "Unsafe statement"

Question

I get a lot of following messages

Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction. Statement:

I don't really understand why this happens. According to messages, it happens because in the code I try to INSERT and UPDATE Innodb table and MyISAM table. Is it right?

Is this dangerous? What's the consequences of this error.

How can I solve it. Can it be solved without changing the code?

Currently, it looks like by default, mysql uses BINLOG_FORMAT = STATEMENT. It means that replication on slaves runs like mysql statements? What's the preferable option for BINLOG_FORMAT?

Mysql version is 5.5.24.

Thanks

Answer 1

You can set any of these three BINLOG-FORMAT :

• STATEMENT causes logging to be statement based.

• ROW causes logging to be row based.

• MIXED causes logging to use mixed format.

The default binary logging format depends on the version of MySQL you are using:

• For MySQL 5.1.11 and earlier, and for MySQL 5.1.29 and later, statement-based logging is used by default.

• For MySQL 5.1.12 through MySQL 5.1.28, mixed logging is used by default.

You have to make sure that:

1. Each MySQL Server can set its own and only its own binary logging format (true whether binlog_format is set with global or session scope). This means that changing the logging format on a replication master does not cause a slave to change its logging format to match. (When using STATEMENT mode, the binlog_format system variable is not replicated; when using MIXED or ROW logging mode, it is replicated but is ignored by the slave.) Changing the binary logging format on the master while replication is ongoing, or without also changing it on the slave can thus cause unexpected results, or even cause replication to fail altogether.

2. When using statement-based logging for replication, it is possible for the data on the master and slave to become different if a statement is designed in such a way that the data modification is nondeterministic; that is, it is left to the will of the query optimizer. In general, this is not a good practice even outside of replication.

Also, this bug report is related about problem you have mentioned here :

http://bugs.mysql.com/bug.php?id=53259