Uploaded image for project: 'Percona Server'
  1. Percona Server
  2. PS-2512

LP #1737622: Add foreign key support for Blackhole storage engine

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: On Hold
    • Priority: Low
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None

      Description

      **Reported in Launchpad by Sveta Smirnova last update 12-12-2017 07:37:26

      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

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                lpjirasync lpjirasync (Inactive)
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated: