Bug #74774 MySQL replication failing with HA_KEY_NOT_FOUND_ERROR even with data available
Submitted: 11 Nov 2014 0:15 Modified: 7 Nov 2019 14:57
Reporter: Srinivasa Krishna Mamillapalli Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S2 (Serious)
Version:5.5.x OS:Linux
Assigned to: MySQL Verification Team CPU Architecture:Any
Tags: HA_ERR_END_OF_FILE, HA_KEY_NOT_FOUND_ERROR

[11 Nov 2014 0:15] Srinivasa Krishna Mamillapalli
Description:
MySQL replication is reporting slave failures with the below errors even when the slave is having the matching the records. 
 
Could not execute Delete_rows event on table 'db.table'; Can't find record in 'table', Error_code: 1032

we have been receiving similar errors with MySQL. 
Using Innodb and Mixed mode replication.

How to repeat:
NA
[16 Dec 2014 17:37] Srinivasa Krishna Mamillapalli
It is really embarrassing to see no ack from MySQL dev team for this bug report even after a month.
[18 Feb 2016 21:20] sdfsfd sdfsdf
Anyone is listening?

Same here, with 5.1.73-3.el6.5 centos 6.5 master and 5.5.48-1.el6.remi centos 6.7 slave.

Using innodb and row mode replication.

mysqlbinlog -v on master shows 66 rows in the query failed all being present on the slave.

The initial data sync was done using mysqldump --master-data.

mysqlbinlog output follows
[CODE]
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160211 13:31:26 server id 11  end_log_pos 106  Start: binlog v 4, server v 5.1.73-log created 160211 13:31:26
BINLOG '
/mK8Vg8LAAAAZgAAAGoAAAAAAAQANS4xLjczLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
# at 481576144
#160211 15:38:31 server id 11  end_log_pos 481576220    Query   thread_id=27805 exec_time=0     error_code=0
SET TIMESTAMP=1455194311/*!*/;
SET @@session.pseudo_thread_id=27805/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=192,@@session.collation_server=192/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 481576220
# at 481576296
# at 481577325
#160211 15:38:31 server id 11  end_log_pos 481576296    Table_map: `sitemanager0`.`b_search_content_stem` mapped to number 159
#160211 15:38:31 server id 11  end_log_pos 481577325    Delete_rows: table id 159
#160211 15:38:31 server id 11  end_log_pos 481577674    Delete_rows: table id 159 flags: STMT_END_F

