Dave Gosselin 650e852e24 MDEV-33616: Routines of a mixed case database are not listed ...

At lower_case_table_names=2 this returns nothing.

  CREATE DATABASE Db1;
  CREATE FUNCTION Db1.f1(a INT) RETURNS INT RETURN a;
  SELECT ROUTINE_NAME FROM information_schema.ROUTINES
   WHERE ROUTINE_SCHEMA='Db1';

mysql.proc records the function's database as db1, in lower case.
Creating a routine lower-cases its database name whenever
lower_case_table_names is anything but 0, at sql/sp_head.h:121.  The
datadir, SCHEMATA and DATABASE() all keep Db1.

CALL Db1.f1() still works, because calling a routine lower-cases the
database name too and then searches mysql.proc for db1.  The query
above never lower-cases it.  It searches for Db1, and mysql.proc.db
collates utf8mb3_bin, so the comparison runs byte for byte and no row
matches.

At setting 1 the server lower-cases the filter value as well, at
sql/sql_show.cc:4394, and lower-cases every name it stores, so the
query and the table always agree.  Setting 2 lower-cases the routine's
copy and nothing else.

The fix lower-cases the filter value before the search.

Sorting the same query brings the row back.

  SELECT ROUTINE_NAME FROM information_schema.ROUTINES
   WHERE ROUTINE_SCHEMA='Db1' ORDER BY ROUTINE_NAME;

The sort keeps the filter from reaching that search.  The server reads
all of mysql.proc instead, then applies the WHERE to ROUTINE_SCHEMA,
which compares case insensitively.  That shape answered correctly all
along.

The same search fills PARAMETERS and backs SHOW FUNCTION STATUS, SHOW
PROCEDURE STATUS, SHOW PACKAGE STATUS and SHOW PACKAGE BODY STATUS.
Every one returned nothing for Db1.  mariadb-dump lists routines with
SHOW FUNCTION STATUS WHERE Db=..., at client/mysqldump.cc:2859, which
is the main.mysqldump failure.

Setting 0 keeps Db1 and db1 as two databases holding two routines.  A
case sensitive volume confirms both stay distinct before and after this
change.  beb9a5459d (MDEV-20609) added the search in 10.11.1.
main.lowercase_routines runs both query shapes.

2026-09-22 08:27:47 -04:00

364 KiB

Raw Permalink Blame History

View Raw