1

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

1 Answer 1

0

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.

8
  • 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
    Sep 1, 2020 at 14:53
  • 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?
    – Sevak
    Sep 3, 2020 at 13:06
  • err and @err are totally different things.
    – Rick James
    Sep 4, 2020 at 3:18
  • 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
    Sep 4, 2020 at 8:02
  • 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 |`
    – Sevak
    Sep 4, 2020 at 8:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.