Непойманный баг 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) и увидел всё те же обескураживающие результаты.
AdBlock похитил этот баннер, но баннеры не зубы — отрастут

Подробнее
Реклама

Комментарии 12

    +1
    Лично меня обескуражило то, что автор делает insert в view
      +3
      Прошу прощения, а для чего updatable VIEW нужны, не для этого ли?
        0
        Для этого. Но факт того, что меня это обескуражило, остаётся фактом :) Давненько я с mysql не работал :))
        Вообще, наверное, это нужно было постить в Q&A
          +3
          Поясните?)
          Баг, я думаю, заслуживает места в постах, а не в Q&A)

          P.S. Если интересно, в итоге пришлось отказаться от использования WITH CASCADED CHECK OPTION, т.к. с UPDATE'ом такой же случай нештатного поведения случился.
          Поэтому теперь в итоге за целостность в базе отвечают только InnoDB-шные CONSTRAINT'ы.
          0
          Я думаю обновляемые предсьавления нужны не для одновременного обновления нескольких таблиц одним запросом. Они необхожимы, например, что бы разграничить права на обновление полей одной таблицы.
        +1
        У вас второй вариант инсерта не отличается от третьего. Это так и задумано? А если инсертить во view, но поставить b_id в 0, то вставляется?

        dev.mysql.com/doc/refman/5.5/en/view-updatability.html:
        The WITH CHECK OPTION clause can be given for an updatable view to prevent inserts or updates to rows except those for which the WHERE clause in the select_statement is true.

        И тут, похоже, проблема заключается в том, что в момент проверки значение b_id пустое.

        Кстати, не боитесь наинсертить в таблицы A и B записей с пустыми полями name? В вашем примере смысла вставлять именно во view ну совсем никакого. Или у вас глупый ORM и он по-другому не умеет?
          0
          Вы хотели сказать, первый не отличается от третьего?
          Да, так и было задумано, чтобы показать принципиальное различие в поведении драйвера в ситуации, когда мы вставляем первую запись и когда мы вставляем запись тем же запросом в уже «проинициализированную» таблицу, приведённую (казалось бы!) к начальному состоянию.

          Явное прописывание 0 в `b_id` ничего не меняет в поведении драйвера.

          INSERT с пустыми полями `name` делаю чисто для экономии места при демонстрации.
          ORM CodeIgniter'а, которым я пользуюсь, делает разницу между "" и NULL.
          Это он в Ваших терминах умный или глупый?
          Или я не понял Вашего вопроса?
            0
            Увидел большую С в середине запроса, действительно. Рекомендую делать иллюстрации более иллюстративными :) а именно — называть похожие сущности более разными именами, чтобы не вводить читателей в заблуждение.

            Про инсерты в view: вы с какой именно целью во view делаете инсерты? Из вашего примера не видно ни одного плюса. Вы хотите заполнять сразу 3 таблицы за 1 запрос? Но зачем, если там 3 разные сущности?
              0
              ToSHiC, ограничение, запрещающее одновременное изменение нескольких таблиц, описано в оф.документации по VIEW.

              Изначально я перечислил три потенциальных плюса использования updatable VIEW:
              1-2. Выделение набора строк, меньших чем одна таблица.
              Например, можно дать пользователю права на updatable VIEW WITH CASCADED|LOCAL CHECK OPTION, содержащее часть её строк, выделенных некоторым WHERE-фильтром. В таком случае пользователь будет иметь права отображать и изменять только «свои» строки, а также, что гораздо вкуснее, не сможет вставить «не свою» строку. (Также это дополнительная логика верификации обновляемых/вставляемых данных.)
              Правда, не могу навскидку назвать ни одного примера, как это использовать, да и от WITH CASCADED CHECK OPTION пришлось отказаться, поэтому первые два плюса в статье не видны.
              0. Отсутствие необходимости таскать с собой кучу JOIN'ов в php-коде.
              Вот это главный и единственный плюс, который я знаю, как использовать.

              P.S. Кстати, любопытно. Как Вы считаете: где лучше хранить логику верификации и валидации данных: на стороне СУБД или на стороне веб-сервера?
                0
                Вообще говоря, в доке написано про one-to-one relationship between the rows in the view and the rows in the underlying table. В комментариях есть фраза «For a multiple-table updatable view, INSERT can work if it inserts into a single table.», но это тоже не запрет, а описание поведения.

                Про пункты 1-2, я это понимаю. Но вы в начале статьи подобные требования не упоминали, так что я их не рассматривал.

                Про ваш пункт 0 — так ваш ORM позволяет делать селекты из view, а инсертить при этом непосредственно в таблицу? Вы в этом случае не будете таскать с собой джоины, и при этом правильно инсертить будете.

                В нормальном продукте логика верификации должна быть как в приложении (в рамках фильтрации поступивших от пользователя данных), так и в БД, если она в принципе это позволяет, и если производительность БД позволяет это делать. Фича практически любой взрослой SQL базы данных заключается в том, что она может поддерживать целостность данных в связанных таблицах, другое дело, что многие про это не хотят думать.
                  0
                  Спасибо за мысль. Действительно, можно делать селекты из представления, а вставлять в таблицу. Как-то не подумал.

                  Что же касается обеспечения и поддержания целостности данных средствами БД, то InnoDB в этом смысле очень мощный движок. Единственное, что меня смущает — то, что при попытке совершить запрос к БД, пытающийся нарушить целостность, вернётся 500-я ошибка. (1. Прав ли я в том, что это неизбежное поведение, и при попытке некорректного запроса возврат страницы со статусом 500 произойдёт обязательно?)

                  Здесь есть два аспекта: проблема с человекоудобностью и проблема безопасности.
                  Проблема с человекоудобностью: при попытке отправки некорректных данных из формы вместо описания ошибки будет возвращаться тупо «HTTP 500» — может быть решена и во фронтенде (проверка регэкспами при автоматической отправке; если человек отправляет руками через консоль или адресную строку, то он, наверное, не нуждается в описании ошибки.)
                  Security issue: информация о том, на какие запросы к БД сервер «ругается», может дать злоумышленнику некоторое знание об архитектуре БД — может быть решена только в бэкенде.

                  Таким образом, хоть использование средств БД для поддержания целостности и необходимо в качестве страховки, но от полной валидации и верификации данных в бэкенде никуда не деться. Security in depths и всё такое. 2. Так?
                    0
                    1. Какую ошибку вернуть — это целиком и полностью на вашей совести. Можно и что нибудь из 4хх кодов подобрать ( en.wikipedia.org/wiki/List_of_HTTP_status_codes#4xx_Client_Error ), и в теле присовокуплять, почему именно ошибка. Отправлять наружу тексты SQL ошибок, конечно же, не стоит.

                    2. Да. Клиент всегда может прислать любую ересь. Более того, клиент может прислать правильный запрос, который стал не валиден из-за действий других клиентов или программ. Например, пытается забронировать билет, а они все только что кончились. С точки зрения клиента и фронта — полностью валидная операция, с точки зрения БД и бэкэнда — уже не валидная.

        Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

        Самое читаемое