Enables or disables the plugin. If disabled, the extension will not plug into mysqlnd to proxy internal mysqlnd C API calls.

If enabled, the plugin checks if the host (server) parameters value of any MySQL connection attempt, matches a section name from the plugin configuration file. If not, the connection attempt is blocked.

This setting is not only useful to restrict PHP to certain servers but also to debug configuration file problems. The configuration file validity is checked at two different stages. The first check is performed when PHP begins to handle a web request. At this point the plugin reads and decodes the configuration file. Errors thrown at this early stage in an extensions life cycle may not be shown properly to the user. Thus, the plugin buffers the errors, if any, and additionally displays them when establishing a connection to MySQL. By default a buffered startup error will emit an error of type E_WARNING. If force_config_usage is set, the error type used is E_RECOVERABLE_ERROR.

Please, see also configuration file debugging notes.

Plugin specific configuration file. This setting has been renamed to mysqlnd_ms.config_file in version 1.4.0.

Plugin specific configuration file. This setting superseeds mysqlnd_ms.ini_file since 1.4.0.

Enables or disables the collection of statistics. The collection of statistics is disabled by default for performance reasons. Statistics are returned by the function mysqlnd_ms_get_stats.

Enables or disables support of MySQL multi master replication setups. Please, see also supported clusters.

Enables or disables built-in read write splitting.

Controls whether load balancing and lazy connection functionality can be used independently of read write splitting. If read write splitting is disabled, only servers from the master list will be used for statement execution. All configured slave servers will be ignored.

The SQL hint MYSQLND_MS_USE_SLAVE will not be recognized. If found, the statement will be redirected to a master.

Disabling read write splitting impacts the return value of mysqlnd_ms_query_is_select. The function will no longer propose query execution on slave servers.

List of MySQL replication master servers. The list of either of the JSON type array to declare an anonymous list of servers or of the JSON type object. Please, see above for examples.

Setting at least one master server is mandatory. The plugin will issue an error of type E_ERROR if the user has failed to provide a master server list for a configuration section. The fatal error may read (mysqlnd_ms) Section [master] doesn't exist for host [name_of_a_config_section] in %s on line %d.

A server is described with the host, port, socket, db, user, password and connect_flags. It is mandatory to provide at a value for host. If any of the other values is not given, it will be taken from the user API connect call, please, see also: using section names example.

Table of server configuration keywords.

The plugin supports using only one master server. An experimental setting exists to enable multi-master support. The details are not documented. The setting is meant for development only.

List of one or more MySQL replication slave servers. The syntax is identical to setting master servers, please, see master above for details.

The plugin supports using one or more slave servers.

Setting a list of slave servers is mandatory. The plugin will report an error of the type E_ERROR if slave is not given for a configuration section. The fatal error message may read (mysqlnd_ms) Section [slave] doesn't exist for host [%s] in %s on line %d. Note, that it is valid to use an empty slave server list. The error has been introduced to prevent accidently setting no slaves by forgetting about the slave setting. A master-only setup is still possible using an empty slave server list.

If an empty slave list is configured and an attempt is made to execute a statement on a slave the plugin may emit a warning like mysqlnd_ms) Couldn't find the appropriate slave connection. 0 slaves to choose from. upon statement execution. It is possible that another warning follows such as (mysqlnd_ms) No connection selected by the last filter.

Global transaction identifier configuration related to both the use of the server built-in global transaction ID feature and the client-side emulation.

List of filters. A filter is responsible to filter the list of available servers for executing a given statement. Filters can be chained. The random and roundrobin filter replace the pick[] directive used in prior version to select a load balancing policy. The user filter replaces the mysqlnd_ms_set_user_pick_server function.

Filters may accept parameters to refine their actions.

If no load balancing policy is set, the plugin will default to random_once. The random_once policy picks a random slave server when running the first read-only statement. The slave server will be used for all read-only statements until the PHP script execution ends. No load balancing policy is set and thus, defaulting takes place, if neither the random nor the roundrobin are part of a configuration section.

If a filter chain is configured so that a filter which output no more than once server is used as input for a filter which should be given more than one server as input, the plugin may emit a warning upon opening a connection. The warning may read: (mysqlnd_ms) Error while creating filter '%s' . Non-multi filter '%s' already created. Stopping in %s on line %d. Furthermore, an error of the error code 2000, the sql state HY000 and an error message similar to the warning may be set on the connection handle.

       {
    "myapp": {
        "master": {
            "master_0": {
                "host": "localhost"
            }
        },
        "slave": {
            "slave_0": {
                "host": "192.168.78.136",
                "port": "3306"
            }
        },
        "filters": [
            "roundrobin",
            "random"
        ]
    }
}

         

