Bug #99072 Regression for bug 81009 fixed in 5.7.17
Submitted: 25 Mar 2020 20:15 Modified: 30 Jun 2020 17:29
Reporter: Carlos Tutte Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: SYS Schema Severity:S3 (Non-critical)
Version:5.7/8.0 OS:Any
Assigned to: CPU Architecture:Any

[25 Mar 2020 20:15] Carlos Tutte
Description:
While having super_read_only enabled, trying to call procedure ps_truncate_all_trables() to reset performance_schema statistics does not work, but it worked until 5.7.28.

Also trying to manually truncate ps tables shows the following error:
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

How to repeat:
Set up:
mysql -u root -psekret
create database test;
use test;
create table t1 (id int auto_increment primary key);
set global super_read_only = 1 ;
quit 

Test1:
mysql -u root -psekret
use performance_schema;
truncate table events_statements_summary_by_digest;

Test2:
mysql -u root -psekret
use sys;
call ps_truncate_all_tables(0);

Suggested fix:
Revert back to behavior of bug fix : https://bugs.mysql.com/bug.php?id=81009 announced in https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-17.html

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. (Bug #23103937, Bug #81009)
[25 Mar 2020 21:12] MySQL Verification Team
Thank you for the bug report.
[29 Jun 2020 9:59] MySQL Verification Team
Bug #99800 marked as duplicate of this one
[30 Jun 2020 17:29] Paul DuBois
Posted by developer:
 
Fixed in 5.7.32, 8.0.22.

After the fix for Bug #81009, privilege checks for truncating
Performance Schema tables were too restrictive when read_only or
super_read_only were enabled, causing truncation to fail even for
users with appropriate table privileges.