<!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>Sets a callback for user-defined read/write splitting</title>
</head>
<body><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="function.mysqlnd-ms-set-qos.html">mysqlnd_ms_set_qos</a></div>
<div class="next" style="text-align: right; float: right;"><a href="mysqlnd-ms.changes.html">Change History</a></div>
<div class="up"><a href="ref.mysqlnd-ms.html">Mysqlnd_ms Functions</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div><hr /><div id="function.mysqlnd-ms-set-user-pick-server" class="refentry">
<div class="refnamediv">
<h1 class="refname">mysqlnd_ms_set_user_pick_server</h1>
<p class="verinfo">(PECL mysqlnd_ms < 1.1.0)</p><p class="refpurpose"><span class="refname">mysqlnd_ms_set_user_pick_server</span> — <span class="dc-title">Sets a callback for user-defined read/write splitting</span></p>
</div>
<div class="refsect1 description" id="refsect1-function.mysqlnd-ms-set-user-pick-server-description">
<h3 class="title">Description</h3>
<div class="methodsynopsis dc-description">
<span class="type">bool</span>
<span class="methodname"><strong>mysqlnd_ms_set_user_pick_server</strong></span>
( <span class="methodparam"><span class="type">string</span> <code class="parameter">$function</code></span>
)</div>
<p class="para rdfs-comment">
Sets a callback for user-defined read/write splitting. The plugin will
call the callback only if <em>pick[]=user</em> is the default
rule for server picking in the relevant section of the plugins configuration
file.
</p>
<p class="para">
The plugins built-in read/write query split mechanism decisions can be
overwritten in two ways. The easiest way is to prepend the query string
with the SQL hints <strong><code>MYSQLND_MS_MASTER_SWITCH</code></strong>,
<strong><code>MYSQLND_MS_SLAVE_SWITCH</code></strong> or
<strong><code>MYSQLND_MS_LAST_USED_SWITCH</code></strong>. Using SQL hints one can
control, for example, whether a query shall be send to the MySQL replication
master server or one of the slave servers. By help of SQL hints it is
not possible to pick a certain slave server for query execution.
</p>
<p class="para">
Full control on server selection can be gained using a callback function.
Use of a callback is recommended to expert users only because the callback
has to cover all cases otherwise handled by the plugin.
</p>
<p class="para">
The plugin will invoke the callback function for selecting a server from the
lists of configured master and slave servers. The callback function
inspects the query to run and picks a server for query execution by returning
the hosts URI, as found in the master and slave list.
</p>
<p class="para">
If the lazy connections are enabled and the callback chooses a slave server for
which no connection has been established so far and establishing the connection
to the slave fails, the plugin will return an error upon the next action
on the failed connection, for example, when running a query. It is the
responsibility of the application developer to handle the error. For example,
the application can re-run the query to trigger a new server selection and
callback invocation. If so, the callback must make sure to select
a different slave, or check slave availability, before returning to
the plugin to prevent an endless loop.
</p>
</div>
<div class="refsect1 parameters" id="refsect1-function.mysqlnd-ms-set-user-pick-server-parameters">
<h3 class="title">Parameters</h3>
<dl>
<dt>
<span class="term"><em><code class="parameter">function</code></em></span>
<dd>
<p class="para">
The function to be called. Class methods may also be invoked
statically using this function by passing
<em>array($classname, $methodname)</em> to this parameter.
Additionally class methods of an object instance may be called by passing
<em>array($objectinstance, $methodname)</em> to this parameter.
</p>
</dd>
</dt>
</dl>
</div>
<div class="refsect1 returnvalues" id="refsect1-function.mysqlnd-ms-set-user-pick-server-returnvalues">
<h3 class="title">Return Values</h3>
<p class="para">
Host to run the query on. The host URI is to be taken from the
master and slave connection lists passed to the callback function.
If callback returns a value neither found in the master nor in the slave
connection lists the plugin will fallback to the second pick method configured
via the <em>pick[]</em> setting in the plugin configuration file.
If not second pick method is given, the plugin falls back to the build-in
default pick method for server selection.
</p>
</div>
<div class="refsect1 unknown-returnvaluet" id="refsect1-function.mysqlnd-ms-set-user-pick-server-unknown-returnvaluet">
<h3 class="title">Notes</h3>
<blockquote class="note"><p><strong class="note">Note</strong>:
<p class="para">
<span class="function"><strong>mysqlnd_ms_set_user_pick_server()</strong></span> is available
with PECL mysqlnd_ms < 1.1.0. It has been replaced by the
<em>user</em> filter. Please, check the
<a href="mysqlnd-ms.changes-one-one.html" class="link">Change History</a>
for upgrade notes.
</p>
</p></blockquote>
</div>
<div class="refsect1 examples" id="refsect1-function.mysqlnd-ms-set-user-pick-server-examples">
<h3 class="title">Examples</h3>
<p class="para">
<div class="example" id="example-1844">
<p><strong>Example #1 <span class="function"><strong>mysqlnd_ms_set_user_pick_server()</strong></span> example</strong></p>
<div class="example-contents">
<div class="inicode"><pre class="inicode">[myapp]
master[] = localhost
slave[] = 192.168.2.27:3306
slave[] = 192.168.78.136:3306
pick[] = user</pre>
</div>
</div>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br /><br /></span><span style="color: #007700">function </span><span style="color: #0000BB">pick_server</span><span style="color: #007700">(</span><span style="color: #0000BB">$connected</span><span style="color: #007700">, </span><span style="color: #0000BB">$query</span><span style="color: #007700">, </span><span style="color: #0000BB">$master</span><span style="color: #007700">, </span><span style="color: #0000BB">$slaves</span><span style="color: #007700">, </span><span style="color: #0000BB">$last_used</span><span style="color: #007700">)<br />{<br /> static </span><span style="color: #0000BB">$slave_idx </span><span style="color: #007700">= </span><span style="color: #0000BB">0</span><span style="color: #007700">;<br /> static </span><span style="color: #0000BB">$num_slaves </span><span style="color: #007700">= </span><span style="color: #0000BB">NULL</span><span style="color: #007700">;<br /> if (</span><span style="color: #0000BB">is_null</span><span style="color: #007700">(</span><span style="color: #0000BB">$num_slaves</span><span style="color: #007700">))<br /> </span><span style="color: #0000BB">$num_slaves </span><span style="color: #007700">= </span><span style="color: #0000BB">count</span><span style="color: #007700">(</span><span style="color: #0000BB">$slaves</span><span style="color: #007700">);<br /><br /> </span><span style="color: #FF8000">/* default: fallback to the plugins build-in logic */<br /> </span><span style="color: #0000BB">$ret </span><span style="color: #007700">= </span><span style="color: #0000BB">NULL</span><span style="color: #007700">;<br /><br /> </span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"User has connected to '%s'...\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$connected</span><span style="color: #007700">);<br /> </span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"... deciding where to run '%s'\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$query</span><span style="color: #007700">);<br /><br /> </span><span style="color: #0000BB">$where </span><span style="color: #007700">= </span><span style="color: #0000BB">mysqlnd_ms_query_is_select</span><span style="color: #007700">(</span><span style="color: #0000BB">$query</span><span style="color: #007700">);<br /> switch (</span><span style="color: #0000BB">$where</span><span style="color: #007700">)<br /> {<br /> case </span><span style="color: #0000BB">MYSQLND_MS_QUERY_USE_MASTER</span><span style="color: #007700">:<br /> </span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"... using master\n"</span><span style="color: #007700">);<br /> </span><span style="color: #0000BB">$ret </span><span style="color: #007700">= </span><span style="color: #0000BB">$master</span><span style="color: #007700">[</span><span style="color: #0000BB">0</span><span style="color: #007700">];<br /> break;<br /> case </span><span style="color: #0000BB">MYSQLND_MS_QUERY_USE_SLAVE</span><span style="color: #007700">:<br /> </span><span style="color: #FF8000">/* SELECT or SQL hint for using slave */<br /> </span><span style="color: #007700">if (</span><span style="color: #0000BB">stristr</span><span style="color: #007700">(</span><span style="color: #0000BB">$query</span><span style="color: #007700">, </span><span style="color: #DD0000">"FROM table_on_slave_a_only"</span><span style="color: #007700">))<br /> {<br /> </span><span style="color: #FF8000">/* a table which is only on the first configured slave */<br /> </span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"... access to table available only on slave A detected\n"</span><span style="color: #007700">);<br /> </span><span style="color: #0000BB">$ret </span><span style="color: #007700">= </span><span style="color: #0000BB">$slaves</span><span style="color: #007700">[</span><span style="color: #0000BB">0</span><span style="color: #007700">];<br /> }<br /> else<br /> {<br /> </span><span style="color: #FF8000">/* round robin */<br /> </span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"... some read-only query for a slave\n"</span><span style="color: #007700">);<br /> </span><span style="color: #0000BB">$ret </span><span style="color: #007700">= </span><span style="color: #0000BB">$slaves</span><span style="color: #007700">[</span><span style="color: #0000BB">$slave_idx</span><span style="color: #007700">++ % </span><span style="color: #0000BB">$num_slaves</span><span style="color: #007700">];<br /> }<br /> break;<br /> case </span><span style="color: #0000BB">MYSQLND_MS_QUERY_LAST_USED</span><span style="color: #007700">:<br /> </span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"... using last used server\n"</span><span style="color: #007700">);<br /> </span><span style="color: #0000BB">$ret </span><span style="color: #007700">= </span><span style="color: #0000BB">$last_used</span><span style="color: #007700">;<br /> break;<br /> }<br /><br /> </span><span style="color: #0000BB">printf</span><span style="color: #007700">(</span><span style="color: #DD0000">"... ret = '%s'\n"</span><span style="color: #007700">, </span><span style="color: #0000BB">$ret</span><span style="color: #007700">);<br /> return </span><span style="color: #0000BB">$ret</span><span style="color: #007700">;<br />}<br /><br /></span><span style="color: #0000BB">mysqlnd_ms_set_user_pick_server</span><span style="color: #007700">(</span><span style="color: #DD0000">"pick_server"</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">$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">"root"</span><span style="color: #007700">, </span><span style="color: #DD0000">"root"</span><span style="color: #007700">, </span><span style="color: #DD0000">"test"</span><span style="color: #007700">);<br /><br />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 1 FROM DUAL"</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">$res</span><span style="color: #007700">-></span><span style="color: #0000BB">close</span><span style="color: #007700">();<br /><br />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 2 FROM DUAL"</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">$res</span><span style="color: #007700">-></span><span style="color: #0000BB">close</span><span style="color: #007700">();<br /><br /><br />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 * FROM table_on_slave_a_only"</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">$res</span><span style="color: #007700">-></span><span style="color: #0000BB">close</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>
User has connected to 'myapp'...
... deciding where to run 'SELECT 1 FROM DUAL'
... some read-only query for a slave
... ret = 'tcp://192.168.2.27:3306'
User has connected to 'myapp'...
... deciding where to run 'SELECT 2 FROM DUAL'
... some read-only query for a slave
... ret = 'tcp://192.168.78.136:3306'
User has connected to 'myapp'...
... deciding where to run 'SELECT * FROM table_on_slave_a_only'
... access to table available only on slave A detected
... ret = 'tcp://192.168.2.27:3306'
</pre></div>
</div>
</div>
</p>
</div>
<div class="refsect1 seealso" id="refsect1-function.mysqlnd-ms-set-user-pick-server-seealso">
<h3 class="title">See Also</h3>
<p class="para">
<ul class="simplelist">
<li class="member">
<span class="function"><a href="function.mysqlnd-ms-query-is-select.html" class="function" rel="rdfs-seeAlso">mysqlnd_ms_query_is_select()</a> - Find whether to send the query to the master, the slave or the last used MySQL server</span>
</li>
<li class="member">
<a href="mysqlnd-ms.filter.html" class="link">Filter concept</a>
</li>
<li class="member">
<a href="mysqlnd-ms.plugin-ini-json.html#ini.mysqlnd-ms-plugin-config-v2.filter-user" class="link"><em>user</em></a> filter
</li>
</ul>
</p>
</div>
</div><hr /><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="function.mysqlnd-ms-set-qos.html">mysqlnd_ms_set_qos</a></div>
<div class="next" style="text-align: right; float: right;"><a href="mysqlnd-ms.changes.html">Change History</a></div>
<div class="up"><a href="ref.mysqlnd-ms.html">Mysqlnd_ms Functions</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div></body></html>
