Bug #39648 Replication failure on RBR + Innodb + 2 bit fields + LIMIT + no PK

• Submitted: 25 Sep 2008 10:34
• Modified: 19 Jun 2010 0:24
• Reporter: Philip Stoev
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 5.1, 6.0 bzr
• OS: Any
• Assigned to: Marko Mäkelä
• CPU Architecture: Any

[25 Sep 2008 10:34] Philip Stoev

Description:
When replicating a innodb table containing two bit columns but no PK, using RBR, a query containing LIMIT will cause the replication to fail with:

Could not execute Delete_rows event on table test.table0_innodb; handler error HA_ERR_END_OF_FILE

How to repeat:
--source include/master-slave.inc
--incude include/have_innodb.inc

CREATE TABLE t1 (`bit` bit, `bit2` bit) ENGINE=innodb;
INSERT INTO t1 ( `bit` ) VALUES ( 0 );
DELETE FROM t1 WHERE `bit` < 2 LIMIT 1;

--save_master_pos
--connection slave
--sync_with_master

SHOW SLAVE STATUS;

[25 Sep 2008 16:41] Sveta Smirnova

Thank you for the report.

I can not repeat error with 5.1 and with 6.0 I get:

Last_Error      Could not execute Delete_rows event on table test.t1; Unknown table engine 'innodb', Error_code: 1286; Using storage engine MyISAM for table 't1', Error_code: 1266; handler error HA_ERR_END_OF_FILE; the event's master log master-bin.000001, end_log_pos 540

Is it same as in your case? Could you please confirm or reject if problem is repeatable with last 5.1. sources.

[25 Sep 2008 16:59] Philip Stoev

From this message

"Unknown table engine
'innodb', Error_code: 1286; Using storage engine MyISAM for table 't1',"

it appears that your servers do not start with innodb enabled. So I can not tell if it is the same bug or not. Please make sure you have innodb enabled and try again.

[25 Sep 2008 18:26] Sveta Smirnova

Thank you for the feedback.

Forgot to start slave with option --innodb. Verified as described with error:

Last_SQL_Errno  0
Last_SQL_Error  Could not execute Delete_rows event on table test.t1; handler error HA_ERR_END_OF_FILE; the event's master log master-bin.000001, end_log_pos 540

[23 Oct 2008 9:39] Lars Thalmann

On Thu, Oct 23, 2008 at 11:32:24AM +0200, Mats Kindahl wrote:
> Here is a detailed description of the problem and the solution.
>
> The cause of BUG#39648 is the way that InnoDB handles BIT fields that are NULL.
> In this particular case, we have the following scenario:
>
> - We have a table t1(a BIT, b BIT) holding the tuple (0, NULL).
>
> - We get a delete row from the master representing the tuple (0, NULL)
>
> The default record for the table is then [FF 00 00], i.e., the NULL bits are set
> for the record and the contents of the fields are 0.
>
> Delete proceeds by locating the row to delete using one of four different
> techniques, three of these require primary key or index, and the last fall-back
> is using range search. In this case, a range search will be used.
>
> To perform a range search, we call rnd_init() to start the range search over the
> entire table (the same applies when doing an index search using index_next() and
> friends), and then use rnd_next() to fetch the records one by one.
>
> Row-based replication does the following steps for each row:
>
> 1. Copy the default record into record[0] (which is used for searching).
>
>    a) This is done so that engines do not have to update fields that are
>       not in the read set or which are NULL and the memcmp() described below
>       will still result in a match.
>
> 2. Fill in record[0] with the fields received from the master (which is not
>    necessarily a full row). In this particular case, it will be [FD 00 *00*],
>    where the marked byte is the value from the default record while the other 0
>    is the "0" in the tuple. Note that FD == 11111101, i.e., the NULL bit for the
>    first column is clear while the NULL bit for the second column is set (the
>    least significant bit is the X bit).
>
> 3. Copy the resulting record to record[1]
>
> 4. Call rnd_next() to get the next record into record[0]. Here it is expected
>    that the storage engine do not touch any fields in the record unless the read
>    bit is set and the field is non-NULL.
>
>    a) InnoDB pads all the NULL BIT fields with 0x20 (space) *and* sets the NULL
>       bit, with the resulting record [FD 00 20]
>
> 5. The replication code does a memory compare of the record to decide if there
>    is a match.
>
>    a) Since InnoDB wrote 0x20 into the NULL field, they don't match.
>
> The alternative is to do a field-by-field comparison, which will catch the fact
> that the field is NULL and ignore the fact that the value was updated by InnoDB.
> However, that will be horribly slow compared to a simple memcmp(), so in that
> case, I will just add that workaround for the InnoDB engine.

