MySQL Bugs: #22861: wrong logging 'create table' if CREATE ... SELECT fails with autocommit=0

Bug #22861	wrong logging 'create table' if CREATE ... SELECT fails with autocommit=0
Submitted:	30 Sep 2006 18:29	Modified:	2 Oct 2006 8:52
Reporter:	Andrei Elkin	Email Updates:
Status:	Duplicate	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:	Assigned Account	CPU Architecture:	Any

Description:
if the statement
CREATE TABLE t2 (primary key (a)) engine=innodb select * from t1;
fails and the new table is not created there must be not log entry.
But it happens.

Note,
 tables types are insensitive, 
set @@session.binlog_format=statement does not get to this actifact.

How to repeat:
set @@session.binlog_format=row;
create table t1 (f int);
set autocommit=0;
INSERT INTO t1 values (1),(1);
CREATE TABLE t2 (f unique int) select * from t1; # failure
show tables; # no table t2;
commit;
show binlog status;
BEGIN |
master | 271 | Query | 1 | 154 | use `test`; CREATE TABLE `t2` (
`a` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`f`)

Suggested fix:
Avoid "create table" logging upon handler thread detects CREATE ... SELECT problem.

Thank you for a problem report. I really was able to get failed CREATE TABLE logged once, but when I tried to repeat once again, step by step, I've got a different (but still buggy!) behaviour:

mysql> reset master;
Query OK, 0 rows affected (0.02 sec)

mysql> show binlog events;
+-----------------+-----+-------------+-----------+-------------+---------------
-----------------------------------+
| Log_name        | Pos | Event_type  | Server_id | End_log_pos | Info
                                   |
+-----------------+-----+-------------+-----------+-------------+---------------
-----------------------------------+
| suse-bin.000001 |   4 | Format_desc |         1 |         102 | Server ver: 5.
1.12-beta-debug-log, Binlog ver: 4 |
+-----------------+-----+-------------+-----------+-------------+---------------
-----------------------------------+
1 row in set (0.00 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> show binlog events;
+-----------------+-----+-------------+-----------+-------------+---------------
-----------------------------------+
| Log_name        | Pos | Event_type  | Server_id | End_log_pos | Info
                                   |
+-----------------+-----+-------------+-----------+-------------+---------------
-----------------------------------+
| suse-bin.000001 |   4 | Format_desc |         1 |         102 | Server ver: 5.
1.12-beta-debug-log, Binlog ver: 4 |
| suse-bin.000001 | 102 | Query       |         1 |         178 | use `test`; dr
op table t1                        |
+-----------------+-----+-------------+-----------+-------------+---------------
-----------------------------------+
2 rows in set (0.00 sec)

mysql> set @@session.binlog_format=row;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (f int);
Query OK, 0 rows affected (0.02 sec)

mysql> show binlog events;
+-----------------+-----+-------------+-----------+-------------+---------------
-----------------------------------+
| Log_name        | Pos | Event_type  | Server_id | End_log_pos | Info
                                   |
+-----------------+-----+-------------+-----------+-------------+---------------
-----------------------------------+
| suse-bin.000001 |   4 | Format_desc |         1 |         102 | Server ver: 5.
1.12-beta-debug-log, Binlog ver: 4 |
| suse-bin.000001 | 102 | Query       |         1 |         178 | use `test`; dr
op table t1                        |
| suse-bin.000001 | 178 | Query       |         1 |         264 | use `test`; cr
eate table t1 (f int)              |
+-----------------+-----+-------------+-----------+-------------+---------------
-----------------------------------+
3 rows in set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 values (1),(1);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> show binlog events;
+-----------------+-----+-------------+-----------+-------------+---------------
-----------------------------------+
| Log_name        | Pos | Event_type  | Server_id | End_log_pos | Info
                                   |
+-----------------+-----+-------------+-----------+-------------+---------------
-----------------------------------+
| suse-bin.000001 |   4 | Format_desc |         1 |         102 | Server ver: 5.
1.12-beta-debug-log, Binlog ver: 4 |
| suse-bin.000001 | 102 | Query       |         1 |         178 | use `test`; dr
op table t1                        |
| suse-bin.000001 | 178 | Query       |         1 |         264 | use `test`; cr
eate table t1 (f int)              |
| suse-bin.000001 | 264 | Table_map   |         1 |         303 | table_id: 39 (
test.t1)                           |
| suse-bin.000001 | 303 | Write_rows  |         1 |         342 | table_id: 39 f
lags: STMT_END_F                   |
+-----------------+-----+-------------+-----------+-------------+---------------
-----------------------------------+
5 rows in set (0.01 sec)

mysql> CREATE TABLE t2 (f unique int) select * from t1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'uniqu
e int) select * from t1' at line 1
mysql> show binlog events;
+-----------------+-----+-------------+-----------+-------------+---------------
-----------------------------------+
| Log_name        | Pos | Event_type  | Server_id | End_log_pos | Info
                                   |
+-----------------+-----+-------------+-----------+-------------+---------------
-----------------------------------+
| suse-bin.000001 |   4 | Format_desc |         1 |         102 | Server ver: 5.
1.12-beta-debug-log, Binlog ver: 4 |
| suse-bin.000001 | 102 | Query       |         1 |         178 | use `test`; dr
op table t1                        |
| suse-bin.000001 | 178 | Query       |         1 |         264 | use `test`; cr
eate table t1 (f int)              |
| suse-bin.000001 | 264 | Table_map   |         1 |         303 | table_id: 39 (
test.t1)                           |
| suse-bin.000001 | 303 | Write_rows  |         1 |         342 | table_id: 39 f
lags: STMT_END_F                   |
+-----------------+-----+-------------+-----------+-------------+---------------
-----------------------------------+
5 rows in set (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> show binlog events;
+-----------------+-----+-------------+-----------+-------------+---------------
-----------------------------------+
| Log_name        | Pos | Event_type  | Server_id | End_log_pos | Info
                                   |
+-----------------+-----+-------------+-----------+-------------+---------------
-----------------------------------+
| suse-bin.000001 |   4 | Format_desc |         1 |         102 | Server ver: 5.
1.12-beta-debug-log, Binlog ver: 4 |
| suse-bin.000001 | 102 | Query       |         1 |         178 | use `test`; dr
op table t1                        |
| suse-bin.000001 | 178 | Query       |         1 |         264 | use `test`; cr
eate table t1 (f int)              |
| suse-bin.000001 | 264 | Table_map   |         1 |         303 | table_id: 39 (
test.t1)                           |
| suse-bin.000001 | 303 | Write_rows  |         1 |         342 | table_id: 39 f
lags: STMT_END_F                   |
+-----------------+-----+-------------+-----------+-------------+---------------
-----------------------------------+
5 rows in set (0.01 sec)

mysql> select * from t1;
+------+
| f    |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.00 sec)

So, we do not have INSERT (committed one!) recordered in the binary log. We surely have a bug here.

Duplicate of BUG#20265 (the patch for that is supposed to fix this too)