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