Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-12162

binlog_row_image minimal Not Working

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Duplicate
    • 10.1.17, 10.1(EOL), 10.2(EOL)
    • N/A
    • Replication
    • 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

        1. binlog_row_image.txt
          2 kB
          Claudio Nanni
        2. my.cnf
          5 kB
          Brad Jorgensen
        3. mysqlbinlog_binlog_row_image_minimal.txt
          14 kB
          Brad Jorgensen

        Issue Links

          Activity

            People

              Elkin Andrei Elkin
              bradjorgensen Brad Jorgensen
              Votes:
              1 Vote for this issue
              Watchers:
              6 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.