|
Derived from MDEV-12606 (courtesy of anikitin)
--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.
|