<?php
$link = new mysqli("myapp", "root", "", "test");
printf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error());
$link->query("SELECT 1 FROM DUAL");
?>

         

PHP Warning:  mysqli::mysqli(): (HY000/2000): (mysqlnd_ms) Error while creating filter 'random' . Non-multi filter 'roundrobin' already created. Stopping in filter_warning.php on line 1
[2000] (mysqlnd_ms) Error while creating filter 'random' . Non-multi filter 'roundrobin' already created. Stopping
PHP Warning:  mysqli::query(): Couldn't fetch mysqli in filter_warning.php on line 3

The random filter features the random and random once load balancing policies, set through the pick[] directive in older versions.

The random policy will pick a random server whenever a read-only statement is to be executed. The random once strategy picks a random slave server once and continues using the slave for the rest of the PHP web request. Random once is a default, if load balancing is not configured through a filter.

If the random filter is not given any arguments, it stands for random load balancing policy.

{
    "myapp": {
        "master": {
            "master_0": {
                "host": "localhost"
            }
        },
        "slave": {
            "slave_0": {
                "host": "192.168.78.136",
                "port": "3306"
            },
            "slave_1": {
                "host": "192.168.78.137",
                "port": "3306"
            }
        },
        "filters": [
            "random"
        ]
    }
}

Optionally, the sticky argument can be passed to the filter. If the parameter sticky is set to the string 1, the filter follows the random once load balancing strategy.

{
    "filters": {
        "random": {
            "sticky": "1"
        }
    }
}

Both the random and roundrobin filters support setting a priority, a weight for a server, since PECL/mysqlnd_ms 1.4.0. If the weight argument is passed to the filter, it must assign a weight for all servers. Servers must be given an alias name in the slave respectively master server lists. The alias must be used to reference servers for assigning a priority with weight.

[E_RECOVERABLE_ERROR] mysqli_real_connect(): (mysqlnd_ms) Unknown server 'slave3' in 'random' filter configuration. Stopping in %s on line %d

Using a wrong alias name with weight may result in an error similar to the shown above.

If weight is omitted, the default weight of all servers is one.

{
   "myapp": {
       "master": {
           "master1":{
               "host":"localhost",
               "socket":"\/var\/run\/mysql\/mysql.sock"
           }
       },
       "slave": {
           "slave1": {
               "host":"192.168.2.28",
               "port":3306
           },
           "slave2": {
               "host":"192.168.2.29",
               "port":3306
           },
           "slave3": {
               "host":"192.0.43.10",
               "port":3306
           },
       },
       "filters": {
           "random": {
               "weights": {
                   "slave1":8,
                   "slave2":4,
                   "slave3":1,
                   "master1":1
               }
           }
       }
   }
}

At the average a server assigned a weight of two will be selected twice as often as a server assigned a weight of one. Different weights can be assigned to reflect differently sized machines, to prefer co-located slaves which have a low network latency or, to configure a standby failover server. In the latter case, you may want to assign the standby server a very low weight in relation to the other servers. For example, given the configuration above slave3 will get only some eight percent of the requests in the average. As long as slave1 and slave2 are running, it will be used sparsely, similar to a standby failover server. Upon failure of slave1 and slave2, the usage of slave3 increases. Please, check the notes on failover before using weight this way.

Valid weight values range from 1 to 65535.

Unknown arguments are ignored by the filter. No warning or error is given.

The filter expects one or more servers as input. Outputs one server. A filter sequence such as random, roundrobin may cause a warning and an error message to be set on the connection handle when executing a statement.

List of filter arguments.

If using the roundrobin filter, the plugin iterates over the list of configured slave servers to pick a server for statement execution. If the plugin reaches the end of the list, it wraps around to the beginning of the list and picks the first configured slave server.

       {
    "myapp": {
        "master": {
            "master_0": {
                "host": "localhost"
            }
        },
        "slave": {
            "slave_0": {
                "host": "192.168.78.136",
                "port": "3306"
            }
        },
        "filters": [
            "roundrobin"
        ]
    }
}

