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

SHOW ANALYZE|EXPLAIN: Lock wait timeout with long GIS computations

Details

    • Bug
    • Status: Stalled (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.8(EOL)
    • 10.11
    • Optimizer
    • None

    Description

      Take a statement doing long GIS computation (it takes minutes):

      SELECT
      		 
      st_buffer(st_buffer(ST_GEOMFROMTEXT('POLYGON((10 10, 10 20, 20 20, 20 10, 10 10))'),1),
      		 
      (st_length(multilinestringfromtext('  MULTILINESTRING( ( 2 2, 2 8, 8 8, 8 2, 2 2 ), ( 4 4, 4 6, 6 6, 6 4, 4 4 ) ) ')) ));

      and try to run SHOW EXPLAIN (or SHOW ANALYZE) on it. The result will be:

      MariaDB [test]> show analyze format=json for  398;
      		 
      ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

      Note that KILL or KILL QUERY are able to kill the statement.

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-27021 Extend SHOW EXPLAIN to support SHOW ANALYZE [FORMAT=JSON]

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            psergei Sergei Petrunia created issue -
            psergei Sergei Petrunia made changes -
            Field Original Value New Value
            Description Take a statement doing long GIS computation (it takes minutes):
            {code:sql}
            SELECT
            st_buffer(st_buffer(ST_GEOMFROMTEXT('POLYGON((10 10, 10 20, 20 20, 20 10, 10 10))'),1),
            (st_length(multilinestringfromtext(' MULTILINESTRING( ( 2 2, 2 8, 8 8, 8 2, 2 2 ), ( 4 4, 4 6, 6 6, 6 4, 4 4 ) ) ')) ));
            {code}

            and try to run SHOW EXPLAIN (or SHOW ANALYZE) on it. The result will be:

            {code}
            MariaDB [test]> show analyze format=json for 398;
            ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
            {code}

            Note that KILL or KILL QUERY are able to kill the statement.
            psergei Sergei Petrunia added a comment -

            Investigation:

            GIS code uses a "non-standard" way to check if the computation is terminated:

            String *Item_func_buffer::val_str(String *str_value)
            		 
            {
            		 
              ...
            		 
              operation.killed= (int *) &(current_thd->killed);

            ...

            #define GCALC_TERMINATED(state_var) (state_var && (*state_var))
            		 
            #define GCALC_SET_TERMINATED(state_var, val) state_var= val

            ..

            int Gcalc_scan_iterator::step()
            		 
            {
            		 
            ...
            		 
              if (GCALC_TERMINATED(killed))
            		 
                GCALC_DBUG_RETURN(0xFFFF);

            This logic allows to check if the thread is killed but doesn't check if there's a need to process SHOW EXPLAIN query.

            psergei Sergei Petrunia added a comment - Investigation: GIS code uses a "non-standard" way to check if the computation is terminated: String *Item_func_buffer::val_str(String *str_value) { ... operation.killed= ( int *) &(current_thd->killed); ... #define GCALC_TERMINATED(state_var) (state_var && (*state_var)) #define GCALC_SET_TERMINATED(state_var, val) state_var= val .. int Gcalc_scan_iterator::step() { ... if (GCALC_TERMINATED(killed)) GCALC_DBUG_RETURN(0xFFFF); This logic allows to check if the thread is killed but doesn't check if there's a need to process SHOW EXPLAIN query.
            psergei Sergei Petrunia made changes -
            Assignee Sergei Petrunia [ psergey ]
            psergei Sergei Petrunia made changes -
            psergei Sergei Petrunia made changes -
            Fix Version/s 10.9 [ 26905 ]
            psergei Sergei Petrunia made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            psergei Sergei Petrunia made changes -
            Affects Version/s 10.6.7 [ 26812 ]
            Affects Version/s 10.6 [ 24028 ]
            Affects Version/s 10.8 [ 26121 ]
            psergei Sergei Petrunia made changes -
            Affects Version/s 10.2 [ 14601 ]
            Affects Version/s 10.3 [ 22126 ]
            Affects Version/s 10.4 [ 22408 ]
            Affects Version/s 10.5 [ 23123 ]
            Affects Version/s 10.6.7 [ 26812 ]
            psergei Sergei Petrunia added a comment -

            Note that this is reproducible with older versions as well. Any version that supports SHOW EXPLAIN is affected.

            psergei Sergei Petrunia added a comment - Note that this is reproducible with older versions as well. Any version that supports SHOW EXPLAIN is affected.
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.11 [ 27614 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.9 [ 26905 ]
            julien.fritsch Julien Fritsch made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]

            People

              psergei Sergei Petrunia
              psergei Sergei Petrunia
              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.