MySQL Bugs: #109130: read_only with Analyze table is not following documented behaviour

Bug #109130	read_only with Analyze table is not following documented behaviour
Submitted:	17 Nov 2022 21:12	Modified:	22 Nov 2022 13:11
Reporter:	Pranay Motupalli	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	analyze table, mysql-server, read_only

Description:
As per the documentation https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_read_only

Even with read_only enabled, the server permits these operations:

Use of ANALYZE TABLE or OPTIMIZE TABLE statements. The purpose of read-only mode is to prevent changes to table structure or contents. Analysis and optimization do not qualify as such changes. This means, for example, that consistency checks on read-only replicas can be performed with mysqlcheck --all-databases --analyze.

However, that's not the case. Analyze table is blocked when server is in --read-only mode

How to repeat:
### CREATE USER AND GRANT REQUIRED PRIVILEGES ####

[ec2-user@ip-172-31-54-9 ~]$ /home/ec2-user/opt/mysql/5.7.39/bin/mysql -uroot -xxxxx -P5739 -h127.0.0.1 --ssl-mode=REQUIRE
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> create database test;
Query OK, 0 rows affected (0.01 sec)

mysql> create table test.t1(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> create user 'pranay'@'%' identified by 'password';
Query OK, 0 rows affected (0.00 sec)

mysql> grant select,insert on test.* to 'pranay'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

### CONNECT WITH NEW USER #####

[ec2-user@ip-172-31-54-9 ~]$ /home/ec2-user/opt/mysql/5.7.39/bin/mysql -upranay -ppassword -P5739 -h127.0.0.1 --ssl-mode=REQUIRE
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.01 sec)

mysql> analyze table test.t1;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
mysql> exit
Bye

### CHANGE THE READ_ONLY TO OFF ####

[ec2-user@ip-172-31-54-9 ~]$ /home/ec2-user/opt/mysql/5.7.39/bin/mysql -uroot -pqwertyuiop -P5739 -h127.0.0.1 --ssl-mode=REQUIRE
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set global read_only=0;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

### TRY RUNNING ANALYZE TABLE ####

[ec2-user@ip-172-31-54-9 ~]$ /home/ec2-user/opt/mysql/5.7.39/bin/mysql -upranay -ppassword -P5739 -h127.0.0.1 --ssl-mode=REQUIRE
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show global variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.00 sec)

mysql> analyze table test.t1;
+---------+---------+----------+----------+
| Table   | Op      | Msg_type | Msg_text |
+---------+---------+----------+----------+
| test.t1 | analyze | status   | OK       |
+---------+---------+----------+----------+
1 row in set (0.00 sec)

Suggested fix:
Either fix the documentation or fix the behaviour. The behaviour is not inline with the documented behaviour.

Hi Mr. Motupalli,

Thank you for your bug report.

However, you have not used mysqlcheck, although our documentation refers to that utility specifically.

Also, you have not specified the category, so we do not know whether you complain about server behaviour or about documentation.

### ADDING ADDITIONAL TESTING HERE 

[ec2-user@ip-172-31-54-9 ~]$ sudo /home/ec2-user/opt/mysql/5.7.38/bin/mysql -uroot -pqwertyuiop -P5738 -h127.0.0.1 --ssl-mode=REQUIRE
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set global read_only=0;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

[ec2-user@ip-172-31-54-9 ~]$ /home/ec2-user/opt/mysql/5.7.38/bin/mysqlcheck --all-databases --analyze  -upranay -pqwertyuiop -P5738 -h127.0.0.1 --ssl-mode=REQUIRE --verbose
mysqlcheck: [Warning] Using a password on the command line interface can be insecure.
# Connecting to 127.0.0.1...
# Disconnecting from 127.0.0.1...

[ec2-user@ip-172-31-54-9 ~]$ sudo /home/ec2-user/opt/mysql/5.7.38/bin/mysql -uroot -pqwertyuiop -P5738 -h127.0.0.1 --ssl-mode=REQUIRE
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set global read_only=0;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

[ec2-user@ip-172-31-54-9 ~]$ /home/ec2-user/opt/mysql/5.7.38/bin/mysqlcheck --all-databases --analyze  -upranay -pqwertyuiop -P5738 -h127.0.0.1 --ssl-mode=REQUIRE --verbose
mysqlcheck: [Warning] Using a password on the command line interface can be insecure.
# Connecting to 127.0.0.1...
test.t1                                            OK
# Disconnecting from 127.0.0.1...
[ec2-user@ip-172-31-54-9 ~]$

Things are getting weird now. As you can see stats are updated irrespective of error message

[ec2-user@ip-172-31-54-9 ~]$ /home/ec2-user/opt/mysql/5.7.38/bin/mysql -upranay -pqwertyuiop -P5738 -h127.0.0.1 --ssl-mode=REQUIRE
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 48
Server version: 5.7.38 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW VARIABLES LIKE '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.00 sec)

mysql> select * from mysql.innodb_table_stats where table_name='t1';
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| test          | t1         | 2022-11-18 18:10:10 |      0 |                    1 |                        0 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
1 row in set (0.00 sec)

mysql> select now();analyze table test.t1;select now();
+---------------------+
| now()               |
+---------------------+
| 2022-11-18 18:16:43 |
+---------------------+
1 row in set (0.00 sec)

ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
+---------------------+
| now()               |
+---------------------+
| 2022-11-18 18:16:43 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from mysql.innodb_table_stats where table_name='t1';
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| test          | t1         | 2022-11-18 18:16:43 |      0 |                    1 |                        0 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
1 row in set (0.00 sec)

The Ask here is - 

Do you support Running ANALYZE TABLE when --read-only is enabled. If yes, why is error thrown?

If you don't support - Why are on disk stats getting updated though the error is thrown?

Hi Mr. Motupalli,

This is indeed a bug, that affects both latest 5.7 and 8.0.

Verified as reported.