Is it possible with a trigger to get the query or primary id of the query that triggered it?
Currently we roughly have:
Delete from Table1 where id = 1
BEFORE DELETE ON
CREATE TRIGGER Table1_Row_Being_Deleted
BEFORE DELETE ON Table1
INSERT INTO deleted_Table1 (deleting_date, tableid) values(now(), ?);
There is no direct link between the trigger and the query that triggered the trigger. But you can use the
performance schema to search for all active queries and log them. One of them should be the caller's query.
create trigger Table1_Row_Being_Deleted after delete on Table1 for each row insert into deleted_Table1(id, dt, user, qry) select old.id, now(), user(), performance_schema.events_statements_current.sql_text from performance_schema.events_statements_current;
This will log every active query, and thus a lot of noise, because the correct query is not known. Since it can be an indirect query (e.g. from a procedure), the correct query will not always have a distinctive part like 'delete from table1' in it. So look for common queries that will be in the logtable every time your problem occurs.
I used an
after delete trigger here, so it will only log when
delete succeeded, you might want to use a
before delete trigger to log even if the
delete will fail later (e.g. because of foreign key constraint).
old.id (and the whole row
old) contains the values the row had before it was deleted (so you can still use it here to log the entry).
performance schema and the
events_statements_current-log are usually enabled by default. Check the result from
select * from performance_schema.events_statements_current;
It should at least contain a row with this
select-query itself (since that was an active query at that time). If it is empty (or you have no permission to use that or it doesn't exists), you should check if
show variables like 'performance_schema'; will show
ON. And you might need to set permissions or some logging options, see Query Profiling Using Performance Schema.