CODEWITHSUNDEEP
mysqltriggersmariadb
user3396065
user3396065

Reputation: 569

Warning Unsafe statement written to the binary log using statement

I've created a log system based on trigger.
Every time a row is inserted or updated the trigger store a new row in another table.

The trigger works fine but after some time I found this message in logs:

[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly. Statement: update `gl_item` set `is_shown` = '0', `updated_at` = '2016-03-21 16:56:28' where `list_id` = '1' and `is_shown` = '1'

I've already red some post related to this issue i like:
- MySQL Replication & Triggers

But I don't understand the nature of the problem.

What this warning mean?
I don't have to insert into auto increment column with triggers?
Which is the best way to create a log system in order to avoid this warning?

Update
Output of SHOW CREATE TABLE, this is the table where the trigger will enter THE rows.

gl_item_log | CREATE TABLE `gl_item_log` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Item log unique id',
  `item_id` bigint(20) unsigned DEFAULT NULL ,
  `updated_by` bigint(20) unsigned DEFAULT NULL ,
  `switch_shown` tinyint(4) DEFAULT NULL ,
  `switch_checked` tinyint(4) DEFAULT NULL ,
  `logged_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `logged_at_microtime` decimal(6,6) unsigned NOT NULL ,
  `logged_at_microtime_int` mediumint(8) unsigned NOT NULL DEFAULT '0' ,
  PRIMARY KEY (`id`),
  KEY `gl_item_log_updated_by_foreign` (`updated_by`),
  KEY `gl_item_log_item_id_updated_by_switch_shown_switch_checked_index` 
      (`item_id`,`updated_by`,`switch_shown`,`switch_checked`),
  CONSTRAINT `gl_item_log_item_id_foreign` 
      FOREIGN KEY (`item_id`) REFERENCES `gl_item` (`id`),
  CONSTRAINT `gl_item_log_updated_by_foreign` 
      FOREIGN KEY (`updated_by`) REFERENCES `gl_general_user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Could be a good idea drop the id column with the auto increment field in order to have logs entries without a unique identifier?

Thanks

Upvotes: 2

Views: 5181

Answers (1)

Matt S
Matt S

Reputation: 15374

What this warning mean?

According to the MySQL documentation:

A statement invoking a trigger (or function) that causes an update to an AUTO_INCREMENT column is not replicated correctly using statement-based replication. MySQL 5.7 marks such statements as unsafe. (Bug #45677)

With statement-based replication, the exact SQL which is run on your master database is also run on your slave(s). When your trigger is fired, if it exists on every one of your databases, it is run on each database and inserts into your log. This can be a tricky situation for your databases to remain in sync.

I don't have to insert into auto increment column with triggers?

Correct. It's never necessary to insert your own values into an auto-increment column.

Which is the best way to create a log system in order to avoid this warning?

First, either keep the trigger on every database and turn off replication for your log table, or have the trigger only on your master database and let replication copy the log table inserts to the other databases.

To work around this specific warning, configure your log table to have no auto-increment column. Your trigger can then insert into it and it shouldn't cause any replication warnings.

Another option is to switch to row-based replication. Then the trigger will only be fired automatically on the master and the auto-increment values will always replicate without issue.

Upvotes: 4

Related Questions