MySQL Bugs: #33329: extraneous ROLLBACK in binlog on connection does not use trans tables

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

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.

Taking to myself as there is the reported fixing plan (yet to be discussed).

Susanne,
tracing and logging includes the slow log, the general  log, but does not include replication and the binary log.

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));

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).

Pushed to 5.0-rpl.

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.

Pushed into 5.0.60

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

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.

Also noted fix in 5.1.23-ndb-6.3.11 changelog.