Description:
trying to drop foreign key that does not exist throw an exception after having copied the table. It can be very long on a big table
How to repeat:
create table f(id int primary key);
insert into f values(1),(2),(3);
create table c(id int,c varchar(40000),key (id), foreign key (id) references f(id) on update cascade);
insert into c values(1,repeat('C',39900));
insert into c select * from c;
insert into c select * from c;
insert into c select * from c;
insert into c select * from c;
insert into c select * from c;
insert into c select * from c;
insert into c select * from c;
insert into c select * from c;
insert into c select * from c;
insert into c select * from c;
insert into c select * from c;
insert into c select * from c;
insert into c select * from c;
ALTER TABLE c DROP FOREIGN KEY nothing;
ERROR 1025 (HY000): Error on rename of './jrep_data_tst/c' to './jrep_data_tst/#sql2-1ae8-d' (errno: 152)
if I do a show processlist during the ALTER TABLE I see
mysql> show processlist\G
*************************** 1. row ***************************
Id: 13
User: root
Host: localhost
db: jrep_data_tst
Command: Query
Time: 25
State: copy to tmp table
Info: ALTER TABLE c DROP FOREIGN KEY nothing
Suggested fix:
check if the foreign key exists before copying the table
Description: trying to drop foreign key that does not exist throw an exception after having copied the table. It can be very long on a big table How to repeat: create table f(id int primary key); insert into f values(1),(2),(3); create table c(id int,c varchar(40000),key (id), foreign key (id) references f(id) on update cascade); insert into c values(1,repeat('C',39900)); insert into c select * from c; insert into c select * from c; insert into c select * from c; insert into c select * from c; insert into c select * from c; insert into c select * from c; insert into c select * from c; insert into c select * from c; insert into c select * from c; insert into c select * from c; insert into c select * from c; insert into c select * from c; insert into c select * from c; ALTER TABLE c DROP FOREIGN KEY nothing; ERROR 1025 (HY000): Error on rename of './jrep_data_tst/c' to './jrep_data_tst/#sql2-1ae8-d' (errno: 152) if I do a show processlist during the ALTER TABLE I see mysql> show processlist\G *************************** 1. row *************************** Id: 13 User: root Host: localhost db: jrep_data_tst Command: Query Time: 25 State: copy to tmp table Info: ALTER TABLE c DROP FOREIGN KEY nothing Suggested fix: check if the foreign key exists before copying the table