MySQL Bugs: #69701: ALTER TABLE ... IMPORT TABLESPACE does not check foreign keys

Bug #69701	ALTER TABLE ... IMPORT TABLESPACE does not check foreign keys
Submitted:	9 Jul 2013 16:59	Modified:	10 Jul 2013 19:37
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6	OS:	Any
Assigned to:	Bugs System	CPU Architecture:	Any
Tags:	alter, transportable tablespace

Description:
This manual page, http://dev.mysql.com/doc/refman/5.6/en/alter-table.html (as well as http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html, or any other it seems) does NOT warn users that foreign keys check does NOT happen when importing tablespace. 

So you may easily end up with inconsistent data while using this feature.

How to repeat:
Do something like this after having a properly saved b.* files (.ibd and .cnf) for the InnoDB table b:

...
mysql> drop table b;
Query OK, 0 rows affected (0.05 sec)

mysql> delete from a where id=1;
Query OK, 1 row affected (0.05 sec)

mysql> select * from a;
+----+------+
| id | c1   |
+----+------+
|  2 |    2 |
+----+------+
1 row in set (0.01 sec)

mysql> create table b(id int primary key, c1 int, foreign key fk(c1) references a(id)) engine=InnoDB;
Query OK, 0 rows affected (0.32 sec)

mysql> alter table b discard tablespace;
Query OK, 0 rows affected (0.10 sec)

-- here just copy files back, like this, in another shell:
-- openxs@debian7:~/dbs/5.6$ cp /tmp/b* data/test
-- openxs@debian7:~/dbs/5.6$ ls -l data/test/b*
-- -rw-r----- 1 openxs openxs    454 Jul  9 19:47 data/test/b.cfg
-- -rw-rw---- 1 openxs openxs   8582 Jul  9 19:47 data/test/b.frm
-- -rw-r----- 1 openxs openxs 114688 Jul  9 19:47 data/test/b.ibd

mysql> alter table b import tablespace;
Query OK, 0 rows affected (0.16 sec)

mysql> select * from a;
+----+------+
| id | c1   |
+----+------+
|  2 |    2 |
+----+------+
1 row in set (0.01 sec)

mysql> select * from b;
+----+------+
| id | c1   |
+----+------+
|  1 |    1 |
|  2 |    2 |
+----+------+
2 rows in set (0.00 sec)

mysql> show create table b\G
*************************** 1. row ***************************
       Table: b
Create Table: CREATE TABLE `b` (
  `id` int(11) NOT NULL,
  `c1` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk` (`c1`),
  CONSTRAINT `b_ibfk_1` FOREIGN KEY (`c1`) REFERENCES `a` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> insert into b values(3,3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`b`, CONSTRAINT `b_ibfk_1` FOREIGN KEY (`c1`) REFERENCES `a` (`id`)

So, FOREIGN KEY is there, it works for new data but no check for the data imported.

Suggested fix:
Probably we should inform users that foreign key constraint is NOT enforced for the data imported...

Indeed, it does not check FK.

The following pages have been updated to include a note indicating that ALTER TABLE ... IMPORT TABLESPACE does not enforce foreign key constraints on imported data. The updated pages will appear soon, in the next published documentation build.

http://dev.mysql.com/doc/refman/5.6/en/innodb-migration.html
http://dev.mysql.com/doc/refman/5.7/en/innodb-migration.html
http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html
http://dev.mysql.com/doc/refman/5.7/en/alter-table.html
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html
http://dev.mysql.com/doc/refman/5.5/en/alter-table.html
http://dev.mysql.com/doc/refman/5.6/en/alter-table.html
http://dev.mysql.com/doc/refman/5.7/en/alter-table.html

Thank you for the bug report.