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:
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

[5 Oct 2009 23:18] steven tang
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> show variables like 'tx%';
| Variable_name | Value          |
| tx_isolation  | READ-COMMITTED |
1 row in set (0.00 sec)



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.
[5 Oct 2009 23:55] MySQL Verification Team
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 Oct 2009 16:10] steven tang
[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:

# Replication Options

# Replication settings
# If a master.info file exists, then master-* options are ignored.
# You should use CHANGE MASTER TO statement to change master-* options.




Please let me know what I should change to eliminate the error and make sure that my replication works as expected.
[6 Oct 2009 18:21] Sveta Smirnova
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')
[6 Oct 2009 18:27] Sveta Smirnova
Please ignore last comment regarding to READ UNCOMMITTED