How to check (fast) if a federated table is connected?

Question

I have 5 federated tables. Each table is connected to another database. Now I do some stuff in a loop and the first thing I do in the loop is some stuff with the federated table. Because the database to which the federated table is linked is not always online I have a error handler which catches the error when the database is offline.

Unfortunately when the database is offline the query (a simple select query) takes about 5 seconds before it returns a error that the database is not online. If the database is then online I skip the rest of the code in the loop.

Now my problem is is that that routine needs to be executed every 5 seconds and needs to be finished in 5 seconds. However when the databases are offline to which the federated table is linked then it the failed attemps to connect to the database takes up: 5 db's * 5 sec = 25 seconds

So my question is it possible to check very fast in SQL if the database to which the federated table is linked to is online?

Answer 1

From: http://onlamp.com/pub/a/databases/2006/08/10/mysql-federated-tables.html?page=3

Checking Whether A Federated Table Is Connected

Because federated tables strictly depend on active connections, it's a good idea to check whether the data is reachable before using a table. Here's a function that does just that, by attempting to query the information schema about a federated table and immediately checking whether it caught SQLSTATE 'HY000'.

CREATE FUNCTION fed_table_available( 
    p_table_schema varchar(50), 
    p_table_name varchar(50) )
RETURNS BOOLEAN
BEGIN
    DECLARE connection_dead BOOLEAN DEFAULT FALSE;
    DECLARE how_many int;
    DECLARE curx CURSOR FOR
        SELECT COUNT(*)
        FROM information_schema.TABLES
        WHERE
            TABLE_SCHEMA = p_table_schema
            AND TABLE_NAME = p_table_name;
    DECLARE CONTINUE HANDLER FOR SQLSTATE 'HY000'
        SET connection_dead = TRUE;
    OPEN curx;
    IF connection_dead THEN
        RETURN FALSE;
    ELSE
        FETCH curx into how_many;
        CLOSE curx;
        RETURN (how_many > 0);
    END IF;
END