Update: Post updated for v0.2, v0.3, and v0.5. As of v0.5 there is a valid upgrade path towards native IPv6 support in MySQL 5.6; please see below.
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 latest version of the UDF’s is available for download here. Additionally, all code is available via a BitBucket repository on:
http://bitbucket.org/watchmouse/mysql-udf-ipv6/
Note: Compilation was tested on Debian, Ubuntu, and FreeBSD. If you get it running on other platforms, send us a patch!
Pieter Ennes
WatchMouse
PS. I’m happy to note that MySQL finally added native IPv6 support functions in v5.6.3 of their product. The good news is that they chose to use exactly the same method, only using slightly different names. So I’ve now added aliases for the native names in v0.5 of the UDF, creating a valid upgrade path from v5.1+UDF towards the native functions in v5.6.
More information on the native functions can be found here:
http://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_inet6-ntoa
For more information on the aliases, please refer to the README file in the BitBucket repository:
https://bitbucket.org/watchmouse/mysql-udf-ipv6/overview
Tags: inet_ntop, inet_pton, IPv6, mysql, mysql udf
Categories: IPv6, Labs
You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.
October 20th, 2009 at 00:05
that looks great! you guyz are improving a lot. Will try these funtions.
January 5th, 2010 at 00:44
Really nice.
Got it to work on FreeBSD 8.0 after some modification.
January 5th, 2010 at 09:26
Hi Edwin, that’s good to hear! I don’t have any BSD’s at hand currently, so it would be great if you could send a patch with your modifications…
Update: Thanks for the patch! Included in the next version.
March 31st, 2010 at 09:42
Hey, thanks a lot for that functions. I had to extend the database for my company to be able to manage ipv6-addresses. Up to now, I’ve been using bitwise functions (mostly & ) for calculating ip-ranges.
Unfortunately these operators won’t work with the varchar(16) columns filled with that inet6_pton. It might well be, that this is due to MySQL using a BigInt (64 bit) for bitwise operations. Although, even with ipv4 addresses converted by your function and a bitwise AND only return a 0.
Have you got any Idea, how I could handle this?
March 31st, 2010 at 09:56
Matthias, good point, a solution for this was still on my list indeed. I suspect some dedicated functions are needed to do proper bitwise operations.
Would inet6_mask(’1:2:3::3:2:1′, 64); help in your situation? What else would you require?
March 31st, 2010 at 10:06
I think it’s the best to explain my exact task:
I have one table (a) with the columns id, ip and subnet mask.
In another table (b) manage a list of other ip’s.
I now need to find out the id of the range, an entry in table b is in.
Example query:
SELECT a.id, b.ip from a right join b on inet6_mask(a.ip, b.mask) = inet6_mask(b.ip, b.mask)
I think that would work with the function you proposed. Additionally The sql would even look much nicer than the calculations I had to do before.
October 4th, 2010 at 15:27
Thanks a lot for providing this. Here’s some work towards a debian package for it:
http://www.cae.wisc.edu/~bpkroth/src/mysql/mysql-udf-ipv6/
October 4th, 2010 at 16:52
Hi Brian, great work!
I just made a public repository available here: https://bitbucket.org/watchmouse/mysql-udf-ipv6/
I’d be happy to commit your Debian work in the repository if that makes things easier.
October 18th, 2010 at 08:47
please check the repository, there is something wrong of the file mysql_udf_ipv6.c frommysql_udf_ipv6.c@r4:047f168c8550, just like ‘#defi#define min(x, y)’
October 19th, 2010 at 00:31
@baixiang: Thanks for pointing that out. It turned out to be svn->hg import corruption, and should be fixed now.
@Brian: I’ve included your files in the repository now, thanks!
October 29th, 2010 at 23:51
I’ve worked on a Centos/RH spec file that created a rpm file that is working. It’s the first I’ve ever written so I’m sure someone could do a much better job. Until then here it is.
%define _topdir /root/mysql-udf-ipv6
%define name mysql-udf-ipv6
%define release 2
%define version 2.05
%define buildroot %{_topdir}/%{name}-%{version}-root
BuildRoot: %{buildroot}
Summary: MySQL UDF IPv6
License: EUPL, v1.1
Name: %{name}
Version: %{version}
Release: %{release}
Source: tip.tar.gz
Prefix: /usr/lib/mysql
Group: devel
BuildRequires: mysql-devel
%description
This library provides IPv6 inet_ntoa()/inet6_pton() and inet_aton()/inet6_ntop() support as user defined functions for MySQL.
%prep
%setup -q -n mysql-udf-ipv6
%build
make
%install
test -d /usr/lib/mysql/plugin || mkdir /usr/lib/mysql/plugin
#make install prefix=$RPM_BUILD_ROOT/usr
rm -rf $RPM_BUILD_ROOT
mkdir -p $RPM_BUILD_ROOT/usr/lib/mysql/plugin
make install
cp /usr/lib/mysql/plugin/mysql_udf_ipv6.so $RPM_BUILD_ROOT/usr/lib/mysql/plugin/
%files
%defattr(-,root,root)
%doc README
%doc Changelog
/usr/lib/mysql/plugin/mysql_udf_ipv6.so
October 30th, 2010 at 19:25
@Sean: Thanks! We completely lack experience regarding RPM packaging, so I’m not even sure how I’d go about merging your changes in the repository properly. But I’d definitely like to pull a patch into BitBucket if someone else can make that available.
October 30th, 2010 at 19:37
@Sean: I placed it in mysql-udf-ipv6.spec for now…
December 15th, 2010 at 19:38
Die wunderbare Welt von Isotopp says:MySQL 5.5 ist die aktuelle Version…
Oracle teilt mit: MySQL 5.5 is GA!. Damit ist MySQL 5.5 jetzt die aktuelle Version. 5.1 wird noch voll unterstützt, ist aber absehbar auf dem Weg nach draußen. MySQL 5.0 ist bereits seit Ende letzten Jahres nicht mehr im Support. Im Gesamtüberblick hab…
December 29th, 2010 at 14:19
MySQL y las IPv6 | EtnasSoft says:[...] http://forums.mysql.com/read.php?20,178272,179710#msg-179710 http://labs.watchmouse.com/2009/10/extending-mysql-5-with-ipv6-functions/ [...]
March 14th, 2011 at 09:04
I’m not too familiar with how user extensions affect query optimization, but it seems when I use this extension that MySQL no longer realizes the function value is constant and eligible for use in index lookups; compare the native INET_NTOA with INET6_NTOP:
mysql> explain SELECT ip FROM log WHERE log.ip = INET_NTOA(123456789)possible_keys: ip
key: ip
key_len: 18
ref: const
rows: 1
Extra: Using where; Using index
mysql> explain SELECT ip FROM log WHERE log.ip = INET6_NTOP('AAAA')
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 8196804
Extra: Using where; Using temporary; Using filesort
Sadly the only workaround I’ve found is to rewrite this into two queries.
March 14th, 2011 at 10:33
I’ll have a look at that soon Rich, thanks…
March 14th, 2011 at 23:01
I just noticed the example output I pasted last night is rather verbose (too much editing in an attempt to simplify the test case, and too little sleep
). Here’s a much better example of the problem manifesting:
mysql> explain SELECT ip FROM bericht_ip WHERE bericht_ip.ip = INET6_PTON(’1.2.3.4′);
+—-+————-+————+——-+—————+——+———+——+———+————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————+——-+—————+——+———+——+———+————————–+
| 1 | SIMPLE | bericht_ip | index | NULL | ip | 18 | NULL | 8205609 | Using where; Using index |
+—-+————-+————+——-+—————+——+———+——+———+————————–+
1 row in set (0.00 sec)
mysql> SELECT ip FROM bericht_ip WHERE bericht_ip.ip = INET6_PTON(’1.2.3.4′);
Empty set (4.04 sec)
—
mysql> SET @ip = (SELECT INET6_PTON(’1.2.3.4′));explain SELECT ip FROM bericht_ip WHERE bericht_ip.ip = @ip;
Query OK, 0 rows affected (0.00 sec)
+—-+————-+————+——+—————+——+———+——-+——+————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————+——+—————+——+———+——-+——+————————–+
| 1 | SIMPLE | bericht_ip | ref | ip | ip | 18 | const | 1 | Using where; Using index |
+—-+————-+————+——+—————+——+———+——-+——+————————–+
1 row in set (0.00 sec)
mysql> SET @ip = (SELECT INET6_PTON(’1.2.3.4′));SELECT ip FROM bericht_ip WHERE bericht_ip.ip = @ip;
Query OK, 0 rows affected (0.00 sec)
Empty set (0.00 sec)
While explain does claim to be using an index, the query times would suggest otherwise (and the fact that there is a full table scan). Thanks for taking the time to look into it.
March 23rd, 2011 at 21:05
I read through some MySQL bug reports regarding UDFs and came across the “const_item” struct member. I set initid->const_item = 1 instead of 0 in the initialization functions inet6_pton_init and inet6_ntop_init, this seems to have resolved the issue.
March 24th, 2011 at 12:20
@Rich: I always found the MySQL documentation on the const_item a bit ambiguous, so didn’t dare using it yet. Does it also work correctly in case the argument is not a constant, but for example a row field like INET_PTON(table.ip)? If you didn’t run into problems with that I’m happy to commit it to Bitbucket…
March 29th, 2011 at 16:11
I agree the documentation is a very vague in this area and I probably would have the same reservations about using it, but the performance issues of the non-constant version were too much for us not to risk trying. From what I pieced together, it seems the constant_item member is for functions that return different results regardless of any input, eg a timer or random number generator but I can’t say with 100% certainty.
It does appear to work with table rows as well as immediate constants, but I’ve not performed enough testing with every possible scenario to say for sure it’s completely safe. It works well enough for us for basic IP operations – printable IPv4/IPv6 to binary and back on both constants and table data.
March 31st, 2011 at 02:32
Finally ran into a problem, having it as const definitely does cause some issues:
(excerpt of a bigger query)
SELECT INET6_NTOP(ip) AS ipstr, HEX(ip) AS hexip
217.91.225.115 | 4A48E0B8
217.91.225.115 | 9B3844D6
It seems to only happen with more complicated joins, but either way I wouldn’t recommend using it. It seems the only way to have this work efficiently and correctly is to make it an internal MySQL function like INET_NTOA which is beyond the scope of what I want to do right now.
June 19th, 2011 at 12:03
Are there any updates on Rich’s issue? I have tables with millions of rows indexed by the IP address, but am finding no way to force the use of the index when using the inet6_pton function. Performance without using indexes is unacceptably poor. Suggestions welcomed.
June 19th, 2011 at 13:36
I have found a work-around that semi-addresses my question above. The issue as Rich’s thread states is that the inet6_pton function does not yield a constant which causes the optimizer to ignore indexes. To avoid this try the something like the following:
SET @bip = inet6_pton(?);
SELECT * FROM … WHERE ipv6=@bip…;
By setting @bip you now have a constant and the WHERE clause can then use indexes. Much faster.
September 16th, 2011 at 15:27
I’m not getting this to work correctly in OSX. I have a self installed version of MySQL 64-bit using the binaries provided on MySQL’s site. I compiled the UDF files and copied them to /usr/local/mysql/lib/plugin and /usr/local/mysql-5.1.58-osx10.6-x86_64/lib/plugin, rebooted, and it’s giving me FUNCTION ips.INET6_PTON does not exist where `ips` is the name of the database table im one. The query i am running is SELECT INET6_PTON(’192.168.1.1′); Any ideas? I also tried loading the plugin with INSTALL PLUGIN but got an error as well.
September 21st, 2011 at 08:12
Hello,
This article states that MySQL supports IPv6 from version 5.6.3. Does anyone know if it is also supported for MySQL Enterprise?
March 20th, 2012 at 14:27
Hello There. I found your weblog the usage of msn. This is a really neatly written article. I’ll make sure to bookmark it and return to learn extra of your helpful info. Thank you for the post. I’ll certainly return.