[MDEV-11943] I_S.TABLES inconsistencies with tables with unknown storage engine - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.1.21
Fix Version/s: 10.1.22, 10.2.5
Component/s: Admin statements, Server
Labels:
None
Environment:
linux

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

relates to

MDEV-11942 BLACKHOLE is no longer active in 10.1 by default, mysql_upgrade not handling the situation

Closed

Activity

Ascending order - Click to sort in descending order

Elena Stepanova added a comment - 2017-02-01 22:45

Inconsistencies are reproducible easily, except for this one:

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

MariaDB [information_schema]> select count(*) from tables where table_comment like "Unknown storage engine %"\G

It's not "anymore", this particular COUNT didn't work before. The one that worked was different:

MariaDB [information_schema]> select count(*) from tables where table_schema="test"\G

(and it still works).

Elena Stepanova added a comment - 2017-02-01 22:45 Inconsistencies are reproducible easily, except for this one: And now COUNT(*) doesn't work anymore either. MariaDB [information_schema]> select count(*) from tables where table_comment like "Unknown storage engine %"\G It's not "anymore", this particular COUNT didn't work before. The one that worked was different: MariaDB [information_schema]> select count (*) from tables where table_schema= "test" \G (and it still works).

Hartmut Holzgraefe added a comment - 2017-02-02 01:54

That's what I meant, after changing the WHERE condition COUNT result doesn't match with number of rows returned by SELECT * with same WHERE condition anymore ...

Hartmut Holzgraefe added a comment - 2017-02-02 01:54 That's what I meant, after changing the WHERE condition COUNT result doesn't match with number of rows returned by SELECT * with same WHERE condition anymore ...

Hartmut Holzgraefe added a comment - 2017-02-03 12:24 - edited

Tracked it down a little more, results change depending on actual columns being queries,
e.g. having CHECK_TIME or UPDATE_TIME in the field list the rows for tables
with unknown engine show up just fine:

MariaDB [information_schema]> select TABLE_SCHEMA, TABLE_NAME from tables where table_comment="Unknown storage engine 'BLACKHOLE'";

Empty set (0.01 sec)

MariaDB [information_schema]> select TABLE_SCHEMA, TABLE_NAME, CHECK_TIME from tables where table_comment="Unknown storage engine 'BLACKHOLE'";+--------------+------------+------------+

| TABLE_SCHEMA | TABLE_NAME | CHECK_TIME |

+--------------+------------+------------+

| test         | t1         | NULL       |

+--------------+------------+------------+

1 row in set, 1 warning (0.01 sec)

MariaDB [information_schema]> select TABLE_SCHEMA, TABLE_NAME, UPDATE_TIME from tables where table_comment="Unknown storage engine 'BLACKHOLE'";

+--------------+------------+-------------+

| TABLE_SCHEMA | TABLE_NAME | UPDATE_TIME |

+--------------+------------+-------------+

| test         | t1         | NULL        |

+--------------+------------+-------------+

1 row in set, 1 warning (0.01 sec)

Hartmut Holzgraefe added a comment - 2017-02-03 12:24 - edited Tracked it down a little more, results change depending on actual columns being queries, e.g. having CHECK_TIME or UPDATE_TIME in the field list the rows for tables with unknown engine show up just fine: MariaDB [information_schema]> select TABLE_SCHEMA, TABLE_NAME from tables where table_comment="Unknown storage engine 'BLACKHOLE'"; Empty set (0.01 sec) MariaDB [information_schema]> select TABLE_SCHEMA, TABLE_NAME, CHECK_TIME from tables where table_comment="Unknown storage engine 'BLACKHOLE'";+--------------+------------+------------+ | TABLE_SCHEMA | TABLE_NAME | CHECK_TIME | +--------------+------------+------------+ | test | t1 | NULL | +--------------+------------+------------+ 1 row in set, 1 warning (0.01 sec) MariaDB [information_schema]> select TABLE_SCHEMA, TABLE_NAME, UPDATE_TIME from tables where table_comment="Unknown storage engine 'BLACKHOLE'"; +--------------+------------+-------------+ | TABLE_SCHEMA | TABLE_NAME | UPDATE_TIME | +--------------+------------+-------------+ | test | t1 | NULL | +--------------+------------+-------------+ 1 row in set, 1 warning (0.01 sec)

Hartmut Holzgraefe added a comment - 2017-02-07 10:11 - edited

