mysql – Troubleshooting INET6_ATON Returning NULL in MySQL

mariadbMySQL

I'm making a MySQL Stored Procedure which takes a string representation of an IPv4 or v6 address and converts it to binary using INET6_ATON. I know that my version of MySQL supports this function because I can run SELECT HEX(INET6_ATON("::ffff:127.0.0.1")); and it returns the correct representation.

However, when I try to put this in to a Stored Procedure and use an input parameter, it always returns NULL no matter what I put in to it.

Here's a simplified version I made using a Stored Function instead:

DROP FUNCTION IF EXISTS `addrToBinary`;
DELIMITER ;;
CREATE FUNCTION `addrToBinary` (`in_remote_ip` varchar(45)) RETURNS varchar(45)
DETERMINISTIC
BEGIN
    RETURN HEX(INET6_ATON(in_remote_ip));
END;;
DELIMITER ;

SELECT
    addrToBinary("::ffff:127.0.0.1"), -- NULL
    HEX(INET6_ATON("::ffff:127.0.0.1"));    -- 00000000000000000000FFFF7F000001

Any ideas on how I can get this function to work the same inside a Stored Procedure/Function as it does typing it in manually?

UPDATE: I've just found out that I'm in fact running MariaDB (5.5.5-10.0.17-MariaDB). Here's a screenshot of the issues when I run it using Adminer:

Stored Function Null

Best Answer

C:\>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.6.22 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> DROP FUNCTION IF EXISTS `addrToBinary`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DELIMITER ;;
mysql> CREATE FUNCTION `addrToBinary` (`in_remote_ip` varchar(45)) RETURNS varchar(45)
    -> DETERMINISTIC
    -> BEGIN
    ->     RETURN HEX(INET6_ATON(in_remote_ip));
    -> END;;
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> select `addrToBinary`("::ffff:127.0.0.1");
+------------------------------------+
| `addrToBinary`("::ffff:127.0.0.1") |
+------------------------------------+
| 00000000000000000000FFFF7F000001   |
+------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT
    ->     addrToBinary("::ffff:127.0.0.1"),
    ->     HEX(INET6_ATON("::ffff:127.0.0.1"));
+----------------------------------+-------------------------------------+
| addrToBinary("::ffff:127.0.0.1") | HEX(INET6_ATON("::ffff:127.0.0.1")) |
+----------------------------------+-------------------------------------+
| 00000000000000000000FFFF7F000001 | 00000000000000000000FFFF7F000001    |
+----------------------------------+-------------------------------------+
1 row in set (0.00 sec)

mysql>

Works Fine For Me By Hand in MySQL 5.6.22 for Windows

mysql> select `addrToBinary`(@given_ip);
+----------------------------------+
| `addrToBinary`(@given_ip)        |
+----------------------------------+
| 00000000000000000000FFFF7F000001 |
+----------------------------------+
1 row in set (0.00 sec)

mysql>

It works when passing in a parameter from the MySQL Console

If you are doing this from PHP, please make sure the IP is properly enclosed in quotes

Related Question