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

Unexpected ER_KEY_NOT_FOUND upon REPLACE into table with foreign key with statement binary logging

Details

    Description

      --source include/have_innodb.inc
      		 
      --source include/have_log_bin.inc
      		 
      --source include/have_binlog_format_mixed.inc
      		 
       
      		 
      CREATE TABLE t1 (f1 INT, f2 INT, PRIMARY KEY (f1), UNIQUE(f2), FOREIGN KEY (f1) REFERENCES t1 (f2)) ENGINE=InnoDB;
      		 
      REPLACE INTO t1 VALUES (1,1),(2,2);
      		 
       
      		 
      # Cleanup
      		 
      DROP TABLE t1;

      The test case above produces error 1032 (ER_KEY_NOT_FOUND):

      Actual result

      		 
      mysqltest: At line 6: query 'REPLACE INTO t1 VALUES (1,1),(2,2)' failed: 1032: Can't find record in 't1'

      which is also written as an error in the error log.

      Normally you would expect a different error, ER_NO_REFERENCED_ROW_2:

      Expected result

      		 
      mysqltest: At line 6: query 'REPLACE INTO t1 VALUES (1,1),(2,2)' failed: 1452: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f2`))

      which is also not very intuitive, but eventually understandable.

      MariaDB 5.5-10.1 and MySQL 5.6/5.7 produce ER_NO_REFERENCED_ROW_2 for the same test case.

      MariaDB 10.2+ produces ER_NO_REFERENCED_ROW_2 if the binary log is not enabled, or the logging format is ROW, or INSERT is run instead of REPLACE.

      Attachments

        Issue Links

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17614 INSERT on dup key update is replication unsafe

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17073 INSERT…ON DUPLICATE KEY UPDATE became more deadlock-prone

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17603 Allow statement-based replication for REPLACE and INSERT…ON DUPLICATE KEY UPDATE

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            marko Marko Mäkelä added a comment -

            In both cases, row_ins_check_foreign_constraints() is reporting DB_NO_REFERENCED_ROW for inserting the record into the PRIMARY KEY. In the problematic case, this error is being ignored, and we will wrongly proceed to insert the record into the secondary index, which will cause row_mysql_handle_errors() to handle the DB_DUPLICATE_KEY.

            I was initially suspecting a regression due to MDEV-17073, but I am observing no change even after reverting that change. Rather, it looks like this is broken by the MySQL 5.7 change that MDEV-17073 only reverted for the case binlog_format=ROW.

            The following test works for me:

            --source include/have_innodb.inc
            		 
            --source include/innodb_binlog.inc
            		 
             
            		 
            CREATE TABLE t1(a INT PRIMARY KEY, b INT UNIQUE,
            		 
            FOREIGN KEY (a) REFERENCES t1 (b)) ENGINE=InnoDB;
            		 
            --error ER_NO_REFERENCED_ROW_2
            		 
            REPLACE INTO t1 VALUES (1,1),(2,2);
            		 
            DROP TABLE t1;


            ./mtr --mysqld=--binlog-format=row --parallel=auto innodb.replace_fk

            For binlog_format=MIXED, thd_rpl_stmt_based(trx->mysql_thd) would hold in row_ins(), and the error DB_NO_REFERENCED_ROW would be transformed into DB_DUPLICATE_KEY, causing the reported difference.

            marko Marko Mäkelä added a comment - In both cases, row_ins_check_foreign_constraints() is reporting DB_NO_REFERENCED_ROW for inserting the record into the PRIMARY KEY . In the problematic case, this error is being ignored, and we will wrongly proceed to insert the record into the secondary index, which will cause row_mysql_handle_errors() to handle the DB_DUPLICATE_KEY . I was initially suspecting a regression due to MDEV-17073 , but I am observing no change even after reverting that change. Rather, it looks like this is broken by the MySQL 5.7 change that MDEV-17073 only reverted for the case binlog_format=ROW . The following test works for me: --source include/have_innodb.inc --source include/innodb_binlog.inc   CREATE TABLE t1(a INT PRIMARY KEY , b INT UNIQUE , FOREIGN KEY (a) REFERENCES t1 (b)) ENGINE=InnoDB; --error ER_NO_REFERENCED_ROW_2 REPLACE INTO t1 VALUES (1,1),(2,2); DROP TABLE t1; ./mtr --mysqld=--binlog-format=row --parallel=auto innodb.replace_fk For binlog_format=MIXED , thd_rpl_stmt_based(trx->mysql_thd) would hold in row_ins() , and the error DB_NO_REFERENCED_ROW would be transformed into DB_DUPLICATE_KEY , causing the reported difference.
            marko Marko Mäkelä added a comment -

            A similar problem should be possible also for INSERT…ON DUPLICATE KEY UNIQUE.

            Elkin, could we always switch to binlog_format=ROW for REPLACE or INSERT…ON DUPLICATE KEY UNIQUE statements?

            If that is too broad, would it be feasible to switch to binlog_format=ROW if there exist FOREIGN KEY constraints either in the affected table(s) or in other tables that are referencing the affected table(s)?

            marko Marko Mäkelä added a comment - A similar problem should be possible also for INSERT…ON DUPLICATE KEY UNIQUE . Elkin , could we always switch to binlog_format=ROW for REPLACE or INSERT…ON DUPLICATE KEY UNIQUE statements? If that is too broad, would it be feasible to switch to binlog_format=ROW if there exist FOREIGN KEY constraints either in the affected table(s) or in other tables that are referencing the affected table(s)?
            marko Marko Mäkelä added a comment -

            This issue was fixed by my after-merge fix of MDEV-17614.

            marko Marko Mäkelä added a comment - This issue was fixed by my after-merge fix of MDEV-17614 .

            People

              marko Marko Mäkelä
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.