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

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

https://github.com/percona/percona-server/pull/115/

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