Bug #13576 RBR master writes nothing to binlog if deleting all rows of a MyISAM table
Submitted: 28 Sep 2005 16:36 Modified: 29 Sep 2005 8:20
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.0-wl1012 OS:Linux (linux)
Assigned to: Guilhem Bichot CPU Architecture:Any

[28 Sep 2005 16:36] Guilhem Bichot
Description:
Start master with --binlog-format=row and do:
mysql> insert into t values(1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

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

mysql> delete from t where a=1;
Query OK, 1 row affected (0.00 sec)

mysql> show binlog events;
+---------------------+-----+-------------+-----------+-------------+-------------------------------------------------------
------+
| Log_name            | Pos | Event_type  | Server_id | End_log_pos | Info                                                        |
+---------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+
| gbichot3-bin.000001 |   4 | Format_desc |         1 |         102 | Server ver: 5.0.14-rc-valgrind-max-debug-log, Binlog ver: 4 |
| gbichot3-bin.000001 | 102 | Table_map   |         1 |         138 | TID 0: `test`.`t`                                           |
| gbichot3-bin.000001 | 138 | Write_rows  |         1 |         175 | TID 0 Flags: TRANS_END_F                                    |
| gbichot3-bin.000001 | 175 | Table_map   |         1 |         211 | TID 0: `test`.`t`                                           |
| gbichot3-bin.000001 | 211 | Delete_rows |         1 |         243 | TID 0 Flags: TRANS_END_F                                    |
+---------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+
5 rows in set (0.00 sec)

mysql> delete from t;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
Empty set (0.00 sec)

mysql> show binlog events;
+---------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+
| Log_name            | Pos | Event_type  | Server_id | End_log_pos | Info                                                        |
+---------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+
| gbichot3-bin.000001 |   4 | Format_desc |         1 |         102 | Server ver: 5.0.14-rc-valgrind-max-debug-log, Binlog ver: 4 |
| gbichot3-bin.000001 | 102 | Table_map   |         1 |         138 | TID 0: `test`.`t`                                           |
| gbichot3-bin.000001 | 138 | Write_rows  |         1 |         175 | TID 0 Flags: TRANS_END_F
      |
| gbichot3-bin.000001 | 175 | Table_map   |         1 |         211 | TID 0: `test`.`t`                                           |
| gbichot3-bin.000001 | 211 | Delete_rows |         1 |         243 | TID 0 Flags: TRANS_END_F                                    |
+---------------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+
5 rows in set (0.00 sec)
See: nothing was binlogged for the "delete from t" whereas it did delete a row ("2").

How to repeat:
see description
[29 Sep 2005 7:50] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30489
[29 Sep 2005 8:20] Guilhem Bichot
fixed