Description:
Currently Blackhole cannot have foreign keys. It makes hard to create server in the middle of replication setup which is common for spreading read load from single master, e.g. (Master which uses real engine) -> (Slave which uses Blackhole) -> (Dozens of other slaves which use real engine)
But since Blackhole does not store anything having it supporting foreign keys should not harm.
How to repeat:
sveta@Thinkie:~/mysql_packages/mysql-5.1.72-linux-x86_64-glibc23/mysql-test$ mysqlmtr test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.72-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE `foos` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.13 sec)
mysql>
mysql> CREATE TABLE `foo_bar` (
-> `id` bigint(20) NOT NULL AUTO_INCREMENT,
-> `foo_id` int(11) DEFAULT NULL,
-> PRIMARY KEY (`id`),
-> CONSTRAINT `FK_foo` FOREIGN KEY (`foo_id`) REFERENCES `foos` (`id`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.11 sec)
mysql> ALTER TABLE foo_bar ENGINE=blackhole;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE foos ENGINE=blackhole;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql> ALTER TABLE foo_bar ENGINE=blackhole;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql> drop table foos, foo_bar;
Query OK, 0 rows affected (0.10 sec)
mysql> CREATE TABLE `foos` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (`id`)
-> ) ENGINE=blackhole;
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql> CREATE TABLE `foo_bar` (
-> `id` bigint(20) NOT NULL AUTO_INCREMENT,
-> `foo_id` int(11) DEFAULT NULL,
-> PRIMARY KEY (`id`),
-> CONSTRAINT `FK_foo` FOREIGN KEY (`foo_id`) REFERENCES `foos` (`id`)
-> ) ENGINE=blackhole;
Query OK, 0 rows affected (0.07 sec)
mysql> show create table foo_bar\G
*************************** 1. row ***************************
Table: foo_bar
Create Table: CREATE TABLE `foo_bar` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`foo_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK_foo` (`foo_id`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Suggested fix:
I expect last statement to output:
mysql> show create table foo_bar\G
*************************** 1. row ***************************
Table: foo_bar
Create Table: CREATE TABLE `foo_bar` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`foo_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK_foo` (`foo_id`),
CONSTRAINT `FK_foo` FOREIGN KEY (`foo_id`) REFERENCES `foos` (`id`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
and ALTER statements do not fail if foreign_key_checks is 0
Description: Currently Blackhole cannot have foreign keys. It makes hard to create server in the middle of replication setup which is common for spreading read load from single master, e.g. (Master which uses real engine) -> (Slave which uses Blackhole) -> (Dozens of other slaves which use real engine) But since Blackhole does not store anything having it supporting foreign keys should not harm. How to repeat: sveta@Thinkie:~/mysql_packages/mysql-5.1.72-linux-x86_64-glibc23/mysql-test$ mysqlmtr test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.1.72-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> CREATE TABLE `foos` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.13 sec) mysql> mysql> CREATE TABLE `foo_bar` ( -> `id` bigint(20) NOT NULL AUTO_INCREMENT, -> `foo_id` int(11) DEFAULT NULL, -> PRIMARY KEY (`id`), -> CONSTRAINT `FK_foo` FOREIGN KEY (`foo_id`) REFERENCES `foos` (`id`) -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.11 sec) mysql> ALTER TABLE foo_bar ENGINE=blackhole; ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails mysql> set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE foos ENGINE=blackhole; ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails mysql> ALTER TABLE foo_bar ENGINE=blackhole; ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails mysql> drop table foos, foo_bar; Query OK, 0 rows affected (0.10 sec) mysql> CREATE TABLE `foos` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (`id`) -> ) ENGINE=blackhole; Query OK, 0 rows affected (0.06 sec) mysql> mysql> CREATE TABLE `foo_bar` ( -> `id` bigint(20) NOT NULL AUTO_INCREMENT, -> `foo_id` int(11) DEFAULT NULL, -> PRIMARY KEY (`id`), -> CONSTRAINT `FK_foo` FOREIGN KEY (`foo_id`) REFERENCES `foos` (`id`) -> ) ENGINE=blackhole; Query OK, 0 rows affected (0.07 sec) mysql> show create table foo_bar\G *************************** 1. row *************************** Table: foo_bar Create Table: CREATE TABLE `foo_bar` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `foo_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FK_foo` (`foo_id`) ) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 1 row in set (0.00 sec) Suggested fix: I expect last statement to output: mysql> show create table foo_bar\G *************************** 1. row *************************** Table: foo_bar Create Table: CREATE TABLE `foo_bar` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `foo_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FK_foo` (`foo_id`), CONSTRAINT `FK_foo` FOREIGN KEY (`foo_id`) REFERENCES `foos` (`id`) ) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 1 row in set (0.00 sec) and ALTER statements do not fail if foreign_key_checks is 0