Потестите. Поможет

[Efendy]

Долгое время постоянно сообщали, что есть некоторые проблемы при работе с динамическим ip. Скорее всего причиной этому были deadlock. Я уже пытался лечить их. Но это все была полумера т.к. я пытался уменьшить вероятность deadlock-ов, а не причину. Кажется, я нашел в чем проблема: в mysql оптимизатор не идеальный и при получении ip он лочил все пустые записи хотя был выставлен limit 1. Для меня было очевидно, что должна лочиться одна запись - а для него нет, поскольку было еще пару условий. Короче. Я сейчас потестил одну ночь и ни одного дедлока не было. Поэтому прош потестить в ваших сетях. Если все будет ок - заменю 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 ;

[Efendy]

 Если вдруг при выполнении кода выше будет ошибка - верните старую процедуру:

Код:

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;

    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 = 0 OR uid = user_id)
            AND type = 'dynamic'
            AND realip = IF(real_ip>0,1,0)
        ORDER BY uid DESC, ip ASC LIMIT 1;

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

[Efendy]

Лана, хрен с вами. Меняю в поставке

[YuSHa]

я попробую через пару дней.... и так народ злой, лишний раз не хотим дергать.... вертолеты, БМД.... стреляют.....надоело

[SeMant]

У меня проблема не исчезла.

[YuSHa]

логи в студию

[SeMant]

логи в студию

какие именно логи демонстрировать?

Код:

140708  8:55:03 [Warning] /usr/local/libexec/mysqld: Forcing close of thread 9  user: 'nodeny1'

140708  8:55:03 [Warning] /usr/local/libexec/mysqld: Forcing close of thread 8  user: 'nodeny'

140708  8:55:03 [Warning] /usr/local/libexec/mysqld: Forcing close of thread 1  user: 'nodeny3'

140708  8:55:03  InnoDB: Starting shutdown...
140708  8:55:04  InnoDB: Shutdown completed; log sequence number 423584779572
140708  8:55:04 [Note] /usr/local/libexec/mysqld: Shutdown complete

140708 08:55:04 mysqld_safe mysqld from pid file /var/db/mysql/hulk.kit.dn.ua.pid ended
140708 08:55:04 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql
140708  8:55:04 InnoDB: The InnoDB memory heap is disabled
140708  8:55:04 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140708  8:55:04 InnoDB: Compressed tables use zlib 1.2.7
140708  8:55:04 InnoDB: Initializing buffer pool, size = 128.0M
140708  8:55:04 InnoDB: Completed initialization of buffer pool
140708  8:55:04 InnoDB: highest supported file format is Barracuda.
140708  8:55:04  InnoDB: Waiting for the background threads to start
140708  8:55:05 InnoDB: 5.5.38 started; log sequence number 423584779572
140708  8:55:05 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
140708  8:55:05 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
140708  8:55:05 [Note] Server socket created on IP: '0.0.0.0'.
140708  8:55:05 [Warning] 'user' entry 'root@hulk' ignored in --skip-name-resolve mode.
140708  8:55:05 [Warning] 'user' entry '@hulk' ignored in --skip-name-resolve mode.
140708  8:55:05 [Warning] 'proxies_priv' entry '@ root@hulk' ignored in --skip-name-resolve mode.
140708  8:55:05 [Note] Event Scheduler: Loaded 0 events
140708  8:55:05 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.5.38-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
140708  9:07:20 [Note] Found 498274 of 498520 rows when repairing './nodeny/X2014_5_29'
140708  9:08:56 [Note] Found 83815 of 83850 rows when repairing './nodeny/X2014_7_6'
140708 12:19:49 [ERROR] Invalid (old?) table or database name 'n+'
140708 12:19:49 [ERROR] Invalid (old?) table or database name 'n+'
140708 12:19:49 [ERROR] Invalid (old?) table or database name 'n+'
140708 12:19:49 [ERROR] Invalid (old?) table or database name 'n+'
140708 12:19:49 [ERROR] Invalid (old?) table or database name 'n+'
140708 12:19:49 [ERROR] Invalid (old?) table or database name 'n+'
140708 12:19:49 [ERROR] Invalid (old?) table or database name 'n+'
140708 12:19:49 [ERROR] Invalid (old?) table or database name 'n+'
140708 12:19:53 [ERROR] Invalid (old?) table or database name 'n+'
140708 12:20:02 [ERROR] Invalid (old?) table or database name 'n+'
140708 22:25:52 [Note] Found 51841929 of 51842952 rows when repairing './nodeny/Z2014_7_6'
140709  8:46:51 [Note] /usr/local/libexec/mysqld: Normal shutdown

