Mats Lindh

How to unit-test code that interacts with a database appeared on the blog of Baron Schwartz, and to be really boring, I agree with what he’s writing. Unit-testing database connectivity and storage is not hard. If it is, it might be a good time to redo that architecture you’ve been talking about.

An important point that Baron mentions is that you _NEVER_ _EVER_ run your tests on your production servers. That will of course be disastrous, as your tests needs a predefined state of the database to be valid for testing. The solution I’ve been using to handle this, is to always set up my environment to use another database when doing the tests. This way, you’ll never end up with running the tests on a live database by accident. I handle this in my AllTests.php file, where the test suites and shared fixtures are set up. We dump the contents of the developer database (databasename), create a new database (databasename_test) and insert all the current table structures and indexes. This way we get an accurate copy of the table definitions currently defined by the developer (so that we don’t run the tests against an old set of tables), and we test that the code works as it should with the active definitions.

The simplest way to do this, is to use mysqldump and mysql through a call to exec. If you’re not in a trusted environment, please, please, please add the appropriate shell argument escape commands. It can however be argued that if you’re allowing random people to change your database login information, you probably have bigger problems than doing unit testing..

It would be very interesting to get more information about which measures Baron advocates for detecting a production system. We have configuration settings for our applications which also defines if this is a development or production system, in addition to the fact that our testing code only touches databases which end in _test.

As Monty asked for help with translations of the current strings available in Drizzle on his blog yesterday, I sat down a couple of hours yesterday and a couple of hours today to at least attempt to contribute something to the project. As my primary language is Norwegian and I have some experience writing, I decided to tackle the Norwegian (Bokmål, not Nynorsk) translation of Drizzle. I’ve currently finished the 358 available messages, but I’d really appreciate it if someone spent a couple of minutes / hours to read through them and confirm that my assumptions are sane.

The most troubling part when it comes to definitions are the issue of MySQL/Drizzle’s ‘relay log’ which I translated into ‘replikasjonslogg’ - which mainly means “replication log”. This sounds much better in Norwegian, but suddenly the code mentioned both a “replication log” and a “relay log”. I tried finding out what the semantic difference in MySQL were, but were unable to grok anything from the MySQL manual or through a Google search. If anyone has any advice here, it’d be very appreciated. I also made a few notes of where there are obvious errors in the original english strings:

Error on close of '%'s (Errcode: %d)
 - Located in mysys/errors.c:28 

Errcode:
Can't read value for symlink '%s' (Error %d)
 - Located in mysys/errors.c:47
Can't create symlink '%s' pointing at '%s' (Error %d)
 - Located in mysys/errors.c:48
Copy text Error on realpath() on '%s' (Error %d)
 - Located in mysys/errors.c:49 

%*s(Defaults to on; use --skip-%s to disable.)
 - Missing space
 - Located in mysys/my_getopt.c:1170 

The event could not be processed no other hanlder error happened
 - hanlder
 -  Located in mysys/my_handler_errors.h:118 

SSL information in the master info file ('%s') are ignored because this MySQL slave was compiled without SSL support.
 - MySQL
 - Located in drizzled/rpl_mi.cc:276 

Slave I/O thread killed while waitnig to reconnect after a failed registration on master
 - waitnig
 - Located in drizzled/slave.cc:90 

Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog'..
 - mysqlbinlog
 - Located in drizzled/slave.cc:1864 

Found wrong key definition in %s; Please do "ALTER TABLE '%s' FORCE " to fix it!
 - feil spaceplass
 - Located in drizzled/table.cc:1162 

Table '%-.64s' was created with a different version of MySQL and cannot be read
 - MySQL
 - Located in drizzled/table.cc:1818

I could probably submit a patch for this, but seeing as the source is very much in flux these days, I think I’ll wait until it settles down a bit — unless someone is interesting in reviewing and committing an “unimportant” patch at this stage.

BTW: Launchpad worked great for doing translations, so I’m going to look into using gettext and Launchpad for doing translations for pwned.no and my other services in the future.

To finally be able to close my now-ready-to-be-archived Firefox-window, I’m rounding up the three other posts I were going to post about in one single batch here:

