MySQL Bugs: #47251: proxy stop functioning when master is shutdown

Bug #47251	proxy stop functioning when master is shutdown
Submitted:	10 Sep 2009 13:42	Modified:	14 Sep 2009 14:34
Reporter:	JF Theroux	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Proxy: Core	Severity:	S1 (Critical)
Version:	0.8 (current trunk)	OS:	Linux
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
While testing the current trunk (I believe the release version will be 0.8), I discovered mysql-proxy would stop working if we stopped our master mysql server.

How to repeat:
Running on Ubuntu Hardy with the following package versions:

mysql-proxy 0.8
libevent 1.4.12

Command line:

mysql-proxy --keepalive ----proxy-address=1.2.3.4:3306 --proxy-lua-script=/etc/mysql-proxy/rw-splitting.lua --proxy-backend-addresses=1.2.3.5:3306 --proxy-read-only-backend-addresses=1.2.3.6:3306 --proxy-read-only-backend-addresses=1.2.3.7:3306

This happens with and without the rw-splitting.lua script.

I'm available to assist in troubleshooting this further.

Thank you for the report.

Verified as described:

Term1:

$mysql-proxy --keepalive --proxy-backend-addresses=127.0.0.1:33511 --proxy-read-only-backend-addresses=127.0.0.1:33512
2009-09-11 00:41:51: (critical) proxy-plugin.c.1574: Cannot connect, all backends are down.

Term2:

$mysql -h127.0.0.1 -P4040
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 246
Server version: 5.1.40-debug-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.11 sec)

mysql> \q
Bye

$mymanager 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 195
Server version: 1.0-beta

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> stop instance mysqld511;
Query OK, 0 rows affected (1.52 sec)

mysql> \q
Bye

$mysql -h127.0.0.1 -P4040
ERROR 1105 (HY000): #07000(proxy) all backends are down

$mysql -h127.0.0.1 -P4040
ERROR 1105 (HY000): #07000(proxy) all backends are down

There is related bug #43126

Kay Roepke writes: 
Without a script, this bug report makes no sense, as the core C code does not perform automatic failover.
With a script, such as rw-splitting, when there is only one master, it going away means there is no place to send write queries to. Thus proxy reports that all backends are down (the message is a slightly misleading, but still true for the read/write backends).

This problem is not solvable in a generic fashion, it must be solved in the script/config by having a pool of write backends.
Thus, I am reassigning the issue to the scripts component, because it is not a Proxy issue per se.

This bug is not scheduled to be fixed at this time.

patch file

Attachment: proxy-plugin.patch (application/octet-stream, text), 554 bytes.

The attached patch file will fix this behavior. It will treat backends added to "proxy-read-only-backend-addresses" as.. backends.

As long as you combine it with the rw-splitting lua script, you should be good to go! Tested on RHEL6 and Centos 6.5.