Bug #77591 ALTER TABLE does not allow to change NULL/NOT NULL if foreign key exists
| Submitted: | 2 Jul 2015 0:16 | Modified: | 2 Jul 2015 6:03 |
|---|---|---|---|
| Reporter: | Sveta Smirnova (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.6.24, 5.6.27 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Contribution, regression | ||
[2 Jul 2015 0:21]
Sveta Smirnova
See also https://bugs.launchpad.net/percona-server/+bug/1470677
[2 Jul 2015 6:03]
MySQL Verification Team
Hello Sveta, Thank you for the report and test case. Verified as described on 5.6.27. Thanks, Umesh
[2 Jul 2015 6:03]
MySQL Verification Team
// Confirmed that latest 5.1/5.5 builds are not affected
## 5.1.76
mysql> show variables like '%version%';
+-------------------------+---------------------+
| Variable_name | Value |
+-------------------------+---------------------+
| protocol_version | 10 |
| version | 5.1.76 |
| version_comment | Source distribution |
| version_compile_machine | x86_64 |
| version_compile_os | unknown-linux-gnu |
+-------------------------+---------------------+
5 rows in set (0.00 sec)
mysql> create table instances(
-> uuid varchar(36) default null,
-> unique(uuid)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql> create table block_device_mapping(
-> instance_uuid varchar(36) DEFAULT NULL,
-> foreign key(instance_uuid) references instances(uuid)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE instances CHANGE COLUMN uuid uuid VARCHAR(36) NOT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table instances\G
*************************** 1. row ***************************
Table: instances
Create Table: CREATE TABLE `instances` (
`uuid` varchar(36) NOT NULL,
UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
// 5.5.46
mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.5.46 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.5.46 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | linux2.6 |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)
mysql> use test;
Database changed
mysql> create table instances(uuid varchar(36) default null,unique(uuid)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
mysql> create table block_device_mapping(instance_uuid varchar(36) DEFAULT NULL,foreign key(instance_uuid) references instances(uuid)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE instances CHANGE COLUMN uuid uuid VARCHAR(36) NOT NULL;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table instances\G
*************************** 1. row ***************************
Table: instances
Create Table: CREATE TABLE `instances` (
`uuid` varchar(36) NOT NULL,
UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
[2 Jul 2015 6:04]
MySQL Verification Team
// 5.6.27 - affected
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------+
| innodb_version | 5.6.27 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.27-enterprise-commercial-advanced |
| version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | linux-glibc2.5 |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)
mysql> use test;
Database changed
mysql> create table instances(uuid varchar(36) default null,unique(uuid)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
mysql> create table block_device_mapping(instance_uuid varchar(36) DEFAULT NULL,foreign key(instance_uuid) references instances(uuid)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER TABLE instances CHANGE COLUMN uuid uuid VARCHAR(36) NOT NULL;
ERROR 1833 (HY000): Cannot change column 'uuid': used in a foreign key constraint 'block_device_mapping_ibfk_1' of table 'test.block_device_mapping'
mysql> show create table instances\G
*************************** 1. row ***************************
Table: instances
Create Table: CREATE TABLE `instances` (
`uuid` varchar(36) DEFAULT NULL,
UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
[2 Jul 2015 6:31]
MySQL Verification Team
// 5.7.8
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------+
| innodb_version | 5.7.8 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.7.8-rc-enterprise-commercial-advanced |
| version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+---------------------------------------------------------+
7 rows in set, 1 warning (0.00 sec)
mysql> use test;
Database changed
mysql> create table instances(uuid varchar(36) default null,unique(uuid)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> create table block_device_mapping(instance_uuid varchar(36) DEFAULT NULL,foreign key(instance_uuid) references instances(uuid)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE instances CHANGE COLUMN uuid uuid VARCHAR(36) NOT NULL;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------------------------------+
| Warning | 1082 | InnoDB: Table ./test/instances has no primary key in InnoDB data dictionary, but has one in MySQL! |
+---------+------+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table instances\G
*************************** 1. row ***************************
Table: instances
Create Table: CREATE TABLE `instances` (
`uuid` varchar(36) NOT NULL,
UNIQUE KEY `uuid` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
[2 Jun 2017 2:05]
Laurynas Biveinis
Bug 77591 fix for 8.0.1 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: bug77591-8.0.1.patch (application/octet-stream, text), 8.37 KiB.
[5 Aug 2017 7:20]
Laurynas Biveinis
Bug 77591 fix for 8.0.2 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: bug77591-8.0.2.patch (application/octet-stream, text), 8.37 KiB.
[2 Feb 2018 7:30]
Laurynas Biveinis
Bug 77591 fix for 8.0.4 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: bug77591-8.0.4.patch (application/octet-stream, text), 8.37 KiB.
[13 Jun 2018 12:29]
Laurynas Biveinis
Bug 77591 fix for 8.0.11 (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: bug77591-8.0.11.patch (application/octet-stream, text), 8.99 KiB.
[14 Jun 2018 5:06]
MySQL Verification Team
Thank you for the contributions! Regards, Umesh
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.066 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: ALTER TABLE does not allow to change null-ability of the column if foreign key exists. Bug is not repeatable with version 5.5. How to repeat: mysql> create table instances( -> uuid varchar(36) default null, -> unique(uuid)) engine=innodb; Query OK, 0 rows affected (0.22 sec) mysql> create table block_device_mapping( -> instance_uuid varchar(36) DEFAULT NULL, -> foreign key(instance_uuid) references instances(uuid)) engine=innodb; Query OK, 0 rows affected (0.20 sec) mysql> ALTER TABLE instances CHANGE COLUMN uuid uuid VARCHAR(36) NOT NULL; ERROR 1833 (HY000): Cannot change column 'uuid': used in a foreign key constraint 'block_device_mapping_ibfk_1' of table 'test.block_device_mapping' Suggested fix: Allow such changes which does not affect existing data.