run trigger without writing to binary log

Question

I'm trying to insert rows into a table via a trigger or stored procedure without writing any data to the binary log. Is this possible? I know that for normal connections you can set SQL_LOG_BIN=0 to disable binary logging for the connection, but I haven't been able to get that to work for triggers. If there's a way to do this with a federated engine table, that would also be OK.

edit:

I can almost accomplish this via a stored procedure:

CREATE PROCEDURE nolog_insert(`id` INT, `data` blob)
BEGIN
  SET SESSION SQL_LOG_BIN = 0;
  INSERT INTO `table` (`id`, `data`) VALUES (id, data);
  SET SESSION SQL_LOG_BIN = 1;
END

I insert a record by calling the procedure from the mysql prompt:

call nolog_insert(50, 'notlogged');

As expected, the record (50, 'notlogged') is inserted into the table, but is not written to the binary log.

However, I want to run this procedure from a trigger. When using a trigger as follows:

create trigger my_trigger before insert on blackhole_table for each row call nolog_insert(new.id, new.data);

The data is both inserted to the table and written to the binary log.

You do know that most DBMSs use the log to track changes in case a transaction is rolled back, and if you don't write to the log and something fails you've broken that rollback capability? I'm not sure this applies to MySQL, but it's something to be concerned about IMO. — Ken White, Apr 04 '12 at 00:32
Yeah, I'm aware of the implications. It's backed by MyISAM and transactions are not used anyway. The insert shouldn't ever fail. (The stored procedure is being used as part of a system to work around the limitations of MySQL replication.) — A B, Apr 04 '12 at 00:37
It's not only the insert failing that's a problem. It's any operations around the insert during a transaction (IOW, anything between the start of the transaction and the point of failure can't be reverted, because the transaction integrity has been compromised). You might not be using transactions now, but what if that changes in the future and you miss changing the trigger? — Ken White, Apr 04 '12 at 00:43
The data store as a whole is being used as a single table key value store. We are not likely to want transactions at the MySQL level ever. — A B, Apr 04 '12 at 00:44
I use database replication, and if I make changes to the database via a connection to a different database, the changes do not replicate. To do it I just reference the database.table in the query. It seems like a bug to me. Marc — Marc Graham, Mar 31 '13 at 03:04

score 1 · Answer 1 · answered Mar 18 '14 at 19:17

If you run statement based replication triggers are executed on both the master and the slave but not replicated. Perhaps that could solve your problem.

Other than that, it's not allowed to change sql_log_bin inside a transaction so I would say that there is no good way to have the effects of a trigger not replicated when using row based replication.

run trigger without writing to binary log

1 Answers1