Xaprb

Maatkit version 3329 has been released, as per our usual monthly release cycle.

Significant changes in this version include some bug fixes to mk-duplicate-key-checker, and a lot of new features in mk-query-digest. We made some changes to mk-table-checksum and mk-table-sync. We also updated shared code in all tools.

Interesting and significant things to mention this month:

• Thanks to a corporation who sponsored development on certain tools (I haven’t gotten permission to mention their name.)
• The efforts to be more inclusive and participatory (e.g. more truly open-source) have really paid off, and personally I find it very gratifying to see the discussions on the mailing list. And new project members have contributed code and very useful advice! Thanks, and hooray!
• The project wiki is reorganized and much better to use now.

Here’s the changelog:

Changelog for mk-archiver:

2009-03-31: version 1.0.14

   * Send debugging output to STDERR (issue 308).
   * Removed string interpolation from debugging calls (issue 308)
   * Connection options were not read from the [client] section (issue 249).
   * Set connection options immediately after connecting (issue 286).
   * Updates to shared code.

Changelog for mk-audit:

2009-03-31: version 0.9.6

   * Send debugging output to STDERR (issue 308).
   * Removed string interpolation from debugging calls (issue 308).
   * Connection options were not read from the [client] section (issue 249).
   * Set connection options immediately after connecting (issue 286).
   * Updates to shared code.

Changelog for mk-deadlock-logger:

2009-03-31: version 1.0.14

   * Send debugging output to STDERR (issue 308).
   * Removed string interpolation from debugging calls (issue 308).
   * Connection options were not read from the [client] section (issue 249).
   * Set connection options immediately after connecting (issue 286).
   * Updates to shared code.

Changelog for mk-duplicate-key-checker:

2009-03-31: version 1.2.2

   * Send debugging output to STDERR (issue 308).
   * Removed string interpolation from debugging calls (issue 308).
   * Crashed getting size of foreign keys (issue 331).
   * Uppercase column names crashed the tool (issue 306).
   * Connection options were not read from the [client] section (issue 249).
   * Set connection options immediately after connecting (issue 286).
   * Updates to shared code.

Changelog for mk-find:

2009-03-31: version 0.9.15

   * Send debugging output to STDERR (issue 308).
   * Removed string interpolation from debugging calls (issue 308).
   * Connection options were not read from the [client] section (issue 249).
   * Set connection options immediately after connecting (issue 286).
   * Updates to shared code.

Changelog for mk-heartbeat:

2009-03-31: version 1.0.13

   * Send debugging output to STDERR (issue 308).
   * Removed string interpolation from debugging calls (issue 308)
   * Connection options were not read from the [client] section (issue 249).
   * Set connection options immediately after connecting (issue 286).
   * Updates to shared code.

Changelog for mk-parallel-dump:

2009-03-31: version 1.0.14

   * Send debugging output to STDERR (issue 308).
   * Removed string interpolation from debugging calls (issue 308)
   * Connection options were not read from the [client] section (issue 249).
   * Set connection options immediately after connecting (issue 286).
   * Updates to shared code.

Changelog for mk-parallel-restore:

2009-03-31: version 1.0.13

   * Send debugging output to STDERR (issue 308).
   * Removed string interpolation from debugging calls (issue 308)
   * Connection options were not read from the [client] section (issue 249).
   * Set connection options immediately after connecting (issue 286).
   * Updates to shared code.

Changelog for mk-query-digest:

2009-03-31: version 0.9.4

   * Send debugging output to STDERR (issue 308).
   * Crashed trying to take square root of negative number (issue 332).
   * --review-history did not store timestamps correctly (issue 329).
   * --shorten discarded too much information (issue 320).
   * No results when --orderby contained a nonexistent attribute (issue 244).
   * Added --review-history and --create-review-history (issue 194).
   * Removed string interpolation from debugging calls (issue 308).
   * Standard deviation was not for all values (issue 321).
   * Attributes with mostly zero values caused an infinite loop (issue 321).
   * Very large INSERT or REPLACE VALUES() segfaulted (issue 322).
   * Empty Schema attribute was not handled properly (issue 323).
   * Connection options were not read from the [client] section (issue 249).
   * Set connection options immediately after connecting (issue 286).
   * Updates to shared code.

