[MDEV-33311] select from information_schema.schemata does not match output from show databases Created: 2024-01-25  Updated: 2024-01-25

Status: Open
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.6.16
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Phil Sumner Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None


 Description   

3-node Galera cluster - CentOS 7.9.2009

I used a query left joining information_schema.schema_priveleges to information_schema.schemata to determine and remove grants on non-existent objects:

– Identify users with privileges on non-existent schema
select distinct sp.GRANTEE, sp.TABLE_SCHEMA, sp.PRIVILEGE_TYPE
from information_schema.SCHEMA_PRIVILEGES sp
left join information_schema.SCHEMATA s
on sp.TABLE_SCHEMA = s.SCHEMA_NAME
where s.SCHEMA_NAME is null
order by sp.TABLE_SCHEMA asc;

This query also returned privs granted to a bunch of schema that do exist and contain tables/data. I then adjusted the query just to return the schema names:

select distinct sp.TABLE_SCHEMA
from information_schema.SCHEMA_PRIVILEGES sp
left join information_schema.SCHEMATA s
on sp.TABLE_SCHEMA = s.SCHEMA_NAME
where s.SCHEMA_NAME is null;

On further checking, we're missing 10 schema from the schemata table that exist in the output of "show databases"

I have been unable to replicate this on a standalone database by creating folders in the datadir and I don't have a handy galera cluster that I can break semi-intentionally.

I'm not sure what information might be useful here, the question is: is it possible to somehow force a sync of the schemata table with what's on disk?


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