MySQL INET_ATON error during update, message is “I

Question

MySQL is 5.7.23.

IFNULL(INET_ATON(''),0) returns a 0 in a plain select, but ERRORS during update ... set assignment

Q: Is there a patch that prevents ERROR 1411 (HY000): Incorrect string value:

The update statement is generated by a larger app and it would be hard to modify the app.

mysql> create table foo (id int not null, ip int not null);
Query OK, 0 rows affected (0.21 sec)

mysql> insert into foo values (0,0);
Query OK, 1 row affected (0.26 sec)

Update works, real ip string

mysql> update foo set ip = ifnull(inet_aton('10.10.10.254'), 0) where id=0;
Query OK, 1 row affected (0.25 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Plain select with IFNULL gets desired 0

mysql> select IFNULL(inet_aton(''),0);
+-------------------------+
| IFNULL(inet_aton(''),0) |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

Update fail with IFNULL and empty ip string for INET_ATON

mysql> update foo set ip = ifnull(inet_aton(''), 0) where id=0;
ERROR 1411 (HY000): Incorrect string value: '''' for function inet_aton

Update fail with IFNULL and IPv6 loop back addr for INET_ATON

mysql> update foo set ip = ifnull(inet_aton('::1'), 0) where id=0;
ERROR 1411 (HY000): Incorrect string value: ''::1'' for function inet_aton

Answer 1

In fact, the empty string is an invalid argument in both cases. You can see that it's a warning if you view the warnings:

mysql> warnings;
Show warnings enabled.

mysql> select IFNULL(inet_aton(''),0);
+-------------------------+
| IFNULL(inet_aton(''),0) |
+-------------------------+
|                       0 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 1411): Incorrect string value: '''' for function inet_aton

I'm not sure why this is only a warning when using SELECT but an error when using the same function in UPDATE.

A workaround is to use NULL instead of a blank string. INET_ATON() returns NULL without a warning or error if you pass NULL as your IP address string:

mysql> update foo set ip = ifnull(inet_aton(''), 0) where id=0;
ERROR 1411 (HY000): Incorrect string value: '''' for function inet_aton

mysql> update foo set ip = ifnull(inet_aton(null), 0) where id=0;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

I understand you said it's hard to change the app, but this is the only workaround I can suggestion.

The best solution of course is to avoid passing any invalid string to INET_ATON().

Answer 2

Thanks for the insight Bill.

Turns out there are only a dozen places in the app where INET_ATON is used, so I'm going to push admin to let me change those to APP_INET_ATON which will only call system INET_ATON when the IP_STRING is 'legitimate'.

DROP FUNCTION IF EXISTS APP_INET_ATON;
CREATE FUNCTION APP_INET_ATON(IP_STRING VARCHAR(50)) RETURNS bigint DETERMINISTIC
  RETURN 
    CASE 
      WHEN IP_STRING IS NULL THEN 0
      WHEN IP_STRING NOT REGEXP '^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}$' THEN 0
      ELSE INET_ATON (IP_STRING)
    END;

Figuring out where the 'bad' IP_STRING values are coming from is for another day.