MySQL Bugs: #6883: ROLLBACK wipes out some DDLs from the binlog (breaks binlog and replication)

Bug #6883	ROLLBACK wipes out some DDLs from the binlog (breaks binlog and replication)
Submitted:	29 Nov 2004 20:57	Modified:	21 Jun 2005 16:50
Reporter:	Guilhem Bichot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0	OS:	Any (all)
Assigned to:	Lars Thalmann	CPU Architecture:	Any

Description:
 reset master;
 set autocommit=0;
 use db1; # or any db
 insert into t1 values (100);
 insert into t1 values (200);
 create database db2;
 use db2;
 create table x1 (id bigint) type=innodb;
 rollback;
 show binlog events;
 
in binlog we see nothing, whereas the CREATEs happened and have not
been rolled back, and should have committed the INSERTs as they are
supposed to be implicit commit statements.

Analysys: it's a big bunch of bugs.
Simply, the code does not obey what the manual says:

The following SQL statements (and any synonyms for them) cause an implicit
commit of the current transaction in MySQL:

  @itemize @bullet
  @item
  @code{ALTER TABLE}, @code{BEGIN}, @code{CREATE INDEX}, @code{DROP DATABASE},
  @code{DROP INDEX}, @code{DROP TABLE}, @code{LOAD MASTER DATA}, @code{LOCK
  TABLES}, @code{RENAME TABLE}, @code{SET AUTOCOMMIT=1}, @code{START
  TRANSACTION}, @code{TRUNCATE}, @code{UNLOCK TABLES}.

In fact DROP DATABASE is refused if there is a started
transaction, whereas it should be accepted and autocommit, according
to the manual above. CREATE DATABASE is not mentioned in the list, it
should logically be in the list, but in fact it does not implicitely
commit (bug).
TRUNCATE has same bug as DROP DATABASE.
I tested all listed others and they are ok (I didn't test LOAD MASTER
DATA but we don't care).
So, to sum up: DROP DATABASE, TRUNCATE TABLE, CREATE DATABASE are
buggy. Most serious is CREATE DATABASE (at least the others give
errors so do not endanger the binlog). Ideally all 3 should be made to
implicitely commit without any error.

Next paragraph of doc:

@item
@code{CREATE TABLE} (this commits only if before MySQL 4.0.13 and MySQL
binary logging is used).

wrong, it does not implicitely commit.

How to repeat:
 reset master;
 set autocommit=0;
 use db1; # or any db
 insert into t1 values (100);
 insert into t1 values (200);
 create database db2;
 use db2;
 create table x1 (id bigint) type=innodb;
 rollback;
 show binlog events;
 

Suggested fix:
see email discussion with Heikki cc: all devs.

please discuss with me before fixing - there are several issues.

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/internals/25301

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/internals/25454

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/internals/25682

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/internals/25712

From MySQL versions 4.1.13, 5.0.8, the following statements cause an implicit commit:
  - Non-temporary CREATE TABLE (needed to fix bug)
  - TRUNCATE TABLE (was not possible to execute at all with "open trx")
  - DROP DATABASE (was not possible to execute at all with "open trx")
  - CREATE DATABASE (just to make it consistent with all other DDL)

Summary for MySQL 4.1 and MySQL 5.0 after made changes:

     MySQL 4.1 and 5.0                  Implicit
     DDL                                commit
     ------------------------------------------------------
     COMMIT                             commit
     ROLLBACK                           no commit

     SET AUTOCOMMIT=1                   commit
     START TRANSACTION                  commit
     BEGIN                              commit
     DROP TABLE d1.t2                   commit
     RENAME TABLE d1.t3 to d1.t20       commit
     ALTER TABLE d1.t4 ADD ...          commit
     CREATE TABLE d1.t21  ...           commit (changed code)
     TRUNCATE TABLE d1.t7               commit (changed code)
     LOCK TABLES d1.t1                  commit
     DROP INDEX my_idx6 ON d1.t6        commit
     CREATE INDEX my_idx5 ON ...        commit
     DROP DATABASE d2                   commit (changed code)
     CREATE DATABASE                    commit (changed code)
     UNLOCK TABLES (with write lock)    commit (was not documented)

     UNLOCK TABLES (with no write lock) no commit
     CREATE TEMPORARY TABLE             no commit
     DROP TEMPORARY TABLE               no commit

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 bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented in change history for 4.1.13 and 5.0.8 releases; marked Closed.