<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title>Connection state</title>
</head>
<body><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="mysqlnd-ms.quickstart.usage.html">Running statements</a></div>
<div class="next" style="text-align: right; float: right;"><a href="mysqlnd-ms.quickstart.sqlhints.html">SQL Hints</a></div>
<div class="up"><a href="mysqlnd-ms.quickstart.html">Quickstart and Examples</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div><hr /><div id="mysqlnd-ms.quickstart.connectionpooling" class="section">
<h2 class="title">Connection state</h2>
<p class="para">
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.
</p>
<p class="para">
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
<a href="mysqlnd-ms.pooling.html" class="link">connection pooling and switching</a>
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.
</p>
<p class="para">
<div class="example" id="example-1760">
<p><strong>Example #1 Plugin config with one slave and one master</strong></p>
<div class="example-contents">
<div class="inicode"><pre class="inicode">{
"myapp": {
"master": {
"master_0": {
"host": "localhost",
"socket": "\/tmp\/mysql.sock"
}
},
"slave": {
"slave_0": {
"host": "192.168.2.27",
"port": "3306"
}
}
}
}</pre>
</div>
</div>
</div>
</p>
<p class="para">
<div class="example" id="example-1761">
<p><strong>Example #2 Pitfall: connection state and SQL user variables</strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br />$mysqli </span><span style="color: #007700">= new </span><span style="color: #0000BB">mysqli</span><span style="color: #007700">(</span><span style="color: #DD0000">"myapp"</span><span style="color: #007700">, </span><span style="color: #DD0000">"username"</span><span style="color: #007700">, </span><span style="color: #DD0000">"password"</span><span style="color: #007700">, </span><span style="color: #DD0000">"database"</span><span style="color: #007700">);<br />if (!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">)<br /> </span><span style="color: #FF8000">/* Of course, your error handling is nicer... */<br /> </span><span style="color: #007700">die(</span><span style="color: #0000BB">sprintf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">mysqli_connect_errno</span><span style="color: #007700">(), </span><span style="color: #0000BB">mysqli_connect_error</span><span style="color: #007700">()));<br /><br /></span><span style="color: #FF8000">/* Connection 1, connection bound SQL user variable, no SELECT thus run on master */<br /></span><span style="color: #007700">if (!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"SET @myrole='master'"</span><span style="color: #007700">)) {<br /> </span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">);<br />}<br /><br /></span><span style="color: #FF8000">/* Connection 2, run on slave because SELECT */<br /></span><span style="color: #007700">if (!(</span><span style="color: #0000BB">$res </span><span style="color: #007700">= </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"SELECT @myrole AS _role"</span><span style="color: #007700">))) {<br /> </span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"[%d] %s\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno</span><span style="color: #007700">, </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">);<br />} else {<br /> </span><span style="color: #0000BB">$row </span><span style="color: #007700">= </span><span style="color: #0000BB">$res</span><span style="color: #007700">-></span><span style="color: #0000BB">fetch_assoc</span><span style="color: #007700">();<br /> </span><span style="color: #0000BB">$res</span><span style="color: #007700">-></span><span style="color: #0000BB">close</span><span style="color: #007700">();<br /> </span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"@myrole = '%s'\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$row</span><span style="color: #007700">[</span><span style="color: #DD0000">'_role'</span><span style="color: #007700">]);<br />}<br /></span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">close</span><span style="color: #007700">();<br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
<div class="example-contents"><p>The above example will output:</p></div>
<div class="example-contents screen">
<div class="cdata"><pre>
@myrole = ''
</pre></div>
</div>
</div>
</p>
<p class="para">
The example opens a load balanced connection and executes two statements.
The first statement <em>SET @myrole='master'</em> does not begin
with the string <em>SELECT</em>. 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.
</p>
<p class="para">
The next statement is <em>SELECT @myrole AS _role</em>.
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
<em>@myrole = ''</em>.
</p>
<p class="para">
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.
</p>
<p class="para">
The pitfalls can easily be worked around using SQL hints.
</p>
</div><hr /><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="mysqlnd-ms.quickstart.usage.html">Running statements</a></div>
<div class="next" style="text-align: right; float: right;"><a href="mysqlnd-ms.quickstart.sqlhints.html">SQL Hints</a></div>
<div class="up"><a href="mysqlnd-ms.quickstart.html">Quickstart and Examples</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div></body></html>
