old - trigger statement mysql

MySQL 5.5.30 desencadenadores en cascada no funcionan

Question

Por alguna razón, en una máquina MySQL 5.5.30, un desencadenador que elimina una fila de una segunda tabla ya no activa el desencadenador de eliminación en la segunda tabla.

Esto funciona perfectamente en nuestra versión local de MySQL 5.5.25

No encontré ninguna documentación que explicara este comportamiento, ¿alguien puede tener un problema igual?

Este es un error que ocurre en la versión de MySQL mayor que 5.5.25 o una "característica" que se habilita por accidente.

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)

OK aquí mis pasos de reproducción:

Base de datos

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

Mesas

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;

Disparadores

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 ;

P: ¿Por qué citas los identificadores usando comillas dobles? (en lugar de backticks)

Porque no me gusta la "sintaxis de nicho"

    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)

Testcase 1: comportamiento esperado (base de datos versión 5.2.20)

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

mysql> SET GLOBAL general_log := ON;

probar el gatillo de inserción

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"

contenido esperado de la tabla:

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)

Prueba de actualización activada, activada

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"

contenido esperado de la tabla:

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)

Prueba de actualización activada, configurada inactiva

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"

contenido esperado de la tabla:

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: comportamiento inesperado (MySQL Versión 5.5.30)

Holy triggers grml - ¿Sabes qué? Es una pena que no haya probado primero el segundo caso, desafortunadamente no pude reproducir el error. La prueba también funcionó el 5.5.30 y te mantendré actualizado :)

El desencadenador EDIT no se colocó en cascada debido a un definidor desconocido que permanecía en el volcado sql realizado para la producción. Eliminar DEFINER = en los volcados de activación (la solución alternativa sería crear el usuario o cambiar DEFINER = a uno existente) resolvió el problema , resolvió una parte del problema.

El definidor desconocido no produjo ninguna salida de archivo de registro

Answer 1

Los disparadores en MySQL (a diferencia de los procedimientos almacenados) siempre se ejecutan en el contexto del DEFINER . Los desencadenadores pueden parecer que no funcionan porque el DEFINER no tiene permisos para ejecutar algunos o todos los desencadenantes. En particular, en MySQL 5.1 y posterior, el DEFINER necesita tener el privilegio TRIGGER así como los privilegios relevantes de SELECT y / o UPDATE .

Cuando los desencadenadores no parecen funcionar, verifique los privilegios.

Answer 2

Conclusión final: MySQL 5.5.30 no tiene errores en este caso, tampoco hubo una configuración incorrecta del servidor.

Varios errores cometidos por uno mismo causaron el problema:

Error I: el usuario de DEFINER no existía

En lugar de simplemente generar la base de datos en la máquina de producción, me quedé vago y descargué la base de datos de prueba a la máquina de producción. Si no establece explícitamente un DEFINER en su instrucción CREATE TRIGGER , se establece en CURRENT_USER . Desafortunadamente, este CURRENT_USER exacto en mi máquina de prueba no existe en el servidor de producción.

Error II: ser flojo

mysqldump volca la definición del desencadenador con DEFINER y crear el desencadenador debería generar una advertencia, pero nuevamente, era flojo e hice algo como esto ...

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

Esto se ve genial (omg geek) y te ahorra mucho empuje de archivo de volcado, pero suprime las advertencias que puedes ver cuando cargas el volcado desde dentro de la consola

MySQL escribe lo siguiente sobre los definidores de desencadenadores:

Si especifica la cláusula DEFINER, estas reglas determinan los valores de usuario del DEFINER legal:

Si no tiene el privilegio SUPER, el único valor de usuario legal es su propia cuenta, ya sea que se especifique literalmente o mediante CURRENT_USER. No puede configurar el definidor para alguna otra cuenta.

Si tiene el privilegio SUPER, puede especificar cualquier nombre de cuenta sintácticamente legal. Si la cuenta no existe en realidad, se genera una advertencia.

Aunque es posible crear un disparador con una cuenta DEFINER inexistente, no es una buena idea que dichos desencadenantes se activen hasta que la cuenta realmente exista. De lo contrario, el comportamiento con respecto a la verificación de privilegios no está definido.

Fuente: http://dev.mysql.com/doc/refman/5.5/en/create-trigger.html

Error III: ser flojo

Tengo un contenedor mysqldump muy atractivo, que puede generar archivos de volcado limpios y reutilizables. Al sobrescribir los desencadenantes sin DEFINER tuve una transacción de consola (bloqueando table2) abierta en el servidor de producción, por lo que los desencadenadores en table2 no se actualizaron, pero debido a mi data sql pipeline en 5 servidores no vi el tiempo de espera error.

Conclusión:

No hubo errores, solo los disparadores no se crearon correctamente.

¡Algunas veces deberías dejar de ser perezoso, darle a las cosas importantes un poco más de tiempo y la atención puede ahorrarte mucho tiempo !