Changelog for mk-query-profiler:

2009-03-31: version 1.1.14

   * Send debugging output to STDERR (issue 308).
   * Removed string interpolation from debugging calls (issue 308).
   * Connection options were not read from the [client] section (issue 249).
   * Set connection options immediately after connecting (issue 286).
   * Updates to shared code.

Changelog for mk-show-grants:

2009-03-31: version 1.0.14

   * Send debugging output to STDERR (issue 308).
   * Removed string interpolation from debugging calls (issue 308).
   * Connection options were not read from the [client] section (issue 249).
   * Set connection options immediately after connecting (issue 286).
   * Updates to shared code.

Changelog for mk-slave-delay:

2009-03-31: version 1.0.12

   * Send debugging output to STDERR (issue 308).
   * Removed string interpolation from debugging calls (issue 308)
   * A MASTER-HOST without binary logging enabled caused a crash (issue 215).
   * Connection options were not read from the [client] section (issue 249).
   * Set connection options immediately after connecting (issue 286).
   * Updates to shared code.

Changelog for mk-slave-find:

2009-03-31: version 1.0.5

   * Send debugging output to STDERR (issue 308).
   * Removed string interpolation from debugging calls (issue 308).
   * Connection options were not read from the [client] section (issue 249).
   * Set connection options immediately after connecting (issue 286).
   * Updates to shared code.

Changelog for mk-slave-move:

2009-03-31: version 0.9.6

   * Send debugging output to STDERR (issue 308).
   * Removed string interpolation from debugging calls (issue 308).
   * Connection options were not read from the [client] section (issue 249).
   * Set connection options immediately after connecting (issue 286).
   * Updates to shared code.

Changelog for mk-slave-prefetch:

2009-03-31: version 1.0.6

   * Send debugging output to STDERR (issue 308).
   * Removed string interpolation from debugging calls (issue 308)
   * Connection options were not read from the [client] section (issue 249).
   * Set connection options immediately after connecting (issue 286).
   * Updates to shared code.

Changelog for mk-slave-restart:

2009-03-31: version 1.0.12

   * Send debugging output to STDERR (issue 308).
   * Removed string interpolation from debugging calls (issue 308)
   * Connection options were not read from the [client] section (issue 249).
   * Set connection options immediately after connecting (issue 286).
   * Updates to shared code.

Changelog for mk-table-checksum:

2009-03-31: version 1.2.4

   * Send debugging output to STDERR (issue 308).
   * --schema was sensitive to the table's AUTO_INCREMENT value (issue 328).
   * Removed string interpolation from debugging calls (issue 308)
   * Connection options were not read from the [client] section (issue 249).
   * Set connection options immediately after connecting (issue 286).
   * Updates to shared code.

Changelog for mk-table-sync:

2009-03-31: version 1.0.14

   * Send debugging output to STDERR (issue 308).
   * Added --ignore-columns option (issue 313).
   * Two NULL column values didn't compare properly w/ Stream/GroupBy (issue 218).
   * Removed string interpolation from debugging calls (issue 308)
   * Connection options were not read from the [client] section (issue 249).
   * Set connection options immediately after connecting (issue 286).
   * Made --where and --replicate mutually exclusive (issue 302).
   * Updates to shared code.

Changelog for mk-visual-explain:

2009-03-31: version 1.0.13

   * Send debugging output to STDERR (issue 308).
   * Removed string interpolation from debugging calls (issue 308).
   * Connection options were not read from the [client] section (issue 249).
   * Set connection options immediately after connecting (issue 286).
   * Updates to shared code.

I really like Dokuwiki. I remember looking at it many years ago (even then, there were way too many wikis) but for some reason my company or project at the time didn’t choose it. Maybe this was the company that decided Sharepoint was better, I don’t remember. (They never did understand why a collection of Word documents was inferior to a wiki.)

