21.9.6.8. Mysqlnd_ms Functions

21.9.6.8.1. `mysqlnd_ms_get_last_gtid`

mysqlnd_ms_get_last_gtid

Returns the latest global transaction ID

Description

string mysqlnd_ms_get_last_gtid(mixed connection);

Returns a global transaction identifier which belongs to a write operation no older than the last write performed by the client. It is not guaranteed that the global transaction identifier is identical to that one created for the last write transaction performed by the client.

Parameters

connection: A PECL/mysqlnd_ms connection handle to a MySQL server of the type PDO_MYSQL, mysqli> or ext/mysql. The connection handle is obtained when opening a connection with a host name that matches a mysqlnd_ms configuration file entry using any of the above three MySQL driver extensions.

Return Values

Returns a global transaction ID (GTID) on success. Otherwise, returns FALSE .

The function mysqlnd_ms_get_last_gtid returns the GTID obtained when executing the SQL statement from the fetch_last_gtid entry of the global_transaction_id_injection section from the plugins configuration file.

The function may be called after the GTID has been incremented.

Notes

Note

mysqlnd_ms_get_last_gtid requires PHP >= 5.4.0 and PECL mysqlnd_ms >= 1.2.0. Internally, it is using a mysqlnd library C functionality not available with PHP 5.3.

Examples

Example 21.300. mysqlnd_ms_get_last_gtidexample

<?php/* Open mysqlnd_ms connection using mysqli, PDO_MySQL or mysql extension */$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()));/* auto commit mode, transaction on master, GTID must be incremented */if (!$mysqli->query("DROP TABLE IF EXISTS test"))  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));printf("GTID after transaction %s\n", mysqlnd_ms_get_last_gtid($mysqli));/* auto commit mode, transaction on master, GTID must be incremented */if (!$mysqli->query("CREATE TABLE test(id INT)"))  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));printf("GTID after transaction %s\n", mysqlnd_ms_get_last_gtid($mysqli));?>

See Also

Global Transaction IDs

21.9.6.8.2. `mysqlnd_ms_get_last_used_connection`

mysqlnd_ms_get_last_used_connection

Returns an array which describes the last used connection

Description

array mysqlnd_ms_get_last_used_connection(mixed connection);

Returns an array which describes the last used connection from the plugins connection pool currently pointed to by the user connection handle. If using the plugin, a user connection handle represents a pool of database connections. It is not possible to tell from the user connection handles properties to which database server from the pool the user connection handle points.

The function can be used to debug or monitor PECL mysqlnd_ms.

Parameters

connection: A MySQL connection handle obtained from any of the connect functions of the mysqli, mysql or PDO_MYSQL extensions.

Return Values

FALSE on error. Otherwise, an array which describes the connection used to execute the last statement on.

Array which describes the connection.

Property	Description	Version
`scheme`	Connection scheme. Either `tcp://host:port` or `unix://host:socket`. If you want to distinguish connections from each other use a combination of `scheme` and `thread_id` as a unique key. Neither `scheme` nor `thread_id` alone are sufficient to distinguish two connections from each other. Two servers may assign the same `thread_id` to two different connections. Thus, connections in the pool may have the same `thread_id`. Also, do not rely on uniqueness of `scheme` in a pool. Your QA engineers may use the same MySQL server instance for two distinct logical roles and add it multiple times to the pool. This hack is used, for example, in the test suite.	Since 1.1.0.
`host`	Database server host used with the connection. The host is only set with TCP/IP connections. It is empty with Unix domain or Windows named pipe connections,	Since 1.1.0.
`host_info`	A character string representing the server hostname and the connection type.	Since 1.1.2.
`port`	Database server port used with the connection.	Since 1.1.0.
`socket_or_pipe`	Unix domain socket or Windows named pipe used with the connection. The value is empty for TCP/IP connections.	Since 1.1.2.
`thread_id`	Connection thread id.	Since 1.1.0.
`last_message`	Info message obtained from the MySQL C API function mysql_info(). Please, see `mysqli_info`for a description.	Since 1.1.0.
`errno`	Error code.	Since 1.1.0.
`error`	Error message.	Since 1.1.0.
`sqlstate`	Error SQLstate code.	Since 1.1.0.

Notes

Note

mysqlnd_ms_get_last_used_connection requires PHP >= 5.4.0 and PECL mysqlnd_ms >> 1.1.0. Internally, it is using a mysqlnd library C call not available with PHP 5.3.

Examples

The example assumes that myapp refers to a plugin configuration file section and represents a connection pool.

Example 21.301. mysqlnd_ms_get_last_used_connectionexample

<?php$link = new mysqli("myapp", "user", "password", "database");$res = $link->query("SELECT 1 FROM DUAL");var_dump(mysqlnd_ms_get_last_used_connection($link));?>

The above example will output:

array(10) {  ["scheme"]=>  string(22) "unix:///tmp/mysql.sock"  ["host_info"]=>  string(25) "Localhost via UNIX socket"  ["host"]=>  string(0) ""  ["port"]=>  int(3306)  ["socket_or_pipe"]=>  string(15) "/tmp/mysql.sock"  ["thread_id"]=>  int(46253)  ["last_message"]=>  string(0) ""  ["errno"]=>  int(0)  ["error"]=>  string(0) ""  ["sqlstate"]=>  string(5) "00000"}

21.9.6.8.3. `mysqlnd_ms_get_stats`

mysqlnd_ms_get_stats

Returns query distribution and connection statistics

Description

array mysqlnd_ms_get_stats();

Returns an array of statistics collected by the replication and load balancing plugin.

The PHP configuration setting mysqlnd_ms.collect_statistics controls the collection of statistics. The collection of statistics is disabled by default for performance reasons.

The scope of the statistics is the PHP process. Depending on your deployment model a PHP process may handle one or multiple requests.

Statistics are aggregated for all connections and all storage handler. It is not possible to tell how much queries originating from mysqli, PDO_MySQL or mysql API calls have contributed to the aggregated data values.

Parameters

This function has no parameters.

Return Values

Returns NULL if the PHP configuration directive mysqlnd_ms.enable has disabled the plugin. Otherwise, returns array of statistics.

Array of statistics

