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.