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
• Status: Verified
• Category: MySQL Server: mysqldump Command-line Client
• Severity: S3 (Non-critical)
• Version: all
• OS: Any
• 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