Statistic	Description	Version
`use_slave`	The semantics of this statistic has changed between 1.0.1 - 1.1.0. The meaning for version 1.0.1 is as follows. Number of statements considered as read-only by the built-in query analyzer. Neither statements which begin with a SQL hint to force use of slave nor statements directed to a slave by an user-defined callback are included. The total number of statements sent to the slaves is `use_slave` + `use_slave_sql_hint` + `use_slave_callback`. PECL/mysqlnd_ms 1.1.0 introduces a new concept of chained filters. The statistics is now set by the internal load balancing filter. With version 1.1.0 the load balancing filter is always the last in the filter chain, if used. In future versions a load balancing filter may be followed by other filters causing another change in the meaning of the statistic. If, in the future, a load balancing filter is followed by another filter it is no longer guaranteed that the statement, which increments `use_slave`, will be executed on the slaves. The meaning for version 1.1.0 is as follows. Number of statements sent to the slaves. Statements directed to a slave by the user filter (an user-defined callback) are not included. The latter are counted by `use_slave_callback`.	Since 1.0.0.
`use_master`	The semantics of this statistic has changed between 1.0.1 - 1.1.0. The meaning for version 1.0.1 is as follows. Number of statements not considered as read-only by the built-in query analyzer. Neither statements which begin with a SQL hint to force use of master nor statements directed to a master by an user-defined callback are included. The total number of statements sent to the master is `use_master` + `use_master_sql_hint` + `use_master_callback`. PECL/mysqlnd_ms 1.1.0 introduces a new concept of chained filters. The statictics is now set by the internal load balancing filter. With version 1.1.0 the load balancing filter is always the last in the filter chain, if used. In future versions a load balancing filter may be followed by other filters causing another change in the meaning of the statistic. If, in the future, a load balancing filter is followed by another filter it is no longer guaranteed that the statement, which increments `use_master`, will be executed on the slaves. The meaning for version 1.1.0 is as follows. Number of statements sent to the masters. Statements directed to a master by the user filter (an user-defined callback) are not included. The latter are counted by `use_master_callback`.	Since 1.0.0.
`use_slave_guess`	Number of statements the built-in query analyzer recommends sending to a slave because they contain no SQL hint to force use of a certain server. The recommendation may be overruled in the following. It is not guaranteed whether the statement will be executed on a slave or not. This is how often the internal `is_select` function has guessed that a slave shall be used. Please, see also the user space function `mysqlnd_ms_query_is_select`.	Since 1.1.0.
`use_master_guess`	Number of statements the built-in query analyzer recommends sending to a master because they contain no SQL hint to force use of a certain server. The recommendation may be overruled in the following. It is not guaranteed whether the statement will be executed on a slave or not. This is how often the internal `is_select` function has guessed that a master shall be used. Please, see also the user space function `mysqlnd_ms_query_is_select`.	Since 1.1.0.
`use_slave_sql_hint`	Number of statements sent to a slave because statement begins with the SQL hint to force use of slave.	Since 1.0.0.
`use_master_sql_hint`	Number of statements sent to a master because statement begins with the SQL hint to force use of master.	Since 1.0.0.
`use_last_used_sql_hint`	Number of statements sent to server which has run the previous statement, because statement begins with the SQL hint to force use of previously used server.	Since 1.0.0.
`use_slave_callback`	Number of statements sent to a slave because an user-defined callback has chosen a slave server for statement execution.	Since 1.0.0.
`use_master_callback`	Number of statements sent to a master because an user-defined callback has chosen a master server for statement execution.	Since 1.0.0.
`non_lazy_connections_slave_success`	Number of successfully opened slave connections from configurations not using `lazy connections`. The total number of successfully opened slave connections is `non_lazy_connections_slave_success` + `lazy_connections_slave_success`	Since 1.0.0.
`non_lazy_connections_slave_failure`	Number of failed slave connection attempts from configurations not using `lazy connections`. The total number of failed slave connection attempts is `non_lazy_connections_slave_failure` + `lazy_connections_slave_failure`	Since 1.0.0.
`non_lazy_connections_master_success`	Number of successfully opened master connections from configurations not using `lazy connections`. The total number of successfully opened master connections is `non_lazy_connections_master_success` + `lazy_connections_master_success`	Since 1.0.0.
`non_lazy_connections_master_failure`	Number of failed master connection attempts from configurations not using `lazy connections`. The total number of failed master connection attempts is `non_lazy_connections_master_failure` + `lazy_connections_master_failure`	Since 1.0.0.
`lazy_connections_slave_success`	Number of successfully opened slave connections from configurations using `lazy connections`.	Since 1.0.0.
`lazy_connections_slave_failure`	Number of failed slave connection attempts from configurations using `lazy connections`.	Since 1.0.0.
`lazy_connections_master_success`	Number of successfully opened master connections from configurations using `lazy connections`.	Since 1.0.0.
`lazy_connections_master_failure`	Number of failed master connection attempts from configurations using `lazy connections`.	Since 1.0.0.
`trx_autocommit_on`	Number of `autocommit` mode activations via API calls. This figure may be used to monitor activity related to the plugin configuration setting `trx_stickiness`. If, for example, you want to know if a certain API call invokes the `mysqlnd` library function `trx_autocommit()`, which is a requirement for `trx_stickiness`, you may call the user API function in question and check if the statistic has changed. The statistic is modified only by the plugins internal subclassed `trx_autocommit()` method.	Since 1.0.0.
`trx_autocommit_off`	Number of `autocommit` mode deactivations via API calls.	Since 1.0.0.
`trx_master_forced`	Number of statements redirected to the master while `trx_stickiness=master` and `autocommit` mode is disabled.	Since 1.0.0.
`gtid_autocommit_injections_success`	Number of successful SQL injections in autocommit mode as part of the plugins client-side global transaction id emulation.	Since 1.2.0.
`gtid_autocommit_injections_failure`	Number of failed SQL injections in autocommit mode as part of the plugins client-side global transaction id emulation.	Since 1.2.0.
`gtid_commit_injections_success`	Number of successful SQL injections in commit mode as part of the plugins client-side global transaction id emulation.	Since 1.2.0.
`gtid_commit_injections_failure`	Number of failed SQL injections in commit mode as part of the plugins client-side global transaction id emulation.	Since 1.2.0.
`gtid_implicit_commit_injections_success`	Number of successful SQL injections when implicit commit is detected as part of the plugins client-side global transaction id emulation. Implicit commit happens, for example, when autocommit has been turned off, a query is executed and autocommit is enabled again. In that case, the statement will be committed by the server and SQL to maintain is injected before the autocommit is re-enabled. Another sequence causing an an implicit commit is `begin()`, `query()`, `begin()`. The second call to `begin()` will implicitly commit the transaction started by the first call to `begin()`. `begin()` refers to internal library calls not actual PHP user API calls.	Since 1.2.0.
`gtid_implicit_commit_injections_failure`	Number of failed SQL injections when implicit commit is detected as part of the plugins client-side global transaction id emulation. Implicit commit happens, for example, when autocommit has been turned off, a query is executed and autocommit is enabled again. In that case, the statement will be committed by the server and SQL to maintain is injected before the autocommit is re-enabled.	Since 1.2.0.
`transient_error_retries`	How often an operation has been retried when a transient error was detected. See also, `transient_error`plugin configuration file setting.	Since 1.6.0.

