MySQL Bugs: #23051: READ COMMITTED breaks mixed and statement-based replication

Bug #23051	READ COMMITTED breaks mixed and statement-based replication
Submitted:	6 Oct 2006 14:00	Modified:	5 Jul 2007 13:25
Reporter:	Guilhem Bichot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1-bk	OS:	Linux (linux)
Assigned to:	Mats Kindahl	CPU Architecture:	Any

Description:
When a transaction is in READ COMMITTED, InnoDB skips some locking which is needed to make mixed or statement-based binlogging work.

How to repeat:
Create this rpl_bug.test file:
-- source include/master-slave.inc
-- source include/have_innodb.inc

connection master;
CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into t1 values(10,2),(20,1);
show create table t1;

connection master;
set session transaction isolation level read committed;

set autocommit=0;
UPDATE t1 SET a=11 where b=2;

connection master1;
set session transaction isolation level read committed;
set autocommit=0;
UPDATE t1 SET b=2 where b=1;
COMMIT;

connection master;
COMMIT;

select * from t1;
show binlog events;
sync_slave_with_master;
show create table t1;
select * from t1;

connection master;
drop table t1;
sync_slave_with_master;

Also create a rpl_bug-slave.opt file in the same directory (so that the slave uses InnoDB), containing:
--innodb

Create an empty r/rpl_bug.result file.
Then run like this:
./mysql-test-run.pl --vardir=/dev/shm/test1 t/rpl_bug.test

You will see that on master, table contains (11,2) and (20,2), while on slave it contains (11,2),(11,2).

Guilhem,

this is the intended behavior. In the future, many InnoDB users will run with READ COMMITTED and use row-based replication.

mysqld should check if replication is used, and give a warning about READ COMMITTED and vice versa.

Regards,

Heikki

Hmm... at a second thought, this is a bug because mysqld should give the appropriate warnings! Reclassifying this as a replication bug.

To be precise:

1) If statement-based or mixed binlogging is enabled, then anyone setting the isolation level to READ COMMITTED should receive a warning;
2) If there is someone using READ COMMITTED, then another user who switches on statement-based or mixed binlogging should receive a warning.

We could also print something to the .err log. That would make debugging future bug reports much easier.

--Heikki

in 5.0 we instead get:
mysqltest: At line 24: query 'UPDATE t1 SET b=2 where b=1' failed: 1205: Lock wait timeout exceeded; try restarting transaction
as expected.
http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html
is now outdated, as it still mentions 
"UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters." which is not true anymore if READ COMMITTED.

Hi!

Tim noted that MySQL-5.1 should actually give an ERROR in if a client is running with READ COMMITTED or READ UNCOMMITTED, and the server is using statement-based or mixed binlogging. The risk of replication breaking with relaxed locking is very big. Therefore, a warning is not enough.

Regards,

Heikki

Apart from giving an error, another possibility, if in mixed mode, is to switch to row-based when an InnoDB table is used in READ COMMITTED.
Whatever is done, the check has to be put into some InnoDB-specific form (ha_innobase::external_lock()?), as we currently have no indication that Falcon (or Solid) would do statement-based-unsafe things in READ COMMITTED (they may want to be like InnoDB 5.0).

I agree with Heikki, Guilhem, Tim...

There is ongoing work to make a storage engine able to specify its
binlog format capabilites:

- HA_BINLOG_ALLOW_STATEMENT_BASED: Statement-based logging capable
- HA_BINLOG_ALLOW_ROW_BASED: Row-based logging capable

SUGGESTED SOLUTION
------------------
1. If someone executes an InnoDB transaction in READ COMMITTED mode
   with STATEMENT format, then give error.

2. If someone executes an InnoDB transaction in READ COMMITTED mode
   with MIXED format, then InnoDB should set
   HA_BINLOG_ALLOW_STATEMENT_BASED to false.
   The setting of this variable must be done before the logging
   starts.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/27500

