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 |
+------------------+-----------------------------------------+------+

Answer 1

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.