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

I_S.TABLES inconsistencies with tables with unknown storage engine

    Details

    • Sprint:
      10.1.22

      Description

      When having tables with unknown storage engine, e.g. due to a missing plugin, information for such tables is only read from INFORMATION_SCHEMA.TABLES on SELECT * FROM TABLES ...", not when trying to get the count of tables with SELECT COUNT(*) or when trying to read specific tables only. (found while trying to come up with a fix for MDEV-11942)

      How to reproduce:

      INSTALL SONAME 'ha_blackhole';
      CREATE TABLE test.t1 (id int primary key) ENGINE=BLACKHOLE;
      UNINSTALL PLUGIN BLACKHOLE;
      

      Now we have a table with unknown engine.

      MariaDB [information_schema]> select * from tables where table_schema="test"\G
      *************************** 1. row ***************************
        TABLE_CATALOG: def
         TABLE_SCHEMA: test
           TABLE_NAME: t1
           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: Unknown storage engine 'BLACKHOLE'
      1 row in set, 1 warning (0.00 sec)
      

      SELECT * found the table entry just fine and correctly reported the engine as unknown.

      Now lets try to only read specific columns instead of '*'

      MariaDB [information_schema]> select table_name, table_comment from tables where table_schema="test"\G
      Empty set (0.00 sec)
      

      The table is no longer found, but COUNT(*) works here:

      MariaDB [information_schema]> select count(*) from tables where table_schema="test"\G
      *************************** 1. row ***************************
      count(*): 1
      1 row in set (0.00 sec)
      

      Now instead of searching by schema lets search by table comment instead:

      MariaDB [information_schema]> select * from tables where table_comment like "Unknown storage engine %"\G
      *************************** 1. row ***************************
        TABLE_CATALOG: def
         TABLE_SCHEMA: test
           TABLE_NAME: t1
           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: Unknown storage engine 'BLACKHOLE'
      1 row in set, 1 warning (0.01 sec)
      

      This worked, but asking for specific columns again doesn't:

      MariaDB [information_schema]> select table_name, table_comment from tables where table_comment like "Unknown storage engine %"\G
      Empty set (0.00 sec)
      

      And now COUNT(*) doesn't work anymore either:

      MariaDB [information_schema]> select count(*) from tables where table_comment like "Unknown storage engine %"\G
      *************************** 1. row ***************************
      count(*): 0
      1 row in set (0.01 sec)
      

      Expected result: changes in the filed list, but not on the WHERE condition, should not change the query results

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                serg Sergei Golubchik
                Reporter:
                hholzgra Hartmut Holzgraefe
              • Votes:
                1 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: