2

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

  • That's weird - I tried sqlfiddle.com/#!9/7fc9f/2 and it works OK in both cases. – jkavalik Jun 1 '15 at 20:00
  • That it works in sqlfiddle worries me that it's probably my fault for using MariaDB instead. – matpie Jun 1 '15 at 21:26
  • 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..) – jkavalik Jun 2 '15 at 6:13
  • I found it to be an issue with the collation and added my own answer below. – matpie Jun 2 '15 at 17:51
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

  • 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. – matpie Jun 1 '15 at 21:27
0

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
  • Good that you found it. May be worth reporting to mariadb or directly to mysql - sqlfiddle.com/#!9/6fe20/1 – jkavalik Jun 2 '15 at 19:43
  • Not necessarily a bug... SHOW WARNINGS; upon receiving the NULL response might also have provided you with some useful info. – Michael - sqlbot Jun 3 '15 at 2:36
  • @Michael-sqlbot I am not getting any warnings in MariaDB case at least. – jkavalik Jun 3 '15 at 5:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.