| 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: | |
| 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
[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.
