ALTER TABLE does not allow to change NULL/NOT NULL if foreign key exists

Bug #1470677 reported by Sveta Smirnova on 2015-07-02
24
This bug affects 5 people
Affects Status Importance Assigned to Milestone
MySQL 5.6
Unknown
Unknown
Percona Server moved to https://jira.percona.com/projects/PS
Fix Released
Medium
Laurynas Biveinis
5.6
Fix Released
Medium
Laurynas Biveinis

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

PostgreSQL allows this:

postgres=# create table instances(
postgres(# uuid varchar(36) default null,
postgres(# unique(uuid));
CREATE TABLE

postgres=# create table block_device_mapping(
postgres(# instance_uuid varchar(36) DEFAULT NULL,
postgres(# foreign key(instance_uuid) references instances(uuid));
CREATE TABLE

postgres=# ALTER TABLE instances ALTER COLUMN uuid SET NOT NULL;
ALTER TABLE

Changed in percona-server:
status: New → Confirmed
tags: added: upstream
Download full text (3.2 KiB)

Just to clarify, we¹ve not seen this problem with all environments. We¹ve
only had this issue with this specific environment with this specific
database with this set of data files in /var/lib/mysql. If we rebuild the
database with mysqldump then the issue goes away. If this was the only
environment we had to do this in, we might do just that, but we¹ve still
got our staging and prod environments to deploy this change to. Our
primary concern is that we cannot rebuild the databases in staging and
prod using mysqldump and we can¹t predict if this problem will occur there
since it doesn¹t seem predictable.

On 7/2/15, 00:17, "<email address hidden> on behalf of Valerii Kravchuk"
<<email address hidden> on behalf of <email address hidden>> wrote:

>** Tags added: upstream
>
>--
>You received this bug notification because you are subscribed to the bug
>report.
>https://bugs.launchpad.net/bugs/1470677
>
>Title:
> ALTER TABLE does not allow to change NULL/NOT NULL if foreign key
> exists
>
>Status in MySQL 5.6:
> Unknown
>Status in Percona Server with XtraDB:
> Confirmed
>Status in Percona Server 5.6 series:
> Confirmed
>
>Bug 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.
>
> PostgreSQL allows this:
>
> postgres=# create table instances(
> postgres(# uuid varchar(36) default null,
> postgres(# unique(uuid));
> CREATE TABLE
>
> postgres=# create table block_device_mapping(
> postgres(# instance_uuid varchar(36) DEFAULT NULL,
> postgres(# foreign key(instance_uuid) references instances(uuid));
> CREATE TABLE
>
> postgres=# ALTER TABLE instances ALTER COLUMN uuid SET NOT NULL;
> ALTER TABLE
>
>To manage notifications about this bug go to:
>https://bugs.launchpad.net/mysql-5.6/+bug/1470677/+subscriptions

This E-mail and any of its attachments may contain Time Warner Cable proprietary information, which is privileged, confidential, or subject to copyright belonging to Time Warner Cable. This E-mail is intended solely for the use of the individual or entity to which it is addressed. If you are not the intended recipient of this E-mail, you are hereby notified that any dissemination, distribution, copying, or action taken in relation to the contents of and attachments to this E-mail is strictly prohibited and may be unlawful. If you have received this E-mail in error, please notify the sender immediately and permanently delete the o...

Read more...

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1635

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.