Wednesday, August 29, 2012

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 -

However, it wasn't much of help. 

You should read about different types of binlog format -

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

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:

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

CREATE TABLE `table_name` (
  `name` varchar(500) NOT NULL
  PRIMARY KEY (`id`) 

That means its running under MyISAM engine.

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


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
WHERE engine = 'innodb'

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


  1. is it bad to change all tables to innodb?

  2. I don't think its bad or good, converting to innodb should be based on your requirement.

    If you want data integrity, you should go with innodb. It implements row-level lock and supports transactions for inserting and updating while myisam implements table-level lock, and no transactions support. Unlike myisam, innodb has foreign keys and relationship contraints. If your app is more readonly text heavy, myisam is your friend as it has full-text search index and if its more of CRUD operations with transactions, you should go with innodb.

    Before start converting all tables, go over some stuff mentioned here for index explanation mentioned here -

  3. thanks, but is it better if all tables have the same engine?

    1. As I explained earlier it depends on your requirement. If you have 50 tables, out of that if 5 tables are write intensive and others where you might be doing lot of reads, in that case you can mix it up and have innodb and myisam in same database.

      When you say better, I would say better is what makes your application respond faster. From developer point it might make life easy to have all tables in one engine to avoid issues like above explained.