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)

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)

If you’ve been using the Event Scheduler that was introduced in MySQL 5.1.6, there are some changes in the workings of the event_scheduler server variable. These changes are fairly simple — but not trivial — and were just committed to the 5.1 tree a few days ago. They will definitely be part of the official 5.1.12 release — which should be out just about any day now — so you’ll want to be aware of them, and how they’ll affect the behaviour of the Event Scheduler, before you upgrade.

This variable determines whether the Event Scheduler is available, and whether it’s actually running. In MySQL 5.1.12, it can be used in either of two ways:

1. a server option that can be used from the command line (--event-scheduler), or in the my.cnf file (event-scheduler)
2. a global variable event_scheduler that you can get and set within mysql or any other MySQL client.

As a server option, it can take one of three values:

• OFF: The Event Scheduler is available, but the event scheduler thread does not run, and no scheduled events are executed. This is the default state of the Event Scheduler.
• ON: The Event Scheduler is active, and events are executed according to their schedules.
• DISABLED: The Event Scheduler is not available, and cannot be made available without restarting the MySQL Server.

As a global variable, event_scheduler can only be toggled between ON and OFF.

You can read the Event Scheduler’s state from a client app using either of these two queries:

• SELECT @@GLOBAL.event_scheduler; (you can actually just use @@event_scheduler instead of @@GLOBAL.event_scheduler here, but the value is always the same as the global value).
• SHOW VARIABLES LIKE 'event%' ;

You can set it from a MySQL client with the following restrictions:

• The current value must be one of ON or OFF. If the current value is DISABLED, then any attempt to set it from the client fails with an error. In other words — if MySQL was started with the --event-scheduler=DISABLED command-line option, or if event-scheduler=DISABLED is present in my.cnf — then you can’t change event_scheduler at all from mysql (or any other MySQL client).
• You cannot set event_scheduler to DISABLED from a MySQL client, either. You must restart the server, making the change from the command line or in my.cnf before doing so.
• event_scheduler must be set as a global variable. If you attempt to set it as a session variable (either implicitly or explicitly, using SESSION), then the SET statement will fail with an error.
• The previous item implies that you must have the SUPER privilege to change the value of event_scheduler at all, just as with any other global variable in MySQL.

This variable/option is no longer numeric (as in 5.1.11), but rather enumerated. It is possible to use 0 instead of OFF, and 1 instead of ON, but I don’t recommend it. This is because, there’s no numeric alias for DISABLED, and trying to set event_scheduler to any value other than 0 or 1 now causes an error, as an esteemed colleague of mine discovered while I was away on holiday, before I had a chance to update the Manual. The output from the SHOW or SELECT statements shown above will always yield one of the values ON, OFF, or DISABLED…

…As soon as the fix for Bug #22662 — now pending — is merged, that is. Sorry about that, Andrey. ;)

Comments(1)