stevieb has asked for the wisdom of the Perl Monks concerning the following question:
update: davido pointed me to the documentation that states that separate processes sharing a single DB handle will almost certainly cause issues. I changed my code so that each event (process) uses its own DB handle, which all point to the same SQLite database. After that, I've got 19+ hours of successful runtime, so it seems to be resolved./update
I've got a long-running web app with Dancer2 that has asynchronous processes doing DB work outside of the main process.
I've been trying to sort out why the event crashes, and after dumping the web access requests to /dev/null, adding some debug printing in the C code that reads a sensor and updates a DB, I finally got what I was looking for. In the below error, it's reading the sensor until it gets a valid value for temp and humidity, and then the C code passes back the values to Perl. Perl then (still inside of an async event), writes these values to the DB.
However, all I can find on the error "file is encrypted..." is related to version mis-matches etc. I can't see that being the case here, because my app was running for 2.5+ hours, then broke suddenly. The web app is still running, but this event is crashed, so no more updates.
Could this be a clash of file access or something? Anyone seen this before?
DHT11 exec temp
temp data: -1
temp data: 21
temp: 21
DHT11 exec humidity
humidity data: -1
humidity data: -1
humidity data: -1
humidity data: -1
humidity data: -1
humidity data: 20
humidity: 20
DBD::SQLite::st execute failed: file is encrypted or is not a database
+ at /home/pi/repos/app-envui/bin/../lib/App/RPi/EnvUI/DB.pm line 66.
DBD::SQLite::st execute failed: database disk image is malformed at /h
+ome/pi/repos/app-envui/bin/../lib/App/RPi/EnvUI/DB.pm line 95.
Error while loading /home/pi/repos/app-envui/bin/app.pl: DBD::SQLite::
+st execute failed: database disk image is malformed at /home/pi/repos
+/app-envui/bin/../lib/App/RPi/EnvUI/DB.pm line 95.
Compilation failed in require at /home/pi/repos/app-envui/bin/app.pl l
+ine 8.
BEGIN failed--compilation aborted at /home/pi/repos/app-envui/bin/app.
+pl line 8.
[App::RPi::EnvUI:7104] error @2016-10-09 13:39:58> Route exception: DB
+D::SQLite::st execute failed: file is encrypted or is not a database
+at /home/pi/repos/app-envui/bin/../lib/App/RPi/EnvUI/DB.pm line 66. i
+n /home/pi/perl5/perlbrew/perls/perl-5.22.2/lib/site_perl/5.22.2/Danc
+er2/Core/App.pm l. 1444
(in cleanup) Can't kill a non-numeric process ID at /home/pi/perl5
+/perlbrew/perls/perl-5.22.2/lib/site_perl/5.22.2/Async/Event/Interval
+.pm line 29 during global destruction.
The last line of the error I understand; that's due to the separate proc crashing without waiting for the child proc, I just haven't tidied that up yet.
The crash is occurring on an open DB handle during a basic select statement (at least in this case it was a select).
Re: DBD::SQLite "file is encrypted or is not a database" in running application
by BrowserUk (Patriarch) on Oct 09, 2016 at 21:28 UTC
|
Not sure if this will be helpful or not, even if it is correct.
Based totally upon your description -- or more correctly, my interpretation of your description -- this sounds like a classic case of corruption due to overlapping concurrent writes to a system that has no protection -- locking -- against it.
If that is a possibility -- hard to tell from the description -- then my first step to confirm the diagnosis would be to implement a very simple locking mechanism. On windows I'd use a CriticalSection as a starting point -- it's not the most efficient mechanism, but it is simple and robust. If that confirmed the diagnosis I'd then look to use one of the more efficient, lightweight mechanisms.
I can't advise on appropriate mechanisms for *nix.
With the rise and rise of 'Social' network sites: 'Computers are making people easier to use everyday'
Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
In the absence of evidence, opinion is indistinguishable from prejudice.
| [reply] |
|
This is where I was leaning as soon as I was able to get the error output of my crashes.
Tomorrow, in hopes of repro-ing in a more consistent way, I'm going to throw together a perl script that spawns off a bunch of children that continuously write to the db simultaneously to try to force the same crash, then I'll play around with locking code, and test it with this mentioned setup.
I'll post back on this thread with what I find, and how I get around to possibly solving it.
| [reply] |
|
| [reply] |
Re: DBD::SQLite "file is encrypted or is not a database" in running application (repro code)
by stevieb (Canon) on Oct 10, 2016 at 13:48 UTC
|
I've written a Parallel::ForkManager reproduction script in hopes others can review/test and provide feedback. It doesn't involve any convoluted event code.
I created a simple database by running sqlite3 test.db < test.sql where test.sql consists of:
DROP TABLE IF EXISTS test;
CREATE TABLE test (
id VARCHAR(50),
value VARCHAR(20)
);
INSERT INTO test VALUES ('test', 0);
Here is the code. Note that lowering the num of forks, and/or tweaking with sleeping will evade the issue... for a period only. It eventually creeps back in. The code below breaks nearly immediately (on my Raspberry Pi 3):
use warnings;
use strict;
use DBI;
use Parallel::ForkManager;
my $dbh = DBI->connect(
"dbi:SQLite:dbname=test.db",
"",
"",
{RaiseError => 1}
) or die $DBI::errstr;
my $pm = Parallel::ForkManager->new(20);
for (1..20){
$pm->start and next;
my $interval = $_ / 10;
while (1){
dbwrite($dbh, $interval);
#print "$interval\n";
}
}
while(1){
my $sth = $dbh->prepare(
"SELECT * FROM test WHERE id=?;"
);
$sth->execute('test');
my $val = $sth->fetchrow_hashref()->{value};
#sleep 1;
}
sub dbwrite {
my ($dbh, $interval) = @_;
my $sth = $dbh->prepare(
'UPDATE test SET value=? WHERE id="test;"'
);
$sth->execute($interval);
};
On my more powerful laptop with an SSD disk, it takes ~10 seconds to break. Now, my application doesn't process nearly as fast as this script does. That said, it still isn't good, and I need to find a reliable solution.
Are there any reasonable fixes for this, or should I instead be looking at using a MySQL database on a remote server? DB read/writes in the real app I'm writing happen at most, every second, give or take.
I'd really, really like to avoid using an external DB if possible; I want this app to be self-contained. I can't use a memory db (ie. I don't know HOW to share a memory db between different processes, so any pointers here that I can test will be quite welcome as well).
Thanks for all the feedback Monks.
-stevieb | [reply] [d/l] [select] |
|
See the POD for DBD::SQLite which states:
You shouldn't (re)use a database handle you created...before you fork(). Otherwise, you might see a database corruption in the worst case. If you need to fork(), (re)open a database after you fork().
Your sample code seems to be doing exactly what the POD suggests shouldn't be done; sharing a database handle across forked children.
SQLite's locking relies on the operating system's flock. Sharing a database handle probably shares the underlying filehandles, and at least on my Linux systems calling for an exclusive flock on the same filehandle twice is perfectly OK.
The manpage for GNU/Linux flock indicates that calling flock more than one time on a file descriptor is ok, and an already locked file will convert an existing lock to the new lock mode. In the case of your sample script this could be even worse, because your forked children are still alive while you enter the 2nd while(1) loop. Inside your 2nd while(1) you are doing SELECT's, which may be converting the database handle's LOCK_EX to a LOCK_SH. ...probably doesn't matter though; each child reusing the same handle is able to quietly obtain a lock, so the previous lock state is irrelevant -- they all stomp on each other without warning.
Instantiate a new database handle in each child, and the problem should go away.
| [reply] |
|
Thank you so much!
That apparently was the problem after a few quick tests. I updated my test script to the below (each fork() creates its own db handle), and letting it run for ~5 minutes, there are no issues. I'll apply similar logic to my real app and see how it goes, then I'll mark this thread solved if the problem vanishes.
I was looking in all the wrong places for information. I should have done more than just skimmed the docs. sigh.
use warnings;
use strict;
use Parallel::ForkManager;
use DBI;
my $db_file = 'test.db';
my $dbh = DBI->connect(
"dbi:SQLite:dbname=$db_file",
"",
"",
{RaiseError => 1}
) or die $DBI::errstr;
my $pm = Parallel::ForkManager->new(20);
for (1..2){
$pm->start and next;
my $interval = $_ / 5;
my $dbh = DBI->connect(
"dbi:SQLite:dbname=$db_file",
"",
"",
{RaiseError => 1}
) or die $DBI::errstr;
while (1){
dbwrite($dbh, $interval);
#sleep 1;
}
}
while(1){
my $sth = $dbh->prepare(
"SELECT * FROM test WHERE id=?;"
);
$sth->execute('test');
my $val = $sth->fetchrow_hashref()->{value};
print "$val\n";
}
sub dbwrite {
my ($dbh, $interval) = @_;
my $sth = $dbh->prepare(
'UPDATE test SET value=? WHERE id="test";'
);
$sth->execute($interval);
};
update: I did trigger the below error after some time, but that's a different problem that it appears as though there are built-in workarounds for that I'll play with (mind you, it's probably triggered because we're write/read to the db at full speed):
DBD::SQLite::st execute failed: database is locked at pm.pl line 49.
/update | [reply] [d/l] [select] |
|
| [reply] |
|
Hey Dave,
I just want to say that having each process use its own DB handle seemed to have corrected my issues.
The way this particular app works is there's a core API class (OO) at its centre. There's also a DB class (OO) that manages all of the database work (it creates the actual DB handle in the new() call). The DB handle gets inserted into the DB object. The API is the only thing that talks to the DB object, so the DB object is inserted into the API object.
So, what I've done is this:
- Dancer2 app library creates a new API object on instantiation, and this is what the Dancer2 routes use to make calls to and get work done
- when the API object is first instantiated, it spins up two events that run in separate processes. When these event objects are created, they are passed in a *copy* of the API object (no sharing between procs)
- the event objects then instantiate their own DB object (which contains a DB handle internally), and inserts that DB object into the API object, overwriting the object that was created during the Dancer2 initial instantiation
- now the Dancer2 process has its own copy of the API with it's own DB object (and therefore its own DB handle), and each event has the same configuration
I know it's not very elegant at the moment, but I have quite a good test suite, so after some more testing, I'll be able to clean it up significantly now that I know what the issue was.
I appreciate the feedback in helping me get this resolved.
ps. The app has been running for 19+ hours as of now with very consistent memory footprint and CPU usage, all of my timers work properly in a real run, and it seems very stable.
| [reply] [d/l] [select] |
Re: DBD::SQLite "file is encrypted or is not a database" in running application
by Anonymous Monk on Oct 09, 2016 at 21:35 UTC
|
What happens if you connect and only execute PRAGMA integrity_check? | [reply] [d/l] |
|
That's kind of irrelevant here I believe, as the DB handle is already open and working, then after the crash, a simple restart of the app loads the DB without any issue.
update: fwiw, immediately following a crash:
sqlite> pragma integrity_check;
ok
/update | [reply] [d/l] |
Re: DBD::SQLite "file is encrypted or is not a database" in running application
by Anonymous Monk on Oct 09, 2016 at 21:33 UTC
|
can sqlite3 open the file? | [reply] |
|
Yes, as I said, this is in a live running program, where the DB handle has been opened for 2+ hours already with no issues. It crashes during this live run.
I'm definitely feeling it's a locking issue as BrowserUK said, so tomorrow, I'll focus my efforts there.
| [reply] |
|
For me, it was just a wrong SQLite DB password. When I put in the right one, all was well again.
| [reply] |
|
|