Bug #115459 global read_only = 1 with read only = 0 on schema level blocks writes to schema
Submitted: 28 Jun 2024 16:07 Modified: 28 Jun 2024 18:03
Reporter: Justin Ewing Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0.34 OS:Ubuntu (20.04.6 LTS)
Assigned to: CPU Architecture:x86 (Intel(R) Xeon(R) CPU E5-2680 v4 @ 2.40GHz)

[28 Jun 2024 16:07] Justin Ewing
Description:
When global read_only = 1 and you execute ALTER DATABASE mydb READ ONLY = 0, the ALTER command completes successfully but the database is still read only. In information_schema.SCHEMATA_EXTENSIONS, all schemas EXCEPT the altered database show READ ONLY=1, however you CANNOT write to the schema.

How to repeat:
CREATE DATABASE mydb;
CREATE TABLE mydb.testtbl (testid INT NOT NULL, PRIMARY KEY (testid));
SET GLOBAL read_only = 1;
ALTER DATABASE mydb READ ONLY = 0;
SELECT * FROM information_schema.SCHEMATA_EXTENSIONS;
INSERT INTO mydb.testtbl (testid) VALUES (1);

Operation failed: There was an error while applying the SQL script to the database.
ERROR 1290: 1290: The MySQL server is running with the --read-only option so it cannot execute this statement

Suggested fix:
Honor the ALTER command executed against the schema to allow read/write access to that schema
[28 Jun 2024 18:03] MySQL Verification Team
HI Mr.  Ewing,

Thank you for your bug report.

However, we can not repeat it, neither with latest 8.0 release, which is 8.0.37, nor with 8.4.0.

We have inserted several rows in the table , done select and dropped the table and the schema:

ATALOG_NAME	SCHEMA_NAME	OPTIONS
def	mysql
def	information_schema
def	performance_schema
def	sys
def	test
def	query_rewrite
def	mydb
testid
1
2
3
4
5

And got the list of the schema after dropping it:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+

Can't repeat.