Description:
We recently ran into a situation where it seemed as if setting read_only=ON on MySQL disabled updates even for the super user:
(see also: https://bugs.launchpad.net/percona-server/+bug/1613208)
mysql> select @@version, @@version_comment;
+-----------------+------------------------------------------------------+
| @@version | @@version_comment |
+-----------------+------------------------------------------------------+
| 5.6.31-77.0-log | Percona Server (GPL), Release 77.0, Revision 5c1061c |
+-----------------+------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show variables like '%read_only%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_read_only | OFF |
| read_only | ON |
| super_read_only | OFF |
| tx_read_only | OFF |
+------------------+-------+
4 rows in set (0.00 sec)
mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*...' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)
mysql> delete from mytable where id=21169806818;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
mysql> set global read_only=off;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from mytable where id=21169806818;
Query OK, 1 row affected (0.00 sec)
After much debugging we found out that the reason the update failed was that the table in question had an AFTER DELETE trigger defined by a non-super user attached.
It was actually the trigger that couldn't be executed, not the original delete.
It would have helped a lot if the error message had been more verbose about where the error actually occurred.
How to repeat:
- Create an arbitrary table and populate with test data
- Create a second arbitrary table
- Define a trigger CREATE DEFINER=`unprivileged`@`localhost` ... AFTER DELETE ... that modifieds data in the second table
- set global read_only=on;
- try to delete a record from the first table using SUPER privileges
Suggested fix:
Improve diagnostic message when an operation fails due to any trigger problem, eg:
ERROR XXXX (HY000): Dependent trigger operation failed with the following error: The MySQL server is running with the --read-only option so it cannot execute this statement