Bug #53075 SBR: Strange warning around CONNECTION_ID()
Submitted: 22 Apr 2010 18:12 Modified: 8 May 2010 17:03
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.6.99-m4 OS:Any
Assigned to: Alfranio Tavares Correia Junior CPU Architecture:Any
Tags: CONNECTION_ID

[22 Apr 2010 18:12] Matthias Leich
Description:
My script:
----------
--source include/master-slave.inc

CREATE TABLE t1 (f1 BIGINT) ENGINE = MyISAM;
INSERT INTO t1 SET f1 = 1;
CREATE TABLE t2 (f1 BIGINT) ENGINE = MyISAM;
INSERT INTO t2 SET f1 = 1;
SET SESSION BINLOG_FORMAT = STATEMENT;
SET SESSION AUTOCOMMIT = 0;
COMMIT;
UPDATE t1 SET f1 = CONNECTION_ID();
UPDATE t2 SET f1 = 2;

Result on 5.6.99-m4
mysql-next-mr-bugfixing revno: 3147 2010-04-22
----------------------------------------------
...
CREATE TABLE t1 (f1 BIGINT) ENGINE = MyISAM;
INSERT INTO t1 SET f1 = 1;
CREATE TABLE t2 (f1 BIGINT) ENGINE = MyISAM;
INSERT INTO t2 SET f1 = 1;
SET SESSION BINLOG_FORMAT = STATEMENT;
SET SESSION AUTOCOMMIT = 0;
COMMIT;
UPDATE t1 SET f1 = CONNECTION_ID();
UPDATE t2 SET f1 = 2;
Warnings:
Note 1592 Unsafe statement binlogged in statement
format since BINLOG_FORMAT = STATEMENT.
Reason for unsafeness: Non-transactional reads
or writes are unsafe if they occur after 
transactional reads or writes inside a transaction.

1. There are no transactional reads or writes
   before the UPDATE t2 SET f1 = 2.
   So the warning is plain wrong.
2. If I replace the
      UPDATE t1 SET f1 = CONNECTION_ID();
   with
      UPDATE t1 SET f1 = 13
   than the warning disappears at all.
3. If I use UPDATE t1 SET f1 = UUID(); instead
   I get
   UPDATE t1 SET f1 = UUID()
   Warnings:
   Warning 1366 Incorrect integer value: ...for column 'f1' at row 1
     <= correct, I really assign garbage
   Note 1592 Unsafe statement binlogged in statement format
   since BINLOG_FORMAT = STATEMENT. Reason for unsafeness:
   Statement uses a system function whose value may differ on slave.
     <== correct, this is unsafe    
The interesting fact is that
- the warning depends on use of CONNECTION_ID within the
  preceding statement
- the informations about CONNECTION_ID and replication are
  inconsistent
  http://bugs.mysql.com/bug.php?id=29928
     Make CONNECTION_ID safe for SBR by using some
     tricky SET pseudo_thread_id= XXX  
  http://forge.mysql.com/wiki/MySQL_Internals_Replication
     ...
     Note: the following non-deterministic functions are not marked unsafe: 
           CONNECTION_ID (Query_log_events contain the connection number) 
     <== If it is unsafe, why not mark it as unsafe ? 

I will add results for other MySQL versions soon.

How to repeat:
See above
[22 Apr 2010 18:36] Matthias Leich
./client/mysqlbinlog var/log/main.rpl_ml4/mysqld.1/data/master-bin.000001
shows
...
# at 1255
#100422 20:32:29 server id 1  end_log_pos 1352  Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1271961149/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
UPDATE t1 SET f1 = CONNECTION_ID()
/*!*/;

So IMHO CONNECTION_ID() shoudn't be unsafe in SBR.
[23 Apr 2010 0:08] Bugs System
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/106403

3015 Alfranio Correia	2010-04-23
      BUG#53075 SBR: Strange warning around CONNECTION_ID
      
      Statements with CONNECTION_ID were forced to be kept in the transactional
      cache and by consequence non-transactional changes that were supposed to
      be flushed ahead of the transaction were kept in the transactional cache.
      
      This happened because after BUG#51894 any statement whose thd's
      thread_specific_used was set was kept in the transactional cache. The idea
      was to keep changes on temporary tables in the transactional cache. However,
      the thread_specific_used was set not only for statements that accessed
      temporary tables but also when the CONNECTION_ID was used.
      
      To fix the problem, we created a new variable to keep track of updates
      to temporary tables.
     @ sql/log_event.cc
        Uses the thread_temporary_used to decide if a statement should
        be kept in the transactional cache or not.
     @ sql/sql_class.cc
        Sets the thread_temporary_used while calling the decide_logging_format.
     @ sql/sql_class.h
        Defines the thread_temporary_used.
     @ sql/sql_parse.cc
        Resets the thread_temporary_used.
