Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Previous 30 Newer Entries Showing entries 31 to 60 of 301 Next 30 Older Entries

Displaying posts with tag: PlanetMySQL (english) (reset)

Executing MySQL queries with PHP mysqli
Employee +1 Vote Up -0Vote Down

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

  [Read more...]
Using MySQL with PHP mysqli: Connections, Options, Pooling
Employee +1 Vote Up -0Vote Down

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

  [Read more...]
Using MySQL multiple statements with PHP mysqli
Employee +2 Vote Up -0Vote Down

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

  [Read more...]
1.1.2-*stable* release of the replication and load balancing plugin for PHP!
Employee +2 Vote Up -0Vote Down

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.

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.

  [Read more...]
Using MySQL prepared statements with PHP mysqli
Employee +1 Vote Up -0Vote Down

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

  [Read more...]
Using MySQL stored procedures with PHP mysqli
Employee +4 Vote Up -0Vote Down

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

  [Read more...]
PHP and MySQL Cluster: Load Balancing without R/W split
Employee +5 Vote Up -0Vote Down

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

Client | | MySQL frontend
(SQL Node) MySQL frontend
(SQL Node)   |   Cluster Node <> Cluster Node <> Cluster Node

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



  [Read more...]
Uh, uh… SQL injection for auto EXPLAIN
Employee +1 Vote Up -0Vote Down

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;
  [Read more...]
Uh, uh… faking or caching MySQL PHP results
Employee +1 Vote Up -0Vote Down

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!");
 }
}
  [Read more...]
Uh, uh… PHP MySQL client fail over
Employee +1 Vote Up -0Vote Down

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
  [Read more...]
Uh, uh… who caused that error? MySQL ?!
Employee +1 Vote Up -0Vote Down

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
  [Read more...]
Uh, uh… extending mysqlnd: monitoring and statement redirection
Employee +2 Vote Up -0Vote Down

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

  [Read more...]
Uh, uh… extending mysqlnd: monitoring and statement redirection
Employee +0 Vote Up -0Vote Down

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

  [Read more...]
The mysqlnd replication plugin 1.1.0 release
Employee +2 Vote Up -0Vote Down

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,

  [Read more...]
The mysqlnd replication plugin 1.1.0 release
Employee +0 Vote Up -0Vote Down

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,

  [Read more...]
Replication plugin ¦ filter ¦ conquer = 1.1.0 coming
Employee +4 Vote Up -0Vote Down

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

  [Read more...]
Replication plugin ¦ filter ¦ conquer = 1.1.0 coming
Employee +0 Vote Up -0Vote Down

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

  [Read more...]
PECL/mysqlnd_ms compared to a classic
Employee +2 Vote Up -0Vote Down

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

  [Read more...]
PECL/mysqlnd_ms compared to a classic
Employee +0 Vote Up -0Vote Down

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

  [Read more...]
A mysqlnd replication plugin presentation
Employee +2 Vote Up -0Vote Down

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

  [Read more...]
A mysqlnd replication plugin presentation
Employee +0 Vote Up -0Vote Down

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

  [Read more...]
No protocol flag for transaction aware load balancing
Employee +1 Vote Up -0Vote Down

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

  [Read more...]
No protocol flag for transaction aware load balancing
Employee +0 Vote Up -0Vote Down

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

  [Read more...]
Wonders of Global Transaction ID injection
Employee +7 Vote Up -0Vote Down

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

  [Read more...]
Wonders of Global Transaction ID injection
Employee +0 Vote Up -0Vote Down

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

  [Read more...]
PHP replication plugin future: eventual consistent, eventual served from cache!
Employee +5 Vote Up -0Vote Down

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 "

  [Read more...]
PHP replication plugin future: eventual consistent, eventual served from cache!
Employee +0 Vote Up -0Vote Down

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.

  [Read more...]
Partitioning support for the PHP replication plugin
Employee +1 Vote Up -0Vote Down

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…

PECL/mysqlnd_ms 1.0.1 Master Slave   | |   PECL/mysqlnd_ms: load balancer   PECL/mysqlnd_ms: read-write split   PHP application using PECL/mysqlnd_ms  

Replicating  [Read more...]

Partitioning support for the PHP replication plugin
Employee +0 Vote Up -0Vote Down

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…

PECL/mysqlnd_ms 1.0.1 Master Slave   | |   PECL/mysqlnd_ms: load balancer   PECL/mysqlnd_ms: read-write split   PHP application using  [Read more...]
PHP replication plugin statistics and troubleshooting
Employee +3 Vote Up -0Vote Down

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 (

  [Read more...]
Previous 30 Newer Entries Showing entries 31 to 60 of 301 Next 30 Older Entries

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.