Thesql client auto reconnect in case of ERROR 2013

clientMySQLperconaproxysql

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

Best Answer

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.

Related Question