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

DROP DATABASE hangs when particular query cache is present

Details

    Description

      Hi,

      With MariaDB 10.6 ( no matter of the subversion but tested mostly with 10.6.10 ) when particular query cache is present you are unable to drop database.

      You will need the files all.sql and source_db_schema.sql which are attached to this task.

      Then just execute from bash console:

      mysql -e "create database source_db"
      		 
       
      		 
       
       
      		 
      mysql source_db < source_db_schema.sql
      		 
       
      		 
      mysql -e 'RESET QUERY CACHE';
      		 
       
      		 
      mysql -e "create database destination_db" ;  mysqldump source_db | mysql destination_db
      		 
       
      		 
      mysql 'source_db' -e "select option_name from VbVoptions;" ;
      		 
       
      		 
       
       
      		 
          mysql destination_db < all.sql; 
       
      		 
          mysql -e "drop database destination_db ;"
      		 
       
      		 
          echo "drop successfully"

      source_db_schema.sql
      all.sql

      In summary if specific query cache is present, you are unable to drop database and it just hangs (MariaDB uses 100% cpu) and no restart ( without "kill -9" ) can be performed .

      Attachments

        Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23527 mariadb-10.5 hangs on "query cache lock" on DROP DATABASE

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25023 MariaDB 10.5 - DROP DATABASE locked

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova added a comment -

            Thanks for the report and the test case. Reproducible as described on 10.5+.

            A reduced and MTR-ed version:

            SET @qcache= @@global.query_cache_type;
            		 
            SET GLOBAL query_cache_type= 1;
            		 
            SET query_cache_type= 1;
            		 
             
            		 
            CREATE TABLE t1 (a VARCHAR(8)) ENGINE=MyISAM;
            		 
            SELECT a FROM test.t1;
            		 
             
            		 
            CREATE DATABASE db;
            		 
            USE db;
            		 
             
            		 
            CREATE TABLE t2 (b INT) ENGINE=MyISAM;
            		 
            CREATE TABLE t3 (c INT) ENGINE=MyISAM;
            		 
             
            		 
            SELECT 't2' table_name FROM t2;
            		 
             
            		 
            SELECT 't2' table_name FROM t2
            		 
            UNION ALL
            		 
            SELECT 't3' table_name FROM t3;
            		 
             
            		 
            DROP DATABASE db;
            		 
             
            		 
            # Cleanup
            		 
            USE test;
            		 
            DROP TABLE t1;
            		 
            SET GLOBAL query_cache_type= @qcache;

            DROP DATABASE db hangs (loops at high CPU), one of stack traces:

            10.5 dca4fc24

            		 
            #3  0x000055f04f7e908d in DbugMalloc (size=568) at /src/dbug/dbug.c:2071
            		 
            #4  0x000055f04f7e7143 in PushState (cs=0x606000015fe0) at /src/dbug/dbug.c:1596
            		 
            #5  0x000055f04f7e12f3 in _db_push_ (control=0x55f0508ff460 "") at /src/dbug/dbug.c:864
            		 
            #6  0x000055f04f7884ca in safe_mutex_lock (mp=0x62b00006b030, my_flags=0, file=0x55f04f97cd60 "/src/sql/sql_class.h", line=4239) at /src/mysys/thr_mutex.c:236
            		 
            #7  0x000055f04da0fe9b in inline_mysql_mutex_lock (that=0x62b00006b030, src_file=0x55f04f97cd60 "/src/sql/sql_class.h", src_line=4239) at /src/include/mysql/psi/mysql_thread.h:750
            		 
            #8  0x000055f04da321a8 in THD::set_killed (this=0x62b000069218, killed_arg=KILL_CONNECTION, killed_errno_arg=0, killed_err_msg_arg=0x0) at /src/sql/sql_class.h:4239
            		 
            #9  0x000055f04dba9a84 in Query_cache::wreck (this=0x55f051c29f40 <query_cache>, line=867, message=0x55f04fa1c840 "incorrect block type") at /src/sql/sql_cache.cc:4691
            		 
            #10 0x000055f04db991bc in Query_cache_block::table (this=0x7f88d71f87f8) at /src/sql/sql_cache.cc:867
            		 
            #11 0x000055f04db9f75e in Query_cache::invalidate (this=0x55f051c29f40 <query_cache>, thd=0x62b000069218, db=0x62b0000382c8 "db") at /src/sql/sql_cache.cc:2380
            		 
            #12 0x000055f04dbf735b in mysql_rm_db_internal (thd=0x62b000069218, db=0x62b00006e0c8, if_exists=false, silent=false) at /src/sql/sql_db.cc:972
            		 
            #13 0x000055f04dbf7eb3 in mysql_rm_db (thd=0x62b000069218, db=0x62b00006e0c8, if_exists=false) at /src/sql/sql_db.cc:1103
            		 
            #14 0x000055f04dcd6ff0 in mysql_execute_command (thd=0x62b000069218) at /src/sql/sql_parse.cc:5241
            		 
            #15 0x000055f04dce8c53 in mysql_parse (thd=0x62b000069218, rawbuf=0x62b000038238 "DROP DATABASE db", length=16, parser_state=0x7f88d767ec40, is_com_multi=false, is_next_command=false) at /src/sql/sql_parse.cc:8087
            		 
            #16 0x000055f04dcc1449 in dispatch_command (command=COM_QUERY, thd=0x62b000069218, packet=0x62900024e219 "DROP DATABASE db", packet_length=16, is_com_multi=false, is_next_command=false) at /src/sql/sql_parse.cc:1891
            		 
            #17 0x000055f04dcbe682 in do_command (thd=0x62b000069218) at /src/sql/sql_parse.cc:1375
            		 
            #18 0x000055f04e09d7d0 in do_handle_one_connection (connect=0x608000002538, put_in_cache=true) at /src/sql/sql_connect.cc:1416
            		 
            #19 0x000055f04e09d198 in handle_one_connection (arg=0x6080000024b8) at /src/sql/sql_connect.cc:1318
            		 
            #20 0x000055f04ebb9b1c in pfs_spawn_thread (arg=0x615000005d18) at /src/storage/perfschema/pfs.cc:2201
            		 
            #21 0x00007f88e1078ea7 in start_thread (arg=<optimized out>) at pthread_create.c:477
            		 
            #22 0x00007f88e0c65aef in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

            The problem was introduced in 10.5.4 by this commit:

            commit dfb41fddf69ccbca89fd322901f2809bc3bcc0e9
            		 
            Author: Monty
            		 
            Date:   Fri Jun 5 18:55:11 2020 +0300
            		 
             
            		 
                Make error messages from DROP TABLE and DROP TABLE IF EXISTS consistent

            (despite the main line, there were "other things" in that commit).

            elenst Elena Stepanova added a comment - Thanks for the report and the test case. Reproducible as described on 10.5+. A reduced and MTR-ed version: SET @qcache= @@ global .query_cache_type; SET GLOBAL query_cache_type= 1; SET query_cache_type= 1;   CREATE TABLE t1 (a VARCHAR (8)) ENGINE=MyISAM; SELECT a FROM test.t1;   CREATE DATABASE db; USE db;   CREATE TABLE t2 (b INT ) ENGINE=MyISAM; CREATE TABLE t3 (c INT ) ENGINE=MyISAM;   SELECT 't2' table_name FROM t2;   SELECT 't2' table_name FROM t2 UNION ALL SELECT 't3' table_name FROM t3;   DROP DATABASE db;   # Cleanup USE test; DROP TABLE t1; SET GLOBAL query_cache_type= @qcache; DROP DATABASE db hangs (loops at high CPU), one of stack traces: 10.5 dca4fc24 #3 0x000055f04f7e908d in DbugMalloc (size=568) at /src/dbug/dbug.c:2071 #4 0x000055f04f7e7143 in PushState (cs=0x606000015fe0) at /src/dbug/dbug.c:1596 #5 0x000055f04f7e12f3 in _db_push_ (control=0x55f0508ff460 "") at /src/dbug/dbug.c:864 #6 0x000055f04f7884ca in safe_mutex_lock (mp=0x62b00006b030, my_flags=0, file=0x55f04f97cd60 "/src/sql/sql_class.h", line=4239) at /src/mysys/thr_mutex.c:236 #7 0x000055f04da0fe9b in inline_mysql_mutex_lock (that=0x62b00006b030, src_file=0x55f04f97cd60 "/src/sql/sql_class.h", src_line=4239) at /src/include/mysql/psi/mysql_thread.h:750 #8 0x000055f04da321a8 in THD::set_killed (this=0x62b000069218, killed_arg=KILL_CONNECTION, killed_errno_arg=0, killed_err_msg_arg=0x0) at /src/sql/sql_class.h:4239 #9 0x000055f04dba9a84 in Query_cache::wreck (this=0x55f051c29f40 <query_cache>, line=867, message=0x55f04fa1c840 "incorrect block type") at /src/sql/sql_cache.cc:4691 #10 0x000055f04db991bc in Query_cache_block::table (this=0x7f88d71f87f8) at /src/sql/sql_cache.cc:867 #11 0x000055f04db9f75e in Query_cache::invalidate (this=0x55f051c29f40 <query_cache>, thd=0x62b000069218, db=0x62b0000382c8 "db") at /src/sql/sql_cache.cc:2380 #12 0x000055f04dbf735b in mysql_rm_db_internal (thd=0x62b000069218, db=0x62b00006e0c8, if_exists=false, silent=false) at /src/sql/sql_db.cc:972 #13 0x000055f04dbf7eb3 in mysql_rm_db (thd=0x62b000069218, db=0x62b00006e0c8, if_exists=false) at /src/sql/sql_db.cc:1103 #14 0x000055f04dcd6ff0 in mysql_execute_command (thd=0x62b000069218) at /src/sql/sql_parse.cc:5241 #15 0x000055f04dce8c53 in mysql_parse (thd=0x62b000069218, rawbuf=0x62b000038238 "DROP DATABASE db", length=16, parser_state=0x7f88d767ec40, is_com_multi=false, is_next_command=false) at /src/sql/sql_parse.cc:8087 #16 0x000055f04dcc1449 in dispatch_command (command=COM_QUERY, thd=0x62b000069218, packet=0x62900024e219 "DROP DATABASE db", packet_length=16, is_com_multi=false, is_next_command=false) at /src/sql/sql_parse.cc:1891 #17 0x000055f04dcbe682 in do_command (thd=0x62b000069218) at /src/sql/sql_parse.cc:1375 #18 0x000055f04e09d7d0 in do_handle_one_connection (connect=0x608000002538, put_in_cache=true) at /src/sql/sql_connect.cc:1416 #19 0x000055f04e09d198 in handle_one_connection (arg=0x6080000024b8) at /src/sql/sql_connect.cc:1318 #20 0x000055f04ebb9b1c in pfs_spawn_thread (arg=0x615000005d18) at /src/storage/perfschema/pfs.cc:2201 #21 0x00007f88e1078ea7 in start_thread (arg=<optimized out>) at pthread_create.c:477 #22 0x00007f88e0c65aef in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95 The problem was introduced in 10.5.4 by this commit: commit dfb41fddf69ccbca89fd322901f2809bc3bcc0e9 Author: Monty Date: Fri Jun 5 18:55:11 2020 +0300   Make error messages from DROP TABLE and DROP TABLE IF EXISTS consistent (despite the main line, there were "other things" in that commit).
            alex2 Alex added a comment -

            I can reproduce the deadlock with Nevens example everytime also with the latest MariaDB 10.6.10.
            It is only working if the Query-cache-reset is immediately executed before the drop.

            Related to MDEV-23527 and MDEV-25023.

            alex2 Alex added a comment - I can reproduce the deadlock with Nevens example everytime also with the latest MariaDB 10.6.10. It is only working if the Query-cache-reset is immediately executed before the drop. Related to MDEV-23527 and MDEV-25023 .
            danblack Daniel Black added a comment -

            Github PR #2346 for review.

            danblack Daniel Black added a comment - Github PR #2346 for review.
            danblack Daniel Black added a comment -

            Thanks NevenIvanov for the test case, and elenst for the reduction/bisect down to commit to blame. Both helped immensely.

            danblack Daniel Black added a comment - Thanks NevenIvanov for the test case, and elenst for the reduction/bisect down to commit to blame. Both helped immensely.

            People

              danblack Daniel Black
              NevenIvanov Neven Ivanov
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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