Examples

Example 21.302. mysqlnd_ms_get_statsexample

<?phpprintf("mysqlnd_ms.enable = %d\n", ini_get("mysqlnd_ms.enable"));printf("mysqlnd_ms.collect_statistics = %d\n", ini_get("mysqlnd_ms.collect_statistics"));var_dump(mysqlnd_ms_get_stats());?>

The above example will output:

mysqlnd_ms.enable = 1mysqlnd_ms.collect_statistics = 1array(26) {  ["use_slave"]=>  string(1) "0"  ["use_master"]=>  string(1) "0"  ["use_slave_guess"]=>  string(1) "0"  ["use_master_guess"]=>  string(1) "0"  ["use_slave_sql_hint"]=>  string(1) "0"  ["use_master_sql_hint"]=>  string(1) "0"  ["use_last_used_sql_hint"]=>  string(1) "0"  ["use_slave_callback"]=>  string(1) "0"  ["use_master_callback"]=>  string(1) "0"  ["non_lazy_connections_slave_success"]=>  string(1) "0"  ["non_lazy_connections_slave_failure"]=>  string(1) "0"  ["non_lazy_connections_master_success"]=>  string(1) "0"  ["non_lazy_connections_master_failure"]=>  string(1) "0"  ["lazy_connections_slave_success"]=>  string(1) "0"  ["lazy_connections_slave_failure"]=>  string(1) "0"  ["lazy_connections_master_success"]=>  string(1) "0"  ["lazy_connections_master_failure"]=>  string(1) "0"  ["trx_autocommit_on"]=>  string(1) "0"  ["trx_autocommit_off"]=>  string(1) "0"  ["trx_master_forced"]=>  string(1) "0"  ["gtid_autocommit_injections_success"]=>  string(1) "0"  ["gtid_autocommit_injections_failure"]=>  string(1) "0"  ["gtid_commit_injections_success"]=>  string(1) "0"  ["gtid_commit_injections_failure"]=>  string(1) "0"  ["gtid_implicit_commit_injections_success"]=>  string(1) "0"  ["gtid_implicit_commit_injections_failure"]=>  string(1) "0"  ["transient_error_retries"]=>  string(1) "0"}

See Also

Runtime configuration

mysqlnd_ms.collect_statistics

mysqlnd_ms.enable

Monitoring

21.9.6.8.4. `mysqlnd_ms_match_wild`

mysqlnd_ms_match_wild

Finds whether a table name matches a wildcard pattern or not

Description

bool mysqlnd_ms_match_wild(string table_name,
 string wildcard);

Finds whether a table name matches a wildcard pattern or not.

This function is not of much practical relevance with PECL mysqlnd_ms 1.1.0 because the plugin does not support MySQL replication table filtering yet.

Parameters

table_name

The table name to check if it is matched by the wildcard.

wildcard

The wildcard pattern to check against the table name. The wildcard pattern supports the same placeholders as MySQL replication filters do.

MySQL replication filters can be configured by using the MySQL Server configuration options --replicate-wild-do-table and --replicate-wild-do-db. Please, consult the MySQL Reference Manual to learn more about this MySQL Server feature.

The supported placeholders are:

% - zero or more literals
% - one literal

Placeholders can be escaped using \.

Return Values

Returns TRUE table_name is matched by wildcard. Otherwise, returns FALSE

Examples

Example 21.303. mysqlnd_ms_match_wildexample

<?phpvar_dump(mysqlnd_ms_match_wild("schema_name.table_name", "schema%"));var_dump(mysqlnd_ms_match_wild("abc", "_"));var_dump(mysqlnd_ms_match_wild("table1", "table_"));var_dump(mysqlnd_ms_match_wild("asia_customers", "%customers"));var_dump(mysqlnd_ms_match_wild("funny%table","funny\%table"));var_dump(mysqlnd_ms_match_wild("funnytable", "funny%table"));?>

The above example will output:

bool(true)bool(false)bool(true)bool(true)bool(true)bool(true)

21.9.6.8.5. `mysqlnd_ms_query_is_select`

mysqlnd_ms_query_is_select

Find whether to send the query to the master, the slave or the last used MySQL server

Description

int mysqlnd_ms_query_is_select(string query);

Finds whether to send the query to the master, the slave or the last used MySQL server.

The plugins built-in read/write split mechanism will be used to analyze the query string to make a recommendation where to send the query. The built-in read/write split mechanism is very basic and simple. The plugin will recommend sending all queries to the MySQL replication master server but those which begin with SELECT, or begin with a SQL hint which enforces sending the query to a slave server. Due to the basic but fast algorithm the plugin may propose to run some read-only statements such as SHOW TABLES on the replication master.

Parameters

query: Query string to test.

Return Values

A return value of MYSQLND_MS_QUERY_USE_MASTER indicates that the query should be send to the MySQL replication master server. The function returns a value of MYSQLND_MS_QUERY_USE_SLAVE if the query can be run on a slave because it is considered read-only. A value of MYSQLND_MS_QUERY_USE_LAST_USED is returned to recommend running the query on the last used server. This can either be a MySQL replication master server or a MySQL replication slave server.

If read write splitting has been disabled by setting mysqlnd_ms.disable_rw_split, the function will always return MYSQLND_MS_QUERY_USE_MASTER or MYSQLND_MS_QUERY_USE_LAST_USED .

Examples

Example 21.304. mysqlnd_ms_query_is_selectexample

<?phpfunction is_select($query){ switch (mysqlnd_ms_query_is_select($query)) {  case MYSQLND_MS_QUERY_USE_MASTER:   printf("'%s' should be run on the master.\n", $query);   break;  case MYSQLND_MS_QUERY_USE_SLAVE:   printf("'%s' should be run on a slave.\n", $query);   break;  case MYSQLND_MS_QUERY_USE_LAST_USED:   printf("'%s' should be run on the server that has run the previous query\n", $query);   break;  default:   printf("No suggestion where to run the '%s', fallback to master recommended\n", $query);   break; }}is_select("INSERT INTO test(id) VALUES (1)");is_select("SELECT 1 FROM DUAL");is_select("/*" . MYSQLND_MS_LAST_USED_SWITCH . "*/SELECT 2 FROM DUAL");?>

The above example will output:

INSERT INTO test(id) VALUES (1) should be run on the master.SELECT 1 FROM DUAL should be run on a slave./*ms=last_used*/SELECT 2 FROM DUAL should be run on the server that has run the previous query

See Also

Predefined Constants

user filter

21.9.6.8.6. `mysqlnd_ms_set_qos`

mysqlnd_ms_set_qos

Sets the quality of service needed from the cluster

Description

bool mysqlnd_ms_set_qos(mixed connection,
 int service_level,
 int service_level_option,

        mixed option_value);

Sets the quality of service needed from the cluster. A database cluster delivers a certain quality of service to the user depending on its architecture. A major aspect of the quality of service is the consistency level the cluster can offer. An asynchronous MySQL replication cluster defaults to eventual consistency for slave reads: a slave may serve stale data, current data, or it may have not the requested data at all, because it is not synchronous to the master. In a MySQL replication cluster, only master accesses can give strong consistency, which promises that all clients see each others changes.