140709  8:46:51 [Note] Event Scheduler: Purging the queue. 0 events
140709  8:46:54  InnoDB: Starting shutdown...
140709  8:46:54  InnoDB: Waiting for 201 pages to be flushed
140709  8:46:54  InnoDB: Shutdown completed; log sequence number 425133786275
140709  8:46:54 [Note] /usr/local/libexec/mysqld: Shutdown complete

140709 08:46:54 mysqld_safe mysqld from pid file /var/db/mysql/hulk.kit.dn.ua.pid ended
140709 08:49:35 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql
140709  8:49:35 InnoDB: The InnoDB memory heap is disabled
140709  8:49:35 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140709  8:49:35 InnoDB: Compressed tables use zlib 1.2.7
140709  8:49:35 InnoDB: Initializing buffer pool, size = 128.0M
140709  8:49:35 InnoDB: Completed initialization of buffer pool
140709  8:49:35 InnoDB: highest supported file format is Barracuda.
140709  8:49:36  InnoDB: Waiting for the background threads to start
140709  8:49:37 InnoDB: 5.5.38 started; log sequence number 425133786275
140709  8:49:37 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
140709  8:49:37 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
140709  8:49:37 [Note] Server socket created on IP: '0.0.0.0'.
140709  8:49:37 [Warning] 'user' entry 'root@hulk' ignored in --skip-name-resolve mode.
140709  8:49:37 [Warning] 'user' entry '@hulk' ignored in --skip-name-resolve mode.
140709  8:49:37 [Warning] 'proxies_priv' entry '@ root@hulk' ignored in --skip-name-resolve mode.
140709  8:49:37 [Note] Event Scheduler: Loaded 0 events
140709  8:49:37 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.5.38-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
140711  7:37:58 [Note] /usr/local/libexec/mysqld: Normal shutdown

140711  7:37:58 [Note] Event Scheduler: Purging the queue. 0 events
140711  7:38:00 [Warning] /usr/local/libexec/mysqld: Forcing close of thread 165911  user: 'nodeny3'

140711  7:38:00 [Warning] /usr/local/libexec/mysqld: Forcing close of thread 2  user: 'nodeny'

140711  7:38:00  InnoDB: Starting shutdown...
140711  7:38:01  InnoDB: Shutdown completed; log sequence number 428353403663
140711  7:38:01 [Note] /usr/local/libexec/mysqld: Shutdown complete

140711 07:38:01 mysqld_safe mysqld from pid file /var/db/mysql/hulk.kit.dn.ua.pid ended
140711 07:38:01 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql
140711  7:38:01 InnoDB: The InnoDB memory heap is disabled
140711  7:38:01 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140711  7:38:01 InnoDB: Compressed tables use zlib 1.2.7
140711  7:38:01 InnoDB: Initializing buffer pool, size = 128.0M
140711  7:38:01 InnoDB: Completed initialization of buffer pool
140711  7:38:01 InnoDB: highest supported file format is Barracuda.
140711  7:38:02  InnoDB: Waiting for the background threads to start
140711  7:38:03 InnoDB: 5.5.38 started; log sequence number 428353403663
140711  7:38:03 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
140711  7:38:03 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
140711  7:38:03 [Note] Server socket created on IP: '0.0.0.0'.
140711  7:38:03 [Warning] 'user' entry 'root@hulk' ignored in --skip-name-resolve mode.
140711  7:38:03 [Warning] 'user' entry '@hulk' ignored in --skip-name-resolve mode.
140711  7:38:03 [Warning] 'proxies_priv' entry '@ root@hulk' ignored in --skip-name-resolve mode.
140711  7:38:03 [Note] Event Scheduler: Loaded 0 events
140711  7:38:03 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.5.38-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

Да и для полной картины. У меня один сервер всё в одном и 3 наса, все обслуживают всех. И только на одном насе Я замечаю такую проблему, причём каждый день. На остальных за всё время, после изминения get_ip не обнаружил.

[SeMant]

 Ещё на одном Насе появилась такая проблема, эти 2 помощней и на них больше абонов подключается. Подскажите на что обратить внимание.

[Efendy]

Код:

SHOW ENGINE INNODB STATUS;

[SeMant]

Код:

