Thesql “Unsafe statement”

MySQLreplication

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

Best Answer

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:

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

Related Solutions

Mysql – the safest way to switch the binlog format at runtime

Since a binlog will have a specific format at the moment you do this, you may decide not to gamble with the two formats together although MySQL (eh Oracle [still can't roll off my tongue]) built this feature.

To play it totally safe without a mysql restart, try the following:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

This will leave the last binlog in the 'MIXED' format. The penultimiate (next to last) binlog exists merely bring closure the last binlog that was in the previous format.

All existing sessions prior to the first FLUSH LOGS; will start writing in the last binlog once UNLOCK TABLES; is executed.

Give it a Try !!!

CAVEAT

Giving credit where credit is due, my answer is really piggybacking off of @Jonathan's answer. I just close and open binlogs on top of that. He gets a +1 for bringing this out first.

UPDATE 2011-10-12 13:58 EDT

If you do this to an active Master and there are one or more Slaves replicating from that Master, you need to be concerned about the relay logs being in the new format as well. Here is what you can do:

On the Slave, run STOP SLAVE;

On the Master run these:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

On the Slave, run START SLAVE;

Running STOP SLAVE; and START SLAVE; rotates the relay logs and causes the new entries to be replicated whichever format it comes. You may want to apply the binlog_format change in the slave as well.

Thesql statement based replication – unsafe statements

It will probably cause differences on the slave in the long run. I've had the same warnings (but with INSERT DELAYED, so your mileage may vary), and on a couple of write intensive tables the data would be different a couple of times a week (leading to a lenghty dump and import).

The ordering of some things is not necessarily the same on every mysql server, so you might end up with the same data in a different order. This is what "not deterministic" means, you execute the exact same query, but you might end up with different results.

If your mysql version supports the MIXED mode for binlogging, I'd definitely use that. As you can see on http://dev.mysql.com/doc/refman/5.1/en/binary-log-formats.html, the only reason it's not MIXED by default is backward compatibility:

Support for row-based logging was added in MySQL 5.1.5. Mixed logging is available beginning with MySQL 5.1.8. In MySQL 5.1.12, MIXED become the default logging mode; in 5.1.29, the default was changed back to STATEMENT for compatibility with MySQL 5.0.

MIXED mode uses STATEMENT mode binlogging (the default), unless it knows the query is non deterministic, then it'll switch to ROW mode for those queries. With ROW mode, it simply sends the changed rows if any, so that's deterministic again.

Best Answer

Related Solutions

Mysql – the safest way to switch the binlog format at runtime

Thesql statement based replication – unsafe statements

Related Question