Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic

comment on

( #3333=superdoc: print w/replies, xml ) Need Help??
I'm writing data to an SQLite db from multiple child processes managed by Parallel::ProcManager. Unfortunately several odd things are happening.
  1. I'm getting error messages indicating that the database is corrupt; yet it seems not to be.
  2. Even though I've got RaiseError on and PrintError off, the error messages are still output and I can't catch them via an eval. I have to resort to checking execute()'s return value.
Test code is at the end of this post. The order and frequencies of errors are not deterministic, but here are those that are output to the standard error stream:
  • DBD::SQLite::st execute failed: file is encrypted or is not a database
  • DBD::SQLite::st execute failed: database disk image is malformed
  • DBD::SQLite::st execute failed: database is locked
The database is locked errors are expected, as the database is being hammered with 30 processes which do nothing but attempt to update it.

I've run this code on both Linux and Mac OS X boxes with the same errors (I seem to get many more errors on the OS X box).

I've applied (correctly I hope) the advice from the DBI documentation regarding the AutoInactiveDestroy flag.

I've read out on the 'net that the malformed or not a database errors may be because of busy timeouts. That doesn't seem to be the case here, as I've set a busy timeout of 5 seconds, and the vast majority of these errors appear before 5 seconds in the child process have elapsed. My questions are:

  1. Am I missing something in setting up the database connection so that it works correctly across forks?
  2. Is there an SQLite parameter I need to set to get database locking to work? My impression from the DBD::SQLite documentation is that it does this automatically.
  3. Why doesn't RaiseError work? This is the first time I've seen this happen (I usually work with PostgreSQL and Sybase; not much experience with SQLite)
Any help is appreciated.


Directly after posting (of course), I noticed that I hadn't supplied the correct number of parameters to DBI->connect; I'd left out the username and password parameters. After fixing that my problems with catching exeptions is fixed. I've amended the code below to include that fix.

Here's the test code:

#!/usr/bin/env perl use strict; use warnings; use DBI; use Parallel::ForkManager; use Time::HiRes qw[ gettimeofday ]; use Getopt::Long; my %opt; GetOptions( \%opt, qw[ nproc|p=i db=s njobs|j=i timeout=f ] ) or die; $opt{nproc} = 30 unless defined $opt{nproc}; $opt{njobs} = 1000 unless defined $opt{njobs}; $opt{timeout} = 5 unless defined $opt{timeout}; die( "must specify --db\n" ) unless defined $opt{db}; unlink( $opt{db} ); my $dbh = DBI->connect( "dbi:SQLite:dbname=$opt{db}", '', '', { PrintError => 0, AutoCommit => 1, AutoInactiveDestroy => 1, RaiseError => 1, } ) or die( "unable to create db\n" ); $dbh->sqlite_busy_timeout( $opt{timeout} * 1000 ); $dbh->do( "create table test ( a int, b int )" ); my $pm = Parallel::ForkManager->new( $opt{nproc} ); for my $idx ( 1 .. $opt{njobs} ) { $pm->start and next; my $time_of_day = gettimeofday; update_results( $dbh, $idx % 5, $$, $time_of_day ); $pm->finish; } $pm->wait_all_children; sub update_results { my ( $dbh, $a, $b, $time_of_day ) = @_; my $sth_update = $dbh->prepare( q[ update test set b = ? where a = ? ] ); my $sth_insert = $dbh->prepare( q[ insert into test ( a, b ) values ( ?, ?) ] ); eval { my $rv = $sth_update->execute( $b, $a ); die( "error in attempt to update: @{[ $dbh->errstr ]}\n" ) if !defined $rv; if ( 0 == $rv ) { $rv = $sth_insert->execute( $a, $b ); die( "error in attempt to insert: @{[ $dbh->errstr ]}\n" ) if !defined $rv; die( "$$: error updating status for a = $a\n" ) unless $rv; } }; my $err = $@; if ( $err ) { my $elapsed_time = gettimeofday() - $time_of_day; # complain if not a busy timeout error message if ( $elapsed_time > $opt{timeout} && $err !~ /database is loc +ked/ ) { die "ERROR: time overrun: \[$$] elapsed time: $elapsed_tim +e: $@" if $@; } die "ERROR: \[$$] elapsed time: $elapsed_time: $@" if $@; } }

In reply to Unexpected and uncatchable database corruption errors using DBD::SQLite & Parallel::ForkManager by djerius

Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":

  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or How to display code and escape characters are good places to start.
Log In?

What's my password?
Create A New User
Domain Nodelet?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (1)
As of 2023-09-23 11:31 GMT
Find Nodes?
    Voting Booth?

    No recent polls found