Bug #83227 Concurrent trigger creation causes errors and corrupts the trigger
Submitted: 30 Sep 2016 16:10 Modified: 16 Nov 2016 6:12
Reporter: Elena Stepanova Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.5, 5.6, 5.7, 5.6.33 OS:Any
Assigned to: CPU Architecture:Any

[30 Sep 2016 16:10] Elena Stepanova
Description:
The concurrent test from 'How to repeat' section reports that the server returns an error like

CREATE TRIGGER tr AFTER INSERT ON t2 FOR EACH ROW SET @a=1 failed: 7 Error on rename of './test/tr.TRN~' to './test/tr.TRN' (Errcode: 2 - No such file or directory)

Further attempt to deal with the trigger reveals problems. The trigger can be seen via SHOW TRIGGERS:

MySQL [test]> show triggers;
+---------+--------+-------+-----------+--------+---------+------------------------+----------------+----------------------+----------------------+--------------------+
| Trigger | Event  | Table | Statement | Timing | Created | sql_mode               | Definer        | character_set_client | collation_connection | Database Collation |
+---------+--------+-------+-----------+--------+---------+------------------------+----------------+----------------------+----------------------+--------------------+
| tr      | INSERT | t1    | SET @a=1  | AFTER  | NULL    | NO_ENGINE_SUBSTITUTION | root@127.0.0.1 | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |
+---------+--------+-------+-----------+--------+---------+------------------------+----------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

but not via SHOW CREATE TRIGGER:
MySQL [test]> show create trigger tr;
ERROR 1360 (HY000): Trigger does not exist

It can't be dropped, either. 

All that because the trigger information went out of sync. 
The TRN file says the trigger belongs to t2:

$ cat tr.TRN 
TYPE=TRIGGERNAME
trigger_table=t2

but the TRG file belongs to t1:

$ ls -l *.TRG
-rw-rw---- 1 elenst elenst 255 Sep 30 19:02 t1.TRG

It's obviously an old problem, but I can't find the corresponding bug report. I'll appreciate if you point at one. 

How to repeat:
# Grammar 1.yy

query_init:
    CREATE TABLE IF NOT EXISTS t1 (i INT); CREATE TABLE t2 LIKE t1;
    
my_table:
    t1 | t2 ;
    
query:
      CREATE TRIGGER tr AFTER INSERT ON my_table FOR EACH ROW SET @a=1
    | DROP TRIGGER tr
;

# End of grammar
# Command line

perl ./runall.pl --grammar=1.yy --threads=2 --skip-gendata --duration=60 --queries=100M --basedir=<basedir> --vardir=<vardir>
[3 Oct 2016 6:38] Umesh Shastry
Hello Elena,

Thank you for the report and test case.
Verified as described with 5.6.33 build.

Thanks,
Umesh
[16 Nov 2016 6:12] Erlend Dahl
Posted by developer:

[3 Oct 2016 5:15] Dmitry Shulga

This bug can't be reproduced in trunk since we migrated triggers support from
file-based approach to the new dd