| Bug #93838 | Not able to ALTER column with foreign key without some SQL_MODES | ||
|---|---|---|---|
| Submitted: | 7 Jan 2019 15:07 | Modified: | 19 May 2019 18:09 |
| Reporter: | Przemyslaw Malkowski | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 5.7.23, 8.0.13 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[7 Jan 2019 15:42]
MySQL Verification Team
Thank you for the bug report. Please check the below note from the Manual:
C:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 -p --prompt="mysql 5.7 > "
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.26-log Source distribution BUILD: 2018-DEC-12
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.7 > use test
Database changed
mysql 5.7 > create table a (id int primary key, name text ) ; create table b (id int primary key, fid int default null, foreign key (fid) references a(id));
Query OK, 0 rows affected (0.04 sec)
Query OK, 0 rows affected (0.04 sec)
mysql 5.7 > show create table b\G
*************************** 1. row ***************************
Table: b
Create Table: 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=latin1
1 row in set (0.00 sec)
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
foreign_key_checks
Note
......
Dropping an index required by a foreign key constraint is not permitted, even with foreign_key_checks=0. The foreign key constraint must be removed before dropping the index.
[7 Jan 2019 15:44]
MySQL Verification Team
C:\dbs>c:\dbs\5.7\bin\mysql -uroot --port=3570 -p --prompt="mysql 5.7 > "
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.26-log Source distribution BUILD: 2018-DEC-12
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.7 > use test
Database changed
mysql 5.7 > create table a (id int primary key, name text ) ; create table b (id int primary key, fid int default null, foreign key (fid) references a(id));
Query OK, 0 rows affected (0.04 sec)
Query OK, 0 rows affected (0.04 sec)
mysql 5.7 > show create table b;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| b | 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=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql 5.7 > show create table b\G
*************************** 1. row ***************************
Table: b
Create Table: 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=latin1
1 row in set (0.00 sec)
mysql 5.7 > alter table b modify column fid int not null ;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql 5.7 >
[7 Jan 2019 15:47]
MySQL Verification Team
C:\dbs>c:\dbs\8.0\bin\mysql -uroot --port=3580 -p --prompt="mysql 8.0 > " --default-character-set=utf8mb4 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.15 Source distribution BUILD: 2018-DEC-11 Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 8.0 > use test Database changed mysql 8.0 > create table a (id int primary key, name text ) ; create table b (id int primary key, fid int default null, foreign key (fid) references a(id)); Query OK, 0 rows affected (0.04 sec) Query OK, 0 rows affected (0.04 sec) mysql 8.0 > show create table b; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | b | 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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql 8.0 > alter table b modify column fid int not null ; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql 8.0 >
[7 Jan 2019 15:51]
MySQL Verification Team
Could you please try version 8.0.13: C:\dbs>8.0\bin\mysql -uroot -p Enter password: ********** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.13 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use test Database changed mysql> create table a (id int primary key, name text ) ; create table b (id int primary key, fid int default null, foreign key (fid) references a(id)); Query OK, 0 rows affected (0.10 sec) Query OK, 0 rows affected (0.07 sec) mysql> Query OK, 0 rows affected (0.10 sec) mysql> alter table b modify column fid int not null ; Query OK, 0 rows affected (0.20 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql>
[7 Jan 2019 17:10]
MySQL Verification Team
Thank you for the bug report. C:\tmp\mysql-5.7.24>bin\mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.24 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database test; Query OK, 1 row affected (0.00 sec) mysql> use test Database changed mysql> create table a (id int primary key, name text ) ; create table b (id int primary key, fid int default null, foreign key (fid) references a(id)); Query OK, 0 rows affected (0.04 sec) Query OK, 0 rows affected (0.03 sec) mysql> set sql_mode=NO_ENGINE_SUBSTITUTION; Query OK, 0 rows affected, 1 warning (0.07 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' mysql> exit Bye C:\tmp\mysql-5.7.24>cd\ C:\>cd dbs C:\dbs>80c C:\dbs>c:\dbs\8.0\bin\mysql -uroot --port=3580 -p --prompt="mysql 8.0 > " --default-character-set=utf8mb4 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.15 Source distribution BUILD: 2018-DEC-11 Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 8.0 > create database t; Query OK, 1 row affected (0.02 sec) mysql 8.0 > use t Database changed mysql 8.0 > create table a (id int primary key, name text ) ; create table b (id int primary key, fid int default null, foreign key (fid) references a(id)); Query OK, 0 rows affected (0.05 sec) Query OK, 0 rows affected (0.04 sec) mysql 8.0 > set sql_mode=NO_ENGINE_SUBSTITUTION; Query OK, 0 rows affected (0.00 sec) mysql 8.0 > 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' mysql 8.0 >
[7 Jan 2019 17:19]
Przemyslaw Malkowski
Miguel,
Regarding doc note: "Dropping an index required by a foreign key constraint is not permitted, even with foreign_key_checks=0. The foreign key constraint must be removed before dropping the index."
I am not dropping any index. I am simply changing a minor column metadata property.
The question is why it works in some SQL_MODE while it does not in other.
Btw, same issue in 8.0.13:
master [localhost:20314] {msandbox} (test) > select @@version,@@version_comment,@@sql_mode;
+-----------+------------------------------+------------+
| @@version | @@version_comment | @@sql_mode |
+-----------+------------------------------+------------+
| 8.0.13 | MySQL Community Server - GPL | |
+-----------+------------------------------+------------+
1 row in set (0.00 sec)
master [localhost:20314] {msandbox} (test) > show create table a\G
*************************** 1. row ***************************
Table: a
Create Table: CREATE TABLE `a` (
`id` int(11) NOT NULL,
`name` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)
master [localhost:20314] {msandbox} (test) > show create table b\G
*************************** 1. row ***************************
Table: b
Create Table: 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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
master [localhost:20314] {msandbox} (test) > 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'
---------------------------
master [localhost:20314] {msandbox} (test) > SET SQL_MODE=STRICT_TRANS_TABLES;
Query OK, 0 rows affected, 1 warning (0.01 sec)
master [localhost:20314] {msandbox} (test) > ALTER TABLE b MODIFY COLUMN fid INT NOT NULL;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
[7 Jan 2019 18:39]
MySQL Verification Team
Indeed sorry I was confused and when re-read your instructions I got it, so now I verified it. Thanks.
[17 Apr 2019 19:09]
Dmitry Lenev
Posted by developer: Hello! The code works as designed in this case so this is not a code bug. Or documentation says that "13.1.9 ALTER TABLE Syntax" page of our documentation says (see https://dev.mysql.com/doc/refman/8.0/en/alter-table.html): "The server prohibits changes to foreign key columns that have the potential to cause loss of referential integrity. It also prohibits changes to the data type of such columns that may be unsafe. For example, changing VARCHAR(20) to VARCHAR(30) is permitted, but changing it to VARCHAR(1024) is not because that alters the number of length bytes required to store individual values. A workaround is to use ALTER TABLE ... DROP FOREIGN KEY before changing the column definition and ALTER TABLE ... ADD FOREIGN KEY afterward." The above applies to making nullable column non-nullable in non-strict mode because such operation will convert NULL values to some default non-null value, for which corresponding parent key value might be missing. At this point our code can't check this when such conversion occurs, so we have to prohibit such operations pessimistically. However, making nullable column non-nullable is OK in strict mode, since in such mode we don't convert NULLs to non-null values and return an error instead. So there is no chance that orphan child will be introduced by such ALTER TABLE. Let me demonstrate this with example (I have used 8.0 branch): CREATE TABLE parent (pk INT PRIMARY KEY); CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent (pk)); INSERT INTO child VALUES (NULL); SET FOREIGN_KEY_CHECKS=1; SET SQL_MODE=''; # Pessimistically fails with ERROR HY000: Cannot change column 'fk': used in a foreign key constraint 'child_ibfk_1' ALTER TABLE child MODIFY fk INT NOT NULL; SET SQL_MODE='traditional'; # Starts changing table, detects NULL value and fails with "ERROR 22004: Invalid use of NULL value" ALTER TABLE child MODIFY fk INT NOT NULL; SET SQL_MODE=''; SET FOREIGN_KEY_CHECKS=0; # Will break referential integrity and create orphan as below SELECT confirms. ALTER TABLE child MODIFY fk INT NOT NULL; # Warnings: # Warning 1265 Data truncated for column 'fk' at row 1 SELECT * FROM child; # fk # 0 Having said above, I agree that this behavior is non-intuitive and our documentation could describe it more clearly/mention it explicitly. So I am changing category of this report to Documentation bug.
[19 May 2019 18:09]
Daniel Price
Posted by developer: The following information was added regarding changes to foreign key columns: "Changing a NULL column to NOT NULL in non-strict mode is prohibited to prevent converting NULL values to default non-NULL values, for which there are no corresponding values in the referenced table. The operation is permitted in strict mode, but an error is returned if any such conversion is required." Changes should appear online soon. https://dev.mysql.com/doc/refman/8.0/en/alter-table.html#alter-table-foreign-key Thank you for the bug report.
Description: It is not possible to modify column with foreign key in less strict SQL_MODEs. I don't see a clear reason for that. How to repeat: mysql [localhost] {msandbox} (test) > create table a (id int primary key, name text ) ; create table b (id int primary key, fid int default null, foreign key (fid) references a(id)); Query OK, 0 rows affected (0.10 sec) Query OK, 0 rows affected (0.07 sec) mysql [localhost] {msandbox} (test) > set sql_mode=NO_ENGINE_SUBSTITUTION; Query OK, 0 rows affected (0.00 sec) mysql [localhost] {msandbox} (test) > 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' mysql [localhost] {msandbox} (test) > set sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql [localhost] {msandbox} (test) > 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' mysql [localhost] {msandbox} (test) > show create table a\G *************************** 1. row *************************** Table: a Create Table: CREATE TABLE `a` ( `id` int(11) NOT NULL, `name` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.01 sec) mysql [localhost] {msandbox} (test) > show create table b\G *************************** 1. row *************************** Table: b Create Table: 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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.01 sec) mysql [localhost] {msandbox} (test) > set sql_mode=STRICT_TRANS_TABLES; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql [localhost] {msandbox} (test) > alter table b modify column fid int not null ; Query OK, 0 rows affected (0.15 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql [localhost] {msandbox} (test) > show create table b\G *************************** 1. row *************************** Table: b Create Table: CREATE TABLE `b` ( `id` int(11) NOT NULL, `fid` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `fid` (`fid`), CONSTRAINT `b_ibfk_1` FOREIGN KEY (`fid`) REFERENCES `a` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) Suggested fix: I think there is no reason for not allowing to change the nullable column to not null (to the same as referenced column has) in any sql mode. So, fix the issue or explain why it happens.