solving the infamous, irritating "database locked" problem

classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

solving the infamous, irritating "database locked" problem

Puneet Kishor
I've been a reading a lot on the "database locked" problem, but still
need guidance trying to locate the source of my problem.

environment: DBI/DBD::SQLite (latest versions) with SQLite3 (3.2.1) on
Mac OS X (10.3.9).

I am trying to update a table via the web. The UPDATE in question is
the very first (and the only) DML statement. Yet, I get the "database
locked" error.

The sequence of commands is

my $sql = "UPDATE statement";
my $sth = $dbh->prepare(qq{$sql});
$sth->execute;
$dbh->commit;

My question is: could I set some kind of "trace" that tells me what is
going on with SQLite?

Many thanks.

--
Puneet Kishor

Reply | Threaded
Open this post in threaded view
|

Re: solving the infamous, irritating "database locked" problem

Jay Sprenkle
I'm not familar with the wrapper you're using, but don't you have a
commit without a matching begin? Did you establish a lock on the table
before
trying to update? Are you updating a table that you currently are reading from?
As in:
select * from t;
for each result
  update t set field = blah;
next

On 6/9/05, Puneet Kishor <[hidden email]> wrote:

> I've been a reading a lot on the "database locked" problem, but still
> need guidance trying to locate the source of my problem.
>
> environment: DBI/DBD::SQLite (latest versions) with SQLite3 (3.2.1) on
> Mac OS X (10.3.9).
>
> I am trying to update a table via the web. The UPDATE in question is
> the very first (and the only) DML statement. Yet, I get the "database
> locked" error.
>
> The sequence of commands is
>
> my $sql = "UPDATE statement";
> my $sth = $dbh->prepare(qq{$sql});
> $sth->execute;
> $dbh->commit;
>
> My question is: could I set some kind of "trace" that tells me what is
> going on with SQLite?
>
> Many thanks.
>
> --
> Puneet Kishor
>
>


--
---
You a Gamer? If you're near Kansas City:
Conquest 36
https://events.reddawn.net

The Castles of Dereth Calendar: a tour of the art and architecture of
Asheron's Call
http://www.lulu.com/content/77264
Reply | Threaded
Open this post in threaded view
|

Re: solving the infamous, irritating "database locked" problem

Puneet Kishor

On Jun 9, 2005, at 9:35 AM, Jay Sprenkle wrote:

> I'm not familar with the wrapper you're using, but don't you have a
> commit without a matching begin?

The commit is in the "wrapper," not in the SQL. When making a database
connection, I specified to turn AutoCommit to OFF. Hence, I have to
explicitly $dbh->commit (unless I understand DBI incorrectly, in which
case, I hope some Perl-ers on this list might correct me).

> Did you establish a lock on the table
> before
> trying to update?

Nope, nothing that I did to create such a lock. The UPDATE in question
is the very first statement.

> Are you updating a table that you currently are reading from?
> As in:
> select * from t;
> for each result
>   update t set field = blah;
> next

Nope. the sequence I've described below is exactly how it goes. Create
an UPDATE statement. Execute it. Commit it. Wait for the error message
to pop up telling me that the database is locked.

Harrrrumph!


>
> On 6/9/05, Puneet Kishor <[hidden email]> wrote:
>> I've been a reading a lot on the "database locked" problem, but still
>> need guidance trying to locate the source of my problem.
>>
>> environment: DBI/DBD::SQLite (latest versions) with SQLite3 (3.2.1) on
>> Mac OS X (10.3.9).
>>
>> I am trying to update a table via the web. The UPDATE in question is
>> the very first (and the only) DML statement. Yet, I get the "database
>> locked" error.
>>
>> The sequence of commands is
>>
>> my $sql = "UPDATE statement";
>> my $sth = $dbh->prepare(qq{$sql});
>> $sth->execute;
>> $dbh->commit;
>>
>> My question is: could I set some kind of "trace" that tells me what is
>> going on with SQLite?
>>
>> Many thanks.
>>
>> --
>> Puneet Kishor
>>
>>
>
>
> --
> ---
> You a Gamer? If you're near Kansas City:
> Conquest 36
> https://events.reddawn.net
>
> The Castles of Dereth Calendar: a tour of the art and architecture of
> Asheron's Call
> http://www.lulu.com/content/77264
>
--
Puneet Kishor

Reply | Threaded
Open this post in threaded view
|

Re: solving the infamous, irritating "database locked" problem

nickg-3
In reply to this post by Jay Sprenkle
Hi,

I'm not sure of your environment, but here's some tidbits that might  
help:

If you app crashes mid-way (say due to a syntax error or an exception  
popped), the db can remain locked.    And... if you are doing webby  
related work and calling sqlite and something crashes, the lock isn't  
released since the process that acquired the lock is still running  
(the web server).  Sometimes restarting the webserver helps, other  
wise you have to delete the journal file.

I'm still working on bulletproofing an app, but you may wish to wrap  
your core code in a try/catch block and then close/commit/release the  
connection to make sure you don't lock the db by mistake.

hope this helps,

--nickg

On Jun 9, 2005, at 10:35 AM, Jay Sprenkle wrote:


> I'm not familar with the wrapper you're using, but don't you have a
> commit without a matching begin? Did you establish a lock on the table
> before
> trying to update? Are you updating a table that you currently are  
> reading from?
> As in:
> select * from t;
> for each result
>   update t set field = blah;
> next
>
> On 6/9/05, Puneet Kishor <[hidden email]> wrote:
>
>
>> I've been a reading a lot on the "database locked" problem, but still
>> need guidance trying to locate the source of my problem.
>>
>> environment: DBI/DBD::SQLite (latest versions) with SQLite3  
>> (3.2.1) on
>> Mac OS X (10.3.9).
>>
>> I am trying to update a table via the web. The UPDATE in question is
>> the very first (and the only) DML statement. Yet, I get the "database
>> locked" error.
>>
>> The sequence of commands is
>>
>> my $sql = "UPDATE statement";
>> my $sth = $dbh->prepare(qq{$sql});
>> $sth->execute;
>> $dbh->commit;
>>
>> My question is: could I set some kind of "trace" that tells me  
>> what is
>> going on with SQLite?
>>
>> Many thanks.
>>
>> --
>> Puneet Kishor
>>
>>
>>
>>
>
>
> --
> ---
> You a Gamer? If you're near Kansas City:
> Conquest 36
> https://events.reddawn.net
>
> The Castles of Dereth Calendar: a tour of the art and architecture of
> Asheron's Call
> http://www.lulu.com/content/77264
>
>


Reply | Threaded
Open this post in threaded view
|

quoting strings issue

Puneet Kishor
In reply to this post by Jay Sprenkle
While I await some insight into my previously posted "database locking"
problem, I have a question regarding quoting text.

Does SQLite have any issues with anything other than single-quotes? For
example, with colon, or semi-colon?

For example, I find occasional complaints if I try to update/insert a
string like --

$text = 'Some long-winded text with lot''s of different things like:

* lists
* more lists

text with semi-colon; and even such: stuff.';

UPDATE tbl SET col = $text WHERE ...

causes error to effect that sql prepare failed (again, Perl/DBI
specific errors).


--
Puneet Kishor

Reply | Threaded
Open this post in threaded view
|

Re: quoting strings issue

Clark Christensen


--- Puneet Kishor <[hidden email]> wrote:

> While I await some insight into my previously posted
> "database locking"
> problem, I have a question regarding quoting text.
>
> Does SQLite have any issues with anything other than
> single-quotes? For
> example, with colon, or semi-colon?
>
> For example, I find occasional complaints if I try to
> update/insert a
> string like --
>
> $text = 'Some long-winded text with lot''s of different
> things like:
>
> * lists
> * more lists
>
> text with semi-colon; and even such: stuff.';
>
> UPDATE tbl SET col = $text WHERE ...
>
> causes error to effect that sql prepare failed (again,
> Perl/DBI
> specific errors).
>
>
> --
> Puneet Kishor
>
>
I think your $dbh->prepare() would be likely to fail if the
value you're updating isn't properly quoted for the SQL
statement.

The Perl DBI lets you get past this to some extent by
allowing you to use replaceable vars in the prepared
statement, and passing those vars as args to
$dbh->execute().  DBI also offers a quote() method against
the $dbh that could help avoid problems executing a
prepared stmt.

IOW, something like.

$sql = "update t1 set a = ?";
$string = $dbh->quote( qq(some long string; has many
'single quotes') );
$sth = $dbh->prepare($sql);
$rc = $sth->execute($string);

will probably eliminate both the prepare() error, and an
UPDATE error later.

 -Clark

Reply | Threaded
Open this post in threaded view
|

Re: quoting strings issue

Dennis Cote
Clark Christensen wrote:

>IOW, something like.
>
>$sql = "update t1 set a = ?";
>$string = $dbh->quote( qq(some long string; has many
>'single quotes') );
>$sth = $dbh->prepare($sql);
>$rc = $sth->execute($string);
>
>will probably eliminate both the prepare() error, and an
>UPDATE error later.
>
> -Clark
>
>
>  
>
Clark,

The arguments passed to sqlite as parameters should not be quoted. These
strings do not pass through the parser, they are used as literal values
when the SQL statement is executed. If you do quote this string, the
quotes will be included in the value of field a in your database.

You'll have to excuse my PERL (it's not a language I use)... but if
arguments to execute() are passed as parameters to sqlite then you
should do something like this.

$sql = "update t1 set a = ?";
$string = "some long string; has many 'single quotes'";
$sth = $dbh->prepare($sql);
$rc = $sth->execute($string);

HTH
Dennis Cote
Reply | Threaded
Open this post in threaded view
|

Re: quoting strings issue

Clark Christensen


--- Dennis Cote <[hidden email]> wrote:

> Clark Christensen wrote:
>
> >IOW, something like.
> >
> >$sql = "update t1 set a = ?";
> >$string = $dbh->quote( qq(some long string; has many
> >'single quotes') );
> >$sth = $dbh->prepare($sql);
> >$rc = $sth->execute($string);
> >
> >will probably eliminate both the prepare() error, and an
> >UPDATE error later.
> >
> > -Clark
> >
> >
> >  
> >
> Clark,
>
> The arguments passed to sqlite as parameters should not
> be quoted. These
> strings do not pass through the parser, they are used as
> literal values
> when the SQL statement is executed. If you do quote this
> string, the
> quotes will be included in the value of field a in your
> database.
>
> You'll have to excuse my PERL (it's not a language I
> use)... but if
> arguments to execute() are passed as parameters to sqlite
> then you
> should do something like this.
>
> $sql = "update t1 set a = ?";
> $string = "some long string; has many 'single quotes'";
> $sth = $dbh->prepare($sql);
> $rc = $sth->execute($string);
>
> HTH
> Dennis Cote
>

Even better.  Thanks Dennis!

 -Clark

Reply | Threaded
Open this post in threaded view
|

Re: quoting strings issue

Puneet Kishor
In reply to this post by Dennis Cote
Thanks to those who responded. However, this thread is going away from
what I really asked... not how to quote a string, but to confirm
whether or not SQLite had any idiosyncrasies related to string-quoting
other than the normal "escape single-quotes within the string."

Btw, I am using $dbh->quote.


On Jun 9, 2005, at 1:02 PM, Dennis Cote wrote:

> Clark Christensen wrote:
>
>> IOW, something like.
>>
>> $sql = "update t1 set a = ?";
>> $string = $dbh->quote( qq(some long string; has many
>> 'single quotes') );
>> $sth = $dbh->prepare($sql);
>> $rc = $sth->execute($string);
>>
>> will probably eliminate both the prepare() error, and an
>> UPDATE error later.
>>
>> -Clark
>>
>>
>>
> Clark,
>
> The arguments passed to sqlite as parameters should not be quoted.
> These strings do not pass through the parser, they are used as literal
> values when the SQL statement is executed. If you do quote this
> string, the quotes will be included in the value of field a in your
> database.
>
> You'll have to excuse my PERL (it's not a language I use)... but if
> arguments to execute() are passed as parameters to sqlite then you
> should do something like this.
>
> $sql = "update t1 set a = ?";
> $string = "some long string; has many 'single quotes'";
> $sth = $dbh->prepare($sql);
> $rc = $sth->execute($string);
>
> HTH
> Dennis Cote
>
--
Puneet Kishor

