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

INSERT ON DUPLICATE KEY UPDATE foreign key fail

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

            Ascending order - Click to sort in descending order
            xorax Martin P created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            Status Open [ 1 ] Confirmed [ 10101 ]
            elenst Elena Stepanova made changes -
            Component/s Storage Engine - InnoDB [ 10129 ]
            Fix Version/s 10.2 [ 14601 ]
            Affects Version/s 10.2 [ 14601 ]
            Assignee Marko Mäkelä [ marko ]
            elenst Elena Stepanova made changes -
            Labels upstream
            jplindst Jan Lindström (Inactive) made changes -
            Assignee Marko Mäkelä [ marko ] Jan Lindström [ jplindst ]
            jplindst Jan Lindström (Inactive) made changes -
            jplindst Jan Lindström (Inactive) made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            jplindst Jan Lindström (Inactive) made changes -
            Assignee Jan Lindström [ jplindst ] Marko Mäkelä [ marko ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Jan Lindström [ jplindst ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            jplindst Jan Lindström (Inactive) made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            jplindst Jan Lindström (Inactive) made changes -
            issue.field.resolutiondate 2017-11-16 09:14:47.0 2017-11-16 09:14:47.729
            jplindst Jan Lindström (Inactive) made changes -
            Fix Version/s 10.2.11 [ 22634 ]
            Fix Version/s 10.2 [ 14601 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            vtermanis Vilnis Termanis made changes -
            vtermanis Vilnis Termanis made changes -
            vtermanis Vilnis Termanis made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 81452 ] MariaDB v4 [ 152404 ]

            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.