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

[9 Jul 2013 16:59] Valeriy Kravchuk
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...
[9 Jul 2013 18:16] Sinisa Milivojevic
Indeed, it does not check FK.
[10 Jul 2013 19:37] Bugs System
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.