Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Duplicate
-
10.1.17, 10.1(EOL), 10.2(EOL)
-
3 10.1.17 servers in galera cluster
Description
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`.
Attachments
Issue Links
- duplicates
-
MDEV-14605 ON UPDATE CURRENT_TIMESTAMP fields by multi-table UPDATE are not logged with binlog_row_image=MINIMAL
- Closed