Bug #22862 Implicit commit of CREATE ... SELECT is not logged
Submitted: 30 Sep 2006 19:39 Modified: 2 Oct 2006 8:59
Reporter: Andrei Elkin Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S3 (Non-critical)
Version:5.1.12-BK OS:Linux (linux)
Assigned to: Lars Thalmann CPU Architecture:Any

[30 Sep 2006 19:39] Andrei Elkin
CREATE ... SELECT according to docs commits the current transaction. Consequently there must be COMMIT record in the log.
This does not happen.

Either @@session.binlog_format = row|statement are vulnerable, table types - myisam or innodb - are insensetive.

How to repeat:
drop  table if exists t1,t2; 
reset master; 
create table t1 (a int); 
set autocommit =0; 
insert into t1 values (1); 
create table t2 select * from t1; 
show binlog events;

-> no COMMIT record


-> it comes

Suggested fix:
Perhaps, create ... selected is not honored as implitly committing.
[1 Oct 2006 5:53] Valeriy Kravchuk
Thank you for a bug report. Verified just as described. Bug #22866 and bug #22864 may be duplicates of this one, or, at least, they all will be fixed with the same patch. But let the developers decide...
[2 Oct 2006 8:59] Lars Thalmann
This is not a bug.

That CREATE-SELECT does implicit commit does not mean that there has
to be a COMMIT in the binary log.  As long as the CREATE-SELECT itself
is logged (or equivalent log entries), then there is no problem.
[2 Oct 2006 11:30] Mats Kindahl
In the event of ending a transaction involving a transactional table, there should be a commit before the create. You're example involves a non-transactional table, and then there is no explicit commit in the binary log.

The reason you get a "COMMIT" in the binary log when you write a commit statement is that the statement is logged explicitly.