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

Today while accessing django-admin to update certain information I was keep getting this error on Save - 

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.

Platform - django 1.3.1, mysql 5.5

While doing some initial research I came across its happening because of, when you are trying to insert something from admin into the table, first its inserting into temp table or you are reading from temp table and trying to insert into main table. It considers temp table as transactional table. according to following article -

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

However, it wasn't much of help. 

You should read about different types of binlog format - http://dev.mysql.com/doc/refman/5.1/en/binary-log-setting.html

you can simply switch to 'MIXED' and that might solve your problem -

SET GLOBAL binlog_format = 'MIXED'; 

If it doesn't then, read further and see different scenarios why it was happening.

I did further research and come across another thread where it was mentioned that, it might be happening because of certain tables are belog to MyISAM Engine, whereas certain tables belong to InnoDB.  I checked my db, and found out that it was correct! 

Tables for which I had downloaded script from server (which runs under mysql 5.0) had default Engine as MyISAM, and my local mysql is 5.5 which has default Engine listed as InnoDB. 

Unless you have specific table that needs a MyISAM specific feature such as fulltext indexes, you should always user InnoDB as Engine as per the most of the recommendations online and by mysql.

As of MySQL 5.5, InnoDB is the default storage Engine. and previous versions had MyISAM as default storage Engine. (reference http://dev.mysql.com/doc/refman/5.5/en/innodb-default-se.html)

to check if your tables using InnoDB as default engine, you can try below command -

SHOW VARIABLES LIKE 'have_innodb';

Below will show all engines for your mysqldb, and which one is default:

  

SHOW ENGINES;

You can check by generating create table script to check if its running under InnoDB or MyISAM.

CREATE TABLE `table_name` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(500) NOT NULL

  PRIMARY KEY (`id`) 

) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1$$

That means its running under MyISAM engine.

In order to change it to run under innodb, you can execute below alter statement -

ALTER TABLE table_name ENGINE=InnoDB;

On running above you might run into,

0 20:55:09 ALTER TABLE table_name ENGINE=InnoDB Error Code: 1217. Cannot delete or update a parent row: a foreign key constraint fails

This might be because foreign key been used by other tables, if we are updating the Engine for the table, you might want to go in order of foreign key reference.

Find out which tables are on InnoDB -


SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE engine = 'innodb'

If you running into this issue, follow above steps and it should be fine! Comments, Feedback welcome.