Starting with PHP mysqli is easy, if one has some SQL and PHP skills. To get started one needs to know about the specifics of MySQL and a few code snippets. Using MySQL stored procedures with PHP mysqli has found enough readers to begin with a “quickstart” or “how-to” series. Take this post with a grain of salt. I have nothing against Prepared Statements as such, but I dislike unreflected blind use.

Using prepared statements with mysqli

The MySQL database supports prepared statements. A prepared statement or a parameterized statement is used to execute the same statement repeatedly with high efficiency.

Basic workflow

The prepared statement execution consists of two stages: prepare and execute. At the prepare stage a statement template is send to the database server. The server performs a syntax …

A couple of weeks ago a friend of mine asked me how to use MySQL stored procedures with PHP’s mysqli API. Out of curiosity I asked another friend, a team lead, how things where going with their PHP MySQL project, for which they had planned to have most of their business logic in stored procedures. I got an email in reply stating something along the lines: "Our developers found that mysqli does not support stored procedures correctly. We use PDO.". Well, the existing documentation from PHP 5.0 times is not stellar, I confess. But still, that’s a bit too much… it ain’t that difficult. And, it works.

Using stored procedures with mysqli

The MySQL database supports stored procedures. A stored procedure is a subroutine stored in the database catalog. Applications can call and execute the stored procedure. The CALL SQL statement is used to execute a stored procedure.

Parameter

Stored procedures can …

The free Mysqlnd replication and load balancing plugin now offers load balancing and lazy connections independent of read write splitting. This makes the plugin attractive for MySQL Cluster users. All nodes participating in a MySQL Cluster can serve all requests, they all accept read and write requests. No statement redirection needs to be done. An application using MySQL Cluster has only one task: load balance requests over MySQL frontends (SQL Nodes).

Would you like to see the EXPLAIN output for all MySQL queries of any PHP application without changing the application much? Easy-peasy: compile PHP to use the mysqlnd library, install PECL/mysqlnd_uh and paste 22 lines of evil code into your auto_prepend_file .

class conn_proxy extends MysqlndUhConnection {
 public function query($conn, $query, $self = false) {
  if (!$self) {
   $this->query($conn, "EXPLAIN " . $query, true);
   if ($this->getFieldCount($conn)) {
    printf("\tAuto EXPLAIN for '%s'\n", $query);
    $res = $this->storeResult($conn);
    $r = new MysqlndUhresult();
    do {
      $row = NULL;
      $r->fetchInto($res, $row, 2, 1);
      if (is_array($row))
        printf("\t\t%s\n", …

Unfortunately MySQL Proxy was no good source of inspiration today. MySQL Proxy can do many wonderful things which you can do with C based mysqlnd plugins as well. But not with PECL/mysqlnd_uh. PECL/mysqlnd_uh lets you write “plugins” in PHP. Given my desire to demo the power of mysqlnd plugins at the upcoming webinar Succeed with Plugins using PHP examples, I had to extend PECL/mysqlnd_uh to allow result set manipulation. Five brand new lines of magic.

class __mysqlnd_result extends MysqlndUhResult {
 public function fetchInto($res, &$rows, $flags, $extension) {
  $rows = array("Your mysqlnd has been hacked!");
 }
}
mysqlnd_uh_set_result_proxy(new __mysqlnd_result());

…

It is the third day I try to find mysqlnd plugin use cases for the Succeed with Plugins webinar on October, 26th. Not being innovative or creative today, I looked into a classic: client fail over. As a trained and talented reader, you won’t be shocked to see 54 lines of PECL/mysqlnd_uh hacking today.

class __mysqlnd_conn_failover extends MysqlndUhConnection {
        
 private $fail_over_errno = array(
  2002 => "Can't connect to local MySQL server through socket '%s' (%d)",
  2003 => "Can't connect to MySQL server on '%s' (%d)",
  2004 => "Unknown MySQL server host '%s' (%d)",
  2006 => "MySQL server has gone away",
  2013 => "Lost connection to MySQL server during query"
 );
 private $fail_over_servers = array(
  array("host" => …

Support nightmare: a customer reports a random PHP MySQL error. As a support expert you have the strong feeling that it is down to some suspicious SQL sequence. How to proof? 25 lines of PECL/mysqlnd_uh swiss-army knife magic…

prepend.php

class __mysqlnd_logger extends MysqlndUhConnection {
 private $protocol;
        
 public function query($conn, $query) {
  $ret = parent::query($conn, $query);
  if ($errno = $this->getErrorNumber($conn)) {
   $this->protocol[] = array(
    "query" => $query,
    "error" => sprintf("[%d] %s",
     $errno, $this->getErrorString($conn)),
    "bt" => debug_backtrace()
   );
  } else {
   $this->protocol[] = $query;
  }
  return $ret;
 }
        
 public function getProtocol() {
  return $this->protocol;
 }
        
}
$__logger = new __mysqlnd_logger();
mysqlnd_uh_set_connection_proxy($__logger);

The …

Uh, uh… about a year ago Mayflower OpenSource Labs released the mysqlnd user handler plugin (PECL/mysqlnd_uh). The extension lets you extend and replace mysqlnd internal function calls with PHP. Uh, uh… mysqlnd internals exported to user space? Who cares as long as it does the trick?! Let me show you seven lines of PHP code to monitor all queries issued by any PHP MySQL application using any PHP MySQL extension (mysql, mysqli, PDO_MySQL) compiled to use the mysqlnd library.

query_monitor.php

class conn_proxy extends MysqlndUhConnection {
 public function query($res, $query) {
  debug_print_backtrace();
  return parent::query($res, $query);
 }
}
mysqlnd_uh_set_connection_proxy(new conn_proxy());

That’s it. Install PECL/mysqlnd_uh and load the above query_monitor.php before your …

Uh, uh… about a year ago Mayflower OpenSource Labs released the mysqlnd user handler plugin (PECL/mysqlnd_uh). The extension lets you extend and replace mysqlnd internal function calls with PHP. Uh, uh… mysqlnd internals exported to user space? Who cares as long as it does the trick?! Let me show you seven lines of PHP code to monitor all queries issued by any PHP MySQL application using any PHP MySQL extension (mysql, mysqli, PDO_MySQL) compiled to use the mysqlnd library.

query_monitor.php

class conn_proxy extends MysqlndUhConnection {
 public function query($res, $query) {
  debug_print_backtrace();
  return parent::query($res, $query);
 }
}
mysqlnd_uh_set_connection_proxy(new conn_proxy());

That’s it. Install PECL/mysqlnd_uh and load the above query_monitor.php before your …

PECL/mysqlnd_ms 1.1.0 (download) has been released (documentation)! It is a drop-in solution to add MySQL replication support to any PHP 5.3+ application using any of the PHP MySQL APIs (mysql, mysqli, PDO_MySQL) when compiled to use the mysqlnd library. It extends the mysqlnd library by replication and load balancing funtionality. The mysqlnd library is an optional replacement for the MySQL Client Library (AKA libmysql). The mysqlnd library ships together with PHP as of version 5.3. As of PHP 5.4 the mysqlnd library is a compile time default choice for all three PHP MySQL extensions.

The plugin provides automatic read-write splitting, …