|
INSTALL SONAME 'ha_federatedx';
|
eval create server s foreign data wrapper mysql options (host "127.0.0.1", database "test", user "root", port $MASTER_MYPORT);
|
|
CREATE TABLE t (a INT);
|
CREATE TABLE t_fed (a INT) ENGINE=FEDERATED CONNECTION="s/t";
|
SELECT * FROM t_fed;
|
|
UPDATE mysql.servers SET port = 12999;
|
FLUSH PRIVILEGES;
|
FLUSH TABLES;
|
|
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test';
|
|
# Cleanup
|
DROP TABLE t_fed, t;
|
DROP SERVER s;
|
UNINSTALL SONAME 'ha_federatedx';
|
The test case above seems artificial, but it just imitates a situation when the remote server was reachable upon creation of a federated table, but stopped being reachable since, which is quite realistic.
So, the query from information schema ends with
|
10.3 92be8d20
|
mysqltest: At line 12: query 'SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test'' failed: 2003: Received error: 2003 : Can't connect to MySQL server on '127.0.0.1' (111 "Connection refused")
|
If we change the query to
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE = 'FEDERATED';
|
it succeeds with a warning, but the warning text is really weird, it appears that it comes from somewhere else:
|
10.3 92be8d20, also in 10.4/10.5
|
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE = 'FEDERATED';
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT MAX_INDEX_LENGTH TEMPORARY
|
def test t_fed BASE TABLE FEDERATED 10 Fixed 0 0 0 0 0 0 NULL NULL NULL NULL latin1_swedish_ci NULL 0N
|
Warnings:
|
Warning 1939 Engine PERFORMANCE_SCHEMA failed to discover table `performance_schema`.`users` with 'CREATE TABLE users(USER CHAR(128) collate utf8_bin default null comment 'The connection''s client user name for the connection, or NULL if an internal thread.',CURRENT_CONNECTIONS bigint not null comment 'Current connections for the user.',TOTAL_CONNECTIONS bigint not null comment 'Total connections for the user.')'
|
|
10.6 79dc3989f, also in 10.7-10.11
|
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE = 'FEDERATED';
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT MAX_INDEX_LENGTH TEMPORARY
|
def test t_fed BASE TABLE FEDERATED 10 Fixed 0 0 0 0 0 0 NULL NULL NULL NULL latin1_swedish_ci NULL 0N
|
Warnings:
|
Warning 1356 View 'sys.x$waits_global_by_latency' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
|
|