Bug #20265 Replication of CREATE-SELECT does not work correctly
Submitted: 5 Jun 2006 7:05 Modified: 18 Oct 2006 10:14
Reporter: Mats Kindahl Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S2 (Serious)
Version:5.1 OS:Any (ALL)
Assigned to: Mats Kindahl CPU Architecture:Any

[5 Jun 2006 7:05] Mats Kindahl
Description:
CREATE-SELECT does not work properly when it fails due to a error or when the transaction is rolled back.

How to repeat:
SET AUTOCOMMIT=0;
CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=INNODB;
INSERT INTO t1 VALUES (1,2), (2,2), (3,4), (4,4);
CREATE TABLE t2(UNIQUE(b)) SELECT * FROM t1;
ROLLBACK;
[18 Aug 2006 19:07] 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/10635

ChangeSet@1.2249, 2006-08-18 21:07:32+02:00, mats@romeo.(none) +4 -0
  BUG#20265 (Replication of CREATE-SELECT does not work correctly):
  Fixing bug by making binary log handle statement transactions.
  The binary log transaction cache can now be truncated to remove 
  events inserted during this statement or transaction. Also, the
  binary log participate in XA transaction handling, although not
  as a full 2pc resource.
[2 Oct 2006 8:53] Lars Thalmann
See also duplicate BUG#22861.
[2 Oct 2006 9:16] Lars Thalmann
See also BUG#22864
[2 Oct 2006 21:27] Lars Thalmann
To add to documentation, if not already in there:

DEFINITIONS
- Internal rollback = done automatically if statement fails
- External rollback = done by ROLLBACK statement

SEMANTICS
- CREATE TABLE
  - Does implicit commit
  - Internally rollbackable
  - Not externally rollbackable
- CREATE TMP TABLE
  - Does not do implicit commit
  - Internally rollbackable
  - Not externally rollbackable
[4 Oct 2006 16:12] Lars Thalmann
This is the full test case for this bug:

+ SET BINLOG_FORMAT=ROW;
+ DROP TABLE IF EXISTS t1;
+ DROP TABLE IF EXISTS t2;
+ SET AUTOCOMMIT=0;
+ CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=INNODB;
+ INSERT INTO t1 VALUES (1,2), (2,2), (3,4), (4,4);
+ CREATE TABLE t2(UNIQUE(b)) SELECT * FROM t1;
+ ERROR 23000: Duplicate entry '2' for key 'b'
+ COMMIT;
+ SHOW BINLOG EVENTS;
+ Log_name      Pos     Event_type      Server_id       End_log_pos     Info
+ master-bin.000001     4       Format_desc     1       102     Server ver: 5.1.12-beta-debug-log, Binlog ver: 4
+ master-bin.000001     102     Query   1       188     use `test`; DROP TABLE IF EXISTS t1
+ master-bin.000001     188     Query   1       274     use `test`; DROP TABLE IF EXISTS t2
+ master-bin.000001     274     Query   1       393     use `test`; CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=INNODB
+ master-bin.000001     393     Query   1       461     use `test`; BEGIN
+ master-bin.000001     461     Table_map       1       40      table_id: 25 (test.t1)
+ master-bin.000001     501     Write_rows      1       105     table_id: 25 flags: STMT_END_F
+ master-bin.000001     566     Xid     1       593     COMMIT /* xid=60 */
+ master-bin.000001     593     Query   1       661     use `test`; BEGIN
+ master-bin.000001     661     Query   1       159     use `test`; CREATE TABLE `t2` (
+   `a` int(11) NOT NULL,
+   `b` int(11) DEFAULT NULL,
+   UNIQUE KEY `b` (`b`)
+ )
+ master-bin.000001     820     Table_map       1       199     table_id: 26 (test.t2)
+ master-bin.000001     860     Write_rows      1       237     table_id: 26 flags: STMT_END_F
+ master-bin.000001     898     Xid     1       925     COMMIT /* xid=61 */

Here one can see that the CREATE-SELECT for t2 is incorrectly logged
with data to insert.  Since the statement failed with duplicate key
error it should not have been logged at all in RBR (in SBR we can log
it with the error code).
[5 Oct 2006 8:46] 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/13102

ChangeSet@1.2248, 2006-10-05 10:46:14+02:00, mats@romeo.(none) +4 -0
  I had forgotten to delete an already disabled line of C++ code.
  ---
  BUG#20265 (Replication of CREATE-SELECT does not work correctly):
  Fixing bug by making binary log handle statement transactions.
  The binary log transaction cache can now be truncated to remove 
  events inserted during this statement or transaction. Also, the
  binary log participate in XA transaction handling, although not
  as a full 2pc resource.
[11 Oct 2006 10:48] Lars Thalmann
Pushed into 5.1.12
[18 Oct 2006 10:14] MC Brown
A note has been added to the 5.1.12 changelog.