Bug #69491 Breaking replication with update on fulltext

Submitted: 17 Jun 2013 12:16 Modified: 18 Jun 2013 7:01
Reporter: Martin Korous Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.1 and 5.5 OS:Linux (debian)
Assigned to: CPU Architecture:Any
Tags: fulltext, replication

[17 Jun 2013 12:16] Martin Korous
Description:
Breaking replication with SQL update command on column where is fulltext. Replication with ROW log level format:

binlog_format = ROW
log-bin-trust-function-creators=1

Tested with versions:
5.1.49-3-log (Debian)
5.1.66-0+squeeze1 (Debian)
5.5.31-0+wheezy1-log (Debian)

How to repeat:
on master:
create table string (`text` text, FULLTEXT KEY `text` (`text`)) engine myisam;
insert into string values ("some string");
update string set text='new text' where text = 'some string';

on slave:
result of show slave status\G:

Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Update_rows event on table testdb.string; Can't find record in 'string', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000020, end_log_pos 46971
[17 Jun 2013 14:41] MySQL Verification Team
Thank you for the bug report.

 mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost                       
                  Master_User: miguel                          
                  Master_Port: 3306                            
                Connect_Retry: 60                              
              Master_Log_File: tikal-bin.000001                
          Read_Master_Log_Pos: 950                             
               Relay_Log_File: tikal-relay-bin.000002          
                Relay_Log_Pos: 714                             
        Relay_Master_Log_File: tikal-bin.000001                
             Slave_IO_Running: Yes                             
            Slave_SQL_Running: No                              
              Replicate_Do_DB:                                 
          Replicate_Ignore_DB:                                 
           Replicate_Do_Table:                                 
       Replicate_Ignore_Table:                                 
      Replicate_Wild_Do_Table:                                 
  Replicate_Wild_Ignore_Table:                                 
                   Last_Errno: 1032                            
                   Last_Error: Could not execute Update_rows event on table testdb.string; Can't find record in 'string', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log tikal-bin.000001, end_log_pos 879
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 706
              Relay_Log_Space: 1114
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1032
               Last_SQL_Error: Could not execute Update_rows event on table testdb.string; Can't find record in 'string', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log tikal-bin.000001, end_log_pos 879
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE "%versio%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 5.5.33              |
| protocol_version        | 10                  |
| slave_type_conversions  |                     |
| version                 | 5.5.33-debug-log    |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | Linux               |
+-------------------------+---------------------+
7 rows in set (0.00 sec)
[18 Jun 2013 7:01] Martin Korous
DELETE breaks replication too:

delete from string where text = 'some string';

Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Delete_rows event on table testdb.string; Can't find record in 'string', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000021, end_log_pos 1001

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.