If any of these columns is used in the query, either as result field or as part of the WHERE condition, the result correctly includes tables for which the engine plugin is not loaded (implicit use by SELECT * also counts):

AUTO_INCREMENT
AVG_ROW_LENGTH
CHECKSUM
CHECK_TIME
CREATE_OPTIONS
CREATE_TIME
DATA_FREE
DATA_LENGTH
INDEX_LENGTH
MAX_DATA_LENGTH
ROW_FORMAT
TABLE_ROWS
UPDATE_TIME

If only these columns are used these tables are somehow ignored:

ENGINE
TABLE_CATALOG
TABLE_COLLATION
TABLE_COMMENT
TABLE_NAME
TABLE_SCHEMA
TABLE_TYPE
VERSION

Hartmut Holzgraefe added a comment - 2017-02-07 10:11 - edited If any of these columns is used in the query, either as result field or as part of the WHERE condition, the result correctly includes tables for which the engine plugin is not loaded (implicit use by SELECT * also counts): AUTO_INCREMENT AVG_ROW_LENGTH CHECKSUM CHECK_TIME CREATE_OPTIONS CREATE_TIME DATA_FREE DATA_LENGTH INDEX_LENGTH MAX_DATA_LENGTH ROW_FORMAT TABLE_ROWS UPDATE_TIME If only these columns are used these tables are somehow ignored: ENGINE TABLE_CATALOG TABLE_COLLATION TABLE_COMMENT TABLE_NAME TABLE_SCHEMA TABLE_TYPE VERSION

Hartmut Holzgraefe added a comment - 2017-02-07 10:34

More 'fun': when uninstalling the ARCHIVE engine plugin the ARCHIVE tables are not listed in I_S.TABLES at all, no matter what columns are used.

So my "check INFORMATION_SCHEMA for tables with unknown engines, then issue appropriate INSTALL SONAME commands for these" plan for ~~MDEV-11942~~ works out for BLACKHOLE, but not for ARCHIVE (and maybe others).

Hartmut Holzgraefe added a comment - 2017-02-07 10:34 More 'fun': when uninstalling the ARCHIVE engine plugin the ARCHIVE tables are not listed in I_S.TABLES at all, no matter what columns are used. So my "check INFORMATION_SCHEMA for tables with unknown engines, then issue appropriate INSTALL SONAME commands for these" plan for MDEV-11942 works out for BLACKHOLE, but not for ARCHIVE (and maybe others).

Hartmut Holzgraefe added a comment - 2017-02-15 09:03

For BLACKHOLE I understand things a tiny little bit better now: BLACKHOLE tables will only appear in I_S.TABLES result when the plugin isn't loaded if one of the I_S.TABLES columns marked with open_table=OPEN_FULL_TABLE in ST_FIELD_INFO tables_fields_info[] in sql/sql_show.cc is touched by the query, either in the WHERE condition or the result field list

Why I_S.TABLES behaves differently on ARCHIVE than on BLACKHOLE when both plugins are not loaded is still a mystery to me though ...

Hartmut Holzgraefe added a comment - 2017-02-15 09:03 For BLACKHOLE I understand things a tiny little bit better now: BLACKHOLE tables will only appear in I_S.TABLES result when the plugin isn't loaded if one of the I_S.TABLES columns marked with open_table=OPEN_FULL_TABLE in ST_FIELD_INFO tables_fields_info[] in sql/sql_show.cc is touched by the query, either in the WHERE condition or the result field list Why I_S.TABLES behaves differently on ARCHIVE than on BLACKHOLE when both plugins are not loaded is still a mystery to me though ...

Sergei Golubchik added a comment - 2017-03-03 16:16

I've now fixed the inconsistent behavior, that dependent on what columns were selected. Almost, if you only select the table name, the table isn't opened, so it's engine isn't known, no warning. If the table is opened, though, and the engine is not loaded, there will be a warning.

Sergei Golubchik added a comment - 2017-03-03 16:16 I've now fixed the inconsistent behavior, that dependent on what columns were selected. Almost, if you only select the table name, the table isn't opened, so it's engine isn't known, no warning. If the table is opened, though, and the engine is not loaded, there will be a warning.

People

Assignee:: Sergei Golubchik

Reporter:: Hartmut Holzgraefe

Votes:: 1 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2017-01-30 13:35

Updated:: 2017-03-10 17:55

Resolved:: 2017-03-10 17:54

Git Integration

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

MariaDB Server