Запрос mysql

[kosmich]

Здравствуйте.

Несколько серверов доступа.
Используем измененные процедуры выдача ip по тегу с привязкой пула к серверу доступа:

Код:

CREATE DEFINER=`root``localhost` PROCEDURE `radcheck`(IN login VARCHAR(64))
BEGIN
  SELECT id,name,'Password' AS Attribute,AES_DECRYPT(passwd,'hardpass') AS Value,'=='
    FROM users WHERE name=login;
END ;;
DELIMITER ;

CREATE DEFINER=`root`@`localhost` PROCEDURE `radreply`(IN login VARCHAR, tag VARCHAR)
BEGIN
  DECLARE usr_id INT;
  DECLARE usr_ip VARCHAR DEFAULT NULL;

  SELECT id INTO usr_id FROM users WHERE name=login LIMIT 1;
  SELECT get_ip_by_tag(usr_id, tag) INTO usr_ip;


  SELECT NULL,login,'Framed-IP-Address',usr_ip,'=';
  SELECT NULL,login,'Framed-IP-Netmask','255.255.255.255','=';
  SELECT NULL,login,'Framed-Protocol','PPP','=';
END ;;


CREATE DEFINER=`root``localhost` PROCEDURE `radstop`(IN login VARCHAR(64))
BEGIN
  DECLARE usr_id INT;
  SELECT id INTO usr_id FROM users WHERE name=login LIMIT 1;
  DELETE FROM auth_now WHERE ip = get_ip(usr_id) LIMIT 1;
END ;;

CREATE DEFINER=`root``localhost` PROCEDURE `radupdate`(IN login VARCHAR(64), IN ip VARCHAR(16), IN properties VARCHAR(255), IN tag VARCHAR(64))
BEGIN
  DECLARE usr_id INT;
  DECLARE usr_ip VARCHAR(15) DEFAULT NULL;
  SELECT id INTO usr_id FROM users WHERE name=login LIMIT 1;
  SELECT get_ip_by_tag(usr_id, tag) INTO usr_ip;
  CALL set_auth(usr_ip, CONCAT('mod=pppoe;',REPLACE(properties,':','')));
END ;;

При определенных условиях, вижу:

Код:

show processlist;
| 56 | nodeny | localhost | nodeny | Query | 1023 | updating | UPDATE ip_pool SET uid=0 WHERE type='dynamic' AND uid>0 AND `release`<UNIX_TIMESTAMP() AND id='4474' | 

Запрос "висит", время идет, новые PPPoE сессии не поднимаются.
В логах мпд начинает валиться пачками No valid RADIUS responses received 
Радиус видит "дублирующиеся" запросы от мпд и морозится.
Все вышеописанное воспроизводится.


Кто виноват и что делать ?

[Efendy]

Покажи get_ip_by_tag и set_auth - что-то из этого я переделывал ибо mysql туговат и впадает в deadlock на элементарных условиях

[gudwin]

Здравствуйте.

Несколько серверов доступа.
Используем измененные процедуры выдача ip по тегу с привязкой пула к серверу доступа:

Код:

CREATE DEFINER=`root``localhost` PROCEDURE `radcheck`(IN login VARCHAR(64))
BEGIN
  SELECT id,name,'Password' AS Attribute,AES_DECRYPT(passwd,'hardpass') AS Value,'=='
    FROM users WHERE name=login;
END ;;
DELIMITER ;

CREATE DEFINER=`root`@`localhost` PROCEDURE `radreply`(IN login VARCHAR, tag VARCHAR)
BEGIN
  DECLARE usr_id INT;
  DECLARE usr_ip VARCHAR DEFAULT NULL;

  SELECT id INTO usr_id FROM users WHERE name=login LIMIT 1;
  SELECT get_ip_by_tag(usr_id, tag) INTO usr_ip;


  SELECT NULL,login,'Framed-IP-Address',usr_ip,'=';
  SELECT NULL,login,'Framed-IP-Netmask','255.255.255.255','=';
  SELECT NULL,login,'Framed-Protocol','PPP','=';
END ;;


CREATE DEFINER=`root``localhost` PROCEDURE `radstop`(IN login VARCHAR(64))
BEGIN
  DECLARE usr_id INT;
  SELECT id INTO usr_id FROM users WHERE name=login LIMIT 1;
  DELETE FROM auth_now WHERE ip = get_ip(usr_id) LIMIT 1;
END ;;

CREATE DEFINER=`root``localhost` PROCEDURE `radupdate`(IN login VARCHAR(64), IN ip VARCHAR(16), IN properties VARCHAR(255), IN tag VARCHAR(64))
BEGIN
  DECLARE usr_id INT;
  DECLARE usr_ip VARCHAR(15) DEFAULT NULL;
  SELECT id INTO usr_id FROM users WHERE name=login LIMIT 1;
  SELECT get_ip_by_tag(usr_id, tag) INTO usr_ip;
  CALL set_auth(usr_ip, CONCAT('mod=pppoe;',REPLACE(properties,':','')));
END ;;

При определенных условиях, вижу:

