Bug #91646 xa command still operation when super_read_only is true
Submitted: 16 Jul 2018 4:40 Modified: 16 Jul 2018 6:48
Reporter: Zhenghu Wen (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: XA transactions Severity:S2 (Serious)
Version:5.7.22, 8.0.11 OS:Any
Assigned to: CPU Architecture:Any
Tags: xa

[16 Jul 2018 4:40] Zhenghu Wen
Description:
when using mysql xa transactions, i found that event if super_read_only is true, all xa commands will still return OK. but in the same situation  normal transaction will return ERROR when doing 'commit'.

How to repeat:
xa trx case:
session 1:
mysql>create database xatest;
Query OK, 1 row affected (0.01 sec)

mysql>use xatest;
Database changed
mysql>create table xatable (a int primary key);
Query OK, 0 rows affected (0.00 sec)

mysql>xa start '1';
Query OK, 0 rows affected (0.00 sec)

mysql>insert into xatable values (1);
Query OK, 1 row affected (0.01 sec)

mysql>xa end '1';
Query OK, 0 rows affected (0.00 sec)

session 2:
mysql>set global super_read_only=on;
Query OK, 0 rows affected (0.00 sec)

session 1:
mysql>xa prepare '1';
Query OK, 0 rows affected (0.00 sec)

mysql>xa commit '1';
Query OK, 0 rows affected (0.01 sec)

normal trx case:
session 1:
mysql>set global super_read_only=off;
Query OK, 0 rows affected (0.00 sec)

mysql>begin;
Query OK, 0 rows affected (0.00 sec)

mysql>insert into xatable values (11);
Query OK, 1 row affected (0.00 sec)

session 2:
mysql>set global super_read_only=on;
Query OK, 0 rows affected (0.00 sec)

session 1:
mysql>commit;
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

session 2:
mysql>set global super_read_only=off;
Query OK, 0 rows affected (0.00 sec)

session 1:
mysql>commit;
Query OK, 0 rows affected (0.00 sec)

Suggested fix:
just like commit. if super_read_only is true some xa commands(xa prepare,xa commit) should return error with message "ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement"
[16 Jul 2018 6:48] MySQL Verification Team
Hello Zhenghu,

Thank you for the report and feedback.

Thanks,
Umesh
[19 Mar 2019 7:48] Song Libing
XA ROLLBACK has the same problem.
read_only variable has the same problem too. 
It mainly effects replication slave. 

--source include/have_binlog_format_row.inc
--source include/master-slave.inc
--source include/rpl_connection_slave.inc
CREATE USER user1;
GRANT ALL ON test.* TO user1;
SET global read_only = 1;

--connect(slave2, 127.0.0.1, user1,, test, $SLAVE_MYPORT)
--error 1290 # The MySQL server is running with the --read-only option
CREATE TABLE t1(c1 INT);

--source include/rpl_connection_master.inc
CREATE TABLE t1(c1 INT) ENGINE=InnoDB;
XA START "slb";
INSERT INTO t1 values(1);
XA END "slb";
XA PREPARE "slb";

sleep 1;
#--source include/sync_slave_sql_with_master.inc
--source include/rpl_connection_slave.inc

SELECT * FROM t1;
# XA COMMIT should be refused with error 1290
XA COMMIT "slb";
# XA ROLLBACK "slb"; has same problem
SELECT * FROM t1;
SHOW BINLOG EVENTS;
--source include/rpl_end.inc