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

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 10.0, 10.1, 10.2, 10.3, 10.4, 10.1.36
    • Fix Version/s: 10.1.39, 10.2.24, 10.3.14, 10.4.4
    • Component/s: Replication
    • Labels:
      None
    • Environment:
      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

            People

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

              Dates

              • Created:
                Updated:
                Resolved: