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.
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.
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.
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.
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
Sergei Golubchik
Martin Reissner
Votes:
0Vote for this issue
Watchers:
3Start 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.
{"report":{"fcp":739.8999999761581,"ttfb":136.69999992847443,"pageVisibility":"visible","entityId":72344,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":1,"journeyId":"d54cefde-669c-4f17-aa5a-d40550702027","navigationType":0,"readyForUser":797,"redirectCount":0,"resourceLoadedEnd":806.8999999761581,"resourceLoadedStart":142.09999990463257,"resourceTiming":[{"duration":139.70000004768372,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":142.09999990463257,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":142.09999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":281.7999999523163,"responseStart":0,"secureConnectionStart":0},{"duration":139.60000002384186,"initiatorType":"link","name":"https://jira.mariadb.org/s/7ebd35e77e471bc30ff0eba799ebc151-CDN/lu2bu7/820016/12ta74/8679b4946efa1a0bb029a3a22206fb5d/_/download/contextbatch/css/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.css?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&slack-enabled=true","startTime":142.29999995231628,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":142.29999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":281.89999997615814,"responseStart":0,"secureConnectionStart":0},{"duration":148.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/fbf975c0cce4b1abf04784eeae9ba1f4-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":142.5,"connectEnd":142.5,"connectStart":142.5,"domainLookupEnd":142.5,"domainLookupStart":142.5,"fetchStart":142.5,"redirectEnd":0,"redirectStart":0,"requestStart":142.5,"responseEnd":291,"responseStart":291,"secureConnectionStart":142.5},{"duration":214.30000007152557,"initiatorType":"script","name":"https://jira.mariadb.org/s/94c15bff32baef80f4096a08aceae8bc-CDN/lu2bu7/820016/12ta74/c92c0caa9a024ae85b0ebdbed7fb4bd7/_/download/contextbatch/js/atl.global,-_super/batch.js?locale=en","startTime":142.59999990463257,"connectEnd":142.59999990463257,"connectStart":142.59999990463257,"domainLookupEnd":142.59999990463257,"domainLookupStart":142.59999990463257,"fetchStart":142.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":142.59999990463257,"responseEnd":356.89999997615814,"responseStart":356.89999997615814,"secureConnectionStart":142.59999990463257},{"duration":210.10000002384186,"initiatorType":"script","name":"https://jira.mariadb.org/s/099b33461394b8015fc36c0a4b96e19f-CDN/lu2bu7/820016/12ta74/8679b4946efa1a0bb029a3a22206fb5d/_/download/contextbatch/js/jira.browse.project,project.issue.navigator,jira.view.issue,jira.general,jira.global,atl.general,-_super/batch.js?agile_global_admin_condition=true&jag=true&jira.create.linked.issue=true&locale=en&slack-enabled=true","startTime":142.59999990463257,"connectEnd":142.59999990463257,"connectStart":142.59999990463257,"domainLookupEnd":142.59999990463257,"domainLookupStart":142.59999990463257,"fetchStart":142.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":142.59999990463257,"responseEnd":352.6999999284744,"responseStart":352.6999999284744,"secureConnectionStart":142.59999990463257},{"duration":214.70000004768372,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-en/jira.webresources:calendar-en.js","startTime":142.69999992847443,"connectEnd":142.69999992847443,"connectStart":142.69999992847443,"domainLookupEnd":142.69999992847443,"domainLookupStart":142.69999992847443,"fetchStart":142.69999992847443,"redirectEnd":0,"redirectStart":0,"requestStart":142.69999992847443,"responseEnd":357.39999997615814,"responseStart":357.39999997615814,"secureConnectionStart":142.69999992847443},{"duration":215.10000002384186,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:calendar-localisation-moment/jira.webresources:calendar-localisation-moment.js","startTime":142.79999995231628,"connectEnd":142.79999995231628,"connectStart":142.79999995231628,"domainLookupEnd":142.79999995231628,"domainLookupStart":142.79999995231628,"fetchStart":142.79999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":142.79999995231628,"responseEnd":357.89999997615814,"responseStart":357.89999997615814,"secureConnectionStart":142.79999995231628},{"duration":215.60000002384186,"initiatorType":"link","name":"https://jira.mariadb.org/s/b04b06a02d1959df322d9cded3aeecc1-CDN/lu2bu7/820016/12ta74/a2ff6aa845ffc9a1d22fe23d9ee791fc/_/download/contextbatch/css/jira.global.look-and-feel,-_super/batch.css","startTime":142.89999997615814,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":142.89999997615814,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":358.5,"responseStart":0,"secureConnectionStart":0},{"duration":215.5,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":143,"connectEnd":143,"connectStart":143,"domainLookupEnd":143,"domainLookupStart":143,"fetchStart":143,"redirectEnd":0,"redirectStart":0,"requestStart":143,"responseEnd":358.5,"responseStart":358.5,"secureConnectionStart":143},{"duration":215.90000009536743,"initiatorType":"link","name":"https://jira.mariadb.org/s/3ac36323ba5e4eb0af2aa7ac7211b4bb-CDN/lu2bu7/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/css/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.css?jira.create.linked.issue=true","startTime":143.09999990463257,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":143.09999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":359,"responseStart":0,"secureConnectionStart":0},{"duration":215.79999995231628,"initiatorType":"script","name":"https://jira.mariadb.org/s/3339d87fa2538a859872f2df449bf8d0-CDN/lu2bu7/820016/12ta74/d176f0986478cc64f24226b3d20c140d/_/download/contextbatch/js/com.atlassian.jira.projects.sidebar.init,-_super,-project.issue.navigator,-jira.view.issue/batch.js?jira.create.linked.issue=true&locale=en","startTime":143.29999995231628,"connectEnd":143.29999995231628,"connectStart":143.29999995231628,"domainLookupEnd":143.29999995231628,"domainLookupStart":143.29999995231628,"fetchStart":143.29999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":143.29999995231628,"responseEnd":359.09999990463257,"responseStart":359,"secureConnectionStart":143.29999995231628},{"duration":662.2999999523163,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-js/jira.webresources:bigpipe-js.js","startTime":143.89999997615814,"connectEnd":143.89999997615814,"connectStart":143.89999997615814,"domainLookupEnd":143.89999997615814,"domainLookupStart":143.89999997615814,"fetchStart":143.89999997615814,"redirectEnd":0,"redirectStart":0,"requestStart":143.89999997615814,"responseEnd":806.1999999284744,"responseStart":806.1999999284744,"secureConnectionStart":143.89999997615814},{"duration":663,"initiatorType":"script","name":"https://jira.mariadb.org/s/d41d8cd98f00b204e9800998ecf8427e-CDN/lu2bu7/820016/12ta74/1.0/_/download/batch/jira.webresources:bigpipe-init/jira.webresources:bigpipe-init.js","startTime":143.89999997615814,"connectEnd":143.89999997615814,"connectStart":143.89999997615814,"domainLookupEnd":143.89999997615814,"domainLookupStart":143.89999997615814,"fetchStart":143.89999997615814,"redirectEnd":0,"redirectStart":0,"requestStart":143.89999997615814,"responseEnd":806.8999999761581,"responseStart":806.8999999761581,"secureConnectionStart":143.89999997615814},{"duration":201.5,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":500.59999990463257,"connectEnd":500.59999990463257,"connectStart":500.59999990463257,"domainLookupEnd":500.59999990463257,"domainLookupStart":500.59999990463257,"fetchStart":500.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":500.59999990463257,"responseEnd":702.0999999046326,"responseStart":702.0999999046326,"secureConnectionStart":500.59999990463257}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":6,"responseStart":136,"responseEnd":139,"domLoading":139,"domInteractive":851,"domContentLoadedEventStart":851,"domContentLoadedEventEnd":888,"domComplete":1222,"loadEventStart":1222,"loadEventEnd":1222,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":829.3999999761581},{"name":"bigPipe.sidebar-id.end","time":830.1999999284744},{"name":"bigPipe.activity-panel-pipe-id.start","time":830.2999999523163},{"name":"bigPipe.activity-panel-pipe-id.end","time":831.7999999523163},{"name":"activityTabFullyLoaded","time":904.5}],"measures":[],"correlationId":"b007504be3e7b9","effectiveType":"4g","downlink":10,"rtt":0,"serverDuration":79,"dbReadsTimeInMs":10,"dbConnsTimeInMs":16,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}
Bug
Minor
Thanks for the test case.
The difference was introduced by this commit (in 10.0):
commit 64a23c1c8a826a6f58f8a415f60a0e3cc0e0375fAuthor: Sergei Golubchik <serg@mariadb.org>Date: Tue Jul 17 16:56:40 2018 +0200extend prelocking to FK-accessed tablesBackport of f1362910980I 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.