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

[Robert Wunderer]

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)

[Robert Wunderer (robert-wunderer-y)]

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.

[Laurynas Biveinis (laurynas-biveinis)]

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

[Laurynas Biveinis (laurynas-biveinis)]

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

[Robert Wunderer (robert-wunderer-y)]

Thanks. I filed this bug:

http://bugs.mysql.com/bug.php?id=83242

[Shahriyar Rzayev (rzayev-sehriyar)]

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