Bug #70979 load of mysqldump script may fail with error 1215 if dest already has tables
Submitted: 22 Nov 2013 10:55
Reporter: Andrii Nikitin Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:all OS:Any
Assigned to: CPU Architecture:Any

[22 Nov 2013 10:55] Andrii Nikitin
Description:
load of mysqldump script may fail with error 1215 if destination database has existing tables with different structure.

How to repeat:
1.
create database test_fk;
use test_fk;
set foreign_key_checks=0;
create table a(i int primary key, foreign key (i) references b(i)) engine=innodb;
create table b(i int primary key, foreign key (i) references a(i)) engine=innodb;

2. create dump of test_fk:

mysqldump test_fk > test_fk.sql

3. make some changes in test_fk:

drop table a;
alter table b modify i varchar(1);

4. try to restore from the backup:

mysql test_fk < test_fk.sql

5. Error is shown:
ERROR 1215 (HY000) at line 25: Cannot add foreign key constraint

Suggested fix:
not sure, either document or make sure that script generated by mysqldump doesn't fail with error 1215