Bug #11087 mysq> source ./file failes to repopulate slave table correctly
Submitted: 3 Jun 2005 22:18 Modified: 25 Jul 2005 19:03
Reporter: Jonathan Miller Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.1 OS:Linux (Linux)
Assigned to: Stewart Smith CPU Architecture:Any

[3 Jun 2005 22:18] Jonathan Miller
Description:
You have a table on the master cluster that is being replicated. 

You delete a row from that table and the slave is updates. 

if you use mysql> source .file to delete from and repopulate the table on the master with all the rows including the deleted one, the table on the master will have all the rows back, but the slave still is missing the row that had been deleted earlier.

How to repeat:
1) setup a master cluster and a slave cluster.

2) create a database > "create database gotoslave;"

3) switch to that database > "use gotoslave;"

4) create a table called matable > 
"Create Table: CREATE TABLE `matable` (
  `nid` int(11) NOT NULL default '0',
  `nom` char(4) default NULL,
  `prenom` char(4) default NULL,
  PRIMARY KEY  (`nid`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1"

5) master >  source ./file2;

6) master > select * from matable order by nid;
+-----+------+--------+
| nid | nom  | prenom |
+-----+------+--------+
|   1 | XYZ1 | ABC1   |
|   2 | XYZ2 | ABC2   |
|   3 | XYZ3 | ABC3   |
|   4 | XYZ4 | ABC4   |
|   5 | XYZ5 | ABC5   |
|   6 | XYZ6 | ABC6   |
|   7 | XYZ7 | ABC7   |
|   8 | XYZ8 | ABC8   |
|   9 | XYZ9 | ABC9   |
+-----+------+--------+

7) slave> select * from matable order by nid;
+-----+------+--------+
| nid | nom  | prenom |
+-----+------+--------+
|   1 | XYZ1 | ABC1   |
|   2 | XYZ2 | ABC2   |
|   3 | XYZ3 | ABC3   |
|   4 | XYZ4 | ABC4   |
|   5 | XYZ5 | ABC5   |
|   6 | XYZ6 | ABC6   |
|   7 | XYZ7 | ABC7   |
|   8 | XYZ8 | ABC8   |
|   9 | XYZ9 | ABC9   |
+-----+------+--------+
8) master>  delete from matable where nid=3;
Query OK, 1 row affected (0.00 sec)

9) master>  select * from matable order by nid;
+-----+------+--------+
| nid | nom  | prenom |
+-----+------+--------+
|   1 | XYZ1 | ABC1   |
|   2 | XYZ2 | ABC2   |
|   4 | XYZ4 | ABC4   |
|   5 | XYZ5 | ABC5   |
|   6 | XYZ6 | ABC6   |
|   7 | XYZ7 | ABC7   |
|   8 | XYZ8 | ABC8   |
|   9 | XYZ9 | ABC9   |
+-----+------+--------+
8 rows in set (0.04 sec)

10) slave> select * from matable order by nid;
+-----+------+--------+
| nid | nom  | prenom |
+-----+------+--------+
|   1 | XYZ1 | ABC1   |
|   2 | XYZ2 | ABC2   |
|   4 | XYZ4 | ABC4   |
|   5 | XYZ5 | ABC5   |
|   6 | XYZ6 | ABC6   |
|   7 | XYZ7 | ABC7   |
|   8 | XYZ8 | ABC8   |
|   9 | XYZ9 | ABC9   |
+-----+------+--------+
8 rows in set (0.04 sec)

11) master> source ./file1;
Query OK, 8 rows affected (0.04 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

12) master>  select * from matable order by nid;
+-----+------+--------+
| nid | nom  | prenom |
+-----+------+--------+
|   1 | XYZ1 | ABC1   |
|   2 | XYZ2 | ABC2   |
|   3 | XYZ3 | ABC3   |
|   4 | XYZ4 | ABC4   |
|   5 | XYZ5 | ABC5   |
|   6 | XYZ6 | ABC6   |
|   7 | XYZ7 | ABC7   |
|   8 | XYZ8 | ABC8   |
|   9 | XYZ9 | ABC9   |
+-----+------+--------+
9 rows in set (0.04 sec)

13) slave> select * from matable order by nid;
+-----+------+--------+
| nid | nom  | prenom |
+-----+------+--------+
|   1 | XYZ1 | ABC1   |
|   2 | XYZ2 | ABC2   |
|   4 | XYZ4 | ABC4   |
|   5 | XYZ5 | ABC5   |
|   6 | XYZ6 | ABC6   |
|   7 | XYZ7 | ABC7   |
|   8 | XYZ8 | ABC8   |
|   9 | XYZ9 | ABC9   |
+-----+------+--------+
8 rows in set (0.04 sec)

NOTE: row 3 is not present.

Suggested fix:
Make sure the addition of row 3 gets replicated.
[3 Jun 2005 22:46] Jonathan Miller
File1

Attachment: file1 (application/octet-stream, text), 430 bytes.

[3 Jun 2005 22:46] Jonathan Miller
File2

Attachment: file2 (application/octet-stream, text), 409 bytes.

[6 Jun 2005 7:23] Stewart Smith
Have managed to duplicate.

It seems that the Write_rows event is being written *before* the Delete_rows event (instead of the other way around)
[6 Jun 2005 8:38] Stewart Smith
The master is recording the binlog correctly (delete, followed by the write).

The slave, however, isn't getting this.
[7 Jun 2005 13:53] Stewart Smith
Bug #11133  	WriteTuple returns 626, if previous op in trans is a delete

We found that we were getting error 626 from NDB and, in fact, it was an NDB bug (common to 4.1, 5.0 and 5.1).

Jonas is investigating and currently testing a patch. We will then test it for this specific scenario.
[8 Jun 2005 13:12] Jonathan Miller
MySQL Bugs: #11133: WriteTuple returns 626, if previous op in trans is a delete