Bug #33055 Replication fails for UPDATE when using falcon Storage engine
Submitted: 7 Dec 2007 2:47 Modified: 11 Jan 2011 20:11
Reporter: Omer Barnir (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Falcon storage engine Severity:S2 (Serious)
Version:6.0.4 OS:Any
Assigned to: Mats Kindahl CPU Architecture:Any

[7 Dec 2007 2:47] Omer Barnir
Description:
When updating fields for a row when using a falcon table, the values for the fields that were not updated are replicated as NULL.

For example:

CREATE DATABASE tpcb;
CREATE TABLE tpcb.account (id INT, bid INT, balance DECIMAL(10,2), 
             filler CHAR(255), PRIMARY KEY(id))ENGINE=falcon;

USE tpcb;

INSERT INTO tpcb.account VALUES (1, 2, 1.2, "FRESH ACCOUNT");
INSERT INTO tpcb.account VALUES (2, 5, 1.2, "FRESH ACCOUNT");
INSERT INTO tpcb.account VALUES (3, 2, 1.2, "FRESH ACCOUNT");
INSERT INTO tpcb.account VALUES (4, 2, 1.2, "FRESH ACCOUNT");

UPDATE tpcb.account SET balance = 3.5 WHERE id = 3;
UPDATE tpcb.account SET filler = 'Updated' WHERE id = 2;
UPDATE tpcb.account SET balance = 9.2, bid = 21, 
                        filler = 'Updated' WHERE id = 4;

selecting from the master returns:
id	bid	balance	filler
1	2	1.20	FRESH ACCOUNT
2	5	1.20	Updated
3	2	3.50	FRESH ACCOUNT
4	21	9.20	Updated

While selecting from the slave returns:
id	bid	balance	filler
1	2	1.20	FRESH ACCOUNT
2	NULL	NULL	Updated
3	NULL	3.50	NULL
4	21	9.20	Updated

Note: all the values in the updated rows that were not updated are NULL

How to repeat:
copy the attached test and result file to the mysql-test/r directory

run mysql-test-run.pl --do-test=rpl_null_bug

Note the attached result file holds the EXPECTED values, not the ones returned by the test

Suggested fix:
Replication should be identical
[7 Dec 2007 2:49] Omer Barnir
test case for the bug

Attachment: rpl_null_bug.test (application/octet-stream, text), 927 bytes.

[7 Dec 2007 2:49] Omer Barnir
Expected result file for the bug

Attachment: rpl_null_bug.result (application/octet-stream, text), 1.21 KiB.

[7 Dec 2007 15:57] Christopher Powers
Updates fail because untouched fields are designated as null. Attached are master and slave trace files with a very simple example (Reverse search for table->record[0] from the bottom of the file.)

Here's a simpler recreation scenario:

CREATE TABLE t2 (s1 tinyint primary key, s2 tinyint, s3 tinyint) engine=falcon;
INSERT INTO t2 values (3, 3, 3);
UPDATE t2 SET s2=66 WHERE s1 = 3;

All the updated fields are replicated, and the the record is found on the slave, but Falcon sees field s3 marked as NULL:

mysql> select * from t2;
+----+------+------+
| s1 | s2   | s3   |
+----+------+------+
|  3 |   66 | NULL |
+----+------+------+
[7 Dec 2007 16:01] Christopher Powers
Consulted with Mats Kindahl. His reply:

I'm pretty sure this is a bug in replication, so you can just re-assign it to me. If it turns out that it is not what I think that it is, I'll reassign it again.

The ability to send subsets of columns over the wire is implemented in the replication code and format, but since the read/write-sets were introduced after row-based replication had implemented this ability, it has probably not been entirely wired into place. Since we've been fixing 5.1 GA bugs and features for Telco at highest priority, we have not had the resources available to ensure that this is working for anybody but NDB.

Previously, a query_id was used to mark what columns that should be changed, and since this is no longer used, there are some engines (MyISAM and InnoDB, AIUI), using this method and some engines (like NDB) using the read/write-sets.

It is not very difficult to fix, it's just a matter of priorities.
[8 Dec 2007 7:47] Christopher Powers
I tested a workaround per Mats suggestion, i.e. populate all fields during an update, including unchanged columns. This resolves the replication problem, but will impact performance. Kelly Long is running performance tests against the patched code. I will post the numbers as they become available.
[8 Dec 2007 23:18] Christopher Powers
Populating unnchanged fields during update operations results in a 14% performance decrease in the DBT2 benchmark.

Normally, this would only occur if binlogging is enabled. For the DBT2 test, the patch was modified to populate unchanged fields regardless of the binlog state.
[18 Dec 2007 17:27] 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/commits/40151

ChangeSet@1.2759, 2007-12-18 18:26:54+01:00, mats@kindahl-laptop.dnsalias.net +10 -0
  BUG#33055 (Replication fails for UPDATE when using falcon Storage engine):
  
  The read- and write-sets were not respected when deciding what fields to
  transfer to the slave, nor when writing the row to the handler on the sla-
  ve side.
  
  This patch adds code that only writes the columns actually needed to be 
  able to replicate row-based. This means that the columns in the write set
  of the table is used for ha_write_row(), the columns in the read set for
  ha_delete_row(), and for ha_update_row() the columns in the read set is
  used for the before image, and the columns of the write set for the after
  image.
  
  In addition, for cursor-based delete and update, the necessary bits are
  set to be able to get enough information to locate the affected row on
  the slave side, which means that it behaves as if HA_PRIMARY_KEY_REQUIRED_
  FOR_DELETE was set for the table when row-based logging is active.
[19 Dec 2007 8:27] 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/commits/40178

ChangeSet@1.2760, 2007-12-19 09:27:29+01:00, mats@kindahl-laptop.dnsalias.net +3 -0
  BUG#33055 (Replication fails for UPDATE when using falcon Storage engine):
  
  Supplementory patch to allow the injector interface to work with the
  new row-based implementation that honor the read- and write-sets of
  the tables being written.
  
  With this patch, NDB can also replicate correctly with the new code.
[19 Dec 2007 11:25] 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/commits/40193

ChangeSet@1.2761, 2007-12-19 12:25:09+01:00, mats@kindahl-laptop.dnsalias.net +2 -0
  BUG#33055 ():
  
  Minor changes done for failing tests.
[19 Dec 2007 15:03] 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/commits/40211

ChangeSet@1.2763, 2007-12-19 16:02:45+01:00, mats@kindahl-laptop.dnsalias.net +5 -0
  BUG#33055 (Replication fails for UPDATE when using falcon Storage engine):
  
  Patch to fix some pushbuild failures.
[23 Dec 2007 18:27] 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/commits/40385

ChangeSet@1.2776, 2007-12-23 19:26:48+01:00, mkindahl@dl145h.mysql.com +1 -0
  BUG#33055 ():
  
  Temporary fix to make tests pass. Will fix it good later.
[28 Jan 2008 14:21] Mats Kindahl
Patch queued to mysql-6.0-release clone.
[28 Jan 2008 14:22] Lars Thalmann
This is pushed into 6.0 release tree (6.0.4) 
and Joerg will merge this tree to main 6.0.
[12 Mar 2008 23:03] Bugs System
Pushed into 6.0.4-alpha
[2 May 2008 1:16] Paul Dubois
Noted in 6.0.4 changelog.

When updating columns for a row when using a Falcon table, the values
for columns that were not updated were replicated as NULL.
[8 Oct 2010 13:29] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101008132832-pbzewvmi9f365ak4) (version source revid:alexander.nozdrin@oracle.com-20101008132832-pbzewvmi9f365ak4) (pib:21)
[13 Nov 2010 16:20] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[11 Jan 2011 20:11] Paul Dubois
Bug is not present in any released 5.6.x version.