Код:

show processlist;
| 56 | nodeny | localhost | nodeny | Query | 1023 | updating | UPDATE ip_pool SET uid=0 WHERE type='dynamic' AND uid>0 AND `release`<UNIX_TIMESTAMP() AND id='4474' | 

Запрос "висит", время идет, новые PPPoE сессии не поднимаются.
В логах мпд начинает валиться пачками No valid RADIUS responses received 
Радиус видит "дублирующиеся" запросы от мпд и морозится.
Все вышеописанное воспроизводится.


Кто виноват и что делать ?

у тебя много саттелитов стоит, ты уверен что один и тот же пул не используется на 2 разных насах

[kosmich]

Покажи get_ip_by_tag и set_auth - что-то из этого я переделывал ибо mysql туговат и впадает в deadlock на элементарных условиях

Код:

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `get_ip_by_tag`( user_id INTEGER UNSIGNED, tag VARCHAR ) RETURNS varchar(15) CHARSET utf8
    NO SQL
BEGIN
    DECLARE user_ip VARCHAR;
    DECLARE real_ip VARCHAR;
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;
SELECT 1 INTO real_ip FROM users_services WHERE uid = user_id AND tags LIKE '%,realip,%';
    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)
          AND tags LIKE CONCAT('%,', tag, ',%')
          LIMIT 1
        ) UNION (
         SELECT id, uid FROM ip_pool
          WHERE uid = user_id AND type = 'dynamic' AND realip = IF(real_ip>0,1,0)
          AND tags LIKE CONCAT('%,', tag, ',%')
          LIMIT 1
        )
    ) AS tbl ORDER BY uid DESC LIMIT 1);
    SELECT INET_NTOA(ip) INTO user_ip FROM ip_pool
        WHERE uid = user_id AND tags LIKE CONCAT('%,', tag, ',%') LIMIT 1;
    RETURN user_ip;
END ;;
DELIMITER ;

Код:

DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `set_auth`(IN usr_ip VARCHAR, IN auth_properties VARCHAR)
BEGIN
  DECLARE usr_id INT;
  SELECT uid INTO usr_id FROM ip_pool WHERE INET_ATON(usr_ip) = ip LIMIT 1;
IF( usr_id > 0 ) THEN
INSERT INTO auth_now SET
        ip = usr_ip,
        properties = auth_properties,
        start = UNIX_TIMESTAMP(),
        last = UNIX_TIMESTAMP()
    ON DUPLICATE KEY UPDATE
        properties = IF(auth_properties!='',auth_properties,properties),
        last = UNIX_TIMESTAMP();
UPDATE ip_pool SET `release` = UNIX_TIMESTAMP() + 300
        WHERE ip = INET_ATON(usr_ip) AND type = 'dynamic' LIMIT 1;
  END IF;
END ;;
DELIMITER ;

[kosmich]

у тебя много саттелитов стоит, ты уверен что один и тот же пул не используется на 2 разных насах

Уверен. Пулы разные к насам "прибиты".

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

[Cell]

 Делай динамическую маршрутизацию между роутерами и вообще проблем ни с чем не будет. Я бгп юзаю для этих дел

[Efendy]

Попробуй эту процедуру:

Код:

DROP FUNCTION IF EXISTS `get_ip_by_tag`;
DELIMITER $$
CREATE FUNCTION `get_ip_by_tag` ( user_id INTEGER UNSIGNED, tag VARCHAR(64) )
    RETURNS VARCHAR(15) NO SQL
BEGIN
    DECLARE user_ip VARCHAR(15);
    DECLARE real_ip VARCHAR(15) DEFAULT 0;
    DECLARE row_cnt INTEGER;

    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;

    SELECT 1 INTO real_ip FROM users_services WHERE uid = user_id AND tags LIKE '%,realip,%';

    UPDATE ip_pool SET uid = user_id, `release` = UNIX_TIMESTAMP() + 300
        WHERE uid = user_id AND type = 'dynamic' AND realip = IF(real_ip>0,1,0)
            AND tags LIKE CONCAT('%,', tag, ',%')
        LIMIT 1;

    SELECT ROW_COUNT() INTO row_cnt;
    IF( row_cnt < 1 )
    THEN
        UPDATE ip_pool SET uid = user_id, `release` = UNIX_TIMESTAMP() + 300
            WHERE uid = 0 AND type = 'dynamic' AND realip = IF(real_ip>0,1,0)
                AND tags LIKE CONCAT('%,', tag, ',%')
            LIMIT 1;
    END IF;

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

END$$
DELIMITER ;

[kosmich]

Код:

| | 119 | root | localhost | nodeny | Query | 0 | System lock | UPDATE ip_pool SET `release` = UNIX_TIMESTAMP() + 300 WHERE ip = INET_ATON(usr_ip) AND type |

Код:

Wed Dec 20 11:49:33 2017 : Error: [sql] SQL query error; rejecting user
Wed Dec 20 11:49:33 2017 : Error: rlm_sql_getvpdata: database query error

Вообще сломалось.
Может посмотришь ?