Thursday, September 30, 2010

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 |
+----------------+-------------------------+---------------+

7 comments:

Jeremy Cole said...

Awesome. It could've been worse, I suppose -- they could've proposed an method using multiplication on each octet, manually...

Gerry Narvaja said...

Agree with Jeremy, however multiplying the octects would've save the conversion to hex and back.

I believe the formula should go under the "useless cat" category ( https://secure.wikimedia.org/wikipedia/en/wiki/Useless_use_of_cat#Useless_use_of_cat )

Daniƫl van Eeden said...

Pity there is not yet an INET_ATON6/INET_NTOA6 for IPv6... so stored procedures are still needed for IPv6 users here...

Darren Cassar said...

Purely a case of RTFM :) .... I agree with Daniel, mysql needs to get support for ipv6

Simon Yiu said...

Dear Master Giuseppe Maxia, can you send me the crosstab_library.zip@Pivot tables in MySQL 5 in 06, 2006,cos I cannot be dl from the net, thx

Giuseppe Maxia said...

@Simon,
Look at http://datacharmer.org/downloads/crosstab_library.zip

Unknown said...

Awesome. It could've been worse, I suppose -- they could've proposed an method using multiplication on each octet, manually...
i like this book