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

I_S.TABLES inconsistencies with tables with unknown storage engine

Details

    • 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

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-11942 BLACKHOLE is no longer active in 10.1 by default, mysql_upgrade not handling the situation

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Transition Time In Source Status Execution Times
            Elena Stepanova made transition -
            Open Confirmed
            		2d 9h 10m 1
            Sergei Golubchik made transition -
            Confirmed In Progress
            		29d 14h 15m 1
            Sergei Golubchik made transition -
            Stalled In Progress
            		4d 1h 34m 1
            Sergei Golubchik made transition -
            In Progress Stalled
            		3h 16m 2
            Sergei Golubchik made transition -
            Stalled Closed
            		3d 3m 1

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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