inet_aton and inet6_aton not working on local server

[larry29936]

When I run either the inet_aton or inet6_aton command on my local mysql server (Server version: 5.7.30-0ubuntu0.18.04.1 (Ubuntu)) I get the following error:

mysql> UPDATE download SET ip_address = inet6_aton(ADDRESS);
ERROR 1411 (HY000): Incorrect string value: '`test`.`download`.`ADDRESS`' for function inet6_aton

When I run the same command on my web host there's no problem. Both have exactly the same table structure and data. The data on the local server was imported from an export of the data on my web host. In both cases, the ADDRESS field is a varbinary(32). I've tried changing the local server to varbinary(16) and re-importing the data but got the same results. I've also restarted the mysql service but it made no difference.
 

Can someone please explain what is going on?

Edited May 10, 2020 by larry29936

[requinix]

Unless the error message is wrong, it thinks somewhere in your table is the string "`test`.`download`.`ADDRESS`".

[Barand]

Tried to replicate the error message myself ...

My table

CREATE TABLE `download` (
  `download_id` int(11) NOT NULL AUTO_INCREMENT,
  `ip_address` varbinary(16) DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`download_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

My csv data

ip_address,address
"","255.50.1.123"
"","255.50.11.123"
"","255.50.2.123"
"","255.50.4.123"

Running the csv upload query, but omitting the IGNORE to skip the header line)

LOAD DATA LOCAL INFILE 'c:/inetpub/wwwroot/test/ip_test.csv'
INTO TABLE download
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
-- IGNORE 1 LINES
(ip_address, address)
SET ip_address = inet6_aton(address);

Gave the message

Quote

5 row(s) affected, 1 warning(s): 1411 Incorrect string value: '`test`.`download`.`address`' for function inet6_aton 
Records: 5  Deleted: 0  Skipped: 0  Warnings: 1

And this in the table

SELECT download_id
     , HEX(ip_address) as binaryIP
     , address as stringIP
FROM download;

+-------------+----------+---------------+
| download_id | binaryIP | stringIP      |
+-------------+----------+---------------+
|           1 | NULL     | address       |
|           2 | FF32017B | 255.50.1.123  |
|           3 | FF320B7B | 255.50.11.123 |
|           4 | FF32027B | 255.50.2.123  |
|           5 | FF32047B | 255.50.4.123  |
+-------------+----------+---------------+

 

EDIT:

Also, specifying the wrong line terminator character (eg /n instead of \n) gave the same 1411 warning and  the records in the table were like this

+-------------+----------+----------------------------------------------------+
| download_id | binaryIP | stringIP                                           |
+-------------+----------+----------------------------------------------------+
|           8 | NULL     | address""                                          |
|           9 | NULL     | "255.50.1.123"","255.50.11.123"","255.50.2.123"    |
+-------------+----------+----------------------------------------------------+

 

Edited May 10, 2020 by Barand

[larry29936]

@Barand, my data was imported from a .sql file exported from phpMyadmin on my host's server. Every 500 rows or so it inserts another INSERT statement. Could this be the problem? I'm not seeing any of the insert statements in the imported data. I think I'm going to open the exported file in a spreadsheet and see if there are any stray characters then save as a csv and bring the data in via "LOAD DATA LOCAL INFILE".

[Barand]

7 minutes ago, larry29936 said:

my data was imported from a .sql file exported from phpMyadmin on my host's server. Every 500 rows or so it inserts another INSERT statement. Could this be the problem?

That should work OK

Try sorting the data on address. Any weird values might go to either the top or the end and make them easy to find.

At what point do you use inet6_aton()?

[larry29936]

@Barand, I'm going to use it to compare download.ADDRESS to the ip_start and ip_end in the ip_lookup table to get the row id to put into download table so I'm not copying the country, area, and city to the download table. I wish there was a way to create a view on multiple tables, it would be so much easier.

[Barand]

Are ip_start and ip_end columns the string values or are they varbinary(16)?

[Barand]

1 hour ago, larry29936 said:

I wish there was a way to create a view on multiple tables, it would be so much easier.

I know. If only someone would invent JOINs.

[larry29936]

@Barand, all ip's are varbinary(16)

[Barand]

Do you have any records in your download table with NULL ip_adresses after the inet_aton() update?

If so they will have invalid ADDRESS values.

[larry29936]

I know that I have 20 or so IPv6 ip's that won't convert because they were truncated on import.

 

[Barand]

56 minutes ago, larry29936 said:

@Barand, all ip's are varbinary(16)

When you last posted your table structure they were unsigned integer ...

ip_lookup    CREATE TABLE `ip_lookup` (
  `id` int(25) unsigned NOT NULL AUTO_INCREMENT,
  `start_ip` int(64) unsigned NOT NULL,
  `end_ip` int(64) unsigned NOT NULL,
  `continent` varchar(2) COLLATE utf8_general_mysql500_ci NOT NULL,
  `country` varchar(50) COLLATE utf8_general_mysql500_ci NOT NULL,
  `area` varchar(50) COLLATE utf8_general_mysql500_ci NOT NULL,
  `city` varchar(50) COLLATE utf8_general_mysql500_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `start_ip` (`start_ip`)
) ENGINE=InnoDB AUTO_INCREMENT=5277086 DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci

The ip_address in the "download" table and the "start_ip" and "end_ip" in the lookup table all need to be varbinary(16) otherwise the match on "IP_address BETWEEN start_ip AND end_ip" will not work;

[larry29936]

All ip's are now varbinary(16). I emptied the two tables, made the changes from int to varbinary(16) and re-imported the data. All the ip's display in dot notation.  inet6_aton should make getting the row id easy but I can't get it to work. Here's what I have:

UPDATE download d
        INNER JOIN 
    d.ADDRESS on download = ip_lookup.start_ip
SET d.refer = ip_lookup.id
WHERE (inet6_aton(d.ADDRESS) >= inet6_aton(ip_lookup.start_ip) AND inet6_aton(d.ADDRESS) <= inet6_aton(ip_lookup.end_ip)) AND
d.FILENAME is not null;

I've always had a problem with joins, I just can't remember the proper format.

[Barand]

1 hour ago, larry29936 said:

All ip's are now varbinary(16)

 

1 hour ago, larry29936 said:

All the ip's display in dot notation

Those two statements would appear to contradict each other.

 

Are you sure you don't want this...

UPDATE download d
        INNER JOIN 
    d.ADDRESS on (inet6_aton(d.ADDRESS) >= inet6_aton(ip_lookup.start_ip) AND inet6_aton(d.ADDRESS) <= inet6_aton(ip_lookup.end_ip))
SET d.refer = ip_lookup.id
WHERE d.FILENAME is not null;    

 

[larry29936]

Here's a sample of what the imported files look like:

INSERT INTO `download` (`ID`, `LOG_TIME`, `ADDRESS`, `FILENAME`, `country`, `area`, `city`, `ip_address`, `refer`) VALUES
(1, '2020-04-14 14:59:14', '72.168.144.48', NULL, NULL, NULL, NULL, 'Hš�0', NULL),
(2, '2020-04-14 15:00:17', '72.168.144.48', NULL, NULL, NULL, NULL, 'Hš�0', NULL),
(3, '2020-04-14 15:03:39', '72.168.144.48', NULL, NULL, NULL, NULL, 'Hš�0', NULL),
(4, '2020-04-14 15:07:08', '72.168.144.48', 'foxclone35-02.iso', 'United States', 'California', 'Littlerock', 'Hš�0', NULL),
(5, '2020-04-14 15:08:12', '72.168.144.48', NULL, NULL, NULL, NULL, 'Hš�0', NULL),
(6, '2020-04-14 15:53:05', '204.187.14.75', NULL, NULL, NULL, NULL, '̻�K', NULL),
(7, '2020-04-14 15:53:13', '204.187.14.75', NULL, NULL, NULL, NULL, '̻�K', NULL),
(8, '2020-04-14 17:17:05', '83.47.231.251', NULL, NULL, NULL, NULL, 'S/çû', NULL),
(9, '2020-04-14 17:17:32', '83.47.231.251', NULL, NULL, NULL, NULL, 'S/çû', NULL),

IP_LOOKUP.csv sample data (the last 2 fields don't get imported)

1.0.0.0,1.0.0.255,OC,AU,Queensland,"South Brisbane",-27.4748,153.017
1.0.1.0,1.0.1.255,AS,CN,Fujian,Qingzhou,26.4837,117.925
1.0.2.0,1.0.3.255,AS,CN,Fujian,Fuzhou,26.1008,119.295
1.0.4.0,1.0.7.255,OC,AU,Victoria,"Narre Warren",-38.0267,145.301
1.0.8.0,1.0.15.255,AS,CN,Guangdong,Guangzhou,23.1322,113.267
1.0.16.0,1.0.31.255,AS,JP,Tokyo,Shinjuku,35.6978,139.706
1.0.32.0,1.0.63.255,AS,CN,Guangdong,"Guangzhou Shi",23.379,113.763
1.0.64.0,1.0.66.255,AS,JP,Hiroshima,Higashi-ku,34.4015,132.477
1.0.67.0,1.0.70.255,AS,JP,Hiroshima,Hiroshima,34.4,132.45
1.0.71.0,1.0.75.255,AS,JP,Hiroshima,Higashi-ku,34.4015,132.477
1.0.76.0,1.0.76.255,AS,JP,Hiroshima,Miyajima,34.2991,132.322
1.0.77.0,1.0.77.255,AS,JP,Hiroshima,Hiroshima,34.4,132.45
1.0.78.0,1.0.78.255,AS,JP,Hiroshima,Higashi-ku,34.4015,132.477
1.0.79.0,1.0.79.255,AS,JP,Hiroshima,Hiroshima,34.4,132.45
1.0.80.0,1.0.82.255,AS,JP,Hiroshima,Higashi-ku,34.4015,132.477
1.0.83.0,1.0.83.255,AS,JP,Tottori,Kurayoshi,35.4333,133.817
1.0.84.0,1.0.84.255,AS,JP,Tottori,Yonago,35.4333,133.333
1.0.85.0,1.0.87.255,AS,JP,Hiroshima,Higashi-ku,34.4015,132.477
1.0.88.0,1.0.88.255,AS,JP,Shimane,Matsue,35.4833,133.05
1.0.89.0,1.0.90.255,AS,JP,Hiroshima,Higashi-ku,34.4015,132.477

Latest table structures:

CREATE TABLE `download` (
  `ID` int(25) unsigned NOT NULL AUTOINCREMENT,
  `LOG_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ADDRESS` varbinary(16) NOT NULL,
  `FILENAME` varchar(60) CHARACTER SET utf8 COLLATE COLLATE=utf8_general_ci; DEFAULT NULL,
  `refer` int(25) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;


CREATE TABLE `ip_lookup` (
  `id` int(25) UNSIGNED NOT NULL AUTOINCREMENT
  `start_ip` varbinary(16) NOT NULL,
  `end_ip` varbinary(16) NOT NULL,
  `continent` varchar(2) COLLATE utf8_general_ci NOT NULL,
  `country` varchar(50) COLLATE utf8_general_ci NOT NULL,
  `area` varchar(50) COLLATE utf8_general_ci NOT NULL,
  `city` varchar(50) COLLATE utf8_general_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Yes, the ip's really do display as xxx.xxx.xxx.xxx when browsing the tables in phpMyadmin.

[larry29936]

@Barand, I really appreciate your help. Your suggested code fails in both phpMyadmin and mysql shell. Results follow:

mysql> UPDATE download d
    ->         INNER JOIN 
    ->     d.ADDRESS on (inet6_aton(d.ADDRESS) >= inet6_aton(ip_lookup.start_ip) AND inet6_aton(d.ADDRESS) <= inet6_aton(ip_lookup.end_ip))
    -> SET d.refer = ip_lookup.id
    -> WHERE d.FILENAME is not null;
ERROR 1146 (42S02): Table 'd.ADDRESS' doesn't exist

I also tried it with UPDATE download as d, no luck.
 

Edited May 10, 2020 by larry29936

[Barand]

1 hour ago, larry29936 said:

Your suggested code fails in both phpMyadmin and mysql shell.

Sorry, should have been

UPDATE download d
        INNER JOIN 
    ip_lookup on (inet6_aton(d.ADDRESS) >= inet6_aton(ip_lookup.start_ip) AND inet6_aton(d.ADDRESS) <= inet6_aton(ip_lookup.end_ip))
SET d.refer = ip_lookup.id
WHERE d.FILENAME is not null; 

 

[larry29936]

@Barand, now I'm getting that "Incorrect string value" error on the ip_lookup table instead of the download table. ☹️  Could malformed IPv6 ip's cause this? I hope I don't need to empty the table and re-import the .csv because then I have to run code to replace the country codes with country names and then might not fix the problem.

mysql> UPDATE download d
    ->         INNER JOIN 
    ->     ip_lookup on (inet6_aton(d.ADDRESS) >= inet6_aton(ip_lookup.start_ip) AND inet6_aton(d.ADDRESS) <= inet6_aton(ip_lookup.end_ip))
    -> SET d.refer = ip_lookup.id
    -> WHERE d.FILENAME is not null; 
ERROR 1411 (HY000): Incorrect string value: '`test`.`ip_lookup`.`end_ip`' for function inet6_aton
mysql> 
 

[Barand]

My code still isn't correct after looking at your "download" data. The ip_address in the download table is already varbinary so doesn't need the inet6_aton(). (That's already been done)

You are getting xxx.xxx.xxx.xxx from ip_lookup in phpMyAdmin presumably because that's what you are putting in those columns instead of converting them to varbinary with inet6_aton() - unless your upload does that conversion in which case you would need to 

SELECT inet6_ntoa(start_ip)

to get xxx.xxx.xxx.xxx format.

Your start and end ip addresses need to be correct varbinary for range checking to work. The x.x.x.x don't sort reliably so can't be used for range definitions. For example

• 1.0.0.1
• 1.0.0.2
• 1.0.0.11

would sort as

• 1.0.0.1
• 1.0.0.11
• 1.0.0.2

What is your code for uploading the "ip_lookup.csv"?

[larry29936]

I'm just importing a .csv that's been gzipped thru phpMyadmin. The csv is more than 5 million rows of data. I guess I could import it into a temporary table then run code to get it into the proper format in the ip_lookup table. I should probably do the same for the download table and re-write the code for inserting rows in it.

What data type do you think I should use to import the ip's into the temporary tables? It's apparent that varbinary isn't going to work. Seeing as the ip's are strings in the import files, would a varchar(16) work?

Edited May 10, 2020 by larry29936

[Barand]

I did show you earlier today how to convert the values on upload. In case you weren't awake, here is a query to upload the lookup data and convert the x.x.x.x to varbinary (16) at the same time

LOAD DATA LOCAL INFILE 'ip_lookup.csv'
INTO TABLE ip_lookup
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES                                                        -- REMOVE if no heading row
(start_ip, end_ip, continent, country, area, city, @dummy, @dummy)
SET start_ip = inet6_aton(start_ip)
  , end_ip = inet6_aton(end_ip);

 

[larry29936]

Thanks for the reminder, I've been up working on this for the last 17 hours. I guess I should put it down until tomorrow when my brain can function properly again. 

Thank you for all your help. Have a good evening.

 

[larry29936]

@Barand, I dropped the download table and recreated it then imported the data. Still getting the "ERROR 1411 (HY000): Incorrect string value: '`test`.`download`.`ADDRESS`' for function inet6_aton". SET start_ip = inet6_aton(start_ip) , end_ip = inet6_aton(end_ip); worked for the ip_lookup table so I tried the same thing on the download table except it was a .sql file, not a .csv.

Edited May 11, 2020 by larry29936

[larry29936]

UPDATE:  Emptied table and imported download.csv. Still have problem of "Incorrect string value".

[Barand]

I used this upload SQL

LOAD DATA LOCAL INFILE 'download.csv'
INTO TABLE download
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(ID, LOG_TIME, ip_address, FILENAME, @dummy, @dummy, @dummy, @dummy, @dummy) 
SET ADDRESS = inet6_aton(ip_address);

But, like you, got 17 "error 1411" reports. These are the records giving the errors

mysql> SELECT id
    ->      , hex(address) as address
    ->      , ip_address
    -> FROM download
    -> WHERE address IS NULL
    -> ORDER BY address;
+------+---------+----------------------------+
| id   | address | ip_address                 |
+------+---------+----------------------------+
|  757 | NULL    | 2a02:c7f:1804:b000:4970:6  |
|  838 | NULL    | 2a01:cb05:4ce:5900:eff0:3  |
|  841 | NULL    | 2a01:cb05:4ce:5900:eff0:3  |
|  967 | NULL    | 2a04:4540:8202:ce00:7c50:  |
|  968 | NULL    | 2a04:4540:8202:ce00:7c50:  |
|  970 | NULL    | 2a04:4540:8202:ce00:7c50:  |
|  977 | NULL    | 2a04:4540:8202:ce00:7c50:  |
|  978 | NULL    | 2a04:4540:8202:ce00:7c50:  |
| 1034 | NULL    | 2a04:4540:820f:a000:350f:  |
| 1035 | NULL    | 2a04:4540:820f:a000:350f:  |
| 1036 | NULL    | 2a04:4540:820f:a000:350f:  |
| 1037 | NULL    | 2a04:4540:820f:a000:350f:  |
| 1044 | NULL    | 2a04:4540:820f:a000:350f:  |
| 1251 | NULL    | 2a04:4540:8205:4200:4ddb:  |
| 1252 | NULL    | 2a04:4540:8205:4200:4ddb:  |
| 1412 | NULL    | 94.34.81.202, 94.34.81.202 |
| 1414 | NULL    | 94.34.81.202, 94.34.81.202 |
+------+---------+----------------------------+

The two at the end are obviously wrong as there are two IPv4 addresses in the same field. The others that didn't convert are all the IPv6 address that you have in your csv file.

I confesss, I haven't worked with IPv6 so I haven't a clue right now what is wrong with them to make them invalid.