Bug #33329 extraneous ROLLBACK in binlog on connection does not use trans tables
Submitted: 18 Dec 2007 9:47 Modified: 26 Mar 2008 19:05
Reporter: Andrei Elkin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.0 OS:Any
Assigned to: Andrei Elkin CPU Architecture:Any
Triage: D2 (Serious) / R3 (Medium) / E3 (Medium)

[18 Dec 2007 9:47] Andrei Elkin
Description:
If configuration options exclude transaction support in the server then running a query on a non-transactional table never causes ROLLBACK appearance in binlog.
If there is the transaction support compiled-in, then even though a connection that has not dealt with a transaction table its partly performed update semantics dml query is accompanied in binlog with ROLLBACK.
The issue affects some tests like multi_update where a log position gets an offset due to unexpectable appearance of yet another query.

How to repeat:
build the server without transaction support like this:

CC="gcc -g" CXX="gcc -g"  ./configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data --libexecdir=/usr/local/mysql/bin --with-comment="MySQL Classic Server (Commercial)" --with-server-suffix="-classic" --enable-thread-safe-client --enable-local-infile --enable-assembler --with-pic --with-fast-mutexes --with-client-ldflags="-static" --with-mysqld-ldflags="-static" --with-zlib-dir=bundled --with-big-tables --with-yassl --with-libedit --with-archive-storage-engine --with-blackhole-storage-engine --with-csv-storage-engine --with-example-storage-engine --without-falcon --with-federated-storage-engine --without-innodb --with-extra-charsets=complex

run

./mysql-test-run multi_update

to see

--- /tmp/andrei/mysql-5.0/mysql-test/r/multi_update.result      2007-10-13 15:49:37.000000000 +0300
+++ /tmp/andrei/mysql-5.0/mysql-test/r/multi_update.reject      2007-12-18 11:31:08.000000000 +0300
@@ -545,7 +545,7 @@
 4      4
 show master status /* there must be the UPDATE query event */;
 File   Position        Binlog_Do_DB    Binlog_Ignore_DB
-master-bin.000001      260
+master-bin.000001      189
 delete from t1;
 delete from t2;
 insert into t1 values (1,2),(3,4),(4,4);

  etc

Suggested fix:
bool MYSQL_LOG::write(Log_event *event_info) should not invoke
binlog handlerton registration
 trans_register_ha(thd...)
until there has been a transaction table modified.
In the current code the condition to register the binlog handlerton and start using the binlog cache immediately is like this:

 if (opt_using_transactions && thd)

where opt_using_transactions is the global server option.
[25 Dec 2007 13:16] Andrei Elkin
Taking to myself as there is the reported fixing plan (yet to be discussed).
[8 Jan 2008 17:06] Konstantin Osipov
Susanne,
tracing and logging includes the slow log, the general  log, but does not include replication and the binary log.
[14 Jan 2008 13:28] Joerg Bruehe
Reproduced in the release builds of 5.0.54 and 5.0.54a,
full symptom is like this:

multi_update                   [ fail ]

--- /PATH/mysql-test/r/multi_update.result
+++ /PATH/mysql-test/r/multi_update.reject
@@ -545,7 +545,7 @@
 4      4
 show master status /* there must be the UPDATE query event */;
 File   Position        Binlog_Do_DB    Binlog_Ignore_DB
-master-bin.000001      260
+master-bin.000001      189
 delete from t1;
 delete from t2;
 insert into t1 values (1,2),(3,4),(4,4);
@@ -555,7 +555,7 @@
 ERROR 23000: Duplicate entry '4' for key 1
 show master status /* there must be the UPDATE query event */;
 File   Position        Binlog_Do_DB    Binlog_Ignore_DB
-master-bin.000001      275
+master-bin.000001      204
 drop table t1, t2;
 drop table if exists t1, t2, t3;
 CREATE TABLE t1 (a int, PRIMARY KEY (a));
[4 Feb 2008 14:38] 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/41651

ChangeSet@1.2568, 2008-02-04 16:37:41+02:00, aelkin@koti.dsl.inet.fi +2 -0
  Bug#33329  extraneous ROLLBACK in binlog on connection 
              does not use trans tables
  
  There had been two issues.
  Rollback statement was recorded in binlog even though a multi-update
  had not modified any non-transactional table.
  The reason for this artifact was a false initial value of multi_update::transactional_tables.
  Yet another artifact that explained on the bug page is that 
  `ha_autocommit_or_rollback' works differently depending on whether
  a transaction engine has been compiled in. 
  
  Fixed: with setting multi_update::transactional_tables to zero at initialization
  time. Multi-update on non-trans table won't cause ROLLBACK in binlog with
  either compilation option.
  
  The 2nd mentioned artifact comprises a self-standing issue (to be reported
  separately).
[12 Feb 2008 10:42] Andrei Elkin
Pushed to 5.0-rpl.
[14 Feb 2008 14:28] 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/42280

ChangeSet@1.2580, 2008-02-14 16:28:32+02:00, aelkin@mysql1000.dsl.inet.fi +2 -0
  bug#33329 extraneous ROLLBACK in binlog on connection does not use trans tables
  
  changes for an assert and an updated results file.
[25 Mar 2008 11:22] Bugs System
Pushed into 5.0.60
[25 Mar 2008 11:23] Bugs System
Pushed into 5.1.24-rc
[26 Mar 2008 19:01] Bugs System
Pushed into 6.0.5-alpha
[26 Mar 2008 19:05] Jon Stephens
Documented bugfix in the 5.0.60 and 5.1.24 changelogs as follows:

        An extraneous ROLLBACK statement was written to the
        binary log by a connection that did not use any transactional tables.
[2 Apr 2008 15:58] Jon Stephens
Also noted fix in 5.1.23-ndb-6.3.11 changelog.