My RSS » Items by Ulf Wendel

Feeds

100883 items (271 unread) in 22 feeds

CNN

• CNN.com - U.S.
• CNN.com - Technology
• CNN.com - Science & Space

MSNBC (15 unread)

• MSNBC.com: U.S. News (10 unread)
• MSNBC.com: Business
• MSNBC.com: Technology & Science (5 unread)

PHP (62 unread)

• Planet PHP (1 unread)
• Nettuts+ (61 unread)

Deals (157 unread)

• Techbargains.com (43 unread)
• dealnews - 20 most recent deals. (71 unread)
• xpBargains.com (18 unread)
• GottaDeal.com - Why Pay Retail? (25 unread)

Tech

• broadbandreports.com
• BetaNews.Com

Web Development (35 unread)

• Dev Shed - The Open Source Web Development Site
• Dev Articles - Programming Help and Tutorials for all development technologies (35 unread)
• developer.com

CNN Money

• CNNMoney.com
• CNNMoney.com - Economy News
• CNNMoney.com - Personal Finance

Frugal Blogs (2 unread)

• Frugal Dad
• The Simple Dollar (2 unread)

Items by Ulf Wendel

Planet PHP

• 

  Some throttling for PECL/mysqlnd_ms 1.4

  Posted: May 15th, 2012, 7:11am MDT by Ulf Wendel

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

  PECL/mysqlnd_ms 1.4 = charset pitfalls solved

  Posted: April 27th, 2012, 9:41am MDT by Ulf Wendel

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

  Slides: MySQL 5.6 Global Transaction Identifier and PECL/mysqlnd_ms for session consistency

  Posted: March 27th, 2012, 8:19am MDT by Ulf Wendel

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

  PECL/mysqlnd_qc: table pattern based query caching

  Posted: March 2nd, 2012, 6:56am MST by Ulf Wendel

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

  PECL/mysqlnd_qc: query cache statistics log

  Posted: January 24th, 2012, 10:20am MST by Ulf Wendel

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

  PHP mysqli quickstart is online!

  Posted: January 12th, 2012, 11:31am MST by Ulf Wendel

  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:

  • Dual procedural and object-oriented interface:
    something pioneerd by MySQL when PHP learned OOP at 5.0 times.
  • Connections: how, options, persistent/pooled connections.
  • Executing statements: buffered, unbuffered, impact of MySQL Client Server protocol flavour used
  • Prepared Statements: what, how, pros and cons
  • Stored Procedures: how, parameters, prepared statements
  • Multiple Statements: what, security considerations
  • API support for transactions
  • Metadata

  In case you prefer listening over reading there is a PHP MySQL web seminar series on PHP for you (hint: search "On Demand" for more). To please everybody, we are giving a webinar summary in german as well on 18.01.2012, register now.

  Please, note that I am inpatient and linking to the PHP documentation teams server: [docs.php.net] . The php.net mirrors will need a while to catch up.

  Happy hacking!

  @Ulf_Wendel

• 

  PHP MySQL documentation updates

  Posted: December 23rd, 2011, 5:36am MST by Ulf Wendel

  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 grouping all MySQL information under this new overview page.

  Under the address [docs.php.net] (later on php.net/mysql may work) users find an overview of the MySQL PHP drivers:

  • Terminology overview
  • Choosing an API
  • Choosing a library

  After an overview on terminology the three PHP MySQL APIs/extensions mysql, mysqli and PDO_MySQL are compared. A comprehensive feature matrix helps to get started. Next, it is explained how the extensions connect to MySQL by help of a client library. Again, a comprehensive feature matrix compares the two choices, the MySQL Client Library (AKA libmysql) and mysqlnd.

  The landing page continues with links to the mysql, mysqli APIs/extensions, followed by mysqlnd and its various plugins.

  Hands-on mysqli quickstart prepared

  Many years ago when Zak Greant and Georg Richter developed the mysqli extension they made sure to have examples for each and every function. That’s fantastic but meanwhile there is a logical and good trend to include hands-on quickstarts and background concepts information to PHP manual. We all know, a software is worth nothing without adequate easy to use documentation.The documentation must cover the needs of beginners, intermediate users and experts who need little more than reference materials. A bit like with the ~70 pages of PECL/mysqlnd_ms documentation, which begins with a quickstart and a concepts section before the reference section.

  Therefore, I’ve written a mysqli quickstart. You have seen some excerpts of it already in my blog:

  • Using MySQL with PHP mysqli: Connections, Options, Pooling
  • Executing MySQL queries with PHP mysqli
  • Using MySQL prepared statements with PHP mysqli
  • Using MySQL multiple statements with PHP mysqli
  • Using MySQL stored procedures with PHP mysqli

  I hope Philip will be able to review and add the quickstart to the mysqli extension documentation soon. That’s it? Come on, its not yet christmas eve and, do we really need chrismas for making presents…

  If you would like to comment on the new contents, please do so on the php.net documentation mailing lists. That’s the appropriate forum for discussions on the PHP reference manual. I’m sure there are still some edges in the new materials that need to be cut off.

  .oO( If Andrey and I continued our current pace, where would we be next christmas… )

  Happy hacking, happy holidays!

  @Ulf_Wendel

