INET6_ATON returns NULL for input parameter

Question

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

• jkavalik: That's weird - I tried sqlfiddle.com/#!9/7fc9f/2 and it works OK in both cases.
• matpie: That it works in sqlfiddle worries me that it's probably my fault for using MariaDB instead.
• jkavalik: Well, I don't think it is some kind of fault to use MariaDB ;) but I just tested it on both "10.0.19-MariaDB-1~precise-log mariadb.org binary distribution" - Ubuntu 12.04 and "10.0.17-MariaDB-wsrep-log" - CentOS 6.6 and again it works ok.. Can you check commandline client instead of Adminer (it should not affect results IMHO.. but you never know..)
• matpie: I found it to be an issue with the collation and added my own answer below.

Answer 1

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

• matpie: I'm using Adminer and I'm connecting to a MariaDB instance. Sorry about the misinformation that I was using MySQL. I've updated my question.

Answer 2

The issue appears to be related to the collation of the DB in which I created it. My DB is collation ucs2_general_ci. If I change the input collation of the function to UTF8 it works fine:

DROP FUNCTION IF EXISTS `addrToBinary`;
CREATE FUNCTION `addrToBinary` (`in_remote_ip` varchar(45) CHARACTER SET 'utf8')
RETURNS varbinary(16) DETERMINISTIC
RETURN INET6_ATON(in_remote_ip);
SELECT HEX(addrToBinary("::ffff:127.0.0.1")); -- 00000000000000000000FFFF7F000001

Setting it to ucs2 (or what was the default for my DB):

DROP FUNCTION IF EXISTS `addrToBinary`;
CREATE FUNCTION `addrToBinary` (`in_remote_ip` varchar(45) CHARACTER SET 'ucs2')
RETURNS varbinary(16) DETERMINISTIC
RETURN INET6_ATON(in_remote_ip);
SELECT HEX(addrToBinary("::ffff:127.0.0.1")); -- NULL

• jkavalik: Good that you found it. May be worth reporting to mariadb or directly to mysql - sqlfiddle.com/#!9/6fe20/1
• Michael - sqlbot: Not necessarily a bug... SHOW WARNINGS; upon receiving the NULL response might also have provided you with some useful info.
• jkavalik: @Michael-sqlbot I am not getting any warnings in MariaDB case at least.