Open bud4 opened 10 months ago
Hi @bud4,
The line "Lock wait timeout exceeded" in your error_log refers to a SQL delete statement made upon update of an inventory section. Would you be able to check the SQL process list while this is happening, see which queries are slowing the database ? Depending on how many assets the OCS server is handling, this could also be a sign that the server needs more resources or that apache or php-fpm are not properly configured.
Regards, Léa
2024-01-22 12:35:01 0x7f82649cf700
(1) TRANSACTION:
TRANSACTION 19789548, ACTIVE 12 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 7 lock struct(s), heap size 1128, 5 row lock(s), undo log entries 2
MySQL thread id 24215, OS thread handle 140198191974144, query id 108510918 localhost ocs_user Updating
UPDATE devices SET TVALUE='NOTIFIED', COMMENTS='Mon Jan 22 12:34:49 2024' WHERE NAME='DOWNLOAD' AND HARDWARE_ID='36773' AND TVALUE IS NULL
(1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 154 page no 633 n bits 1216 index HARDWARE_ID of table ocs_db
.devices
trx id 19789548 lock_mode X waiting
Record lock, heap no 890 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80008fa5; asc ;;
1: len 4; hex 8000eaca; asc ;;
(2) TRANSACTION:
TRANSACTION 19789494, ACTIVE 61 sec fetching rows
mysql tables in use 3, locked 3
179 lock struct(s), heap size 24696, 4988 row lock(s), undo log entries 1
MySQL thread id 24208, OS thread handle 140198010484480, query id 108499186 localhost ocs_user Updating
UPDATE devices SET TVALUE='SUCCESS', COMMENTS='Mon Jan 22 12:34:00 2024'
WHERE NAME="DOWNLOAD"
AND HARDWARE_ID='36773'
AND IVALUE='24'
(2) HOLDS THE LOCK(S):
RECORD LOCKS space id 154 page no 633 n bits 1216 index HARDWARE_ID of table ocs_db
.devices
trx id 19789494 lock_mode X
Record lock, heap no 890 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80008fa5; asc ;;
1: len 4; hex 8000eaca; asc ;;
Record lock, heap no 892 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80008fa5; asc ;; 1: len 4; hex 8000eacb; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 154 page no 633 n bits 1216 index HARDWARE_ID of table ocs_db
.devices
trx id 19789494 lock_mode X waiting
Record lock, heap no 979 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80008fa5; asc ;;
1: len 4; hex 8000eacd; asc ;;
` +--------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Error in /var/log/httpd/error.log DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction at /usr/local/share/perl5/Apache/Ocsinventory/Server/Inventory/Update.pm line 102. DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction at /usr/local/share/perl5/Apache/Ocsinventory/Server/Inventory/Update.pm line 102. can't find ANONSUB's 'anon33' entry. DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction at /usr/local/share/perl5/Apache/Ocsinventory/Server/Inventory/Update.pm line 102. can't find ANONSUB's 'anon30' entry. DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction at /usr/local/share/perl5/Apache/Ocsinventory/Server/Inventory/Update.pm line 102. can't find ANONSUB's 'anon2' entry. DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction at /usr/local/share/perl5/Apache/Ocsinventory/Server/Inventory/Update.pm line 102. can't find ANONSUB's 'anon27' entry. DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction at /usr/local/share/perl5/Apache/Ocsinventory/Server/Inventory/Update.pm line 102. DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction at /usr/local/share/perl5/Apache/Ocsinventory/Server/Inventory/Update.pm line 102. DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction at /usr/local/share/perl5/Apache/Ocsinventory/Server/Inventory/Update.pm line 102. can't find ANONSUB's 'anon23' entry. ### DB status show processlist; +-------+-------------+-----------+--------+---------+-------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +-------+-------------+-----------+--------+---------+-------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 16787 | ocs_user | localhost | ocs_db | Sleep | 27913 | | NULL | 0.000 | | 16788 | ocs_user | localhost | ocs_db | Sleep | 26892 | | NULL | 0.000 | | 16817 | ocs_user | localhost | ocs_db | Sleep | 26255 | | NULL | 0.000 | | 16826 | ocs_user | localhost | ocs_db | Sleep | 26439 | | NULL | 0.000 | | 16827 | ocs_user | localhost | ocs_db | Sleep | 16319 | | NULL | 0.000 | | 19481 | ocs_user | localhost | ocs_db | Query | 19 | Updating | DELETE FROM driverslist WHERE HARDWARE_ID='9717' | 0.000 | | 19510 | ocs_user | localhost | ocs_db | Query | 0 | Updating | DELETE FROM runningprocess WHERE HARDWARE_ID='16801' | 0.000 | | 19523 | ocs_user | localhost | ocs_db | Query | 25 | Updating | UPDATE hardware SET FIDELITY=FIDELITY+1,QUALITY='0.0517557189542484',LASTCOME=NOW(),USERAGENT='OCS-N | 0.000 | | 19524 | ocs_user | localhost | ocs_db | Query | 15 | Updating | DELETE FROM driverslist WHERE HARDWARE_ID='38649' | 0.000 | | 19529 | ocs_user | localhost | ocs_db | Sleep | 207 | | NULL | 0.000 | | 19530 | ocs_user | localhost | ocs_db | Query | 6 | Updating | DELETE FROM groups_cache WHERE GROUP_ID='14979' AND STATIC=0 | 0.000 | | 19536 | ocs_user | localhost | ocs_db | Query | 37 | Updating | DELETE FROM driverslist WHERE HARDWARE_ID='8709' | 0.000 | | 19537 | ocs_user | localhost | ocs_db | Query | 27 | Updating | DELETE FROM driverslist WHERE HARDWARE_ID='38638' | 0.000 | | 19540 | ocs_user | localhost | ocs_db | Query | 27 | Updating | DELETE FROM driverslist WHERE HARDWARE_ID='38579' | 0.000 | | 19544 | ocs_user | localhost | ocs_db | Query | 30 | Updating | DELETE FROM driverslist WHERE HARDWARE_ID='28940' | 0.000 | | 19546 | ocs_user | localhost | ocs_db | Sleep | 196 | | NULL | 0.000 | | 19557 | ocs_user | localhost | ocs_db | Query | 37 | Updating | DELETE FROM driverslist WHERE HARDWARE_ID='12281' | 0.000 | | 19560 | ocs_user | localhost | ocs_db | Sleep | 1 | | NULL | 0.000 | | 19563 | ocs_user | localhost | ocs_db | Sleep | 3 | | NULL | 0.000 | | 19565 | ocs_user | localhost | ocs_db | Sleep | 21 | | NULL | 0.000 | | 19567 | ocs_user | localhost | ocs_db | Query | 5 | Updating | DELETE FROM driverslist WHERE HARDWARE_ID='14018' | 0.000 | | 19568 | root | localhost | NULL | Query | 0 | Init | show processlist | 0.000 | +-------+-------------+-----------+--------+---------+-------+--------------------------+------------------------------------------------------------------------------------------------------+----------+ ariaDB [(none)]> SHOW FULL PROCESSLIST; +-------+-------------+-----------+--------+---------+-------+--------------------------+--------------------------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +-------+-------------+-----------+--------+---------+-------+--------------------------+--------------------------------------------------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 16787 | ocs_user | localhost | ocs_db | Sleep | 28023 | | NULL | 0.000 | | 16788 | ocs_user | localhost | ocs_db | Sleep | 27002 | | NULL | 0.000 | | 16817 | ocs_user | localhost | ocs_db | Sleep | 26365 | | NULL | 0.000 | | 16826 | ocs_user | localhost | ocs_db | Sleep | 26549 | | NULL | 0.000 | | 16827 | ocs_user | localhost | ocs_db | Sleep | 16429 | | NULL | 0.000 | | 19481 | ocs_user | localhost | ocs_db | Query | 13 | Updating | DELETE FROM groups_cache WHERE GROUP_ID='14984' AND STATIC=0 | 0.000 | | 19510 | ocs_user | localhost | ocs_db | Sleep | 24 | | NULL | 0.000 | | 19546 | ocs_user | localhost | ocs_db | Sleep | 307 | | NULL | 0.000 | | 19567 | ocs_user | localhost | ocs_db | Query | 61 | Updating | DELETE FROM groups_cache WHERE GROUP_ID='9463' AND STATIC=0 | 0.000 | | 19568 | root | localhost | NULL | Query | 0 | Init | SHOW FULL PROCESSLIST | 0.000 | | 19573 | ocs_user | localhost | ocs_db | Query | 12 | Updating | DELETE FROM driverslist WHERE HARDWARE_ID='12275' | 0.000 | | 19575 | ocs_user | localhost | ocs_db | Sleep | 29 | | NULL | 0.000 | | 19576 | ocs_user | localhost | ocs_db | Sleep | 14 | | NULL | 0.000 | | 19577 | ocs_user | localhost | ocs_db | Query | 1 | Updating | DELETE FROM winupdatestate WHERE HARDWARE_ID='13952' | 0.000 | | 19578 | ocs_user | localhost | ocs_db | Query | 27 | Updating | DELETE FROM driverslist WHERE HARDWARE_ID='21784' | 0.000 | | 19579 | ocs_user | localhost | ocs_db | Query | 47 | Updating | DELETE FROM driverslist WHERE HARDWARE_ID='679' | 0.000 | | 19580 | ocs_user | localhost | ocs_db | Query | 42 | Updating | DELETE FROM driverslist WHERE HARDWARE_ID='9372' | 0.000 | | 19581 | ocs_user | localhost | ocs_db | Query | 28 | Updating | DELETE FROM driverslist WHERE HARDWARE_ID='10203' | 0.000 | | 19582 | ocs_user | localhost | ocs_db | Query | 17 | Updating | DELETE FROM driverslist WHERE HARDWARE_ID='12529' | 0.000 | | 19583 | ocs_user | localhost | ocs_db | Query | 17 | Updating | DELETE FROM driverslist WHERE HARDWARE_ID='13830' | 0.000 | | 19584 | ocs_user | localhost | ocs_db | Sleep | 19 | | NULL | 0.000 | | 19585 | ocs_user | localhost | ocs_db | Query | 12 | Updating | DELETE FROM driverslist WHERE HARDWARE_ID='9150' | 0.000 | | 19586 | ocs_user | localhost | ocs_db | Query | 1 | Updating | DELETE FROM driverslist WHERE HARDWARE_ID='17124' | 0.000 | +-------+-------------+-----------+--------+---------+-------+--------------------------+--------------------------------------------------------------+----------+ 28 rows in set (0.022 sec) MariaDB [(none)]> show engine innodb status;
2024-01-31 09:29:38 0x7fe480a5d700
(1) TRANSACTION:
TRANSACTION 20137662, ACTIVE 12 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 15 lock struct(s), heap size 1128, 37 row lock(s), undo log entries 6
MySQL thread id 18699, OS thread handle 140619388237568, query id 88049305 localhost ocs_user Updating
UPDATE devices SET HARDWARE_ID='38617' WHERE HARDWARE_ID='38467'
(1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 154 page no 637 n bits 720 index HARDWARE_ID of table ocs_db
.devices
trx id 20137662 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
(2) TRANSACTION:
TRANSACTION 20137611, ACTIVE 39 sec updating or deleting
mysql tables in use 1, locked 1
18 lock struct(s), heap size 1128, 38 row lock(s), undo log entries 6
MySQL thread id 18693, OS thread handle 140619387623168, query id 88036261 localhost ocs_user Updating
UPDATE devices SET HARDWARE_ID='38615' WHERE HARDWARE_ID='38426'
(2) HOLDS THE LOCK(S):
RECORD LOCKS space id 154 page no 637 n bits 720 index HARDWARE_ID of table ocs_db
.devices
trx id 20137611 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 341 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 8000961a; asc ;; 1: len 4; hex 8000ea97; asc ;;
Record lock, heap no 342 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000961a; asc ;; 1: len 4; hex 8000ea98; asc ;;
Record lock, heap no 343 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000961a; asc ;; 1: len 4; hex 8000eb48; asc H;;
Record lock, heap no 344 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000961a; asc ;; 1: len 4; hex 8000eb49; asc I;;
Record lock, heap no 345 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000961a; asc ;; 1: len 4; hex 8000eb4a; asc J;;
Record lock, heap no 346 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000961a; asc ;; 1: len 4; hex 8000eb4b; asc K;;
Record lock, heap no 347 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000961a; asc ;; 1: len 4; hex 8000ec01; asc ;;
Record lock, heap no 348 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000961a; asc ;; 1: len 4; hex 8000ec02; asc ;;
Record lock, heap no 349 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000961a; asc ;; 1: len 4; hex 8000ec03; asc ;;
Record lock, heap no 350 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000961a; asc ;; 1: len 4; hex 8000ec04; asc ;;
Record lock, heap no 351 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000961a; asc ;; 1: len 4; hex 8000ed3b; asc ;;;
Record lock, heap no 352 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000961a; asc ;; 1: len 4; hex 8000ed3c; asc <;;
Record lock, heap no 353 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000961a; asc ;; 1: len 4; hex 8000ed3d; asc =;;
Record lock, heap no 354 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 8000961a; asc ;; 1: len 4; hex 8000ed3e; asc >;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 154 page no 637 n bits 720 index HARDWARE_ID of table ocs_db
.devices
trx id 20137611 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Trx id counter 20145228
Purge done for trx's n:o < 20118020 undo n:o < 0 state: running but idle
History list length 13142
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422094358480632, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422094358476448, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 20145215, ACTIVE 13 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 18 lock struct(s), heap size 1128, 213 row lock(s), undo log entries 1
MySQL thread id 19596, OS thread handle 140619399317248, query id 89645333 localhost ocs_user Updating
DELETE FROM driverslist WHERE HARDWARE_ID='13755'
Trx read view will not see trx with id >= 20145214, sees < 20145163
------- TRX HAS BEEN WAITING 12 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 542 page no 86 n bits 152 index PRIMARY of table ocs_db
.driverslist
trx id 20145215 lock_mode X waiting
Record lock, heap no 18 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
0: len 4; hex 8002fe87; asc ;;
1: len 4; hex 80004c82; asc L ;;
2: len 6; hex 000000000000; asc ;;
3: len 7; hex 80000000000000; asc ;;
4: len 10; hex 5052494e545155455545; asc PRINTQUEUE;;
5: len 17; hex 4c6f63616c205072696e74205175657565; asc Local Print Queue;;
6: len 17; hex 4c6f63616c205072696e74205175657565; asc Local Print Queue;;
7: len 9; hex 4d6963726f736f6674; asc Microsoft;;
8: len 12; hex 31302e302e31393034312e31; asc 10.0.19041.1;;
9: len 10; hex 323030362f30362f3231; asc 2006/06/21;;
10: len 14; hex 7072696e7471756575652e696e66; asc printqueue.inf;;
11: len 22; hex 4d6963726f736f6674205072696e7420746f20504446; asc Microsoft Print to PDF;;
12: len 4; hex 5665726f; asc Vero;;
13: len 30; hex 5357445c5052494e54454e554d5c7b46443133374545302d433632312d34; asc SWD\PRINTENUM{FD137EE0-C621-4; (total 52 bytes);
---TRANSACTION 422094358430424, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 20145210, ACTIVE 18 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 13 lock struct(s), heap size 1128, 217 row lock(s), undo log entries 1
MySQL thread id 19592, OS thread handle 140619400238848, query id 89643983 localhost ocs_user Updating
DELETE FROM driverslist WHERE HARDWARE_ID='37008'
Trx read view will not see trx with id >= 20145210, sees < 20145163
------- TRX HAS BEEN WAITING 17 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 542 page no 86 n bits 152 index PRIMARY of table ocs_db
.driverslist
trx id 20145210 lock_mode X waiting
Record lock, heap no 18 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
0: len 4; hex 8002fe87; asc ;;
1: len 4; hex 80004c82; asc L ;;
2: len 6; hex 000000000000; asc ;;
3: len 7; hex 80000000000000; asc ;;
4: len 10; hex 5052494e545155455545; asc PRINTQUEUE;;
5: len 17; hex 4c6f63616c205072696e74205175657565; asc Local Print Queue;;
6: len 17; hex 4c6f63616c205072696e74205175657565; asc Local Print Queue;;
7: len 9; hex 4d6963726f736f6674; asc Microsoft;;
8: len 12; hex 31302e302e31393034312e31; asc 10.0.19041.1;;
9: len 10; hex 323030362f30362f3231; asc 2006/06/21;;
10: len 14; hex 7072696e7471756575652e696e66; asc printqueue.inf;;
11: len 22; hex 4d6963726f736f6674205072696e7420746f20504446; asc Microsoft Print to PDF;;
12: len 4; hex 5665726f; asc Vero;;
13: len 30; hex 5357445c5052494e54454e554d5c7b46443133374545302d433632312d34; asc SWD\PRINTENUM{FD137EE0-C621-4; (total 52 bytes);
---TRANSACTION 20145203, ACTIVE 27 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 16 lock struct(s), heap size 1128, 203 row lock(s), undo log entries 1
MySQL thread id 19591, OS thread handle 140619391616768, query id 89641656 localhost ocs_user Updating
DELETE FROM driverslist WHERE HARDWARE_ID='18257'
Trx read view will not see trx with id >= 20145202, sees < 20145080
------- TRX HAS BEEN WAITING 25 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 542 page no 86 n bits 152 index PRIMARY of table ocs_db
.driverslist
trx id 20145203 lock_mode X waiting
Record lock, heap no 18 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
0: len 4; hex 8002fe87; asc ;;
1: len 4; hex 80004c82; asc L ;;
2: len 6; hex 000000000000; asc ;;
3: len 7; hex 80000000000000; asc ;;
4: len 10; hex 5052494e545155455545; asc PRINTQUEUE;;
5: len 17; hex 4c6f63616c205072696e74205175657565; asc Local Print Queue;;
6: len 17; hex 4c6f63616c205072696e74205175657565; asc Local Print Queue;;
7: len 9; hex 4d6963726f736f6674; asc Microsoft;;
8: len 12; hex 31302e302e31393034312e31; asc 10.0.19041.1;;
9: len 10; hex 323030362f30362f3231; asc 2006/06/21;;
10: len 14; hex 7072696e7471756575652e696e66; asc printqueue.inf;;
11: len 22; hex 4d6963726f736f6674205072696e7420746f20504446; asc Microsoft Print to PDF;;
12: len 4; hex 5665726f; asc Vero;;
13: len 30; hex 5357445c5052494e54454e554d5c7b46443133374545302d433632312d34; asc SWD\PRINTENUM{FD137EE0-C621-4; (total 52 bytes);
---TRANSACTION 20145200, ACTIVE 30 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 14 lock struct(s), heap size 1128, 365 row lock(s), undo log entries 1
MySQL thread id 19590, OS thread handle 140619391923968, query id 89640728 localhost ocs_user Updating
DELETE FROM driverslist WHERE HARDWARE_ID='20507'
Trx read view will not see trx with id >= 20145200, sees < 20145080
------- TRX HAS BEEN WAITING 28 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 542 page no 86 n bits 152 index PRIMARY of table ocs_db
.driverslist
trx id 20145200 lock_mode X waiting
Record lock, heap no 18 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
0: len 4; hex 8002fe87; asc ;;
1: len 4; hex 80004c82; asc L ;;
2: len 6; hex 000000000000; asc ;;
3: len 7; hex 80000000000000; asc ;;
4: len 10; hex 5052494e545155455545; asc PRINTQUEUE;;
5: len 17; hex 4c6f63616c205072696e74205175657565; asc Local Print Queue;;
6: len 17; hex 4c6f63616c205072696e74205175657565; asc Local Print Queue;;
7: len 9; hex 4d6963726f736f6674; asc Microsoft;;
8: len 12; hex 31302e302e31393034312e31; asc 10.0.19041.1;;
9: len 10; hex 323030362f30362f3231; asc 2006/06/21;;
10: len 14; hex 7072696e7471756575652e696e66; asc printqueue.inf;;
11: len 22; hex 4d6963726f736f6674205072696e7420746f20504446; asc Microsoft Print to PDF;;
12: len 4; hex 5665726f; asc Vero;;
13: len 30; hex 5357445c5052494e54454e554d5c7b46443133374545302d433632312d34; asc SWD\PRINTENUM{FD137EE0-C621-4; (total 52 bytes);
---TRANSACTION 20145199, ACTIVE 35 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 15 lock struct(s), heap size 1128, 228 row lock(s), undo log entries 1
MySQL thread id 19589, OS thread handle 140619387008768, query id 89639151 localhost ocs_user Updating
DELETE FROM driverslist WHERE HARDWARE_ID='28268'
Trx read view will not see trx with id >= 20145197, sees < 20145080
------- TRX HAS BEEN WAITING 35 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 542 page no 86 n bits 152 index PRIMARY of table ocs_db
.driverslist
trx id 20145199 lock_mode X waiting
Record lock, heap no 18 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
0: len 4; hex 8002fe87; asc ;;
1: len 4; hex 80004c82; asc L ;;
2: len 6; hex 000000000000; asc ;;
3: len 7; hex 80000000000000; asc ;;
4: len 10; hex 5052494e545155455545; asc PRINTQUEUE;;
5: len 17; hex 4c6f63616c205072696e74205175657565; asc Local Print Queue;;
6: len 17; hex 4c6f63616c205072696e74205175657565; asc Local Print Queue;;
7: len 9; hex 4d6963726f736f6674; asc Microsoft;;
8: len 12; hex 31302e302e31393034312e31; asc 10.0.19041.1;;
9: len 10; hex 323030362f30362f3231; asc 2006/06/21;;
10: len 14; hex 7072696e7471756575652e696e66; asc printqueue.inf;;
11: len 22; hex 4d6963726f736f6674205072696e7420746f20504446; asc Microsoft Print to PDF;;
12: len 4; hex 5665726f; asc Vero;;
13: len 30; hex 5357445c5052494e54454e554d5c7b46443133374545302d433632312d34; asc SWD\PRINTENUM{FD137EE0-C621-4; (total 52 bytes);
---TRANSACTION 422094358417872, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 20145214, ACTIVE 13 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 13 lock struct(s), heap size 1128, 104 row lock(s), undo log entries 1
MySQL thread id 19586, OS thread handle 140619386701568, query id 89645268 localhost ocs_user Updating
DELETE FROM driverslist WHERE HARDWARE_ID='32921'
Trx read view will not see trx with id >= 20145214, sees < 20145163
------- TRX HAS BEEN WAITING 12 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 542 page no 86 n bits 152 index PRIMARY of table ocs_db
.driverslist
trx id 20145214 lock_mode X waiting
Record lock, heap no 18 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
0: len 4; hex 8002fe87; asc ;;
1: len 4; hex 80004c82; asc L ;;
2: len 6; hex 000000000000; asc ;;
3: len 7; hex 80000000000000; asc ;;
4: len 10; hex 5052494e545155455545; asc PRINTQUEUE;;
5: len 17; hex 4c6f63616c205072696e74205175657565; asc Local Print Queue;;
6: len 17; hex 4c6f63616c205072696e74205175657565; asc Local Print Queue;;
7: len 9; hex 4d6963726f736f6674; asc Microsoft;;
8: len 12; hex 31302e302e31393034312e31; asc 10.0.19041.1;;
9: len 10; hex 323030362f30362f3231; asc 2006/06/21;;
10: len 14; hex 7072696e7471756575652e696e66; asc printqueue.inf;;
11: len 22; hex 4d6963726f736f6674205072696e7420746f20504446; asc Microsoft Print to PDF;;
12: len 4; hex 5665726f; asc Vero;;
13: len 30; hex 5357445c5052494e54454e554d5c7b46443133374545302d433632312d34; asc SWD\PRINTENUM{FD137EE0-C621-4; (total 52 bytes);
---TRANSACTION 20145221, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 19 lock struct(s), heap size 1128, 197 row lock(s), undo log entries 1
MySQL thread id 19585, OS thread handle 140619392538368, query id 89646005 localhost ocs_user Updating
DELETE FROM driverslist WHERE HARDWARE_ID='9699'
Trx read view will not see trx with id >= 20145221, sees < 20145179
------- TRX HAS BEEN WAITING 10 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 542 page no 86 n bits 152 index PRIMARY of table ocs_db
.driverslist
trx id 20145221 lock_mode X waiting
Record lock, heap no 18 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
0: len 4; hex 8002fe87; asc ;;
1: len 4; hex 80004c82; asc L ;;
2: len 6; hex 000000000000; asc ;;
3: len 7; hex 80000000000000; asc ;;
4: len 10; hex 5052494e545155455545; asc PRINTQUEUE;;
5: len 17; hex 4c6f63616c205072696e74205175657565; asc Local Print Queue;;
6: len 17; hex 4c6f63616c205072696e74205175657565; asc Local Print Queue;;
7: len 9; hex 4d6963726f736f6674; asc Microsoft;;
8: len 12; hex 31302e302e31393034312e31; asc 10.0.19041.1;;
9: len 10; hex 323030362f30362f3231; asc 2006/06/21;;
10: len 14; hex 7072696e7471756575652e696e66; asc printqueue.inf;;
11: len 22; hex 4d6963726f736f6674205072696e7420746f20504446; asc Microsoft Print to PDF;;
12: len 4; hex 5665726f; asc Vero;;
13: len 30; hex 5357445c5052494e54454e554d5c7b46443133374545302d433632312d34; asc SWD\PRINTENUM{FD137EE0-C621-4; (total 52 bytes);
---TRANSACTION 20145209, ACTIVE 24 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 14 lock struct(s), heap size 1128, 213 row lock(s), undo log entries 1
MySQL thread id 19582, OS thread handle 140619391002368, query id 89642628 localhost ocs_user Updating
DELETE FROM driverslist WHERE HARDWARE_ID='32860'
Trx read view will not see trx with id >= 20145209, sees < 20145163
------- TRX HAS BEEN WAITING 23 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 542 page no 86 n bits 152 index PRIMARY of table ocs_db
.driverslist
trx id 20145209 lock_mode X waiting
Record lock, heap no 18 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
0: len 4; hex 8002fe87; asc ;;
1: len 4; hex 80004c82; asc L ;;
2: len 6; hex 000000000000; asc ;;
3: len 7; hex 80000000000000; asc ;;
4: len 10; hex 5052494e545155455545; asc PRINTQUEUE;;
5: len 17; hex 4c6f63616c205072696e74205175657565; asc Local Print Queue;;
6: len 17; hex 4c6f63616c205072696e74205175657565; asc Local Print Queue;;
7: len 9; hex 4d6963726f736f6674; asc Microsoft;;
8: len 12; hex 31302e302e31393034312e31; asc 10.0.19041.1;;
9: len 10; hex 323030362f30362f3231; asc 2006/06/21;;
10: len 14; hex 7072696e7471756575652e696e66; asc printqueue.inf;;
11: len 22; hex 4d6963726f736f6674205072696e7420746f20504446; asc Microsoft Print to PDF;;
12: len 4; hex 5665726f; asc Vero;;
13: len 30; hex 5357445c5052494e54454e554d5c7b46443133374545302d433632312d34; asc SWD\PRINTENUM{FD137EE0-C621-4; (total 52 bytes);
---TRANSACTION 20145196, ACTIVE 37 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 20 lock struct(s), heap size 3488, 201 row lock(s), undo log entries 1
MySQL thread id 19581, OS thread handle 140619389159168, query id 89638390 localhost ocs_user Updating
DELETE FROM driverslist WHERE HARDWARE_ID='11316'
Trx read view will not see trx with id >= 20145196, sees < 20145080
------- TRX HAS BEEN WAITING 36 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 542 page no 86 n bits 152 index PRIMARY of table ocs_db
.driverslist
trx id 20145196 lock_mode X waiting
Record lock, heap no 18 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
0: len 4; hex 8002fe87; asc ;;
1: len 4; hex 80004c82; asc L ;;
2: len 6; hex 000000000000; asc ;;
3: len 7; hex 80000000000000; asc ;;
4: len 10; hex 5052494e545155455545; asc PRINTQUEUE;;
5: len 17; hex 4c6f63616c205072696e74205175657565; asc Local Print Queue;;
6: len 17; hex 4c6f63616c205072696e74205175657565; asc Local Print Queue;;
7: len 9; hex 4d6963726f736f6674; asc Microsoft;;
8: len 12; hex 31302e302e31393034312e31; asc 10.0.19041.1;;
9: len 10; hex 323030362f30362f3231; asc 2006/06/21;;
10: len 14; hex 7072696e7471756575652e696e66; asc printqueue.inf;;
11: len 22; hex 4d6963726f736f6674205072696e7420746f20504446; asc Microsoft Print to PDF;;
12: len 4; hex 5665726f; asc Vero;;
13: len 30; hex 5357445c5052494e54454e554d5c7b46443133374545302d433632312d34; asc SWD\PRINTENUM{FD137EE0-C621-4; (total 52 bytes);
| 1 row in set (0.105 sec)
MariaDB [(none)]> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
the problem persists seems to be inherent to these sql operations:
DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction at /usr/local/share/perl5/Apache/Ocsinventory/Server/Duplicate.pm line 268. can't find ANONSUB's 'anon9' entry. DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction at /usr/local/share/perl5/Apache/Ocsinventory/Server/Inventory/Update.pm line 102. can't find ANONSUB's 'anon39' entry. can't find ANONSUB's 'anon13' entry. DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction at /usr/local/share/perl5/Apache/Ocsinventory/Server/Inventory/Update.pm line 102.
the problem persists ``
DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction at /usr/local/share/perl5/Apache/Ocsinventory/Server/Inventory/Update.pm line 102. DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction at /usr/local/share/perl5/Apache/Ocsinventory/Server/Inventory/Update.pm line 102. DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction at /usr/local/share/perl5/Apache/Ocsinventory/Server/Inventory/Update.pm line 102. can't find ANONSUB's 'anon19' entry. DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction at /usr/local/share/perl5/Apache/Ocsinventory/Server/Inventory/Update.pm line 102. DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction at /usr/local/share/perl5/Apache/Ocsinventory/Server/Inventory/Update.pm line 102. DBD::mysql::db do failed: Lock wait timeout exceeded; try restarting transaction at /usr/local/share/perl5/Apache/Ocsinventory/Server/Inventory/Update.pm line 102. ``
Hi,
To be honest, I installed OCS where asset number went above 50k / 100k assets and everytime I encountered this issue was due to mariadb configuration.
In some rare case, it can be due to looping agents. On big instances we recommend to setup a third party system to filter / drop to recurrent request in order to keep the service quality up.
To get more information on the lock itself you need to enable verbose loggin on mariadb and not OCS Inventory which won't provide anything related to the "why" of the locks.
On big instances, the tmpdir variable may sometimes link to a partition / directory that doesn't allow enough space and slow the mariadb instance over time.
Could you also tell me how many assets are you inventorying on your instance ? If you exceed 10k assets, having a full supported version using our professional services would be a good idea :)
Regards, Gilles DUBOIS.
Hi @bud4 ,
Any news ?
Best regards, Charlene
OCS Inventory version Version : 2.12.0 Distribution: CentOS Stream 8 db : MariaDB Server version 10.3.28-MariaDB
Describe the bug not response from ocs-report url. httpd active.
To Reproduce after days of correct functioning the web interface was no longer responsive
Log file (optional) php error log :+1: [31-Oct-2023 10:05:16.379791] DEBUG: pid 1160, fpm_pctl_perform_idle_server_maintenance(), line 382: [pool www] currently 0 active children, 6 spare children, 6 running children. Spawning rate 1 [31-Oct-2023 10:05:17.380960] DEBUG: pid 1160, fpm_pctl_perform_idle_server_maintenance(), line 382: [pool www] currently 0 active children, 6 spare children, 6 running children. Spawning rate 1 [31-Oct-2023 10:05:18.382152] DEBUG: pid 1160, fpm_pctl_perform_idle_server_maintenance(), line 382: [pool www] currently 0 active children, 6 spare children, 6 running children. Spawning rate 1 [31-Oct-2023 10:05:19.383501] DEBUG: pid 1160, fpm_pctl_perform_idle_server_maintenance(), line 382: [pool www] currently 0 active children, 6 spare children, 6 running children. Spawning rate 1 [31-Oct-2023 10:05:20.384605] DEBUG: pid 1160, fpm_pctl_perform_idle_server_maintenance(), line 382: [pool www] currently 0 active children, 6 spare children, 6 running children. Spawning rate 1 [31-Oct-2023 10:05:21.385790] DEBUG: pid 1160, fpm_pctl_perform_idle_server_maintenance(), line 382: [pool www] currently 0 active children, 6 spare children, 6 running children. Spawning rate 1 [31-Oct-2023 10:05:22.387177] DEBUG: pid 1160, fpm_pctl_perform_idle_server_maintenance(), line 382: [pool www] currently 0 active children, 6 spare children, 6 running children. Spawning rate 1 [31-Oct-2023 10:05:23.388323] DEBUG: pid 1160, fpm_pctl_perform_idle_server_maintenance(), line 382: [pool www] currently 0 active children, 6 spare children, 6 running children. Spawning rate 1
ystemctl status httpd.service ● httpd.service - The Apache HTTP Server Loaded: loaded (/etc/systemd/system/httpd.service; enabled; vendor preset: disabled) Drop-In: /etc/systemd/system/httpd.service.d └─php-fpm.conf Active: failed (Result: timeout) since Tue 2023-10-31 09:26:08 CET; 55s ago Docs: man:httpd.service(8) Main PID: 95862 (code=killed, signal=KILL) Status: "Total requests: 121028; Idle/Busy workers 62/38;Requests/sec: 0.0527; Bytes served/sec: 4.2KB/sec"
ott 31 09:26:08 ocs systemd[1]: httpd.service: Killing process 220174 (/usr/sbin/httpd) with signal SIGKILL. ott 31 09:26:08 ocs systemd[1]: httpd.service: Killing process 224181 (/usr/sbin/httpd) with signal SIGKILL. ott 31 09:26:08 ocs systemd[1]: httpd.service: Killing process 261371 (/usr/sbin/httpd) with signal SIGKILL. ott 31 09:26:08 ocs systemd[1]: httpd.service: Killing process 268158 (/usr/sbin/httpd) with signal SIGKILL. ott 31 09:26:08 ocs systemd[1]: httpd.service: Killing process 276534 (/usr/sbin/httpd) with signal SIGKILL. ott 31 09:26:08 ocs systemd[1]: httpd.service: Killing process 279842 (/usr/sbin/httpd) with signal SIGKILL. ott 31 09:26:08 ocs systemd[1]: httpd.service: Killing process 282647 (/usr/sbin/httpd) with signal SIGKILL. ott 31 09:26:08 ocs systemd[1]: httpd.service: Killing process 301189 (/usr/sbin/httpd) with signal SIGKILL. ott 31 09:26:08 ocs systemd[1]: httpd.service: Failed with result 'timeout'. ott 31 09:26:08 ocs systemd[1]: Stopped The Apache HTTP Server. bug.zip