super_read_only mode breaks replication with some queries
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.5 |
Invalid
|
Undecided
|
Unassigned | |||
5.6 |
Fix Released
|
High
|
Yura Sorokin | |||
5.7 |
Invalid
|
High
|
Yura Sorokin |
Bug Description
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_
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`
KEY `ix_tbl_
KEY `ix_tbl_
CONSTRAINT `tbl_postfix_
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Percona server version we use: 5.6.21-70.1-log, platform - ubuntu.
tags: | added: super-read-only |
Changed in percona-server: | |
assignee: | nobody → Muhammad Irfan (muhammad-irfan) |
Changed in percona-server: | |
status: | Incomplete → New |
tags: | added: i63567 |
I did tried to reproduce it with no luck so far. Can you please provide my.cnf file from both servers i.e. master and slave.
Further, is the slave stops only on join query with update statement ? Also, provide slave server error log to check further.