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).
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 ...
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)
|
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
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).
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 ...
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.