[MDEV-8818] Sometimes triggers are not invoked by LOAD DATA Created: 2015-09-19  Updated: 2015-10-19  Resolved: 2015-10-19

Status: Closed
Project: MariaDB Server
Component/s: Triggers
Affects Version/s: 10.0.21, 10.0.21-galera
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Federico Razzoli Assignee: Unassigned
Resolution: Cannot Reproduce Votes: 0
Labels: need_feedback


 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;



 Comments   
Comment by Federico Razzoli [ 2015-09-20 ]

With BEFORE INSERT, the test case works as expected.

Comment by Elena Stepanova [ 2015-09-20 ]

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)

f_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.

Comment by Elena Stepanova [ 2015-10-19 ]

Closing as "Cannot reproduce" for now; if you have more information, please comment to re-open the report.

Generated at Thu Feb 08 07:30:01 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.