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)