The plugin changes the semantics of a PHP MySQL connection handle. A connection handle does no longer represent a single MySQL client-server network connection but a connection pool. The connection pool consists of a master connection and none, one or multiple slave connections.
Every connection from the connection pool has its own state. For example, SQL user variables, temporary tables and transactions are part of the state. Please, find a complete list of what belongs to the state of a connection at the concepts page on connection pooling and switching. If the plugin decides to switch connections for load balancing the application could be given connection which has a different state. Applications must be made aware of this!
例1 Plugin config with one slave and one master
[myapp] master[]=localhost:/tmp/mysql.sock slave[]=192.168.2.27:3306
例2 Pitfall: connection state and SQL user variables
<?php
$mysqli = new mysqli("myapp", "username", "password", "database");
if (!$mysqli)
/* Of course, your error handling is nicer... */
die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error()));
/* Connection 1, connection bound SQL user variable, no SELECT thus run on master */
if (!$mysqli->query("SET @myrole='master'")) {
printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
}
/* Connection 2, run on slave because SELECT */
if (!($res = $mysqli->query("SELECT @myrole AS _role"))) {
printf("[%d] %s\n", $mysqli->errno, $mysqli->error);
} else {
$row = $res->fetch_assoc();
$res->close();
printf("@myrole = '%s'\n", $row['_role']);
}
$mysqli->close();
?>
上の例の出力は以下となります。
@myrole = ''
The example openes a load balanced connection and executes two statements. The first statement SET @myrole='master' does not begin with the string SELECT. Therefore the plugin does not recognize it as a read-only query which shall be run on a slave. The plugin runs the statement on the connection to the master. The statement sets a SQL user variable which is bound to the master connection. The state of the master connection has been changed.
The next statement is SELECT @myrole AS _role. The plugin does recognize it as a read-only query and sends it to the slave. The statement is run on a connection to the slave. This second connection does not have any SQL user variables bound to it. It has a different state than the first connection to the master. The requested SQL user variable is not set. The example script prints @myrole = ''.
It is the responsibility of the application developer to take care of the connection state. The plugin does not monitor all connection state changing activities. Monitoring all possible cases would be a very CPU intensive task, if it could be done at all.
The pitfalls can easily be worked around using SQL hints.