• Quickstart and
  • Connection state

  • Connection state
  • Connection state

    Connection state

    The plugin changes the semantics of a PHP MySQL
    connection handle. A new connection handle represents a connection
    pool, instead of a single MySQL client-server network connection.
    The connection pool consists of a master connection, and optionally
    any number of slave connections.

    Every connection from the connection pool has its
    own state. For example, SQL user variables, temporary tables and
    transactions are part of the state. For a complete list of items
    that belong to the state of a connection, see the connection pooling and
    concepts documentation. If the plugin decides to
    switch connections for load balancing, the application could be
    given a connection which has a different state. Applications must
    be made aware of this.

    Example #1 Plugin config with one slave and one

        "myapp": {
            "master": {
                "master_0": {
                    "host": "localhost",
                    "socket": "\/tmp\/mysql.sock"
            "slave": {
                "slave_0": {
                    "host": "",
                    "port": "3306"

    Example #2 Pitfall: connection state and SQL user

    = new mysqli("myapp""username""password""database");
    if (!
    $mysqli) {
    /* Of course, your error handling is nicer... */
    die(sprintf("[%d] %s\n"mysqli_connect_errno(), mysqli_connect_error()));

    /* Connection 1, connection bound SQL user variable, no SELECT thus run on master */
    if (!$mysqli->query("SET @myrole='master'")) {
    printf("[%d] %s\n"$mysqli->errno$mysqli->error);

    /* Connection 2, run on slave because SELECT */
    if (!($res $mysqli->query("SELECT @myrole AS _role"))) {
    printf("[%d] %s\n"$mysqli->errno$mysqli->error);
    } else {
    $row $res->fetch_assoc();
    printf("@myrole = '%s'\n"$row['_role']);

    The above example will output:

    @myrole = ''

    The example opens a load balanced connection and
    executes two statements. The first statement SET
    does not begin with the string
    SELECT. Therefore the plugin does not recognize it as a
    read-only query which shall be run on a slave. The plugin runs the
    statement on the connection to the master. The statement sets a SQL
    user variable which is bound to the master connection. The state of
    the master connection has been changed.

    The next statement is SELECT @myrole AS
    . The plugin does recognize it as a read-only query and
    sends it to the slave. The statement is run on a connection to the
    slave. This second connection does not have any SQL user variables
    bound to it. It has a different state than the first connection to
    the master. The requested SQL user variable is not set. The example
    script prints @myrole = ”.

    It is the responsibility of the application
    developer to take care of the connection state. The plugin does not
    monitor all connection state changing activities. Monitoring all
    possible cases would be a very CPU intensive task, if it could be
    done at all.

    The pitfalls can easily be worked around using SQL