[24 Oct 2008 7:18] Mats Kindahl

Here is a detailed description of the problem and the solution.

The cause of BUG#39648 is the way that InnoDB handles BIT fields that are NULL.
In this particular case, we have the following scenario:

- Table t1 holds the tuple (0, NULL).

- A delete row arrives from the master representing the tuple (0, NULL), i.e.,
  the tuple in the table shall be deleted.

The default record for the table is then [FF 00 00], i.e., the NULL bits are set
for the record and the contents of the fields are 0. (The first byte holds the NULL bits, the filler bits, and the X bit.) Note that since the NULL bits are set, the values in the record is not looked at when computing the value of the field for presenting to, e.g., a client.

Delete proceeds by locating the row to delete using one of four different
techniques, three of these require primary key or index, and the last fall-back
is using range search. In this case, a range search will be used.

To perform a range search, we call rnd_init() to start the range search over the
entire table and then use rnd_next() to fetch the records one by one. The same applies when doing an index search using index_next() and friends, but we do not consider this case here.

Row-based replication does the following steps for each row:

1. Copy the default record into record[0] (which is used for searching).

   a) This is done so that engines do not have to update fields that are
      not in the read set or which are NULL and the memcmp() described below
      will still result in a match.

   b) It also allows engines that don't know the contents of the row to "fake"
      finding the row and get the correct contents out. This is used by the
      Blackhole engine.

2. Fill in record[0] with the fields received from the master (which is not
   necessarily a full row). In this particular case, it will be [FD 00 *00*],
   where the marked byte is the value from the default record while the other
   00 is the "0" in the tuple to delete. Note that FD == 11111101, i.e., the
   NULL bit for the first column is clear while the NULL bit for the second
   column is set (the least significant bit is the X bit).

3. Copy the resulting record to record[1]

4. Call rnd_next() to get the next record *in the table* into record[0]. Here
   it is expected that the storage engine do not touch any fields in the record
   unless the read bit is set and the field is non-NULL. In the event of a NULL
   field with the read bit set, the engine shall *only* touch the NULL bit (to
   set it).

   a) Unfortunately, since InnoDB treats BIT as equivalent to CHAR(1), the NULL
      BIT field is padded with 0x20 (space) *and* the NULL bit is set, with the
      resulting record [FD 00 20].

5. The replication code does a memory compare of the record to decide if there
   is a match.

   a) Since InnoDB wrote 0x20 into the NULL field, they don't match, even
      though it is the correct record.

[10 Dec 2008 12:19] Marko Mäkelä

Does the row-based replication always call handler::extra(HA_EXTRA_KEYREAD_PRESERVE_FIELDS)?

If it does, I may have a patch, which must pass our internal review first.

If not, then I must point out that CHECKSUM TABLE is apparently still committing the "old sin" of not initializing records[0] between retrieving rows. I found this out by making row_sel_store_mysql_rec() and row_sel_pop_cached_row_for_mysql() avoid writing any data for NULL columns. innodb.test would report mismatch for CHECKSUM TABLE.

[10 Dec 2008 13:54] Mats Kindahl

I don't think that flags is currently set, but that can be arranged. It seems no other engine is using it anyway, so it should not cause any problems.

[12 Dec 2008 14:16] Marko Mäkelä

Given that there are code paths that do not initialize all fields of records[0] before fetching a row from the storage engine (CHECKSUM TABLE being an example), I think that it would be safer to pad BIT columns with 0x00 instead of 0x20. I will try to come up with such a patch next week.

[17 Dec 2008 8:07] Mats Kindahl

Marko,

Please consider using the value from the default record instead. In the event that the default value for the field is something that is not 0, there will be a mismatch again for tables like:

    create table t1(a bit default b'1');

[4 Mar 2009 7:01] Zhenxing He

