[1.3.1]: SQLite lock when using frontend-php

[LEM]

Hi,

I often cannot use the frontend with SQLite support (correctly) activated, related to locking issues. For example, I come to the Login page, and enter 'Admin/no passwd' and get the following error:

Error in query [insert into sessions (sessionid,userid,lastaccess) values ('2aa3a8d5aeafa7168b99e92f61de1c8c',1,1166038148)] [database is locked]

zabbix_agentd and zabbix_server are running on the same host than the frontend-php.

zabbix_agentd (log level 4) continuously complain about:

 16762:20061213:202747 Before read
 16762:20061213:202750 Timeout while answering request
 16762:20061213:202750 Timeout while receiving data from [127.0.0.1:10051]
 16762:20061213:202750 Getting list of active checks failed. Will retry after 60 seconds
 16762:20061213:202850 get_active_checks('127.0.0.1',10051)
 16762:20061213:202850 Sending [ZBX_GET_ACTIVE_CHECKS
homebox
]
 16762:20061213:202850 Before read
 16762:20061213:202853 Timeout while answering request
 16762:20061213:202853 Timeout while receiving data from [127.0.0.1:10051]
 16762:20061213:202853 Getting list of active checks failed. Will retry after 60 seconds
 16762:20061213:202953 get_active_checks('127.0.0.1',10051)
 16762:20061213:202953 Sending [ZBX_GET_ACTIVE_CHECKS
homebox
]
 16762:20061213:202953 Before read
 16762:20061213:202956 Timeout while answering request
 16762:20061213:202956 Timeout while receiving data from [127.0.0.1:10051]
 16762:20061213:202956 Getting list of active checks failed. Will retry after 60 seconds
 16762:20061213:203056 get_active_checks('127.0.0.1',10051)
 16762:20061213:203059 Sending [ZBX_GET_ACTIVE_CHECKS
homebox
]

while zabbix_server (log level 4) continously complain about:

 16698:20061213:035010 Trapper got [ZBX_GET_ACTIVE_CHECKS
homebox]
 16698:20061213:035010 In autoregister(homebox)
 16698:20061213:035010 Executing query:select hostid from hosts where host='homebox'
 16699:20061213:035113 After accept()
 16699:20061213:035113 Old protocol
 16699:20061213:035113 After read() 3 [25]
 16699:20061213:035113 Got data:ZBX_GET_ACTIVE_CHECKS
homebox

 16699:20061213:035113 Trapper got [ZBX_GET_ACTIVE_CHECKS
homebox]
 16699:20061213:035113 In autoregister(homebox)
 16699:20061213:035113 Executing query:select hostid from hosts where host='homebox'
 16697:20061213:035216 After accept()
 16697:20061213:035216 Old protocol
 16697:20061213:035216 After read() 3 [25]
 16697:20061213:035216 Got data:ZBX_GET_ACTIVE_CHECKS
homebox

 16697:20061213:035216 Trapper got [ZBX_GET_ACTIVE_CHECKS
homebox]
 16697:20061213:035216 In autoregister(homebox)
 16697:20061213:035216 Executing query:select hostid from hosts where host='homebox'
 16702:20061213:044754 Executing housekeeper

fuser /tmp/zabbix.db does not return me any process using the SQLite database.

Any ideas for this annoying (make the frontend-php not usable most of the time) locking issue ?

Cheers,

[Alexei]

ZABBIX server and the front-end use single UNIX semaphore to prevent any locking issues with SQLite. SQLite has highly unefficient handling of locks!

I believe that you have some problems with user permissions. Apache user must have access to ZABBIX server IPC resources. The semaphore is created by ZABBIX server, user 'zabbix", with rw+rw+rw permissions. So normally Apache user should have access to the semaphore. Perhaps you see some warnings/erros in the front-end related to sempahores?

Perhaps some other process (apart of ZABBIX server/frontend) is accessing ZABBIX database?

By the way, fuser <zabbix database> won't help you much because for each transaction SQLite does:

1. file open
2. do chanches
3. file close

It is very likely that you run fuser in between transactions.

[LEM]

ZABBIX server and the front-end use single UNIX semaphore to prevent any locking issues with SQLite. SQLite has highly unefficient handling of locks!

I believe that you have some problems with user permissions. Apache user must have access to ZABBIX server IPC resources. The semaphore is created by ZABBIX server, user 'zabbix", with rw+rw+rw permissions. So normally Apache user should have access to the semaphore. Perhaps you see some warnings/erros in the front-end related to sempahores?

Ok. I changed my Apache to run under zabbix.zabbix (User/Group directives) and this dont solve the issue:

Error in query [insert into sessions (sessionid,userid,lastaccess) values ('4f7ab3f87f59c8a3a635f55c0707d2ab',1,1166135480)] [database is locked]

So, same user running server and frontend don't solve the issue

Perhaps some other process (apart of ZABBIX server/frontend) is accessing ZABBIX database?

By the way, fuser <zabbix database> won't help you much because for each transaction SQLite does:

1. file open
2. do chanches
3. file close

It is very likely that you run fuser in between transactions.

Ok... But, stopping the zabbix_server solve the locking issue for the frontend (I repeated this thing before beeing sure, of course). So I feel the issue is not related to other procesess using the zabbix db.

Do you see any other way I could follow to track this issue down (with our precious help) ?

[Alexei]

It seems that I provided incorrect details about details of SQLite implementation, sorry.

In fact ZABBIX uses two semaphores (not one as I stated before!), one for server and onother for the front-end.

It means that there could be situations when front-end 'conflicts' with the server, however both server and front-end will retry update operation many times before giving up. Perhaps this is not perfect solution for busy systems.

I'm going to make more tests today...