Description:
I seem to be having problems with pooled connections to a master server. My setup has one master, and multiple slaves, the master also functions as a 'slave', so receives read-only queries.
It seems that when the lua returns a PROXY_IGNORE_RESULT, the php client assumes the connection failed, and refuses to continue. This means that whenever the proxy attempts to use a pooled connection, php decides to give up.
This is shown by the debug output of two consecutive requests:
I think I may have found a problem in proxy_lua_connect_server
[connect_server]
[1].address = master.example.com:3306
[1].connected_clients = 0
[1].idling_connections = 3
[1].type = 1
[1].state = 1
[2].address = slave.example.com:3306
[2].connected_clients = 0
[2].idling_connections = 4
[2].type = 2
[2].state = 1
[3].address = master.example.com:3306
[3].connected_clients = 0
[3].idling_connections = 4
[3].type = 2
[3].state = 1
opening new connection on: 1
[read_query]
authed backend = 0
used db =
sending to master: 1
[read_query]
authed backend = 0
used db =
sending to master: 1
[read_query]
authed backend = 0
used db =
sending "SELECT * FROM Product;" to slave: 2
[read_query]
authed backend = 0
used db =
[disconnect_client]
[connect_server]
[1].address = master.example.com:3306
[1].connected_clients = 0
[1].idling_connections = 4
[1].type = 1
[1].state = 1
[2].address = slave.example.com:3306
[2].connected_clients = 0
[2].idling_connections = 4
[2].type = 2
[2].state = 1
[3].address = master.example.com:330
[3].connected_clients = 0
[3].idling_connections = 4
[3].type = 2
[3].state = 1
using pooled connection from: 1
[disconnect_client]
No further queries are run in the case of the second connect.
In the case of the first connection, the master receives an EXPLAIN SELECT * FROM Products (which explains the additional read queries), and the slave receives the actual select. In the case of the second connection (using pooled connection from: 1) neither query is executed.
The process lists of the servers show 8 connections on the master (4 idling for the backend connection, and 4 for the readonly-backend) and 4 on the slave. So they connections are working just fine, it just seems to be the response being sent to the client when a connection is being reused.
How to repeat:
The proxy is using the trunk tutorials-keepalive.lua
started with:
/usr/local/sbin/mysql-proxy --proxy-lua-script=/usr/local/etc/tutorials-keepalive.lua\
--proxy-backend-addresses=master.example.com:3306 \
--proxy-read-only-backend-addresses=slave.example.com:3306 \
--proxy-read-only-backend-addresses=master.example.com:3306
The test script below was called repeatedly. The connection fails every other connection.
<?php
require_once 'MDB2.php';
$db = MDB2::connect('mysql://test:test@127.0.0.1:4040/test');
if (MDB2::isError($db)) {
echo $db->getMessage();
}
$res = $db->query("SELECT * FROM Product;");
$count = 0;
while ($row = $res->fetchRow()) {
$count++;
}
echo $count;
$res->free();
?>
Suggested fix:
When using a pooled connection, send a successful auth response to the client.
Description: I seem to be having problems with pooled connections to a master server. My setup has one master, and multiple slaves, the master also functions as a 'slave', so receives read-only queries. It seems that when the lua returns a PROXY_IGNORE_RESULT, the php client assumes the connection failed, and refuses to continue. This means that whenever the proxy attempts to use a pooled connection, php decides to give up. This is shown by the debug output of two consecutive requests: I think I may have found a problem in proxy_lua_connect_server [connect_server] [1].address = master.example.com:3306 [1].connected_clients = 0 [1].idling_connections = 3 [1].type = 1 [1].state = 1 [2].address = slave.example.com:3306 [2].connected_clients = 0 [2].idling_connections = 4 [2].type = 2 [2].state = 1 [3].address = master.example.com:3306 [3].connected_clients = 0 [3].idling_connections = 4 [3].type = 2 [3].state = 1 opening new connection on: 1 [read_query] authed backend = 0 used db = sending to master: 1 [read_query] authed backend = 0 used db = sending to master: 1 [read_query] authed backend = 0 used db = sending "SELECT * FROM Product;" to slave: 2 [read_query] authed backend = 0 used db = [disconnect_client] [connect_server] [1].address = master.example.com:3306 [1].connected_clients = 0 [1].idling_connections = 4 [1].type = 1 [1].state = 1 [2].address = slave.example.com:3306 [2].connected_clients = 0 [2].idling_connections = 4 [2].type = 2 [2].state = 1 [3].address = master.example.com:330 [3].connected_clients = 0 [3].idling_connections = 4 [3].type = 2 [3].state = 1 using pooled connection from: 1 [disconnect_client] No further queries are run in the case of the second connect. In the case of the first connection, the master receives an EXPLAIN SELECT * FROM Products (which explains the additional read queries), and the slave receives the actual select. In the case of the second connection (using pooled connection from: 1) neither query is executed. The process lists of the servers show 8 connections on the master (4 idling for the backend connection, and 4 for the readonly-backend) and 4 on the slave. So they connections are working just fine, it just seems to be the response being sent to the client when a connection is being reused. How to repeat: The proxy is using the trunk tutorials-keepalive.lua started with: /usr/local/sbin/mysql-proxy --proxy-lua-script=/usr/local/etc/tutorials-keepalive.lua\ --proxy-backend-addresses=master.example.com:3306 \ --proxy-read-only-backend-addresses=slave.example.com:3306 \ --proxy-read-only-backend-addresses=master.example.com:3306 The test script below was called repeatedly. The connection fails every other connection. <?php require_once 'MDB2.php'; $db = MDB2::connect('mysql://test:test@127.0.0.1:4040/test'); if (MDB2::isError($db)) { echo $db->getMessage(); } $res = $db->query("SELECT * FROM Product;"); $count = 0; while ($row = $res->fetchRow()) { $count++; } echo $count; $res->free(); ?> Suggested fix: When using a pooled connection, send a successful auth response to the client.