I'm removing about 200GB of blobs from my servers in a galera cluster (don't get me started on them being in the database to begin). I have had `binlog_row_image` set to `MINIMAL` for at least several months.
The rows I'm updating to remove the blobs are in a table with an `INT` PK called `id` and a `MEDIUMTEXT` column called `message`. For consistency for now, instead of deleting the rows, I am updating the `messsage` to `'s3'` (we are storing the data on S3 now).
I assumed that with `binlog_row_image` set to `MINIMAL` the binlog would only store the old PK (`id`) and the new blob (`message`), but the size of the newly written binlogs is consistent with the amount of data that is being removed and `mysqlbinlog` shows the full old blob. I also tried using `NOBLOB` for testing, but the binlogs looked the same.
I thought it might be that galera caused the full image to be written but our async slaves with basically the same config are also writing the full image.
I have attached the config for one of the servers in the galera cluster, the others are basically the same.
I have moved to updating a similar table as part of the same migration. The table structure is different, but I'm still performing basically the same operation. Here is the create statement for it:
CREATE TABLE `contacts_messages` (
|
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
`acctID` INT(10) UNSIGNED NOT NULL,
|
`stamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
`activity_id` INT(10) UNSIGNED NOT NULL,
|
`user_id` INT(10) UNSIGNED NOT NULL,
|
`contact_id` INT(10) UNSIGNED NOT NULL,
|
`subject` VARCHAR(255) NOT NULL,
|
`message` TEXT NOT NULL,
|
`to_email` VARCHAR(255) NULL DEFAULT NULL,
|
`cc_email` VARCHAR(255) NULL DEFAULT NULL,
|
`attachments` TEXT NULL,
|
`opened_at` TIMESTAMP NULL DEFAULT NULL,
|
PRIMARY KEY (`id`),
|
INDEX `contact_id` (`contact_id`),
|
INDEX `acctID` (`acctID`)
|
)
|
COLLATE='utf8_general_ci'
|
ENGINE=InnoDB
|
ROW_FORMAT=DYNAMIC
|
AUTO_INCREMENT=56049803
|
;
|
This is a sample of the update query I'm running. `contacts_messages_upload_status` is a table I was using to track which blobs were uploaded to S3.
UPDATE `contacts_messages` SET `message` = 's3' WHERE message != 's3' AND id >= 12109100 AND id < 12109200 AND id IN (SELECT id FROM contacts_messages_upload_status WHERE id >= 12109100 AND id < 12109200)
|
I added `SELECT @@binlog_row_image` to the top of the script I'm running to do these updates and it does return `MINIMAL`.
- duplicates
-
MDEV-14605
ON UPDATE CURRENT_TIMESTAMP fields by multi-table UPDATE are not logged with binlog_row_image=MINIMAL
-
-
Closed
{"report":{"fcp":811.4000000953674,"ttfb":138,"pageVisibility":"visible","entityId":60398,"key":"jira.project.issue.view-issue","isInitial":true,"threshold":1000,"elementTimings":{},"userDeviceMemory":8,"userDeviceProcessors":64,"apdex":1,"journeyId":"124e711c-f8ed-445b-b581-a024a62330d0","navigationType":0,"readyForUser":880.7999999523163,"redirectCount":0,"resourceLoadedEnd":851.7999999523163,"resourceLoadedStart":143.29999995231628,"resourceTiming":[{"duration":51,"initiatorType":"link","name":"https://jira.mariadb.org/s/2c21342762a6a02add1c328bed317ffd-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/css/_super/batch.css","startTime":143.29999995231628,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":143.29999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":194.29999995231628,"responseStart":0,"secureConnectionStart":0},{"duration":201.90000009536743,"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":143.59999990463257,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":143.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":345.5,"responseStart":0,"secureConnectionStart":0},{"duration":210.5,"initiatorType":"script","name":"https://jira.mariadb.org/s/fbf975c0cce4b1abf04784eeae9ba1f4-CDN/lu2bu7/820016/12ta74/0a8bac35585be7fc6c9cc5a0464cd4cf/_/download/contextbatch/js/_super/batch.js?locale=en","startTime":143.70000004768372,"connectEnd":143.70000004768372,"connectStart":143.70000004768372,"domainLookupEnd":143.70000004768372,"domainLookupStart":143.70000004768372,"fetchStart":143.70000004768372,"redirectEnd":0,"redirectStart":0,"requestStart":143.70000004768372,"responseEnd":354.2000000476837,"responseStart":354.09999990463257,"secureConnectionStart":143.70000004768372},{"duration":270,"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":143.79999995231628,"connectEnd":143.79999995231628,"connectStart":143.79999995231628,"domainLookupEnd":143.79999995231628,"domainLookupStart":143.79999995231628,"fetchStart":143.79999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":143.79999995231628,"responseEnd":413.7999999523163,"responseStart":413.7999999523163,"secureConnectionStart":143.79999995231628},{"duration":273.5,"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":143.90000009536743,"connectEnd":143.90000009536743,"connectStart":143.90000009536743,"domainLookupEnd":143.90000009536743,"domainLookupStart":143.90000009536743,"fetchStart":143.90000009536743,"redirectEnd":0,"redirectStart":0,"requestStart":143.90000009536743,"responseEnd":417.40000009536743,"responseStart":417.40000009536743,"secureConnectionStart":143.90000009536743},{"duration":274.5,"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":144,"connectEnd":144,"connectStart":144,"domainLookupEnd":144,"domainLookupStart":144,"fetchStart":144,"redirectEnd":0,"redirectStart":0,"requestStart":144,"responseEnd":418.5,"responseStart":418.5,"secureConnectionStart":144},{"duration":274.90000009536743,"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":144.09999990463257,"connectEnd":144.09999990463257,"connectStart":144.09999990463257,"domainLookupEnd":144.09999990463257,"domainLookupStart":144.09999990463257,"fetchStart":144.09999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":144.09999990463257,"responseEnd":419,"responseStart":419,"secureConnectionStart":144.09999990463257},{"duration":275.39999985694885,"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":144.20000004768372,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":144.20000004768372,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":419.59999990463257,"responseStart":0,"secureConnectionStart":0},{"duration":275.40000009536743,"initiatorType":"script","name":"https://jira.mariadb.org/rest/api/1.0/shortcuts/820016/47140b6e0a9bc2e4913da06536125810/shortcuts.js?context=issuenavigation&context=issueaction","startTime":144.29999995231628,"connectEnd":144.29999995231628,"connectStart":144.29999995231628,"domainLookupEnd":144.29999995231628,"domainLookupStart":144.29999995231628,"fetchStart":144.29999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":144.29999995231628,"responseEnd":419.7000000476837,"responseStart":419.7000000476837,"secureConnectionStart":144.29999995231628},{"duration":275.7999999523163,"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":144.5,"connectEnd":0,"connectStart":0,"domainLookupEnd":0,"domainLookupStart":0,"fetchStart":144.5,"redirectEnd":0,"redirectStart":0,"requestStart":0,"responseEnd":420.2999999523163,"responseStart":0,"secureConnectionStart":0},{"duration":275.80000019073486,"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":144.59999990463257,"connectEnd":144.59999990463257,"connectStart":144.59999990463257,"domainLookupEnd":144.59999990463257,"domainLookupStart":144.59999990463257,"fetchStart":144.59999990463257,"redirectEnd":0,"redirectStart":0,"requestStart":144.59999990463257,"responseEnd":420.40000009536743,"responseStart":420.40000009536743,"secureConnectionStart":144.59999990463257},{"duration":482.7000000476837,"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":145.20000004768372,"connectEnd":145.20000004768372,"connectStart":145.20000004768372,"domainLookupEnd":145.20000004768372,"domainLookupStart":145.20000004768372,"fetchStart":145.20000004768372,"redirectEnd":0,"redirectStart":0,"requestStart":145.20000004768372,"responseEnd":627.9000000953674,"responseStart":627.9000000953674,"secureConnectionStart":145.20000004768372},{"duration":706.5,"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":145.29999995231628,"connectEnd":145.29999995231628,"connectStart":145.29999995231628,"domainLookupEnd":145.29999995231628,"domainLookupStart":145.29999995231628,"fetchStart":145.29999995231628,"redirectEnd":0,"redirectStart":0,"requestStart":145.29999995231628,"responseEnd":851.7999999523163,"responseStart":851.7000000476837,"secureConnectionStart":145.29999995231628},{"duration":153.89999985694885,"initiatorType":"xmlhttprequest","name":"https://jira.mariadb.org/rest/webResources/1.0/resources","startTime":565.7000000476837,"connectEnd":565.7000000476837,"connectStart":565.7000000476837,"domainLookupEnd":565.7000000476837,"domainLookupStart":565.7000000476837,"fetchStart":565.7000000476837,"redirectEnd":0,"redirectStart":0,"requestStart":565.7000000476837,"responseEnd":719.5999999046326,"responseStart":719.5999999046326,"secureConnectionStart":565.7000000476837}],"fetchStart":0,"domainLookupStart":0,"domainLookupEnd":0,"connectStart":0,"connectEnd":0,"requestStart":16,"responseStart":138,"responseEnd":140,"domLoading":141,"domInteractive":942,"domContentLoadedEventStart":942,"domContentLoadedEventEnd":992,"domComplete":2077,"loadEventStart":2077,"loadEventEnd":2078,"userAgent":"Mozilla/5.0 AppleWebKit/537.36 (KHTML, like Gecko; compatible; ClaudeBot/1.0; +claudebot@anthropic.com)","marks":[{"name":"bigPipe.sidebar-id.start","time":922.9000000953674},{"name":"bigPipe.sidebar-id.end","time":923.5999999046326},{"name":"bigPipe.activity-panel-pipe-id.start","time":923.7000000476837},{"name":"bigPipe.activity-panel-pipe-id.end","time":926.2000000476837},{"name":"activityTabFullyLoaded","time":1005}],"measures":[],"correlationId":"ae2333b9107a3c","effectiveType":"4g","downlink":9.4,"rtt":0,"serverDuration":69,"dbReadsTimeInMs":9,"dbConnsTimeInMs":15,"applicationHash":"9d11dbea5f4be3d4cc21f03a88dd11d8c8687422","experiments":[]}}