IP Address conversions

This is the part 2 of converting an IP address to an integer or vice versa. For more details and little introdution see Part 1.

Converting an ip address to integer and back is little straight forward in MySQL compared to SQL Server since SQL Server (2008) doesn’t provide bit shift operators.

In short you can’t do,

SELECT 1<<2 


SELECT 3232261320 -- IP ''
* (256*256*256*256*256*256) -- Left shift 6 bytes
/ (256*256*256*256*256*256*256) -- Right shift 7 bytes to get 2nd octet

One will encounter “Arithmetic overflow error converting expression to data type int”. Even casting each integer to bigint will not solve the problem as the multiplication results in overflow of signed bigint. And there is no unsigned bigint in SQL Server.

Another way to solve this is to do the following in SQL Server by using bit AND. Retain only the byte that is off interest and then right shift the bits appropriately.

#-- IP ''
SELECT (cast(3232261320 as bigint) & cast(4278190080 as bigint))/(256*256*256);
SELECT (cast(3232261320 as bigint) & 16711680)/(256*256);
SELECT (cast(3232261320 as bigint) & 65280)/(256);
SELECT (cast(3232261320 as bigint) & 255);

#- And the output will be

On systems with unsigned bigint you can also do (MySQL)

SELECT cast(3232261320*256*256*256*256 as unsigned)
/ (256*256*256*256*256*256*256) 4th_octet;

SELECT cast(3232261320*256*256*256*256*256*256*256 as unsigned)
/ (256*256*256*256*256*256*256 as unsigned) as 1st_octet;

They will result in outputs 192 and 200 respectively.

With given ip you can simply run the following to get its integer value,

