How do I get MySQL's "load data" to properly import very large integers into a binary(16) field?

Question

I have a file containing ipv6 addresses represented as very large integers. When I import it into mysql the data is wrong. What do I need to do in order to get the load data to properly import the data into a binary(16) column?

Schema:

CREATE TABLE `ipv6_test` (
  `ip` binary(16) NOT NULL,
  `name` varchar(128) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CSV file to import, ipv6_test.csv:

"58569107296622255421594597096899477505","test"

MySQL import command:

mysql> load data local infile 'ipv6_test.csv' into table ipv6_test fields terminated by ',' enclosed by '"' lines terminated by '\n';

Query:

select ip, INET6_NTOA(ip), name from ipv6_test;

Incorrect Results:

+------------------+-----------------------------------------+------+
| ip               | INET6_NTOA(ip)                          | name |
+------------------+-----------------------------------------+------+
| 5856910729662225 | 3538:3536:3931:3037:3239:3636:3232:3235 | test |
+------------------+-----------------------------------------+------+

Expected Results:

+------------------+-----------------------------------------+------+
| ip               | INET6_NTOA(ip)                          | name |
+------------------+-----------------------------------------+------+
| ? binary data ?  | 2C0F:FFF0:0000:0000:0000:0000:0000:0001 | test |
+------------------+-----------------------------------------+------+

Why not just the textual representation? This is far easier to read, query, and work with. If you're doing a lot of IP work, it's worth noting Postgres has a native IP address column type. — tadman, Dec 19 '19 at 19:01
The file to import comes from a third party. We already have a process that imports ipv4 addresses which works. I'd rather just change the load data command to handle the larger ipv6 addresses than write a program or script to convert these decimal representation of ipv6 addresses to a more typical format. — Alpha Fighter, Dec 19 '19 at 19:06
Your third party is doing it completely wrong. This is one of the worst ways to represent IP addresses. They're making your life miserable to shave a few bytes off their file size. — tadman, Dec 19 '19 at 19:07

tadman · Accepted Answer · 2019-12-19T19:13:30.703

INET6_NTOA() takes a raw binary address and converts it to a hex version. You're passing in an integer, which is invalid.

"58569107296622255421594597096899477505" becomes 0x353835363931303732393636... as raw hex, which is where that weird value comes from.

I'm not sure that MySQL can manipulate 128-bit numbers and account for the endian-issues that crop up as well. Apart from NUMERIC types, the largest value it can handle internally appears to be 64-bit. You'll need to convert your data before writing the CSV.

For example, if you can convert it to look like this in the file:

0x2c0ffff0000000000000000000000001,test

Then you can import that as as a raw binary value.

How do I get MySQL's "load data" to properly import very large integers into a binary(16) field?

1 Answers1