[Из песочницы] Непойманный баг MySQL: невозможность добавления первой записи в составной VIEW

Привет, Хабр!

Я привык выполнять свою работу добросовестно и перед написанием этого поста параноидально проверил несколько раз, насколько подмеченное мной является действительно багом (а не последствиями бессонной ночи перед компьютером), а также попытался найти что-либо похожее в интернетах. In vain. Verloren. Тщетно.

Итак, если интересно, добро пожаловать под кат, чтобы увидеть несложный архитектурный элемент, на котором некорректно срабатывает добавление первой записи в составной VIEW.

Что курил автор

Сначала вкратце о том, зачем подобное архитектурное решение понадобилось.

Не разглашая деталей (соглашение о неразглашении конфиденциальной информации, все дела :)), скажу, что в работе над нынешним проектом мне требуется для трёх классов объектов (a ,b ,c ) реализовать следующие отношения:

c к a — ∞ к 1,

c к b — ∞ к 0..1.

Таким образом, каждый объект c имеет отношение к одному объекту a , а также может иметь отношение к одному объекту b или не иметь отношения к объектам b вовсе.

Велотренажёр для фрилансера

Данный фрагмент БД был спроектирован следующим образом:

+ таблица, перечисляющая все объекты класса a (для простоты пусть их единственный параметр кроме айдишника — название);

+ таблица, перечисляющая все объекты класса b (та же петрушка);

+ таблица, перечисляющая все объекты класса c (кроме айдишника имеет параметры: название, айдишник объекта класса a (must!), айдишник объекта класса b (необязательный));

+ представление, содержащее все объекты класса c с названиями связанных с ними объектов классов a и b (из соображений безопасности (можно выдавать права на VIEW, не затрагивая саму таблицу), для переноса части логики верификации целостности данных из php в MySQL, а также чтобы не таскать в php-коде JOIN-ы) с WITH CASCADED CHECK OPTION.

Месье знает толк в козлятах

Чтобы обеспечить изменяемость представления, я должен был обойтись исключительно INNER JOIN'ами (LEFT OUTER JOIN запрещает изменяемость представления), но с другой стороны необходимо было также отобразить в представлении даже те объекты класса c , которые не имеют отношения к объектам класса b .

Для этого я применил следующий трюк: пусть айдишник связанного объекта класса b может принимать также нулевое значение ('0'), что означает отсутствие связанного объекта класса b ; пусть также таблица объектов класса b содержит нулевую запись (с нулевым айдишником), соответствующую отсутствию объекта класса b (дадим ему имя 'N/A').

И вот этот трюк в сочетании с WITH CASCADED CHECK OPTION даёт нештатное поведение оператора INSERT, применённого к представлению объектов класса c .

Как научить оператор INSERT плохому

Приведу модельные запросы к БД, которые воссоздают ситуацию:

CREATE TABLE `a`(`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,`name` VARCHAR(255) DEFAULT NULL) ENGINE='InnoDB' CHARSET='utf8' COLLATE='utf8_general_ci';
INSERT INTO `a`(`name`) VALUES('test_a');

CREATE TABLE `b`(`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,`name` VARCHAR(255) DEFAULT NULL) ENGINE='InnoDB' CHARSET='utf8' COLLATE='utf8_general_ci';
SET SESSION `SQL_MODE`='NO_AUTO_VALUE_ON_ZERO';
INSERT INTO `b`(`id`,`name`) VALUES('0','N/A');
INSERT INTO `b`(`id`,`name`) VALUES('1','test_b');
SET SESSION `SQL_MODE`='';

CREATE TABLE `c`(`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,`name` VARCHAR(255) NOT NULL,`a` INT NOT NULL,`b` INT DEFAULT '0',FOREIGN KEY(`a`) REFERENCES `a`(`id`),FOREIGN KEY(`b`) REFERENCES `b`(`id`)) ENGINE='InnoDB' CHARSET='utf8' COLLATE='utf8_general_ci';

CREATE VIEW `C` AS SELECT `t1`.`id` `id`,`t1`.`name` `name`,`t2`.`name` `a`,`t3`.`name` `b`,`t1`.`a` `a_id`,`t1`.`b` `b_id` FROM `c` `t1` JOIN `a` `t2` ON(`t1`.`a`=`t2`.`id`) JOIN `b` `t3` ON(`t1`.`b`=`t3`.`id`) WITH CASCADED CHECK OPTION;

SELECT `id` FROM `a`;
SELECT `id` FROM `b`;

mysql> SELECT `id` FROM `a`;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.01 sec)

mysql> SELECT `id` FROM `b`;
+----+
| id |
+----+
|  0 |
|  1 |
+----+
2 rows in set (0.00 sec)

Всё как и должно быть, не так ли?

А теперь попробуем просто вставить первую запись без связанного объекта b в представление C .

mysql> INSERT INTO `C`(`a_id`,`name`) VALUES('1','test_c');
ERROR 1369 (HY000): CHECK OPTION failed 'test.C'
mysql> 

Обескураживает? Не знаю, как Вас, но меня — да.

Ладно. Попробуем разобраться.

Осуществим абсолютно идентичный запрос напрямую к таблице c , после чего выведем на экран содержимое представления C .

mysql> INSERT INTO `c`(`a`,`name`) VALUES('1','test_c');
Query OK, 1 row affected (0.09 sec)

mysql> SELECT * FROM `C`;
+----+--------+--------+------+------+------+
| id | name   | a      | b    | a_id | b_id |
+----+--------+--------+------+------+------+
|  1 | test_c | test_a | N/A  |    1 |    0 |
+----+--------+--------+------+------+------+
1 row in set (0.00 sec)

mysql> 

Обескураживает? Не знаю, как Вас, но меня — очень.

Я не могу объяснить подобное поведение иначе как словом «баг».

Тем более что, если теперь привести таблицу c к изначальному виду, записи будут добавляться через представление C «на ура».

mysql> DELETE FROM `c`; ALTER TABLE `c` AUTO_INCREMENT=1; INSERT INTO `C`(`a_id`,`name`) VALUES('1','test_c'); SELECT * FROM `C`;
Query OK, 1 row affected (0.05 sec)

Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 1 row affected (0.00 sec)

+----+--------+--------+------+------+------+
| id | name   | a      | b    | a_id | b_id |
+----+--------+--------+------+------+------+
|  1 | test_c | test_a | N/A  |    1 |    0 |
+----+--------+--------+------+------+------+
1 row in set (0.01 sec)

mysql> 

Выводы?

Nuff said. Я думаю научиться писать багрепорты в Сообщество (Linux-community или MySQL-community — ещё вопрос: я не видел ещё MySQL 5.6: возможно, там нет этого бага), если Хабровчане одобрят сей плод полуночного задротства и полуденной графомании. (Попиарюсь немного: этой ночью я уже получил первый одобренный pull request на гитхабе.)

Postscriptum

Сказанное выше изначально относилось к MySQL версии 5.1 (да-да, к сожалению, пока что работа на площадке с MySQL 5.1 — неизбежное условие), но затем я попробовал всё то же самое на своей печатной машинке с MySQL 5.5.35 (testing релиз из официальных репозиториев Debian) и увидел всё те же обескураживающие результаты.

This entry passed through the Full-Text RSS service — if this is your content and you're reading it on someone else's site, please read the FAQ at http://ift.tt/jcXqJW.