radius sql ошибка

[Sidius]

Код:

rlm_sql (sql): Reserving sql socket id: 27
rlm_sql (sql): Released sql socket id: 31
Sending Accounting-Response of id 73 to 127.0.0.1 port 23746
rlm_sql_mysql: MYSQL check_error: 1213 received
rlm_sql (sql): Couldn't update SQL accounting ALIVE record - Deadlock found when trying to get lock; try restarting transaction
rlm_sql_mysql: MYSQL check_error: 1213 received
rlm_sql_mysql: Cannot store result
rlm_sql_mysql: MySQL error 'Deadlock found when trying to get lock; try restarting transaction'
rlm_sql (sql): Released sql socket id: 28
rlm_sql (sql): Released sql socket id: 30
Sending Accounting-Response of id 208 to 127.0.0.1 port 22213
rlm_sql (sql): Released sql socket id: 29
Sending Accounting-Response of id 115 to 127.0.0.1 port 31041
rlm_sql (sql): Released sql socket id: 27

Из-за чего это может происходить?

при этом в логе мпд выпадает

Код:

Aug  6 23:45:29 access mpd: [vlan41-202] RADIUS: rad_send_request for user 'bodya' failed: No valid RADIUS responses received
Aug  6 23:45:30 access mpd: [vlan39-129] RADIUS: rad_send_request for user 'zg-010' failed: No valid RADIUS responses received
Aug  6 23:45:30 access mpd: [vlan5-170] RADIUS: rad_send_request for user 'IVAN' failed: No valid RADIUS responses received

увеличение num_sql_socks в sql.conf радиус сервера эффекта не дает.

[Efendy]

Я думаю надо отталкиваться от Deadlock found when trying to get lock; try restarting transaction
Транзакции не менял?

[Sidius]

Ничего не менял. Походу это как-то связано с моим постом по-поводу мпд и разных ип у клиентов.

[sever]

Периодически в логе радиуса вижу

Код:

Thu Jan 26 09:02:04 2017 : Error: rlm_sql_mysql: MySQL error 'Deadlock found when trying to get lock; try restarting transaction'
Thu Jan 26 09:02:04 2017 : Error: [sql] Couldn't update SQL accounting ALIVE record - Deadlock found when trying to get lock; try restarting transaction
Thu Jan 26 09:02:04 2017 : Error: rlm_sql_mysql: Cannot store result
Thu Jan 26 09:02:04 2017 : Error: rlm_sql_mysql: MySQL error 'Deadlock found when trying to get lock; try restarting transaction'
Thu Jan 26 09:02:04 2017 : Error: [sql] Couldn't update SQL accounting ALIVE record - Deadlock found when trying to get lock; try restarting transaction
Thu Jan 26 09:02:04 2017 : Error: rlm_sql_mysql: Cannot store result
Thu Jan 26 09:02:04 2017 : Error: rlm_sql_mysql: MySQL error 'Deadlock found when trying to get lock; try restarting transaction'

[Efendy]

Я уже писал, что проблема в функции get_ip - mysql не оптимизированно выполняет sql, надо помочь ему:

Код:

DROP FUNCTION IF EXISTS `get_ip`;
DELIMITER $$
CREATE FUNCTION `get_ip` ( user_id INTEGER UNSIGNED )  RETURNS VARCHAR(15) NO SQL
BEGIN
    DECLARE user_ip VARCHAR(15);
    DECLARE real_ip VARCHAR(15);

    SELECT INET_NTOA(ip) INTO user_ip FROM ip_pool
        WHERE uid = user_id AND type='static' LIMIT 1;
    IF( user_ip IS NOT NULL ) THEN RETURN user_ip; END IF;

    UPDATE ip_pool SET uid = user_id, `release` = UNIX_TIMESTAMP() + 300
    WHERE id = (SELECT id FROM (
        (
          SELECT id, uid FROM ip_pool 
          WHERE uid = 0 AND type = 'dynamic' AND realip = IF(real_ip>0,1,0)
          LIMIT 1
        ) UNION (
         SELECT id, uid FROM ip_pool
          WHERE uid = user_id AND type = 'dynamic' AND realip = IF(real_ip>0,1,0)
          LIMIT 1
        )
    ) AS tbl ORDER BY uid DESC LIMIT 1);

    SELECT INET_NTOA(ip) INTO user_ip FROM ip_pool
        WHERE uid = user_id LIMIT 1;
    RETURN user_ip;
END$$
DELIMITER ;

[sever]

Код:

[quote author=Efendy link=topic=2308.msg30719#msg30719 date=1485416444]
Я уже писал, что проблема в функции get_ip - mysql не оптимизированно выполняет sql, надо помочь ему:

Спасибо, fixed.