Deadlock

[Maks]

Обновили + на NEXT и перенесли работать под VM на базе proxmox.

Решил посмотреть в лог радиуса и там заметил записи:

Код:

Deadlock found when trying to get lock; try restarting transaction

Авторизации проходят, глюков не заметили.

Подскажите, в чем может быть причина Deadlock и стоит ли волноваться ?

Код:

| InnoDB |      | 
=====================================
2021-11-01 11:51:23 0x93f340700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 40 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 92389 srv_active, 0 srv_shutdown, 54 srv_idle
srv_master_thread log flush and writes: 92443
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 5541118
OS WAIT ARRAY INFO: signal count 5730755
RW-shared spins 0, rounds 2708391, OS waits 789633
RW-excl spins 0, rounds 124594692, OS waits 834675
RW-sx spins 3665545, rounds 98289045, OS waits 1616329
Spin rounds per wait: 2708391.00 RW-shared, 124594692.00 RW-excl, 26.81 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-11-01 11:50:45 0x93e386600
*** (1) TRANSACTION:
TRANSACTION 659162265, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 48084, OS thread handle 39715082496, query id 181416906 localhost nodeny Searching rows for update
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)
            LIMIT 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1073 page no 507 n bits 1000 index uid of table `nodeny`.`ip_pool` trx id 659162265 lock_mode X waiting
Record lock, heap no 449 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 0000000000000000; asc         ;;
 1: len 4; hex 00000004; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 659162263, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1136, 386 row lock(s), undo log entries 1
MySQL thread id 47609, OS thread handle 39698589184, query id 181416897 localhost nodeny updating
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)
            LIMIT 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1073 page no 507 n bits 1000 index uid of table `nodeny`.`ip_pool` trx id 659162263 lock_mode X
Record lock, heap no 449 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 0000000000000000; asc         ;;
 1: len 4; hex 00000004; asc     ;;

Record lock, heap no 451 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 0000000000000000; asc         ;;
 1: len 4; hex 00000009; asc     ;;


и так далее до 


Record lock, heap no 721 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 0000000000000000; asc         ;;
 1: len 4; hex 000001e1; asc     ;;

Record lock, heap no 769 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 8; hex 0000000000000000; asc         ;;
 1: len 4; hex 00001ff1; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1073 page no 730 n bits 1000 index uid of table `nodeny`.`ip_pool` trx id 659162263 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 722 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 00000000000027d8; asc       ' ;;
 1: len 4; hex 00001631; asc    1;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 659189013