mysql toad>  
SELECT ( ((substring_index(ip, '.',1))<<24)
+ ((substring_index(substring_index(ip, '.', 2), '.', -1))<<16)
+ ((substring_index(substring_index(ip, '.', 3), '.', -1))<<8)
+ ((substring_index(substring_index(ip, '.', 4), '.', -1)<<0))
( SELECT '' as ip
SELECT ip FROM som_ip_table;
) t;


Converting an IP address to an integer and reverse

In dealing with IP address analysis one may required to map IPs to geo location. There are third party applications or software that perform IP to city, state, country mapping and businesses, for example, can make use of them to see where their web traffic is coming from. 

Article covers IPv4 addresses which use 32 bits and similar technique can be used to IPv6 addresses which use 128 bits by using 2 BIGINTs to store (64bits each).

It is easy to handle reasonable load when one is querying for few IPs or few hundred IPs either from simple user interface entering one or more IP addresses or through APIs. In these cases IP addresses can simply be matched in string format without much of performance issue but it can become a performance bottleneck when dealing with millions of rows loaded through ETL process into Data Warehouse environment and reports are run against fact tables with hundreds of millions of rows. Under these circumstances converting IP to an integer will boost the performance substantially.

MySQL provides functions to convert IP string to integer and backwards. For example, inet_aton converts string to a number (integer) and inet_ntoa converts number to integer.

inet_aton and inet_ntoa:

mysql> select inet_aton('');
| inet_aton('') |
| 3232261320 |

mysql> select inet_ntoa(3232261320);
| inet_ntoa(3232261320) |
| |
1 row in set (0.00 sec)

Bit operations:

The functions are simple to use and at the lower level they basically perform bit shifting. In the above example, ‘’ is represented as 11000000 10101000 01100100 11001000 in binary form. For more on binary system or to convert a number from decimal to binary see wiki here.

To convert string IP to integer function inet_aton multiplies 192 by 256*256*256, 168 by 258*256, 100 by 256 and then adding 200 (last octet in the above ip address), that is,

and it is same as 192<<24 i.e., left shift 192 by 24 bits, 168 << 16 and 100 << 8. Bit shifting is a much faster multiplication since CPU can use its register to move the bit in simple operation.

In reverse, one can not simply divide the ip integer (3232261320) to get back the ip address in octet string format.  But one need to appropriately left and right shift the number (or do multiply and then divide) to get the octet that is of interest. To get the most highest valued position (192), shift the bits to right. For example,

11000000 10101000 01100100 11001000 >> 24 (right shift 24 bits) gives you

00000000 00000000 00000000 11000000

Therefore, all the right most 24 bits dropped off the register (cliff) and we are left with 4th octet 11000000 prefixed with 24 zeros.

mysql> SELECT 3232261320>>24 as 4th_octet;
| 4th_octet |
| 192 |
1 row in set (0.00 sec)

By the same logic to get the 3rd octet, we can move the bits 16 times to the right but we will be left with 4th octet as well. Doing

11000000 10101000 01100100 11001000 >>16 will result in

00000000 00000000 11000000 10101000

So we should first shift the bits to left so that the most highest octet (4th octet) drops off the register.  We can do so by shifting left by 8 bits.

mysql> SELECT 3232261320<<8 as 8blsn;  -- 8blsn: 8 bit left shift number
| 8blsn |
| 827458897920 |
1 row in set (0.00 sec)

Instead of getting a smaller number than before we got a larger number! And obviously, right shifting this number by 16bits to right will not give the 3rd octet of IP.   This happens due to fact that MySQL converted int to bigint silently.  The 4th octet moved into 5th octet resulting in appending 8 zeros to the right as below.

11000000 10101000 01100100 11001000 00000000

Since bigint is 8 bytes long one need to shift not by 1 byte (8bits) but 5 bytes (40 bits) to delete the highest octet (#192).

Just with 4 byte (32 bit) left shift the register would look like below.

11000000 10101000 01100100 11001000 00000000 00000000 00000000 00000000

And with 40 bit left shift the register would look like thus removing (#192 – 11000000 ),

10101000 01100100 11001000 00000000 00000000 00000000 00000000 00000000

Now right shift 56 bits to get the 3rd octet (#168).

00000000 00000000 00000000 00000000 00000000 00000000 00000000 10101000

This is same as
(3232261320 *256 *256 *256 *256 *256) / (256 *256 *256 *256 *256 *256 *256)

 mysql> SELECT 3232261320><>56;
| 3232261320<>56 |
| 168 |
1 row in set (0.00 sec)

To get the 2nd octet first left shift the original number 48 bits and then right shift by 56 bits. For the 1st octet left shift by 56 bits both ways.

mysql> SELECT 3232261320<>56;
| 3232261320<>56 |
| 100 |
1 row in set (0.00 sec)

mysql> SELECT 3232261320<>56;
| 3232261320<>56 |
| 200 |
1 row in set (0.00 sec)

With all imperative/procedural languages providing the bit operators (AND, OR, bit shift, etc), one can and should convert the IP string to integer before being loaded to staging. Thus the parsing or conversion can also be easily parallel processed for large loads.

An example in C sharp (C#) is shown below.

// C# program to convert IP address string to an integer.
using System;

namespace IP_addr_to_int
class Program
static void Main(string[] args)
// Infinite loop until user enters control-c.
for (; ; ) {
string ip = "";
Console.Write("Enter ip address: ");
ip = Console.ReadLine();

ulong ipNum = 0;
string[] ipArr = ip.Split('.');

if (ipArr.Length != 4) {
Console.WriteLine("Error. Enter four octet IP address only.");
// Environment.Exit(-1);
for (int ii = ipArr.Length - 1; ii >= 0; ii--)
ulong ipOctet = Convert.ToUInt64(ipArr[3 - ii]);
// ipOctet * 256 * 256 * 256;
if (ii == 3) { ipNum += ipOctet << 24; }
// ipOctet * 256 * 256;
else if (ii == 2) { ipNum += ipOctet << 16; }
// ipOctet * 256;
else if (ii == 1) { ipNum += ipOctet << 8; }
// ipOctet * 1
else { ipNum += ipOctet }
// System.Threading.Thread.Sleep(1000);
(Convert.ToString(ip)+ " converted to integer " + Convert.ToString(ipNum));

For more ways to convert an IP see Part 2  and for tools see Part 3.

NFS Cache – Invisible file issue

I ran into a following situation and it took some time to diagnose the issue and help from couple of folks from DBA and operations team to resolve it.  Here is what happened.

I exported a large data set from MySQL to a file in directory /dir_1/dir_2/exported_file.txt, for example, in an application.  Then after the file was exported the application went on to consume the file by reading it’s content.  Since MySQL OUTFILE (exporting data) doesn’t overwrite a file if the file name already exists, the code would rename the file to *.bak.   See below for pseudo code.

If OUTFILE exists
    Move or Rename OUTFILE to OUTFILE.bak    /* Step 1 */
Run MySQL export to OUTFILE    /*  Step 2 */
Check the error code
Read OUTFILE and parse               /* Step 3 */

When I ran the application, it would sometime create the output file and go on to parse it correctly but many a times it would fail in step 1 throwing an error like “file already exists” when in fact it was not.  Because I had removed the file with ‘rm -f’ before rerunning the program.  Other times it would fail in step 3 indicating that file does not exists even though SQL exported the file successfully in step 2.  I even provided sleep time between each step ranging from 5 to 60 seconds but continued to see the same random behavior.

After spending sometime trying to diagnose what might be going on, ended up debugging NFS caching.  The directory /dir_1 was a mounted file system with NFS caching set to few hundred seconds.  When the application wrote to NFS directory, the write cache was updated but not the OS directory structure (inode). Reducing the parameter setting (actimeo) to lower number, say 30 seconds, will help alliviate the delay. If sys admins are reluctant to change the older mounted system settings, you should get a new mount point with actimeo set (30).   Once these changes were made application was able to run smoothly with the application sleep set to little higher than actimeo timings.  Note, using actimeo sets all of acregmin, acregmax, acdirmin, and acdirmax to the same value. There is no default value. See man pages for more details.