• 

  Welcome PECL/mysqlnd_ms 1.2.0-alpha with global transaction ID support

  Posted: December 13th, 2011, 4:45pm MST by Ulf Wendel

  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, ...
• 

  PECL/mysqlnd_ms: quality of service filter

  Posted: December 5th, 2011, 3:52am MST by Ulf Wendel

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

  Waiting for table metadata lock and PECL/mysqlnd_ms

  Posted: November 29th, 2011, 1:00pm MST by Ulf Wendel

  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 second transaction is waiting for a lock hold on a non-existing table, if you are using MySQL 5.5.3 or higher… The SQL is syntactically correct, thus the lock is acquired.
  

  $link = new mysqli($host, $user, $passwd, $db, $port, $socket);
  $link->autocommit(false);
  $link->query(\"UPDATE foo SET bar = bar + 1\");
  	
  $link2 = new mysqli($host, $user, $passwd, $db, $port, $socket);
  $link2->query(\"DROP TABLE IF EXISTS foo\");
  

  mysql> show processlist;
  +------+------+--------------------+------+---------+------+---------------------------------+--------------------------+
  | Id   | User | Host               | db   | Command | Time | State                           | Info                     |
  +------+------+--------------------+------+---------+------+---------------------------------+--------------------------+
  | 5754 | root | 192.168.78.1:44307 | test | Sleep   |  160 |                                 | NULL                     |
  | 5755 | root | 192.168.78.1:44308 | test | Query   |  160 | Waiting for table metadata lock | DROP TABLE IF EXISTS foo |
  | 5756 | root | localhost:60751    | test | Query   |    0 | NULL                            | show processlist         |
  +------+------+--------------------+------+---------+------+---------------------------------+--------------------------+
  

  This behaviour is different from previous MySQL versions and it is documented in the MySQL 5.5.3 Changes as an incompatible change: A table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends. . The change fixed issues with the binary log. So far, so good - though surprising.

  Under normal circumstances the server will release the lock when the clients commit their transactions or disconnect. If they don’t end their transactions nicely but die, the wait_timeout should detect failing TCP/IP clients and solve the deadlock.

  How its related to the plugin

  What I was doing here was simlating a failure of the plugin to inject a global transaction ID because the transaction table was not set up. The injection, the UPDATE fails, when the client is not in autocommit mode. My initial idea was that the plugin would then report and error to the application and the application could create the transaction id table, if it wanted. In my test I tried catching the error and did DROP TABLE IF EXISTS trx_table, CREATE TABLE trx_table to recreate the table. My test timed out. And, that day it was time for me to call it a day.

  The other day, I checked the processlist and saw the deadlock. My PHP test had stopped and disconnected but the deadlock still existed and borked numerous other test runs. It was necessary to KILL the sleeping lock holder manually.

  Error handling in the plugin

  The plugin offers two ways of dealing with injection errors. By default the injection error is ignored and the function called by the application is executed nonetheless. If, for example, we are running in transaction mode (auto commit off), the user calls the commit() function and injection of the UPDATE statement for increasing the transaction counter fails, the plugin commits anyway. The transaction ends. Other transactions should not be blocked because the metadata lock on the transaction table is released by ending the transaction. No matter if using MySQL 5.5.3 or earlier.

  user_commit()
     if (!inject_trx_id() && report_error)
         return false;
  	
    return commit()
  

  Optionally, users can tell the plugin to bubble up the injection error. If so, users should roll back the current transaction calling rollback() to prevent the metadata lock issue. Alternatively, you can check for the error code and try to detect cases when its required to recreate the transaction id table.

  Moral

  As said, if you go for plugin default settings, no locking issue but possibly gaps and holes in your transaction id table. If you know what you do, if you can adapt your application and code changes are possible, you can manually handle the error and recreate the transaction id table on demand.