Details
-
Type:
Bug
-
Status: Open
-
Priority:
Critical
-
Resolution: Unresolved
-
Affects Version/s: 10.0.21, 10.0.21-galera
-
Fix Version/s: None
-
Component/s: Triggers
-
Labels:
Description
In MySQL docs:
https://dev.mysql.com/doc/refman/5.6/en/create-trigger.html
I read:
"INSERT: The trigger activates whenever a new row is inserted into the table; for example, through INSERT, LOAD DATA, and REPLACE statements."
In MariaDB, it doesn't seem to be always the case. I didn't try all types of triggers, or all versions, or MySQL. But see this test case.
MariaDB [test]> \! cat /tmp/test.csv
1,2,3
4,5,6
7,8,9
SET @trigger_invocations := 0;
USE test;
CREATE OR REPLACE TABLE t (
a INT,
b INT,
c INT
)
ENGINE = MyISAM
;
DELIMITER ||
CREATE TRIGGER t_ai
AFTER INSERT
ON t
FOR EACH ROW
BEGIN
SET @trigger_invocations := @trigger_invocations + 1;
END ||
DELIMITER ;
LOAD DATA LOCAL INFILE '/tmp/test.csv'
INTO TABLE t
FIELDS TERMINATED BY ','
;
SELECT @trigger_invocations;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Works for me.
MariaDB [test]> CREATE OR REPLACE TABLE t (
-> a INT,
-> b INT,
-> c INT
-> )
-> ENGINE = MyISAM
-> ;
Query OK, 0 rows affected (0.17 sec)
MariaDB [test]>
MariaDB [test]> delimiter ||
MariaDB [test]>
MariaDB [test]> CREATE TRIGGER t_ai
-> AFTER INSERT
-> ON t
-> FOR EACH ROW
-> BEGIN
-> SET @trigger_invocations := @trigger_invocations + 1;
-> END ||
Query OK, 0 rows affected (0.09 sec)
MariaDB [test]>
MariaDB [test]> delimiter ;
MariaDB [test]>
MariaDB [test]> LOAD DATA LOCAL INFILE '/home/elenst/tmp/test.csv'
-> INTO TABLE t
-> FIELDS TERMINATED BY ','
-> ;
Query OK, 3 rows affected (0.02 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
MariaDB [test]>
MariaDB [test]> SELECT @trigger_invocations;
+----------------------+
| @trigger_invocations |
+----------------------+
| 3 |
+----------------------+
1 row in set (0.00 sec)
MariaDB [test]> select @@version;
+-----------------+
| @@version |
+-----------------+
| 10.0.21-MariaDB |
+-----------------+
1 row in set (0.01 sec)
Federico Razzoli, could it be that when it failed for you, you ran the client without --local-infile, and the LOAD just did not work?
If not, could you please paste the output like above, but showing the wrong behavior, and attach your cnf file?
Thanks.
With BEFORE INSERT, the test case works as expected.