MySQL Bugs: #82919: Let's break the restriction of `create table select` statement.

Bug #82919	Let's break the restriction of `create table select` statement.
Submitted:	9 Sep 2016 5:49	Modified:	14 Sep 2016 11:56
Reporter:	baizhong zhao (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.7.14	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution, GTID

Description:
In 5.7.14,  the CTS statement  is forbidden to executed when  enforce-gtid-consistency=ON.
But it  can be executed if gtid mode off, and the statement generate the following binlog (row format):
```
      Anonymous_gtid_log_event  // stmt_cache
      CREATE TABLE event 
      Anonymous_gtid_log_event  // trx_cache
      BEGIN 
      rows event
      COMMIT
``` 
One CTS statement  generate two anonymous gtid event because it use both stmt_cache, trx_cache
When this binlog send to slave, everything  is ok.
But in 5.6.28,  if enforce-gtid-consistency=OFF , one CTS statement generate the following binlog(row format):
```
      Gtid_log_event  // trx_cache
      BEGIN 
      CREATE TABLE event 
      rows event
      COMMIT
```
The statement  use only the trx_cache, so it generate only one gtid evnet. 
When this binlog sended to slave, the slave sql thread would stop because of "Cannot execute statements with implicit commit inside a transaction when @@SESSION.GTID_NEXT != AUTOMATIC".

This tell us : the DDL statements cause implicit commit, so the CTS statement generate two transaction.  And the behaviour In 5.6.28 is wrong, it can not generate  just one gtid.

So In 5.7.14, one CTS statement  generate two gtid event is right. 
It makes sense that CTS statement generate the following binlog if gtid mode on:
```
      Gtid_log_event1  // stmt_cache
      CREATE TABLE event 
      Gtid_log_event2  // trx_cache
      BEGIN 
      rows event
      COMMIT
``` 

But why still forbidden the CTS statement?
I think it is due to the `thd->owned_gtid` , one statement can not genreate more than one gtid
And i have work on to break this restriction by using the `thd->owned_gtid_set`  to genreate more than one gtid in one statement, and fix some bug existed in 5.7.14 that relative with `thd->owned_gtid_set`, see Bug #82892.

How to repeat:
Omission

Suggested fix:
The suggest fix is my patch, base on commit 1020e95b.

my patch to break the restriction

Attachment: my.patch (application/octet-stream, text), 14.49 KiB.

Hello Baizhong,

Thank you for the report and contribution.
Please be informed that in order to submit contributions you must first sign the Oracle Contribution Agreement (OCA). For additional information please check http://www.oracle.com/technetwork/community/oca-486395.html.
If you have any questions, please contact the MySQL community team - http://www.mysql.com/about/contact/?topic=community.

Thanks,
Umesh

Hello Umesh

I have send my singed OCA.
I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Thanks,
Baizhong

patch base on commit 1020e95b. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: my.patch (application/octet-stream, text), 14.49 KiB.