Блог

MySQL 5.7.23.

IFNULL(INET_ATON(''),0) возвращает 0 при обычном выборе, но ОШИБКИ при update ... set назначении

Вопрос: Существует ли исправление, которое предотвращает ERROR 1411 (HY000): Incorrect string value:

Инструкция update генерируется более крупным приложением, и было бы сложно модифицировать приложение.

 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)
  

Обновление работает, реальная строка ip

 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
  

Обычный выбор с IFNULL возвращает желаемый 0

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

Сбой обновления с IFNULL и пустой строкой ip для INET_ATON

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

Сбой обновления с IFNULL и повторный цикл IPv6 addr для 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
  

Фактически, пустая строка является недопустимым аргументом в обоих случаях. Вы можете увидеть, что это предупреждение, если просмотрите предупреждения:

 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
  

Я не уверен, почему это только предупреждение при использовании SELECT, но ошибка при использовании той же функции в UPDATE.

Обходной путь заключается в использовании NULL вместо пустой строки. INET_ATON() возвращает NULL без предупреждения или ошибки, если вы передаете NULL в качестве строки вашего IP-адреса:

 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
  

Я понимаю, вы сказали, что трудно изменить приложение, но это единственное обходное решение, которое я могу предложить.

Конечно, лучшее решение — избегать передачи любой недопустимой строки в INET_ATON ().

Спасибо за счет insight.

Оказывается, в приложении есть только дюжина мест, где INET_ATON используется, поэтому я собираюсь попросить администратора разрешить мне изменить их на, APP_INET_ATON которые будут вызывать system INET_ATON только тогда, когда IP_STRING является «законным».

 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;
  

Выяснение того, откуда берутся «неправильные» значения IP_STRING, займет другой день.