MySQL Bugs: #99072: Regression for bug 81009 fixed in 5.7.17

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

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)

Thank you for the bug report.

Bug #99800 marked as duplicate of this one

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.