MySQL Bugs: #33055: Replication fails for UPDATE when using falcon Storage engine

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

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

test case for the bug

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

Expected result file for the bug

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

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 |
+----+------+------+

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.

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.

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.

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.

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.

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.

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.

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.

Patch queued to mysql-6.0-release clone.

This is pushed into 6.0 release tree (6.0.4) 
and Joerg will merge this tree to main 6.0.

Pushed into 6.0.4-alpha

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.

Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101008132832-pbzewvmi9f365ak4) (version source revid:alexander.nozdrin@oracle.com-20101008132832-pbzewvmi9f365ak4) (pib:21)

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)

Bug is not present in any released 5.6.x version.