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 中文
Showing entries 1 to 30 of 301 Next 30 Older Entries

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

MySQL 5.6: SHA256 secure password support for PHP mysqlnd
Employee +2 Vote Up -0Vote Down

Pluggable Authentication is one of the many new MySQL 5.6 features. Pluggable authentication adds a capability to use external authentication services (PAM, Windows login IDs, LDAP, Kerberos, …) but also introduces built-in strong SHA-256 hashing for passwords. The SHA-256 Authentication Plugin uses encryption to protect the password from being sniffed during authentication. Read a live report from teaching the PHP mysqlnd library the new secure authentication method.

Your choices

Early MySQL versions have had very poor password hashing. The PHP mysqlnd library has never supported the old, insecure

  [Read more...]
Not only SQL injection: I don’t trust you!
Employee +1 Vote Up -0Vote Down

Never trust user input! Injection is a threat . You are the new web developer, aren’t you?. Never trust user input is the first rule I had to learn as a web developer in anchient times. Injection can happen whenever user input is interpreted or used to compose new data. A quick recap of the #3 mistake from todays Top 10 MySQL Tips and Mistakes for PHP Developers (http://www.mysql.com/news-and-events/web-seminars/display-725.html) web presentation. A webinar recording should be available in a couple of days.

Don’t!

Your probation period as a PHP developer may come to an immediate end if you write code like this:

mysqli_query(
  $link, "SELECT actor, rating FROM movies WHERE name = '" .
  $_GET['movie_name'] . "'"
);
mysql> SELECT actor, rating FROM movies
    WHERE

  [Read more...]
Webinar tomorrow: Top 10 MySQL Tips and Mistakes for PHP Developers
Employee +2 Vote Up -0Vote Down

