Affects Version/s: 5.7.25-31.35
Fix Version/s: None
PXC 5.7.25 (inside a docker container)
In PXC 5.7.25, ALTER TABLE query is failing with an error when trying to modify the column's data type if the column is part of the foreign key constraint. Below example test case was captured from a 2 node PXC 5.7.25 test cluster.
Steps to reproduce:
1. Creating parent table product_catalog. catalog_id is defined as INT(11).
2. Creating the child table product_details. product_catalog_id refers to catalog_id in the parent table above.
I want to modify the datatype of these two columns from INT(11) to BIGINT. ERROR 1832 received when the ALTER is issued on the child table.
ERROR 1833 received when the ALTER is issued on the child table.
As per the bug reported in PS-4770https://jira.percona.com/browse/PS-4770 the behavior is different in PS and the ALTER is working in some SQL_MODEs(STRICT_TRANS_TABLES or STRICT_ALL_TABLES or TRADITIONAL).
I see that the ALTERs are failing even in those SQL_MODES in PXC 5.7.25.
The only workaround I could figure out so far is to drop the Foreign key constraint on the child table and perform the ALTER on both the tables and then re inforce the Foreign key constraint.