Подтвердить что ты не робот

MySQL 5.5.30 каскадные триггеры не работают

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

Это отлично работает на нашей локальной версии MySQL 5.5.25

Я не нашел никакой документации, которая бы объясняла это поведение, может ли кто-нибудь, возможно, иметь равную проблему?

Это либо ошибка, которая возникает в версии MySQL более 5.5.25, либо "функция", которая включена случайно.

UPDATE table1 => fires BEFORE UPDATE trigger ON table1
      table1 BEFORE UPDATE TRIGGER executes: DELETE FROM table2 => should fire BEFORE DELETE trigger on table2 ( but doesn't )
            table 2 BEFORE DELETE TRIGGER executes: DELETE FROM table3 (never happens)

ОК здесь мои действия:

База данных

CREATE DATABASE "triggerTest" DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Таблицы

CREATE TABLE "table1" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "active" tinyint(1) NOT NULL DEFAULT '0',
  "sampleData" varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY ("id")
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC;


CREATE TABLE "table2" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "table1_id" int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY ("id"),
  CONSTRAINT "test2_fk_table1_id" FOREIGN KEY ("table1_id") REFERENCES "table1" ("id") ON DELETE CASCADE ON UPDATE CASCADE  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC;


CREATE TABLE "table3" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "table1_id" int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY ("id"),
  CONSTRAINT "test3_fk_table1_id" FOREIGN KEY ("table1_id") REFERENCES "table1" ("id") ON DELETE CASCADE ON UPDATE CASCADE  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC;

Триггеры

DELIMITER $$

CREATE TRIGGER "table1_rtrg_AI" AFTER INSERT ON "table1" FOR EACH ROW
BEGIN
    IF NEW."active" THEN
        INSERT INTO "table2" ( "table1_id" ) SELECT NEW."id";
    END IF;
END$$

CREATE TRIGGER "table1_rtrg_BU" BEFORE UPDATE ON "table1" FOR EACH ROW
BEGIN
    IF NOT NEW."active" AND OLD."active" THEN
        DELETE FROM "table2" WHERE "table1_id" = OLD."id";
    END IF;

    IF NEW."active" AND NOT OLD."active" THEN
        INSERT INTO "table2" ( "table1_id" ) SELECT NEW."id";
    END IF;
END$$

CREATE TRIGGER "table2_rtrg_AI" AFTER INSERT ON "table2" FOR EACH ROW
BEGIN
    INSERT INTO "table3" ( "table1_id" ) SELECT NEW."table1_id";
END$$

CREATE TRIGGER "table2_rtrg_BD" BEFORE DELETE ON "table2" FOR EACH ROW
BEGIN
    DELETE FROM "table3" WHERE "table1_id" = OLD."table1_id";
END$$

DELIMITER ;

В: Почему вы цитируете идентификаторы, используя двойные кавычки? (вместо обратных шагов)

Потому что мне не нравится синтаксис ниши

    mysql> show variables LIKE 'sql_mode';
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                                |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | PIPES_AS_CONCAT,**ANSI_QUOTES**,IGNORE_SPACE,NO_UNSIGNED_SUBTRACTION,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Тестовая версия 1: ожидаемое поведение (версия базы данных 5.2.20)

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.5.20    |
+-----------+
1 row in set (0.00 sec)

mysql> SET GLOBAL general_log := ON;

триггер тестовой вставки

mysql> INSERT INTO "table1" ( "active", "sampleData" ) SELECT 0, 'sample data row 1';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

general_log: 
130423 12:51:27 78010 Query     INSERT INTO "table1" ( "active", "sampleData" ) SELECT 0, 'sample data row 1'


mysql> INSERT INTO "table1" ( "active", "sampleData" ) SELECT 1, 'sample data row 2';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

general_log:
130423 12:51:33 78010 Query     INSERT INTO "table1" ( "active", "sampleData" ) SELECT 1, 'sample data row 2'
                78010 Query     INSERT INTO "table2" ( "table1_id" ) SELECT NEW."id"
                78010 Query     INSERT INTO "table3" ( "table1_id" ) SELECT NEW."table1_id"

ожидаемое содержимое таблицы:

mysql> SELECT * FROM "table1";
+----+--------+-------------------+
| id | active | sampleData        |
+----+--------+-------------------+
|  1 |      0 | sample data row 1 |
|  2 |      1 | sample data row 2 |
+----+--------+-------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM "table2";
+----+-----------+
| id | table1_id |
+----+-----------+
|  1 |         2 |
+----+-----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM "table3";
+----+-----------+
| id | table1_id |
+----+-----------+
|  1 |         2 |
+----+-----------+
1 row in set (0.00 sec)

запуск триггера обновлений, установка активного

mysql> UPDATE "table1" SET "active" = 1 WHERE "id" = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

query_log:
130423 12:52:15 78010 Query     UPDATE "table1" SET "active" = 1 WHERE "id" = 1
                78010 Query     INSERT INTO "table2" ( "table1_id" ) SELECT NEW."id"
                78010 Query     INSERT INTO "table3" ( "table1_id" ) SELECT NEW."table1_id"

ожидаемое содержимое таблицы:

mysql> SELECT * FROM "table1";
+----+--------+-------------------+
| id | active | sampleData        |
+----+--------+-------------------+
|  1 |      1 | sample data row 1 |
|  2 |      1 | sample data row 2 |
+----+--------+-------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM "table2";
+----+-----------+
| id | table1_id |
+----+-----------+
|  2 |         1 |
|  1 |         2 |
+----+-----------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM "table3";
+----+-----------+
| id | table1_id |
+----+-----------+
|  2 |         1 |
|  1 |         2 |
+----+-----------+
2 rows in set (0.00 sec)

запуск триггера обновлений, установка неактивного

mysql> UPDATE "table1" SET "active" = 0 WHERE "id" = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

query_log:

130423 12:52:49 78010 Query     UPDATE "table1" SET "active" = 0 WHERE "id" = 2
                78010 Query     DELETE FROM "table2" WHERE "table1_id" = NEW."id"
                78010 Query     DELETE FROM "table3" WHERE "table1_id" = OLD."table1_id"

ожидаемое содержимое таблицы:

mysql> SELECT * FROM "table1";
+----+--------+-------------------+
| id | active | sampleData        |
+----+--------+-------------------+
|  1 |      1 | sample data row 1 |
|  2 |      0 | sample data row 2 |
+----+--------+-------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM "table2";
+----+-----------+
| id | table1_id |
+----+-----------+
|  2 |         1 |
+----+-----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM "table3";
+----+-----------+
| id | table1_id |
+----+-----------+
|  2 |         1 |
+----+-----------+
1 row in set (0.00 sec)

Testcase2: неожиданное поведение (версия MySQL 5.5.30)

Святые триггеры grml - Знаете что? Позор, что я не тестировал второй случай первым - к сожалению, я не смог воспроизвести ошибку. Тест также работал на 5.5.30, будет держать вас в курсе:)