SHOW ENGINE INNODB STATUS;

Код:

mysql> use nodeny;
Database changed
mysql> SHOW ENGINE INNODB STATUS;
| Type   | Name | Status                                                                                                                                                                                                                            |
+--------+------+-----------------------------------------------------------------------------------------------------------                            
| InnoDB |      |
=====================================
140723 10:35:50 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 12 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1043192 1_second, 1043186 sleeps, 104318 10_second, 3 background, 3 flush
srv_master_thread log flush and writes: 1045585
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 448944, signal count 1607915
Mutex spin waits 28700909, rounds 57749126, OS waits 163263
RW-shared spins 836390, rounds 8616167, OS waits 151548
RW-excl spins 315985, rounds 7104529, OS waits 99450
Spin rounds per wait: 2.01 mutex, 10.30 RW-shared, 22.48 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
140723 10:35:43
*** (1) TRANSACTION:
TRANSACTION BE51C0B5, ACTIVE 0 sec starting index read
mysql tables in use 5, locked 5
LOCK WAIT 6 lock struct(s), heap size 3112, 5 row lock(s)
MySQL thread id 702577, OS thread handle 0x82937f000, query id 391035141 localhost nodeny Sending data
UPDATE ip_pool SET uid =  NAME_CONST('user_id',1324), `release` = UNIX_TIMESTAMP() + 300
    WHERE id = (SELECT id FROM (
        (SELECT id, uid FROM ip_pool
        WHERE uid = 0
            AND type = 'dynamic'
            AND realip = IF( NAME_CONST('real_ip',NULL)>0,1,0)
        LIMIT 1)
            UNION
        (SELECT id, uid FROM ip_pool
        WHERE uid =  NAME_CONST('user_id',1324)
            AND type = 'dynamic'
            AND realip = IF( NAME_CONST('real_ip',NULL)>0,1,0)
        LIMIT 1)
    ) AS tbl ORDER BY uid DESC LIMIT 1)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 368 n bits 736 index `type` of table `nodeny`.`ip_pool` trx id BE51C0B5 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001000; asc     ;;

*** (2) TRANSACTION:
TRANSACTION BE51C0B3, ACTIVE 0 sec fetching rows
mysql tables in use 5, locked 5
11 lock struct(s), heap size 3112, 243 row lock(s)
MySQL thread id 702578, OS thread handle 0x80280ac00, query id 391035131 localhost nodeny Sending data
UPDATE ip_pool SET uid =  NAME_CONST('user_id',2452), `release` = UNIX_TIMESTAMP() + 300
    WHERE id = (SELECT id FROM (
        (SELECT id, uid FROM ip_pool
        WHERE uid = 0
            AND type = 'dynamic'
            AND realip = IF( NAME_CONST('real_ip',NULL)>0,1,0)
        LIMIT 1)
            UNION
        (SELECT id, uid FROM ip_pool
        WHERE uid =  NAME_CONST('user_id',2452)
            AND type = 'dynamic'
            AND realip = IF( NAME_CONST('real_ip',NULL)>0,1,0)
        LIMIT 1)
    ) AS tbl ORDER BY uid DESC LIMIT 1)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 368 n bits 736 index `type` of table `nodeny`.`ip_pool` trx id BE51C0B3 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001000; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001001; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001002; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001003; asc     ;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001004; asc     ;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001005; asc     ;;

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

Record lock, heap no 9 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001007; asc     ;;

Record lock, heap no 10 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001008; asc     ;;

Record lock, heap no 11 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001009; asc     ;;

Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000100a; asc     ;;

Record lock, heap no 13 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000100b; asc     ;;

Record lock, heap no 14 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000100c; asc     ;;

Record lock, heap no 15 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000100d; asc     ;;

Record lock, heap no 16 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000100e; asc     ;;

Record lock, heap no 17 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000100f; asc     ;;

Record lock, heap no 18 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001010; asc     ;;

Record lock, heap no 19 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001011; asc     ;;

Record lock, heap no 20 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001012; asc     ;;

Record lock, heap no 21 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001013; asc     ;;

Record lock, heap no 22 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001014; asc     ;;

Record lock, heap no 23 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001015; asc     ;;

Record lock, heap no 24 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001016; asc     ;;

Record lock, heap no 25 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001017; asc     ;;

Record lock, heap no 26 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001018; asc     ;;

Record lock, heap no 27 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001019; asc     ;;

Record lock, heap no 28 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000101a; asc     ;;