Ulf Wendel has a post up about PDO_MYSQLND: The new features of PDO_MYSQL in PHP 5.3. Besides being yet another introduction to how MYSQLND differs from the regular libmysqlclient, Ulf writes in detail about how mysqlnd brings other speedups to PDO in general, by allowing the drivers to return zvals directly. This allows the driver to return data without requiring an explicit copy by the overlying architecture. Interesting stuff and well worth a read for anyone, regardless if you actually know what a zval is.

Nicklas Westerlund has a post about MySQL Back to Basics: Analyze, Check, Optimize, and Repair on the pythian blog, featuring a overview of the useful - and abused - methods of rescuing and keeping your data intact. Do regular and good backups. It’s as easy as that. This might however help when you’re in a hurry or needs to fix a corruption that has occured. Read it.

The last item on today’s list is Sphinx - a free open-source full-text search engine. Sphinx uses it’s own indexing and retrieval system, while Solr is built on top of Lucene. Haven’t had much time to play with it yet, but it’s worth checking out. A native PHP module has also popped up (and that’s where I read about it just now), so if you need a fast and native PHP interface to a full search engine without blowing the big bucks, this may be what you’re looking for.

As noted I recently went for a total of five days of vacation. In the meantime, the intarwebs of blogospheres and the seas of web 2.0 exploded with posts and discussion over Drizzle. Drizzle is a fork of MySQL with the intention of making things more suitable for cloud computing and the regular web use cases. I’m actually quite intrigued by this, and I really look forward to getting some more time to read up on the issues at hand. It will be interesting to see how things compare to CouchDB, Amazon S3, Solr and other service that take a different road than regular relationial databases. Interesting. Anyways, here’s the posts I’d suggest checking out to get better and more usable information about Drizzle:

• Monty’s “What If”
• Brian Aker’s “What If” - The second of the two “What If” posts that are required reading. This is where the idea was formed. The reasoning behind doing what people are doing.
• Where Drizzle fits in for me by Brian Moon
• Is Drizzle Good for MySQL?
• Drizzle… Seeing Sun Through The Clouds

I’ll take it for a testdrive as soon as the first public version becomes available, and I’m looking forward to it. Might be fun!

Ulf Wendel is presenting several good articles about the process about implementing the mysqlnd library for PDO as PDO_MYSQLND. I wrote about PDO_MYSQLND when it first was announced, but Ulf has posted two good articles about the implementation of PDO_MYSQLND since then. These articles provide a unique insight into how PDO is built and what challenges lies ahead for PDO2 (.. in addition to the license and NDA debate..)

• PDO_MYSQLND: R[a|u]mbling and a breeze of progress
• PDO_MYSQLND: Prepared Statements, again

Remember that you can always follow the latest developments over at Planet PHP.

php|tek is taking place halfway around half of the world for me, but the first presentations from the conference is beginning to appear online now. The first three presentations are from Brian DeShong and Maggie Nelson:

• Brian DeShong: The Grown-Up Company’s Guide to Development
• Brian DeShong: Robust Batch Processing with PHP
• Maggie Nelson: Keeping Your DB and PHP in Sync

While Brians two presentations were mostly familiar stuff for me, Maggie’s presentation touched something that has troubled me time over and again, and that Christer and me has been looking for a good solution to. We’re currently experiencing the problems described, and I’ve been searching several times for a good tool to generate sqldiffs (and not for the _values_ of most of the tables). I’m waiting eagerly for the first release, and as soon as things are up and running, I’ll look into if there’s anything I can contribute.

Stumbled across a post by Sunny Walia (.. I tried finding your name, but it’s not on your about page, in your title or on the top of your page, so I had to dig through my feedreader) about installing innotop under RedHat. I’ve never heard about innotop before, but after a bit of using the almighty Google (or you could just follow the link in the last part of the linked article), I found the man page for innotop. It’s a tool with an user interface similiar to the regular unix tool top, but for supervising the current state of your innodb tables instead. Several examples of output are given in the man page.

Jan Kneschke has a very interesting post about how you could use replication and MySQL Proxy to mark entries as dirty in memcached. This way you’re able to expire the data from memcached when it actually is updated on the database level, without having to add another level of abstraction in your application. A very novel approach and it’ll be nice to see how this plays out in practice with 5.1.