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

Can't create foreign key referencing a virtual column

Details

    Description

      create or replace table a (
        cola int(10) primary key,
        v_cola int(10) as (cola mod 10) virtual,
        p_cola int(10) as (cola mod 10) persistent
      );
       
      create index v_cola on a (v_cola);
      create index p_cola on a (p_cola);
       
      create or replace table b(
      cola int(10),
      v_cola int(10),
      p_cola int(10)
      );
       
      alter table b add constraint `p_cola_fk`
      foreign key (p_cola) references a (p_cola)
      on delete restrict
      on update restrict;
       
      alter table b add constraint `v_cola_fk`
      foreign key (v_cola) references a (v_cola)
      on delete restrict
      on update restrict;
      

      ERROR 1005 (HY000): Can't create table `test`.`#sql-181_8` (errno: 150 "Foreign key constraint is incorrectly formed")
      MariaDB [test]> show warnings;
      +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Level   | Code | Message                                                                                                                                                                                                              |
      +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Warning |  150 | Alter  table `test`.`b` with foreign key constraint failed. Parse error in '
      foreign key (v_cola) references a (v_cola)
      on delete restrict
      on update restrict' near 'v_cola)
      on delete restrict
      on update restrict'. |
      | Error   | 1005 | Can't create table `test`.`#sql-181_8` (errno: 150 "Foreign key constraint is incorrectly formed")                                                                                                                   |
      | Warning | 1215 | Cannot add foreign key constraint                                                                                                                                                                                    |
      +---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      3 rows in set (0.00 sec)
      
      

      Attachments

        Activity

          jplindst Jan Lindström (Inactive) added a comment - http://lists.askmonty.org/pipermail/commits/2017-January/010471.html

          Is it possible to match the columns by position (with a debug assertion that the names match with strcmp), instead of looping over all columns and looking for a matching name using a case-insensitive search?

          Also, please import (and if necessary, adapt) all the virtual column tests from MySQL 5.7 that are related to foreign keys, such as innodb.virtual_basic, innodb.virtual_fk, innodb.virtual_fk_restart. We want to make sure that the patch is not breaking other functionality.

          marko Marko Mäkelä added a comment - Is it possible to match the columns by position (with a debug assertion that the names match with strcmp), instead of looping over all columns and looking for a matching name using a case-insensitive search? Also, please import (and if necessary, adapt) all the virtual column tests from MySQL 5.7 that are related to foreign keys, such as innodb.virtual_basic, innodb.virtual_fk, innodb.virtual_fk_restart. We want to make sure that the patch is not breaking other functionality.

          I could not find easy way to map column positions to virtual column number. Tests you mention are already part of the gcol suite. Note that MySQL 5.7 does not support the above syntax or foreign keys to virtual columns:

          jan@jan-laptop-asus:~/mysql/mysql-server/mysql-test$ perl -I. -U mysql-test-run.pl --vardir=/dev/shm jfk
          Logging: mysql-test-run.pl  --vardir=/dev/shm jfk
          2017-01-31T09:21:54.420345Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
          2017-01-31T09:21:54.420405Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
          MySQL Version 5.7.17
          Checking supported features...
           - SSL connections supported
           - binaries are debug compiled
          Collecting tests...
          Checking leftover processes...
          Removing old var directory...
          Creating var directory '/dev/shm'...
          Installing system database...
          Using parallel: 1
           
          ==============================================================================
           
          TEST                                      RESULT   TIME (ms) or COMMENT
          --------------------------------------------------------------------------
           
          worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
           
          create table a (
          cola int(10) primary key,
          v_cola int(10) as (cola mod 10) virtual,
          p_cola int(10) as (cola mod 10)
          ) engine=innodb;
          create index v_cola on a (v_cola);
          create index p_cola on a (p_cola);
          create table b(
          cola int(10),
          v_cola int(10),
          p_cola int(10)
          ) engine=innodb;
          show create table a;
          Table	Create Table
          a	CREATE TABLE `a` (
            `cola` int(10) NOT NULL,
            `v_cola` int(10) GENERATED ALWAYS AS ((`cola` % 10)) VIRTUAL,
            `p_cola` int(10) GENERATED ALWAYS AS ((`cola` % 10)) VIRTUAL,
            PRIMARY KEY (`cola`),
            KEY `v_cola` (`v_cola`),
            KEY `p_cola` (`p_cola`)
          ) ENGINE=InnoDB DEFAULT CHARSET=latin1
          alter table b add constraint `p_cola_fk`
          foreign key (p_cola) references a (p_cola)
          on delete restrict
          on update restrict;
          ERROR HY000: Cannot add foreign key constraint
          show warnings;
          Level	Code	Message
          Error	1215	Cannot add foreign key constraint
          show create table b;
          Table	Create Table
          b	CREATE TABLE `b` (
            `cola` int(10) DEFAULT NULL,
            `v_cola` int(10) DEFAULT NULL,
            `p_cola` int(10) DEFAULT NULL
          ) ENGINE=InnoDB DEFAULT CHARSET=latin1
          alter table b add constraint `v_cola_fk`
          foreign key (v_cola) references a (v_cola)
          on delete restrict
          on update restrict;
          ERROR HY000: Cannot add foreign key constraint
          show warnings;
          Level	Code	Message
          Error	1215	Cannot add foreign key constraint
          show create table b;
          Table	Create Table
          b	CREATE TABLE `b` (
            `cola` int(10) DEFAULT NULL,
            `v_cola` int(10) DEFAULT NULL,
            `p_cola` int(10) DEFAULT NULL
          ) ENGINE=InnoDB DEFAULT CHARSET=latin1
          

          jplindst Jan Lindström (Inactive) added a comment - I could not find easy way to map column positions to virtual column number. Tests you mention are already part of the gcol suite. Note that MySQL 5.7 does not support the above syntax or foreign keys to virtual columns: jan@jan-laptop-asus:~/mysql/mysql-server/mysql-test$ perl -I. -U mysql-test-run.pl --vardir=/dev/shm jfk Logging: mysql-test-run.pl --vardir=/dev/shm jfk 2017-01-31T09:21:54.420345Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000) 2017-01-31T09:21:54.420405Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000) MySQL Version 5.7.17 Checking supported features... - SSL connections supported - binaries are debug compiled Collecting tests... Checking leftover processes... Removing old var directory... Creating var directory '/dev/shm'... Installing system database... Using parallel: 1   ==============================================================================   TEST RESULT TIME (ms) or COMMENT --------------------------------------------------------------------------   worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009   create table a ( cola int(10) primary key, v_cola int(10) as (cola mod 10) virtual, p_cola int(10) as (cola mod 10) ) engine=innodb; create index v_cola on a (v_cola); create index p_cola on a (p_cola); create table b( cola int(10), v_cola int(10), p_cola int(10) ) engine=innodb; show create table a; Table Create Table a CREATE TABLE `a` ( `cola` int(10) NOT NULL, `v_cola` int(10) GENERATED ALWAYS AS ((`cola` % 10)) VIRTUAL, `p_cola` int(10) GENERATED ALWAYS AS ((`cola` % 10)) VIRTUAL, PRIMARY KEY (`cola`), KEY `v_cola` (`v_cola`), KEY `p_cola` (`p_cola`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 alter table b add constraint `p_cola_fk` foreign key (p_cola) references a (p_cola) on delete restrict on update restrict; ERROR HY000: Cannot add foreign key constraint show warnings; Level Code Message Error 1215 Cannot add foreign key constraint show create table b; Table Create Table b CREATE TABLE `b` ( `cola` int(10) DEFAULT NULL, `v_cola` int(10) DEFAULT NULL, `p_cola` int(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 alter table b add constraint `v_cola_fk` foreign key (v_cola) references a (v_cola) on delete restrict on update restrict; ERROR HY000: Cannot add foreign key constraint show warnings; Level Code Message Error 1215 Cannot add foreign key constraint show create table b; Table Create Table b CREATE TABLE `b` ( `cola` int(10) DEFAULT NULL, `v_cola` int(10) DEFAULT NULL, `p_cola` int(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1

          commit 45f451c769668e6a351b0c023a994fdf9c07b1f7
          Author: Jan Lindström <jan.lindstrom@mariadb.com>
          Date: Fri Jan 20 12:10:13 2017 +0200

          MDEV-11850: Can't create foreign key referencing a virtual column

          Both dict_foreign_find_index and dict_foreign_qualify_index
          did not consider virtual columns as possible foreign key
          columns and there was assertion to disable virtual columns.

          Fixed by also looking referencing and referenced column
          from virtual columns if needed.

          jplindst Jan Lindström (Inactive) added a comment - commit 45f451c769668e6a351b0c023a994fdf9c07b1f7 Author: Jan Lindström <jan.lindstrom@mariadb.com> Date: Fri Jan 20 12:10:13 2017 +0200 MDEV-11850 : Can't create foreign key referencing a virtual column Both dict_foreign_find_index and dict_foreign_qualify_index did not consider virtual columns as possible foreign key columns and there was assertion to disable virtual columns. Fixed by also looking referencing and referenced column from virtual columns if needed.

          People

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