Bug #107759 Enabling events when super_read_only=1 fails
Submitted: 5 Jul 2022 13:11 Modified: 7 Jul 2022 9:43
Reporter: Russ Smith Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S4 (Feature request)
Version:5.7.34/8.0.29 OS:Any
Assigned to: CPU Architecture:Any

[5 Jul 2022 13:11] Russ Smith
Description:
Summary:
Enabling an event on a replica with super-read-only=1 errors:
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --super-read-only option so it cannot execute this statement

Background:
Several database monitoring software applications, notably DataDog's DPM,  use the table performance_schema.events_statements_summary_by_digest for query analysis. 

This table needs to be periodically truncated for the software to work properly. Unfortunately:
- Truncating events_statements_summary_by_digest does not replicate
- Truncating events_statements_summary_by_digest on a replica with SRO=1 is allowed
- Creating an event on the source to truncate events_statements_summary_by_digest replicates, but the event is disabled on the replicas
- Enabling the event on the source does not replicate
- Enabling the event on replicas with SRO=1 errors

How to repeat:
-- Create an event on the source:
delimiter //
create event test.truncate_events_statements_summary_by_digest
on schedule every 1 second
do
begin
    truncate table performance_schema.events_statements_summary_by_digest;
end//
delimiter ;

-- Event is created and enabled on the source:
master [localhost:21930] {msandbox} (test) > show events\G
*************************** 1. row ***************************
                  Db: test
                Name: truncate_events_statements_summary_by_digest
             Definer: msandbox@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 1
      Interval field: SECOND
              Starts: 2022-07-05 12:49:06
                Ends: NULL
              Status: ENABLED
          Originator: 21930
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

-- Event is enabled on the source, but disabled on the replicas as expected:
# master
EVENT_NAME	STATUS
truncate_events_statements_summary_by_digest	ENABLED
# server: 1
EVENT_NAME	STATUS
truncate_events_statements_summary_by_digest	SLAVESIDE_DISABLED
# server: 2
EVENT_NAME	STATUS
truncate_events_statements_summary_by_digest	SLAVESIDE_DISABLED

-- Enabling the event on the source does NOT replicate:
./m test -e "alter event truncate_events_statements_summary_by_digest enable"

./use_all -e "select event_name, status from information_schema.events"
# master
EVENT_NAME	STATUS
truncate_events_statements_summary_by_digest	ENABLED
# server: 1
EVENT_NAME	STATUS
truncate_events_statements_summary_by_digest	SLAVESIDE_DISABLED
# server: 2
EVENT_NAME	STATUS
truncate_events_statements_summary_by_digest	SLAVESIDE_DISABLED

-- Enabling the event on the replicas errors:
./s1 test -e "alter event truncate_events_statements_summary_by_digest enable"
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --super-read-only option so it cannot execute this statement

-- Executing the statements in the event on replicas with SRO=1 does NOT error:
./s1 -e "truncate table performance_schema.events_statements_summary_by_digest"
echo $?
0

Suggested fix:
Enabling events on replicas when super_read_only is enabled should not error.
[7 Jul 2022 9:43] MySQL Verification Team
Hi,

I do not agree this is a bug so I will be filing this as a Feature Request.

Thanks