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

[30 Sep 2006 18:29] Andrei Elkin
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.
[1 Oct 2006 6:08] Valeriy Kravchuk
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.
[2 Oct 2006 8:52] Lars Thalmann
Duplicate of BUG#20265 (the patch for that is supposed to fix this too)

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.052 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.