Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-18466

Unsafe to log updates on tables referenced by foreign keys with triggers in statement format



    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 10.0, 10.1, 10.1.36, 10.2, 10.3, 10.4
    • 10.2.24, 10.1.39, 10.3.14, 10.4.4
    • Replication
    • None
    • Debian GNU/Linux 9.7 (stretch) using Mariadb packages from Debian and from Mariadb repository


      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.




            serg Sergei Golubchik
            mreissner Martin Reissner
            0 Vote for this issue
            3 Start watching this issue



              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.