Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16248

Row based replication to spider with float column fails on delete/update

    XMLWordPrintable

Details

    Description

      Replicating using binlog_format = ROW to a spider table can fail for float columns for DELETE and UPDATE statements, due to spider sends delete/update queries to its datanodes that tries to match every column in the table definition, which may fail since you cannot do a comparison like 'float_column = 0.671437' reliably.

      After finding a repeatable test case (see attached test case files) i have also updated this description. I missed to add the 'UPDATE' to the test case, but just after the DELETE statement, add:
      UPDATE t1 SET f = 0.632 WHERE id = 2;

      Also notice in the .cnf file there is an additional way to even make the master fail.

      I have not tested federatedX, but assume it suffers from the same issue?

      The rest of this description is from the original bug report, where we found the inconsistency. This happened due to this bug but we found it first due to the row was never deleted, so the following part is more of a part of our troubleshooting:
      This was found after stopping an intermediate master, dumping the table (using --where) to several different spider data nodes (thus partitioning the data), setting up a spider head using those spider data nodes.

      Every now and then the replication fails with:
      Could not execute Write_rows event on table test_db.test_table; Duplicate entry '1234567890' for key 'PRIMARY', Error_code: 1062; handler error No Error!; the event's master log binlog.000001, end_log_pos 12345678

      From the error log on the spider head:
      20180522 13:35:40 [SEND SPIDER SQL] from 8688 to [testserver2.example.com] 22452051: sql: insert high_priority into
      `test_db`.`test_table2`(`id`,...)values(1234567890,...)
      20180522 13:35:40 [ERROR SPIDER RESULT] to 8688: 1062 Duplicate entry '1234567890' for key 'PRIMARY'
      2018-05-22 13:35:40 8688 [ERROR] Slave SQL: Could not execute Write_rows event on table test_db.test_table2; Duplicate entry '1234567890' for key 'PRIMARY', Error_code: 1062; handler error No Error!; the event's master log binlog.000001, end_log_pos 12345678, Internal MariaDB error code: 1062
      2018-05-22 13:35:40 8688 [Warning] Slave: Duplicate entry '1234567890' for key 'PRIMARY' Error_code: 1062

      From the query log on the spider head:
      8688 Query BEGIN
      8688 Query mysql testserver2.example.com set session transaction isolation level read committed ;set session autocommit = 1;start transaction
      8688 Query mysql testserver2.example.com insert high_priority into `test_db`.`test_table_1`(`id`,...)values(1234567890,...)
      8688 Query mysql testserver2.example.com insert high_priority into `test_db`.`test_table_2`(`id`,...)values(1234567890,...)
      8688 Query mysql testserver2.example.com rollback

      From the query log one of the data nodes:
      12345678 Connect test_user@testserver.example.com as anonymous on
      12345678 Query set session transaction isolation level read committed;set session autocommit = 1;start transaction
      12345678 Query SET NAMES latin1
      12345678 Init DB test_db
      12345678 Query insert high_priority into `test_db`.`test_table1`(`id`,...)values(1234567890,...)
      12345678 Query insert high_priority into `test_db`.`test_table2`(`id`,...)values(1234567890,...)
      12345678 Query rollback
      12345678 Quit

      And we can see a row in test_table_2 with that id, but not in test_table_1.

      show slave status output:

      Slave_IO_State: Waiting for master to send event
      Master_Host: intermediate_test_master
      Master_User: replication_user
      Master_Port: 3306
      Connect_Retry: 60
      Master_Log_File: binlog.000537
      Read_Master_Log_Pos: 94002275
      Relay_Log_File: relaylog.001004
      Relay_Log_Pos: 86488413
      Relay_Master_Log_File: binlog.000051
      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: 1062
      Last_Error: Could not execute Write_rows event on table test_db.test_table_2; Duplicate entry '1234567890' for key 'PRIMARY', Error_code: 1062; handler error No Error!; the event's master log binlog.000051, end_log_pos 87949203
      Skip_Counter: 0
      Exec_Master_Log_Pos: 87948048
      Relay_Log_Space: 49417412287
      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: 1062
      Last_SQL_Error: Could not execute Write_rows event on table test_db.test_table_2; Duplicate entry '1234567890' for key 'PRIMARY', Error_code: 1062; handler error No Error!; the event's master log binlog.000051, end_log_pos 87949203
      Replicate_Ignore_Server_Ids:
      Master_Server_Id: 217056038
      Master_SSL_Crl:
      Master_SSL_Crlpath:
      Using_Gtid: No
      Gtid_IO_Pos:
      Replicate_Do_Domain_Ids:
      Replicate_Ignore_Domain_Ids:
      Parallel_Mode: conservative
      SQL_Delay: 0
      SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
      Slave_DDL_Groups: 0
      Slave_Non_Transactional_Groups: 0
      Slave_Transactional_Groups: 0

      Attachments

        1. mdev_16248.cnf
          0.7 kB
        2. mdev_16248.test
          4 kB
        3. mdev_16248.result
          3 kB

        Issue Links

          Activity

            People

              Kentoku Kentoku Shiba (Inactive)
              mattiasjonsson Mattias Jonsson
              Votes:
              0 Vote for this issue
              Watchers:
              14 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.