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.