BUG#40638 was marked as Duplicate of this bug.

[12 Mar 2009 22:17] Paul DuBois

Noted in 5.1.32, 6.0.11 changelogs.

With row-based binary logging, replication of InnoDB tables
containing NULL-valued BIT columns could fail.

[3 Jun 2009 8:58] Marko Mäkelä

Bug #40565 (different symptoms) shares the same cause.

[7 Jul 2009 7:52] Bugs System

Pushed into 5.0.84 (revid:[e-mail]-20090707074938-ksah1ibn0vs92cem) (version source revid:[e-mail]-20090625095026-w8pdxn8ls04g7tcp) (merge vers: 5.0.84) (pib:11)

[8 Jul 2009 13:30] Bugs System

Pushed into 5.1.37 (revid:[e-mail]-20090708131116-kyz8iotbum8w9yic) (version source revid:[e-mail]-20090626084621-g6zteyvzehto95j0) (merge vers: 5.1.37) (pib:11)

[9 Jul 2009 7:35] Bugs System

Pushed into 5.0.84 (revid:[e-mail]-20090707074938-ksah1ibn0vs92cem) (version source revid:[e-mail]-20090625095026-w8pdxn8ls04g7tcp) (merge vers: 5.0.84) (pib:11)

[9 Jul 2009 7:36] Bugs System

Pushed into 5.1.37 (revid:[e-mail]-20090708131116-kyz8iotbum8w9yic) (version source revid:[e-mail]-20090626084621-g6zteyvzehto95j0) (merge vers: 5.1.37) (pib:11)

[10 Jul 2009 11:21] Bugs System

Pushed into 5.4.4-alpha (revid:[e-mail]-20090710111017-bnh2cau84ug1hvei) (version source revid:[e-mail]-20090625092510-lq3mm6ytxb2ldfim) (merge vers: 5.4.4-alpha) (pib:11)

[26 Aug 2009 13:46] Bugs System

Pushed into 5.1.37-ndb-7.0.8 (revid:[e-mail]-20090826132541-yablppc59e3yb54l) (version source revid:[e-mail]-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

[26 Aug 2009 13:46] Bugs System

Pushed into 5.1.37-ndb-6.3.27 (revid:[e-mail]-20090826105955-bkj027t47gfbamnc) (version source revid:[e-mail]-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)

[26 Aug 2009 13:48] Bugs System

Pushed into 5.1.37-ndb-6.2.19 (revid:[e-mail]-20090825194404-37rtosk049t9koc4) (version source revid:[e-mail]-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)

[27 Aug 2009 16:33] Bugs System

Pushed into 5.1.35-ndb-7.1.0 (revid:[e-mail]-20090827163030-6o3kk6r2oua159hr) (version source revid:[e-mail]-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

[5 May 2010 15:12] Bugs System

Pushed into 5.1.47 (revid:[e-mail]-20100505145753-ivlt4hclbrjy8eye) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[6 May 2010 16:01] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

[28 May 2010 6:05] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100524190136-egaq7e8zgkwb9aqi) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (pib:16)

[28 May 2010 6:33] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100524190941-nuudpx60if25wsvx) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[28 May 2010 7:01] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100524185725-c8k5q7v60i5nix3t) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[29 May 2010 15:35] Paul DuBois

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

[15 Jun 2010 8:13] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100615080459-smuswd9ooeywcxuc) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)

[15 Jun 2010 8:29] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100615080558-cw01bzdqr1bdmmec) (version source revid:[e-mail]-20100415070122-1nxji8ym4mao13ao) (pib:16)

[17 Jun 2010 12:09] Bugs System

Pushed into 5.1.47-ndb-7.0.16 (revid:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 12:56] Bugs System

Pushed into 5.1.47-ndb-6.2.19 (revid:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 13:36] Bugs System

Pushed into 5.1.47-ndb-6.3.35 (revid:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[18 Jun 2010 21:42] Roel Van de Paar

Bug #46597 was marked as a duplicate of bug #40638, the latter which was previously marked as a duplicate of this bug.

[18 Jun 2010 21:49] Roel Van de Paar

See also bug #44360

[18 Jun 2010 21:50] Roel Van de Paar

See also bug #47322