Bug #83242 Improve diagnostics for non-SUPER DEFINER trigger blocking --read_only=ON update
Submitted: 3 Oct 2016 7:19 Modified: 3 Oct 2016 8:07
Reporter: Robert Wunderer Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any
Tags: read_only, super_read_only, trigger

[3 Oct 2016 7:19] Robert Wunderer
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
[3 Oct 2016 8:07] MySQL Verification Team
Hello Robert,

Thank you for the report and feedback!
Verifying for the proper diagnostics message.

Thanks,
Umesh