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

Query with recursive cte inside stored function hangs

    Details

      Description

      10.2 commit 8639e288086247ce39917f4cb5

      Server version: 10.2.17-MariaDB-debug Source distribution
       
      MariaDB [test]> CREATE or replace TABLE t1 (id int  KEY) engine=innodb;
      Query OK, 0 rows affected (0.05 sec)
       
      MariaDB [test]> INSERT INTO t1 VALUES (0), (1),(2);
      Query OK, 3 rows affected (0.01 sec)
      Records: 3  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> CREATE OR REPLACE FUNCTION func() RETURNS int RETURN 
          -> ( WITH recursive cte AS (SELECT 1 a UNION SELECT cte.* FROM cte natural join t1) SELECT * FROM cte  limit 1 );
      Query OK, 0 rows affected (0.01 sec)
       
      MariaDB [test]>  WITH recursive cte AS (SELECT 1 a UNION SELECT cte.* FROM cte natural join t1) SELECT * FROM cte  limit 1;
      +---+
      | a |
      +---+
      | 1 |
      +---+
      1 row in set (0.00 sec)
       
      MariaDB [test]> select func();
      ^CCtrl-C -- query killed. Continuing normally.
      

      After sending KILL QUERY, in processlist it hangs in state "checking permissions"

      MariaDB [(none)]> show processlist;
      +----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
      | Id | User        | Host      | db   | Command | Time | State                    | Info             | Progress |
      +----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
      |  2 | system user |           | NULL | Daemon  | NULL | InnoDB purge coordinator | NULL             |    0.000 |
      |  3 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
      |  1 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
      |  4 | system user |           | NULL | Daemon  | NULL | InnoDB purge worker      | NULL             |    0.000 |
      |  5 | system user |           | NULL | Daemon  | NULL | InnoDB shutdown handler  | NULL             |    0.000 |
      |  9 | root        | localhost | test | Killed  |   36 | checking permissions     | select func()    |    0.000 |
      | 11 | root        | localhost | NULL | Query   |    0 | init                     | show processlist |    0.000 |
      +----+-------------+-----------+------+---------+------+--------------------------+------------------+----------+
      7 rows in set (0.00 sec)
      

      After trying to shutdown - assertion `!thd->spcont' fails. (reported in MDEV-15151)

      --source include/have_innodb.inc
       
      --connection default
      CREATE or replace TABLE t1 (id int  KEY) engine=innodb;
      INSERT INTO t1 VALUES (0), (1),(2);
       
      CREATE OR REPLACE FUNCTION func() RETURNS int RETURN 
      ( WITH recursive cte AS (SELECT 1 a UNION SELECT cte.* FROM cte natural join t1) SELECT * FROM cte  limit 1 );
       
      --let $conid= `SELECT CONNECTION_ID()`
      --send SELECT func();
       
      --connect (con1,localhost,root,,)
      --eval KILL QUERY $conid
      --source include/restart_mysqld.inc
       
      --connection default
      

      2018-07-02 12:57:04 140593826244352 [Warning] /home/alice/git/10.2/sql/mysqld: Forcing close of thread 9  user: 'root'
       
      mysqld: /home/alice/git/10.2/sql/protocol.cc:155: bool net_send_error(THD*, uint, const char*, const char*): Assertion `!thd->spcont' failed.
      180702 12:57:04 [ERROR] mysqld got signal 6 ;
       
      Server version: 10.2.17-MariaDB-debug-log
      key_buffer_size=1048576
      read_buffer_size=131072
      max_used_connections=2
      max_threads=153
      thread_count=1
      It is possible that mysqld could use up to 
      key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 63108 K  bytes of memory
      Hope that's ok; if not, decrease some variables in the equation.
       
      Thread pointer: 0x0
      Attempting backtrace. You can use the following information to find out
      where mysqld died. If you see no messages after this, something went
      terribly wrong...
      stack_bottom = 0x0 thread_stack 0x49000
      /usr/lib/x86_64-linux-gnu/libasan.so.2(+0x4a077)[0x7fdea4eb4077]
      mysys/stacktrace.c:267(my_print_stacktrace)[0x55733718a6d4]
      sql/signal_handler.cc:168(handle_fatal_signal)[0x5573361854dc]
      /lib/x86_64-linux-gnu/libpthread.so.0(+0x11390)[0x7fdea3d20390]
      linux/raise.c:54(__GI_raise)[0x7fdea30d9428]
      stdlib/abort.c:91(__GI_abort)[0x7fdea30db02a]
      assert/assert.c:92(__assert_fail_base)[0x7fdea30d1bd7]
      /lib/x86_64-linux-gnu/libc.so.6(+0x2dc82)[0x7fdea30d1c82]
      sql/protocol.cc:156(net_send_error(THD*, unsigned int, char const*, char const*))[0x557335a135eb]
      sql/mysqld.cc:2825(close_connection(THD*, unsigned int))[0x5573359e666e]
      sql/mysqld.cc:1800(close_connections())[0x5573359e272d]
      sql/mysqld.cc:2013(kill_server(void*))[0x5573359e3689]
      sql/mysqld.cc:2045(kill_server_thread)[0x5573359e3805]
      perfschema/pfs.cc:1864(pfs_spawn_thread)[0x55733708f2a8]
      /lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7fdea3d166ba]
      x86_64/clone.S:111(clone)[0x7fdea31ab41d]
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                igor Igor Babaev
                Reporter:
                alice Alice Sherepa
              • Votes:
                1 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: