Examples
Connection state
Connection state
Connection state
The plugin changes the semantics of a PHP MySQL
connection handle. A new connection handle represents a connection
pool, instead of a single MySQL client-server network connection.
The connection pool consists of a master connection, and optionally
any number of 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. For a complete list of items
that belong to the state of a connection, see the connection pooling and
switching concepts documentation. If the plugin decides to
switch connections for load balancing, the application could be
given a connection which has a different state. Applications must
be made aware of this.
Example #1 Plugin config with one slave and one
master
{
"myapp": {
"master": {
"master_0": {
"host": "localhost",
"socket": "\/tmp\/mysql.sock"
}
},
"slave": {
"slave_0": {
"host": "192.168.2.27",
"port": "3306"
}
}
}
}
Example #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();
?>The above example will output:
@myrole = ''
The example opens 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.