Description:
Getting unexpected error when using INET6_ATON with IPv4 address.
The documentation (https://dev.mysql.com/doc/refman/8.0/en/miscellaneous-functions.html#function_inet6-aton) states the following:
"Given an IPv6 or IPv4 network address as a string, returns a binary string that represents the numeric value of the address in network byte order (big endian)."
We are using the query below on a MySQL 5.7 based cluster. The same query on MySQL 8.0 the following error: "ERROR 1366 (HY000): Incorrect integer value: 'W\xD6b;' for column 'ip' at row 1"
mysql> INSERT INTO `session` (`ip`) VALUES (INET6_ATON('198.51.8.1'));
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 5.7.40 |
+-----------+
How to repeat:
mysql> SHOW CREATE TABLE session;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| session | CREATE TABLE `session` (
`session_id` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '',
`session_data` mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
`expires` int NOT NULL DEFAULT '0',
`ttl` int NOT NULL DEFAULT '1800',
`ip` int unsigned NOT NULL,
PRIMARY KEY (`session_id`,`ttl`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO `session` (`ip`) VALUES (INET6_ATON('198.51.8.1'));
ERROR 1366 (HY000): Incorrect integer value: '\xC63\x08\x01' for column 'ip' at row 1
mysql> SELECT @@version;
+-----------+
| @@version |
+-----------+
| 8.0.32 |
+-----------+
Suggested fix:
MySQL 5.7 is processing IPv4 addresses using INET6_ATON. MySQL 8.0 is giving an error. I would expect MySQL 8.0 to process IPv4 addresses using INET6_ATON.
