Bug #69974 Replication breaks on delete from wordpress
Submitted: 9 Aug 2013 15:51 Modified: 21 Jul 2014 12:24
Reporter: Martin Moerner Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.5.31 OS:Linux (Ubuntu 12.04.2)
Assigned to: CPU Architecture:Any

[9 Aug 2013 15:51] Martin Moerner
Description:
Our Wordpress instance seems to halt the replication every 2 days or so with the following error:

Last_Error: Could not execute Delete_rows event on table table.wp_similar_posts; Can't find record in 'wp_similar_posts', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000008, end_log_pos 53641792

Binlog Data and query:

# at 53641792
#130809 16:25:09 server id 1  end_log_pos 53641870 	Query	thread_id=1944840	exec_time=1	error_code=0
SET TIMESTAMP=1376058309/*!*/;
SET @@session.pseudo_thread_id=1944840/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@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=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
COMMIT
/*!*/;
# at 53641870
#130809 16:25:10 server id 1  end_log_pos 53641947 	Query	thread_id=1944840	exec_time=0	error_code=0
SET TIMESTAMP=1376058310/*!*/;
BEGIN
/*!*/;
# at 53641947
# at 53642013
#130809 16:25:10 server id 1  end_log_pos 53642013 	Table_map: `fastforward_t`.`wp_options` mapped to number 65545
#130809 16:25:10 server id 1  end_log_pos 53644937 	Delete_rows: table id 65545 flags: STMT_END_F

