If you follow my blog or twitter stream you might know I've recently been at Barcelona to attend the PHP Barcelona conference. Conferences are great for exchanging ideas one of the ideas I discussed with Combell's Thijs Feryn: They are a hosting company providing managed MySQL instances to their customers, as such they run multiple MySQL servers and each server serves a few of their customers. Now they have to provide every customer with database credentials, including a host name to connect to. The issue there is that a fixed hostname takes flexibility out of the setup. Say you have db1.example.com and db2.example.com over time you figure out that there are two high load customers on db1 while db2 is mostly idle. …

To automatically create Foreign Key relationships is a typical use case for developers working with the MyISAM storage engine. This has been a popular topic on the Blog and forums so we’re revisiting it here – with a new and improved plugin written in Python.

While the InnoDB storage engine supports foreign keys, MyISAM doesn’t, but developers often pick it for various reasons and leave the application to handle relationships itself. MySQL Workbench can be used to reverse engineer a database to a model to better visualize or maintain it. But since MyISAM doesn’t have foreign keys, databases that use it will be lacking a very important part of it’s structure in the diagrams. You can link the columns manually, using the relationship tool to link columns, but you can also automate that. Databases are usually created so that columns that represent relationships have names that follow some kind of convention or pattern. For …

What I'm up to lately (giving love to some projects):

* Fixing bugs in DBD::mysql, just released 4.015, 4.016, and next 4.017. I had a patch sent yesterday from a user/developer that I want to get out there
* Memcached::libmemcached - 0.4201 version - now using latest libmemcached 0.42. This is the only Perl client that supports binary protocol!

patg@patg-desktop:~/code_dev/perl-libmemcached$ PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/12-set-get-binary.t
t/12-set-get-binary....ok                                                   
All tests successful.
Files=1, …

Wow, 15 years ago, on June 8th 1995, Rasmus announced PHP 1.0. Time for a personal look back:

I can't remember when I first went online. Out from the local BBS systems into the wide open net. It must have been around the same time. But soon I figured out that I needed my own homepage, so I created one, using black fonts on a green (#00ff00) background. I was proud. The only issue: I had no idea how to make it accessible to others, I had the HTML file local on the PC, but well, I still was proud. I was proud since the effect of this HTML with a little bit of JavaScript was way stronger than most of my BASIC stuff I did before.

A bit later, in 1998, a cousin brought a magazine about writing Perl CGI applications which caught my attention so I started learning about CGI and Perl and all the …

Everybody working on Unix or in the database world stumbles over Oracle Berkeley DB every now and then. DB is an Open Source embedded database used by applications like OpenLDAP or Postfix. Traditionally it followed mostly a key-value access pattern. Now what caught my attention was the fact that the recently released DB 5.0 provides an SQLite-like C API with the promise of providing better concurrency and performance than regular SQLite. Time to give it a shot.

So I grabbed the source distribution, checked the documentation and saw that I shall use the …

In a couple of recent consulting cases, I needed a tool to analyze how a log of queries accesses indexes and tables in the database, specifically, to find out which indexes are not used. I initially hacked together something similar to Daniel Nichter’s mysqlidxchk, but using the framework provided by Maatkit, which gave me a pretty good start right out of the box. This was useful in the very tight time constraints I was under, but was not a complete solution. Alas, I could not use anything like Percona’s enhancements for finding unused indexes.

So, in response to another consultant’s customer request (and sponsorship — thank you!) I spent more time actually writing a real tool in the Maatkit style, with full tests and all the rest of the usual goodies. The resulting …

Maatkit does more than just MySQL. I’ve just committed a new version of mk-query-digest, a powerful log analysis tool, with support for Posgtres logs, in both syslog and stderr format. I’m hoping that people will give this a spin in the real world. I have lots of test cases, but that’s never enough; I’m looking for people to crunch their logs and let me know if anything breaks.

A brief tutorial:

# Get it
$ wget http://www.maatkit.org/trunk/mk-query-digest

# Run it
$ perl mk-query-digest --type pglog /path/to/log/file

# Learn about it (search for the string "pglog")
$ perldoc mk-query-digest

I’m going to close comments on this blog post so I don’t get bug reports in the comments. If you have feedback, please post it to the Maatkit mailing list, or …

If there’s one thing that will always make me angry, it’s people that should not be editing my code going and editing my code. If you want to change something on the server and you have sudo privileges please let the real admin know beforehand. I don’t mind people improving processes or scripts but if [...]

A community member recently noted, that it takes quite a long time to compile MySQL Workbench. So he started wondering about how big the project actually is and asked for the Lines Of Code we have in our MySQL Workbench 5.2 repository.

We did not have this information at hand and therefore Alfredo ran some scripts during the weekend and generated this nice breakdown.

As you can see, we almost have 700k lines of code to maintain. Given that the MySQL Server itself has about 900k lines of code this is a pretty decent number I think, especially for a small team of 7.

Every once in a while, I find myself needing to round a number up to the nearest even power of 10, or round a time interval to the nearest quarter of an hour, or something like that. This is actually quite simple, but for some reason I always find myself confused about how to do it. I have to reason it out all over again, instead of just remembering how to do it. Perhaps writing this blog post will help me remember next time.

The basic idea for rounding to whole multiples is to divide the number, losing precision. Then round, floor, or ceiling the resulting number, and multiply to get back to the original magnitude. For rounding to fractions, reverse the process: multiply, round and divide again.

This actually works for any programming language, not just SQL. But I find myself doing it in SQL most often.

Here’s an example of how to turn a year into a decade:

mysql> SELECT FLOOR(YEAR(NOW()) / 10) * 10 AS …