I’ve been using it for a while now, and of all the wikis I’ve used, which are quite a few, it is easily the nicest. It has such nice conventions — once you learn what they are, and use them to your advantage, everything gets so easy. Example: being able to name pages with [[brackets in the flow of text]]. And there are many more things; I don’t want to start any heated debates about it, but in my opinion, it hits exactly the right balance between simplicity and power.

SQL and Relational Theory

SQL and Relational Theory How to Write Accurate SQL Code by C. J. Date, O’Reilly 2009. Page count: 266 pages of “real” text, plus hefty appendixes. (Here’s a link to the publisher’s site: SQL and Relational Theory How to Write Accurate SQL Code).

This is a very important book for anyone involved with databases. Before I say why, I need to apologize to Mr. Date. I tech-reviewed part of the book and did not care for it. I am afraid I was quite a curmudgeon in my review comments. So, Mr. Date, if you’re reading this — I want to say I enjoyed the book very much after all.

Back to the topic. This book is a follow-on and replacement to Database in Depth: Relational Theory for Practitioners, which I also own along with some of Mr. Date’s earlier books. However, I found it a much more profitable read than Database in Depth. Mr. Date’s writing is still recognizably his, with lots of digressions, notes, asides, and footnotes. But something’s different. It is much more direct and readable, and I think readers will find it practical and applicable to their real-world problems. (This is Mr. Date’s expressed hope.)

I had a lot of moments while reading the book where I thought yes! He’s said it so clearly! It always bugs me to see those mistakes, and finally here’s a book that can teach people how not to make them.

The book will help the student develop understanding of the relational model, and learn how SQL diverges from the relational model. As the book says, to the extent that SQL is correct and useful, it’s largely because it adheres to the relational model; in the places where it takes a side trip, there be dragons. It’s universal in every discipline, but I believe Mr. Date is the only authority I’ve read who says this about SQL: learn the rules, and when you know them, you know when and why it’s safe to break them. Otherwise you’re a ship without a rudder and compass.

The general format is this: learn the theory of some topic; learn about a relational approach; learn how that maps onto SQL; and learn some rules of thumb for staying away from the dangers caused by SQL’s approach. Here are some of the recommendations, which he has highlighted in bold for easy reference at the appropriate places in the book:

• Since the phrase “null value” is a contradiction in terms, don’t use it; always say just “null” instead.
• Never write SQL code that relies on ordinal positioning.
• Never lie to the system by defining as a key some column combination that you know isn’t irreducible.

Anyone who’s tried to explain NULL to a beginner to SQL, or explain the difference between a key and an index, or fixed a system that broke because it relied on blind inserts and the table definition changed, will see the value of these recommendations. There’s one such recommendation every few pages. This book is packed with them, and every one is well justified and rigorously explained.

That’s not all. Mr. Date teaches the connection between logic and the relational model. Let me tell my own story here for a moment. I didn’t know anything about databases (except a course on relational theory, which I was unable to connect to the “real world” at all) until after I’d left university with a degree in Computer Science. And then I learned SQL, and then sometime later, I put two and two together: all those discrete math courses, all those proofs, all those predicates and propositions, and so on — it was the foundation for the sensible part of relational databases. So eventually it all did come together in my head, and I got a huge kick out of that. Now I cheer whenever I see someone teaching people to think logically about databases.

So I was definitely cheering as I read things like Types are sets of things we can talk about; relations are (true) statements we can make about those things. And then there’s a whole chapter on how to write questions you’d like to ask of a database, transform those into logical expressions, and then transform those in turn into SQL that is correct and will give the right answer.

I don’t expect this book will work for everyone; some people will not have the foundation in either SQL or logic they need to understand it, and so it will be a steep learning curve unless they’re willing to go study some other things as needed. And I think there is room for different approaches to teaching this subject, depending on the reader’s mental orientation (visual, logical, etc). But for those who like the formal logic-based approach, and/or are willing to invest some effort into really understanding, I think the payoffs will be great.