Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6.10
-
None
-
None
-
debian bullseye
Description
I am filing this bug which is affecting 10.6.10 but seems to be fixed on 10.6.12, feel free to close it or further investigate if it is needed.
Filing it cause I haven't found anything on JIRA or anything relevant on 10.6.11 and 10.6.12 release notes/changelogs.
We have this specific event to kill slow queries:
root@db1127.eqiad.wmnet[ops]> show create event wmf_slave_wikiuser_slow\G
|
*************************** 1. row ***************************
|
Event: wmf_slave_wikiuser_slow
|
sql_mode: IGNORE_BAD_TABLE_OPTIONS
|
time_zone: SYSTEM
|
Create Event: CREATE DEFINER=`root`@`localhost` EVENT `wmf_slave_wikiuser_slow` ON SCHEDULE EVERY 30 SECOND STARTS '2023-02-27 00:00:03' ON COMPLETION NOT PRESERVE ENABLE DO begin
|
|
declare sid int;
|
declare all_done int default 0;
|
declare thread_id bigint default null;
|
declare thread_query varchar(100);
|
|
declare slow_queries cursor for
|
SELECT ps.PROCESSLIST_ID, substring(ps.PROCESSLIST_INFO,1,100)
|
FROM performance_schema.threads ps
|
WHERE ps.processlist_user = 'wikiuser2023'
|
AND ps.type='FOREGROUND'
|
AND ps.PROCESSLIST_COMMAND <> 'Sleep'
|
AND ps.processlist_time between 60 and 1000000
|
AND lower(ps.PROCESSLIST_INFO) like '%select%'
|
ORDER BY ps.processlist_time DESC;
|
|
declare continue handler for not found set all_done = 1;
|
|
set sid := @@server_id;
|
set @@session.sql_log_bin := 0;
|
|
if (get_lock('wmf_slave_wikiuser_slow', 1) = 0) then
|
signal sqlstate value '45000' set message_text = 'get_lock';
|
end if;
|
|
set all_done = 0;
|
open slow_queries;
|
|
repeat fetch slow_queries into thread_id, thread_query;
|
|
if (thread_id is not null) then
|
|
kill thread_id;
|
|
insert into event_log values (sid, now(), 'wmf_slave_wikiuser_slow (>60)',
|
concat('kill ',thread_id, '; ',LEFT(thread_query, 997))
|
);
|
|
end if;
|
|
until all_done
|
end repeat;
|
|
close slow_queries;
|
|
|
select 1 from (select 1) as t;
|
|
do release_lock('wmf_slave_wikiuser_slow');
|
|
end
|
character_set_client: utf8mb3
|
collation_connection: utf8mb3_general_ci
|
Database Collation: binary
|
1 row in set (0.001 sec)
|
This event wasn't working on 10.6.10 but works on 10.6.12
How to reproduce:
- Create a wikiuser2023 user.
- Run the following query: select sleep(900)
That query should be killed after 60 seconds or so.
On 10.6.10 the query is never killed, on 10.6.12 it gets killed when it is supposed to be killed.