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

FK Constraint not dropped but modify succesfull

Details

    Description

      Have some PRI keys which are created as int which must be changed to bigint.
      on 10.4.24 I could just disable foreign_key_checks but this no longer works as it is described else where MDEV-31086

      table:

      | documententries | CREATE TABLE `documententries` (
      		 
        `id` int(11) NOT NULL AUTO_INCREMENT,
      		 
        `entryuuid` varchar(64) NOT NULL,
      		 
        `logicaluuid` varchar(64) NOT NULL,
      		 
        `version` int(11) NOT NULL,
      		 
        `patientid` varchar(32) NOT NULL,
      		 
        `patientid_assigningauthorityid` varchar(32) NOT NULL,
      		 
        `patientid_assigningauthoritytype` varchar(16) NOT NULL,
      		 
        `availabilitystatus` varchar(64) NOT NULL,
      		 
        `documententrytype` varchar(64) NOT NULL,
      		 
        `uniqueid` varchar(128) NOT NULL,
      		 
        `classcode_codename` varchar(64) NOT NULL,
      		 
        `classcode_schemename` varchar(64) NOT NULL,
      		 
        `typecode_codename` varchar(64) NOT NULL,
      		 
        `typecode_schemename` varchar(64) NOT NULL,
      		 
        `practicesettingcode_codename` varchar(64) DEFAULT NULL,
      		 
        `practicesettingcode_schemename` varchar(64) DEFAULT NULL,
      		 
        `creationtime` datetime(6) DEFAULT NULL,
      		 
        `servicestarttime` datetime(6) DEFAULT NULL,
      		 
        `servicestoptime` datetime(6) DEFAULT NULL,
      		 
        `healthcarefacilitytypecode_codename` varchar(64) NOT NULL,
      		 
        `healthcarefacilitytypecode_schemename` varchar(64) NOT NULL,
      		 
        `formatcode_codename` varchar(64) NOT NULL,
      		 
        `formatcode_schemename` varchar(64) NOT NULL,
      		 
        `documententrycontentid` int(11) NOT NULL,
      		 
        `creation_time` datetime(6) NOT NULL DEFAULT current_timestamp(6),
      		 
        `last_update_time` datetime(6) DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6),
      		 
        `migration_pid` int(11) NOT NULL DEFAULT 0,
      		 
        `creationtime_upper` datetime(6) DEFAULT NULL,
      		 
        `servicestarttime_upper` datetime(6) DEFAULT NULL,
      		 
        `servicestoptime_upper` datetime(6) DEFAULT NULL,
      		 
        `deletetrigger_time` datetime(6) GENERATED ALWAYS AS (coalesce(`servicestarttime`,`creation_time`)) STORED,
      		 
        `repository_uniqueid` varchar(64) DEFAULT NULL,
      		 
        PRIMARY KEY (`id`),
      		 
        UNIQUE KEY `documententrycontentid` (`documententrycontentid`),
      		 
        UNIQUE KEY `documententry_entryuuid_uk` (`entryuuid`),
      		 
        KEY `documententries_patientid_idx` (`patientid`,`patientid_assigningauthorityid`,`patientid_assigningauthoritytype`,`id`),
      		 
        KEY `documententries_uniqueid_idx` (`uniqueid`,`id`),
      		 
        KEY `documententries_entryuuid_idx` (`entryuuid`,`id`),
      		 
        KEY `documententries_logicaluuid_idx` (`logicaluuid`),
      		 
        KEY `documententries_patientid_availabilitystatus_idx` (`patientid`,`patientid_assigningauthorityid`,`patientid_assigningauthoritytype`,`availabilitystatus`,`id`),
      		 
        KEY `documententries_type_code_type_scheme_deletetrigger` (`typecode_codename`,`typecode_schemename`,`deletetrigger_time`),
      		 
        CONSTRAINT `documententry_fk_content` FOREIGN KEY (`documententrycontentid`) REFERENCES `documententrycontent` (`id`) ON DELETE CASCADE
      		 
      ) ENGINE=InnoDB AUTO_INCREMENT=70400 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin |


      MariaDB [SOLOnxrg]> ALTER TABLE documententries
      		 
          -> DROP CONSTRAINT documententry_fk_content,
      		 
          -> MODIFY COLUMN id BIGINT(11) NOT NULL AUTO_INCREMENT,
      		 
          -> MODIFY COLUMN documententrycontentid BIGINT(11) NOT NULL;
      		 
      Query OK, 26235 rows affected (0.532 sec)
      		 
      Records: 26235  Duplicates: 0  Warnings: 0
      		 
       
      		 
      MariaDB [SOLOnxrg]> ALTER TABLE documententrycontent
      		 
          -> MODIFY COLUMN id BIGINT(11) NOT NULL AUTO_INCREMENT;
      		 
      ERROR 1833 (HY000): Cannot change column 'id': used in a foreign key constraint 'documententry_fk_content' of table 'SOLOnxrg.documententries'

      For some reason it is not dropped the first time on table documententries.

      Dropping it alone without any modify it actually drops it and I can perform the MODIFY.

      MariaDB [SOLOnxrg]> ALTER TABLE documententries
      		 
          -> DROP CONSTRAINT documententry_fk_content;
      		 
      Query OK, 0 rows affected (0.013 sec)
      		 
      Records: 0  Duplicates: 0  Warnings: 0
      		 
       
      		 
      MariaDB [SOLOnxrg]> ALTER TABLE documententrycontent
      		 
          -> MODIFY COLUMN id BIGINT(11) NOT NULL AUTO_INCREMENT;
      		 
      Query OK, 26235 rows affected (0.464 sec)
      		 
      Records: 26235  Duplicates: 0  Warnings: 0
      		 
       
      		 
      MariaDB [SOLOnxrg]> ALTER TABLE documententries ADD CONSTRAINT `documententry_fk_content` FOREIGN KEY (`documententrycontentid`) REFERENCES `documententrycontent` (`id`) ON DELETE CASCADE;
      		 
      Query OK, 0 rows affected (0.009 sec)
      		 
      Records: 0  Duplicates: 0  Warnings: 0

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31086 MODIFY COLUMN can break FK constraints, and lead to unrestorable dumps

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            marko Marko Mäkelä added a comment -

            I can confirm this with the following:

            --source include/have_innodb.inc
            		 
             
            		 
            create table parent(id int primary key) engine=innodb;
            		 
            create table child
            		 
            (id int primary key,
            		 
             parent_id int not null,
            		 
             constraint foreign key fk(parent_id) references parent(id))
            		 
            engine=innodb;
            		 
             
            		 
            --error ER_ERROR_ON_RENAME
            		 
            alter table child drop constraint fk,
            		 
            modify id bigint, modify parent_id bigint not null;
            		 
             
            		 
            alter table child drop constraint fk;
            		 
            alter table child modify id bigint, modify parent_id bigint not null;
            		 
             
            		 
            alter table parent modify id bigint;
            		 
            alter table child add foreign key fk(parent_id) references parent(id);
            		 
             
            		 
            --error ER_NO_REFERENCED_ROW_2
            		 
            insert into child values(1,1);
            		 
            insert into parent values(1);
            		 
            insert into child values(1,1);
            		 
             
            		 
            drop table child,parent;

            The bug is that the DROP CONSTRAINT fk has to be executed in a separate ALTER TABLE statement in order to avoid the error:

            10.6 887bb3f73555ff8a50138a580ca8308b9b5c069c

            		 
            mysqltest: At line 11: query 'alter table child drop constraint fk,
            		 
            modify id bigint, modify parent_id bigint not null' failed: ER_ERROR_ON_RENAME (1025): Error on rename of './test/#sql-alter-27626-4' to './test/child' (errno: 150 "Foreign key constraint is incorrectly formed")

            I would expect this to be similar on 10.4 and 10.5, but I did not test that.

            marko Marko Mäkelä added a comment - I can confirm this with the following: --source include/have_innodb.inc   create table parent(id int primary key ) engine=innodb; create table child (id int primary key , parent_id int not null , constraint foreign key fk(parent_id) references parent(id)) engine=innodb;   --error ER_ERROR_ON_RENAME alter table child drop constraint fk, modify id bigint , modify parent_id bigint not null ;   alter table child drop constraint fk; alter table child modify id bigint , modify parent_id bigint not null ;   alter table parent modify id bigint ; alter table child add foreign key fk(parent_id) references parent(id);   --error ER_NO_REFERENCED_ROW_2 insert into child values (1,1); insert into parent values (1); insert into child values (1,1);   drop table child,parent; The bug is that the DROP CONSTRAINT fk has to be executed in a separate ALTER TABLE statement in order to avoid the error: 10.6 887bb3f73555ff8a50138a580ca8308b9b5c069c mysqltest: At line 11: query 'alter table child drop constraint fk, modify id bigint, modify parent_id bigint not null' failed: ER_ERROR_ON_RENAME (1025): Error on rename of './test/#sql-alter-27626-4' to './test/child' (errno: 150 "Foreign key constraint is incorrectly formed") I would expect this to be similar on 10.4 and 10.5, but I did not test that.

            People

              thiru Thirunarayanan Balathandayuthapani
              lmk@netic.dk Lars Mikkelsen
              Votes:
              1 Vote for this issue
              Watchers:
              3 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.