0

I have created a stored procedure to insert data into an audit table. The procedure accepts an argument that takes table name and same value is inserted into the audit table along with PK.

When i'm calling this stored procedure from AFTER DELETE trigger, from multiple tables it is inserting duplicate values. Basically, same PK value is getting inserted for all table names in the audit table. So, if i delete a record from a table, number of inserts in audit table for single delete = number of triggers.

CREATE PROCEDURE sp_ins_log(
trigger_name VARCHAR(64),
column_name VARCHAR(64),
column_value VARCHAR(255),
requested_by VARCHAR(100))
INSERT INTO log (request_id,reference_schema, reference_table, reference_field, reference_id, action_performed, requested_by)
SELECT 
(SELECT trx_id FROM INFORMATION_SCHEMA.INNODB_TRX WHERE trx_mysql_thread_id = CONNECTION_ID()),
EVENT_OBJECT_SCHEMA, 
EVENT_OBJECT_TABLE, 
column_name,
column_value, 
CONCAT(ACTION_TIMING,'_',EVENT_MANIPULATION),
IFNULL(requested_by,CURRENT_USER()) 
FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME = trigger_name AND TRIGGER_SCHEMA = SCHEMA();
CREATE TRIGGER tr_table_ad
AFTER DELETE
ON table_name FOR EACH ROW
CALL sp_ins_log('table_name',old.id);

But, if i put the same insert query individually in each trigger, it works fine.

Reproduced the issue DB Fiddle

Compare this with direct execution of INSERT query inside SP.

7
  • @Akina I have updated but unable to reproduce in this environment. I'm running on MySQL version 5.7.27. Not sure is it because of FK relation between tables. But there is no cascade delete even with FK. May 23, 2020 at 19:09
  • Found something on MySQL docs. Replication Considerations Use of stored routines can cause replication problems. This issue is discussed further in [Section 24.7, “Stored Program Binary Logging”][1] The --replicate-wild-do-table=db_name.tbl_name option applies to tables, views, and triggers. It does not apply to stored procedures and functions, or events. To filter statements operating on the latter objects, use one or more of the --replicate-*-db options. [1]:https://dev.mysql.com/doc/refman/8.0/en/stored-programs-logging.html May 23, 2020 at 19:11
  • Nothing to do with constraints. Still happening after removing FK May 23, 2020 at 19:40
  • I have narrowed it down. It seems like issue is occuring when SELECTING from INFORMATION_SCHEMA.TRIGGERS inside store procedure. May 23, 2020 at 19:50
  • please add full stored procedure code May 23, 2020 at 20:34

1 Answer 1

0

The reason is simple - inaccuracy. Variable name and column name interference. You type WHERE TRIGGER_NAME = trigger_name. Both TRIGGER_NAME and trigger_name are treated as input variable name, and this condition is always true. You have 2 triggers matched the rest condition TRIGGER_SCHEMA = SCHEMA(), so query returns 2 rows.

Simply rename input variables. Or add table alias and allow the server to distinguish the column name and variable name: WHERE INFORMATION_SCHEMA.TRIGGERS.TRIGGER_NAME = trigger_name AND TRIGGER_SCHEMA = SCHEMA();.

1
  • What a blunder on my end. 🙈 Thanks for pointing out name collision. May 24, 2020 at 8:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.