Перехват сообщений об ошибках на сервере

[JohnSparrow]

Перехват сообщений об ошибках на сервере

Я пытаюсь вставить в таблицу одни и те же данные, нарушая тем самым ограничение на уникальность:

-- создаем таблицу
CREATE TABLE radcdb.cloudness (
INTERNAL_ID BIGINT(20) AUTO_INCREMENT COMMENT 'Уникальный код',
TITLE VARCHAR(128) NOT NULL DEFAULT '' COMMENT 'Наименование',
PRIMARY KEY (INTERNAL_ID),
UNIQUE INDEX INDX_CLOUDNESS_TITLE USING BTREE (TITLE)
)
ENGINE = INNODB
AUTO_INCREMENT = 17
CHARACTER SET utf8 COLLATE utf8_general_ci;

-- пытаемся вставить в нее два раза одно и то же
insert into CLOUDNESS(TITLE) values('Clear');
insert into CLOUDNESS(TITLE) values('Clear');

В ответ на вторую попытку вставки в клиент приходит сообщение об ошибке, что и показано ниже:

------ Выполнение начато: SQL1.sql ------
Таблица создана [0,057 c]
1 Строка вставлена [0,014 c]
Ошибка (17,1): Duplicate entry 'Clear' for key 'INDX_CLOUDNESS_TITLE'
------------ Готово: SQL1.sql -------------

Идея заключается в том, чтобы:

• Для вставки данных создать хранимую процедуру на сервере.
• В этой процедуре перехватывать ошибки (получать сообщение об ошибке в том виде, в котором оно приведено выше).
• По маске выделять из них "виновника" (в данном конкретном случае - уникальный индекс).
• Зная виновника, возвращать в клиент "вменяемое" сообщение об ошибке (в данном случае - "Тип облачности /Clear/ уже зарегистрирован"), которое будет ясно оператору.

Дело в том, что возможных ограничений масса (внешние ключи, ограничение на длину строки или размер числа) и предполагается, что клиенты могут быть разные (настольное Windows-приложение и web-интерфейс), поэтому кажется не лучшим вариант реализовывать проверку ошибок на стороне клиента.

А, ну так вот, можно ли получить сообщение об ошибке непосредственно после выполнения манипуляций с данными в хранимой процедуре и, если да, то как?

---
MySQL - 5.1

 

[FractalizeR]

Проверять наличие ошибки следует до выполнения оператора. Например, SELECT оператором внутри транзакции. Намеренно выполнять оператор, который может привести к ошибке - не слишком хороший стиль программирования.

Если все же делать по-вашему, можно использовать операторы INSERT с модификатором IGNORE. А еще в хранимой процедуре можно использовать DECLARE HANDLER (см. раздел справки MySQL 18.2.8. Conditions and Handlers)

 

[JohnSparrow]

Спасибо. Проблема в том, что если использовать DECLARE HANDLER в хранимой процедуре, то придется в нем описывать действия для каждой из вероятных ошибок. Т.к. таких ошибок может быть много, то получится слишком большой обработчик ошибок. Идея же заключается в том, чтобы выполнить нечто вроде
---------------------------------
PROCEDURE myDB.InsertData(IN vTitle, IN vReferenceToOtherTable, OUT vError, OUT vReason)
BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET vError = ?????;
SET vReason = ?????;
END;

INSERT INTO table(title, ref_id) VALUES(vTitle, vReferenceToOtherTable);
COMMIT;
END;
-----------------------------------------------------------
Короче говоря, написать единый обработчик исключений, а в нем получить текст ошибки и дальше по маске вычислить вызвавшее его ограничение. Если я знаю, что, например, сработало ограничение на максимальную длину строки TITLE, то возвращаю клиенту одно сообщение об ошибке, если ограничение на уникальность TITLE - другое сообщение. Ну и т.д. Проблема в том, что таблиц много и процедур для каждой из них минимум две - вставка и изменение записи. С другой стороны, если я сначала напишу настольный клиент и в нем в соответствующих формах по рабоет с данными предусмотрю все проверки, а потом web-клиент, в котором проделаю все то же, но на РНР, то это выйдет масса лишней работы.

Кроме того, привлекает сама идея создать базу данных, которая корректно и без помощи клиентов обрабатывает все запросы на модификацию ее содержимого. А клиент тогда можно к ней пришпилить любой.

Вторая задача - исходные коды клиентов останутся открытыми для последующего сопровождения (например, добавления отчетов, необходимость и даже форма которых сейчас не определены и т .д.), но структура базы данных должна быть защищена от модификаций (и закрыта для просмотра) и сама БД будет располагаться на сервере, физически недоступном разработчикам клиентского ПО.

