Error during table cpzone1 creation. Error message: database is locked

Gertjan · Dec 11, 2015, 7:18 AM

Nope, I don't have any tool like this, I check users and bandwidth graphs on RRd. What more functionalities does your graph tool give than RRD?

I wanted to have a tool that monitors my servers (on the Internet). I added also some graphs coming from pfSense, which isn't "on the net" of course.
It helps me to see from everywhere 'on the planet' how things are going without logging in remotely.
If I was responsible for the access of several thousand of users, I would really consider adding such tools. It could run on a 'couple of $' VPS.

My pfsense setup https://www.test-domaine.fr/munin/brit-hotel-fumel.net/pfsense.brit-hotel-fumel.net/index.html (and look one level up, you could see all my servers).

@amitaussie:

Good thing is I myself have faced this error of database is locked till yesterday, but after changes, it doesn't occur before me, but updates from other users are still pending…

So far, so good :)
If users have logging problems related to the errors you showed above, then log-lines present in pfsense WILL tell you even before they come to you.

Keep checking the size of the RAM disk - when this one goes full, pfsense WILL panic. (but I guess it won't happen).

Gertjan · Dec 11, 2015, 9:33 PM

Check out this https://forum.pfsense.org/index.php?topic=103732.0 (it concerns a pfsense Beta 2.3 but the part where the errors come from looks the same / didn't change … )
Time for a :( I guess ....
Probably we are on the wrong track.

=> Still no errors anymore ?

Btw : your are using a VM or is your pfsense running on a native device ?

amitaussie · Dec 13, 2015, 9:52 AM

Hi,

Gertjan,

There are no database errors reported from anywhere in the network till

I think that changes have worked, I ll keep checking it for few more days.

Thank you,

I have read that thread you mentioned, but those errors might be due to vmware.

I am not using vmware. Pfsense is running on native hardware.

I am facing another CP error though, this error has not started after the changes but it was already happening.

Error is CP service stops and then CP page does not pop up.
CP service does stop in 48 hours. I have also seen sometimes squidguard service does stop but it starts automatically but CP service does not start automatically, I manually have to restart.

Gertjan · Dec 13, 2015, 8:01 PM

@amitaussie:

….
Error is CP service stops and then CP page does not pop up.
CP service does stop in 48 hours. I have also seen sometimes squidguard service does stop but it starts automatically but CP service does not start automatically, I manually have to restart.

Before (2.2.5 ?) squid and Captive portal was impossible.
Now : I don't know (I don't know why I should use Squid …)
My Captive Portail never dies.

All I know is : Squid + Captive portal …. unstable, or worse.

amitaussie · Dec 14, 2015, 4:44 AM

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

cmb · Dec 16, 2015, 12:23 AM

This is the root issue, we're working on it.
https://redmine.pfsense.org/issues/5622

amitaussie · Dec 16, 2015, 11:36 AM

Hi,

Hope to see a solution soon.

Thanks and regards

Gertjan · Dec 16, 2015, 9:05 PM

"Soon" might be right now : https://forum.pfsense.org/index.php?topic=103732.new;topicseen#new

amitaussie · Dec 17, 2015, 4:19 PM

ok, I got it.

So should I undo the earlier changes made in /var/run ?

Regards

cmb · Dec 17, 2015, 10:45 PM

@amitaussie:

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.

cmb · Dec 18, 2015, 1:55 AM

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=D

32 bit
https://snapshots.pfsense.org/FreeBSD_releng/10.1/i386/pfSense_RELENG_2_2/updates/?C=M;O=D

amitaussie · Dec 18, 2015, 3:42 PM

Ok,

I,ll apply the 2.2.6-64bit snapshot and report back.

Thanks and Regards,

amitaussie · Dec 24, 2015, 3:24 AM

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

Gertjan · Dec 24, 2015, 6:52 AM

@amitaussie:

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 ??

amitaussie · Dec 24, 2015, 10:50 AM

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.15

But after making changes in /var/run database error is certainly not appearing and everything is working ok.

Regards

amitaussie · Jan 7, 2016, 2:33 PM

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

Gertjan · Jan 7, 2016, 4:03 PM

@amitaussie:

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

cmb · Jan 7, 2016, 7:36 PM

@amitaussie:

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.

Gertjan · Jan 8, 2016, 8:15 AM

@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.

cmb · Jan 9, 2016, 12:46 AM

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.