PECL/mysqlnd_ms hides the complexity of choosing appropriate nodes to achieve a certain level of service from the cluster. The "Quality of Service" filter implements the necessary logic. The filter can either be configured in the plugins configuration file, or at runtime using mysqlnd_ms_set_qos.

Similar results can be achieved with PECL mysqlnd_ms < 1.2.0, if using SQL hints to force the use of a certain type of node or using the master_on_write plugin configuration option. The first requires more code and causes more work on the application side. The latter is less refined than using the quality of service filter. Settings made through the function call can be reversed, as shown in the example below. The example temporarily switches to a higher service level (session consistency, read your writes) and returns back to the clusters default after it has performed all operations that require the better service. This way, read load on the master can be minimized compared to using master_on_write, which would continue using the master after the first write.

Since 1.5.0 calls will fail when done in the middle of a transaction if transaction stickiness is enabled and transaction boundaries have been detected. properly.

Parameters

connection

A PECL/mysqlnd_ms connection handle to a MySQL server of the type PDO_MYSQL, mysqli or ext/mysql for which a service level is to be set. The connection handle is obtained when opening a connection with a host name that matches a mysqlnd_ms configuration file entry using any of the above three MySQL driver extensions.

service_level

The requested service level: MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL , MYSQLND_MS_QOS_CONSISTENCY_SESSION or MYSQLND_MS_QOS_CONSISTENCY_STRONG .

service_level_option

An option to parameterize the requested service level. The option can either be MYSQLND_MS_QOS_OPTION_GTID or MYSQLND_MS_QOS_OPTION_AGE .

The option MYSQLND_MS_QOS_OPTION_GTID can be used to refine the service level MYSQLND_MS_QOS_CONSISTENCY_SESSION . It must be combined with a fourth function parameter, the option_value. The option_value shall be a global transaction ID obtained from mysqlnd_ms_get_last_gtid. If set, the plugin considers both master servers and asynchronous slaves for session consistency (read your writes). Otherwise, only masters are used to achieve session consistency. A slave is considered up-to-date and checked if it has already replicated the global transaction ID from option_value. Please note, searching appropriate slaves is an expensive and slow operation. Use the feature sparsely, if the master cannot handle the read load alone.

The MYSQLND_MS_QOS_OPTION_AGE option can be combined with the MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL service level, to filter out asynchronous slaves that lag more seconds behind the master than option_value. If set, the plugin will only consider slaves for reading if SHOW SLAVE STATUS reports Slave_IO_Running=Yes, Slave_SQL_Running=Yes and Seconds_Behind_Master <= option_value. Please note, searching appropriate slaves is an expensive and slow operation. Use the feature sparsely in version 1.2.0. Future versions may improve the algorithm used to identify candidates. Please, see the MySQL reference manual about the precision, accuracy and limitations of the MySQL administrative command SHOW SLAVE STATUS.

option_value

Parameter value for the service level option. See also the service_level_option parameter.

Return Values

Returns TRUE if the connections service level has been switched to the requested. Otherwise, returns FALSE

Notes

Note

mysqlnd_ms_set_qos requires PHP >= 5.4.0 and PECL mysqlnd_ms >= 1.2.0. Internally, it is using a mysqlnd library C functionality not available with PHP 5.3.

Examples

Example 21.305. mysqlnd_ms_set_qosexample

<?php/* Open mysqlnd_ms connection using mysqli, PDO_MySQL or mysql extension */$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()));/* Session consistency: read your writes */$ret = mysqlnd_ms_set_qos($mysqli, MYSQLND_MS_QOS_CONSISTENCY_SESSION);if (!$ret)  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));/* Will use master and return fresh data, client can see his last write */if (!$res = $mysqli->query("SELECT item, price FROM orders WHERE order_id = 1"))  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));/* Back to default: use of all slaves and masters permitted, stale data can happen */if (!mysqlnd_ms_set_qos($mysqli, MYSQLND_MS_QOS_CONSISTENCY_EVENTUAL))  die(sprintf("[%d] %s\n", $mysqli->errno, $mysqli->error));?>

See Also

mysqlnd_ms_get_last_gtid

Service level and consistency concept

Filter concept

21.9.6.8.7. `mysqlnd_ms_set_user_pick_server`

