Bug #90865 Cannot rename foreign key field and datatype in single alter table

Submitted:	15 May 2018 1:48	Modified:	15 May 2018 17:20
Reporter:	Ryan Brothers	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I am attempting to alter an existing table, and in the same query:

- rename a field that is used in a foreign key
- change a datatype

I am running 5.7, but also tried it in 8.0.  Is it possible to do the below ALTER TABLE in a single query?

How to repeat:
mysql> USE test;
Database changed

mysql> CREATE TABLE a (a_id int PRIMARY KEY);
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE b (b_id int PRIMARY KEY, a_id int, c enum('1', '2'), CONSTRAINT FOREIGN KEY (a_id) REFERENCES a (a_id));
Query OK, 0 rows affected (0.05 sec)

mysql> ALTER TABLE b CHANGE a_id a2_id int, MODIFY c enum('3', '1', '2');
ERROR 1846 (0A000): ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE.

mysql> ALTER TABLE b CHANGE a_id a2_id int, MODIFY c enum('3', '1', '2'), ALGORITHM=INPLACE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

mysql> ALTER TABLE b CHANGE a_id a2_id int, MODIFY c enum('3', '1', '2'), ALGORITHM=COPY;
ERROR 1846 (0A000): ALGORITHM=COPY is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE.

Thank you for the bug report. Duplicate of bug https://bugs.mysql.com/bug.php?id=77097.

Page generated in 0.049 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.