BINLOG '
x4C8VhMLAAAATAAAAGhFtBwAAJ8AAAAAAAEADHNpdGVtYW5hZ2VyNAAVYl9zZWFyY2hfY29udGVu
dF9zdGVtAAUD/gMEBAT+BgQEAA==
x4C8VhkLAAAABQQAAG1JtBwAAJ8AAAAAAAAABf/gqgsBAAJydSsAAADD9ag+mpkdQ+CqCwEAAnJ1
eQAAAJMYxD5r22JD4KoLAQACcnWFAQAAysMCPgAAKELgqgsBAAJydYcBAADKwwI+AADwQeCqCwEA
AnJ1iwEAAK62gj6mqtRC4KoLAQACcnWPAQAAysMCPgAAGELgqgsBAAJydY0CAACutoI+TVVhQuCq
CwEAAnJ1jwIAAMrDAj4AAOhB4KoLAQACcnW1AgAAysMCPgAA+EHgqgsBAAJydbcCAAC7J08+AIAX
Q+CqCwEAAnJ1uQIAALsnTz4AAC9D4KoLAQACcnX5AgAAysMCPgAANELgqgsBAAJydVkDAAC+wZc+
AAB6QuCqCwEAAnJ1XwMAAMrDAj4AAABC4KoLAQACcnWFAwAAysMCPgAAQEHgqgsBAAJyddMDAAC7
J08+AAC0QuCqCwEAAnJ1UQQAAMrDAj4AACBD4KoLAQACcnV9BQAAuydPPgAAkEHgqgsBAAJydYsF
AAC7J08+AICZQ+CqCwEAAnJ1CQYAALsnTz4AgCRD4KoLAQACcnUVBgAAysMCPgAAPELgqgsBAAJy
dRkGAADKwwI+AABAQuCqCwEAAnJ1UwYAALsnTz4AAB5D4KoLAQACcnUZBwAAysMCPgAALELgqgsB
AAJydRkIAAC7J08+AIB+Q+CqCwEAAnJ1qwkAAL7Blz4AQH9D4KoLAQACcnWtCQAAvsGXPgBAf0Pg
qgsBAAJydRsLAADKwwI+AAAEQuCqCwEAAnJ11wsAAMrDAj4AADhC4KoLAQACcnWLDAAAysMCPgAA
JELgqgsBAAJydYsNAADKwwI+AAAwQuCqCwEAAnJ1NREAAK62gj5NVWlC4KoLAQACcnX9EQAAw/Wo
PgAAfELgqgsBAAJydSsTAAC7J08+AIBCQ+CqCwEAAnJ1TRUAAL7Blz4AAH5C4KoLAQACcnXbFQAA
uydPPgCAQ0PgqgsBAAJydaUYAADD9Sg/YvFIQ+CqCwEAAnJ1XRkAALsnTz4AgH9D4KoLAQACcnWj
HAAAysMCPgCAhUPgqgsBAAJydecdAADXNM8+AADFQuCqCwEAAnJ18SAAAK62gj5NVWlC4KoLAQAC
cnUBIQAAysMCPgAAHkPgqgsBAAJydbEiAACTGMQ+uW1iQ+CqCwEAAnJ1ciMAALsnTz4AgJtD4KoL
AQACcnWvIwAAuydPPgAAm0PgqgsBAAJyda4lAAC+wZc+AACDQuCqCwEAAnJ1+yYAAMrDAj4AAIlD
4KoLAQACcnX/JgAAysMCPgAAmUPgqgsBAAJydZAnAAC+wZc+AAByQuCqCwEAAnJ1XS4AAMrDAj4A
ABFD
### DELETE FROM `sitemanager0`.`b_search_content_stem`
### WHERE
###   @1=68522
###   @2='ru'
###   @3=43
###   @4=0.33
###   @5=157.6
### DELETE FROM `sitemanager0`.`b_search_content_stem`
### WHERE
###   @1=68522
###   @2='ru'
###   @3=121
###   @4=0.383
###   @5=226.857

[...]

### DELETE FROM `sitemanager0`.`b_search_content_stem`
### WHERE
###   @1=68522
###   @2='ru'
###   @3=82375
###   @4=0.2553
###   @5=160.667
### DELETE FROM `sitemanager0`.`b_search_content_stem`
### WHERE
###   @1=68522
###   @2='ru'
###   @3=97251
###   @4=0.2023
###   @5=254.5
# at 481577674
#160211 15:38:31 server id 11  end_log_pos 481577701    Xid = 2070966
COMMIT/*!*/;
# at 481577701
#160211 15:38:31 server id 11  end_log_pos 481577777    Query   thread_id=27805 exec_time=0     error_code=0
SET TIMESTAMP=1455194311/*!*/;
BEGIN
[/CODE]

The table structure follows
[CODE]
CREATE TABLE `b_search_content_stem` (
  `SEARCH_CONTENT_ID` int(11) NOT NULL,
  `LANGUAGE_ID` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `STEM` int(11) NOT NULL,
  `TF` float NOT NULL,
  `PS` float NOT NULL,
  UNIQUE KEY `UX_B_SEARCH_CONTENT_STEM` (`STEM`,`LANGUAGE_ID`,`TF`,`PS`,`SEARCH_CONTENT_ID`),
  KEY `IND_B_SEARCH_CONTENT_STEM` (`SEARCH_CONTENT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci DELAY_KEY_WRITE=1
[/CODE]

I've heard adding auto_increment primary key fixes the issue but I have no possibility to do that.
[7 Oct 2019 14:57] MySQL Verification Team
Hi Srinivasa,

Can you please supply the create table of the tables in question and config for both master and slave servers?

sdf...,

You should really have a PK on your table, a good read:
https://jfg-mysql.blogspot.com/2017/08/danger-no-pk-with-RBR-and-mariadb-protection.html
[8 Nov 2019 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".