Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-12669

Circular foreign keys cause a loop and OOM upon LOCK TABLE

    XMLWordPrintable

    Details

      Description

      Derived from MDEV-12606 (courtesy of Andrii Nikitin)

      --source include/have_innodb.inc
       
      SET FOREIGN_KEY_CHECKS=0;
       
      DROP SCHEMA IF EXISTS sakila;
      CREATE SCHEMA sakila;
      USE sakila;
       
      CREATE TABLE staff (
        staff_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
        store_id TINYINT UNSIGNED NOT NULL,
        PRIMARY KEY  (staff_id),
        KEY idx_fk_store_id (store_id),
        CONSTRAINT fk_staff_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE
      )ENGINE=InnoDB DEFAULT CHARSET=utf8;
       
      CREATE TABLE store (
        store_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
        manager_staff_id TINYINT UNSIGNED NOT NULL,
        PRIMARY KEY  (store_id),
        UNIQUE KEY idx_unique_manager (manager_staff_id),
        CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE
      )ENGINE=InnoDB DEFAULT CHARSET=utf8;
       
      SET FOREIGN_KEY_CHECKS=DEFAULT;
       
      LOCK TABLE staff WRITE;
       
       
      # Cleanup
      UNLOCK TABLES;
      DROP SCHEMA sakila;
      

      No special options are needed, server default or MTR default will do.
      Upon reaching LOCK TABLE statement server rapidly consumes all available memory.

      25261 elenst    20   0 3165920 1.846g  17312 S  97.6 11.8   0:02.31 mysqld
      25261 elenst    20   0 6180576 4.703g  17312 S  99.1 30.2   0:05.29 mysqld
      25261 elenst    20   0 9195232 7.570g  17312 S  99.1 48.5   0:08.27 mysqld
      ...
      

      Removing the loop in foreign keys makes the problem go away, but since the original data structure is taken from Sakila database, apparently it is considered valid.

      10.1 or MySQL 5.7(.17) don't have the problem.

        Attachments

          Activity

            People

            Assignee:
            serg Sergei Golubchik
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: