Wednesday, August 28, 2013

Django - Error caused by Mariadb/MySQL's BINLOG_FORMAT = STATEMENT

Today, when I was trying to run the celery task to make changes to a bulk of data (Mariadb5.5), I got the following error and the task cannot be done:

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




After a while of asking and Googling, found that  there are 2 possible reasons of this error:

1. By default, Mariadb5.5/MySQL5.5 enable the replication between db servers (master/slave)

2. And by default, Mariadb5.5/MySQL5.5 using innodb engine. Maybe not all of my tables were created under the same engine (I re-used some 3rd party django apps).



To solve this issue, set binlog_format as mixed:

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

References:
http://dba.stackexchange.com/questions/6150/what-is-the-safest-way-to-switch-the-binlog-format-at-runtime
http://generics7.blogspot.com/2012/08/unsafe-statement-written-to-binary-log.html