Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-7578

Replication failure with UPDATE when replica server has a PK and source not

Details

    Description

      Replication crashes with the following error when executing an UPDATE statement in the source(where the table does not have a PK) and the replica has a PK.

      Last_SQL_Errno: 1032
       Last_SQL_Error: Could not execute Update_rows event on table percona.test_table; Can't find record in 'test_table', Error_code: 1032; Can't find record in 'test_table', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000003, end_log_pos 29043405

      The same does not occur with INSERT statements.

      The binlog_format is set to ROW and using the FULL image.

       

      Test case:
      1-) create table in the source

      CREATE TABLE `test_table` (
      `id` int(10) NOT NULL DEFAULT '0',
      `field1` varchar(100),
      KEY `idx` (`id`,`field1`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

      2-) Insert some rows, check the source and replica

      insert into test_table values (1,'11/19'),(1,'11/19'),(1,'11/2019') on duplicate key update field1=values(field1), id=values(id);

      master [localhost:48008] {msandbox} (percona) > select * from test_table;
      -----------+

      id field1

      -----------+

      1 11/19
      1 11/19
      1 11/2019

      -----------+
      3 rows in set (0.00 sec)

      slave1 [localhost:48009] {msandbox} (percona) > select * from test_table;
      -----------+

      id field1

      -----------+

      1 11/19
      1 11/19
      1 11/2019

      -----------+
      3 rows in set (0.00 sec)

      3-) Add a PK on the replica

      slave1 [localhost:48009] {msandbox} (percona)> ALTER TABLE `test_table` ADD COLUMN `id_pk` BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY ;

      4-) Insert more rows, check both servers:

      insert into test_table values (1,'11/19'),(1,'11/19'),(1,'11/2019') on duplicate key update field1=values(field1), id=values(id);

      master [localhost:48008] {msandbox} (percona) > select * from test_table;
      -----------+

      id field1

      -----------+

      1 11/19
      1 11/19
      1 11/19
      1 11/19
      1 11/2019
      1 11/2019

      -----------+
      6 rows in set (0.00 sec)

      slave1 [localhost:48009] {msandbox} (percona) > select * from test_table;
      ----------------

      id field1 id_pk

      ----------------

      1 11/19 1
      1 11/19 2
      1 11/19 4
      1 11/19 5
      1 11/2019 3
      1 11/2019 6

      ----------------
      6 rows in set (0.01 sec)

      5-) Run update on the source server

      master [localhost:48008] {msandbox} (percona) > update test_table set id=1,field1='11/2019';
      Query OK, 4 rows affected (0.01 sec)
      Rows matched: 6 Changed: 4 Warnings: 0

      6-) Check replication and its broken

      Last_SQL_Errno: 1032
      Last_SQL_Error: Could not execute Update_rows event on table percona.test_table; Can't find record in 'test_table', Error_code: 1032; Can't find record in 'test_table', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000003, end_log_pos 29043405
       

      Attachments

        Activity

          People

            venkatesh.prasad Venkatesh Prasad
            vinicius.grippa Vinicius Grippa
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - Not Specified
                Not Specified
                Logged:
                Time Spent - 1 week, 3 days, 4 hours
                1w 3d 4h

                Smart Checklist