MariaDB Development
  1. MariaDB Development
  2. MDEV-4029

SELECT on information_schema using a subquery locks up the information_schema table due to incorrect mutexes handling

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 10.0.1, 5.5.28a, 5.3.11, 5.2.13, 5.1.66
    • Fix Version/s: 10.0.1, 5.5.29, 5.3.12, 5.2.14, 5.1.67
    • Labels:
    • Environment:
      Debian 6.0.6 amd64 on both machines : one running 5.5.28a-MariaDB-a1~squeeze-l and one running MySQL 5.1.66-0+squeeze1-log
    • Global Rank:
      2386

      Description

      Doing a SELECT query involving a subquery on information_schema will lock the query and the whole information_schema (even if the query is done by an unpriviledged user).

      All queries done afterwise on the information_schema (including the use of SHOW STATUS and SHOW VARIABLES) wont end and cannot be killed (Status: Killed and State: executing) until the mysqld process is stopped and the mysqld wont shutdown gracefully (a kill -9 must be done in order to stop it).

      Here is an example of these problematic queries :

      SELECT VARIABLE_VALUE FROM information_schema.session_status WHERE VARIABLE_NAME = 'Uptime' + (SELECT VARIABLE_VALUE FROM information_schema.session_status WHERE VARIABLE_NAME = 'Uptime');

      ====================

      The query boils down to

      SELECT * FROM information_schema.session_status 
      WHERE VARIABLE_NAME = (SELECT VARIABLE_NAME FROM information_schema.session_status);
      

      and one can add additional conditions of one's choice or extend it to make more realistic.

      Reproducible on all current trees, MariaDB 5.1-10.0 and MySQL 5.1-5.6.
      Stack trace from maria/5.5 revno 3607.

      safe_mutex: Trying to lock mutex at maria-5.5/sql/sql_show.cc, line 6932, when the mutex was already locked at maria-5.5/sql/sql_show.cc, line 6932 in thread T@5
      130114  5:39:03 [ERROR] mysqld got signal 6 ;
      
      #3  <signal handler called>
      #4  0x00007f00f89b03a5 in __GI_raise (sig=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:64
      #5  0x00007f00f89b3b0b in __GI_abort () at abort.c:92
      #6  0x0000000000cb1b85 in safe_mutex_lock (mp=0x149fba0, my_flags=0, file=0xd27cd0 "maria-5.5/sql/sql_show.cc", line=6932) at maria-5.5/mysys/thr_mutex.c:266
      #7  0x000000000067c945 in inline_mysql_mutex_lock (that=0x149fba0, src_file=0xd27cd0 "maria-5.5/sql/sql_show.cc", src_line=6932) at maria-5.5/include/mysql/psi/mysql_thread.h:618
      #8  0x0000000000694e4a in fill_status (thd=0x2b06390, tables=0x2c08720, cond=0x0) at maria-5.5/sql/sql_show.cc:6932
      #9  0x0000000000697100 in do_fill_table (thd=0x2b06390, table_list=0x2c08720, join_table=0x2bfd138) at maria-5.5/sql/sql_show.cc:7682
      #10 0x0000000000697512 in get_schema_tables_result (join=0x2bfc108, executed_place=PROCESSED_BY_JOIN_EXEC) at maria-5.5/sql/sql_show.cc:7789
      #11 0x0000000000641f4d in JOIN::exec (this=0x2bfc108) at maria-5.5/sql/sql_select.cc:2313
      #12 0x000000000085ca9c in subselect_single_select_engine::exec (this=0x2c08e30) at maria-5.5/sql/item_subselect.cc:3205
      #13 0x0000000000855628 in Item_subselect::exec (this=0x2c08cf8) at maria-5.5/sql/item_subselect.cc:644
      #14 0x0000000000856e8f in Item_singlerow_subselect::val_str (this=0x2c08cf8, str=0x2c08fb8) at maria-5.5/sql/item_subselect.cc:1233
      #15 0x00000000007f3465 in Arg_comparator::compare_string (this=0x2c08f30) at maria-5.5/sql/item_cmpfunc.cc:987
      #16 0x0000000000803919 in Arg_comparator::compare (this=0x2c08f30) at maria-5.5/sql/item_cmpfunc.h:78
      #17 0x00000000007f5e58 in Item_func_eq::val_int (this=0x2c08e70) at maria-5.5/sql/item_cmpfunc.cc:1858
      #18 0x00000000006847ab in show_status_array (thd=0x2b06390, wild=0x0, variables=0x29c5a00, value_type=OPT_SESSION, status_var=0x2b06d18, prefix=0xd2683b "", table=0x2c43028, ucase_names=true, cond=0x2c08e70) at maria-5.5/sql/sql_show.cc:2641
      #19 0x0000000000694eb2 in fill_status (thd=0x2b06390, tables=0x2c07498, cond=0x2c08e70) at maria-5.5/sql/sql_show.cc:6938
      #20 0x0000000000697100 in do_fill_table (thd=0x2b06390, table_list=0x2c07498, join_table=0x2bfe218) at maria-5.5/sql/sql_show.cc:7682
      #21 0x0000000000697512 in get_schema_tables_result (join=0x2bfb868, executed_place=PROCESSED_BY_JOIN_EXEC) at maria-5.5/sql/sql_show.cc:7789
      #22 0x0000000000641f4d in JOIN::exec (this=0x2bfb868) at maria-5.5/sql/sql_select.cc:2313
      #23 0x0000000000644965 in mysql_select (thd=0x2b06390, rref_pointer_array=0x2b090f0, tables=0x2c07498, wild_num=1, fields=..., conds=0x2c08e70, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2684619520, result=0x2bfb848, unit=0x2b087c0, select_lex=0x2b08e98) at maria-5.5/sql/sql_select.cc:3054
      #24 0x000000000063b551 in handle_select (thd=0x2b06390, lex=0x2b08710, result=0x2bfb848, setup_tables_done_option=0) at maria-5.5/sql/sql_select.cc:316
      #25 0x00000000006147a4 in execute_sqlcom_select (thd=0x2b06390, all_tables=0x2c07498) at maria-5.5/sql/sql_parse.cc:4622
      #26 0x000000000060d26f in mysql_execute_command (thd=0x2b06390) at maria-5.5/sql/sql_parse.cc:2182
      #27 0x000000000061700b in mysql_parse (thd=0x2b06390, rawbuf=0x2c071a8 "SELECT * FROM information_schema.session_status \nWHERE VARIABLE_NAME = (SELECT VARIABLE_NAME FROM information_schema.session_status)", length=132, parser_state=0x7f00ee08c500) at maria-5.5/sql/sql_parse.cc:5737
      #28 0x000000000060a7e2 in dispatch_command (command=COM_QUERY, thd=0x2b06390, packet=0x2c88e01 "SELECT * FROM information_schema.session_status \nWHERE VARIABLE_NAME = (SELECT VARIABLE_NAME FROM information_schema.session_status)", packet_length=132) at maria-5.5/sql/sql_parse.cc:1055
      #29 0x0000000000609a99 in do_command (thd=0x2b06390) at maria-5.5/sql/sql_parse.cc:794
      #30 0x000000000070c873 in do_handle_one_connection (thd_arg=0x2b06390) at maria-5.5/sql/sql_connect.cc:1253
      #31 0x000000000070c25e in handle_one_connection (arg=0x2b06390) at maria-5.5/sql/sql_connect.cc:1168
      #32 0x0000000000b921fb in pfs_spawn_thread (arg=0x2c87b60) at maria-5.5/storage/perfschema/pfs.cc:1015
      #33 0x00007f00f96ecefc in start_thread (arg=0x7f00ee08d700) at pthread_create.c:304
      #34 0x00007f00f8a5ef4d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
      

        Issue Links

          Activity

          Hide
          Elena Stepanova added a comment -

          I think even though it's an upstream bug, it's worth fixing in the upcoming 5.5.29 release (next releases of 5.1+ might already pick up the upstream fix, but 5.5.29 won't).

          Show
          Elena Stepanova added a comment - I think even though it's an upstream bug, it's worth fixing in the upcoming 5.5.29 release (next releases of 5.1+ might already pick up the upstream fix, but 5.5.29 won't).
          Hide
          Jean Weisbuch added a comment -
          Show
          Jean Weisbuch added a comment - Seems related to http://bugs.mysql.com/bug.php?id=58738
          Hide
          Sergei Golubchik added a comment -

          fixed in 5.7.0

          Show
          Sergei Golubchik added a comment - fixed in 5.7.0
          Hide
          Elena Stepanova added a comment -

          I find it weird that it was only fixed in 5.7.0, doesn't seem to be usual approach to such bugs. Maybe it's because the bug was marked as debug-only, although in fact it affects release builds also, as Jean described.

          Show
          Elena Stepanova added a comment - I find it weird that it was only fixed in 5.7.0, doesn't seem to be usual approach to such bugs. Maybe it's because the bug was marked as debug-only, although in fact it affects release builds also, as Jean described.
          Hide
          Patryk Pomykalski added a comment -

          trying to fix it

          Show
          Patryk Pomykalski added a comment - trying to fix it

            People

            • Assignee:
              Sergei Golubchik
              Reporter:
              Jean Weisbuch
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 3 hours
                3h