Expects one or more servers as input. Outputs one server. A filter sequence such as roundrobin, random may cause a warning and an error message to be set on the connection handle when executing a statement.

List of filter arguments.

The user replaces mysqlnd_ms_set_user_pick_server function, which was removed in 1.1.0-beta. The filter sets a callback for user-defined read/write splitting and server selection.

The plugins built-in read/write query split mechanism decisions can be overwritten in two ways. The easiest way is to prepend a 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.

{
    "myapp": {
        "master": {
            "master_0": {
                "host": "localhost"
            }
        },
        "slave": {
            "slave_0": {
                "host": "192.168.78.136",
                "port": "3306"
            }
        },
        "filters": {
            "user": {
                "callback": "pick_server"
            }
        }
    }
}

The callback is supposed to return a 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 emit an error of the type E_RECOVERABLE_ERROR The error may read like (mysqlnd_ms) User filter callback has returned an unknown server. The server 'server that is not in master or slave list' can neither be found in the master list nor in the slave list. If the application catches the error to ignore it, follow up errors may be set on the connection handle, for example, (mysqlnd_ms) No connection selected by the last filter with the error code 2000 and the sqlstate HY000. Furthermore a warning may be emitted.

Referencing a non-existing function as a callback will result in any error of the type E_RECOVERABLE_ERROR whenever the plugin tries to callback function. The error message may reads like: (mysqlnd_ms) Specified callback (pick_server) is not a valid callback. If the application catches the error to ignore it, follow up errors may be set on the connection handle, for example, (mysqlnd_ms) Specified callback (pick_server) is not a valid callback with the error code 2000 and the sqlstate HY000. Furthermore a warning may be emitted.

The following parameters are passed from the plugin to the callback.

{
    "myapp": {
        "master": {
            "master_0": {
                "host": "localhost"
            }
        },
        "slave": {
            "slave_0": {
                "host": "192.168.2.27",
                "port": "3306"
            },
            "slave_1": {
                "host": "192.168.78.136",
                "port": "3306"
            }
        },
        "filters": {
            "user": {
                "callback": "pick_server"
            }
        }
    }
}

         

<?php
function pick_server($connected, $query, $masters, $slaves, $last_used_connection, $in_transaction)
{
 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 = $masters[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_connection;
   break;
 }

 printf("... ret = '%s'\n", $ret);
 return $ret;
}

