Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.4, 11.8, 12.3, 11.8.6
-
None
-
Server version: 11.8.6-MariaDB-0+deb13u1 from Debian
source revision: 9bfea48ce1214cc4470f6f6f8a4e30352cef84e7
OS: Debian 13 (trixie), x86_64, kernel 6.8.x
Description
Summary
mariadbd crashes with SIGSEGV while optimizing a prepared statement
whose WHERE contains UPPER(<column>) IN (<bound parameters>). The
crash is in the optimizer condition-transform pass and is triggered by
the sargable_casefold optimization (new in 11.8). The whole server
goes down (every other connection is dropped), then restarts.
Server version
1
|
Server version: 11.8.6-MariaDB-0+deb13u1 from Debian
|
2
|
source revision: 9bfea48ce1214cc4470f6f6f8a4e30352cef84e7
|
3
|
OS: Debian 13 (trixie), x86_64, kernel 6.8.x
|
How to reproduce
The trigger is the combination of three things in a *prepared
statement*:
- a function wrapping the column on the left-hand side (UPPER(cs) /
UCASE(cs)), - an IN (...) list,
- whose elements are bound parameters (not literals).
Minimal, self-contained repro using a server-side PREPARE (run as a
user with CREATE):
1
|
CREATE TABLE mdb_casefold_repro (
|
2
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
3
|
cs VARCHAR(10) NOT NULL
|
4
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
|
5
|
|
6
|
INSERT INTO mdb_casefold_repro (cs) VALUES ('AA1AA'),('BB2BB'),('CC3CC');
|
7
|
|
8
|
PREPARE s FROM 'SELECT id FROM mdb_casefold_repro WHERE UPPER(cs) IN (?, ?)';
|
9
|
SET @a = 'AA1AA', @b = 'BB2BB';
|
10
|
EXECUTE s USING @a, @b; -- <-- server crashes here (SIGSEGV)
|
We observed the crash via the client/binary prepared-statement protocol
(PHP mysqli::prepare() + bind_param('iss', ...) + execute()),
which lands in the same Prepared_statement::execute ->
JOIN::optimize_inner path shown in the backtrace below.
What does NOT crash (same dataset, same session)
1
|
-- (a) no function on the column:
|
2
|
SELECT id FROM t WHERE cs IN (?, ?); -- OK
|
3
|
-- (b) function but single value, not an IN-list:
|
4
|
SELECT id FROM t WHERE UPPER(cs) = ?; -- OK
|
5
|
-- (c) function + IN-list but literal operands, not parameters:
|
6
|
SELECT id FROM t WHERE UPPER(cs) IN ('AA1AA','BB2BB'); -- OK
|
7
|
-- (d) the crashing form, but with sargable_casefold disabled:
|
8
|
SET SESSION optimizer_switch = 'sargable_casefold=off';
|
9
|
SELECT id FROM t WHERE UPPER(cs) IN (?, ?); -- OK
|
Only UPPER(col) IN (<bound params>) with sargable_casefold=on
(the default) crashes.
Root cause / workaround
sargable_casefold (on by default in 11.8) is the trigger. Disabling it
avoids the crash:
1
|
SET GLOBAL optimizer_switch = 'sargable_casefold=off';
|
This is the recommended server-side mitigation until a fix ships.
Applications can also avoid the pattern by not wrapping the column in
UPPER()/UCASE() on the left of an IN (?, ...) (e.g. rely on a
case-insensitive collation instead).
Backtrace
|
Show all |
|
1
|
mariadbd got signal 11 ;
|
2
|
Server version: 11.8.6-MariaDB-0+deb13u1 from Debian source revision: 9bfea48ce1214cc4470f6f6f8a4e30352cef84e7
|
3
|
|
4
|
Thread pointer: 0x70ed54000c68
|
5
|
stack_bottom = 0x70eda0304000 thread_stack 0x49000
|
6
|
my_print_stacktrace
|
7
|
handle_fatal_signal
|
8
|
libc.so.6(+0x3fdf0)
|
9
|
mariadbd(+0x6cc740)
|
10
|
Item_cond::do_transform(THD*, Item* (Item::*)(THD*, uchar*), uchar*, bool)
|
11
|
JOIN::transform_all_conds_and_on_exprs(THD*, Item* (Item::*)(THD*, uchar*))
|
12
|
JOIN::optimize_inner()
|
13
|
JOIN::optimize()
|
14
|
mysql_select(THD*, TABLE_LIST*, List<Item>&, ...)
|
15
|
handle_select(THD*, LEX*, select_result*, unsigned long long)
|
16
|
mysql_execute_command(THD*, bool)
|
17
|
Prepared_statement::execute(String*, bool)
|
18
|
Prepared_statement::execute_loop(String*, bool, uchar*, uchar*)
|
19
|
mysqld_stmt_execute(THD*, char*, unsigned int)
|
20
|
dispatch_command(enum_server_command, THD*, char*, unsigned int, bool)
|
21
|
do_command(THD*, bool)
|
22
|
do_handle_one_connection(CONNECT*, bool)
|
23
|
handle_one_connection
|
1
|
Query (...): SELECT id FROM netlog_notes WHERE anchor=2 AND netid=? AND UPPER(callsign) IN (?,?)
|
2
|
Status: NOT_KILLED
|
Relevant optimizer_switch flags at crash time (defaults):
sargable_casefold=on, cset_narrowing=on, sargable_casefold being
the decisive one per the bisection above.
Impact
- A single crafted/ordinary SELECT from any client crashes the whole
server, not just the issuing connection (DoS / availability). - Hit in normal application traffic (an ORM/data-layer that prepares
WHERE UPPER(col) IN (?, ...)), so it is reachable without malicious
intent.
Attachments
Issue Links
- is caused by
-
MDEV-31496 Make optimizer handle UCASE(varchar_col)=...
-
- Closed
-