Book review: Instant InnoDB

Instant Innodb, by Matt Reid

This book does a good job of explaining the InnoDB internals. I have found particularly useful the section where it describe in detail all the server variables affecting InnoDB. Although these variables are also in the MySQL manual, some of them have never been explained to me as thoroughly as this book as done.

The title claims that it is a InnoDB reference. If is more than that, as the reference part id covered in three chapters. The rest of the book gives useful advice on maintenance, monitoring, and troubleshooting.

Book review: MySQL 5.1 plugin development

MySQL 5.1 Plugin Development, by Sergei Golubchik and Andrew Hutchings. Packt Publishing, 2010. Executive summary: Highly recommended. If you want to develop MySQL extensions, buy this book. It's a must, written by two expert professionals who probably know more than anyone else on this matter. The book is full of practical examples explained with the theoretical information necessary to make it stick. This book fills a gap in the world of MySQL documentation. Although the MySQL docs are extensive and thorough, to the point that sometimes you wished that the writers were less verbose and stick more to the topic, when it comes to the plugin architecture, there are still more some unanswered questions. I guess that the ones who have implemented MySQL extensions so far have read the code, more than the documentation.

But, back to the point. The void is filled now, and in such a wonderful way! I have to disclose that I was the reviewer of this book, and while this fact puts me in a conflicting position when it comes to a review, it has also given me inner reason for praise, beyond the experience of a regular reader. The people who have worked with me will know that I am hard to please, and so my review was peppered with suggestions for improvements that, I admit it, made the authors' life quite difficult. I mostly complained that some of the examples proposed for each chapter were not enough useful and interesting. In short, there was not enough incentive for the reader to start coding immediately. I am glad to report that the authors were very responsive, and, rather than being annoyed by my demands, did work enthusiastically at the examples in the book until they became a true gem in this category. This book will surprise you for its practical approach, when it guides you through the examples, until you end up with a working extension.
The first chapter is probably the part that will save many hours of attempts to everyone who wants to build a plugin. In that chapter the authors explain how to build the different types of plugins in several operating systems. If you have ever tried to build a plugin, you will appreciate this chapter.
The book's body covers every type of plugin that you can create in MySQL, from the classic UDF to the more recent storage engine. For each chapter, you will have code ready to compile and try on your own, and the line-by-line explanation of what that code does, and why it was written the way it is.
Whenever the code is less than intuitive, the authors take care of it with additional theoretical and practical explanation, making sure that the reader is fully informed on the topic, and can complete the task effectively.
Summing up, this is not a book for the idle reader. It's a a practical help for the doers, the ones who want to get the job done, and need guidance through the maze of the MySQL plugin protocol.

Update. There is also a review at Slashdot.

A funny recipe.

According to a recent book about MySQL, this is the recipe to convert an IP address into an integer, with the purpose of using it as server-ID.

1. Take for example 10.0.159.22
2. Using a calculator (!!!), convert each of the four numbers to hexadecimal (you get 0a.00.9f.16)
3. Then glue the four hexadecimal numbers together, after removing the dots, and, again with the calculator, convert them to decimal (0a009f16HEX=167812886DEC)
4. Use this number (167812886) as your server ID in the options file.

Brilliant, eh?

Had the authors searched the MySQL manual for "IP address", they would have found the INET_ATON function, which can be used like this:

select inet_aton('10.0.159.22');
+--------------------------+
| inet_aton('10.0.159.22') |
+--------------------------+
|                167812886 |
+--------------------------+

Update.
Of course, if you want to ignore INET_ATON and avoid using a calculator, you can still leverage the server to do something like this:

delimiter //
drop function if exists redundant_inet_aton //
create function redundant_inet_aton(IP varchar(16))
returns bigint
deterministic
begin
    return conv( 
      concat(
        lpad(hex(substring_index(IP, '.', 1)+0),2,'0'),
        lpad(hex(substring_index(
                    substring_index(IP, '.', 2),
                 '.', -1) +0 ),2,'0'), 
        lpad(hex(substring_index(
                    substring_index(IP, '.', 3),
                 '.', -1) +0 ),2,'0'),
        lpad(hex(substring_index(IP, '.', -1) +0),2,'0') 
      ), 
        16,10);