ChangeSet@1.2581, 2007-05-28 19:56:39+02:00, mats@kindahl-laptop.dnsalias.net +5 -0
  BUG#23051 (READ COMMITTED breaks mixed and statement-based
  replication):
  
  Printing error when switching to READ COMMITTED transaction
  isolation level when in STATEMENT or MIXED binlog mode, or
  when trying to switch out of ROW binlog mode when in READ
  COMMITTED transaction isolation level. In the latter case, the
  transaction isolation level and the binlog format will be untouched.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/27781

ChangeSet@1.2584, 2007-05-31 11:03:06+02:00, mats@kindahl-laptop.dnsalias.net +5 -0
  BUG#23051 (READ COMMITTED breaks mixed and statement-based
  replication):
  
  Printing error when switching to READ COMMITTED transaction
  isolation level when in STATEMENT or MIXED binlog mode, or
  when trying to switch out of ROW binlog mode when in READ
  COMMITTED transaction isolation level. In the latter case, the
  transaction isolation level and the binlog format will be untouched.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/27783

ChangeSet@1.2584, 2007-05-31 11:12:23+02:00, mats@kindahl-laptop.dnsalias.net +6 -0
  BUG#23051 (READ COMMITTED breaks mixed and statement-based
  replication):
  
  Patch to add capabilities to the InnoDB storage engine. The engine will
  not allow statement format logging when in READ COMMITTED transaction
  isolation level.
  
  In addition, an informative error message is printed when trying to use
  READ COMMITTED transaction isolation level in STATEMENT binlog mode.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/28599

ChangeSet@1.2584, 2007-06-12 20:47:50+02:00, mats@kindahl-laptop.dnsalias.net +7 -0
  BUG#23051 (READ COMMITTED breaks mixed and statement-based
  replication):
  
  Patch to add binlog format capabilities to the InnoDB storage engine.
  The engine will not allow statement format logging when in READ COMMITTED
  or READ UNCOMMITTED transaction isolation level.
  
  In addition, an error is generated when trying to use READ COMMITTED
  or READ UNCOMMITTED transaction isolation level in STATEMENT binlog
  mode.

approved with minor comments sent by mail

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/28604

ChangeSet@1.2584, 2007-06-12 21:47:00+02:00, mats@kindahl-laptop.dnsalias.net +7 -0
  BUG#23051 (READ COMMITTED breaks mixed and statement-based
  replication):
  
  Patch to add binlog format capabilities to the InnoDB storage engine.
  The engine will not allow statement format logging when in READ COMMITTED
  or READ UNCOMMITTED transaction isolation level.
  
  In addition, an error is generated when trying to use READ COMMITTED
  or READ UNCOMMITTED transaction isolation level in STATEMENT binlog
  mode.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/28639

ChangeSet@1.2553, 2007-06-13 12:28:35+02:00, mats@kindahl-laptop.dnsalias.net +7 -0
  BUG#23051 (READ COMMITTED breaks mixed and statement-based replication):
  
  Post-merge fixes.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/28741

ChangeSet@1.2555, 2007-06-14 13:33:31+02:00, mats@kindahl-laptop.dnsalias.net +2 -0
  BUG#23051 (READ COMMITTED breaks mixed and statement-based replication):
  Temporarily using thd_tx_isolation() until decision is made on how to
  get the isolation level.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/29270

ChangeSet@1.2560, 2007-06-21 14:02:46+02:00, mats@kindahl-laptop.dnsalias.net +2 -0
  BUG#23051 (READ COMMITTED breaks mixed and statement-based replication):
  Moving error generating code from table_flags() to external_lock().

Pushed into 5.1.20-beta

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/29349

ChangeSet@1.2532, 2007-06-22 01:08:26+02:00, mats@kindahl-laptop.dnsalias.net +3 -0
  BUG#23051 (READ COMMITTED breaks mixed and statement-based replication):
  Minor fixes to get proper detection of transaction isolation level when
  inside external_lock().

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/29354

ChangeSet@1.2533, 2007-06-22 02:27:18+02:00, mats@kindahl-laptop.dnsalias.net +1 -0
  BUG#23051 (READ COMMITTED breaks mixed and statement-based replication):
  Removing dead code.

Pushed into 5.1.21-beta

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.1.21 changelog.