Improve diagnostics for non-SUPER DEFINER trigger blocking --read_only=ON update for a SUPER user
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_
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)
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.