inet_aton and inet6_aton not working on local server

[larry29936]

@Barand, Thanks for taking the time and effort. Like you, I'm not familiar with working with IPv6 ip's. Time for me to do more research. At my age, I've got nothing but time. I think the problem is that I'm not getting the full IPv6 ip so they won't convert.

Edited May 11, 2020 by larry29936

[Barand]

Similar problem in your download table. You have defined the string version of the  ip_address as varchar(32) but to hold 8 groups of 4 hex chars plus colons between them you need 40 chars.

[gizmola]

2 hours ago, Barand said:

I used this upload SQL

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.

 

IPV6 is a 128 bit number, broken for representation into 8 16 bit blocks.  Since each byte requires 2 hex digits, a 16 bit block requires 4 hex chars.  

This range of numbers has 2 issues I see:

• Only 6 blocks.
• Some of the 6 bocks have only 1 hex digit.

It is ok to omit "continguous" zero blocks, and replace them with '::'.  This is the most confusing part of ipv6, because it doesn't matter how many contiguous '0000' blocks you have.  It can be 2,3,4 etc.  You can only use that trick one time however in a number.  ipv6 parsing figures this out and expands the '::' to the correct number of zero blocks.

Thus the example #967 is invalid because there are only 5 blocks.  

2a04:4540:8202:ce00:7c50:

This would be valid

2a04:4540:8202:ce00:7c50::


# Is actually equivalent to 

2a04:4540:8202:ce00:7c50:0000:0000:0000

See if something is messed up that is stripping off  an ending '::' and reducing it to ':'

ipv6 numbering supports "zero suppression" in a few ways.  You can remove leading zeros OR you can compress 4 zeros into one, OR you can omit a block of zeros (one time, I think)  in a number as per the example above.

 

So testing #757, again we have only 6 groups, when we need 8, so once again, adding an end '::' will make it valid:

 

2a02:0c7f:1804:b000:4970:6::

#Equivalent to

2a02:0c7f:1804:b000:4970:0006:0000:0000