| 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
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.