[MDEV-24994] user can write data in read-only mode Created: 2021-02-26  Updated: 2022-03-19  Resolved: 2022-03-19

Status: Closed
Project: MariaDB Server
Component/s: Galera, Server
Affects Version/s: 10.4.18
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Yanjun Wu Assignee: Ramesh Sivaraman
Resolution: Incomplete Votes: 0
Labels: None
Environment:

centos8


 Description   

In galera cluster, set read-only and wait a moment, then user can write data in read-only mode .

MariaDB [nova]> set global read_only=1;
 
Query OK, 0 rows affected (0.061 sec)
 
 
 
MariaDB [nova]> select @@read_only;
 
+-------------+
 
| @@read_only |
 
+-------------+
 
|           1 |
 
+-------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [nova]> delete from tags;
 
ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement
 
MariaDB [nova]> insert into tags values ('11','aa');
 
ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement
 
MariaDB [nova]>
 
MariaDB [nova]>
 
MariaDB [nova]>
 
MariaDB [nova]> insert into tags values ('11','aa');
 
Query OK, 1 row affected (0.002 sec)



 Comments   
Comment by Elena Stepanova [ 2021-03-28 ]

Is it unabridged output from the client? The sequence of events is not very clear.
To set the global read_only variable, your current user must have SUPER privilege.
But if the user has SUPER privilege, it shouldn't be affected by read_only variable. So if anything, the first two failures are unexpected.

Comment by Yanjun Wu [ 2021-03-29 ]

I set the global read_only variable by root first.Then I use another user without SUPER privilege to run the sql command.When I set the global wsrep_on=0, the user without SUPER privilege can't write.But when I set the global wsrep_on=1 and have several running connections, the user without SUPER privilege can write.It is very strange.I think it may be a bug in galera cluster.

Comment by Ramesh Sivaraman [ 2022-02-02 ]

wadewds Galera works as expected in read-only mode. Galera cluster does not allow non-super users to write data. Can you share the privileges info from non-super user?

MariaDB [test]> select @@version,@@read_only,@@wsrep_on;
 
+-----------------+-------------+------------+
 
| @@version       | @@read_only | @@wsrep_on |
 
+-----------------+-------------+------------+
 
| 10.4.23-MariaDB |           1 |          1 |
 
+-----------------+-------------+------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> create table t1(id int);
 
ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement
 
MariaDB [test]> select user();
 
+--------------------+
 
| user()             |
 
+--------------------+
 
| testuser@localhost |
 
+--------------------+
 
1 row in set (0.006 sec)
 
 
 
MariaDB [test]> show grants;
 
+-------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Grants for testuser@%                                                                                                                           |
 
+-------------------------------------------------------------------------------------------------------------------------------------------------+
 
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON *.* TO `testuser`@`%` IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
 
+-------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.003 sec)
 
 
 
MariaDB [test]> 
 
 
sysbench /usr/share/sysbench/oltp_insert.lua  --mysql-storage-engine=innodb --table-size=10000 --tables=10 --threads=10 --mysql-db=test --mysql-user=testuser --mysql-password=test --db-driver=mysql --mysql-socket=/test/GAL_MD130122-mariadb-10.4.23-linux-x86_64-opt/node1/node1_socket.sock prepare | grep read-only
 
FATAL: mysql_drv_query() returned error 1290 (The MariaDB server is running with the --read-only option so it cannot execute this statement) for query 'CREATE TABLE sbtest8(
 
FATAL: mysql_drv_query() returned error 1290 (The MariaDB server is running with the --read-only option so it cannot execute this statement) for query 'CREATE TABLE sbtest1(
 
FATAL: mysql_drv_query() returned error 1290 (The MariaDB server is running with the --read-only option so it cannot execute this statement) for query 'CREATE TABLE sbtest9(
 
FATAL: mysql_drv_query() returned error 1290 (The MariaDB server is running with the --read-only option so it cannot execute this statement) for query 'CREATE TABLE sbtest2(
 
FATAL: `sysbench.cmdline.call_command' function failed: /usr/share/sysbench/oltp_common.lua:197: SQL error, errno = 1290, state = 'HY000': The MariaDB server is running with the --read-only option so it cannot execute this statement
 
FATAL: mysql_drv_query() returned error 1290 (The MariaDB server is running with the --read-only option so it cannot execute this statement) for query 'CREATE TABLE sbtest5(
 
FATAL: `sysbench.cmdline.call_command' function failed: /usr/share/sysbench/oltp_common.lua:197: SQL error, errno = 1290, state = 'HY000': The MariaDB server is running with the --read-only option so it cannot execute this statement
 
FATAL: mysql_drv_query() returned error 1290 (The MariaDB server is running with the --read-only option so it cannot execute this statement) for query 'CREATE TABLE sbtest7(
 
FATAL: `sysbench.cmdline.call_command' function failed: /usr/share/sysbench/oltp_common.lua:197: SQL error, errno = 1290, state = 'HY000': The MariaDB server is running with the --read-only option so it cannot execute this statement
 
FATAL: mysql_drv_query() returned error 1290 (The MariaDB server is running with the --read-only option so it cannot execute this statement) for query 'CREATE TABLE sbtest6(
 
FATAL: `sysbench.cmdline.call_command' function failed: /usr/share/sysbench/oltp_common.lua:197: SQL error, errno = 1290, state = 'HY000': The MariaDB server is running with the --read-only option so it cannot execute this statement
 
