MySQL Bugs: #46364: MyISAM transbuffer problems (NTM problem)

Bug #46364	MyISAM transbuffer problems (NTM problem)
Submitted:	24 Jul 14:26	Modified:	4 Dec 16:21
Reporter:	Lars Thalmann
Status:	In progress
Category:	Server: Replication	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	Alfranio Correia	Target Version:	5.1+
Triage:	Triaged: D3 (Medium)

Description:
Mixing N (e.g. MyISAM) and T (e.g. InnoDB) statements in the same 
transaction can theoretically, in any implementation, cause one of 
two problems:

1. Recovery can't be done (N-changes not written to disk before commit)
2. Serializability is broken (N-changes written before T-changes in the log) 

This bug is about the potential of regression in the latest code.  

(See also BUG#28976.)

How to repeat:
CASE 1:
=======

create table foo (num_1 int);
create table foo2 (num_2 int, key foo2$num_2 (num_2)) engine=innodb;
insert into foo values (1);
insert into foo2 values (1),(2),(3);

connection 1                     connection 2
begin;
                                          begin;
update foo2 set num_2=12 where num_2=1;
                                          update foo2 set num_2=14 where num_2=3;
update foo set num_1=12;
                                          update foo set num_1=14;
                                          commit;
commit;

data on master:

mysql> select * from foo;
+-------+
| num_1 |
+-------+
|    14 |
+-------+

data on slave:

mysql> select * from foo;
+-------+
| num_1 |
+-------+
|    12 |
+-------+

CASE 2:
=======
create table t1 (a varchar(255));

connection 1                     connection 2

BEGIN
INSERT INTO t1 (a) VALUES
("Connection one");
                                 INSERT INTO t1 (a)
                                 VALEUS ("Connection two");

COMMIT

The transbuff is not used and the binlog is correct:

"Connection one"

"Connection two"

Suggested fix:
Several cases are described and each should be analyzed to ensure that the semantics have
not changed in recent versions.

If the semantics has changed, then an option may be introduced to get the old behavior
back.

CASE 3:
=======
connection open;
begin # transactions starts when connections opens
...bunch of sql denpending on what function it in
UPDATE configuration SET last_contact_id = last_insert_id(last_contact_id + 1);
INSERT INTO contacts with c_id = id above grabbed from last_insert_id()
more insert/updates into other tables
UPDATE configuration SET last_incident_id = last_insert_id(last_incident_id + 1);
UPDATE configuration SET last_ref_no_id = if(date(incident_date) >= CURRENT_DATE,
last_insert_id(last_ref_no_id+1), last_insert_id(0)), incident_date =
@incident_date:=greatest(CURRENT_DATE, incident_date);
INSERT INTO incicdents with i_id = id above grabbed from last_insert_id() and ref_no
frabled from the second statement.
more inserts/updates into other table.
Commit;
connect close

Basically there could 200 updates to the configuration table in one transactions and
there could be hundreds of other users trying to increment these at the same time. We
don't commit mid transaction. So someone sends us an API call we either commit the entire
thing or we don't.
Each function can make a call to increment some counters in the configuration table at
any point and it does this as needed.

Alfranio has analyzed case 1-3, and the semantics of this has 
not changed for the last 2 years.  So, this is not a regression.

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/89228

3160 Alfranio Correia	2009-11-03
      BUG#46364 MyISAM transbuffer problems (NTM problem)
      
      It is well-known that due to concurrency issues, a slave can become
      inconsistent when a transaction contains updates to both transaction and
      non-transactional tables in statement and mixed modes.
      
      In a nutshell, the current code-base tries to preserve causality among the
      statements by not writing non-transactional statements directly to the
      binary log. Unfortunately, modifications done to non-transactional tables
      on behalf of a transaction become immediately visible to other connections
      but may not immediately get into the binary log and therefore consistency
      may be broken.
      
      In general, it is impossible to automatically detect causality/dependency
      among statements by just analyzing the statements sent to the server. This
      happen because dependency may be hidden in the application code and it is
      necessary to know a Pryor all the statements processed in the context of
      a transaction such as in a procedure. Moreover, even for the few cases that
      we could automatically address in the server, the computation effort
      required could make the approach infeasible.
      
      So, in this patch we introduce the option "ignore-causality" that can be
      used to bypass the current behavior in order to write directly to binary
      log statements that change non-transactional tables.

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/91363

3217 Alfranio Correia	2009-11-23
      BUG#46364 MyISAM transbuffer problems (NTM problem)
      
      It is well-known that due to concurrency issues, a slave can become
      inconsistent when a transaction contains updates to both transaction and
      non-transactional tables in statement and mixed modes.
      
      In a nutshell, the current code-base tries to preserve causality among the
      statements by writing non-transactional statements to the txn-cache which
      is flushed upon commit. However, modifications done to non-transactional
      tables on behalf of a transaction become immediately visible to other
      connections but may not immediately get into the binary log and therefore
      consistency may be broken.
      
      In general, it is impossible to automatically detect causality/dependency
      among statements by just analyzing the statements sent to the server. This
      happen because dependency may be hidden in the application code and it is
      necessary to know a priori all the statements processed in the context of
      a transaction such as in a procedure. Moreover, even for the few cases that
      we could automatically address in the server, the computation effort
      required could make the approach infeasible.
      
      So, in this patch we introduce the option
          - "--binlog-direct-non-transactional-updates" that can be used to bypass
          the current behavior in order to write directly to binary log statements
          that change non-transactional tables.
     @ mysql-test/extra/rpl_tests/rpl_mixing_engines.inc
        Backported this from Celosia to improve the test cases related to the NTM issue.
     @ sql/log.cc
        Checks the --binlog-direct-non-transactional-updates before choosing
        to either use the trxn-cache or not.
     @ sql/mysqld.cc
        Introduces the option --binlog-direct-non-transactional-updates.
     @ sql/set_var.cc
        Introduces the option --binlog-direct-non-transactional-updates.
     @ sql/sql_class.h
        Introduces the option --binlog-direct-non-transactional-updates.