Posted by Pieter Ennes on October 19th, 2009
Update: Post updated for v0.2, and v0.3…
Hi folks,
While our own migration to IPv6-enabled monitoring is progressing nicely, we are making available our version of some rudimentary MySQL User Defined Functions (UDF’s) that allow you to work with IPv6 addresses in pre-6.0 versions of MySQL.
The currently implemented functions are inet6_pton() and inet6_ntop() to convert IPv6 (and IPv4) addresses between presentation and numeric (or binary) form directly in SQL. The former function converts a readable IP address string to a binary representation of either 4 bytes (IPv4) or 16 bytes (IPv6) long. The latter function does exactly the opposite, as shown in:
mysql> select inet6_ntop(inet6_pton('2001:4860:a005::68'));
+----------------------------------------------+
| inet6_ntop(inet6_pton('2001:4860:a005::68')) |
+----------------------------------------------+
| 2001:4860:a005::68 |
+----------------------------------------------+
1 row in set (0.00 sec)
mysql> select inet6_ntop(inet6_pton('1.2.3.4'));
+-----------------------------------+
| inet6_ntop(inet6_pton('1.2.3.4')) |
+-----------------------------------+
| 1.2.3.4 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select
length(inet6_pton('1.2.3.4')) as ipv4len,
length(inet6_pton('2001:4860:a005::68')) as ipv6len;
+---------+---------+
| ipv4len | ipv6len |
+---------+---------+
| 4 | 16 |
+---------+---------+
1 row in set (0.00 sec)
You can neatly store the output of inet6_pton() in a VARBINARY(16) column (using a VARCHAR(16) would not be wise because because of possible character set conversion problems).
Of course, there are many ways one could store IPv6 addresses in a database, but the method described here seems to work best for us and is unlikely to conflict with any native support for IPv6 that MySQL will implement in future versions. Furthermore, the binary notation seems to work nicely with ranges, so you can use WHERE ip BETWEEN a AND b to do something like:
mysql> select * from log
where ip between inet6_pton('2001:db8::') and inet6_pton('2001:db9::');
to get all IP addresses in the 2001:db8::/32 range.
We’ve also included two address lookup functions that are IPv6 compatible:
mysql> select inet6_lookup('www.watchmouse.com');
+------------------------------------+
| inet6_lookup('www.watchmouse.com') |
+------------------------------------+
| 2001:1938:80:73::2 |
+------------------------------------+
1 row in set (2.08 sec)
mysql> select inet6_rlookup('2a02:f8::ffff:ffe5'), inet6_rlookup('64.128.190.61');
+-------------------------------------+--------------------------------+
| inet6_rlookup('2a02:f8::ffff:ffe5') | inet6_rlookup('64.128.190.61') |
+-------------------------------------+--------------------------------+
| it.watchmouse.com | ny.watchmouse.com |
+-------------------------------------+--------------------------------+
1 row in set (0.07 sec)
As you can see, inet6_lookup takes a host name and converts it to a readable IP address. The reverse lookup function, inet6_rlookup, works on both binary as well as human readable IP addresses (since v0.2).
The inet6_pton() and inet6_ntop() functions should be quite fast, but as each call to inet6_lookup() involves a DNS lookup, the lookup functions can make your queries extremely slow. Make sure you LIMIT your result set to something sensible. In general, I would not recommend to use the lookup functions in production code as I don’t know the impact of these queries on MySQL’s performance.
Once the UDF’s are installed in the MySQL plugin directory, they can be loaded at runtime using:
mysql> CREATE FUNCTION inet6_ntop RETURNS STRING SONAME "mysql_udf_ipv6.so";
mysql> CREATE FUNCTION inet6_pton RETURNS STRING SONAME "mysql_udf_ipv6.so";
mysql> CREATE FUNCTION inet6_mask RETURNS STRING SONAME "mysql_udf_ipv6.so";
mysql> CREATE FUNCTION inet6_lookup RETURNS STRING SONAME "mysql_udf_ipv6.so";
mysql> CREATE FUNCTION inet6_rlookup RETURNS STRING SONAME "mysql_udf_ipv6.so";
UPDATE: As suggested by Matthias in the comments, in version 0.3 I’ve included a mask calculation function that may help you when comparing IP address ranges. You could use it like this:
mysql> select inet6_mask(inet6_pton('2001:db8:1::1'), 64) = inet6_mask(inet6_pton('2001:db8:1::2'), 64) as samenet;
+---------+
| samenet |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
These functions have helped us with our immediate needs for managing IPv6 addresses in our DBMS, and hopefully they can help you too. We have some more improvements in mind, so stay tuned if you’re interested!
The current version of the UDF’s can be downloaded from:
Note: Compilation was tested on Debian etch+lenny, Ubuntu jaunty+karmic, and on FreeBSD. If you get it running on other platforms, send us a patch!
Pieter Ennes
WatchMouse