[MDEV-16987] ALTER DATABASE possible in read-only mode Created: 2018-08-15  Updated: 2020-08-25  Resolved: 2018-12-13

Status: Closed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 5.5, 10.0, 10.1, 10.1.34, 10.3.8, 10.2, 10.3
Fix Version/s: 10.4.1, 10.1.38, 5.5.63, 10.0.38, 10.2.20, 10.3.12

Type: Bug Priority: Critical
Reporter: Hartmut Holzgraefe Assignee: Sergey Vojtovich
Resolution: Fixed Votes: 0
Labels: upstream-fixed

Issue Links:
Relates

 Description   

Test user has all privileges but SUPER, and read-only is set.
This gets detected correctly when trying to create a new database, or when trying to change the character set of an existing table:

MariaDB [test]> create database test2;
 
ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement
 
 
 
MariaDB [test]> alter table t1 character set latin1;
 
ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement

Changing the default character set of an existing database is possible though, it does not raise any error, and the changes effect even though the MariaDB instance is in read-only mode:

MariaDB [test]> alter database test character set latin1;
 
Query OK, 1 row affected (0.001 sec)
 
 
 
MariaDB [test]> show create database test;
 
+----------+-----------------------------------------------------------------+
 
| Database | Create Database                                                 |
 
+----------+-----------------------------------------------------------------+
 
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ |
 
+----------+-----------------------------------------------------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> alter database test character set utf8;
 
Query OK, 1 row affected (0.001 sec)
 
 
 
MariaDB [test]> show create database test;
 
+----------+---------------------------------------------------------------+
 
| Database | Create Database                                               |
 
+----------+---------------------------------------------------------------+
 
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
 
+----------+---------------------------------------------------------------+
 
1 row in set (0.000 sec)



 Comments   
Comment by Elena Stepanova [ 2018-08-29 ]

Also reproducible on MySQL 5.5-5.7, but not on 8.0.

Comment by Sergey Vojtovich [ 2018-12-11 ]

It is documented behaviour: https://mariadb.com/kb/en/library/server-system-variables/#read_only
Could you elaborate why it is expected to behave differently?

Comment by Hartmut Holzgraefe [ 2018-12-11 ]

Where do you see this documented in that KB section? It lists a few exceptions, but ALTER DATABASE is not among these as far as I can tell:

Inserting rows to log tables, updates to temporary tables and OPTIMIZE or ANALYZE TABLE statements are excluded from this limitation. From MariaDB 5.5, if read_only is set to 1, SET PASSWORD is limited only to users with the SUPER privilege.

I especially can't see why CREATE DATABASE and ALTER TABLE are blocked by read_only, but ALTER DATABASE should be OK?

Comment by Sergey Vojtovich [ 2018-12-11 ]

When set to 1 (0 is default), no updates are permitted except from users with the SUPER privilege or slave servers updating from a master. The read_only variable is useful for slave servers to ensure no updates are accidentally made outside of what are performed on the master.

Comment by Hartmut Holzgraefe [ 2018-12-11 ]

While "no updates" may seem to imply DML only, all DDL operations are blocked, too.

The only exception seems to be ALTER DATABASE.

That's what the bug report is about: why this single DDL exception?

PS: also please note that I wrote "Test user has all privileges but SUPER", not "Test user has SUPER privilege".

Comment by Sergey Vojtovich [ 2018-12-12 ]

hholzgra, sorry, you're right. I misread your test cases.

I wonder if ALTER DATABASE ... UPGRADE should be fixed as well?

Fix itself is fairly simple:

diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
 
index f357f8b..d1f75eb 100644
 
--- a/sql/sql_parse.cc
 
+++ b/sql/sql_parse.cc
 
@@ -566,7 +566,7 @@ void init_update_queries(void)
 
   sql_command_flags[SQLCOM_CREATE_PACKAGE_BODY]= CF_CHANGES_DATA | CF_AUTO_COMMIT_TRANS;
 
   sql_command_flags[SQLCOM_DROP_PACKAGE_BODY]= CF_CHANGES_DATA | CF_AUTO_COMMIT_TRANS;
 
   sql_command_flags[SQLCOM_ALTER_DB_UPGRADE]= CF_AUTO_COMMIT_TRANS;
 
-  sql_command_flags[SQLCOM_ALTER_DB]=       CF_CHANGES_DATA | CF_AUTO_COMMIT_TRANS;
 
+  sql_command_flags[SQLCOM_ALTER_DB]=       CF_CHANGES_DATA | CF_AUTO_COMMIT_TRANS | CF_DB_CHANGE;
 
   sql_command_flags[SQLCOM_RENAME_TABLE]=   CF_CHANGES_DATA | CF_AUTO_COMMIT_TRANS;
 
   sql_command_flags[SQLCOM_DROP_INDEX]=     CF_CHANGES_DATA | CF_AUTO_COMMIT_TRANS | CF_REPORT_PROGRESS;
 
   sql_command_flags[SQLCOM_CREATE_VIEW]=    CF_CHANGES_DATA | CF_REEXECUTION_FRAGILE |

Comment by Sergey Vojtovich [ 2018-12-12 ]

serg, could you review fix for this bug?

Comment by Sergei Golubchik [ 2018-12-13 ]

ok to push.

Although, I'd push it into an earlier version. 10.1 or even 5.5.

Comment by Hartmut Holzgraefe [ 2018-12-17 ]

"I wonder if ALTER DATABASE ... UPGRADE should be fixed as well?"

Maybe .. but on the other hand it does not perform any changes visible to the SQL layer, so it shouldn't really matter.

I can't see a slave getting out of sync with its master due to this, so it is probably acceptable even with read_only=true

Generated at Thu Feb 08 08:33:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.