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

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 10.1.36, 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
    • 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

    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.

      Attachments

        Activity

          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.

          elenst Elena Stepanova added a comment - 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.
          mreissner Martin Reissner added a comment - - edited

          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.

          mreissner Martin Reissner added a comment - - edited 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.

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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