| Bug #68454 | Using inet6_ntoa and int6_aton to convert IP causes errors | ||
|---|---|---|---|
| Submitted: | 21 Feb 2013 9:52 | Modified: | 4 Mar 2013 15:30 |
| Reporter: | fengfu zhao | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S1 (Critical) |
| Version: | 5.6.11 | OS: | Any |
| Assigned to: | Tor Didriksen | CPU Architecture: | Any |
| Tags: | inet6_ntoa inet6_aton error | ||
[21 Feb 2013 10:26]
MySQL Verification Team
It does look suspicious indeed:
mysql> select id, inet6_ntoa(ip),ip,hex(ip) from ipconvert;
+----+----------------+------+----------+
| id | inet6_ntoa(ip) | ip | hex(ip) |
+----+----------------+------+----------+
| 1 | 123.183.5.59 | {╖♣; | 7BB7053B |
| 2 | NULL | NULL | NULL |
| 3 | NULL | {╖♣; | 7BB7053B |
| 4 | NULL | {╖♣; | 7BB7053B |
+----+----------------+------+----------+
4 rows in set (0.00 sec)
mysql> select id, inet6_ntoa(ip),ip,hex(ip) from ipconvert where ip is not null;
+----+----------------+------+----------+
| id | inet6_ntoa(ip) | ip | hex(ip) |
+----+----------------+------+----------+
| 1 | 123.183.5.59 | {╖♣; | 7BB7053B |
| 3 | 123.183.5.59 | {╖♣; | 7BB7053B |
| 4 | 123.183.5.59 | {╖♣; | 7BB7053B |
+----+----------------+------+----------+
3 rows in set (0.00 sec)
mysql>
mysql> select version();
+--------------+
| version() |
+--------------+
| 5.6.11-debug |
+--------------+
1 row in set (0.00 sec)
[4 Mar 2013 15:30]
Paul DuBois
Noted in 5.6.11, 5.7.1 changelogs. If INET6_NTOA() or INET6_ATON() returned NULL for a row in a result set, following rows also returned NULL.
Description: I store ip as bytes(using inet6_aton to convert) in mysql. when my program run the query "select inet6_ntoa(ip) as IP from ipconvert", I want mysql to return right normal IP. But i find that the result is unexpected。in return dataset, if ip is null in mysql , inet6_ntoa(ip) is null too. this result is right; but the following inet6_ntoa(ip) s are null too, even if ip corresponding to IP is not null. such as: select inet6_ntoa(ip) as IP from ipconvert; return dataset: IP(return dataset) ip(stored in mysql) 123.183.5.59 {?; right null null right null {?; IP should be 123.183.5.59 null {?; IP should be 123.183.5.59 if my program run the query 'select inet6_ntoa(ip) as IP from ipconvert where ip is not null', the result is right; return dataset: IP(return dataset) ip(stored in mysql) 123.183.5.59 {?; right 123.183.5.59 {?; right 123.183.5.59 {?; right After testing, i find if ip is null , the following IP is null too, even if ip corresponding to IP is not null in one query operation. and when my program run the query 'load data infile 'filename' into table ipconvert set sAddr=inet6_aton(ip)' using inet6_aton also attain such problems How to repeat: test inet6_ntoa 1. create a table: schema: CREATE TABLE `ipconvert` ( `id` bigint(20) NOT NULL, `ip` varbinary(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 2. insert some records: insert into ipconvert values(1,inet6_aton('123.183.5.59')),(2,NULL),(3,inet6_aton('123.183.5.59')),(4,inet6_aton('123.183.5.59')) 3. run these queries: 'select inet6_ntoa(ip) as IP from ipconvert' and 'select inet6_ntoa(ip) as IP from ipconvert where ip is not null'. and compare results test inet6_aton 1. create a table: schema: CREATE TABLE `ipconvert` ( `id` bigint(20) NOT NULL, `ip` varbinary(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 2 insert some records: use load data style load data infile 'filename' into table ipconvert set sAddr=inet6_aton(ip) file content: 1 123.183.5.59 2 \N 3 123.183.5.59 4 123.183.5.59 3 After loading, check the data in mysql