Uploaded image for project: 'Percona XtraDB Cluster'
  1. Percona XtraDB Cluster
  2. PXC-2552

Not able to ALTER columns involved in the foreign key constraint

    Details

    • Type: Bug
    • Status: Open
    • Priority: High
    • Resolution: Unresolved
    • Affects Version/s: 5.7.25-31.35
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Environment:

      Ubuntu 18.04

      PXC 5.7.25 (inside a docker container)

      Description

      In PXC 5.7.25, ALTER TABLE query is failing with an error when trying to modify the column's data type if the column is part of the foreign key constraint.  Below example test case was captured from a 2 node PXC 5.7.25 test cluster.

      Steps to reproduce:

      1. Creating parent table product_catalog. catalog_id is defined as INT(11).

       

      CREATE TABLE `product_catalog` (
        `catalog_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
        `catalog_type` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
        `catalog_desc` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
        PRIMARY KEY (`catalog_id`),
        KEY `item_id_item_type` (`catalog_id`,`catalog_type`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
      

      2. Creating the child table product_details. product_catalog_id refers to catalog_id in the parent table above.

       

       

      CREATE TABLE `product_details` (
        `product_id` int(11) NOT NULL AUTO_INCREMENT,
        `product_catalog_id` int(11) unsigned NOT NULL,
        `product_code` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
        `product_type` varchar(4) COLLATE utf8mb4_unicode_ci NOT NULL,
        `product_mrk_value` blob,
        PRIMARY KEY (`product_id`),
        KEY `fk_audit_detail_audit_header_idx` (`product_catalog_id`),
        CONSTRAINT `product_details_ibfk_1` FOREIGN KEY (`product_catalog_id`) REFERENCES `product_catalog` (`catalog_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
      

       

       

      I want to modify the datatype of these two columns from INT(11) to BIGINT. ERROR 1832 received when the ALTER is issued on the child table.

      mysql> ALTER TABLE product_details MODIFY COLUMN product_catalog_id BIGINT UNSIGNED NOT NULL ;
      ERROR 1832 (HY000): Cannot change column 'product_catalog_id': used in a foreign key constraint 'product_details_ibfk_1'
      

      ERROR 1833 received when the ALTER is issued on the child table.

       

      mysql> ALTER TABLE product_catalog MODIFY COLUMN catalog_id BIGINT UNSIGNED NOT NULL ;
      ERROR 1833 (HY000): Cannot change column 'catalog_id': used in a foreign key constraint 'product_details_ibfk_1' of table 'DB255525.product_details'
      

       

       

      As per the bug reported in PS-4770https://jira.percona.com/browse/PS-4770 the behavior is different in PS and the ALTER is working in some SQL_MODEs(STRICT_TRANS_TABLES or STRICT_ALL_TABLES or TRADITIONAL).

      I see that the ALTERs are failing even in those SQL_MODES in PXC 5.7.25.

      mysql> set sql_mode='TRADITIONAL';
      Query OK, 0 rows affected (0.00 sec)
      
      mysql> ALTER TABLE product_details MODIFY COLUMN product_catalog_id BIGINT UNSIGNED NOT NULL ;
      ERROR 1832 (HY000): Cannot change column 'product_catalog_id': used in a foreign key constraint 'product_details_ibfk_1'
      

       

      Workaround:

      The only workaround I could figure out so far is to drop the Foreign key constraint on the child table and perform the ALTER on both the tables and then re inforce the Foreign key constraint. 

       

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                uday.varagani Uday Varagani
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated: