Error during table cpzone1 creation. Error message: database is locked
-
Hi,
I am using Pfsense at 2 more places and at every place no pf users are 1200-1400. I have never faced any database locked error or CP service stopped error and there are always 200-400 concurrent users 24/7.
And there also I am using squid2.7 + squid-guard and CP authentication and using 2.2.5 version, but started from version 2.0.1 and never faced any such difficulties .
Regards
-
This is the root issue, we're working on it.
https://redmine.pfsense.org/issues/5622 -
Hi,
Hope to see a solution soon.
Thanks and regards
-
"Soon" might be right now : https://forum.pfsense.org/index.php?topic=103732.new;topicseen#new
-
ok, I got it.
So should I undo the earlier changes made in /var/run ?
Regards
-
So should I undo the earlier changes made in /var/run ?
Absolutely, yes. That was never a good idea. Though you can just wait until a fixed snapshot's available and upgrade to it to make sure you're back to a sane state.
I just committed what should be a good fix for the issue here. 2.2.6 snapshot builders just restarted, we'll have a new snapshot in a couple hours that'll hopefully fix this.
-
Those who are seeing this issue, please try the newest available 2.2.6 snapshot and report back. It looks to be fixed, but I would like additional confirmation.
64 bit
https://snapshots.pfsense.org/FreeBSD_releng/10.1/amd64/pfSense_RELENG_2_2/updates/?C=M;O=D32 bit
https://snapshots.pfsense.org/FreeBSD_releng/10.1/i386/pfSense_RELENG_2_2/updates/?C=M;O=D -
Ok,
I,ll apply the 2.2.6-64bit snapshot and report back.
Thanks and Regards,
-
Sorry for late reply. As I cant afford to down server even for 15 mins for last 4 days for some urgent time bound jobs.
I just upgraded to 2.2.6 release.
AND again I just saw the following error at CP login page, as this is the best time to check this error as lots of hit for CP authentication are coming, what I do, I keep on refreshing the CP authentication page, I do not enter my login id and password. So after 2-3 refreshes I am able to see this error on my system. So anywhere else in the network, other people would also be seeing this error.
Warning: SQLite3::exec(): database is locked in /etc/inc/captiveportal.inc on line 2045 Warning: SQLite3::query(): Unable to prepare statement: 5, database is locked in /etc/inc/captiveportal.inc on line 2057
I haven't seen this error in last 4-5 days when I implemented changes suggest by Gertjan.
I am reverting back to Gertjan suggested changes for the time being…
Regards
Regards & Thanks
-
Warning: SQLite3::exec(): database is locked in /etc/inc/captiveportal.inc on line 2045
Line 2045 : https://github.com/pfsense/pfsense/blob/RELENG_2_2/etc/inc/captiveportal.inc#L2045 - a simple log line
@amitaussie:Warning: SQLite3::query(): Unable to prepare statement: 5, database is locked in /etc/inc/captiveportal.inc on line 2057
Line 2057 : https://github.com/pfsense/pfsense/blob/RELENG_2_2/etc/inc/captiveportal.inc#L2057 == empty line ??,
Your file /etc/inc/captiveportal.inc contains the edits that puts the database file on RAM (what I suggested in the beginning), but this doesn't explain why your version of /etc/inc/captiveportal.inc is so much different as the original : https://github.com/pfsense/pfsense/blob/RELENG_2_2/etc/inc/captiveportal.inc
The database error should happen around lines 1300 - 1400 ….
You're NOT using an original pfsense setup, it has been patched with something else ?? -
Hi,
Gertjan!
My captiveportal.inc is different as I am using a file which is patched for manual logout as mentioned in this post of yours
https://forum.pfsense.org/index.php?topic=77143.15But after making changes in /var/run database error is certainly not appearing and everything is working ok.
Regards
-
Hi
Gertjan!
I am not able to find changes you suggested for /var/run.
Can you please re-send the changes as these were helping.
Regards -
I am not able to find changes you suggested for /var/run.
Can you please re-send the changes as these were helping.
They should be on the forum - I didn't remove anything.
It was a question of changing the the 2 places where the data base was created.But, be careful : read this https://forum.pfsense.org/index.php?topic=103707.msg580857#msg580857 (post above).
I don't know why it is a bad idea, but if cmb says so, I will not discus that (although I do want to the the 'why' part ;)). -
Hi
Gertjan!
I am not able to find changes you suggested for /var/run.
I removed them because they weren't related to the root issue and weren't a good idea. Just upgrade to 2.2.6, that will properly fix the root issue.
-
@cmb:
I removed them because ….....
That's what I figured out.
@amitaussie : I advise you to use a clean "2.2.6" version (undo ALL changes in /etc/inc/captiveportal.inc).
If you still see the same issues ("database locked") then I really start to think that your hardware isn't simply fast enough ….edit : @cmb : I wasn't looking for solving a programming error, but using a RAM disk instead of ordinary disk so treatment would speed up.
From what I made of it, database locking and waiting for it wasn't optimal, so their was a chance an execution path (user logging in) could fall into the attempt of another user logging in at the same time. The database would be locked at the same, the last attempts bails out.
Now, if locking, updating and unlocking takes time, and enough attempt are made, the locking issue start to pop up (race condition).
That's why I was thing of borrowing temporarily some RAM space.Of course, when pfSense is rebooted, this RAM based database file will be gone which produces new unpredictable results.
When the (very small) RAM disk fills up, new, totally unpredictable results will pop up.
Etc ....The test existed in the fist place to see if "time" could be an issue. It clearly was.
-
Indeed because the RAM disk would be much faster than the hard drive, the locked operations would complete faster, which would lessen the chance of hitting the lock contention. It was a fine suggestion for something to try at the time, when we hadn't identified the root cause or fixed it yet. But once the lock issue was fixed, making those kinds of changes aren't desirable, which is why I removed it after the root problem was fixed. Otherwise what we've seen in similar situations in the past is people making changes that were intended to address an issue that no longer exists and just causing problems in the process (then reporting problems, not mentioning the changes they made, and we chase our tails wasting time trying to figure out what the issue is, when it was actually user modification induced).
If anyone's still seeing any locking issues, please make sure you're on a stock 2.2.6 release (no modifications), and start a new thread describing what's happening. The issue in this thread was resolved, so something different is happening if you're still seeing that.
-
Hi
Gertjan & CMB
Thanks for your valuable inputs.
As suggested I have made a clean install of 2.2.6 and no more database locked errors are found.
Then I changed 2.2.6 captiveportal.inc & index.php for manual logout page as mentioned in https://forum.pfsense.org/index.php?topic=77143.0
Everything is working fine now with manual logout page.
Earlier when i upgraded to 2.2.6, I used to copy modified captiveportal.inc & index.php from version 2.2.4 to get manual logout page. I didnt modify 2.2.6 version captiveportal.inc & index.php for manual logout page. So that's why I was getting database error after upgrading to 2.2.6.
Sorry, it was my fault, I should have made changes to 2.2.6 version captiveportal.inc & index.php files to get logout page.
Thanks & regards
