The mysqli quickstart series is coming to an end. Today, the post is about non-prepared statements. You may also want to check out the following related blog posts:

• Using MySQL prepared statements with PHP mysqli
• Using MySQL multiple statements with PHP mysqli
• Using MySQL stored procedures with PHP mysqli

Using mysqli to execute statements

Statements can be executed by help of the mysqli_query(), mysqli_real_query() and mysqli_multi_query() function. The mysqli_query() function is the most

Opening a database connection is a boring tasks. But do you know how defaults are determined, if values are omitted? Or, did you know there are two flavours of persistent connections in mysqli? Of course you, as a german reader, know it. I blogged about it in 2009 over at phphatesme.com (Nimmer Ärger mit den Persistenten Verbindungen von MySQL? ) …

Database connections with mysqli

The MySQL server supports the use of different transport layers for connections. Connections use TCP/IP, Unix domain sockets or Windows named pipes.

The hostname localhost has a special meaning. It is bound to the use of Unix domain sockets. It is not possible to open a TCP/IP connection using the hostname localhost you must use 127.0.0.1

The series Using X with PHP mysqli continues. After notes on calling stored procedures and using prepared statements, its time for a multiple statement quickstart. A mighty tool, if used with care…

Using Multiple Statements with mysqli

MySQL optionally allows having multiple statements in one statement string. Sending multiple statements at once reduces client-server round trips but requires special handling.

Multiple statements or multi queries must be executed with mysqli_multi_query(). The individual statements of the statement string are seperated by semicolon. Then, all result sets returned by the executed statements must be

PECL/mysqlnd 1.1.2-stable has been released. The mysqlnd replication and load balancing plugin for PHP 5.3/5.4 finally got the download label it deserves: stable, ready for production use! PECL/mysqlnd_ms makes using any kind of MySQL database cluster easier.

• Download PECL/mysqlnd from pecl.php.net
• Documentation at the PHP Reference Manual

Key features

The release motto of the 1.1 series is “cover MySQL Replication basics with production quality”, which shows that the plugin is optimized for supporting MySQL replication cluster. But with its feature set it is not limited to. MySQL Cluster users will also profit from it.

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 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

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 (http://www.mysql.com/products/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).

If using the new configuration setting mysqlnd_ms.disable_rw_split=1, the plugin will load balance requests over the

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;

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 (http://mysql.com/news-and-events/web-seminars/display-668.html) 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!");
 }
}

It is the third day I try to find mysqlnd plugin use cases for the Succeed with Plugins (http://mysql.com/news-and-events/web-seminars/display-668.html) 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

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

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

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());

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,

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,

The soon to be announced version 1.1.0-beta of the mysqlnd replication and load balancing plugin (PECL/mysqlnd_ms) for PHP introduces a new concept of filters to the plugin. Filters take a list of servers to pick one or more of it. Filters can be chained, similar command line tools. Imagine a future with a filter chain like: user_multi | roundrobin, table_partitioning | random_once, table_partitioning | adaptive_loadbalancer, … For example, user_multi | roundrobin will first invoke a callback and then apply static round robin load balancing to the servers returned by the callback set with user_multi . Or, table_partitioning | adaptive_loadbalancer would first apply

The soon to be announced version 1.1.0-beta of the mysqlnd replication and load balancing plugin (PECL/mysqlnd_ms) for PHP introduces a new concept of filters to the plugin. Filters take a list of servers to pick one or more of it. Filters can be chained, similar command line tools. Imagine a future with a filter chain like: user_multi | roundrobin, table_partitioning | random_once, table_partitioning | adaptive_loadbalancer, … For example, user_multi | roundrobin will first invoke a callback and then apply static round robin load balancing to the servers returned by the callback set with user_multi . Or, table_partitioning | adaptive_loadbalancer would first apply

Recently I was asked if PECL/mysqlnd_ms should be used to add MySQL replication support to a yet to be developed PHP application. The mysqlnd plugin, which supports all PHP MySQL extensions (PDO, mysqli, mysql), stood up against a classical, simple, proven and fast approach: one connection for reads, one connection for writes. Let’s compare. This is a bit of an unfair challenge, because PECL/mysqlnd_ms was designed as a drop-in for existing applications, not optimized for those starting from scratch, *yell*… The plugin stands up quite well, anyway!

The classical pattern

If starting from scratch you can manually direct all reads to the slaves and all writes to the masters. Use a factory/singleton to create database objects. For database read requests, ask the factory for connection

Recently I was asked if PECL/mysqlnd_ms should be used to add MySQL replication support to a yet to be developed PHP application. The mysqlnd plugin, which supports all PHP MySQL extensions (PDO, mysqli, mysql), stood up against a classical, simple, proven and fast approach: one connection for reads, one connection for writes. Let’s compare. This is a bit of an unfair challenge, because PECL/mysqlnd_ms was designed as a drop-in for existing applications, not optimized for those starting from scratch, *yell*… The plugin stands up quite well, anyway!

The classical pattern

If starting from scratch you can manually direct all reads to the slaves and all writes to the masters. Use a factory/singleton to create database objects. For database read

After a short sprint for PHP 5.4 beta, which is on its way with mysqlnd as a configuration default for all three PHP MySQL extensions, we continued working on the mysqlnd replication plugin (PECL/mysqlnd_ms). Please, find a high level overview presentation further below in this blog post. Because replication support is added at the mysqlnd library level, it is almost transparent from an applications point of view. If you are new to mysqlnd plugins, think of it as a proxy. A proxy that you can drop-in to any existing PHP MySQL application.

