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

[24 Jun 2015 3:34] Fungo Wang
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
[24 Jun 2015 3:38] Fungo Wang
correct version number typo
[24 Jun 2015 5:28] MySQL Verification Team
Hello Fungo Wang,

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

Thanks,
Umesh
[20 Jul 2015 2:05] zhang simon
patch for 5.5.18

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

[7 May 2019 11:07] Dmitry Lenev
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.
[7 May 2019 11:44] Dmitry Lenev
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!
[13 May 2019 14:42] Dmitry Lenev
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.
[14 May 2019 12:33] Margaret Fisher
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.