[MDEV-28802] DROP DATABASE still is case-insensitive Created: 2022-06-11  Updated: 2022-06-13  Resolved: 2022-06-13

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.6.2, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.6.9, 10.7.5, 10.8.4, 10.9.2

Type: Bug Priority: Critical
Reporter: Roel Van de Paar Assignee: Marko Mäkelä
Resolution: Fixed Votes: 0
Labels: regression

Issue Links:
Problem/Incident
is caused by MDEV-25691 Simplify handlerton::drop_database fo... Closed
Relates
relates to MDEV-25748 DROP DATABASE drops unrelated FOREIGN... Closed
relates to MDEV-25749 DROP DATABASE in InnoDB is case-insen... Closed

 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)

 Comments   
Comment by Marko Mäkelä [ 2022-06-13 ]

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.

Generated at Thu Feb 08 10:03:35 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.