Purge done for trx's n:o < 659187785 undo n:o < 0 state: running but idle
History list length 530
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281509884874504, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509884878120, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509884882640, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509884880832, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509884876312, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509884873600, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509884879024, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509884884448, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509884872696, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509884877216, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509884879928, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509884875408, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509884871792, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (read thread)
I/O thread 7 state: waiting for i/o request (read thread)
I/O thread 8 state: waiting for i/o request (read thread)
I/O thread 9 state: waiting for i/o request (read thread)
I/O thread 10 state: waiting for i/o request (write thread)
I/O thread 11 state: waiting for i/o request (write thread)
I/O thread 12 state: waiting for i/o request (write thread)
I/O thread 13 state: waiting for i/o request (write thread)
I/O thread 14 state: waiting for i/o request (write thread)
I/O thread 15 state: waiting for i/o request (write thread)
I/O thread 16 state: waiting for i/o request (write thread)
I/O thread 17 state: waiting for i/o request (write thread)
Pending normal aio reads: [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
70121 OS file reads, 14797413 OS file writes, 14684844 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 173.82 writes/s, 173.82 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 72, seg size 74, 3986 merges
merged operations:
 insert 7439, delete mark 898, delete 214
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 1106311, node heap has 54 buffer(s)
Hash table size 1106311, node heap has 75 buffer(s)
Hash table size 1106311, node heap has 43 buffer(s)
Hash table size 1106311, node heap has 24 buffer(s)
Hash table size 1106311, node heap has 4460 buffer(s)
Hash table size 1106311, node heap has 30 buffer(s)
Hash table size 1106311, node heap has 2673 buffer(s)
Hash table size 1106311, node heap has 44 buffer(s)
11306.34 hash searches/s, 2921.50 non-hash searches/s
---
LOG
---
Log sequence number 156519175267
Log flushed up to   156519175132
Pages flushed up to 156363911047
Last checkpoint at  156363911047
0 pending log flushes, 0 pending chkp writes
14657859 log i/o's done, 173.82 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 4397727744
Dictionary memory allocated 606886
Buffer pool size   262122
Free buffers       182455
Database pages     72264
Old database pages 26667
Modified db pages  1157
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 176, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 70039, created 2225, written 130776
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 72264, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   32767
Free buffers       22951
Database pages     8905
Old database pages 3288
Modified db pages  146
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 28, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 8630, created 275, written 14459
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 8905, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   32768
Free buffers       22888
Database pages     8939
Old database pages 3300
Modified db pages  75
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 25, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 8633, created 306, written 13778
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 8939, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   32764
Free buffers       22798
Database pages     9026
Old database pages 3313
Modified db pages  76
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 34, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 8759, created 267, written 14527
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 9026, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   32764
Free buffers       22686
Database pages     9167
Old database pages 3365
Modified db pages  204
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 34, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 8897, created 270, written 22655
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 9167, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   32766
Free buffers       22646
Database pages     9187
Old database pages 3380
Modified db pages  103
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 21, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 9042, created 145, written 14075
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 9187, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   32764
Free buffers       22669
Database pages     9165
Old database pages 3384
Modified db pages  116
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 8838, created 327, written 14432
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 9165, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   32764
Free buffers       23144
Database pages     8682
Old database pages 3224
Modified db pages  193
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 11, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 8427, created 255, written 16232
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 8682, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   32765
Free buffers       22673
Database pages     9193
Old database pages 3413
Modified db pages  244
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 19, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 8813, created 380, written 20618
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 9193, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=15923, Main thread ID=39828897792, state: sleeping
Number of rows inserted 110076506, updated 14465305, deleted 203636, read 3171617681
1298.44 inserts/s, 175.72 updates/s, 0.02 deletes/s, 23364.79 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
 |

Все правки в базе которые делали для переноса описаны тут:
http://forum.nodeny.com.ua/index.php?topic=3302.msg37084#msg37084

[Efendy]

Похоже у тебя старые процедуры/функции mysql используются. Надо выяснить какие устарели (или все). Список твоих процедур:

Код:

SHOW PROCEDURE STATUS WHERE db = 'nodeny';

nodeny - имя базы данных.

Но скорее нужно начать с функций:

Код:

SHOW FUNCTION STATUS WHERE db = 'nodeny';

Ну и  дальше по списку функций:

Код:

SHOW CREATE FUNCTION get_ip;

и сравниваешь с тем, что в /usr/local/nodeny/bill.sql

p.s. посмотрел, да, скорее всего как раз надо обновить get_ip (за всем не уследишь, позже обновлялку обновлю). Заодно проверь функцию normalize_ippool - она очень важная, сортирует ip таким образом чтоб  get_ip выбирал свободный ip  без дедлоков

[Maks]

Сравнил normalize_ippool он как нужно,  но вот get_ip  отличается сильно.

Такой у меня (он остался с +)

Код:

| get_ip   | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` FUNCTION `get_ip`( user_id INTEGER UNSIGNED ) RETURNS varchar(15) CHARSET utf8
    NO SQL
BEGIN
    DECLARE user_ip VARCHAR(15);
    DECLARE real_ip VARCHAR(15);
    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;

    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)
        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)
            LIMIT 1;
    END IF;

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

Вот новый для NEXT

Код:

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) DEFAULT 0;
    DECLARE row_cnt INTEGER;
    DECLARE ip_id INTEGER;
    DECLARE tries INTEGER DEFAULT 30;
    DECLARE id_min INTEGER;
    DECLARE id_max 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,%';

    SELECT id, INET_NTOA(ip) INTO ip_id, user_ip FROM ip_pool
        WHERE uid = user_id AND type = 'dynamic' AND realip = IF(real_ip>0,1,0)
        LIMIT 1;


    IF( ip_id IS NOT NULL) THEN
        UPDATE ip_pool SET `release` = UNIX_TIMESTAMP() + 3600
            WHERE id = ip_id AND uid = user_id;
        SELECT ROW_COUNT() INTO row_cnt;
        IF( row_cnt > 0 ) THEN RETURN user_ip; END IF;
        UPDATE ip_pool SET `release` = UNIX_TIMESTAMP() + 3600
            WHERE uid = user_id AND type = 'dynamic' AND realip = IF(real_ip>0,1,0);
        SELECT ROW_COUNT() INTO row_cnt;
        IF( row_cnt > 0 ) THEN RETURN user_ip; END IF;
    END IF;

    SELECT MAX(id), MIN(id) INTO id_max, id_min
        FROM ip_pool
        WHERE type = 'dynamic' AND realip = IF(real_ip>0,1,0);

    sel_ip: WHILE tries > 0 DO
        SELECT id, INET_NTOA(ip) INTO ip_id, user_ip
            FROM ip_pool
            WHERE uid = 0
                AND id >= (CEIL(RAND() * (id_max - id_min)) + id_min)
                AND id <= id_max
                AND `release` < UNIX_TIMESTAMP()
                LIMIT 1;
        IF( user_ip IS NOT NULL) THEN
            UPDATE ip_pool SET uid = user_id, `release` = UNIX_TIMESTAMP() + 3600
                WHERE id = ip_id AND uid = 0 AND `release` < UNIX_TIMESTAMP();
            SELECT ROW_COUNT() INTO row_cnt;
            IF( row_cnt > 0 ) THEN RETURN user_ip; END IF;
            SET tries = tries - 5;
        END IF;
        SET tries = tries - 1;
    END WHILE;

Хотел уточнить, после замены функции, все должно работать ? или может что-то нужно еще править.
И я вроде как не первый кто обновился, странно, что никто об этом ранее не написал.

[Efendy]

Не написали, потому что и та и та функция рабочие и делают одно и тоже, только разными способами. Но новая защищена (по крайней мере намного лучше чем старая) от дедлоков.
После замены функции все должно работать ибо как я только что сказал - функции делают одно и тоже

[Maks]

Сменил функцию get_ip  и добавил set_auth

Дедлоки ушли, но сейчас в логе увидел

Код:

Wed Nov  3 10:36:07 2021 : ERROR: (48018356) sql: ERROR: rlm_sql_mysql: ERROR 1321 (FUNCTION get_ip ended without RETURN): 2F005

Также в логе есть записи, но если я понял они есть у всех с "коробки":

Код:

Wed Nov  3 10:36:49 2021 : ERROR: (48021589) sql: ERROR: rlm_sql_mysql: ERROR 1048 (Column 'uid' cannot be null): 23000
Wed Nov  3 10:37:13 2021 : WARNING: (48023432) sql: WARNING: Cannot do check groups when group_membership_query is not set

И заметил странную ситуацию, что за одним клиентом теперь может числиться более одного IP. Скрин приложил.
Это возможно было и ранее, но так как они уходили через 2 минуты было незаметно.

[Maks]

У нас qinq и 4 наса обслуживают все vlanы. Т.е абон подключается одновременно ко всем насам.

Но тут вышла ситуация когда подключение было в одному насу, но нас взял IP х.х.198.32,  а вот биллинг запомнил другой.

Сейчас из-за этого количество дублей начало только стремительно увеличиваться.


НАС
 

Код:

stat.3002.1300 | stat.3002.1300 | bc:ae:c5:5e:01:e6 |х.х.198.32   | 22024/22024 | ipoe |      | active | 01:11:37  

[Maks]

Вернул старую функцию, опять есть deadlock, но зато дублей IP нет.  Правда в старой функции заменил 300 на 3600, чтоб IP абонов не часто менялись.

Код:

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);
    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;

    UPDATE ip_pool SET uid = user_id, `release` = UNIX_TIMESTAMP() + 3600
        WHERE uid = user_id AND type = 'dynamic' AND realip = IF(real_ip>0,1,0)
        LIMIT 1;

    SELECT ROW_COUNT() INTO row_cnt;
    IF( row_cnt < 1 )
    THEN
        UPDATE ip_pool SET uid = user_id, `release` = UNIX_TIMESTAMP() + 3600
            WHERE uid = 0 AND type = 'dynamic' AND realip = IF(real_ip>0,1,0)
            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 ;

В предыдущем случае самое плохое, что было, это в карточке клиента светилось 3 авторизации, и правильная авторизация была от нужного NAS, но IP был не верный.
Т.е на NAS один IP в биллинге с зеленым ключиком другой.
А тот что на NAS через час выдавался другому клиенту и дубли копились как снежный комп.

Пробовал функции которые тут
http://forum.nodeny.com.ua/index.php?topic=3034.msg32300#msg32300
результат аналогичный.


Может быть авторизация где все хорошо, т.е выдается одному клиенту 1 IP
Может где одному клиенту 2 ip, но второй не правильный без ключа и он освободится через 1 час.
Но самое плохое когда выдаёт 3 IP и остается с ключём неправильный IP

[Maks]

Если кому мешает это:

Код:

WARNING: (123484726) sql: WARNING: Cannot do check groups when group_membership_query is not set

то нужно в файл
/usr/local/etc/raddb/mods-enabled/sql

в блок sql добавить  read_groups = no

----------------------------

Возможно у кого есть еще идеи по какой причине новая функция get_ip которая без deadlock закрепляет за клиентом сразу несколько IP и как это можно побороть.

[veca16]

Добрый вечер! Столкнулся с дедлоком на ippool но ситуация немного другая, до этого работал dhcp модуль на freebsd, клиентов онлайн гдето 2500 в пике но решиши переходить на accel-ipoe. Подключили отдельным насом accel-ipoe на debian все было нормально пока нагрузка была до 1 Гига и гдето 800 клиентов. Но на днях решили перейти полностью на accel-ipoe, Debian справляется с нагрузкой в 4Гига на ура, но вот с базой всплыл дедлок который пока не могу решить.

Код:

*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2021-11-23 22:17:47 0x103b318400 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 16 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 7959461 srv_active, 0 srv_shutdown, 6558 srv_idle
srv_master_thread log flush and writes: 7966019
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2462433
OS WAIT ARRAY INFO: signal count 76174368
RW-shared spins 0, rounds 28611375, OS waits 518541
RW-excl spins 0, rounds 1136312466, OS waits 271132
RW-sx spins 152589985, rounds 794750727, OS waits 998241
Spin rounds per wait: 28611375.00 RW-shared, 1136312466.00 RW-excl, 5.21 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-11-23 22:17:28 0x1037682200
*** (1) TRANSACTION:
TRANSACTION 5235292920, ACTIVE 157 sec starting index read
mysql tables in use 2, locked 1
LOCK WAIT 98 lock struct(s), heap size 24784, 636 row lock(s), undo log entries 380
MySQL thread id 20778661, OS thread handle 69649049344, query id 5511644405 localhost nodeny updating
UPDATE ip_pool SET `release` = UNIX_TIMESTAMP() + 3600
            WHERE id = ip_id AND uid = user_id
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 84 page no 154 n bits 392 index PRIMARY of table `nodeny`.`ip_pool` trx id 5235292920 lock_mode X locks rec but not gap waiting
Record lock, heap no 204 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 00000c37; asc    7;;
 1: len 6; hex 0001380c4cfb; asc   8 L ;;
 2: len 7; hex 3a000002ac1674; asc :     t;;
 3: len 4; hex 0a00069f; asc     ;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 619d5a07; asc a Z ;;
 7: len 8; hex 000000000000019f; asc         ;;
 8: len 8; hex 2c766c616e35362c; asc ,vlan56,;;

*** (2) TRANSACTION:
TRANSACTION 5235291913, ACTIVE 169 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 2, locked 1
100 lock struct(s), heap size 24784, 1077 row lock(s), undo log entries 652
MySQL thread id 20778679, OS thread handle 69649048064, query id 5511678319 localhost nodeny updating
UPDATE ip_pool SET `release` = UNIX_TIMESTAMP() + 3600
            WHERE id = ip_id AND uid = user_id
*** (2) TRANSACTION:
TRANSACTION 5235291913, ACTIVE 169 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 2, locked 1
100 lock struct(s), heap size 24784, 1077 row lock(s), undo log entries 652
MySQL thread id 20778679, OS thread handle 69649048064, query id 5511678319 localhost nodeny updating
UPDATE ip_pool SET `release` = UNIX_TIMESTAMP() + 3600
            WHERE id = ip_id AND uid = user_id
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 84 page no 154 n bits 392 index PRIMARY of table `nodeny`.`ip_pool` trx id 5235291913 lock_mode X locks rec but not gap
Record lock, heap no 203 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 00000c36; asc    6;;
 1: len 6; hex 0001380c3709; asc   8 7 ;;
 2: len 7; hex 2c000001b20ed0; asc ,      ;;
 3: len 4; hex 0a00061d; asc     ;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 619d5a67; asc a Zg;;
 7: len 8; hex 0000000000000ba8; asc         ;;
 8: len 8; hex 2c766c616e35362c; asc ,vlan56,;;

Record lock, heap no 204 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 00000c37; asc    7;;
 1: len 6; hex 0001380c4cfb; asc   8 L ;;
 2: len 7; hex 3a000002ac1674; asc :     t;;
 3: len 4; hex 0a00069f; asc     ;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 619d5a07; asc a Z ;;
 7: len 8; hex 000000000000019f; asc         ;;
 8: len 8; hex 2c766c616e35362c; asc ,vlan56,;;

Record lock, heap no 215 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 00000c42; asc    B;;
 1: len 6; hex 0001380c3709; asc   8 7 ;;
 2: len 7; hex 2c000001af279c; asc ,    ' ;;
 3: len 4; hex 0a0006f9; asc     ;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 619d5a05; asc a Z ;;
 7: len 8; hex 0000000000000bca; asc         ;;
 8: len 8; hex 2c766c616e35362c; asc ,vlan56,;;

Record lock, heap no 252 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 00000c67; asc    g;;
 1: len 6; hex 0001380c3709; asc   8 7 ;;
 2: len 7; hex 2c000001911ed3; asc ,      ;;
 3: len 4; hex 0a000674; asc    t;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 619d5a65; asc a Ze;;
 7: len 8; hex 0000000000000712; asc         ;;
 8: len 8; hex 2c766c616e35362c; asc ,vlan56,;;
Record lock, heap no 287 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 00000c8a; asc     ;;
 1: len 6; hex 0001380c3709; asc   8 7 ;;
 2: len 7; hex 2c0000018b2d37; asc ,    -7;;
 3: len 4; hex 0a00069a; asc     ;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 619d5a52; asc a ZR;;
 7: len 8; hex 0000000000000a3c; asc        <;;
 8: len 8; hex 2c766c616e35362c; asc ,vlan56,;;

Record lock, heap no 291 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 00000c8e; asc     ;;
 1: len 6; hex 0001380c3709; asc   8 7 ;;
 2: len 7; hex 2c0000018b356c; asc ,    5l;;
 3: len 4; hex 0a0006af; asc     ;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 619d5a54; asc a ZT;;
 7: len 8; hex 0000000000000e36; asc        6;;
 8: len 8; hex 2c766c616e35362c; asc ,vlan56,;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 84 page no 12 n bits 264 index PRIMARY of table `nodeny`.`ip_pool` trx id 5235291913 lock_mode X locks rec but not gap waiting
Record lock, heap no 110 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 0000068d; asc     ;;
 1: len 6; hex 0001380c3af8; asc   8 : ;;
 2: len 7; hex 4e000002ba0865; asc N     e;;
 3: len 4; hex 0a010176; asc    v;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 619d5a04; asc a Z ;;
 7: len 8; hex 0000000000000dba; asc         ;;
 8: len 9; hex 2c766c616e3430312c; asc ,vlan401,;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 5235303911
Purge done for trx's n:o < 5235298554 undo n:o < 0 state: running but idle
History list length 2301
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281509535383984, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509535380368, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509535357768, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509535374944, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509535385792, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509535370424, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509535359576, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 281509535365000, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 5235303423, ACTIVE 9 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 20778694, OS thread handle 69649041664, query id 5511690085 localhost nodeny Sending data
SELECT INET_NTOA(ip) INTO usr_ip FROM ip_pool
            WHERE uid=0 AND type='dynamic' AND `release` < UNIX_TIMESTAMP()
            AND tags LIKE CONCAT('%,', tag ,',%')
            ORDER BY RAND() LIMIT 1 FOR UPDATE
------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 84 page no 85 n bits 984 index uid of table `nodeny`.`ip_pool` trx id 5235303423 lock_mode X waiting
Record lock, heap no 730 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 0000000000000000; asc         ;;
 1: len 4; hex 00000003; asc     ;;

------------------
---TRANSACTION 5235303212, ACTIVE 12 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 20778608, OS thread handle 70605841152, query id 5511686919 localhost nodeny Sending data
SELECT INET_NTOA(ip) INTO usr_ip FROM ip_pool
            WHERE uid=0 AND type='dynamic' AND `release` < UNIX_TIMESTAMP()
            AND tags LIKE CONCAT('%,', tag ,',%')
            ORDER BY RAND() LIMIT 1 FOR UPDATE
------- TRX HAS BEEN WAITING 12 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 84 page no 85 n bits 984 index uid of table `nodeny`.`ip_pool` trx id 5235303212 lock_mode X waiting
Record lock, heap no 730 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 0000000000000000; asc         ;;
 1: len 4; hex 00000003; asc     ;;

------------------
---TRANSACTION 5235302935, ACTIVE 16 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 20778688, OS thread handle 69712572416, query id 5511682638 localhost nodeny Sending data
SELECT INET_NTOA(ip) INTO usr_ip FROM ip_pool
            WHERE uid=0 AND type='dynamic' AND `release` < UNIX_TIMESTAMP()
            AND tags LIKE CONCAT('%,', tag ,',%')
            ORDER BY RAND() LIMIT 1 FOR UPDATE
------- TRX HAS BEEN WAITING 16 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 84 page no 85 n bits 984 index uid of table `nodeny`.`ip_pool` trx id 5235302935 lock_mode X waiting
Record lock, heap no 730 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 0000000000000000; asc         ;;
 1: len 4; hex 00000003; asc     ;;

------------------
---TRANSACTION 5235302753, ACTIVE 17 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 20778676, OS thread handle 69712576256, query id 5511680596 localhost nodeny Sending data
SELECT INET_NTOA(ip) INTO usr_ip FROM ip_pool
            WHERE uid=0 AND type='dynamic' AND `release` < UNIX_TIMESTAMP()
            AND tags LIKE CONCAT('%,', tag ,',%')
            ORDER BY RAND() LIMIT 1 FOR UPDATE
------- TRX HAS BEEN WAITING 17 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 84 page no 85 n bits 984 index uid of table `nodeny`.`ip_pool` trx id 5235302753 lock_mode X waiting
Record lock, heap no 730 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 0000000000000000; asc         ;;
 1: len 4; hex 00000003; asc     ;;

Если я правильно понимаю то изза этой функции у меня лочится

Код:

CREATE DEFINER=`root`@`localhost` FUNCTION `get_ip_by_tag`( user_id INTEGER UNSIGNED, tag VARCHAR(64) ) RETURNS varchar(15) CHARSET utf8
    NO SQL
BEGIN
    DECLARE user_ip VARCHAR(15);
    DECLARE real_ip VARCHAR(15) DEFAULT 0;
    DECLARE row_cnt INTEGER;
    DECLARE ip_id INTEGER;
    DECLARE tries INTEGER DEFAULT 30;
    DECLARE id_min INTEGER;
    DECLARE id_max 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,%';

    SELECT id, INET_NTOA(ip) INTO ip_id, user_ip 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;

    IF( ip_id IS NOT NULL) THEN
        UPDATE ip_pool SET `release` = UNIX_TIMESTAMP() + 3600
            WHERE id = ip_id AND uid = user_id;
        SELECT ROW_COUNT() INTO row_cnt;
        IF( row_cnt > 0 ) THEN RETURN user_ip; END IF;
    END IF;

    SELECT MAX(id), MIN(id) INTO id_max, id_min
        FROM ip_pool
        WHERE type = 'dynamic' AND realip = IF(real_ip>0,1,0)
            AND tags LIKE CONCAT('%,', tag, ',%');

    sel_ip: WHILE tries > 0 DO
        SELECT id, INET_NTOA(ip) INTO ip_id, user_ip
            FROM ip_pool
            WHERE uid = 0
                AND id >= (CEIL(RAND() * (id_max - id_min)) + id_min)
                AND id <= id_max
                LIMIT 1;
        IF( user_ip IS NOT NULL) THEN
            UPDATE ip_pool SET uid = user_id, `release` = UNIX_TIMESTAMP() + 3600
                WHERE id = ip_id AND uid = 0;
            SELECT ROW_COUNT() INTO row_cnt;
            IF( row_cnt > 0 ) THEN RETURN user_ip; END IF;
            SET tries = tries - 5;
        END IF;
        SET tries = tries - 1;
    END WHILE;

END

База стоит на машине с Freebsd 12.1 Release, Mysql 5.7
Если кто знает как можно поправить буду очень благодарен.
Заранее спасибо!