Why do I get 'Binary logging not possible.' on my MySQL server?

mysql linux virtualbox toad
Steven picture Steven · Mar 12, 2012 · Viewed 11.3k times · Source

When I started up my MySQL server today and try to do some changes using Toad for Mysql, I get this message:

MySQL Database Error

Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'

I have no idea what this means. I'm running Mysql on VirtualBox with Ubuntu 11.x.

Has anyone run into this problem before?

Answer

Vikram picture Vikram · Mar 12, 2012

According to ERROR 1598 (HY000): Binary Logging not Possible. Message: Transaction Level READ-COMMITTED in InnoDB is not Safe for Binlog Mode STATEMENT:

There are several solutions for the issue:

  • You need to change the binlog mode to either ROW or MIXED in order to run the load of the data into the database

    mysql> SET GLOBAL binlog_format = 'ROW';

  • If you are not planning to use your MySQL server for the replication consider turning the binary logging off by removing the option --log-bin from the command options for the mysqld utility starting the MySQL server.

Related questions

error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)' -- Missing /var/run/mysqld/mysqld.sock

My problem started off with me not being able to log in as root any more on my mysql install. I was attempting to run mysql without passwords turned on... but whenever I ran the command # mysqld_safe --skip-grant-tables & …

Read More
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I am an electrical engineer who mainly plays around with power systems instead of programming. Recently, I have been following a manual to install a software suite on Ubuntu. I have no knowledge of mySQL at all, actually. I have …

Read More
How do I find the MySQL my.cnf location

Is there a MySQL command to locate the my.cnf configuration file, similar to how PHP's phpinfo() locates its php.ini?

Read More