Description:
I have to switch MySQLProxy from one backend to another just manually, without any automatic switching. So, lua script from "How to repeat" section checks for desired backend index in specified file and runs query or denies it with error message.
If I stop MySQL server on backend during a persistent connection and connected client tries to run query, MySQLProxy goes into unbreakable loop, continuously shouting :
network-mysqld.c.303: connect(X1.Y1.Z1.N1:3306) failed: Connection refused
network-mysqld-proxy.c.3196: connecting to backend (X1.Y1.Z1.N1:3306) failed, marking it as down for ...
How to repeat:
* Start MySQL daemon on server X1.Y1.Z1.N1 (a backend1)
* Start MySQL daemon on server X2.Y2.Z2.N2 (a backend2)
* Start MySQL proxy with the following parameters:
/mysql-proxy-0.6.0 -D --proxy-address=A.B.C.D:3306
--proxy-backend-addresses=X1.Y1.Z1.N1:3306
--proxy-backend-addresses=X2.Y2.Z2.N2:3306
--proxy-lua-script=script.lua
* Lua script is:
--------------------------------------------------------------------------------
ndx=0
function connect_server( )
local file = io.open("/root/lua/backend_ndx", "r")
ndx = file:read()
proxy.connection["backend_ndx"] = ndx
print (proxy.connection["backend_ndx"])
end
function read_query( packet )
local file = io.open("/root/lua/backend_ndx", "r")
local new_ndx = file:read()
print ("< new ndx: " .. new_ndx .. " >")
if new_ndx ~= ndx then
proxy.response = {
type = proxy.MYSQLD_PACKET_ERR,
errmsg = "Backend has changed"
}
return proxy.PROXY_SEND_RESULT
end
proxy.queries:append(1, packet )
return proxy.PROXY_SEND_QUERY
end
--------------------------------------------------------------------------------
* Connect a client to MySQLProxy
mysql -h mysqlproxy -u testuser test_db
* And run a query
mysql> select * from test_table;
* Stop MySQL server on current backend (X1.Y1.Z1.N1)
* Run query from client
mysql> select * from test_table;
ERROR 2013 (HY000): Lost connection to MySQL server during query
* Run it once more to make client to reconnect
mysql> select * from test_table;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
* Take a look at MySQLProxy, it got caught in an endless loop
Description: I have to switch MySQLProxy from one backend to another just manually, without any automatic switching. So, lua script from "How to repeat" section checks for desired backend index in specified file and runs query or denies it with error message. If I stop MySQL server on backend during a persistent connection and connected client tries to run query, MySQLProxy goes into unbreakable loop, continuously shouting : network-mysqld.c.303: connect(X1.Y1.Z1.N1:3306) failed: Connection refused network-mysqld-proxy.c.3196: connecting to backend (X1.Y1.Z1.N1:3306) failed, marking it as down for ... How to repeat: * Start MySQL daemon on server X1.Y1.Z1.N1 (a backend1) * Start MySQL daemon on server X2.Y2.Z2.N2 (a backend2) * Start MySQL proxy with the following parameters: /mysql-proxy-0.6.0 -D --proxy-address=A.B.C.D:3306 --proxy-backend-addresses=X1.Y1.Z1.N1:3306 --proxy-backend-addresses=X2.Y2.Z2.N2:3306 --proxy-lua-script=script.lua * Lua script is: -------------------------------------------------------------------------------- ndx=0 function connect_server( ) local file = io.open("/root/lua/backend_ndx", "r") ndx = file:read() proxy.connection["backend_ndx"] = ndx print (proxy.connection["backend_ndx"]) end function read_query( packet ) local file = io.open("/root/lua/backend_ndx", "r") local new_ndx = file:read() print ("< new ndx: " .. new_ndx .. " >") if new_ndx ~= ndx then proxy.response = { type = proxy.MYSQLD_PACKET_ERR, errmsg = "Backend has changed" } return proxy.PROXY_SEND_RESULT end proxy.queries:append(1, packet ) return proxy.PROXY_SEND_QUERY end -------------------------------------------------------------------------------- * Connect a client to MySQLProxy mysql -h mysqlproxy -u testuser test_db * And run a query mysql> select * from test_table; * Stop MySQL server on current backend (X1.Y1.Z1.N1) * Run query from client mysql> select * from test_table; ERROR 2013 (HY000): Lost connection to MySQL server during query * Run it once more to make client to reconnect mysql> select * from test_table; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... * Take a look at MySQLProxy, it got caught in an endless loop