mysqlnd-ms-php-quickstart-php-sqlhints-0

  • Quickstart and
    Examples
  • SQL Hints

  • SQL Hints
  • SQL Hints

    SQL Hints

    SQL hints can force a query to choose a specific
    server from the connection pool. It gives the plugin a hint to use
    a designated server, which can solve issues caused by connection
    switches and connection state.

    SQL hints are standard compliant SQL comments.
    Because SQL comments are supposed to be ignored by SQL processing
    systems, they do not interfere with other programs such as the
    MySQL Server, the MySQL Proxy, or a firewall.

    Three SQL hints are supported by the plugin: The
    MYSQLND_MS_MASTER_SWITCH hint makes
    the plugin run a statement on the master,
    MYSQLND_MS_SLAVE_SWITCH enforces the
    use of the slave, and
    MYSQLND_MS_LAST_USED_SWITCH will run
    a statement on the same server that was used for the previous
    statement.

    The plugin scans the beginning of a statement for
    the existence of an SQL hint. SQL hints are only recognized if they
    appear at the beginning of the statement.

    Example #1 Plugin config with one slave and one
    master

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

    Example #2 SQL hints to prevent connection
    switches

    <?php
    $mysqli 
    = new mysqli("myapp""username""password""database");
    if (
    mysqli_connect_errno()) {
        
    /* 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 1, run on master because of SQL hint */
    if (!($res $mysqli->query(sprintf("/*%s*/SELECT @myrole AS _role"MYSQLND_MS_LAST_USED_SWITCH)))) {
        
    printf("[%d] %s\n"$mysqli->errno$mysqli->error);
    } else {
        
    $row $res->fetch_assoc();
        
    $res->close();
        
    printf("@myrole = '%s'\n"$row['_role']);
    }
    $mysqli->close();
    ?>

    The above example will output:

    @myrole = 'master'
    

    In the above example, using
    MYSQLND_MS_LAST_USED_SWITCH prevents
    session switching from the master to a slave when running the
    SELECT statement.

    SQL hints can also be used to run SELECT
    statements on the MySQL master server. This may be desired if the
    MySQL slave servers are typically behind the master, but you need
    current data from the cluster.

    In version 1.2.0 the concept of a service level has
    been introduced to address cases when current data is required.
    Using a service level requires less attention and removes the need
    of using SQL hints for this use case. Please, find more information
    below in the service level and consistency section.

    Example #3 Fighting replication lag

    <?php
    $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()));
    }

    /* Force use of master, master has always fresh and current data */
    if (!$mysqli->query(sprintf("/*%s*/SELECT critical_data FROM important_table"MYSQLND_MS_MASTER_SWITCH))) {
        
    printf("[%d] %s\n"$mysqli->errno$mysqli->error);
    }
    ?>

    A use case may include the creation of tables on a
    slave. If an SQL hint is not given, then the plugin will send
    CREATE and INSERT statements to the master. Use
    the SQL hint MYSQLND_MS_SLAVE_SWITCH
    if you want to run any such statement on a slave, for example, to
    build temporary reporting tables.

    Example #4 Table creation on a slave

    <?php
    $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()));
    }

    /* Force use of slave */
    if (!$mysqli->query(sprintf("/*%s*/CREATE TABLE slave_reporting(id INT)"MYSQLND_MS_SLAVE_SWITCH))) {
        
    printf("[%d] %s\n"$mysqli->errno$mysqli->error);
    }
    /* Continue using this particular slave connection */
    if (!$mysqli->query(sprintf("/*%s*/INSERT INTO slave_reporting(id) VALUES (1), (2), (3)"MYSQLND_MS_LAST_USED_SWITCH))) {
        
    printf("[%d] %s\n"$mysqli->errno$mysqli->error);
    }
    /* Don't use MYSQLND_MS_SLAVE_SWITCH which would allow switching to another slave! */
    if ($res $mysqli->query(sprintf("/*%s*/SELECT COUNT(*) AS _num FROM slave_reporting"MYSQLND_MS_LAST_USED_SWITCH))) {
        
    $row $res->fetch_assoc();
        
    $res->close();
        
    printf("There are %d rows in the table 'slave_reporting'"$row['_num']);
    } else {
        
    printf("[%d] %s\n"$mysqli->errno$mysqli->error);
    }
    $mysqli->close();
    ?>

    The SQL hint
    MYSQLND_MS_LAST_USED forbids
    switching a connection, and forces use of the previously used
    connection.