ИЗМЕНИТЬ Триггер не каскадировался из-за неизвестного определителя, который оставался в свалке sql для производства. Удаление DEFINER = в дампах триггеров (альтернативным решением было бы создать пользователя или изменить DEFINER = на существующий) решить проблему, решить часть проблемы.

Неизвестный определитель не вызвал выхода из файла журнала

4b9b3361

ОТВЕТЫ

Ответ 1

Окончательный вывод: MySQL 5.5.30 не является ошибкой в ​​этом случае, также не было неправильной конфигурации самого сервера.

Некоторые ошибки, сделанные самим собой, вызвали проблему:

Ошибка I: пользователь DEFINER не существовал

Вместо того, чтобы просто генерировать базу данных на производственной машине, я был ленив и бросил тестовую базу данных на производственную машину. Если вы явно не устанавливаете DEFINER в свой оператор CREATE TRIGGER, он устанавливается на CURRENT_USER. К сожалению, этот точный CURRENT_USER на моей тестовой машине не существует на рабочем сервере.

Ошибка II: быть ленивым

mysqldump сбрасывает определение триггера с помощью DEFINER, а создание триггера должно генерировать предупреждение, но опять же, я ленился и сделал что-то вроде этого.

mysqldump --triggers --routines -h test -p database | gzip -3 | ssh production "gunzip -c | mysql -h production_database_host -p production_database"

Этот выглядит классно (omg geek) и сохраняет много нажатий на файл дампа, но он подавляет предупреждения, которые вы можете увидеть при загрузке дампа из консоли

MySQL записывает следующие сведения о определителях триггеров:

Если вы укажете опцию DEFINER, эти правила определяют правовую Значения пользователя DEFINER:

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

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

Хотя возможно создать триггер с несуществующим DEFINER учетной записи, это не очень хорошая идея для активации таких триггеров до тех пор, пока учетная запись действительно существует. В противном случае поведение с уважением для проверки привилегий undefined.

Источник: http://dev.mysql.com/doc/refman/5.5/en/create-trigger.html

Ошибка III: ленивый

У меня очень классная оболочка mysqldump, которая способна генерировать чистые, повторно используемые файлы дампа. При перезаписывании триггеров без DEFINER у меня была консольная транзакция (блокировка table2), открытая на рабочем сервере, поэтому триггеры на таблице2 вообще не обновлялись, но опять же, из-за моего конвейера sql-данных на 5 серверах я не видел таймаут ошибка.

Вывод:

Не было ошибок, только триггеры не были созданы правильно.

Иногда вам стоит перестать быть ленивым, давая важные вещи бит больше времени, а внимание может сэкономить вам много времени

Ответ 2

Триггеры в MySQL (в отличие от хранимых процедур) всегда запускаются в контексте DEFINER. Возможно, триггеры не работают, потому что DEFINER не имеет прав на выполнение какого-либо или всего триггера. В частности, в MySQL 5.1 и более поздних версиях DEFINER должна быть привилегия TRIGGER, а также соответствующие привилегии SELECT и/или UPDATE.

Если триггеры не работают, проверьте привилегии.