В Оракле это реализуется просто: в конце процедуры ставится блок обработки исключений и там уже мы из текста сообщения об ошибке и номера ошибки извлекаем все что нужно и возвращаем клиенту. Хотелось бы, чтобы так было и здесь, .

 

[FractalizeR]

Я думаю, вы неправильно пользуетесь исключениями. В книге Макконнелла Совершенный Код есть такая рекомендация относительно использования исключений:

"Исключения используются в таких же обстоятельствах, как и утверждения (assertions): для событий, которые не просто редко происходят, а которые никогда не должны случаться".

Вы же превратили исключительные ситуации в норму в своем коде. Если оператор INSERT завершается с ошибкой MySQL - это именно исключительная ситуация, а не норма. И эту ситуацию следует предвосхищать.

 

[JohnSparrow]

С утверждением спорить не буду хотя бы потому, что сам новичок. Тогда вот такой вопрос: как, по Вашему мнению, следует предвосхищать подобные ситуации при выполнении, например, операции вставки в таблицу? У меня есть следующие варианты:
1) На стороне клиента проверить размеры всех передаваемых параметров (длину строк, допустимость NULL, размеры чисел и т.д.).
2) Сделать то же самое, но уже внутри хранимой процедуры.

Самое веселое, что если мы имеем некоторую таблицу фактов, в которой 10 полей - внешние ключи записей из соответствующих таблиц измерений (например, поля "Код товара", "Код магазина", "Код продавца" и т.д. в таблице "Заказы"), то необходимо будет проверить переданные ID на существование в их таблицах. Если не проверять, а внешний ключ, как объект БД, существует, то при некорректном, например, "Код товара" возникнет исключение. Если же проверять, то можно вообще не создавать внешние ключи (связи между таблицами), т.к. в этом случае вся проверка целостности вставляемых (модифицируемых) данных ложится на плечи хранимой процедуры сервера или (о ужас) ПО клиента.

Я так понимаю, что ограничения целостности существуют для реализации логики предметной области, данными которой и оперирует конкретная БД. В этом случае использование порождаемых ими исключений как средства выявления ошибок мне кажется более удобным путем.

Но, впрочем, это уже рассуждения. Тогда такой вопрос: у нас есть таблица, в которой два значимых поля - "Наименование" (не может быть NULL, 64 символа макс.) и "Код группы" (внешний ключ, ссылается на некую таблицу). Как и где лучше всего проверить, что для вставки введены некорректные данные? Варианты ошибок здесь:
1) "Наименование" = NULL или имеет нулевую длину.
2) "Наименование" имеет более 64 символов.
3) "Код группы" ссылается на несуществующую запись в родительской таблице.

 

[FractalizeR]

С утверждением спорить не буду хотя бы потому, что сам новичок. Тогда вот такой вопрос: как, по Вашему мнению, следует предвосхищать подобные ситуации при выполнении, например, операции вставки в таблицу? У меня есть следующие варианты:
1) На стороне клиента проверить размеры всех передаваемых параметров (длину строк, допустимость NULL, размеры чисел и т.д.).
2) Сделать то же самое, но уже внутри хранимой процедуры.

У вас проект - двухзвенка, как я понимаю. А для таких серьезных целей лучше пользоваться трехзвенкой (клиент, ваш сервер, сервер БД). На сервере происходят все проверки данных на корректность. В случае двухзвенки, наверное, без проверки данных на корректность внутри хранимых процедур не обойтись....

Что касается проверки существования всех внешних ключей для вставки записи в какую-либо таблицу это достаточно просто делается внутри транзакции:
1. Начинаем транзакцию.
2. Выбираем все строки из всех других таблиц, имеющих отнощение к вставляемой строке с помощью SELECT ... LOCK IN SHARE MODE
3. Если все SELECT вернули результат (это значит, что все внешние ключи существуют) - выполняем вставку. Пока транзакция не завершится, MySQL будет удерживать блокировки, не дающие обновлять или удалять выбранные по SELECT ... LOCK IN SHARE MODE записи.
4. Завершаем транзакцию.

 

[JohnSparrow]

Большое спасибо, Ваша идея про запрос с блокировкой - как раз то, что нужно. Плохо быть новичком и не иметь достаточно времени для подготовки, .

 

[FractalizeR]

Не за что. Обратите еще внимание на SELECT ... FOR UPDATE