MySQL создает сбой триггера "перед вставкой" и вызывает ошибки в триггерах

Question

Я успешно создал вставку перед триггером в таблицу, используя механизм innodb, который "выдает" ошибку, выполняя вставку в несуществующую таблицу. Проблема заключается в том, что при попытке запустить сценарий создания базы данных на производственном сервере происходит сбой, поскольку таблица вставки не существует. Тем не менее, тот же сценарий отлично работает на моей рабочей станции, что заставляет меня думать, что есть параметр конфигурации MySQL, который вызывает сбой сценария создания.

Вопрос, который поднимает моя проблема, заключается в том, компилирует ли рабочий сервер триггер, а рабочая станция - нет (или компилирует во время выполнения). В производственной среде я бы предпочел, чтобы SQL компилировался при создании.

Answer 1

Оказывается, длина имени таблицы была причиной проблемы. Две версии сервера были из разных выпусков, при этом сервер был предыдущим выпуском (5.0 против 5.1).

Что касается появления ошибок в триггерах, я выбрал другой подход, однако он может быть ошибочным в зависимости от того, как MySQL обрабатывает оператор создания триггера. Если операторы триггера проверяются при создании, то это не удастся.

Как только возникает ошибка (ожидаемая ошибка), я устанавливаю переменную сеанса (имя переменной с префиксом @) с отображением сообщения об ошибке. Как только сообщение об ошибке установлено, я делаю вставку в несуществующую таблицу Die, чтобы вызвать ошибку в MySQL.

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

MySQL Trigger:

CREATE TRIGGER Error_Trigger 
BEFORE INSERT ON Fubar 
FOR EACH ROW
BEGIN
    if DataIsBad then
        set @Err = 'The data is fubared.';
        insert into Die values (1);                
    end if;    
END$$

Код стороны приложения:

public function getDatabaseErrorMessage($AdminMessage = false){
    if ($this->db->_error_number() != 0){
        $AdminError = $this->db->_error_message();
        $Query = $this->db->query("select @Err");
        if ($Query){
            $Error = $Query->row_array();
        }
        if (isset($Error["@Err"]) && $Error["@Err"] != ""){
            $Error = $Error["@Err"];
            $this->db->query("set @Err = ''");
        } else {
            if ($AdminMessage){
                $Error = $AdminError;
            } else {
                return false;
            }
        }
        throw new Exception($Error);
    }
}

Answer 2

Мой метод для имитации возникновения ошибки в триггере заключается в DECLARE целочисленная переменная и хранить в ней строку. Если у вас есть SQL_MODE = STRICT_ALL_TABLES это вызывает ошибку. В противном случае это вызывает предупреждение.

use test;

drop table if exists foo;
create table foo (id serial primary key, bar varchar(10));

drop trigger if exists RaiseError;

delimiter //

create trigger RaiseError before insert on foo
for each row
begin
  declare bar int;
  if new.bar = 'boom' then
    set bar = 'You cannot store that value!';
  end if;
end //

delimiter ;

set sql_mode = strict_all_tables;

insert into foo (bar) values ('boom');

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

ОШИБКА 1366 (HY000): Неверное целочисленное значение: "Вы не можете сохранить это значение!" для столбца 'bar' в строке 1