Add foreign key support for Blackhole storage engine

Bug #1737622 reported by Sveta Smirnova on 2017-12-11
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server moved to https://jira.percona.com/projects/PS
Status tracked in 5.7
5.5
In Progress
Wishlist
Stephanie
5.6
Triaged
Wishlist
Unassigned
5.7
Triaged
Wishlist
Unassigned
8.0
Triaged
Wishlist
Unassigned

Bug 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)

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

tags: added: upstream

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2512

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.