• Thirunarayanan Balathandayuthapani's avatar
    MDEV-31086 MODIFY COLUMN can break FK constraints, and lead to unrestorable dumps · 5f09b53b
    Thirunarayanan Balathandayuthapani authored
    - When foreign_key_check is disabled, allowing to modify the
    column which is part of foreign key constraint can lead to
    refusal of TRUNCATE TABLE, OPTIMIZE TABLE later. So it make
    sense to block the column modify operation when foreign key
    is involved irrespective of foreign_key_check variable.
    
    Correct way to modify the charset of the column when fk is involved:
    
    SET foreign_key_checks=OFF;
    ALTER TABLE child DROP FOREIGN KEY fk, MODIFY m VARCHAR(200) CHARSET utf8mb4;
    ALTER TABLE parent MODIFY m VARCHAR(200) CHARSET utf8mb4;
    ALTER TABLE child ADD CONSTRAINT FOREIGN KEY (m) REFERENCES PARENT(m);
    SET foreign_key_checks=ON;
    
    fk_check_column_changes(): Remove the FOREIGN_KEY_CHECKS while
    checking the column change for foreign key constraint. This
    is the partial revert of commit 5f1f2fc0
    and it changes the behaviour of copy alter algorithm
    
    ha_innobase::prepare_inplace_alter_table(): Find the modified
    column and check whether it is part of existing and newly
    added foreign key constraint.
    5f09b53b
innodb.result 99.5 KB