mysql client auto reconnect in case of ERROR 2013

Question

I'm searching for a way how to instruct mysql client to reconnect in case of ERROR 2013. I'm testing PXC with ProxySQL and want to keep sql statements flowing from the client in case when the writer node get killed and new one is promoted. Is it possible for mysql client to reconnect when server goes down during the query? Can mysql client rerun the sql query (insert, update, ...)? With Sysbench it is possible if setting the --mysql-ignore-errors=all parameter.

(from Comment) I'll be calling that SP from a custom lua script, where I'll test the 'error 2013' condition and in that case I'll rerun that query. Does this make sense, or the value of @err set by the error handler can't be passed to the script, because the session will just die, when the mysqld will get killed?

DELIMITER // 
CREATE PROCEDURE sbtest.InsertIntoTable (
 IN trnid INT, IN unixtime INT,
 OUT err INT)
BEGIN DECLARE CONTINUE HANDLER FOR 2013 SET @err = 1;
INSERT
 INTO sbtest.failover_test ( node, trn_id, unix_time )
 VALUES (@@hostname, trnid, unixtime);
END// 

my table:

failover_test | CREATE TABLE `failover_test` (
 `id` int NOT NULL AUTO_INCREMENT,
 `node` varchar(255) DEFAULT NULL,
 `trn_id` int DEFAULT NULL,
 `unix_time` int DEFAULT NULL,
 `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=116837 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

--

CALL sbtest.InsertIntoTable(1, 1599207191, @err);SELECT @err

Answer 1

Encapsulate the query in a stored procedure. In the SP, test for errors. When one happens, reconnect and retry the query.

In general, autoreconnect is not wise. This is because you might crash in the middle of a multi-statement transaction. If you don't notice the crash, it could start a new transaction in the middle.

So, depending on how critical the data is, be careful.

• Sevak: I'll be calling that SP from a custom lua script, where I'll test the 'error 2013' condition and in that case I'll rerun that query. Does this make sense, or the value of @err set by the error handler can't be passed to the script, because the session will just die, when the mysqld will get killed? DELIMITER // CREATE PROCEDURE sbtest.InsertIntoTable ( IN trnid INT, IN unixtime INT, OUT err INT) BEGIN DECLARE CONTINUE HANDLER FOR 2013 SET err = 1; INSERT INTO sbtest.failover_test ( node, trn_id, unix_time ) VALUES (@@hostname, trnid, unixtime); END//
• Sevak: still I'm unable to get that error handler working. even when I try to catch another error code, the value of err variable is always NULL. What I'm doing wrong?
• Rick James: err and @err are totally different things.
• Sevak: oh yes, my fault, I was confusing err and @err variables. but still, there's no value passed into it from the error handler. now for the testing purpose I'm trying to catch a different error code, e.g. 1366 when inserting a invalid value into a column. but still can't get a that @err value out of the SP.
• Sevak: this is my table: failover_test | CREATE TABLE failover_test` ( id int NOT NULL AUTO_INCREMENT, node varchar(255) DEFAULT NULL, trn_id int DEFAULT NULL, unix_time int DEFAULT NULL, created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=116837 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |`