Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.2, 10.3, 10.4, 10.5, 10.6, 10.7
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.