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

Misleading results upon querying INFORMATION_SCHEMA for broken tables/views

    XMLWordPrintable

    Details

      Description

      Note: InnoDB is only needed to avoid extra warnings upon querying I_S

      --source include/have_innodb.inc
       
      create table tm (a int) engine=mrg_myisam union (t);
       
      --vertical_results
      select * from information_schema.tables where table_comment = "Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist";
      --echo ###########################
      select table_name from information_schema.tables where table_comment = "Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist";
       
      # Cleanup
      drop table tm;
      

      The first query selects all columns from the I_S table, and it returns the expected row, with a warning:

      10.2 059797ed

      MariaDB [test]> select * from information_schema.tables where table_comment = "Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist"
          -> \G
      *************************** 1. row ***************************
        TABLE_CATALOG: def
         TABLE_SCHEMA: test
           TABLE_NAME: tm
           TABLE_TYPE: BASE TABLE
               ENGINE: NULL
              VERSION: NULL
           ROW_FORMAT: NULL
           TABLE_ROWS: NULL
       AVG_ROW_LENGTH: NULL
          DATA_LENGTH: NULL
      MAX_DATA_LENGTH: NULL
         INDEX_LENGTH: NULL
            DATA_FREE: NULL
       AUTO_INCREMENT: NULL
          CREATE_TIME: NULL
          UPDATE_TIME: NULL
           CHECK_TIME: NULL
      TABLE_COLLATION: NULL
             CHECKSUM: NULL
       CREATE_OPTIONS: NULL
        TABLE_COMMENT: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
      1 row in set, 1 warning (0.057 sec)
       
      MariaDB [test]> show warnings;
      +---------+------+-----------------------------------------------------------------------------------------------------+
      | Level   | Code | Message                                                                                             |
      +---------+------+-----------------------------------------------------------------------------------------------------+
      | Warning | 1168 | Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist |
      +---------+------+-----------------------------------------------------------------------------------------------------+
      1 row in set (0.000 sec)
      

      The second query, with the same WHERE condition, attempts to select certain fields (table name in this case), and it returns an empty result set, no warnings:

      MariaDB [test]> select table_name from information_schema.tables where table_comment = "Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist";
      Empty set (0.021 sec)
      

      Same is true for views which suffer from "references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them".

      It probably can be explained by the internals of I_S, but it's still misleading and potentially problematic.

      In my somewhat realistic use case, I am trying to take periodic mysqldump from the schema. When broken views or merge tables exist (which can easily happen if an underlying table is carelessly dropped), the whole dump will fail. So, before creating the dump, I check the schema for such broken ("widow") views and tables and drop them. And it appears that I can easily find them via a query from I_S, but when my periodic job selects the table_schema/table_name, it comes up with nothing. It is of course easy to work around when you know the problem (select all and then only use the columns you need), but one would have to know that a workaround is needed.

      Reproducible on all of 10.x and MySQL 5.7. MySQL 8.0 doesn't seem to return anything even to the SELECT * query.

        Attachments

          Activity

            People

            Assignee:
            sanja Oleksandr Byelkin
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.