Uploaded image for project: 'Percona Server'
  1. Percona Server
  2. PS-4770

Not able to ALTER column with foreign key without some SQL_MODES

    Details

    • Type: Bug
    • Status: On Hold
    • Priority: Medium
    • Resolution: Unresolved
    • Affects Version/s: 5.6, 5.7, 8.0
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      Description

      When you do ALTER a column having foreign key, not able to convert from DEFAULT NULL to NOT NULL. Please check the test case below:

      mysql> CREATE TABLE `a` (
      {{ -> `id` int(11) NOT NULL,}}
      {{ -> `name` text,}}
      {{ -> PRIMARY KEY (`id`)}}
      {{ -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;}}
      Query OK, 0 rows affected (0.02 sec)mysql> CREATE TABLE `b` (
      {{ -> `id` int(11) NOT NULL,}}
      {{ -> `fid` int(11) DEFAULT NULL,}}
      {{ -> PRIMARY KEY (`id`),}}
      {{ -> KEY `fid` (`fid`),}}
      {{ -> CONSTRAINT `b_ibfk_1` FOREIGN KEY (`fid`) REFERENCES `a` (`id`)}}
      {{ -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;}}
      Query OK, 0 rows affected (0.03 sec)mysql> set sql_mode='';
      Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'sql_mode';
      --------------------+
      | Variable_name | Value |
      --------------------+
      | sql_mode | |
      --------------------+
      1 row in set (0.01 sec)mysql> alter table b modify column fid int not null ;
      ERROR 1832 (HY000): Cannot change column 'fid': used in a foreign key constraint 'b_ibfk_1'

       

      Workaround:

      when you have either one of these SQL_MODES (or haven't checked all possibilities), you could do the ALTER without issue - STRICT_TRANS_TABLES or STRICT_ALL_TABLES or TRADITIONAL. 

      mysql> set sql_mode='TRADITIONAL';
      Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> alter table b modify column fid int not null ;
      Query OK, 0 rows affected (0.05 sec)
      Records: 0 Duplicates: 0 Warnings: 0

       

      Checked with both PS and MySQL 5.6 and 5.7

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                vinodh.krishnaswamy Vinodh Krishnaswamy
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated: