Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"

Re^3: SQLite with mod_perl

by james2vegas (Chaplain)
on Oct 03, 2010 at 02:42 UTC ( #863129=note: print w/replies, xml ) Need Help??

in reply to Re^2: SQLite with mod_perl
in thread SQLite with mod_perl

What error are you getting? When I run code like this twice, I don't get errors, I just get waiting until the other transaction is committed:
use strict; use warnings; use DBI; my $dbh = DBI->connect("dbi:SQLite:dbname=test.sqlite","","", { RaiseError => 1, AutoCommit => 1 }); $dbh->begin_work; $dbh->do("UPDATE abc SET foo = 'heyo'"); my @x = <>; $dbh->commit;

Replies are listed 'Best First'.
Re^4: SQLite with mod_perl
by punkish (Priest) on Oct 09, 2010 at 02:12 UTC
    Hi james2vegas, sorry for the delay in reply.

    Well, I thought I had surmounted this problem, but no, it is back with a vengeance. Here is what is going on in a single web action --

    SELECT col1 FROM table WHERE condition; if (col1 exists) { UPDATE table SET col2 = <new value> }

    Well, I get the "db is locked" error at the UPDATE statement. Now, in my httpd.conf I started using

    PerlModule Apache::DBI

    and I thought my troubles were over. Except, this lock problem has started rearing up again. I have tried a few other things, such as starting every db action with $dbh->begin_work and ending with $dbh->commit, but I am already using AutoCommit => 1 so I am not sure if that even has any effect. I have even tried sqlite_use_immediate_transaction => 1, but that doesn't help either.

    My application is being loaded in Apache conf file with

    <Location /> SetHandler perl-script PerlHandler Plack::Handler::Apache2 PerlSetVar psgi_app /path/to/ </Location>

    So, I want all the perl modules to be loaded when Apache2 starts, and then a $dbh created for each user when the user comes to the web site, but not recreated for the same user on every reload. I am assuming that would be the correct way to work speedily.


    when small people start casting long shadows, it is time to go to bed
      Do not cache your database handles, rather let Apache::DBI take care of it, use Apache::DBI in your mod_perl startup script (or use PerlModule Apache::DBI in your httpd.conf) then whenever you need to open a database connection, do it as would normally my $dbh = DBI->connect(...). If you need connections per user add private_username => $username to your connection attributes, like so:
      my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile", "", "", { private_ +username => $username });
      This would ensure that each user would get a per-process database handle. Note that you can't share database handles across processes unless you use something single process like FastCGI.
        Hmmm... as I mentioned in my post above, I am indeed using  PerlModule Apache::DBI. Maybe I tinkered above (too smart for my own good) trying to cache the db handle. Now I am trying with just plain my $dbh = ... , and things seem to be working.

        One other thing I have done now -- I have turned off transactions. My db handle is now created with AutoCommit => 1.

        This is really unreliable. I have no idea what caused my app to start puking and croaking yesterday, and what caused it to start working today. I have become too dependent on SQLite (and for good reason -- it is a great db store), and I really don't know of any alternatives. Any suggestions? I could try some of the key-value stores such as MongoDB/CouchDB, etc., but it would require learning a whole new paradigm, and writing new code for retrieving values (is there a DBI interface to a key-value store? **) While I do use PostGres for the bigger datasets, SQLite seems perfect for serving as the Session store, and user accounts and such.

        Update: Hot damn! DBD::DBM


        when small people start casting long shadows, it is time to go to bed

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://863129]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (7)
As of 2018-06-20 05:48 GMT
Find Nodes?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?

    Results (116 votes). Check out past polls.