Bug #81009 ps_truncate_all_tables() does not work in super_read_only mode

• Submitted: 8 Apr 2016 10:01
• Modified: 11 Aug 2016 14:24
• Reporter: Simon Mudd (OCA)
• Status: Closed
• Category: MySQL Server: SYS Schema
• Severity: S3 (Non-critical)
• Version: 5.7.10, 5.7.12
• OS: Any
• CPU Architecture: Any
• Tags: performance_schema, super_read_only, sys

[8 Apr 2016 10:01] Simon Mudd

Description:
Calling ps_truncate_all_trables() to reset performance_schema statistics does not work in super_read_only mode. 

For this usage where the tables contain memory counters etc and because performance_schema will not replicate this is inconvenient.

It requires me to turn off super_read_only mode prior to running this and turning it back on again afterwards.

How to repeat:
root@localhost [sys]> select @@version;
+------------+
| @@version  |
+------------+
| 5.7.10-log |
+------------+
1 row in set (0.00 sec)

root@localhost [sys]> set global super_read_only = 1;
Query OK, 0 rows affected (0.01 sec)

root@localhost [sys]> call ps_truncate_all_tables(0);
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
root@localhost [sys]> 

Suggested fix:
It would be good if the stored procedure could be executed but the statements inside the stored procedure would be run (depending on whether running as SUPER or not) thus allowing the stored procedure to temporarily turn off super_read_only while making the change and then turning it on again afterwards.

That would be more intuitive.

[14 Apr 2016 8:15] MySQL Verification Team

Hello Simon,

Thank you for the feature request/feedback!

Thanks,
Umesh

[12 May 2016 17:16] Mark Leith

Changing from FR to bug. See related Bug#81394.

[11 Aug 2016 14:24] Paul DuBois

Posted by developer:
 
Noted in 5.7.16 changelog.

The sys schema ps_truncate_all_tables() function did not work with
read_only enabled or for users with the SUPER privilege with
super_read_only enabled, due to errors attempting to truncate
Performance Schema tables. The function now skips these tables if
they cannot be truncated.

[17 Aug 2016 17:18] Paul DuBois

Revised changelog entry:

The sys schema ps_truncate_all_tables() function did not work with
read_only enabled or for users with the SUPER privilege with
super_read_only enabled, due to errors attempting to truncate
Performance Schema tables. The server now skips the
read_only/super_read_only check for Performance Schema tables, with 
the result that ps_truncate_all_tables() will work under such
configurations.

[28 Sep 2016 14:26] Paul DuBois

Posted by developer:
 
Noted in 5.7.17 (not 5.7.16) changelog.