My So-Called Blog

Giuseppe Maxia’s new article at the MySQL DevZone rightly points out that using partitions doesn’t work by magic; as with any other enhancement, it requires a bit of know-how to realise its value. MySQL 5.1 Partitions in Practice reviews some of the basics and points out a few gotchas (if you want to partition on a DATE or DATETIME column, pay close attention here). He then provides some realistic examples showing how you actually get partition pruning working for your queries and backs it up with some interesting performance figures.

On a related note, I recently reorganised the MySQL Manual’s discussion of functions in partitioning expressions to get rid of the multiple listings (at Giuseppe’s behest). Now there’s a single Supported Functions list, which we hope you’ll find less confusing and more useful.

Comments(1)

I’ve written an article highlighting some recent developments with XML support in MySQL 5.1 and MySQL 6.0. Topics include:

• Outputting MySQL data in XML format; includes a look at the 3^rd-party lib_mysqludf_xql library
• Getting XML into MySQL; includes a discussion of the LOAD XML statement (new in MySQL 6.0)
• The ExtractValue() and UpdateXML() functions (new in MySQL 5.1)
• Security issues, including a look at a little nasty known as “XPath injection”

You can read it at the MySQL Developer Zone.

Alexander Barkov contributed a nifty stored procedure and a very helpful pre-publication review. Thanks, Bar!

Comments(0)

A little over a year since the project to write this book began, the MySQL 5.1 Cluster Certification Study Guide is now at long last available. It covers everything you’ll need to know to pass the Certified MySQL Cluster 5.1 Database Administrator exam, including MySQL Cluster Concepts, Architecture, Configuration, Deployment, NDB Internals basics, High Availability techniques, Security Issues, and more.

(It does not cover MySQL Cluster 5.1 Carrier Grade Edition, for the simple reason that MCCGE features are not part of the official mainline MySQL 5.1 release. However, those features will be part of MySQL 6.0 [yes, MySQL 6.0 is already starting to happen, and there are already some cool new Cluster, Replication, and XML features in the 6.0 tree, but I’m starting to get off-topic here…], and there’s a good chance that there will be a 6.0 version of the certification course and book in a year or so.)

In addition to yours truly, the authors include NDB developer Stewart Smith, Pro MySQL author Mike Kruckenberg, and MySQL training course developer (and former MySQL certification exam developer) Roland Bouman. Roland also helped me edit and index. (Did I mention that this is my first official Editor credit? Yay.)

You can order your copy here, and still have time to read it and then get certified on MySQL 5.1 Cluster before MySQL 5.1 is GA.

Comments(0)

