MySQL Bugs: #77467: drop foreign key lead to inconsistent table structure on master and slave

Bug #77467	drop foreign key lead to inconsistent table structure on master and slave
Submitted:	24 Jun 2015 3:34	Modified:	14 May 2019 12:33
Reporter:	Fungo Wang (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S1 (Critical)
Version:	5.5.18, 5.5.44, 5.5.45	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	ALTER TABLE, foreign key

Description:
When executing ALTER TABLE reply DROP FOREIGN KEY reply_ibfk_2, DROP INDEX userId;, mysql server complains with error:

ERROR 1025 (HY000): Error on rename of './test/#sql-1a19_8' to './test/reply' (errno: 150).

The ALTER DDL fails, but when we check the `reply` table structure,  we'll see that the foreign key constraint is actually dropped,  and no binlog event recorded.

If there is a slave, and we do such DDL on master, slave will not see such table structure change on master. This will lead to inconsistent table structure .

The affected versions I have tested are 5.5.18 and 5.5.44; 5.6 series are not affected.

How to repeat:
// test case as bellow:

--source include/have_binlog_format_row.inc
--source include/master-slave.inc

# create three tables
CREATE TABLE user (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(100) NOT NULL,
  PRIMARY KEY (Id)
) ENGINE=InnoDB;

CREATE TABLE blog (
  id int(11) NOT NULL AUTO_INCREMENT,
  title varchar(1024) NOT NULL,
  content text NOT NULL,
  PRIMARY KEY (Id)
) ENGINE=InnoDB;

CREATE TABLE reply (
  id int(11) NOT NULL AUTO_INCREMENT,
  contents text NOT NULL,
  userId int(11) NOT NULL,
  blogId int(11) NOT NULL,
  PRIMARY KEY (Id),
  KEY userId (userId),
  KEY blogId (blogId),
  CONSTRAINT reply_ibfk_1 FOREIGN KEY (userId) REFERENCES user (Id),
  CONSTRAINT reply_ibfk_2 FOREIGN KEY (blogId) REFERENCES blog (Id)
) ENGINE=InnoDB;

# alter reply table, try to drop index.
# The evil begins...
--error ER_ERROR_ON_RENAME
Alter table reply
change blogId topicId int(11) NOT NULL,
drop index userId,
drop foreign key reply_ibfk_2;

# now, the structures of table `reply` are different on master and slave
SHOW CREATE TABLE reply;

connection slave;
SHOW CREATE TABLE reply;

# slave SQL thread failed after bellow sql clause
connection master;
insert into user values (1, 'fungo');
insert into reply values(1, 'bla bla bla', 1, 1);

--source include/rpl_end.inc

// please add these options to your case opt or cnf file
--log-bin  --binlog_format=ROW

correct version number typo

Hello Fungo Wang,

Thank you for the report and test case.
Observed this with 5.5.45.

Thanks,
Umesh

patch for 5.5.18

Attachment: patch_for_5518.diff (application/octet-stream, text), 11.92 KiB.

Posted by developer:
 
Hello!

The problem is actually repeatable in 5.7 branch (and probably in 5.6 as well)
with slightly adjusted test case (I have used 5.7.27-git):

CREATE TABLE user (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(100) NOT NULL,
PRIMARY KEY (Id)
) ENGINE=InnoDB;
CREATE TABLE blog (
id int(11) NOT NULL AUTO_INCREMENT,
title varchar(1024) NOT NULL,
content text NOT NULL,
PRIMARY KEY (Id)
) ENGINE=InnoDB;
CREATE TABLE reply (
id int(11) NOT NULL AUTO_INCREMENT,
contents text NOT NULL,
userId int(11) NOT NULL,
blogId int(11) NOT NULL,
PRIMARY KEY (Id),
KEY userId (userId),
KEY blogId (blogId),
CONSTRAINT reply_ibfk_1 FOREIGN KEY (userId) REFERENCES user (Id),
CONSTRAINT reply_ibfk_2 FOREIGN KEY (blogId) REFERENCES blog (Id)
) ENGINE=InnoDB;

ALTER TABLE reply
CHANGE blogId topicId int(11) NOT NULL,
DROP INDEX userId,
DROP FOREIGN KEY reply_ibfk_2, ALGORITHM=COPY;
# The above ALTER fails with "ERROR HY000: Error on rename of './test/#sql-629d_3' to './test/reply' (errno: 150 - Foreign key constraint is incorrectly formed)"

SHOW CREATE TABLE reply;
# Returns:
#
#	Table	Create Table
#	reply	CREATE TABLE `reply` (
#	  	`id` int(11) NOT NULL AUTO_INCREMENT,
#	  	`contents` text NOT NULL,
#		`userId` int(11) NOT NULL,
#		`blogId` int(11) NOT NULL,
#		PRIMARY KEY (`id`),
#		KEY `userId` (`userId`),
#		KEY `blogId` (`blogId`),
#		CONSTRAINT `reply_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
#		) ENGINE=InnoDB DEFAULT CHARSET=latin1
#
# Notice missing reply_ibfk_2 !

SHOW BINLOG EVENTS;
#Log_name	Pos	Event_type	Server_id	End_log_pos	Info
#0.000001	4	Format_desc	1	123	Server ver: 5.7.27-debug-log, Binlog ver: 4
#0.000001	123	Previous_gtids	1	154	
#0.000001	154	Anonymous_Gtid	1	219	SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
#0.000001	219	Query	1	409	use `test`; CREATE TABLE user (
#								id int(11) NOT NULL AUTO_INCREMENT,
#								name varchar(100) NOT NULL,
#								PRIMARY KEY (Id)
#								) ENGINE=InnoDB
#0.000001	409	Anonymous_Gtid	1	474	SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
#0.000001	474	Query	1	689	use `test`; CREATE TABLE blog (
#								id int(11) NOT NULL AUTO_INCREMENT,
#								title varchar(1024) NOT NULL,
#								content text NOT NULL,
#								PRIMARY KEY (Id)
#								) ENGINE=InnoDB
#0.000001	689	Anonymous_Gtid	1	754	SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
#0.000001	754	Query	1	1167	use `test`; CREATE TABLE reply (
#								id int(11) NOT NULL AUTO_INCREMENT,
#								contents text NOT NULL,
#								userId int(11) NOT NULL,
#								blogId int(11) NOT NULL,
#								PRIMARY KEY (Id),
#								KEY userId (userId),
#								KEY blogId (blogId),
#								CONSTRAINT reply_ibfk_1 FOREIGN KEY (userId) REFERENCES user (Id),
#								CONSTRAINT reply_ibfk_2 FOREIGN KEY (blogId) REFERENCES blog (Id)
#								) ENGINE=InnoDB
#
# Notice that ALTER TABLE is missing from binary log!

Of course the fact that it is repeatable in 5.7 only if ALTER TABLE
is executed using COPY algorithm decreases impact.

Posted by developer:
 
However, the problem is not repeatable on modern 8.0 (I've used 8.0.17-git):

CREATE TABLE user (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(100) NOT NULL,
PRIMARY KEY (Id)
) ENGINE=InnoDB;
CREATE TABLE blog (
id int(11) NOT NULL AUTO_INCREMENT,
title varchar(1024) NOT NULL,
content text NOT NULL,
PRIMARY KEY (Id)
) ENGINE=InnoDB;
CREATE TABLE reply (
id int(11) NOT NULL AUTO_INCREMENT,
contents text NOT NULL,
userId int(11) NOT NULL,
blogId int(11) NOT NULL,
PRIMARY KEY (Id),
KEY userId (userId),
KEY blogId (blogId),
CONSTRAINT reply_ibfk_1 FOREIGN KEY (userId) REFERENCES user (Id),
CONSTRAINT reply_ibfk_2 FOREIGN KEY (blogId) REFERENCES blog (Id)
) ENGINE=InnoDB;
ALTER TABLE reply
CHANGE blogId topicId int(11) NOT NULL,
DROP INDEX userId,
DROP FOREIGN KEY reply_ibfk_2, ALGORITHM=COPY;
# The above ALTER Tfails with ERROR HY000: Cannot drop index 'userId': needed in a foreign key constraint
SHOW CREATE TABLE reply;
#	Table	Create Table
#	reply	CREATE TABLE `reply` (
#		  `id` int(11) NOT NULL AUTO_INCREMENT,
#		  `contents` text NOT NULL,
#		  `userId` int(11) NOT NULL,
#		  `blogId` int(11) NOT NULL,
#		  PRIMARY KEY (`id`),
#		  KEY `userId` (`userId`),
#		  KEY `blogId` (`blogId`),
#		  CONSTRAINT `reply_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`),
#		  CONSTRAINT `reply_ibfk_2` FOREIGN KEY (`blogId`) REFERENCES `blog` (`id`)
#		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

# Notice that both foreign keys are still there!

Posted by developer:
 
Hello!

As it was mentioned above the problem is not repeatable in recent 8.0.
This is because it was fixed in 8.0.14, by one of patches for bug25722927
"NEWDD FK: ALTER TABLE CHANGE COLUMN TYPE SHOULD CHECK FK CONSTRAINT".
This patch added check whether it is safe to remove supporting index
for foreign key to SQL-layer before even involving SE. So the
problematic ALTER TABLE now fails early, with better error message
and without side-effects. (Moreover, the problem was probably not
repeatable starting from 8.0.3 which made ALTER TABLE on InnoDB
tables atomic.)

I am closing this bug as fixed in 8.0.14. Moving it to Documenting
state to let Documentation team to decide if anything needs to be
added to Release Notes.

Posted by developer:
 
I've added the bug numbers to the existing changelog entry for Bug #25722927:

        A check that ensures compatibility of referencing and referenced
        column types in a foreign key definition was moved from the
        storage engine layer to the SQL layer. In addition, a better
        error message is produced when columns are not compatible.