MySQL Bugs: #22862: Implicit commit of CREATE ... SELECT is not logged

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:	None
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

Description:
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

commit;

-> it comes

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

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

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.

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.