Record lock, heap no 29 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000101b; asc     ;;

Record lock, heap no 30 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000101c; asc     ;;

Record lock, heap no 31 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000101d; asc     ;;

Record lock, heap no 32 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000101e; asc     ;;

Record lock, heap no 33 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000101f; asc     ;;

Record lock, heap no 34 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001020; asc     ;;

Record lock, heap no 35 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001021; asc    !;;

Record lock, heap no 36 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001022; asc    ";;

Record lock, heap no 37 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001023; asc    #;;

Record lock, heap no 38 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001024; asc    $;;

Record lock, heap no 39 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001025; asc    %;;

Record lock, heap no 40 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001026; asc    &;;

Record lock, heap no 41 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001027; asc    ';;

Record lock, heap no 42 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001028; asc    (;;

Record lock, heap no 43 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001029; asc    );;

Record lock, heap no 44 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000102a; asc    *;;

Record lock, heap no 45 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000102b; asc    +;;

Record lock, heap no 46 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000102c; asc    ,;;

Record lock, heap no 47 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000102d; asc    -;;

Record lock, heap no 48 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000102e; asc    .;;

Record lock, heap no 49 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000102f; asc    /;;

Record lock, heap no 50 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001030; asc    0;;

Record lock, heap no 51 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001031; asc    1;;

Record lock, heap no 52 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001032; asc    2;;

Record lock, heap no 53 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001033; asc    3;;

Record lock, heap no 54 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001034; asc    4;;

Record lock, heap no 55 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001035; asc    5;;

Record lock, heap no 56 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001036; asc    6;;

Record lock, heap no 57 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001037; asc    7;;

Record lock, heap no 58 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001038; asc    8;;

Record lock, heap no 59 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001039; asc    9;;

[SeMant]

Код:

Record lock, heap no 60 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000103a; asc    :;;

Record lock, heap no 61 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000103b; asc    ;;;

Record lock, heap no 62 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000103c; asc    <;;

Record lock, heap no 63 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000103d; asc    =;;

Record lock, heap no 64 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000103e; asc    >;;

Record lock, heap no 65 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000103f; asc    ?;;

Record lock, heap no 66 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001040; asc    @;;

Record lock, heap no 67 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001041; asc    A;;

Record lock, heap no 68 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001042; asc    B;;

Record lock, heap no 69 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001043; asc    C;;

Record lock, heap no 70 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001044; asc    D;;

Record lock, heap no 71 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001045; asc    E;;

Record lock, heap no 72 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001046; asc    F;;

Record lock, heap no 73 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001047; asc    G;;

Record lock, heap no 74 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001048; asc    H;;

Record lock, heap no 75 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001049; asc    I;;

Record lock, heap no 76 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000104a; asc    J;;

Record lock, heap no 77 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000104b; asc    K;;

Record lock, heap no 78 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000104c; asc    L;;

Record lock, heap no 79 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000104d; asc    M;;

Record lock, heap no 80 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000104e; asc    N;;

Record lock, heap no 81 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000104f; asc    O;;

Record lock, heap no 82 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001050; asc    P;;

Record lock, heap no 83 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001051; asc    Q;;

Record lock, heap no 84 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001052; asc    R;;

Record lock, heap no 85 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001053; asc    S;;

Record lock, heap no 86 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001054; asc    T;;

Record lock, heap no 87 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001055; asc    U;;

Record lock, heap no 88 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001056; asc    V;;

Record lock, heap no 89 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001057; asc    W;;

Record lock, heap no 90 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001058; asc    X;;

Record lock, heap no 91 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001059; asc    Y;;

Record lock, heap no 92 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000105a; asc    Z;;

Record lock, heap no 93 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000105b; asc    [;;

Record lock, heap no 94 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000105c; asc    \;;

Record lock, heap no 95 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000105d; asc    ];;

Record lock, heap no 96 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000105e; asc    ^;;

Record lock, heap no 97 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000105f; asc    _;;