end //
#
# Probably good for an obfuscated code contest
#
delimiter ;

#
# testing the new function
#
select IP, redundant_inet_aton(IP), inet_aton(IP)
    from (
               SELECT '10.0.159.22' AS IP 
        UNION  SELECT '192.168.2.6' 
        UNION  SELECT '127.0.0.1' 
        UNION  SELECT '10.176.1.15' 
        UNION  SELECT '173.194.36.104' 
    ) as t;
+----------------+-------------------------+---------------+
| IP             | redundant_inet_aton(IP) | inet_aton(IP) |
+----------------+-------------------------+---------------+
| 10.0.159.22    |               167812886 |     167812886 |
| 192.168.2.6    |              3232236038 |    3232236038 |
| 127.0.0.1      |              2130706433 |    2130706433 |
| 10.176.1.15    |               179306767 |     179306767 |
| 173.194.36.104 |              2915181672 |    2915181672 |
+----------------+-------------------------+---------------+

Book review : SQL Antipatterns

SQL Antipatterns, by Bill Karwin

I remember that when I finished reading The Lord Of The Rings, I felt a pang of disappointment. "What? Already finished? What am I going to read now? What can give me the same pleasure and sense of accomplishment that these wonderful pages have given me?"
That's how I felt when I came to the last page of SQL Antipatterns. And, no, Bill Karwin doesn't tell imaginary tales from a fictitious world. This book is full of very real and very practical advice, but all the material is presented with such grace and verve that I could not put it down until the very end. I read it cover to cover in just a few hours, and I savored every page.

What is this Antipatterns, anyway? The title may deceive a casual bookshop browser into believing that it's about some philosophical database theory. Digging further, you realize that it's a collection of best practice in database handling, but told from the side of the bad practice. Every chapter starts with the story of a disaster, followed by an explanation of why it happened, and how to recognize the symptoms. Along the way, you learn what people do to fix the problem when they don't understand where exactly the trouble comes from. And then comes the best practice section, where you are told of several ways of fixing the problem for good.
It's a pleasure from the start. When I read the introductory stories, I recognized mistakes that I have witnessed in my past experience and some that I made myself. It was a fascinating sequence of "look at this mess!", "Here is what you shouldn't do", "It happens when ...", and "this is what you should do".
The more I read, the more I wanted to read. And after a few chapters, a pattern appeared among the Antipatterns. The stories are not a casual collection of database mistakes. There is a thread. Most of the stories tell you what happens when you violate the relational theory principles, and the best practice solutions focus on how to apply relational principles in real life.
The relationship between the horror stories and the relational database theory becomes apparent when you get to the appendix. In other books, you can often skip the appendixes as a reminder of something that you know already. Not here. If you skip this one, you will miss a sort of Ellery Queen ending, where the detective puts together all the pieces of the mystery for the amazed audience.

I feel I would do a disservice to Bill if I revealed more about the book. I may have already spoiled some of the surprise by disclosing the inner structure of the book. But certainly I won't give away any of the juicy stories that make this book an invaluable tool for every database professional.
I can only say this. When I read a technical book, I usually find something wrong, or debatable, or some technical solution that I could improve upon. Not in this book. I completely agree with the theory and the practice that is suggested from start to finish. My QA ego suffers at the idea of not having found anything wrong, but this slight disappointment is more than compensated by the pleasure of reading such a beautiful book. Well done, Bill! I am already queuing to buy your next one!

Giving away one copy of MySQL Administration Bible in Madrid

I will be in Madrid on June 18-19, to participate to the Sun Open Communities Forum. I will have two presentations on my own, and I will be a guest speaker during Victor Jimenez's session. There will be some ancillary activities, among which a lunch with the MySQL community and a MySQL workshop.

During one of these activities, I will give away one copy of the MySQL Administrator’s Bible. If you are a MySQL enthusiast and you are in Madrid, that's an extra reason to attend the forum. For the ones who still don't know how good this book is, you can have a look at this Review.
During the forum, there will be an announcement about the MySQL community in Spain.