MySQL Bugs: #22864: Rollback following CREATE ... SELECT discards 'CREATE table' from log

Bug #22864	Rollback following CREATE ... SELECT discards 'CREATE table' from log
Submitted:	30 Sep 2006 20:01	Modified:	1 Feb 2007 0:57
Reporter:	Andrei Elkin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S3 (Non-critical)
Version:	5.1.12-BK	OS:	Linux (Linux)
Assigned to:	Mats Kindahl	CPU Architecture:	Any
Tags:	RBR, rbr_autocommit, rbr_create_select

Description:
Rollback query immidiately following CREATE .. SELECT in the same connection can not change anything including data or log records due to the CREATE.

However the log misses CREATE whereas new table exists indeed.

Happens with either binlog_format.

How to repeat:
drop table if exists t1,t2; create table t1 (a int);  
reset master; 
set autocommit=0;
create table t2 (a int) engine=innodb  select * from t1; 
rollback; 
show tables;         # t2 exists
show binlog events;  # t2 create has not been logged

Suggested fix:
Perhaps, create ... selected is not honored as implicitly committing.

Thank you for a problem report. Verified just as described. Moreover, according to my test in related bug #22866, CREATE is NOT logged even without ROLLBACK.

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

ChangeSet@1.2309, 2006-10-23 22:06:55+02:00, mats@romeo.(none) +5 -0
  BUG#22864 (Rollback following CREATE... SELECT discards 'CREATE TABLE' from log):
  Adding a rollback point which will be set for the CREATE-SELECT
  statement only. Explicit rollbacks will truncate the transaction
  cache to the rollback point and write the remaining data in the
  transaction cache to the binary log.

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

ChangeSet@1.2309, 2006-11-03 13:51:42+01:00, mats@romeo.(none) +9 -0
  BUG#22864 (Rollback following CREATE... SELECT discards 'CREATE TABLE' from log):
  Adding a rollback point which will be set for the CREATE-SELECT
  statement only. Explicit rollbacks will truncate the transaction
  cache to the rollback point and write the remaining data in the
  transaction cache to the binary log.

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

ChangeSet@1.2309, 2006-11-09 10:35:48+01:00, mats@romeo.(none) +9 -0
  BUG#22864 (Rollback following CREATE... SELECT discards 'CREATE TABLE' from log):
  On failure, table is dropped by issuing a DROP TABLE into the binary
  log.

I had a discussion about this with Andrei, Mats and Rafal and these
are our conclusions and suggested solution.

CURRENT SEMANTICS
-----------------

------------------   ----- ----- ----- -----
Statement              SC    EC   URB   ERB
------------------   ----- ----- ----- -----
CREATE TABLE          Yes   Yes   No    Yes
CREATE SELECT         Yes   No    1/2   Yes
CREATE TMP TABLE      No    No    No    Yes
CREATE TMP SELECT     No    No    No    Yes
------------------   ----- ----- ----- -----

Legend
------
SC = Implicit Start Commit, i.e. the statement commits any earlier
     ongoing transaction.

EC = Implicit End Commit, i.e. the statement commits after being
     executed.

URB = If the user issues a ROLLBACK after the statement, the statement
      is rolled back (1/2 means that only half of the statement is
      rolled back).

ERB = If there is any kind of error while executing the statement, the
      whole statement is rolled back.

PROBLEM
-------
1. SQL standard mentions that transactions should be ACID and assume
   that a transaction start at a unique point.

2. Dmitri is implementing a fix that makes the started transaction
   isolated (from other transactions) from the *start* of the CREATE
   SELECT.

3. Currently, only the select part of the statement is rolled back.
   This conflicts with the SQL Standard that says that the select part
   of a CREATE-SELECT is part of the create part.  This mens that
   atomicity starts from the *middle* of the CREATE SELECT.  This is
   really weird.

SUGGESTED SOLUTION
------------------
Since the above behavior 1) conflicts with the SQL Standard and
2) makes it hard to replicate it correctly, we propose the following
semantics:

------------------   ----- ----- ----- -----
Statement              SC    EC   URB   ERB
------------------   ----- ----- ----- -----
CREATE TABLE          Yes   Yes   No    Yes
CREATE SELECT         Yes   Yes   No    Yes
CREATE TMP TABLE      No    No    No    Yes
CREATE TMP SELECT     No    No    No    Yes
------------------   ----- ----- ----- -----

Benefits:
---------
1. Consistency (CREATE and CREATE-SELECT work in same way)
2. Follows standard (SELECT part of CREATE-SELECT is considered to
   belong to the CREATE part)

Needed to do:
-------------
1. Add extra trans_end for CREATE-SELECT
2. Fix binlogging so that it follows this suggestion (prototype
   already coded by Mats)

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

ChangeSet@1.2309, 2006-12-12 20:50:29+01:00, mats@romeo.(none) +10 -0
  BUG#22864 (Rollback following CREATE... SELECT discards 'CREATE TABLE'
  from log):
  When row-based logging is used, the CREATE-SELECT is written as two
  parts: as a CREATE TABLE statement and as the rows for the table. For
  both transactional and non-transactional tables, the CREATE TABLE
  statement was written to the transaction cache, as were the rows, and
  on statement end, the entire transaction cache was written to the binary
  log if the table was non-transactional. For transactional tables, the
  events were kept in the transaction cache until end of transaction (or
  statement that were not part of a transaction).
  
  For the case when AUTOCOMMIT=0 and we are creating a transactional table
  using a create select, we would then keep the CREATE TABLE statement and
  the rows for the CREATE-SELECT, while executing the following statements.
  On a rollback, the transaction cache would then be cleared, which would
  also remove the CREATE TABLE statement. Hence no table would be created
  on the slave, while there is an empty table on the master.
  
  This relates to BUG#22865 where the table being created exists on the
  master, but not on the slave during insertion of rows into the newly
  created table. This occurs since the CREATE TABLE statement were still
  in the transaction cache until the statement finished executing, and
  possibly longer if the table was transactional.
  
  This patch changes the behaviour of the CREATE-SELECT statement by
  adding an implicit commit at the end of the statement when creating
  non-temporary tables. Hence, non-temporary tables will be written to the
  binary log on completion, and in the even of AUTOCOMMIT=0, a new
  transaction will be started. Temporary tables do not commit an ongoing
  transaction: neither as a pre- not a post-commit.
  
  The events for both transactional and non-transactional tables are
  saved in the transaction cache, and written to the binary log at end
  of the statement.

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

ChangeSet@1.2365, 2006-12-22 12:09:44+01:00, mats@romeo.(none) +1 -0
  BUG#22864 (CREATE-SELECT does not work correctly):
  Post-merge fixes.

Documentation need an addition that there is an implicit commit before and after execution of a CREATE TABLE ... SELECT ..., but that there is none before and after execution of CREATE TEMPORARY TABLE ... SELECT ....

Pushed into 5.1.15

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 bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.1.15 changelog; added note to "Statements That Cause an Implicit Commit".