Reply | Threaded
Open this post in threaded view
|

Re: quoting strings issue

D. Richard Hipp
On Thu, 2005-06-09 at 14:29 -0500, Puneet Kishor wrote:
> Thanks to those who responded. However, this thread is going away from
> what I really asked... not how to quote a string, but to confirm
> whether or not SQLite had any idiosyncrasies related to string-quoting
> other than the normal "escape single-quotes within the string."
>

SQLite has no idiosyncrasies regarding string quoting.  However, I
cannot speak for the Perl DBI interface to SQLite - I am unfamiliar
with that code.
--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: quoting strings issue

Jay Sprenkle
In reply to this post by Puneet Kishor
> Thanks to those who responded. However, this thread is going away from
> what I really asked... not how to quote a string, but to confirm
> whether or not SQLite had any idiosyncrasies related to string-quoting
> other than the normal "escape single-quotes within the string."

You're doing it the hard way.

if you bind program variables to parameters in the sql instead of
building sql with literal data strings then you don't have to escape
quotes at all. The sql interpreter never looks at them and they don't
have to be quoted.
Reply | Threaded
Open this post in threaded view
|

Re: quoting strings issue

Puneet Kishor
In reply to this post by D. Richard Hipp

On Jun 9, 2005, at 2:30 PM, D. Richard Hipp wrote:

> On Thu, 2005-06-09 at 14:29 -0500, Puneet Kishor wrote:
>> Thanks to those who responded. However, this thread is going away from
>> what I really asked... not how to quote a string, but to confirm
>> whether or not SQLite had any idiosyncrasies related to string-quoting
>> other than the normal "escape single-quotes within the string."
>>
>
> SQLite has no idiosyncrasies regarding string quoting.  However, I
> cannot speak for the Perl DBI interface to SQLite - I am unfamiliar
> with that code.

Thanks for the confirmation. That is what I was seeking. Now I can
focus my debugging efforts elsewhere.

Now, if only I can also figure out the db locking issue, all will be
copacetic (fer now).

--
Puneet Kishor

Reply | Threaded
Open this post in threaded view
|

Re: solving the infamous, irritating "database locked" problem

Scott Leighton
In reply to this post by Puneet Kishor
On Thursday 09 June 2005 7:15 am, Puneet Kishor wrote:

> I've been a reading a lot on the "database locked" problem, but still
> need guidance trying to locate the source of my problem.
>
> environment: DBI/DBD::SQLite (latest versions) with SQLite3 (3.2.1) on
> Mac OS X (10.3.9).
>
> I am trying to update a table via the web. The UPDATE in question is
> the very first (and the only) DML statement. Yet, I get the "database
> locked" error.
>
> The sequence of commands is
>
> my $sql = "UPDATE statement";
> my $sth = $dbh->prepare(qq{$sql});
> $sth->execute;
> $dbh->commit;
>
> My question is: could I set some kind of "trace" that tells me what is
> going on with SQLite?
>

    Try this....

 my $sql = "UPDATE statement";
 my $sth = $dbh->prepare(qq{$sql}) || die $dbh->errstr;

 $dbh->begin_work;
 $sth->execute || die $dbh->errstr;
 $dbh->commit;


    Scott


--
POPFile, the OpenSource EMail Classifier
http://popfile.sourceforge.net/
Linux 2.6.11.4-21.7-default x86_64
Reply | Threaded
Open this post in threaded view
|

Re: solving the infamous, irritating "database locked" problem

Matt Sergeant-2
In reply to this post by Puneet Kishor
On 9 Jun 2005, at 10:15, Puneet Kishor wrote:

> My question is: could I set some kind of "trace" that tells me what is
> going on with SQLite?

$dbh->trace(LEVEL);

Try a level of 2 first and go up from there.


______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
______________________________________________________________________