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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.