In my MySQL database on my PC, I have a table that is on another MySQL server in reality, seen from my local MySQL as a federated table. I access that table in a stored procedure.
Since "another MySQL server" can always just be unreachable, I want to put a continue handler into my stored procedures to detect it when the connection is gone, but I cannot figure out, how to do that, or at least not all the time.
This is an example procedure:
DROP PROCEDURE IF EXISTS `ABCDE_SELECT`;
DELIMITER $$
CREATE PROCEDURE `ABCDE_SELECT` ()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, 10000, SQLSTATE 'HY000', SQLSTATE '10000', 1296, 2003, 10060, SQLSTATE '10060' SELECT "Error!" AS RESULT;
SELECT ID, Message_Timestamp, Message FROM test_table_federated;
END$$
DELIMITER ;
When I call it with the other server present, it gives out the table content as expected.
When I call it with the other server gone, it gives out "Error!" as expected.
When I now replace the SELECT statement with an INSERT[1] statement...
...and call the procedure with the other server present, it inserts and doesn't give out a thing as expected.
...and call the procedure with the other server gone, it raises an error:
Error Code: 1296. Got error 10000 'Error on remote system: 2003: Can't connect to MySQL server on '...' (10060)' from FEDERATED
Why is the CONTINUE HANDLER not working in that case?
[1]: e.g. INSERT INTO test_table_federated (ID, Message_Timestamp, Message) VALUES (NULL, NOW(6), "Hello!");