FATAL: mysql_drv_query() returned error 1290 (The MariaDB server is running with the --read-only option so it cannot execute this statement) for query 'CREATE TABLE sbtest4(
 
FATAL: mysql_drv_query() returned error 1290 (The MariaDB server is running with the --read-only option so it cannot execute this statement) for query 'CREATE TABLE sbtest3(
 
FATAL: mysql_drv_query() returned error 1290 (The MariaDB server is running with the --read-only option so it cannot execute this statement) for query 'CREATE TABLE sbtest10(
 
FATAL: `sysbench.cmdline.call_command' function failed: /usr/share/sysbench/oltp_common.lua:197: SQL error, errno = 1290, state = 'HY000': The MariaDB server is running with the --read-only option so it cannot execute this statement

Comment by Yanjun Wu [ 2022-02-10 ]

Here are the privileges info:

MariaDB [(none)]> show grants for 'nova'@'192.168.0.%';
 
+-----------------------------------------------------------------------------------------------------------------------------------+
 
| Grants for nova@192.168.0.%                                                                                                       |
 
+-----------------------------------------------------------------------------------------------------------------------------------+
 
| GRANT USAGE ON *.* TO `nova`@`192.168.0.%` IDENTIFIED VIA ed25519 USING 'psFonKNB7faYCidZIomzxeY6+nvuVLzP7SJXU/UPugQ' REQUIRE SSL |
 
| GRANT ALL PRIVILEGES ON `nova`.* TO `nova`@`192.168.0.%`                                                                          |
 
| GRANT ALL PRIVILEGES ON `nova_cell0`.* TO `nova`@`192.168.0.%`                                                                    |
 
+-----------------------------------------------------------------------------------------------------------------------------------+
 
3 rows in set (0.001 sec)

The user 'nova' can't write when i set read-only. But a few moment later , it can write data in read-only mode.

MariaDB [nova]> insert into tags values ('11','aa');
 
ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement
 
MariaDB [nova]>
 
MariaDB [nova]>
 
MariaDB [nova]> select @@version,@@read_only,@@wsrep_on;
 
+---------------------+-------------+------------+
 
| @@version           | @@read_only | @@wsrep_on |
 
+---------------------+-------------+------------+
 
| 10.4.22-MariaDB-log |           1 |          1 |
 
+---------------------+-------------+------------+
 
1 row in set (0.001 sec)
 
MariaDB [nova]> insert into tags values ('11','aa');
 
ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement
 
MariaDB [nova]> insert into tags values ('11','aa');
 
ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement
 
MariaDB [nova]> insert into tags values ('11','aa');
 
ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement
 
MariaDB [nova]>
 
MariaDB [nova]>
 
MariaDB [nova]> insert into tags values ('11','aa');
 
Query OK, 1 row affected (0.002 sec)
 
MariaDB [nova]> select @@version,@@read_only,@@wsrep_on;
 
+---------------------+-------------+------------+
 
| @@version           | @@read_only | @@wsrep_on |
 
+---------------------+-------------+------------+
 
| 10.4.22-MariaDB-log |           1 |          1 |
 
+---------------------+-------------+------------+

Comment by Ramesh Sivaraman [ 2022-02-14 ]

wadewds I had created a user similar to nova, but still could not reproduce the problem. Can you also share the configuration files from cluster nodes?

MariaDB [test]> insert into t1 values('11','tom');
 
ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement
 
MariaDB [test]> insert into t1 values('111','tom');
 
ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement
 
MariaDB [test]> show grants;
 
+---------------------------------------------------------------------------------------------------------------------------------+
 
| Grants for test@127.0.0.%                                                                                                       |
 
+---------------------------------------------------------------------------------------------------------------------------------+
 
| GRANT USAGE ON *.* TO `test`@`127.0.0.%` IDENTIFIED VIA ed25519 USING 'vtqh3zUpf2TlO+WL6v8WCihOA9rCPfAk6nhtFKpuGeo' REQUIRE SSL |
 
| GRANT ALL PRIVILEGES ON `test`.* TO `test`@`127.0.0.%`                                                                          |
 
| GRANT ALL PRIVILEGES ON `testone`.* TO `test`@`127.0.0.%`                                                                       |
 
+---------------------------------------------------------------------------------------------------------------------------------+
 
3 rows in set (0.000 sec)
 
 
 
MariaDB [test]> insert into t1 values('111','tom');
 
ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement
 
MariaDB [test]> insert into t1 values('111','tom');
 
ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement
 
MariaDB [test]> insert into t1 values('111','tom');
 
ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement
 
MariaDB [test]> insert into t1 values('111','tom');
 
ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement
 
MariaDB [test]>
 
MariaDB [test]> select @@version,@@read_only,@@wsrep_on;
 
+-----------------+-------------+------------+
 
| @@version       | @@read_only | @@wsrep_on |
 
+-----------------+-------------+------------+
 
| 10.4.22-MariaDB |           1 |          1 |
 
+-----------------+-------------+------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]>

Generated at Thu Feb 08 09:34:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.