3

Convert old INET_ATON value to new binary INET6_ATON value without INET6_ATON / INET6_NTOA

We have existing data in a table, the field is of type UNSIGNED INT which holds IPv4 data which was created with INET_ATON().

But now we move to INET6_ATON() in the queries and want to migrate the data diectly without creating additional database fields for the conversion.

So I changed the field type from UNSIGNED INT to VARBINARY(16).

New data is stored as binary value with INET6_ATON().

But how can we convert the old data? I already tried to cast the existing values to integer and convert them to with HEX. This gives me the same hex string when saving it with INET6_ATON().

Did I miss some step or some literal? UPDATE visitors SET ip = HEX(CAST(ip AS UNSIGNED))

The binary data is not the same. In this case it is saved as hex value. Using BIN(), CONVERT() and CAST() did not help.

Example data:

old ip value in unsigned int field:
2130706433 ( = ip2long('127.0.0.1') )

old value as shown in varbinary field:
32313330373036343333 ( = 2130706433 )

new value as shown in varbinary field:
7f000001 ( = INET6_ATON('127.0.0.1') )

We can not directly use INET6_ATON() / INET6_NTOA() for conversion.

Should we convert the data for every row with the PHP functions inet_top() and inet_pton() or is there a pure SQL solution for this without the need for some UDF so all rows are updated at once?

It seems there was already a similar question but there is no solution and the solution mentioned in the comments procudes wrong data and does not provide some working example code: Convert IPv6 to binary without INET6_ATON()

References:
http://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_inet6-aton
http://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_inet6-ntoa
http://php.net/manual/en/function.inet-pton.php
http://php.net/manual/en/function.inet-ntop.php

Community
  • 1
  • 1
  • You seemed to have skipped the problem where IPv4 addresses can't directly be converted to IPv6 addresses. In your example, `127.0.0.1` is the IPv4 loopback address, but the IPv6 equivalent loopback address is `::1`. What you are attempting doesn't really make sense. – Ron Maupin Jan 30 '16 at 20:17
  • This was just an example and yet it works like desired. Take the result of ip2long of 127.0.0.1, get the hex of it and put it into inet_ntop and you get 127.0.0.1 again. We just have to convert all old data to the new format. The old data just consits of IPv4 addresses so this is completely fine. inet_pton and inet_ntop support IPv6 and IPv4, also INET6_NTOA and INET6_ATON support both. No need to convert to IPv6 directly (they can not be converted to IPv6 directly as the IPv6 address may be different). I am not sure what you mean but the code actually makes sense. It's in the docs. –  Jan 30 '16 at 20:50
  • OK. Just don't try to use the results as IPv6 addresses since you will be creating unsupported IPv6 addresses. – Ron Maupin Jan 30 '16 at 20:53
  • No, we won't do this. We still get the right results using inet_pton / inet_ntop like expected when using INET6_NTOA and INET6_ATON. New data uses directly the right format, no need to convert them from the long (integer) to the hex format. It is just about the actual formats representing the packed IPs ;-) –  Jan 30 '16 at 20:56

1 Answers1

2

I found the solution.

With an UPDATE query we can fetch the original value from the database, cast it to int again, hex and unhex it (can this be further shortened?) and we get the right binary value in the database.

UPDATE table SET ip = UNHEX(HEX(CAST(ip AS UNSIGNED)))