$mysqli = new mysqli("myapp", "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();
?>

         

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'

The user_multi differs from the user only in one aspect. Otherwise, their syntax is identical. The user filter must pick and return exactly one node for statement execution. A filter chain usually ends with a filter that emits only one node. The filter chain shall reduce the list of candidates for statement execution down to one. This, only one node left, is the case after the user filter has been run.

The user_multi filter is a multi filter. It returns a list of slave and a list of master servers. This list needs further filtering to identify exactly one node for statement execution. A multi filter is typically placed at the top of the filter chain. The quality_of_service filter is another example of a multi filter.

The return value of the callback set for user_multi must be an an array with two elements. The first element holds a list of selected master servers. The second element contains a list of selected slave servers. The lists shall contain the keys of the slave and master servers as found in the slave and master lists passed to the callback. The below example returns random master and slave lists extracted from the functions input.

<?php
function pick_server($connected, $query, $masters, $slaves, $last_used_connection, $in_transaction)
{
  $picked_masters = array()
  foreach ($masters as $key => $value) {
    if (mt_rand(0, 2) > 1)
      $picked_masters[] = $key;
  }
  $picked_slaves = array()
  foreach ($slaves as $key => $value) {
    if (mt_rand(0, 2) > 1)
      $picked_slaves[] = $key;
  }
  return array($picked_masters, $picked_slaves);
}
?>

The plugin will issue an error of type E_RECOVERABLE if the callback fails to return a server list. The error may read (mysqlnd_ms) User multi filter callback has not returned a list of servers to use. The callback must return an array in %s on line %d. In case the server list is not empty but has invalid servers key/ids in it, an error of type E_RECOVERABLE will the thrown with an error message like (mysqlnd_ms) User multi filter callback has returned an invalid list of servers to use. Server id is negative in %s on line %d, or similar.

Whether an error is emitted in case of an empty slave or master list depends on the configuration. If an empty master list is returned for a write operation, it is likely that the plugin will emit a warning that may read (mysqlnd_ms) Couldn't find the appropriate master connection. 0 masters to choose from. Something is wrong in %s on line %d. Typically a follow up error of type E_ERROR will happen. In case of a read operation and an empty slave list the behavior depends on the fail over configuration. If fail over to master is enabled, no error should appear. If fail over to master is deactivated the plugin will emit a warning that may read (mysqlnd_ms) Couldn't find the appropriate slave connection. 0 slaves to choose from. Something is wrong in %s on line %d.

The node_groups filter lets you group cluster nodes and query selected groups, for example, to support data partitioning. Data partitioning can be required for manual sharding, primary copy based clusters running multiple masters, or to avoid hot spots in update everywhere clusters that have no built-in partitioning. The filter is a multi filter which returns zero, one or multiple of its input servers. Thus, it must be followed by other filters to reduce the number of candidates down to one for statement execution.

 {
  "myapp": {
       "master": {
            "master_0": {
                "host": "localhost",
                "socket": "\/tmp\/mysql.sock"
            }
        },
        "slave": {
            "slave_0": {
                "host": "192.168.2.28",
                "port": 3306
            },
            "slave_1": {
                "host": "127.0.0.1",
                "port": 3311
            }
        },
        "filters": {
            "node_groups": {
                "Partition_A" : {
                    "master": ["master_0"],
                    "slave": ["slave_0"]
                }
            },
           "roundrobin": []
        }
    }
}

The quality_of_service identifies cluster nodes capable of delivering a certain quality of service. It is a multi filter which returns zero, one or multiple of its input servers. Thus, it must be followed by other filters to reduce the number of candidates down to one for statement execution.

The quality_of_service filter has been introduced in 1.2.0-alpha. In the 1.2 series the filters focus is on the consistency aspect of service quality. Different types of clusters offer different default data consistencies. For example, an asynchronous MySQL replication slave offers eventual consistency. The slave may not be able to deliver requested data because it has not replicated the write, it may serve stale database because its lagging behind or it may serve current information. Often, this is acceptable. In some cases higher consistency levels are needed for the application to work correct. In those cases, the quality_of_service can filter out cluster nodes which cannot deliver the necessary quality of service.

The quality_of_service filter can be replaced or created at runtime. A successful call to mysqlnd_ms_set_qos removes all existing qos filter entries from the filter list and installs a new one at the very beginning. All settings that can be made through mysqlnd_ms_set_qos can also be in the plugins configuration file. However, use of the function is by far the most common use case. Instead of setting session consistency and strong consistency service levels in the plugins configuration file it is recommended to define only masters and no slaves. Both service levels will force the use of masters only. Using an empty slave list shortens the configuration file, thus improving readability. The only service level for which there is a case of defining in the plugins configuration file is the combination of eventual consistency and maximum slave lag.

{
    "myapp": {
        "master": {
            "master_0": {
                "host": "localhost"
            }
        },
        "slave": {
            "slave_0": {
                "host": "192.168.2.27",
                "port": "3306"
            },
            "slave_1": {
                "host": "192.168.78.136",
                "port": "3306"
            }
        },
        "filters": {
            "quality_of_service": {
                "eventual_consistency": {
                    "age":123
                }
            }
        }
    }
}

Failover policy. Supported policies: disabled (default), master, loop_before_master (Since 1.4.0).

If no failover policy is set, the plugin will not do any automatic failover (failover=disabled). Whenever the plugin fails to connect a server it will emit a warning and set the connections error code and message. Thereafter it is up to the application to handle the error and, for example, resent the last statement to trigger the selection of another server.

Please note, the automatic failover logic is applied when opening connections only. Once a connection has been opened no automatic attempts are made to reopen it in case of an error. If, for example, the server a connection is connected to is shut down and the user attempts to run a statement on the connection, no automatic failover will be tried. Instead, an error will be reported.

If using failover=master the plugin will implicitly failover to a master, if available. Please check the concepts documentation to learn about potential pitfalls and risks of using failover=master.

{
    "myapp": {
        "master": {
            "master_0": {
                "host": "localhost"
            }
        },
        "slave": {
            "slave_0": {
                "host": "192.168.78.136",
                "port": "3306"
            }
        },
        "failover": "master"
    }
}

Since PECL/mysqlnd_ms 1.4.0 the failover configuration keyword refers to an object.

{
    "myapp": {
        "master": {
            "master_0": {
                "host": "localhost"
            }
        },
        "slave": {
            "slave_0": {
                "host": "192.168.78.136",
                "port": "3306"
            }
        },
        "failover": {"strategy": "master" }
    }
}

Setting failover to any other value but disabled, master or loop_before_master will not emit any warning or error.

Controls the use of lazy connections. Lazy connections are connections which are not opened before the client sends the first connection. Lazy connections are a default.

It is strongly recommended to use lazy connections. Lazy connections help to keep the number of open connections low. If you disable lazy connections and, for example, configure one MySQL replication master server and two MySQL replication slaves, the plugin will open three connections upon the first call to a connect function although the application might use the master connection only.

Lazy connections bare a risk if you make heavy use of actions which change the state of a connection. The plugin does not dispatch all state changing actions to all connections from the connection pool. The few dispatched actions are applied to already opened connections only. Lazy connections opened in the future are not affected. Only some settings are "remembered" and applied when lazy connections are opened.

{
    "myapp": {
        "master": {
            "master_0": {
                "host": "localhost"
            }
        },
        "slave": {
            "slave_0": {
                "host": "192.168.78.136",
                "port": "3306"
            }
        },
        "lazy_connections": 0
    }
}

Please, see also server_charset to overcome potential problems with string escaping and servers using different default charsets.

The setting has been introduced in 1.4.0. It is recommended to set it if using lazy connections.

The server_charset setting serves two purposes. It acts as a fallback charset to be used for string escaping done before a connection has been established and it helps to avoid escaping pitfalls in heterogeneous environments which servers using different default charsets.

String escaping takes a connections charset into account. String escaping is not possible before a connection has been opened and the connections charset is known. The use of lazy connections delays the actual opening of connections until a statement is send.

An application using lazy connections may attempt to escape a string before sending a statement. In fact, this should be a common case as the statement string may contain the string that is to be escaped. However, due to the lazy connection feature no connection has been opened yet and escaping fails. The plugin may report an error of the type E_WARNING and a message like (mysqlnd_ms) string escaping doesn't work without established connection. Possible solution is to add server_charset to your configuration to inform you of the pitfall.

Setting server_charset makes the plugin use the given charset for string escaping done on lazy connection handles before establishing a network connection to MySQL. Furthermore, the plugin will enforce the use of the charset when the connection is established.

Enforcing the use of the configured charset used for escaping is done to prevent tapping into the pitfall of using a different charset for escaping than used later for the connection. This has the additional benefit of removing the need to align the charset configuration of all servers used. No matter what the default charset on any of the servers is, the plugin will set the configured one as a default.

The plugin does not stop the user from changing the charset at any time using the set_charset call or corresponding SQL statements. Please, note that the use of SQL is not recommended as it cannot be monitored by the plugin. The user can, for example, change the charset on a lazy connection handle after escaping a string and before the actual connection is opened. The charset set by the user will be used for any subsequent escaping before the connection is established. The connection will be established using the configured charset, no matter what the server charset is or what the user has set before. Once a connection has been opened, set_charset is of no meaning anymore.

{
    "myapp": {
        "master": {
            "master_0": {
                "host": "localhost"
            }
        },
        "slave": {
            "slave_0": {
                "host": "192.168.78.136",
                "port": "3306"
            }
        },
        "lazy_connections": 1,
        "server_charset" : "utf8"
    }
}

        

<?php
$mysqli = new mysqli("myapp", "username", "password", "database");
$mysqli->real_escape("this will be escaped using the server_charset setting - utf8");
$mysqli->set_charset("latin1");
$mysqli->real_escape("this will be escaped using latin1");
/* server_charset implicitly set - utf8 connection */
$mysqli->query("SELECT 'This connection will be set to server_charset upon establishing' AS _msg FROM DUAL");
/* latin1 used from now on */
$mysqli->set_charset("latin1");
?>

If set, the plugin will use the master server only after the first statement has been executed on the master. Applications can still send statements to the slaves using SQL hints to overrule the automatic decision.

The setting may help with replication lag. If an application runs an INSERT the plugin will, by default, use the master to execute all following statements, including SELECT statements. This helps to avoid problems with reads from slaves which have not replicated the INSERT yet.

{
    "myapp": {
        "master": {
            "master_0": {
                "host": "localhost"
            }
        },
        "slave": {
            "slave_0": {
                "host": "192.168.78.136",
                "port": "3306"
            }
        },
        "master_on_write": 1
    }
}

Please, note the quality_of_service filter introduced in version 1.2.0-alpha. It gives finer control, for example, for achieving read-your-writes and, it offers additional functionality introducing service levels.

All transaction stickiness settings, including trx_stickiness=on, are overruled by master_on_write=1.

Transaction stickiness policy. Supported policies: disabled (default), master.

The setting requires 5.4.0 or newer. If used with PHP older than 5.4.0, the plugin will emit a warning like (mysqlnd_ms) trx_stickiness strategy is not supported before PHP 5.3.99.

If no transaction stickiness policy is set or, if setting trx_stickiness=disabled, the plugin is not transaction aware. Thus, the plugin may load balance connections and switch connections in the middle of a transaction. The plugin is not transaction safe. SQL hints must be used avoid connection switches during a transaction.

As of PHP 5.4.0 the mysqlnd library allows the plugin to monitor the autocommit mode set by calls to the libraries set_autocommit() function. If setting set_stickiness=master and autocommit gets disabled by a PHP MySQL extension invoking the mysqlnd library internal function call set_autocommit(), the plugin is made aware of the begin of a transaction. Then, the plugin stops load balancing and directs all statements to the master server until autocommit is enabled. Thus, no SQL hints are required.

An example of a PHP MySQL API function calling the mysqlnd library internal function call set_autocommit() is mysqli_autocommit.

Although setting trx_stickiness=master, the plugin cannot be made aware of autocommit mode changes caused by SQL statements such as SET AUTOCOMMIT=0 or BEGIN.

As of PHP 5.5.0, the mysqlnd library features additional C API calls to control transactions. The level of control matches the one offered by SQL statements. The mysqli API has been modified to use these calls. Since version 1.5.0, PECL/mysqlnd_ms can monitor not only mysqli_autocommit, but also mysqli_begin, mysqli_commit and mysqli_rollback to detect transaction boundaries and stop load balancing for the duration of a transaction.

{
    "myapp": {
        "master": {
            "master_0": {
                "host": "localhost"
            }
        },
        "slave": {
            "slave_0": {
                "host": "192.168.78.136",
                "port": "3306"
            }
        },
        "trx_stickiness": "master"
    }
}

Since version 1.5.0 automatic and silent failover is disabled for the duration of a transaction. If the boundaries of a transaction have been properly detected, transaction stickiness is enabled and a server fails, the plugin will not attempt to fail over to the next server, if any, regardless of the failover policy configured. The user must handle the error manually. Depending on the configuration, the plugin may emit an error of type E_WARNING reading like (mysqlnd_ms) Automatic failover is not permitted in the middle of a transaction. This error may then be overwritten by follow up errors such as (mysqlnd_ms) No connection selected by the last filter. Those errors will be generated by the failing query function.

<?php
/* assumption: automatic failover configured */
$mysqli = new mysqli("myapp", "username", "password", "database");

/* sets plugin internal state in_trx = 1 */
$mysqli->autocommit(false);

/* assumption: server fails */
if (!($res = $mysqli->query("SELECT 'Assume this query fails' AS _msg FROM DUAL"))) {
 /* handle failure of transaction, plugin internal state is still in_trx = 1 */
 printf("[%d] %s", $mysqli->errno, $mysqli->error);
 /*
  If using autocommit() based transaction detection it is a
  MUST to call autocommit(true). Otherwise the plugin assumes
  the current transaction continues and connection
  changes remain forbidden.
 */
 $mysqli->autocommit(true);
 /* Likewise, you'll want to start a new transaction */
 $mysqli->autocommit(false);
}
/* latin1 used from now on */
$mysqli->set_charset("latin1");
?>

If a server fails in the middle of a transaction the plugin continues to refuse to switch connections until the current transaction has been finished. Recall that the plugin monitors API calls to detect transaction boundaries. Thus, you have to, for example, enable auto commit mode to end the current transaction before the plugin continues load balancing and switches the server. Likewise, you will want to start a new transaction immediately thereafter and disable auto commit mode again.

Not handling failed queries and not ending a failed transaction using API calls may cause all following commands emit errors such as Commands out of sync; you can't run this command now. Thus, it is important to handle all errors.

URI of a MySQL replication master server. The URI follows the syntax hostname[:port|unix_domain_socket].

The plugin supports using only one master server.

Setting a master server is mandatory. The plugin will report a warning upon connect if the user has failed to provide a master server for a configuration section. The warning may read (mysqlnd_ms) Cannot find master section in config. Furthermore the plugin may set an error code for the connection handle such as HY000/2000 (CR_UNKNOWN_ERROR). The corresponding error message depends on your language settings.

URI of one or more MySQL replication slave servers. The URI follows the syntax hostname[:port|unix_domain_socket].

The plugin supports using one or more slave servers.

Setting a slave server is mandatory. The plugin will report a warning upon connect if the user has failed to provide at least one slave server for a configuration section. The warning may read (mysqlnd_ms) Cannot find slaves section in config. Furthermore the plugin may set an error code for the connection handle such as HY000/2000 (CR_UNKNOWN_ERROR). The corresponding error message depends on your language settings.

Load balancing (server picking) policy. Supported policies: random, random_once (default), roundrobin, user.

If no load balancing policy is set, the plugin will default to random_once. The random_once policy picks a random slave server when running the first read-only statement. The slave server will be used for all read-only statements until the PHP script execution ends.

The random policy will pick a random server whenever a read-only statement is to be executed.

If using roundrobin the plugin iterates over the list of configured slave servers to pick a server for statement execution. If the plugin reaches the end of the list, it wraps around to the beginning of the list and picks the first configured slave server.

Setting more than one load balancing policy for a configuration section makes only sense in conjunction with user and mysqlnd_ms_set_user_pick_server. If the user defined callback fails to pick a server, the plugin falls back to the second configured load balancing policy.

Failover policy. Supported policies: disabled (default), master.

If no failover policy is set, the plugin will not do any automatic failover (failover=disabled). Whenever the plugin fails to connect a server it will emit a warning and set the connections error code and message. Thereafter it is up to the application to handle the error and, for example, resent the last statement to trigger the selection of another server.

If using failover=master the plugin will implicitly failover to a slave, if available. Please check the concepts documentation to learn about potential pitfalls and risks of using failover=master.

Controls the use of lazy connections. Lazy connections are connections which are not opened before the client sends the first connection.

It is strongly recommended to use lazy connections. Lazy connections help to keep the number of open connections low. If you disable lazy connections and, for example, configure one MySQL replication master server and two MySQL replication slaves, the plugin will open three connections upon the first call to a connect function although the application might use the master connection only.

Lazy connections bare a risk if you make heavy use of actions which change the state of a connection. The plugin does not dispatch all state changing actions to all connections from the connection pool. The few dispatched actions are applied to already opened connections only. Lazy connections opened in the future are not affected. If, for example, the connection character set is changed using a PHP MySQL API call, the plugin will change the character set of all currently opened connection. It will not remember the character set change to apply it on lazy connections opened in the future. As a result the internal connection pool would hold connections using different character sets. This is not desired. Remember that character sets are taken into account for escaping.

If set, the plugin will use the master server only after the first statement has been executed on the master. Applications can still send statements to the slaves using SQL hints to overrule the automatic decision.

The setting may help with replication lag. If an application runs an INSERT the plugin will, by default, use the master to execute all following statements, including SELECT statements. This helps to avoid problems with reads from slaves which have not replicated the INSERT yet.

Transaction stickiness policy. Supported policies: disabled (default), master.

Experimental feature.

The setting requires 5.4.0 or newer. If used with PHP older than 5.4.0, the plugin will emit a warning like (mysqlnd_ms) trx_stickiness strategy is not supported before PHP 5.3.99.

If no transaction stickiness policy is set or, if setting trx_stickiness=disabled, the plugin is not transaction aware. Thus, the plugin may load balance connections and switch connections in the middle of a transaction. The plugin is not transaction safe. SQL hints must be used avoid connection switches during a transaction.

As of PHP 5.4.0 the mysqlnd library allows the plugin to monitor the autocommit mode set by calls to the libraries trx_autocommit() function. If setting trx_stickiness=master and autocommit gets disabled by a PHP MySQL extension invoking the mysqlnd library internal function call trx_autocommit(), the plugin is made aware of the begin of a transaction. Then, the plugin stops load balancing and directs all statements to the master server until autocommit is enabled. Thus, no SQL hints are required.

An example of a PHP MySQL API function calling the mysqlnd library internal function call trx_autocommit() is mysqli_autocommit.

Although setting trx_stickiness=master, the plugin cannot be made aware of autocommit mode changes caused by SQL statements such as SET AUTOCOMMIT=0.