How ProxySQL handles if Slave is in Error state for xyz reasons

[Amit Rout]

Hi Team, I am just learning the ProxySQL and have not installed yet. Few queries if you can help me to answer that would definitely help.

- How ProxySQL handles when there is an error in slave ( for any reasons ) ,basically when slave is broken. Is there a setting we can do where it'll stop the instance or it's taken care automatically with the settings ?

- Replica Lag : For an instance we have a SELECT into one slave and at the same time replica lag goes up to the max limit . ( I think Server will be shunned )

 So what happens to the SELECT which is running ?

       What happens to the thread which is connected already .

Thanks

Amit

[Amit Rout]

Team, Any help is appreciated. Thanks in Advance.

[Data Guy]

Hi Amit,

     Good to hear you have started to learn proxysql. 

-  How ProxySQL handles when there is an error in slave ( for any reasons ) ,basically when slave is broken. Is there a setting we can do where it'll stop the instance or it's taken care automatically with the settings ?

• proxysql can make the replica node shunned if there is an error. 

1) And it is not by default. you need to keep the max_replication_lag  value greater than 0 in mysql_servers table(By default, it is 0. It won't do any replication checks).

2) This variable mysql-monitor_replication_lag_interval  monitor the replication status of the replicas on that frequency. 

3) Just ensure the monitor user has enough privileges(Replication client) to check this.

 - Replica Lag : For an instance we have a SELECT into one slave and at the same time replica lag goes up to the max limit . ( I think Server will be shunned )

 So what happens to the SELECT which is running ?

• I tested this case, it rerouted the existing select to the primary.(Your select starts executes from scratch) Basically, the client gets error as follow

ERROR 2013 (HY000): Lost connection to MySQL server during query

No connection. Trying to reconnect...

Connection id:    38

Current database: *** NONE ***

In the proxysql error log, it prints like 

2021-09-08 17:09:55 MySQL_Session.cpp:4063:handler(): [ERROR] Detected a lagging server during query: 127.0.0.1, 20945

2021-09-08 17:09:55 MySQL_Session.cpp:4075:handler(): [WARNING] Retrying query.

What happens to the thread which is connected already .

• It keeps connected until it is idle. when I tried to refresh it, got the error and it reinitiated a new thread on the primary.

mysql> \s

ERROR 2013 (HY000): Lost connection to MySQL server during query

No connection. Trying to reconnect...

Connection id:    34

Current database: *** NONE ***

In error log:      [ERROR] Detected a lagging server during query: 127.0.0.1, 20945

There is no difference in select and threads connected, both behaves the same. It reinitiated the process on the healthy node available.

Hope this clears your doubts.

[Amit Rout]

Thanks so much. That helps.