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!