[23 Apr 2010 0:11] Alfranio Tavares Correia Junior
This is a regression of BUG#51894.
[23 Apr 2010 8:58] Bugs System
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/106414

3015 Alfranio Correia	2010-04-23
      BUG#53075 SBR: Strange warning around CONNECTION_ID
      
      Statements with CONNECTION_ID were forced to be kept in the transactional
      cache and by consequence non-transactional changes that were supposed to
      be flushed ahead of the transaction were kept in the transactional cache.
      
      This happened because after BUG#51894 any statement whose thd's
      thread_specific_used was set was kept in the transactional cache. The idea
      was to keep changes on temporary tables in the transactional cache. However,
      the thread_specific_used was set not only for statements that accessed
      temporary tables but also when the CONNECTION_ID was used.
      
      To fix the problem, we created a new variable to keep track of updates
      to temporary tables.
     @ mysql-test/suite/rpl/r/rpl_temp_temporary.result
        Added a test case.
     @ mysql-test/suite/rpl/t/rpl_temp_temporary.test
        Added a test case.
     @ sql/log_event.cc
        Uses the thread_temporary_used to decide if a statement should
        be kept in the transactional cache or not.
     @ sql/sql_class.cc
        Sets the thread_temporary_used while calling the decide_logging_format.
     @ sql/sql_class.h
        Defines the thread_temporary_used.
     @ sql/sql_parse.cc
        Resets the thread_temporary_used.
[26 Apr 2010 9:03] Bugs System
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/106478

3016 Alfranio Correia	2010-04-26
      BUG#53075 SBR: Strange warning around CONNECTION_ID
      
      Statements with CONNECTION_ID were forced to be kept in the transactional
      cache and by consequence non-transactional changes that were supposed to
      be flushed ahead of the transaction were kept in the transactional cache.
      
      This happened because after BUG#51894 any statement whose thd's
      thread_specific_used was set was kept in the transactional cache. The idea
      was to keep changes on temporary tables in the transactional cache. However,
      the thread_specific_used was set not only for statements that accessed
      temporary tables but also when the CONNECTION_ID was used.
      
      To fix the problem, we created a new variable to keep track of updates
      to temporary tables.
     @ mysql-test/suite/rpl/r/rpl_temp_temporary.result
        Added a test case.
     @ mysql-test/suite/rpl/t/rpl_temp_temporary.test
        Added a test case.
     @ sql/log_event.cc
        Uses the thread_temporary_used to decide if a statement should
        be kept in the transactional cache or not.
     @ sql/sql_class.cc
        Sets the thread_temporary_used while calling the decide_logging_format.
     @ sql/sql_class.h
        Defines the thread_temporary_used.
     @ sql/sql_parse.cc
        Resets the thread_temporary_used.
[27 Apr 2010 9:45] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100427094135-5s49ecp3ckson6e2) (version source revid:alik@sun.com-20100427093843-uekr85qkd7orx12t) (merge vers: 6.0.14-alpha) (pib:16)
[27 Apr 2010 9:48] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100427093804-a2k3rrjpwu5jegu8) (version source revid:alik@sun.com-20100427093804-a2k3rrjpwu5jegu8) (merge vers: 5.5.5-m3) (pib:16)
[27 Apr 2010 9:50] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100427094036-38frbg3famdlvjup) (version source revid:alik@sun.com-20100427093825-92wc8b22d4yg34ju) (pib:16)
[28 Apr 2010 13:39] Jon Stephens
Documented bugfix in the 5.5.5 and 6.0.14 changelogs as follows:

      When using statement-based logging, statements that used 
      CONNECTION_ID() were always kept in the transaction cache; 
      consequently, nontransactional changes that should have been 
      flushed before the transaction were kept in the transaction 
      cache.

Closed.
[29 Apr 2010 10:44] Bugs System
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/106919

3026 Alfranio Correia	2010-04-29
      Post-merge fix for BUG#51894, BUG#53075
      
      thread_temporary_used is not initialized causing
      valgrind's warnings.
[7 May 2010 9:21] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100507091908-vqyhpwf2km0aokno) (version source revid:alik@sun.com-20100507091737-12vceffs11elb25g) (merge vers: 6.0.14-alpha) (pib:16)
[7 May 2010 9:22] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100507091655-349gwq21ursz8y4p) (version source revid:alik@sun.com-20100507091655-349gwq21ursz8y4p) (merge vers: 5.5.5-m3) (pib:16)
[7 May 2010 9:23] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100507091823-nzao4h3qosau4tin) (version source revid:alik@sun.com-20100507091720-ib9r8uny2aeazvas) (pib:16)
[8 May 2010 17:03] Paul Dubois
Already fixed in 5.5.x, 6.0.x.