[MDEV-18466] Unsafe to log updates on tables referenced by foreign keys with triggers in statement format Created: 2019-02-04  Updated: 2019-03-27  Resolved: 2019-03-27

Status: Closed
Project: MariaDB Server
Component/s: Replication
Affects Version/s: 10.0, 10.1, 10.1.36, 10.2, 10.3, 10.4
Fix Version/s: 10.2.24, 10.1.39, 10.3.14, 10.4.4

Type: Bug Priority: Minor
Reporter: Martin Reissner Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None
Environment:

Debian GNU/Linux 9.7 (stretch) using Mariadb packages from Debian and from Mariadb repository

Attachments: File demodb.sql    

 Description   

After updating our Debian servers from 10.1.26 to 10.1.37 from the Debian Stable repos I noticed that some simple UPDATE statements suddenly caused issues by either triggering a warning when using binlog_format=statement or by being logged in row format when using binlog_format=mixed or row.

I investigated a bit and it showed that this behaviour started in 10.1.36 and is still prevalent in 10.3 and I found nothing in the changelogs so I decided to report it. I have attached a simple testcase that when run with binlogs enabled and everything else default on a 10.1 instance < 10.1.36 will run without issues but from 10.1.36 on will trigger the warning:

Note 1592 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.

It seems to boil down to the triggers/actions in the foreign key constraints referencing the updated table. When those are not there no warning is triggered and I can't see why this query might actually be unsafe to be logged in statement format as the actual foreign key column is not touched.
I know this behaviour usually won't break anything which is why I left it at minor priority, for me this caused quite a problem though as I have a table matching the testcase that gets updated frequently and with using binlog_format=mixed we suddenly faced 1GB of binlogs every 10 minutes instead of every 2h with statement format which filled up disks and caused a lot more io to happen.

 Comments   
Comment by Elena Stepanova [ 2019-02-06 ]

Thanks for the test case.
The difference was introduced by this commit (in 10.0):

commit 64a23c1c8a826a6f58f8a415f60a0e3cc0e0375f
 
Author: Sergei Golubchik <serg@mariadb.org>
 
Date:   Tue Jul 17 16:56:40 2018 +0200
 
 
 
    extend prelocking to FK-accessed tables
 
    
    Backport of f1362910980

I can't tell from the commit message or added test cases whether the effect on statement binary logging was intentional, so assigning to serg to clarify.

Comment by Martin Reissner [ 2019-03-18 ]

I don't want to rush anything or anybody, I'm just commenting to state that this is still an issue for us. We're still stuck with 10.1.26-0+deb9u1 and this also makes dist-upgrades of old Debian 8 servers difficult as upgrading to Debian 9 usually brings the latest Mariadb version which we have to downgrade to 10.1.26 before we can use it.

Generated at Thu Feb 08 08:44:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.