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

LOAD DATA REPLACE into ucs2-encoded FK field fails

    XMLWordPrintable

Details

    Description

      A replace from file of duplicated values into ucs2-encoded referencing field fails:

      --source include/have_innodb.inc
       
      create table t1 (
        pk int primary key,
        f char(10) character set ucs2,
        key(f)
      ) engine=innodb;
       
      create table t2 (
        pk int primary key,
        f13 char(10) character set ucs2
      ) engine=innodb;
       
      set foreign_key_checks = off;
      alter table t2 add foreign key (f13) references t1 (f) on delete set null;
      set foreign_key_checks = on;
       
      insert into t1 values (1,'q');
      insert into t2(pk, f13) values (1, 'q'), (2, 'q');
       
      select * from t2 into outfile 't2.data';
       
      load data infile 't2.data' replace into table t2;
      # cleanup
      drop table t2, t1;
      

      10.3 c562ccf796c085211461386510ea5f7a8137cb96

      mysqltest: At line 26: query 'load data infile 't2.data' replace
      into table t2' failed: 1452: Cannot add or update a child row:
      a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` 
      FOREIGN KEY (`f13`) REFERENCES `t1` (`f`) ON DELETE SET NULL)
      

      Expected behavior: no error. Note that if a foreign key is added during CREATE TABLE, LOAD DATA does not fail:

      create table t1 (
        pk int primary key,
        f char(10) character set ucs2,
        key(f)
      ) engine=innodb;
       
      create table t2 (
        pk int primary key,
        f13 char(10) character set ucs2 references t1 (f) on delete set null
      ) engine=innodb;
       
      insert into t1 values (1,'q');
      insert into t2(pk, f13) values (1, 'q'), (2, 'q');
       
      select * from t2 into outfile 't2.data';
       
      load data infile 't2.data' replace into table t2;
      # cleanup
      drop table t2, t1;
      

      Attachments

        Issue Links

          Activity

            People

              nikitamalyavin Nikita Malyavin
              nikitamalyavin Nikita Malyavin
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.