[MDEV-32003] MODIFY COLUMN no longer possible with Foreign Key Constraints Created: 2023-08-24  Updated: 2023-08-30  Resolved: 2023-08-30

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.11.5
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: KrZipfel Assignee: Unassigned
Resolution: Won't Fix Votes: 1
Labels: None

Issue Links:
Duplicate
duplicates MDEV-31987 Cannot disable FOREIGN_KEY_CHECKS any... Closed
Problem/Incident
is caused by MDEV-31086 MODIFY COLUMN can break FK constraint... Closed

 Description   

Starting with MariaDB version 10.11.5 modifying the column type of a foreign key in the referenced table leads to an error. Consider the following example:

MariaDB [(none)]> DROP DATABASE IF EXISTS playground;
MariaDB [(none)]> CREATE DATABASE playground;
MariaDB [(none)]> USE playground;

MariaDB [playground]> CREATE TABLE table1 (my_id VARCHAR(20), PRIMARY KEY (my_id));
MariaDB [playground]> CREATE TABLE table2 (your_id VARCHAR(20), FOREIGN KEY (your_id) REFERENCES table1(my_id));

MariaDB [playground]> ALTER TABLE table1 MODIFY my_id VARCHAR(25);
ERROR 1833 (HY000): Cannot change column 'my_id': used in a foreign key constraint 'playground/table2_ibfk_1' of table 'playground/table2'

This used to work with MariaDB 10.11.4 without the error. It also works in MySQL 5.7.
Is this an intended change to protect the foreign key relation? This impacts our existing database migration scripts, which no longer work as before.



 Comments   
Comment by KrZipfel [ 2023-08-24 ]

Maybe it relates to MDEV-31086

Comment by Marko Mäkelä [ 2023-08-30 ]

Yes, this is an intentional change due to MDEV-31086.

Comment by Marko Mäkelä [ 2023-08-30 ]

MDEV-31987 had already been filed for this with a more specific title.

Comment by Marko Mäkelä [ 2023-08-30 ]

The ALTER TABLE statement in the Description would leave the database in an inconsistent state. Because MariaDB (or MySQL 5.7) cannot execute multiple ALTER TABLE statements atomically, the character set cannot be changed in both a parent and a child tables with just 2 ALTER TABLE statements. At least 3 statements will be needed to avoid committing an inconsistent schema, like I explained in MDEV-32003.

Generated at Thu Feb 08 10:28:04 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.