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

mariadb-10.5 hangs on "query cache lock" on DROP DATABASE

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Duplicate
    • 10.5.5, 10.6
    • N/A
    • Locking, Query Cache
    • mariadb 10.5.5-1
      archlinux 5.7.12-arch1-1

      query_cache_type = 1
      query_cache_size = 64M

    Description

      ever since upgrading to 10.5 (from 10.4.14) we experience strange hangs of the mariadb server. This is a nice example:

      818 xx localhost xxx-2_1-unittest Query 14945 closing tables DROP DATABASE IF EXISTS `xxx-2_1-20200821055757` 0.000
      2002 xxx localhost xxx-1_3-20200821055909 Killed 14879 Waiting for query cache lock INSERT INTO xxx 0.000
      2030 root localhost   Query 0 starting show processlist 0.000

      there seems to be no real relation between the DROP and the INSERT as they operate on completely different databases (they have no relation). I also have examples where there are multiple (killed) Waiting for query cache lock threads and a single DROP closing tables

      The mariadbd consumes 100% CPU but I'm not sure what it's doing. strace shows some futex calls

      Attachments

        1. all.sql
          1 kB
          Neven Ivanov
        2. source_db_schema.sql
          5 kB
          Neven Ivanov

        Issue Links

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29760 DROP DATABASE hangs when particular query cache is present

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

          Activity

            Ascending order - Click to sort in descending order
            View 5 older comments
            alex2 Alex added a comment - - edited

            Same error here, deadlock when dropping a small 8 MB database with 33 tables.
            Also happening at 10.6, so please add 10.6 to affected versions.
            Working without problems in 10.4.

            alex2 Alex added a comment - - edited Same error here, deadlock when dropping a small 8 MB database with 33 tables. Also happening at 10.6, so please add 10.6 to affected versions. Working without problems in 10.4.
            f.bosch@genkgo.nl Frederik Bosch added a comment -

            Today I also hit this issue from a 10.6 Docker environment, so I can confirm 10.6 is also affected. I am using the RESET QUERY CACHE suggestion from Johny to prevent the lock.

            f.bosch@genkgo.nl Frederik Bosch added a comment - Today I also hit this issue from a 10.6 Docker environment, so I can confirm 10.6 is also affected. I am using the RESET QUERY CACHE suggestion from Johny to prevent the lock.
            remi.augier Rémi Augier added a comment -

            Same issue here in 10.5.12 and 10.6.5

            remi.augier Rémi Augier added a comment - Same issue here in 10.5.12 and 10.6.5
            NevenIvanov Neven Ivanov added a comment - - edited

            Hi,

            I was able to reproduce the problem on 100% of the times with MariaDB 10.5/10.6 ( no matter of the subversion but tested mostly with 10.6.10 )

            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.

            NevenIvanov Neven Ivanov added a comment - - edited Hi, I was able to reproduce the problem on 100% of the times with MariaDB 10.5/10.6 ( no matter of the subversion but tested mostly with 10.6.10 ) 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.
            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.

            As written already last year, please add 10.6 to affected versions!

            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. As written already last year, please add 10.6 to affected versions!

            People

              danblack Daniel Black
              sjon sjon
              Votes:
              6 Vote for this issue
              Watchers:
              12 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.