Блог

После обновления до MySQL 8.0 я столкнулся со следующей проблемой:

У меня есть две таблицы, назовем их A и B. B имеет ссылку на столбец для таблицы A и ограничение (внешний ключ), установленное так, что при удалении ссылочного объекта значение устанавливается равным null (ПРИ УДАЛЕНИИ УСТАНАВЛИВАЕТСЯ NULL).

  /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
 /*!40101 SET NAMES UTF8 */;
 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
 /*!40103 SET TIME_ZONE=' 00:00' */;
 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

 CREATE TABLE `A` (
 `ID` VARCHAR(32) CHARACTER SET UTF8 COLLATE UTF8_BIN NOT NULL,
  PRIMARY KEY (`ID`)
 ) ENGINE=INNODB DEFAULT CHARSET=UTF8;

 CREATE TABLE `B` (
   `ID` VARCHAR(32) CHARACTER SET UTF8 COLLATE UTF8_BIN 
    DEFAULT NULL,
   `COL_B` VARCHAR(32) CHARACTER SET UTF8 COLLATE UTF8_BIN 
    DEFAULT NULL,
    CONSTRAINT `B_FK5` FOREIGN KEY (`COL_B`) REFERENCES `A` (`ID`) 
 ON 
    DELETE SET NULL
 ) ENGINE=INNODB DEFAULT CHARSET=UTF8;

 INSERT INTO `A` (`ID`) VALUES ('1');

 INSERT INTO `B` (`ID`,`COL_B`) VALUES ('1','1');

 DELETE FROM A;

 SELECT * FROM B;
  

Проблема: при удалении элемента из таблицы A ссылка в таблице B не имеет значения null (ключ остается, хотя объект с этим ключом был удален).

Дополнительная информация: До сих пор я использовал MySQL 5.7, и у меня не было этой проблемы. После перехода на MySQL 8.0.15 я начал видеть это поведение. Кроме того, эта проблема возникает только на локальных и компакт-дисках и не воспроизводится на облачных компьютерах. В результате некоторые тесты начали давать сбои при выполнении заданий CD.

Еще одна странная вещь заключается в том, что прямо перед удалением элемента, если в таблице A внесено изменение (переключение порядка столбцов), проблема не воспроизводится.

Я также пытался использовать запуск MySQL в Docker, и проблема та же. На самом деле, это проблема блокировки, и тот факт, что это происходит только в этом случае, затрудняет понимание. Из журналов MySQL я не смог получить squat.

Любая подсказка, которая могла бы помочь в этом расследовании?

ПОСЛЕДУЮЩЕЕ РЕДАКТИРОВАНИЕ: ограничение определено правильно, оно работало правильно до сих пор

Мне удалось воспроизвести его с помощью следующего скрипта, который содержит только 2 таблицы, как в автономной установке MySQL 8.0.15, так и с помощью Docker. Оба были выполнены на macOS.

Это команда для запуска MySQL 8.0.15 в Docker:

 docker run -p 3306:3306 --restart=always --name mysql -e MYSQL_ROOT_PASSWORD=somepassword -d mysql:8.0.15 mysqld --default_authentication_plugin=mysql_native_password --sql-mode="NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES"
  

Это сценарий SQL:

 create schema test;
use test;

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES UTF8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE=' 00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

DROP TABLE IF EXISTS `EMPLOYEES`;
/*!40101 SET @SAVED_CS_CLIENT     = @@CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_CLIENT = UTF8 */;
CREATE TABLE `EMPLOYEES` (
    `KEY` VARCHAR(32) CHARACTER SET UTF8 COLLATE UTF8_BIN NOT NULL,
    `PROFILEIMAGEKEY1` VARCHAR(32) CHARACTER SET UTF8 COLLATE UTF8_BIN,
    PRIMARY KEY (`KEY`),
    CONSTRAINT `EMPLOYEE_FK1` FOREIGN KEY (`PROFILEIMAGEKEY1`) REFERENCES `PICTURES` (`KEY`) ON DELETE SET NULL
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
/*!40101 SET CHARACTER_SET_CLIENT = @SAVED_CS_CLIENT */;

DROP TABLE IF EXISTS `PICTURES`;
/*!40101 SET @SAVED_CS_CLIENT     = @@CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_CLIENT = UTF8 */;
CREATE TABLE `PICTURES` (
  `KEY` VARCHAR(32) CHARACTER SET UTF8 COLLATE UTF8_BIN NOT NULL,
  PRIMARY KEY (`KEY`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
/*!40101 SET CHARACTER_SET_CLIENT = @SAVED_CS_CLIENT */;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

INSERT INTO `PICTURES` (`KEY`) VALUES ('1');

INSERT INTO `EMPLOYEES` (`KEY`,`PROFILEIMAGEKEY1`) VALUES ('abc', '1');
select * from `PICTURES`;
SELECT * FROM `EMPLOYEES`;
DELETE FROM `PICTURES`;

SELECT * FROM `EMPLOYEES`;
  

Пожалуйста, обратите внимание, что этот скрипт отлично работает на MySQL 5.7.x (автономная установка или Docker).

Это была ошибка (https://bugs.mysql.com/bug.php?id=94400 ), которые мы исправили в 8.0.16. Я только что протестировал ваш скрипт на 8.0.15 и 8.0.16. Я смог воспроизвести проблему в версии 8.0.15, и действительно, в версии 8.0.16 (следующая версия) проблема исчезла.