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

select from information_schema.schemata does not match output from show databases

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.6.16
    • None
    • Server
    • 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?

      Attachments

        Activity

          People

            Unassigned Unassigned
            psumner Phil Sumner
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.