Uploaded image for project: 'Percona Server'
  1. Percona Server
  2. PS-891

LP #1441259: super_read_only mode breaks replication with some queries

    Details

    • Type: Bug
    • Status: Done
    • Priority: High
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None

      Description

      **Reported in Launchpad by Arturas Moskvinas last update 03-02-2016 11:04:03

      We're using super_read_only mode on slaves to avoid accidental data changes/corruptions if modification query is executed on replica directly. It was working fine with replication and yesterday replication stopped with such error:
      Last_SQL_Error: Error 'The MySQL server is running with the --read-only (super) option so it cannot execute this statement' on query. Default database: 'somerandomdb'. Query:
      'UPDATE tbl INNER JOIN tbl_postfix on tbl.id_ = tbl_postfix.tbl_id SET is_visible = 1 WHERE hex(tbl_postfix.postfix_id) LIKE 'yadada%' AND tbl.is_visible = 0'

      Table definitions:
      CREATE TABLE `tbl` (
      `created` datetime NOT NULL,
      `updated` datetime NOT NULL,
      `id_` binary(16) NOT NULL,
      `updated_by` varchar(191) DEFAULT NULL,
      `deleted` datetime DEFAULT NULL,
      `type_` varchar(191) NOT NULL,
      `parent_id` binary(16) DEFAULT NULL,
      `user_type` varchar(191) NOT NULL,
      `device_type` varchar(191) DEFAULT NULL,
      `issue_type` varchar(191) DEFAULT NULL,
      `is_visible` tinyint(1) DEFAULT NULL,
      `user_status` varchar(191) DEFAULT 'signed-in',
      PRIMARY KEY (`id_`),
      KEY `parent_id` (`parent_id`),
      KEY `ix_tbl_deleted` (`deleted`),
      CONSTRAINT `tbl_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `tbl` (`id_`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8

      CREATE TABLE `tbl_postfix` (
      `tbl_id` binary(16) NOT NULL,
      `postfix_id` binary(16) NOT NULL,
      `ordinal` int(11) DEFAULT NULL,
      PRIMARY KEY (`tbl_id`,`postfix_id`),
      KEY `ix_tbl_postfix_tbl_id` (`tbl_id`),
      KEY `ix_tbl_postfix_postfix_id` (`postfix_id`),
      CONSTRAINT `tbl_postfix_ibfk_1` FOREIGN KEY (`tbl_id`) REFERENCES `tbl` (`id_`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8

      Percona server version we use: 5.6.21-70.1-log, platform - ubuntu.

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                lpjirasync lpjirasync (Inactive)
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: