MySQL Bugs: #94816: Alter table results in foreign key error that appears to drop referenced table

Bug #94816	Alter table results in foreign key error that appears to drop referenced table
Submitted:	28 Mar 2019 14:25	Modified:	15 Apr 2019 14:03
Reporter:	Z Mason	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.7.25	OS:	Any (Windows and GCP CloudSQL)
Assigned to:		CPU Architecture:	Any

Description:
Encountered an issue where a table referenced by a pair of fields had one of the component fields moved from a primary key to a unique index and adding a generated field to that table results in a foreign key error (error on rename of...foreign key is incorrectly formed) and the table seemingly being dropped, though the indexes appear to remain as running the sample a second time results in "Can't write; duplicate key in table 'src'".

How to repeat:
create table a (
	id int unsigned not null,
	
	constraint PK_a primary key (id)
) engine=InnoDB;
create table b (
	id int unsigned not null,
	
	constraint PK_b primary key (id)
) engine=InnoDB;
create table src (
	a_id int unsigned not null,
	b_id int unsigned not null,
	
	constraint PK_src primary key (a_id),
	constraint FK_src_a foreign key (a_id) references a (id),
	constraint FK_src_b foreign key (b_id) references b (id)
) engine=InnoDB;
create table ref (
	a_id int unsigned not null,
	b_id int unsigned not null,
	
	constraint FK_ref_a_b foreign key (b_id,a_id) references src (b_id,a_id)
) engine=InnoDB;

alter table src
	add constraint UQ_src_a unique (a_id),
	drop primary key,
	add column id int unsigned not null,
	add constraint PK_src primary key (id)
;

alter table src
	add column other int unsigned generated always as (1) stored null
;

Suggested fix:
My thought is that an alter should either be successful or result in no state transformation.

Hi,

Thank you for your bug report.

However, I can't repeat this behaviour.

When I create table `ref`, I get the following error:

ERROR 1822 (HY000) at line 36: Failed to add the foreign key constraint. Missing index for constraint 'FK_ref_a_b' in the referenced table 'src';

What version are you using and do you have any configuration options that could be relevant? I have a few relationships defined in this same way and haven't seen that message. I ran this exact script and everything appears fine until the last statement.

I was actually originally surprised that it had worked since the pair is not specifically indexed, but assumed that the engine used the index for the foreign key combined with the primary key. Having it fail as you described would be reasonable, but that is consistently not the behavior I have seen.

Hi,

The message that I have sent you is from 8.0.15.

With 5.7.25 I get the following warning and error:

"
Warning	1088	InnoDB: Could not add foreign key constraints.
ERROR 1025 (HY000) at line 57: Error on rename of './test/#sql-1313_2' to './test/src' (errno: 150 - Foreign key constraint is incorrectly formed)
"

Regarding my settings, these are mostly performance tuning settings and do not influence foreign keys, SQL mode or anything other setting that might influence this behaviour. And I do not wish to share my finely tuned configuration, for many reasons.

Yes, that is the error I have seen. The issue is that following that error, the `src` table appears to no longer exists. Are you seeing that behavior?

Hi,

Yes, I have seen the same behaviour.

But, it has been fixed in MySQL 8.0. This fix can not be ported back to 5.7, since it required a total change of the infrastructure in 8.0, to fix this behaviour, as well as many others.

Hence, plan the upgrade to 8.0.

Got it. Thanks for the confirmation. I would very much like to move to 8 but without CloudSQL support it's tough for us.

Hi,

You are welcome. I did not know what "Cloud SQL" is, so I have searched for it.

I assure you that there are, literally, millions of 8.0 installations out there who are using it without any cloud help. However, that is your decision ...........

Totally agree and understand. Definitely a business decision, not a technical one.

Changing to verified for 5.7.

Hello!

Here is somewhat simplified test case for this issue which allows
to repeat problem in 5.7.27-git:

create table src (
a_id int unsigned not null,
b_id int unsigned not null,
primary key (a_id),
key (b_id)
) engine=InnoDB;
create table ref (
a_id int unsigned not null,
b_id int unsigned not null,
constraint FK_ref_a_b foreign key (b_id,a_id) references src (b_id,a_id)
) engine=InnoDB;
alter table src
drop primary key,
add column id int unsigned not null,
add constraint PK_src primary key (id);
# The above emits the following warnings:
# Warning	1088	InnoDB: Could not add foreign key constraints.

alter table src
add column other int unsigned generated always as (1) stored null;
# The above fails with ERROR HY000: Error on rename of './test/#sql-70c9_3' to './test/src' (errno: 150 - Foreign key constraint is incorrectly formed)
# Also writes "[ERROR] InnoDB: In ALTER TABLE `test`.`src` has or is referenced in foreign key constraints which are not compatible with the new table definition." to error log.

select * from src;
# Fails with "ERROR 42S02: Table 'test.src' doesn't exist" as previous ALTER TABLE leaves table under internal #sql... name

Indeed, the same test case works differently in 8.0.17-git (unlike Sinisa,
I am using version after bug #28480149 "UPGRADE FAIL: FAILED TO ADD THE
FOREIGN KEY CONSTRAINT. MISSING INDEX FOR CONSTR"):

create table src (
a_id int unsigned not null,
b_id int unsigned not null,
primary key (a_id),
key (b_id)
) engine=InnoDB;
create table ref (
a_id int unsigned not null,
b_id int unsigned not null,
constraint FK_ref_a_b foreign key (b_id,a_id) references src (b_id,a_id)
) engine=InnoDB;
alter table src
drop primary key,
add column id int unsigned not null,
add constraint PK_src primary key (id);
# The above fails with "ERROR HY000: Cannot drop index 'b_id': needed in a foreign key constraint"
# which is expected and correct error.

# Both the following ALTER TABLE and SELECT succeed.
alter table src
add column other int unsigned generated always as (1) stored null;
select * from src;
# a_id	b_id	other

So, the problem is solved in 8.0 thanks to introduction of support for atomic DDL
and fix for bug#27506922 "DROPPING OF PARENT KEY FOR FOREIGN KEY IS ALLOWED".

However, since
1) we are talking about situation which can be considered by users as a table
   loss (recovering table from the temporary #sql.. can be non-trivial in case
   of InnoDB),
2) unlike similar bug #26746175/#87612 "DROP INDEX ON A FOREIGN KEY COLUMN
   LEADS TO MISSING TABLE" the problem is still repeatable in recent 5.7
   versions.

I think it is better to verify this report as a valid bug against 5.7 and
do some additional investigation of it (why there is difference to #26746175
is especially interesting). But, of course, it might turn out that it is
infeasible to fix this issue in 5.7 there in the end...

Taking into account the above I am changing the status of this bug report
to Verified.

This is verified for 5.7 only.