Record lock, heap no 98 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001060; asc    `;;

Record lock, heap no 99 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001061; asc    a;;

Record lock, heap no 100 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001062; asc    b;;

Record lock, heap no 101 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001063; asc    c;;

Record lock, heap no 102 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001064; asc    d;;

Record lock, heap no 103 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001065; asc    e;;

Record lock, heap no 104 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001066; asc    f;;

Record lock, heap no 105 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001067; asc    g;;

Record lock, heap no 106 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001068; asc    h;;

Record lock, heap no 107 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 00001069; asc    i;;

Record lock, heap no 108 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 02; asc  ;;
 1: len 4; hex 0000106a; asc    j;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 465 n bits 440 index `PRIMARY` of table `nodeny`.`ip_pool` trx id BE51C0B3 lock_mode X locks                                  rec but not gap waiting
Record lock, heap no 259 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 4; hex 0000106a; asc    j;;
 1: len 6; hex 0000be518b02; asc    Q  ;;
 2: len 7; hex 730000274d041a; asc s  'M  ;;
 3: len 4; hex 0a00006c; asc    l;;
 4: len 1; hex 02; asc  ;;
 5: len 1; hex 00; asc  ;;
 6: len 4; hex 53cf66a1; asc S f ;;
 7: len 8; hex 000000000000052c; asc        ,;;
 8: len 0; hex ; asc ;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter BE51C445
Purge done for trx's n:o < BE51BF31 undo n:o < 0
History list length 4314
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 918164, OS thread handle 0x835d3c800, query id 391037277 localhost root
SHOW ENGINE INNODB STATUS
---TRANSACTION BE51C3AB, not started
MySQL thread id 912668, OS thread handle 0x82937d000, query id 391036920 localhost nodeny
---TRANSACTION BE51C43D, not started
MySQL thread id 702577, OS thread handle 0x82937f000, query id 391037243 localhost nodeny
---TRANSACTION BE51C444, not started
MySQL thread id 702576, OS thread handle 0x82937ec00, query id 391037261 localhost nodeny
---TRANSACTION BE51C432, not started
MySQL thread id 702578, OS thread handle 0x80280ac00, query id 391037219 localhost nodeny
---TRANSACTION BE51C42A, not started
MySQL thread id 702579, OS thread handle 0x82937cc00, query id 391037201 localhost nodeny
---TRANSACTION BE51C422, not started
MySQL thread id 702580, OS thread handle 0x82937e000, query id 391037183 localhost nodeny
---TRANSACTION BE51C35C, not started
MySQL thread id 485680, OS thread handle 0x82937fc00, query id 391036731 95.47.104.8 nodeny2
---TRANSACTION BE51C2BB, not started
MySQL thread id 279344, OS thread handle 0x82937f800, query id 391036361 95.47.104.10 nodeny3
---TRANSACTION BE51BC82, not started
MySQL thread id 104, OS thread handle 0x82937f400, query id 391032584 localhost nodeny
---TRANSACTION BE51C2E5, not started
MySQL thread id 7, OS thread handle 0x82937c000, query id 391036469 95.47.104.6 nodeny1
---TRANSACTION BE51C2C4, not started
MySQL thread id 6, OS thread handle 0x82937c400, query id 391036405 localhost nodeny
--------
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 (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
22717 OS file reads, 54210379 OS file writes, 50580484 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 47.00 writes/s, 45.50 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5, seg size 7, 3743 merges
merged operations:
 insert 3746, delete mark 2, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 804 buffer(s)
14941.59 hash searches/s, 1056.25 non-hash searches/s
---
LOG
---
Log sequence number 449072340699
Log flushed up to   449072340699
Last checkpoint at  449067495863
0 pending log writes, 0 pending chkp writes
48730805 log i/o's done, 43.33 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 215905
Buffer pool size   8192
Free buffers       1
Database pages     7387
Old database pages 2706
Modified db pages  502
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 55324, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 23080, created 702, written 10127561
0.00 reads/s, 0.00 creates/s, 3.67 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: 7387, unzip_LRU len: 0
I/O sum[207]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 34401729536, state: sleeping
Number of rows inserted 110177, updated 48582490, deleted 55002, read 20587271547
0.25 inserts/s, 42.91 updates/s, 0.08 deletes/s, 19471.71 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
                                                                                                                                -----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[Efendy]

Сколько NAS, которые работают с Radius?

[SeMant]

Всего 4, на одном из них радиус установлен.

[Efendy]

Всего 4, на одном из них радиус установлен.

Надо попробовать сделать 4 разных процедуры для этих насов. Сегодня постараюсь нарисовать. Никак не получается одолеть этот долбанный  mysql - все таки отстает он от postgres-а

[Efendy]

В общем, сначала читаешь http://forum.nodeny.com.ua/index.php?topic=2374.0 - описание как сделать чтоб каждый NAS выдавал ip только из своего пула. Только саму функцию сделать такой:

Код:

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

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