0

I have the following MySQL database table:

CREATE TABLE `example` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`ip` BIGINT(11) NOT NULL,
`ipv6` VARBINARY(16) NOT NULL
PRIMARY KEY (`id`)
);

My goal is simply to copy/convert the existing IPv4 IP addresses in to IPv6 format in the new ipv6 column. So I run the following query which worked just fine in all my test cases:

UPDATE example SET ipv6 = INET6_ATON(INET_NTOA(ip));

This should be simple right? No... After processing 1,083 records MariaDB returns the following error:

Column 'ipv6' cannot be null.

I'm thinking odd so I decide to start verifying the data:

  • There are 1279 records in this table.
  • All records contain a value for the ip column so that seems good. So I scroll down to the first record which did not convert. It has a value of 40036798809 which is 11 numbers so that should match up with INT(11) right?
  • However the second row that was not processed (keeping in mind that MySQL naturally executed the UPDATE query going in the ascending order of the primary key id) that the ip value for that record is 10317637058914 which is 14 numbers long which is not supposed to be possible in an INT(11) field, correct?
  • I see some other integers that are clearly exceeding the integer length so I decide to ORDER the table by the ip in HeidiSQL and then suddenly the highest value record for the ip column is 1202623438. That is ten numbers in length. phpMyAdmin also shows the larger number however I have switched to HeidiSQL since I find it's GUI is superior for my local development.
  • After some research it appears that the length of the datatype for BIGINT has nothing to do with column range. HeidiSQL changes the ip column values by simply changing the ORDER! Wait, what?!
  • My next step after continued reading was to check whether the column is signed or unsigned. As HeidiSQL shows that the ip column is not checked for Unsigned that implies that the ip column is signed and therefore it's maximum value is (all commas added only for visualization, actual values are purely numeric) 2,147,483,647 while the value that would not parse during the UPDATE query is 40,036,798,809.
  • Earlier research suggested that if a number in the column is larger than what is allowed (not sure why that would even be allowed?) then it would be treated as the maximum allowed value (I imagine in this case 2,147,483,647); is this true?

The Question(s)

  • In summation: why won't the UPDATE query parse the entire table?
  • Which depends on: what is the problem with MySQL and/or HeidiSQL with storing values?
  • Will MariaDB / MySQL allow the storage of numbers larger than what the table structure allows?
  • How is is the value 40,036,798,809 (again, without commas) being treated during the UPDATE query if the ip column type is BIGINT(11)?
  • What would be the effective value stored for the highest valid IPv4 IP address (255.255.255.255)?
  • I presume when I ORDER in HeidiSQL that it is showing the effective highest value; is this best-guess accurate?

IPv4 to IPv6 Conversion Issue Screenshot

John
  • 1
  • 13
  • 98
  • 177

2 Answers2

1

Your number 40,036,798,809 seems to be larger than the maximum possible ipv4 address 255.255.255.255, which yields 4.294.967.295 (or 0xFFFFFFFF) or largest unsigned longint. So it cant possibly be an IPV4 address, hence the null, hence the error.

Maybe some of your numbers are not IPV4 addresses ?

As for your MySQL Integer column sizes, I sense some confusion there with its byte sizes. You can read up at What is the size of column of int(11) in mysql in bytes? on that subject.

Tuncay Göncüoğlu
  • 1,699
  • 17
  • 21
  • I've been working to deploy proper direct IPv6 (with complete absence of IPv4 such as through tunnels) the past couple of days. I am now curious if I can convert to IPv6 from that though? – John Sep 16 '17 at 14:59
  • in theory, yes, sure, if all your equipment supports ipv6. I'm not too sure on how practical it would be tho. certainly not if you run a website and expect it to be reachable by all internet. – Tuncay Göncüoğlu Sep 16 '17 at 15:02
  • No, I said IPv6 *support* as in fully tested *without* IPv4 on a connection, *not* that I would drop IPv4 support. Any way I'm trying to see if I can salvage the larger integers; this doesn't seem to work though: `UPDATE example SET ipv6 = IF (INET6_ATON(INET_NTOA(ip)) IS NULL,INET6_NTOA(ip),INET6_ATON(INET_NTOA(ip)));` – John Sep 16 '17 at 15:04
  • what INET6_ATON returns seems binary, not a number. Try something like this: `update example set ipv6=inet6_aton(coalesce(inet_ntoa(ip), inet6_ntoa(ip), 0));` – Tuncay Göncüoğlu Sep 16 '17 at 15:14
0

Your conversion is correct; your data is wrong. IPv4 involves 32-bit numbers; you have things stored in your BIGINT bigger than 32 bits.

This will locate the bad rows:

SELECT ip FROM example WHERE ip > INET_ATON('255.255.255.255');

You will probably find 196 (1279-1083) bad rows.

The (11) is totally unused and irrelevant (unless you have ZEROFILL).

Rick James
  • 135,179
  • 13
  • 127
  • 222