mysqlnd_ms_set_user_pick_server

Sets a callback for user-defined read/write splitting

Description

bool mysqlnd_ms_set_user_pick_server(string function);

Sets a callback for user-defined read/write splitting. The plugin will call the callback only if pick[]=user is the default rule for server picking in the relevant section of the plugins configuration file.

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 MYSQLND_MS_MASTER_SWITCH , MYSQLND_MS_SLAVE_SWITCH or MYSQLND_MS_LAST_USED_SWITCH . 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.

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.

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.

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.

Parameters

function: The function to be called. Class methods may also be invoked statically using this function by passing array($classname, $methodname) to this parameter. Additionally class methods of an object instance may be called by passing array($objectinstance, $methodname) to this parameter.

Return Values

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 pick[] 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.

Notes

Note

mysqlnd_ms_set_user_pick_server is available with PECL mysqlnd_ms < 1.1.0. It has been replaced by the user filter. Please, check the Change History for upgrade notes.

Examples

Example 21.306. mysqlnd_ms_set_user_pick_serverexample

[myapp]master[] = localhostslave[] = 192.168.2.27:3306slave[] = 192.168.78.136:3306pick[] = user

<?phpfunction pick_server($connected, $query, $master, $slaves, $last_used){ static $slave_idx = 0; static $num_slaves = NULL; if (is_null($num_slaves))  $num_slaves = count($slaves); /* default: fallback to the plugins build-in logic */ $ret = NULL; printf("User has connected to '%s'...\n", $connected); printf("... deciding where to run '%s'\n", $query); $where = mysqlnd_ms_query_is_select($query); switch ($where) {  case MYSQLND_MS_QUERY_USE_MASTER:   printf("... using master\n");   $ret = $master[0];   break;  case MYSQLND_MS_QUERY_USE_SLAVE:   /* SELECT or SQL hint for using slave */   if (stristr($query, "FROM table_on_slave_a_only"))   {    /* a table which is only on the first configured slave  */    printf("... access to table available only on slave A detected\n");    $ret = $slaves[0];   }   else   {    /* round robin */    printf("... some read-only query for a slave\n");    $ret = $slaves[$slave_idx++ % $num_slaves];   }   break;  case MYSQLND_MS_QUERY_LAST_USED:   printf("... using last used server\n");   $ret = $last_used;   break; } printf("... ret = '%s'\n", $ret); return $ret;}mysqlnd_ms_set_user_pick_server("pick_server");$mysqli = new mysqli("myapp", "root", "root", "test");if (!($res = $mysqli->query("SELECT 1 FROM DUAL"))) printf("[%d] %s\n", $mysqli->errno, $mysqli->error);else $res->close();if (!($res = $mysqli->query("SELECT 2 FROM DUAL"))) printf("[%d] %s\n", $mysqli->errno, $mysqli->error);else $res->close();if (!($res = $mysqli->query("SELECT * FROM table_on_slave_a_only"))) printf("[%d] %s\n", $mysqli->errno, $mysqli->error);else $res->close();$mysqli->close();?>

The above example will output:

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'

See Also

mysqlnd_ms_query_is_select

Filter concept

user filter

21.9.6.8. Mysqlnd_ms Functions

21.9.6.8.1. mysqlnd_ms_get_last_gtid

21.9.6.8.2. mysqlnd_ms_get_last_used_connection

21.9.6.8.3. mysqlnd_ms_get_stats

21.9.6.8.4. mysqlnd_ms_match_wild

21.9.6.8.5. mysqlnd_ms_query_is_select

21.9.6.8.6. mysqlnd_ms_set_qos

21.9.6.8.7. mysqlnd_ms_set_user_pick_server

21.9.6.8.1. `mysqlnd_ms_get_last_gtid`

21.9.6.8.2. `mysqlnd_ms_get_last_used_connection`

21.9.6.8.3. `mysqlnd_ms_get_stats`

21.9.6.8.4. `mysqlnd_ms_match_wild`

21.9.6.8.5. `mysqlnd_ms_query_is_select`

21.9.6.8.6. `mysqlnd_ms_set_qos`

21.9.6.8.7. `mysqlnd_ms_set_user_pick_server`