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

sys.ps_setup_save does not restore SQL_LOG_BIN after error, causes replication discrepancy

    XMLWordPrintable

Details

    Description

      SELECT @@sql_log_bin;
      --error ER_SIGNAL_EXCEPTION
      CALL sys.ps_setup_save(-1);
      SELECT @@sql_log_bin;
      

      10.11 f1aaa7518461c346c06d4ed0701b42552129cfc5

      SELECT @@sql_log_bin;
      @@sql_log_bin
      1
      CALL sys.ps_setup_save(-1);
      ERROR 90000: Could not lock the sys.ps_setup_save user lock, another thread has a saved configuration
      SELECT @@sql_log_bin;
      @@sql_log_bin
      0
      

      Naturally nothing in the session is written into the binlog anymore, which causes all kinds of replication problems. It's something a user didn't subscribe for and cannot expect (at least I didn't).

      The procedure body is

      BEGIN
          DECLARE v_lock_result INT;
          SET @log_bin := @@sql_log_bin;
          SET sql_log_bin = 0;
          SELECT GET_LOCK('sys.ps_setup_save', in_timeout) INTO v_lock_result;
          IF v_lock_result THEN
              DROP TEMPORARY TABLE IF EXISTS tmp_setup_actors;
              DROP TEMPORARY TABLE IF EXISTS tmp_setup_consumers;
              DROP TEMPORARY TABLE IF EXISTS tmp_setup_instruments;
              DROP TEMPORARY TABLE IF EXISTS tmp_threads;
              CREATE TEMPORARY TABLE tmp_setup_actors AS SELECT * FROM performance_schema.setup_actors;
              CREATE TEMPORARY TABLE tmp_setup_consumers AS SELECT * FROM  performance_schema.setup_consumers;
              CREATE TEMPORARY TABLE tmp_setup_instruments AS SELECT * FROM  performance_schema.setup_instruments;
              CREATE TEMPORARY TABLE tmp_threads (THREAD_ID bigint unsigned NOT NULL PRIMARY KEY, INSTRUMENTED enum('YES','NO') NOT NULL);
              INSERT INTO tmp_threads SELECT THREAD_ID, INSTRUMENTED FROM performance_schema.threads;
          ELSE
              SIGNAL SQLSTATE VALUE '90000'
                 SET MESSAGE_TEXT = 'Could not lock the sys.ps_setup_save user lock, another thread has a saved configuration';
          END IF;
          SET sql_log_bin = @log_bin;
      END
      

      A test case uses a simple (and dumb) way to produce an error, in reality it can be something less obvious, e.g. a timeout on GET_LOCK.

      The upstream procedure (at least in 8.2) has the same problem, although it takes a little more to get an error on a procedure call.

      Maybe other sys schema procedures should be revised to see if they have a similar issue. At a quick grep, at least ps_setup_reload_saved and statement_performance_analyzer may have the same problem, although I didn't check.

      Attachments

        Activity

          People

            serg Sergei Golubchik
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated: