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

Cannot modify a column if there is a generated column before it and a foreign key

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.11.10, 10.5, 10.6, 10.11, 11.4, 11.7(EOL)
    • 10.5, 10.6, 10.11, 11.4
    • Server, Virtual Columns
    • None
    • Ubuntu and Debian

    Description

      Trying to modify a column that is not part of any foreign key throws an error that the column can not be modified because it is part of a foreign key.
      The prerequisites for the bug to manifest is to have a foreign key to another table for another column and a generated column before the column you are tryging to modify.

      Here is a simple script to reproduce: 

      DROP TABLE IF EXISTS b_poses;
      		 
      DROP TABLE IF EXISTS b_users;
      		 
       
      		 
      CREATE TABLE `b_users` (
      		 
        `USER_ID` int(11) NOT NULL AUTO_INCREMENT,
      		 
        PRIMARY KEY (`USER_ID`)
      		 
       );
      		 
       
      		 
      CREATE TABLE `b_poses` (
      		 
        `POS_ID` int(11) NOT NULL AUTO_INCREMENT,
      		 
        `DELETE_FLAG` tinyint(1) NOT NULL DEFAULT 0,
      		 
        `DEL_UNQ` char(0) GENERATED ALWAYS AS (if(`DELETE_FLAG` = 0,'',NULL)) STORED,
      		 
        `FOR_USER_ID` int(11) DEFAULT NULL,
      		 
        `XRANDR_PRIMARY` varchar(10) NOT NULL DEFAULT '',
      		 
        PRIMARY KEY (`POS_ID`),
      		 
        KEY `b_poses_fk11` (`FOR_USER_ID`),
      		 
        CONSTRAINT `b_poses_fk11` FOREIGN KEY (`FOR_USER_ID`) REFERENCES `b_users` (`USER_ID`) ON DELETE CASCADE
      		 
      );
      		 
       
      		 
      ALTER TABLE b_poses MODIFY COLUMN XRANDR_PRIMARY VARCHAR(25) NOT NULL DEFAULT '';

      This throws an error 

      ERROR 1832 (HY000) at line 20: Cannot change column 'XRANDR_PRIMARY': used in a foreign key constraint 'test/b_poses_fk11'


      If we move the generated column after the column we are trying to change the operation is successful.
      The operation is also successful if instead of trying to make the column bigger we make it lower.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          lukav Anton Avramov created issue -
          lukav Anton Avramov made changes -
          Field Original Value New Value
          Description Trying to modify a column that is not part of any foreign key throws an error that the column can not be modified because it is part of a foreign key.
          The prerequisites for the bug to manifest is to have a foreign key to another table for another column and a generated column before the column you are tryging to modify.

          Here is a simple script to reproduce:

          {code:sql}
          DROP TABLE IF EXISTS b_poses;
          DROP TABLE IF EXISTS b_users;

          CREATE TABLE `b_users` (
            `USER_ID` int(11) NOT NULL AUTO_INCREMENT,
            PRIMARY KEY (`USER_ID`)
           );

          CREATE TABLE `b_poses` (
            `POS_ID` int(11) NOT NULL AUTO_INCREMENT,
            `DELETE_FLAG` tinyint(1) NOT NULL DEFAULT 0,
            `DEL_UNQ` char(0) GENERATED ALWAYS AS (if(`DELETE_FLAG` = 0,'',NULL)) STORED,
            `FOR_USER_ID` int(11) DEFAULT NULL,
            `XRANDR_PRIMARY` varchar(10) NOT NULL DEFAULT '',
            PRIMARY KEY (`POS_ID`),
            KEY `b_poses_fk11` (`FOR_USER_ID`),
            CONSTRAINT `b_poses_fk11` FOREIGN KEY (`FOR_USER_ID`) REFERENCES `b_users` (`USER_ID`) ON DELETE CASCADE
          );

          ALTER TABLE b_poses MODIFY COLUMN XRANDR_PRIMARY VARCHAR(25) NOT NULL DEFAULT '';
          {code}

          This throws an error ```ERROR 1832 (HY000) at line 20: Cannot change column 'XRANDR_PRIMARY': used in a foreign key constraint 'test/b_poses_fk11'
          ```
          If we move the generated column after the column we are trying to change the operation is successful.
          The operation is also successful if instead of trying to make the column bigger we make it lower.           		Trying to modify a column that is not part of any foreign key throws an error that the column can not be modified because it is part of a foreign key.
          The prerequisites for the bug to manifest is to have a foreign key to another table for another column and a generated column before the column you are tryging to modify.

          Here is a simple script to reproduce:

          {code:sql}
          DROP TABLE IF EXISTS b_poses;
          DROP TABLE IF EXISTS b_users;

          CREATE TABLE `b_users` (
            `USER_ID` int(11) NOT NULL AUTO_INCREMENT,
            PRIMARY KEY (`USER_ID`)
           );

          CREATE TABLE `b_poses` (
            `POS_ID` int(11) NOT NULL AUTO_INCREMENT,
            `DELETE_FLAG` tinyint(1) NOT NULL DEFAULT 0,
            `DEL_UNQ` char(0) GENERATED ALWAYS AS (if(`DELETE_FLAG` = 0,'',NULL)) STORED,
            `FOR_USER_ID` int(11) DEFAULT NULL,
            `XRANDR_PRIMARY` varchar(10) NOT NULL DEFAULT '',
            PRIMARY KEY (`POS_ID`),
            KEY `b_poses_fk11` (`FOR_USER_ID`),
            CONSTRAINT `b_poses_fk11` FOREIGN KEY (`FOR_USER_ID`) REFERENCES `b_users` (`USER_ID`) ON DELETE CASCADE
          );

          ALTER TABLE b_poses MODIFY COLUMN XRANDR_PRIMARY VARCHAR(25) NOT NULL DEFAULT '';
          {code}

          This throws an error {code}ERROR 1832 (HY000) at line 20: Cannot change column 'XRANDR_PRIMARY': used in a foreign key constraint 'test/b_poses_fk11'{code}
          If we move the generated column after the column we are trying to change the operation is successful.
          The operation is also successful if instead of trying to make the column bigger we make it lower.
          alice Alice Sherepa made changes -
          Component/s Virtual Columns [ 10803 ]
          alice Alice Sherepa made changes -
          Fix Version/s 10.5 [ 23123 ]
          Fix Version/s 10.6 [ 24028 ]
          Fix Version/s 10.11 [ 27614 ]
          Fix Version/s 11.4 [ 29301 ]
          Fix Version/s 11.7 [ 29815 ]
          alice Alice Sherepa made changes -
          Affects Version/s 10.5 [ 23123 ]
          Affects Version/s 10.6 [ 24028 ]
          Affects Version/s 10.11 [ 27614 ]
          Affects Version/s 11.4 [ 29301 ]
          Affects Version/s 11.7 [ 29815 ]
          alice Alice Sherepa made changes -
          Status Open [ 1 ] Confirmed [ 10101 ]
          alice Alice Sherepa added a comment -

          Thanks! I repeated as described on 10.5-11.7. Seems to be related to STORED generated column:

          MariaDB [test]> create table t1 (user_id int primary key) engine=innodb;
          		 
          Query OK, 0 rows affected (0,039 sec)
          		 
           
          		 
          MariaDB [test]> CREATE TABLE t2 ( a int AS (1) stored, id int, b varchar(10),
          		 
              ->   constraint `a1` foreign key (id) references t1 (user_id) 
              -> )engine=innodb;
          		 
          Query OK, 0 rows affected (0,046 sec)
          		 
           
          		 
          MariaDB [test]> alter table t2 modify column b varchar(11) ;
          		 
          ERROR 1832 (HY000): Cannot change column 'b': used in a foreign key constraint 'test/a1'
          		 
           
          		 
          MariaDB [test]> CREATE or replace  TABLE t2 (id int,  a int AS (1) stored, b varchar(10),   constraint `a1` foreign key (id) references t1 (user_id)  )engine=innodb;
          		 
          Query OK, 0 rows affected (0,073 sec)
          		 
           
          		 
          MariaDB [test]> alter table t2 modify column b varchar(11) ;
          		 
          Query OK, 0 rows affected (0,042 sec)
          		 
          Records: 0  Duplicates: 0  Warnings: 0
          		 
           
          		 
          MariaDB [test]> CREATE or replace TABLE t2 ( a int AS (1), id int, b varchar(10),   constraint `a1` foreign key (id) references t1 (user_id)  )engine=innodb;
          		 
          Query OK, 0 rows affected (0,079 sec)
          		 
           
          		 
          MariaDB [test]> alter table t2 modify column b varchar(11) ;
          		 
          Query OK, 0 rows affected (0,047 sec)
          		 
          Records: 0  Duplicates: 0  Warnings: 0

          alice Alice Sherepa added a comment - Thanks! I repeated as described on 10.5-11.7. Seems to be related to STORED generated column: MariaDB [test]> create table t1 (user_id int primary key) engine=innodb; Query OK, 0 rows affected (0,039 sec)   MariaDB [test]> CREATE TABLE t2 ( a int AS (1) stored, id int, b varchar(10), -> constraint `a1` foreign key (id) references t1 (user_id) -> )engine=innodb; Query OK, 0 rows affected (0,046 sec)   MariaDB [test]> alter table t2 modify column b varchar(11) ; ERROR 1832 (HY000): Cannot change column 'b': used in a foreign key constraint 'test/a1'   MariaDB [test]> CREATE or replace TABLE t2 (id int, a int AS (1) stored, b varchar(10), constraint `a1` foreign key (id) references t1 (user_id) )engine=innodb; Query OK, 0 rows affected (0,073 sec)   MariaDB [test]> alter table t2 modify column b varchar(11) ; Query OK, 0 rows affected (0,042 sec) Records: 0 Duplicates: 0 Warnings: 0   MariaDB [test]> CREATE or replace TABLE t2 ( a int AS (1), id int, b varchar(10), constraint `a1` foreign key (id) references t1 (user_id) )engine=innodb; Query OK, 0 rows affected (0,079 sec)   MariaDB [test]> alter table t2 modify column b varchar(11) ; Query OK, 0 rows affected (0,047 sec) Records: 0 Duplicates: 0 Warnings: 0
          alice Alice Sherepa made changes -
          Assignee Nikita Malyavin [ nikitamalyavin ]
          julien.fritsch Julien Fritsch made changes -
          Fix Version/s 11.7(EOL) [ 29815 ]

          People

            nikitamalyavin Nikita Malyavin
            lukav Anton Avramov
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.