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

DROP DATABASE still is case-insensitive

Details

    Description

      Lightly sporadic. Repeat 1-3 times with server restarts. Note the difference Db vs DB which is at the core of the issue.
      Serious case issue. Potential blocker.

      SET GLOBAL innodb_file_per_table=0;
      		 
      CREATE DATABASE Db;
      		 
      USE Db;
      		 
      CREATE TABLE t1 (c1 INT KEY) ENGINE=InnoDB;
      		 
      CREATE DATABASE DB;
      		 
      DROP DATABASE IF EXISTS DB;
      		 
      INSERT INTO t1 VALUES (0);  # Will delay-crash

      Leads to:

      10.9.2 6ec17142dcfb1e9d9f41211ed1b6d82e062d1541 (Debug)

      		 
      mysqld: /test/10.9_dbg/storage/innobase/buf/buf0buf.cc:2928: buf_block_t* buf_page_get_low(page_id_t, ulint, ulint, buf_block_t*, ulint, mtr_t*, dberr_t*, bool): Assertion `mode == 16 || mode == 12 || state > buf_page_t::UNFIXED' failed.

      10.9.2 6ec17142dcfb1e9d9f41211ed1b6d82e062d1541 (Debug)

      		 
      Core was generated by `/test/MD310522-mariadb-10.9.2-linux-x86_64-dbg/bin/mysqld --no-defaults --core-'.
      		 
      Program terminated with signal SIGABRT, Aborted.
      		 
      #0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
      		 
      [Current thread is 1 (Thread 0x148f102bc700 (LWP 1719564))]
      		 
      (gdb) bt
      		 
      #0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
      		 
      #1  0x0000148f28f7e859 in __GI_abort () at abort.c:79
      		 
      #2  0x0000148f28f7e729 in __assert_fail_base (fmt=0x148f29114588 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=0x56520d638d08 "mode == 16 || mode == 12 || state > buf_page_t::UNFIXED", file=0x56520d6373f8 "/test/10.9_dbg/storage/innobase/buf/buf0buf.cc", line=2928, function=<optimized out>) at assert.c:92
      		 
      #3  0x0000148f28f8ffd6 in __GI___assert_fail (assertion=assertion@entry=0x56520d638d08 "mode == 16 || mode == 12 || state > buf_page_t::UNFIXED", file=file@entry=0x56520d6373f8 "/test/10.9_dbg/storage/innobase/buf/buf0buf.cc", line=line@entry=2928, function=function@entry=0x56520d638a38 "buf_block_t* buf_page_get_low(page_id_t, ulint, ulint, buf_block_t*, ulint, mtr_t*, dberr_t*, bool)") at assert.c:101
      		 
      #4  0x000056520d088c9d in buf_page_get_low (page_id=page_id@entry={m_id = 312}, zip_size=zip_size@entry=0, rw_latch=rw_latch@entry=1, guess=<optimized out>, guess@entry=0x148f20168a40, mode=mode@entry=10, mtr=mtr@entry=0x148f102bb6f0, err=0x148f102ba5d4, allow_ibuf_merge=false) at /test/10.9_dbg/storage/innobase/buf/buf0buf.cc:2928
      		 
      #5  0x000056520d0891ee in buf_page_get_gen (page_id=page_id@entry={m_id = 312}, zip_size=zip_size@entry=0, rw_latch=rw_latch@entry=1, guess=guess@entry=0x148f20168a40, mode=mode@entry=10, mtr=mtr@entry=0x148f102bb6f0, err=0x148f102ba5d4, allow_ibuf_merge=false) at /test/10.9_dbg/storage/innobase/buf/buf0buf.cc:3042
      		 
      #6  0x000056520d052404 in btr_cur_search_to_nth_level_func (index=index@entry=0x148eb80219c0, level=<optimized out>, tuple=tuple@entry=0x148ed8001e20, mode=mode@entry=PAGE_CUR_LE, latch_mode=<optimized out>, latch_mode@entry=2, cursor=cursor@entry=0x56521045cce0, ahi_latch=<optimized out>, mtr=<optimized out>, autoinc=<optimized out>) at /test/10.9_dbg/storage/innobase/include/dict0mem.h:1167
      		 
      #7  0x000056520cf81021 in btr_pcur_open_low (index=index@entry=0x148eb80219c0, level=<optimized out>, level@entry=0, tuple=tuple@entry=0x148ed8001e20, mode=mode@entry=PAGE_CUR_LE, latch_mode=latch_mode@entry=2, cursor=cursor@entry=0x56521045cce0, autoinc=0, mtr=0x148f102bb6f0) at /test/10.9_dbg/storage/innobase/include/btr0pcur.inl:368
      		 
      #8  0x000056520cf81260 in row_search_on_row_ref (pcur=pcur@entry=0x56521045cce0, mode=mode@entry=2, table=<optimized out>, ref=0x148ed8001e20, mtr=mtr@entry=0x148f102bb6f0) at /test/10.9_dbg/storage/innobase/row/row0row.cc:1223
      		 
      #9  0x000056520cf6a593 in row_purge_reposition_pcur (mode=mode@entry=2, node=node@entry=0x56521045cc40, mtr=mtr@entry=0x148f102bb6f0) at /test/10.9_dbg/storage/innobase/row/row0purge.cc:82
      		 
      #10 0x000056520cf6e974 in row_purge_reset_trx_id (node=node@entry=0x56521045cc40, mtr=mtr@entry=0x148f102bb6f0) at /test/10.9_dbg/storage/innobase/row/row0purge.cc:688
      		 
      #11 0x000056520cf71043 in row_purge_record_func (node=node@entry=0x56521045cc40, undo_rec=undo_rec@entry=0x56521046ae00 "", thr=thr@entry=0x56521045ca60, updated_extern=<optimized out>) at /test/10.9_dbg/storage/innobase/row/row0purge.cc:1112
      		 
      #12 0x000056520cf73889 in row_purge (thr=0x56521045ca60, undo_rec=0x56521046ae00 "", node=0x56521045cc40) at /test/10.9_dbg/storage/innobase/row/row0purge.cc:1156
      		 
      #13 row_purge_step (thr=thr@entry=0x56521045ca60) at /test/10.9_dbg/storage/innobase/row/row0purge.cc:1205
      		 
      #14 0x000056520cee7fb2 in que_thr_step (thr=0x56521045ca60) at /test/10.9_dbg/storage/innobase/que/que0que.cc:653
      		 
      #15 que_run_threads_low (thr=0x56521045ca60) at /test/10.9_dbg/storage/innobase/que/que0que.cc:709
      		 
      #16 que_run_threads (thr=thr@entry=0x56521045ca60) at /test/10.9_dbg/storage/innobase/que/que0que.cc:729
      		 
      #17 0x000056520cfbc8fd in srv_task_execute () at /test/10.9_dbg/storage/innobase/srv/srv0srv.cc:1625
      		 
      #18 purge_worker_callback () at /test/10.9_dbg/storage/innobase/srv/srv0srv.cc:1869
      		 
      #19 0x000056520d1a6bda in tpool::task_group::execute (this=0x56520e51ef20 <purge_task_group>, t=t@entry=0x56520e4ec2a0 <purge_worker_task>) at /test/10.9_dbg/tpool/task_group.cc:55
      		 
      #20 0x000056520d1a6c63 in tpool::task::execute (this=0x56520e4ec2a0 <purge_worker_task>) at /test/10.9_dbg/tpool/task.cc:32
      		 
      #21 0x000056520d1a58f9 in tpool::thread_pool_generic::worker_main (this=0x5652103c9190, thread_var=0x5652103d89b0) at /test/10.9_dbg/tpool/tpool_generic.cc:588
      		 
      #22 0x000056520d1a5c30 in std::__invoke_impl<void, void (tpool::thread_pool_generic::*)(tpool::worker_data*), tpool::thread_pool_generic*, tpool::worker_data*> (__t=<optimized out>, __f=<optimized out>) at /usr/include/c++/9/bits/invoke.h:89
      		 
      #23 std::__invoke<void (tpool::thread_pool_generic::*)(tpool::worker_data*), tpool::thread_pool_generic*, tpool::worker_data*> (__fn=<optimized out>) at /usr/include/c++/9/bits/invoke.h:95
      		 
      #24 std::thread::_Invoker<std::tuple<void (tpool::thread_pool_generic::*)(tpool::worker_data*), tpool::thread_pool_generic*, tpool::worker_data*> >::_M_invoke<0ul, 1ul, 2ul> (this=<optimized out>) at /usr/include/c++/9/thread:244
      		 
      #25 std::thread::_Invoker<std::tuple<void (tpool::thread_pool_generic::*)(tpool::worker_data*), tpool::thread_pool_generic*, tpool::worker_data*> >::operator() (this=<optimized out>) at /usr/include/c++/9/thread:251
      		 
      #26 std::thread::_State_impl<std::thread::_Invoker<std::tuple<void (tpool::thread_pool_generic::*)(tpool::worker_data*), tpool::thread_pool_generic*, tpool::worker_data*> > >::_M_run (this=<optimized out>) at /usr/include/c++/9/thread:195
      		 
      #27 0x0000148f29375de4 in ?? () from /usr/lib/x86_64-linux-gnu/libstdc++.so.6
      		 
      #28 0x0000148f2948f609 in start_thread (arg=<optimized out>) at pthread_create.c:477
      		 
      #29 0x0000148f2907b133 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

      Bug confirmed present in:
      MariaDB: 10.6.9 (dbg), 10.7.5 (dbg), 10.8.4 (dbg), 10.9.2 (dbg), 10.10.0 (dbg)

      Bug (or feature/syntax) confirmed not present in:
      MariaDB: 10.3.36 (dbg), 10.3.36 (opt), 10.4.26 (dbg), 10.4.26 (opt), 10.5.17 (dbg), 10.5.17 (opt), 10.6.9 (opt), 10.7.5 (opt), 10.8.4 (opt),10.9.2 (opt), 10.10.0 (opt)
      MySQL: 5.5.62 (dbg), 5.5.62 (opt), 5.6.51 (dbg), 5.6.51 (opt), 5.7.38 (dbg), 5.7.38 (opt), 8.0.29 (dbg), 8.0.29 (opt)

      Attachments

        Issue Links

          is caused by

          Task - A task that needs to be done. MDEV-25691 Simplify handlerton::drop_database for InnoDB

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25748 DROP DATABASE drops unrelated FOREIGN KEY constraints

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25749 DROP DATABASE in InnoDB is case-insensitive

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Activity

            marko Marko Mäkelä added a comment -

            Here is a deterministic test case:

            --source include/have_innodb.inc
            		 
            --source include/have_case_sensitive_file_system.inc
            		 
            SET @save_fpt=@@GLOBAL.innodb_file_per_table;
            		 
            SET GLOBAL innodb_file_per_table=0;
            		 
            CREATE DATABASE Db;
            		 
            CREATE TABLE Db.t1 (c1 INT KEY) ENGINE=InnoDB;
            		 
            CREATE DATABASE DB;
            		 
            DROP DATABASE DB;
            		 
            SELECT NAME FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'D%';
            		 
            DROP DATABASE Db;
            		 
            SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'D%';
            		 
            SET GLOBAL innodb_file_per_table=@save_fpt;

            And a fix:

            diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
            		 
            index 300a1cd83ae..b676f83b8a3 100644
            		 
            --- a/storage/innobase/handler/ha_innodb.cc
            		 
            +++ b/storage/innobase/handler/ha_innodb.cc
            		 
            @@ -1476,7 +1476,8 @@ static void innodb_drop_database(handlerton*, char *path)
            		 
                 "WHILE 1 = 1 LOOP\n"
            		 
                 "  FETCH tab INTO tid,name;\n"
            		 
                 "  IF (SQL % NOTFOUND) THEN EXIT; END IF;\n"
            		 
            -    "  IF SUBSTR(name, 0, LENGTH(:db)) <> :db THEN EXIT; END IF;\n"
            		 
            +    "  IF TO_BINARY(SUBSTR(name, 0, LENGTH(:db))) <> TO_BINARY(:db)"
            		 
            +    " THEN EXIT; END IF;\n"
            		 
                 "  DELETE FROM SYS_COLUMNS WHERE TABLE_ID=tid;\n"
            		 
                 "  DELETE FROM SYS_TABLES WHERE ID=tid;\n"
            		 
                 "  OPEN idx;\n"

            Without the fix, the table Db.t1 would be wrongly dropped. Something similar was fixed in MDEV-25748 earlier.

            marko Marko Mäkelä added a comment - Here is a deterministic test case: --source include/have_innodb.inc --source include/have_case_sensitive_file_system.inc SET @save_fpt=@@ GLOBAL .innodb_file_per_table; SET GLOBAL innodb_file_per_table=0; CREATE DATABASE Db; CREATE TABLE Db.t1 (c1 INT KEY ) ENGINE=InnoDB; CREATE DATABASE DB; DROP DATABASE DB; SELECT NAME FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'D%' ; DROP DATABASE Db; SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'D%' ; SET GLOBAL innodb_file_per_table=@save_fpt; And a fix: diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index 300a1cd83ae..b676f83b8a3 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -1476,7 +1476,8 @@ static void innodb_drop_database(handlerton*, char *path) "WHILE 1 = 1 LOOP\n" " FETCH tab INTO tid,name;\n" " IF (SQL % NOTFOUND) THEN EXIT; END IF;\n" - " IF SUBSTR(name, 0, LENGTH(:db)) <> :db THEN EXIT; END IF;\n" + " IF TO_BINARY(SUBSTR(name, 0, LENGTH(:db))) <> TO_BINARY(:db)" + " THEN EXIT; END IF;\n" " DELETE FROM SYS_COLUMNS WHERE TABLE_ID=tid;\n" " DELETE FROM SYS_TABLES WHERE ID=tid;\n" " OPEN idx;\n" Without the fix, the table Db.t1 would be wrongly dropped. Something similar was fixed in MDEV-25748 earlier.

            People

              marko Marko Mäkelä
              Roel Roel Van de Paar
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.