Привет, Хабр!
Я привык выполнять свою работу добросовестно и перед написанием этого поста параноидально проверил несколько раз, насколько подмеченное мной является действительно багом(а не последствиями бессонной ночи перед компьютером), а также попытался найти что-либо похожее в интернетах. 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.
Приведу модельные запросы к БД, которые воссоздают ситуацию:
Всё как и должно быть, не так ли?
А теперь попробуем просто вставить первую запись без связанного объекта b в представление C.
Обескураживает? Не знаю, как Вас, но меня — да.
Ладно. Попробуем разобраться.
Осуществим абсолютно идентичный запрос напрямую к таблице c, после чего выведем на экран содержимое представления C.
Обескураживает? Не знаю, как Вас, но меня — очень.
Я не могу объяснить подобное поведение иначе как словом «баг».
Тем более что, если теперь привести таблицу c к изначальному виду, записи будут добавляться через представление C «на ура».
Nuff said. Я думаю научиться писать багрепорты в Сообщество (Linux-community или MySQL-community — ещё вопрос: я не видел ещё MySQL 5.6: возможно, там нет этого бага), если Хабровчане одобрят сей плод полуночного задротства и полуденной графомании.(Попиарюсь немного: этой ночью я уже получил первый одобренный pull request на гитхабе.)
Сказанное выше изначально относилось к MySQL версии 5.1 (да-да, к сожалению, пока что работа на площадке с MySQL 5.1 — неизбежное условие), но затем я попробовал всё то же самое на своей печатной машинке с MySQL 5.5.35 (testing релиз из официальных репозиториев Debian) и увидел всё те же обескураживающие результаты.
Я привык выполнять свою работу добросовестно и перед написанием этого поста параноидально проверил несколько раз, насколько подмеченное мной является действительно багом
Итак, если интересно, добро пожаловать под кат, чтобы увидеть несложный архитектурный элемент, на котором некорректно срабатывает добавление первой записи в составной 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: возможно, там нет этого бага), если Хабровчане одобрят сей плод полуночного задротства и полуденной графомании.
Postscriptum
Сказанное выше изначально относилось к MySQL версии 5.1 (да-да, к сожалению, пока что работа на площадке с MySQL 5.1 — неизбежное условие), но затем я попробовал всё то же самое на своей печатной машинке с MySQL 5.5.35 (testing релиз из официальных репозиториев Debian) и увидел всё те же обескураживающие результаты.