Significant progress has been made since the initial alpha release: new powerful configuration syntax, many limitations on lazy connections lifted, countless issues identified and fixed through many new

After a short sprint for PHP 5.4 beta, which is on its way with mysqlnd as a configuration default for all three PHP MySQL extensions, we continued working on the mysqlnd replication plugin (PECL/mysqlnd_ms). Please, find a high level overview presentation further below in this blog post. Because replication support is added at the mysqlnd library level, it is almost transparent from an applications point of view. If you are new to mysqlnd plugins, think of it as a proxy. A proxy that you can drop-in to any existing PHP MySQL application.

Significant progress has been made since the initial alpha release: new powerful configuration syntax, many limitations on lazy connections lifted, countless

The PHP replication and load balancing plugin has a configuration setting to run all transactions on the master. But how to detect the start of a transaction? I got a hint to look at a flag in the client-server protocol which tells us, if we are in a transaction. I was not aware of the flag and, I loved the idea when I heard it. The plugin wouldn’t have to worry about how the client starts a transaction. A PHP client can start a transaction by turning off autocommit via API (e.g. mysqli_autocommit()) or executing SQL (BEGIN, START TRANSACTION, SET AUTOCOMMIT=0). It is time consuming to catch them all, if possible at all.

Server status flag from the protocol

I hacked mysqlnd to print the

The PHP replication and load balancing plugin has a configuration setting to run all transactions on the master. But how to detect the start of a transaction? I got a hint to look at a flag in the client-server protocol which tells us, if we are in a transaction. I was not aware of the flag and, I loved the idea when I heard it. The plugin wouldn’t have to worry about how the client starts a transaction. A PHP client can start a transaction by turning off autocommit via API (e.g. mysqli_autocommit()) or executing SQL (BEGIN, START TRANSACTION, SET AUTOCOMMIT=0). It is time consuming to catch them all, if possible at all.

Server status flag from the protocol

I hacked

SQL injection is wonderful! MySQL Proxy can do it, mysqlnd plugins - even written in PHP (not Lua or C) - can do it. Global Transaction IDs are wonderful. A mashup of the PHP replication plugin and global transaction ID injection, makes your replication cluster fail-over much smoother and opens up an opportunity for an API to support consistent reads from slaves "immediately" after a write. Less hassle identifying and promoting a new master for fail-over, even better read load balancing - my last proposal for the future of the PHP replication

SQL injection is wonderful! MySQL Proxy can do it, mysqlnd plugins - even written in PHP (not Lua or C) - can do it. Global Transaction IDs are wonderful. A mashup of the PHP replication plugin and global transaction ID injection, makes your replication cluster fail-over much smoother and opens up an opportunity for an API to support consistent reads from slaves "immediately" after a write. Less hassle identifying and promoting a new master for fail-over, even better read load balancing - my last proposal for

While Andrey is busy implementing partitioned replication infrastructure code for the PHP replication and load balancing plugin (PECL/mysqlnd_ms), I continued my search for ideas to steal. Mr. Robert Hodges, I’ve robbed the idea of a service level and caching.. If an application is able to function with stale data read from a MySQL replication slave, it can also deal with stale data from a local cache. The replication plugin (PECL/mysqlnd_ms) could, in certain cases, populate the query cache plugin (PECL/mysqlnd_qc) for you and read replies from it.

In the blog posting "

While Andrey is busy implementing partitioned replication infrastructure code for the PHP replication and load balancing plugin (PECL/mysqlnd_ms), I continued my search for ideas to steal. Mr. Robert Hodges, I’ve robbed the idea of a service level and caching.. If an application is able to function with stale data read from a MySQL replication slave, it can also deal with stale data from a local cache. The replication plugin (PECL/mysqlnd_ms) could, in certain cases, populate the query cache plugin (PECL/mysqlnd_qc) for you and read replies from it.

The Mysqlnd replication and load balancing plugin alpha release has focused on laying foundations for read-write splitting and load balancing. Thus, we can now look into more juicy topics such as support of schemata based partitioning to increase the granularity of load balancing. Not every slave server needs to replicate all schemata (databases) and tables of the master. The plugin load balancer shall be aware of it and transparently pick the server which has the entities required to execute a statement. Some brainstorming…

Replicating  [Read more...]

The Mysqlnd replication and load balancing plugin alpha release has focused on laying foundations for read-write splitting and load balancing. Thus, we can now look into more juicy topics such as support of schemata based partitioning to increase the granularity of load balancing. Not every slave server needs to replicate all schemata (databases) and tables of the master. The plugin load balancer shall be aware of it and transparently pick the server which has the entities required to execute a statement. Some brainstorming…

The PHP replication and load balancing mysqlnd plugin as been released as an alpha through PECL. Alpha is for those who want to try out the 1.0 feature set as early as possible. Alpha is for those who do not fear debugging, if need be. Notes on troubleshooting.

Testing load balancing and read/write split

There are three ways to verify that load balancing and read/write split works as it should:

• within PHP script: checking thread/connection ids
• within PHP script: monitoring statistics
• externally: mysqlnd debug log file

Thread/connection ids to distinguish connections

PECL/mysqlnd_ms changes the semantics of a PHP MySQL connection handle. If using the plugin, a connection handle returned by any of the three PHP MySQL extensions (