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:
None 
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:16] Sveta Smirnova
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.
[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