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

INSERT ON DUPLICATE KEY UPDATE foreign key fail

    XMLWordPrintable

Details

    Description

      Using INSERT ... ON DUPLICATE KEY UPDATE on a table which has a field in a foreign key linked to a PRIMARY field, throw a foreign key error even this field is not updated.

      To reproduce :

      create database test;
      		 
      use test;
      		 
       
      		 
      CREATE TABLE `v` (
      		 
        `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      		 
        PRIMARY KEY (`id`)
      		 
      ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
      		 
       
      		 
      INSERT v values (1);
      		 
       
      		 
      CREATE TABLE `vp` (
      		 
        `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      		 
        `v_id` int(10) unsigned NOT NULL,
      		 
        `p_id` int(10) unsigned NOT NULL,
      		 
        `ppp` varchar(255) NOT NULL,
      		 
        PRIMARY KEY (`id`),
      		 
        UNIQUE KEY `IDX_vp_uniq` (`v_id`,`p_id`),
      		 
        KEY `FK_vp_v` (`v_id`),
      		 
        CONSTRAINT `FK_vp_v` FOREIGN KEY (`v_id`) REFERENCES `v` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
      		 
      ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
      		 
       
      		 
      INSERT vp VALUES (12, 1, 100, 'text12');
      		 
       
      		 
      set sql_mode='';
      		 
       
      		 
      INSERT INTO `vp` (`id`,`ppp`) VALUES (12, 'test12-2') ON DUPLICATE KEY UPDATE `ppp` = VALUES(`ppp`);

      Result:

      ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`vp`, CONSTRAINT `FK_vp_v` FOREIGN KEY (`v_id`) REFERENCES `v` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

      I can reproduce it on MariaDB 10.2.6 and 10.3.0.

      There is no error on 10.1.24.

      Thanks

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15042 INSERT ON DUPLICATE KEY UPDATE produces error 1032 (Can't find record)

          • Major - Major loss of function.
          • Closed
          includes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14390 INSERT .. ON DUPLICATE KEY UPDATE does not update

          • 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-17614 INSERT on dup key update is replication unsafe

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-9663 InnoDB assertion failure: *cursor->index->name == TEMP_INDEX_PREFIX, or !cursor->index->is_committed()

          • Major - Major loss of function.
          • Closed

          Activity

            People

              jplindst Jan Lindström (Inactive)
              xorax Martin P
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.