MySQL Bugs: #47842: Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in Inno

Bug #47842	Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in Inno
Submitted:	5 Oct 2009 23:18	Modified:	6 Oct 2009 18:21
Reporter:	steven tang	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.37-log	OS:	Linux (Linux dev82.webtech.local 2.6.18-164.el5 #1 SMP Thu Sep 3 03:28:30 EDT 2009 x86_64 x86_64 x86_64 GNU)
Assigned to:		CPU Architecture:	Any

Description:
I have setup a two instances on the same dev82 server and Master @3306 and Slave @3309 and had replication setup as well. 

I created a table:

CREATE TABLE `test_tbl` (
  `id` int(11) DEFAULT NULL,
  `descr` varchar(100) COLLATE latin1_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

C:\PROJECTS > mysql -uroot -p<pass> -hdev82 quadrant
and want to change it to use MyISAM engine in the master and I got:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.1.37-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> alter table test_tbl engine = MyISAM;
ERROR 1598 (HY000): Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT'
mysql>

mysql> show variables like 'tx%';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)

mysql>

 

How to repeat:
Every time when I tried to change engine.

Even when I started the two servers with the default tx_isolation=REPEATABLE-READ, I still got the same error which is really wierd.

Thank you for the bug report. Please read the last comment from James of bug:
http://bugs.mysql.com/bug.php?id=40360. Thanks in advance.

----
[6 Feb 4:48] James Day 
Note that if you use READ-COMMITTED and binary logging (including for replication) 5.1 you
must use row-based binlogging. It's expected that you will see this error if you try to
add rows to an InnoDB table otherwise. For more information see the manual at
http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html . You should change the
transaction isolation level or the binary logging mode. 
----

This comment is very vague when you follow the link to read MySQL document. 

I just want to make sure that when I use InnoDB with transaction-isolation=READ-COMMITTED, my replication works with every valid SQL statement.

The following are my replication settings:

[MASTER]
#
# Replication Options
#
binlog-do-db=quadrant
binlog-do-db=reportschedule
binlog-do-db=mysql
relay-log=dev82-relay-bin

[SLAVE]
#
# Replication settings
# If a master.info file exists, then master-* options are ignored.
# You should use CHANGE MASTER TO statement to change master-* options.
#
replicate-do-db=quadrant
replicate-do-db=reportschedule
replicate-do-db=mysql

master-host=dev82
master-port=3306
master-user=webtechrp
master-password=webtechrp

relay-log=/opt/mysql_s/data/mysql-relay-bin
sync_binlog=1

replicate-ignore-table=quadrant.xmlrecordpending2
replicate-ignore-table=quadrant.activityreportgpsr
replicate-ignore-table=quadrant.activityreportvehicle
replicate-wild-ignore-table=quadrant_archive.%
slave-skip-errors=1062

Please let me know what I should change to eliminate the error and make sure that my replication works as expected.

Thank you for the feedback.

You don't use binary log format=ROW while using transaction isolation  level 'READ-COMMITTED'. So this behavior is expected. Either use binary log format=ROW/MIXED or less strict transaction isolation  level ('READ-UNCOMMITTED')

Please ignore last comment regarding to READ UNCOMMITTED