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 developers to implement all kinds of logic needed to use MySQL …

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. The current production ready 1.1 release has three …

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. The second transaction is now in waiting for the first transaction to release a metadata lock. Yes, the …

Happy hacking!

@Ulf_Wendel

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 hightest global transaction ID. Please find details, for example, in …

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.

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

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

$mysqli = new mysqli("localhost", "root", "", "test");
echo …

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

The MySQL server allows having statements that …

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.

• Automatic read/write …