'/*!*/;
### DELETE FROM `fastforward_t`.`wp_options`
### WHERE
###   @1=128135
###   @2='kjgrc_cache'
###   @3='a:2:{s:8:"comments";s:2532:"PGxpIGlkPSJnZXQtcmVjZW50LWNvbW1lbnRzIiBjbGFzcz0id2lkZ2V0IHdpZGdldF9nZXRfcmVjZW50X2NvbW1lbnRzIj48aDIgY2xhc3M9IndpZGdldHRpdGxlIj5OZXVlc3RlIEtvbW1lbnRhcmU8L2gyPgo8ZGl2IGlkPSJnZXRfcmVjZW50X2NvbW1lbnRzX3dyYXAiPjx1bD4JPGxpPkZyYXVrZSBFaGxlcnM6IDxhIGhyZWY9Imh0dHBzOi8vdGVzdGluZy5mYXN0Zm9yd2FyZC5hcGVydG8uZGUvP3A9MTY4OTUjY29tbWVudC0zNzQ4IiB0aXRsZT0iWmltbWVyIGZyZWkuICAsIDIzLiBKdWxpIDIwMTMiPlppbW1lciBpc3QgdmVyZ2ViZW4uIDopPC9hPjwvbGk+Cgk8bGk+RmxvcmlhbiBHw6RuZzogPGEgaHJlZj0iaHR0cHM6Ly90ZXN0aW5nLmZhc3Rmb3J3YXJkLmFwZXJ0by5kZS8/cD0xNjgzOSNjb21tZW50LTM3NDciIHRpdGxlPSJQcmVzc2VtZWxkdW5nZW46IFVuaSBCYXNlbCB1bmQgbGFGcmFpc2UsIDE4LiBKdWxpIDIwMTMiPkludGVyZXNzYW50ZSBSZWRld2VuZHVuZyAmIzgyMjA7aW0gQmVyaXR0JiM4MjIxOyA6LSk8L2E+PC9saT4KCTxsaT5QaGlsaXBwIFNjaGllZGVsOiA8YSBocmVmPSJodHRwczovL3Rlc3RpbmcuZmFzdGZvcndhcmQuYXBlcnRvLmRlLz9wPTE2ODM2I2NvbW1lbnQtMzc0NiIgdGl0bGU9IlRvbnkgS2F5ZSBpbiBCZXJsaW4gKGbDvHIgZGllIEZpbG1mcmV1bmRlIHVudGVyIHVucyksIDE3LiBKdWxpIDIwMTMiPkNvb2wuIEJpbiBsZWlkZXIgaW0gVXJsYXViLjwvYT48L2xpPgoJPGxpPkZsb3JpYW4gR8Okbmc6IDxhIGhyZWY9Imh0dHBzOi8vdGVzdGluZy5mYXN0Zm9yd2FyZC5hcGVydG8uZGUvP3A9MTY4MDEjY29tbWVudC0zNzQ1IiB0aXRsZT0iV2FudGVkOiBHcnVtcHkgQ2F0IFRhc3NlLCAxMC4gSnVsaSAyMDEzIj5CbG9nZ29wb3dlciEgVGFzc2UgaXN0IGluIGRlciBQaWFub2ZhYnJpayBhdWZnZXRhdWNodCB1bmQgd2llZGVyIGltIEvDtnJiY2hlbi48L2E+PC9saT4KCTxsaT5TZWJhc3RpYW4gTWFzY2hpbnNraTogPGEgaHJlZj0iaHR0cHM6Ly90ZXN0aW5nLmZhc3Rmb3J3YXJkLmFwZXJ0by5kZS8/cD0xNjc3NSNjb21tZW50LTM3NDQiIHRpdGxlPSJBcGVydG8gYmVpIFN0eWxlVXBZb3VyT2ZmaWNlLmNvbSwgOC4gSnVsaSAyMDEzIj5TZWl0IEV3aWdrZWl0ZW4gaXN0IGd1dC4gRGFzIEJpbGQgaXN0IG1pbmRlc3RlbnMgNiBKYWhyZSBhbHQuIEFiZXIgdHJvdHpkZW0gdG9sbGUgQmlsZGVyLiBEZXIuLi48L2E+PC9saT4KCTxsaT5OYS1Zb3VuZyBMZWU6IDxhIGhyZWY9Imh0dHBzOi8vdGVzdGluZy5mYXN0Zm9yd2FyZC5hcGVydG8uZGUvP3A9MTY3NzUjY29tbWVudC0zNzQzIiB0aXRsZT0iQXBlcnRvIGJlaSBTdHlsZVVwWW91ck9mZmljZS5jb20sIDguIEp1bGkgMjAxMyI+RWNodCBzY2jDtm5lIEJpbGRlci4gSWNoIGZpbmRlIGVzIG51ciBzY2hhZGUsIGRhc3MgaWhyIGbDvHIgZGllIEF1w59lbmFuc2ljaHQgZGVyIEFnZW50dXIgc28gZWluIHVyYWx0Li4uPC9hPjwvbGk+Cgk8bGk+SmFuIEdlc3NlbmhhcmR0OiA8YSBocmVmPSJodHRwczovL3Rlc3RpbmcuZmFzdGZvcndhcmQuYXBlcnRvLmRlLz9wPTE2Nzk1I2NvbW1lbnQtMzc0MiIgdGl0bGU9IlJ1cGVydCBpbiBkZXIgTmV3IEJ1c2luZXNzIMO8YmVyIEJyYW5kIFV0aWxpdGllcyAsIDguIEp1bGkgMjAxMyI+UHJpbWEgQXJ0aWtlbCEgR2zDvGNrd3Vuc2NoITwvYT48L2xpPgoJPGxpPkNhdGhsZWVuIEViZXJoYXJkdDogPGEgaHJlZj0iaHR0cHM6Ly90ZXN0aW5nLmZhc3Rmb3J3YXJkLmFwZXJ0by5kZS8/cD0xNjc3MSNjb21tZW50LTM3NDEiIHRpdGxlPSJEZXIgUmVsYXVuY2ggZGVzIFNPUy1LaW5kZXJkb3JmICBlLlYuIGlzdCB2b2xsYnJhY2h0ISwgMjYuIEp1bmkgMjAxMyI+R2zDvGNrd3Vuc2NoITwvYT48L2xpPgo8L3VsPjwvZGl2PjwvbGk+Cg==";s:10:"trackbacks";s:276:"PGxpIGlkPSJnZXQtcmVjZW50LWNvbW1lbnRzIiBjbGFzcz0id2lkZ2V0IHdpZGdldF9nZXRfcmVjZW50X2NvbW1lbnRzIj48aDIgY2xhc3M9IndpZGdldHRpdGxlIj5SZWNlbnQgVHJhY2tiYWNrczwvaDI+CjxkaXYgaWQ9ImdldF9yZWNlbnRfY29tbWVudHNfd3JhcCI+PHVsPjxsaT48IS0tIG5vIGNvbW1lbnRzIHlldCAtLT48L2xpPjwvdWw+PC9kaXY+PC9saT4K";}'
###   @4='yes'
# at 53644937
#130809 16:25:10 server id 1  end_log_pos 53645015 	Query	thread_id=1944840	exec_time=0	error_code=0
SET TIMESTAMP=1376058310/*!*/;
COMMIT

Tables:

show create table wp_options\G
*************************** 1. row ***************************
       Table: wp_options
Create Table: CREATE TABLE `wp_options` (
  `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `option_name` varchar(64) NOT NULL DEFAULT '',
  `option_value` longtext NOT NULL,
  `autoload` varchar(20) NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`option_id`,`option_name`),
  UNIQUE KEY `option_name` (`option_name`)
) ENGINE=MyISAM AUTO_INCREMENT=128139 DEFAULT CHARSET=utf8

show create table wp_similar_posts\G
*************************** 1. row ***************************
       Table: wp_similar_posts
Create Table: CREATE TABLE `wp_similar_posts` (
  `pID` bigint(20) unsigned NOT NULL,
  `content` longtext NOT NULL,
  `title` text NOT NULL,
  `tags` text NOT NULL,
  FULLTEXT KEY `title` (`title`),
  FULLTEXT KEY `content` (`content`),
  FULLTEXT KEY `tags` (`tags`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

A fix would be awesome due to the repetition of this error.

Thanks in Advance

How to repeat:
Deletion on wp_options table of wordpress.
[9 Aug 2013 17:07] Davi Arnaut
Duplicate of Bug#58997
[21 Jul 2014 12:24] Sveta Smirnova
Thank you for the report.

Davi, thank you for the feedback.

Marked as duplicate of bug #58997

Please upgrade to version 5.6.