A few days ago, Alexander Barkov pushed some changes to the MySQL 5.1 tree that I’ve been waiting to see for some time — variable support for XPath functions used with ExtractValue() and UpdateXML(). (This was a fix for Bug #26518, BTW.) This will be available in MySQL 5.1.20 (or grab the MySQL 5.1 source from bkbits and build it yourself, if you just can’t wait).

Two slightly different notations are supported, depending on the context, and what sort of checking you want done on the values:

1. If you don’t want or need type checking, prefix the variable name with $@, like this: $@myvar. However, if you do this, and you make a typo, you’re on your own.
2. To make use of strict checking inside routines, leave off the @ sign, like this: $myvar.

This may not seem like a lot, but it greatly expands the usefulness of MySQL 5.1’s XML functions:

• You should now be able to import XML that uses practically any format into MySQL easily.
• This also makes it much easier to filter for specific rows/columns (or filter out unwated ones).
• You can easily do preprocessing with MySQL string functions like CONCAT(), REPLACE(), TRIM(), etc.

I’ve not yet had time to play with this and come up with some good examples for the Manual, but I’m really looking forward to doing so. Keep an eye out for them in the XML Functions section of the Manual.

Comments(1)

Some people have been saying that MySQL will not provide any more binary releases for its Community users, and that from now on you’ll have to build from source or pay up. Say it ain’t so!

It ain’t so.

Yes, it’s true that MySQL 5.0.33-community is a source-only release. However, this does not mean that all future MySQL Community Server releases will be source-only! In fact, we are planning another (probably 5.0.35) Community release in the near future, that will include binaries that you can download from dev.mysql.com/downloads, same as always.

But don’t take my word for it, when you can read for yourself what Kaj Arnö has to say about it.
(Read the article)

Comments(3)

Over the last two days, I’ve deleted about a dozen comments from the MySQL Manual — and received emails from about a dozen friends — all asking the same question: “When will 5.0.26 and/or 5.1.12 be released?”

Here’s how I answer the emails: “Even if I knew for sure… I could tell you, but then I’d have to kill you“.

If you’re looking for a sure source of insider info, maybe you need to look into actually being on the inside. Get yourself one of those jobs, and you’ll know what I do - maybe even more.

All I can say is that 5.0.26 will very definitely happen Real Soon Now™, and 5.1.12 shouldn’t be far behind.

In the meantime… keep watching those mirrors.

Comments(0)

If you’re like me (and, gosh, I know that I am!), then you’ve probably been keeping CNN or BBCWorld bleating in the background waiting for them to dole out the latest bits on the latest member of the Nuclear Club. Fortunately — thanks to a tip from my good friend The Evil System Administrator — I’ve been reminded that there are some truly great minds out there, and one of them has come up with an elegant Endlösung that requires only a coffee can and a photo of Angelina Jolie. Now, that’s minimalist.

The good news: The North Koreans are rank amateurs. I’m sure we can all sleep easier now.

The bad news: I’ve only an empty coffee jar, and the Kill Bill and Kill Bill 2 DVD cases (thanks, Morgan!) with some stills of Uma Thurman and Lucy Liu on the back.

While I happen to think that Lucy makes Angelina look like Goat Chow, I doubt the effect would be the same.

Comments(0)

Ross McFarland recently posted to the Cluster mailing list about a Perl binding that he’s been working on for the NDB API. Ross says that it’s just a proof of concept, and at this point nowhere near complete, but is inviting folks to download it and take it for a spin. I’m not a Perl hacker myself, but I’d be interested in hearing your reaction or if you’re doing something interesting with this.

Of course, if you’re working on your own port of the NDB API to some other language - say, PHP, Python, or (heh) even JavaScript - I’d love to hear about it.

Comments(0)

As I mentioned a few days ago, I’ve been working recently on documenting the Cluster utilities; I’m still working on ndb_config, but should get that into the Manual Real Soon Now™. In the meantime, here’s a tip I received from one of the developers. This generates a connectstring for use with data, SQL, or API nodes, using ndb_config, which you should be able to find in your mysql/bin directory:

ndb_config −−config-file=path/to/config.ini −−query=hostname,portnumber −−fields=: −−rows=, −−type=ndb_mgmd

For this to work, you need to have the Cluster management server running, with a viable config.ini at the location specified by path/to/config.ini.

Hope that someone finds this useful.

Comments(1)

We’ve been getting lots of questions about how MySQL 5.1 optimises queries on partitioned tables. Recently, we added some new information about this to our Internals Manual provided by one of our developers, Sergey Petrunia. (I hope that my edits and formatting haven’t messed up anything — if there are any problems with what’s been published in the Internals Manual, then the fault’s probably mine and not his.)

The new section can be accessed here, and includes coverage of the following topics:

• An overview of how MySQL performs partition pruning
• What partitioning and subpartitioning intervals are, and why they’re important
• Converting WHERE and ON clauses into intervals
• Partition selection
• Where to find partition pruning and selection code in the 5.1 sources

Sergey’s included several helpful examples and diagrams as well. Even if you’re not interested in hacking the source, you might find this material useful for understanding better how MySQL Partitioning works “behind the scenes”, so be sure to check it out.

Comments(0)