Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-30760

Specific event not working on 10.6.10 but works on 10.6.12

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.6.10
    • None
    • Events
    • 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.

      Attachments

        Activity

          People

            Unassigned Unassigned
            marostegui Manuel Arostegui
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.