Bug #91646 xa command still operation when super_read_only is true
Submitted: 16 Jul 4:40 Modified: 16 Jul 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 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 6:48] Umesh Shastry
Hello Zhenghu,

Thank you for the report and feedback.

Thanks,
Umesh