Improve diagnostics for non-SUPER DEFINER trigger blocking --read_only=ON update for a SUPER user

Bug #1613208 reported by Robert Wunderer on 2016-08-15
12
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Percona Server moved to https://jira.percona.com/projects/PS
Status tracked in 5.7
5.5
Won't Fix
Wishlist
Unassigned
5.6
Triaged
Wishlist
Unassigned
5.7
Triaged
Wishlist
Unassigned
Ubuntu
Unknown
Unknown

Bug Description

When setting read_only=ON even the super user cannot delete or update data, though super_read_only=OFF.

This has been tested in Percona Server 5.6.29-76.2-log and 5.6.31-77.0-log:

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)

tags: added: super-read-only

I've found the reason for the above behavior:

The table I used in these examples has triggers with a non-SUPER user as DEFINER. So when the trigger tries to change data in an associated table, the trigger fails and the whole statement emits the above error message.

I've found this to be a general problem with triggers in MySQL: it's not clear from the error message if the original table or the trigger is causing problems.

But anyway, I think the above bug can be closed as bogus.

Let's keep this bug open to see whether the diagnostics could be improved

summary: - read_only applies to users with SUPER priv as well
+ Improve diagnostics for non-SUPER DEFINER trigger blocking
+ --read_only=ON update for a SUPER user
tags: added: upstream
removed: super-read-only

Also feel free to report this issue to bugs.mysql.com. If you do, please post the link here. Thanks!

affects: mysql-server → ubuntu

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2482

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.