You are here: Home / Technologies / Databases / Unexplained function output

Unexplained function output

by

I was asked today to confirm the operation of INET_ATON() and INET_NTOA() functions for converting IP4 strings to numeric representations. My tests on the machine I was just connected to at the very instant reported the following results.

mysql> SELECT VERSION();
+-----------------+
| VERSION()       |
+-----------------+
| 5.1.31-1ubuntu2 |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT INET_ATON('74.165.97.204') AS ipn;
+------------+
| ipn        |
+------------+
| 1252352460 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT INET_NTOA(1252352460) as ipa;
+---------------+
| ipa           |
+---------------+
| 74.165.97.204 |
+---------------+
1 row in set (0.01 sec)

The results on the client via ODBC using MySQL 5.0.45 was?

ipn
1252352460

ipa
37342E3136352E39372E323034

What is causing this? It was immediately suspicious the pattern of 2E (the only letter), which translates from Hex to decimal to ‘.’ and 37 becomes 7 and 34 becomes 4, so the string is in hexidecimal and then with the UNHEX() function you get the right answer?

mysql> select unhex('37342E3136352E39372E323034');
+-------------------------------------+
| unhex('37342E3136352E39372E323034') |
+-------------------------------------+
| 74.165.97.204                       |
+-------------------------------------+

Could it be the ODBC drivers? Investigation showed them current as 5.01.05.00

Searching the MySQL Bugs database didn’t reveal anything noticable, nor on the forums.

My best suggestion was to post on the MySQL ODBC forums but I welcome any feedback from my readers.

Comments

  1. says

    The INET_NTOA() function returns a binary string, which the ODBC driver dutifully returns as binary data. You can use CAST() to get around the problem, and pile on to one of the bugs (that I am too lazy to look up) that complains about functions in the server that return text data using a binary data type.