"But 63 slides is too much" has been the initial reply to the draft of tomorrows Top 10 MySQL Tips and Mistakes for PHP Developers (http://www.mysql.com/news-and-events/web-seminars/display-725.html) web presentation (Tuesday, September 25, 2012: 10:00 CET). SQL injection, security and access control, monitoring for indexing and tuning, choosing adequate data types, character sets, how your web application speaks UTF8 correctly, types of connections and their performance properties as well as planning for tomorrow are too important to allow for less slides, Johannes Schlueter and I thought.

Beginners will be presented with a list of pitfalls to avoid. Advanced users profit from the presentation of the latest news put in context. And, of course, we will answer questions. Whoever of us is not

  [Read more...]
Searching data in NotOnlyMySQL databases: a rich query language?
Employee +2 Vote Up -0Vote Down

A popular NoSQL store claims to have a rich query language. A powerful, portable query language is most important. If not powerful, you have to send multiple commands to fetch the desired data. In the worst case a program must be written which contraditcs the idea of a query language. If not portable, you are back to the 1960th and vendor lock-in. Developing portable applications is impossible. Stepping up from one database to another means rewriting your application. SQL does not have any of these issues. Plus, it is widely known which means low training costs and high understanding of its properties.

SQL - a query language for the relational model

It may seem odd to compare a query language for the relational model (SQL) with access methods of a document store. However, the NoSQL store compared with explains how to map their

  [Read more...]
Searching data in NotOnlyMySQL databases: two extremes
Employee +5 Vote Up -0Vote Down

MySQL and NoSQL go together. In the data center, that’s a known. Inside MySQL, that’s lesser known. MySQL 5.6 is both a puristic key value store and a full-fledged relational database. MySQL could also be developed into a schema free document store supporting MapReduce as a search method. With regards to searching those are the extremes. What search methods should a NotOnlyMySQL offer? The extremes first.

Thanks, MySQL. Losing a key is no nightmare!

Memcached is a high performance key value store for volatile data. Memcached is part of a classic scaling story: first, we buy a bigger machine for MySQL, then we try replication, then we cache database results in

  [Read more...]
PHP MySQL persistent, cached, pooled connection - for how long?
Employee +2 Vote Up -0Vote Down

It is a common question: what is the lifespan of a persistent, pooled, cached, however-you-call-it PHP MySQL connection? What about CGI, FastCGI, web server module? The answer is always the same. Standard I/O file handles, including socket connections, are bound to processes. The lifespan of a persistent, pooled, cached, however-it-is-to-be-called-in-context-x connection is that of the PHP process. Depending on the web server deployment model, a PHP process handles one or multiple web requests. Read on only if puzzled.

A process

Do you have any colleguages around? Please, ask them for "APUX". In case of no reply, get a copy and study. To become a master PHP developer you will have to master todays frameworks (Zend Framework, Symfony, Dojo, …) and yesterdays. Yesterdays framework is still dominant. Yesterdays framework is

  [Read more...]
New plugin: Connection multiplexing with PHP and MySQL
Employee +4 Vote Up -0Vote Down

Here comes the next open source mysqlnd plugin prototype: PECL/mysqlnd_mux. The PHP mysqlnd connection multiplexing plugin shares a connection to MySQL between multiple user connection handles. Connection multiplexing aims to reduce connect time and lowers the total number of concurrenly opened connections. The first is primarily a benefit for the client, the latter is great for the server. Multiplexing may be most desired in environments with many short-lived connections and weak client-side pooling - such as a PHP web application.


PHP mysqlnd connection multiplexing plugin . More about

  [Read more...]
Non-blocking INSERT with mysqlnd
Employee +0 Vote Up -0Vote Down

An INSERT does not delay me much. At least, it does not necessarily block a PHP MySQL script immediately. The asynchronous query feature of the mysqlnd library helps out. A walkthrough what mysqlnd can do today and could do in theory.

Traditional synchronous API

$ret = $handle1->query("INSERT ...");
$ret = $handle1->query("INSERT ...");

By default, mysqli_query() is a blocking API call. It sends the query to MySQL and waits for MySQL to reply. Let’s see what happens in general when executing two INSERT statements. PHP offers no parallel processing language primitives such as threads and thus, the two INSERT statements run in a serial fashion. Execution times add up.

Synchronous, blocking API, single connection Connect  [Read more...]
PECL/mysqlnd_ms 1.4: A failover standby using weighted/prioritized load balancing
Employee +3 Vote Up -0Vote Down

Some people speak incredibly fast, others make you fall asleep listening. If you have a heterogenous MySQL cluster with differently sized machines, some will be able to answer questions faster than others. PECL/mysqlnd_ms 1.4, the PHP MySQL replication plugin, allows you to assign more requests to the faster ones than to the others. Cluster nodes can be given a weight, a priority for load balancing. For example, a node given a weight of two will get twice as many requests in the average as a node assigned the default weight of one.

Load Balancing adaptive to hardware size

The mysqlnd replication plugin features round robin and random load balancing. Random can be combined with a sticky option which means the
plugin picks a random node at the beginning of

  [Read more...]
PECL/mysqlnd_ms 1.4 - Load? MySQL? Clustering! Balancing!
Employee +2 Vote Up -0Vote Down

Clustering databases is a mainstream technology. MySQL PHP users can take many approaches to load balancing, pooling and redirection. PECL/mysqlnd_ms is one solution. How does it handle transactions? Transactions are a challenge for every MySQL load balancer.

MySQL? Load? Clustering! Balancing! PECL/mysqlnd_ms 1.4
View more presentations from Ulf Wendel

Load Balancer and transactions

A load balancer must not switch from one cluster node to another in the middle of a transactions. Unfortunately, the MySQL Protocol


  [Read more...]
PoC: HTTP, JSON, JavaScript, Map&Reduce built-in to MySQL
Employee +6 Vote Up -0Vote Down

What if MySQL had an HTTP interface and would reply JSON? What if MySQL had server-side JavaScript? What if you could store JSON documents in MySQL? And, what if there was Map and Reduce built-in to MySQL? Make it happen, today. A proof of concept MySQL 5.6 daemon plugin presented at IPC Spring 2012.

HTTP, JSON, JavaScript, Map&Reduce built-in to MySQL
View more presentations from Ulf Wendel

Eye-opening community contributions


  [Read more...]
Some throttling for PECL/mysqlnd_ms 1.4
Employee +3 Vote Up -0Vote Down

Users of MySQL Replication sometimes throttle client requests to give slaves time to catch up to the master. PECL/mysqlnd_ms 1.4, the current development version, features some throttling through the quality-of-service filter and global transaction identifier (GTID). Both the plugins client-side GTID emulation and the MySQL 5.6 built-in GTID feature can be used to slow down PHP MySQL requests, if wanted.

How its done

The replication plugin has a neat feature called quality-of-service filter. If, for example, the quality of service you need from a MySQL Replication cluster is "read your writes",

  [Read more...]
PECL/mysqlnd_ms 1.4 = charset pitfalls solved
Employee +0 Vote Up -0Vote Down

Tweaking is the motto - what an easy release PECL/mysqlnd_ms 1.4 will be! The first tweak for the next stable version of the mysqlnd replication and load balancing plugin solves pitfalls around charsets. String escaping now works on lazy connection handles (default) prior to establishing a connection to MySQL. A new server_charset setting has been introduced for this. The way it works also prevents you from the risk of using a different charset for escaping than used later on for your connection.

Lazy connections and server_charset

PECL/mysqlnd_ms is a load balancer. A users connection handle can point to different nodes of a replication cluster over time. For example, if using MySQL Replication, the connection handle may point to the master for running writes and, later on, to one of

  [Read more...]
Slides: MySQL 5.6 Global Transaction Identifier and PECL/mysqlnd_ms for session consistency
Employee +4 Vote Up -0Vote Down

Why do we have to bother about built-in GTID support in MySQL 5.6 at all? Sure, it is a tremendous step forward for a lazy primary copy system like MySQL Replication. Period. GTIDs make server-side failover easier (slides). And, load balancer, including PECL/mysqlnd_ms as an example of a driver integrated load balancer, can use them to provide session consistency. Please, see the slides. But…

MySQL 5.6 Global Transaction IDs - Use case: (session) consistency
View more

  [Read more...]
Slides: MySQL 5.6 Global Transaction Identifier and PECL/mysqlnd_ms for failover
Employee +4 Vote Up -0Vote Down

MySQL 5.6 Global Transaction Identifier - Use case: Failover
View more presentations on PHP and MySQL

The long lasting MySQL replication failover issue is cured. MySQL 5.6 makes master failover easy, PECL/mysqlnd_ms assists with the client/connection failover. Compared to the past this is a significant step towards improving MySQL replication cluster availability, eleminating the need to use 3rd party tools in many cases. The slides illustrate the basic idea,


  [Read more...]
PECL/mysqlnd_ms: MySQL 5.6.4-m8+ global transaction identifier feature supported
Employee +5 Vote Up -0Vote Down

MySQL Replication is sometimes critizied for being asynchronous and having slaves that lag behind. True! However, sometimes slaves can be used safely and reliably for read-your-writes. Its easy for PHP MySQL users. All the magic is in the driver. As of yesterday, the development version of PECL/mysqlnd_ms 1.3.0-alpha supports not only a client-side global transaction ID emulation but also the global transaction identifier feature of MySQL 5.6.4-m8.

Read-your-writes (session consistency) with MySQL Replication

A global transaction identifier can be understood as a sequence number for a transaction. The sequence number is incremented whenever a write transaction is performed on a MySQL replication master. Slaves replicate the

  [Read more...]
PECL/mysqlnd_qc: table pattern based query caching
Employee +1 Vote Up -0Vote Down

Cache all queries which match a schema pattern is one of the few visible feature additions to PECL/mysqlnd_qc 1.1, the client-side query cache plugin for the mysqlnd library. As usual, this client-side cache is mostly transparent, works with all PHP MySQL APIs (mysql, mysqli, PDO_MySQL) and, of course, supports various storage backends including Memcache, process memory, SQLite, user-defined and more. Please, find details in the quickstart.

Setting a cache condition

Caching only selected queries is something that could be done in 1.0 already, for example, using a callback. What’s new is that filtering is built-in to 1.1. The new feature is straigth forward to use.

  [Read more...]
PECL/mysqlnd_ms: faster slave reads
Employee +2 Vote Up -0Vote Down

Why read stale data from an asynchronous MySQL replica (slave)? Fetch it from a local cache instead! Good for the clusters overall load, good for your applications performance. And, possible with PECL/mysqlnd_ms 1.3, the replication and load balancing plugin for PHP MySQL users.

The idea is simple

Any application using asynchronous MySQL replication must be capable of handling stale results read from a slave (replica) that is lagging behind the master. The quality of service that the application needs from the database cluster is low. If an application explicitly states the minimum service quality it needs, the underlying systems can adopt to it. That’s a cool thing, because the underlying systems don’t need to do more work than necessary. In the case of a PHP MySQL user, the first underlying system is the

  [Read more...]
PECL/mysqlnd_qc: query cache statistics log
Employee +0 Vote Up -0Vote Down

Is it worth the efforts to cache the results of a MySQL query at the client? In most cases the answer is: try it, measure it! Install the development version of the mysqlnd query cache plugin, which can be used with PDO_MySQL, mysqli and mysql. Set three PHP directives and find the answer in a log file.

While updating the query cache plugin to support PHP 5.4, the latest versions of APC and Memcached for cache storage, I virtually stumbled upon an undocumented feature I had long forgotten. The plugin can periodically dump statistics into a log file. The plugin collects tons of statistics and query traces to

  [Read more...]
PHP mysqlnd query cache plugin quickstart is online!
Employee +1 Vote Up -0Vote Down

New in the PHP manual: a quickstart for the mysqlnd query cache plugin. PECL/mysqlnd_qc, the mysqlnd query cache plugin, is transparent and ease to use. But, how? Some pointers have been given in assorted presentations, here on my blog and in some, few examples from the manual. Fixed. You can now browse a quickstart to gain a quick overview.

  [Read more...]
PHP mysqli quickstart is online!
Employee +0 Vote Up -0Vote Down

New in the PHP manual: a mysqli quickstart. You are new to PHP but you know how to code, you know SQL, you know relational databases and MySQL? Then, I hope, this is for you. All you need is a quick overview on the concepts? The rest is in the reference section! Here you go.

The quickstart contains:

  [Read more...]
PECL/mysqlnd_*: CCC - cloud, cluster, caching!
Employee +3 Vote Up -0Vote Down

We are giving PECL/mysqlnd_qc a second chance. PECL/mysqlnd_qc is a query cache plugin for mysqlnd. It can cache any query issued by any PHP MySQL extension using storage handler for process memory, APC, Memcache and SQLlite. Its default invalidation strategy is Time to Live (TTL). Using a more sophisticated invalidation strategy is possible. Of course, its transparent to use and inherits all the other advantages of a driver based approach.

Award-winning technology: Oxid loves the query cache
View more presentations on PHP and MySQL

Cloud and Cluster

Albeit quite


  [Read more...]
PHP MySQL documentation updates
Employee +2 Vote Up -0Vote Down

The MySQL part of the PHP reference manual is currently being restructured: new landing and overview page, mysqli quickstart prepared. Ten years ago, there was the mysql extension and that was it. Today, beginners are faced with three MySQL APIs/extensions, two libraries and more than three library plugins. MySQL support by PHP has never been better. But, where to start: web search for tutorials, a book? The results one gets tend to be flawed: outdated, incomplete, flawed… Thus, the update.

A landing and overview page

The MySQL documentation staging server already shows the new landing and overview page "MySQL Drivers and Plugins". Its introduction makes the PHP reference manuals "Vendor Specific Database Extensions" section less cluttered by

  [Read more...]
Load balancing for PHP and MySQL
Employee +4 Vote Up -0Vote Down

A single MySQL server is a single point of failure. A single MySQL server can only be scaled vertically by increasing hardware size, which has its limits. That’s two good reasons to migrate from a single MySQL server to a cluster of MySQL servers. However, in cloudy white christmas times, few appreciate the extra work that using a cluster causes. For example, MySQL connections must be load balanced. Please, find a comparison of different load balancing architectures in the short presentation. Choose the one that’s best for you - maybe it is PECL mysqlnd_ms 1.2, the mysqlnd replication and load balancing plugin…

  [Read more...]
Welcome PECL/mysqlnd_ms 1.2.0-alpha with global transaction ID support
Employee +3 Vote Up -0Vote Down

Christmas time, time for presents! Version 1.2.0-alpha of the free and open source PHP mysqlnd replication and load balancing plugin has been made available on PECL. PECL/mysqlnd_ms makes using any kind of MySQL database cluster easier featuring:

  • Read-write splitting: automatic, SQL hints, can be disabled
  • Load balancing: random, round robin, user defined
  • Fail over
  • Global transaction ID support: client-side emulation
  • Service levels: eventual consistency, session consistency, strong consistency

The last two features are new. The motto/theme of the 1.2 series is: Global Transaction ID injection and quality-of-service concept.

For many years MySQL has adviced PHP

  [Read more...]
PECL/mysqlnd_ms: quality of service filter
Employee +2 Vote Up -0Vote Down

What if your PHP application could tell the mysqlnd library what service quality you need when using a MySQL replication cluster? If you wanted read-your-writes, the driver would select replication nodes for you which can offer it. If you can allow replication lag but no more than three seconds, the driver would select… One function call and you get the service you need. That’s what version 1.2 of PECL/mysqlnd_ms is about.

The quality of service filter

In the world of PECL/mysqlnd_ms, the free and Open Source replication and load balancing plugin for mysqlnd, a so-called filter is responsible for choosing nodes for statement execution. A filter looks at the SQL statement to be executed and picks a capable server.

  [Read more...]
Waiting for table metadata lock and PECL/mysqlnd_ms
Employee +1 Vote Up -0Vote Down

The MySQL administration SQL command SHOW PROCESSLIST may read "Waiting for table metadata lock" in its "State" column for a statement. The statement in question is waiting for another transaction to release a metadata lock. Its a state that may appear when using the global transaction ID injection feature of PECL/mysqlnd_ms 1.2.0-alpha. But only in case of errors and if not using default settings of the plugin. In the worst case, during testing only, I experienced a deadlock with MySQL 5.5.3 or higher which never got resolved automatically.

Provoking metadata lock

Let a transaction update a record in a table. In my specific case it was an failing UPDATE. It failed because the table did not exist. Let a second transaction run a DDL statement, for example, DROP TABLE on the same table.

  [Read more...]
Client (PECL/mysqlnd_ms) load balancing is good!
Employee +1 Vote Up -0Vote Down

Happy hacking!

@Ulf_Wendel

Global transaction ID support for PECL/mysqlnd_ms
Employee +1 Vote Up -0Vote Down

The catchy theme/motto of the PECL/mysqlnd_ms 1.2 release will be Global Transaction ID support. Hidden behind the buzzword are two features. We will allow users to request a certain level of service from the replication cluster (keyword: consistency) and we will do basic global transaction ID injection to help with master failover. Failover refers to the procedure of electing a new master in case of a master failure.

Global Transaction ID support is the 1.2 motto/theme

The two features are somewhat related, thus the theme. In very basic words, the idea of a global transaction ID is to have a sequential number in a table on the master. Whenever a client inserts data, the ID/counter gets incremented. The table is replicated to the slaves. If the master fails, the database administrator checks the slaves to find the one with the

  [Read more...]
Consistency, cloud and the PHP mysqlnd replication plugin
Employee +0 Vote Up -0Vote Down

Elastic, fantastic: click here to add a MySQL replication database cluster to your cloud configuration. Click - yes, we can! Just one little thing, you need to update your application: consistency model changed. Plan for it. Some thoughts for PECL/mysqlnd_ms 1.x, the PHP mysqlnd replication plugin.

Problem: C as in ACID is no more

A MySQL replication cluster is eventual consistent. All writes are to be send to the master. A write request is considered successful once the master has performed it.

MySQL replication cluster Master Slave Slave id = 1 id = NULL id = 2 | set(id = 1) Client 1

Then, the master sends the update to the slave. Slave updates are asynchronous. For a short period, until all slaves have replicated the

  [Read more...]
Showing entries 1 to 30 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.