execute() failing with: Not an error!

After hours of googling and testing I'm down to you guys as my last
resort. I have an issue that results in execute() failing with the
error:

not an error(21) at dbdimp.c line 376

The test script below brings out the problem, everywhere except my
development environment. I get the same problem if I
use prepare() instead of prepare_cached().

Would really appreciate it if someone can duplicate this, especially
against a target other than DBD::SQLite (that's all I'm able to try at
the moment). Any ideas before I start submitting bugs to RT?

Cheers,
Mark.


1. Debian GNU/Linux (i686), DBI v1.53, Perl v5.8.8, DBD::SQLite v1.14
============================================================ =========

1..3
ok 1 - insert id 1
DBD::SQLite::st execute failed: PRIMARY KEY must be unique(19) at dbdimp.c line 403 at x.pl line 32.
ok 2 - insert duplicate id 1
ok 3 - insert id 2
closing dbh with active statement handles at x.pl line 46.


2. Windows XP, DBI v1.601, Perl v5.10.0 (Strawberry), DBD::SQLite v1.14
============================================================ ===========
1..3
ok 1 - insert id 1
DBD::SQLite::st execute failed: PRIMARY KEY must be unique(19) at dbdimp.c line 403 at x.pl line 32.
ok 2 - insert duplicate id 1
DBD::SQLite::st execute failed: not an error(21) at dbdimp.c line 376 at x.pl line 40.
not ok 3 - insert id 2
# Failed test 'insert id 2'
# at x.pl line 41.
# Looks like you failed 1 test of 3.


3. The Test Case
================

#!/usr/bin/perl
BEGIN {$ENV{DBI_TRACE} = 0;}
use strict;
use warnings;
use Test::More tests => 3;
use DBI;

my $dbh;
my $sth;
my $res;

unlink('test.db');
$dbh = DBI->connect('dbi:SQLite:test.db');
$dbh->{RaiseError} = 0;

$dbh->do('
CREATE TABLE artists (
id INTEGER,
name VARCHAR(32),
PRIMARY KEY(id)
)
');


$sth = $dbh->prepare_cached('INSERT INTO artists(id,name) VALUES(?,?)');
$res = $sth->execute(1,'one');
ok($res, 'insert id 1');
$sth->finish;

$dbh->begin_work;
$sth = $dbh->prepare_cached('INSERT INTO artists(id,name) VALUES(?,?)');
$res = $sth->execute(1,'one');
ok(!$res, 'insert duplicate id 1');
$dbh->commit if($res);
$dbh->rollback if(!$res);
$sth->finish;

$dbh->begin_work;
$sth = $dbh->prepare_cached('INSERT INTO artists(id,name) VALUES(?,?)');
$res = $sth->execute(2, 'two'); # <===== This fails: "not an error"
ok($res, 'insert id 2');
$dbh->commit if($res);
$dbh->rollback if(!$res);
$sth->finish;

$dbh->disconnect;
exit;


--
Mark Lawrence

Re: execute() failing with: Not an error!

On Wed Apr 02, 2008 at 11:55:53AM +0200, Mark Lawrence wrote:

> $dbh->begin_work;
> $sth = $dbh->prepare_cached('INSERT INTO artists(id,name) VALUES(?,?)');
> $res = $sth->execute(1,'one');
> ok(!$res, 'insert duplicate id 1');
> $dbh->commit if($res);
> $dbh->rollback if(!$res);
> $sth->finish;

Yes, I also know that the transaction stuff is not necessary here, but I
was using transactions in my code and just duplicated here. The failure
also occurs with autocommit...

Mark.
--
Mark Lawrence

Re: execute() failing with: Not an error!

On Wed, Apr 02, 2008 at 11:55:53AM +0200, Mark Lawrence wrote:
> After hours of googling and testing I'm down to you guys as my last
> resort. I have an issue that results in execute() failing with the
> error:
>
> not an error(21) at dbdimp.c line 376

> The test script below brings out the problem, everywhere except my
> development environment. I get the same problem if I
> use prepare() instead of prepare_cached().
>
> Would really appreciate it if someone can duplicate this, especially
> against a target other than DBD::SQLite (that's all I'm able to try at
> the moment). Any ideas before I start submitting bugs to RT?

I believe it's an error from DBD::SQLite

Google http://www.google.com/search?q=sqlite+error+%2B21 suggests
http://www.sqlite.org/cvstrac/wiki?p=LibraryRoutineCalledOut OfSequence

Tim.

> Cheers,
> Mark.
>
>
> 1. Debian GNU/Linux (i686), DBI v1.53, Perl v5.8.8, DBD::SQLite v1.14
> ============================================================ =========
>
> 1..3
> ok 1 - insert id 1
> DBD::SQLite::st execute failed: PRIMARY KEY must be unique(19) at dbdimp.c line 403 at x.pl line 32.
> ok 2 - insert duplicate id 1
> ok 3 - insert id 2
> closing dbh with active statement handles at x.pl line 46.
>
>
> 2. Windows XP, DBI v1.601, Perl v5.10.0 (Strawberry), DBD::SQLite v1.14
> ============================================================ ===========
> 1..3
> ok 1 - insert id 1
> DBD::SQLite::st execute failed: PRIMARY KEY must be unique(19) at dbdimp.c line 403 at x.pl line 32.
> ok 2 - insert duplicate id 1
> DBD::SQLite::st execute failed: not an error(21) at dbdimp.c line 376 at x.pl line 40.
> not ok 3 - insert id 2
> # Failed test 'insert id 2'
> # at x.pl line 41.
> # Looks like you failed 1 test of 3.
>
>
> 3. The Test Case
> ================
>
> #!/usr/bin/perl
> BEGIN {$ENV{DBI_TRACE} = 0;}
> use strict;
> use warnings;
> use Test::More tests => 3;
> use DBI;
>
> my $dbh;
> my $sth;
> my $res;
>
> unlink('test.db');
> $dbh = DBI->connect('dbi:SQLite:test.db');
> $dbh->{RaiseError} = 0;
>
> $dbh->do('
> CREATE TABLE artists (
> id INTEGER,
> name VARCHAR(32),
> PRIMARY KEY(id)
> )
> ');
>
>
> $sth = $dbh->prepare_cached('INSERT INTO artists(id,name) VALUES(?,?)');
> $res = $sth->execute(1,'one');
> ok($res, 'insert id 1');
> $sth->finish;
>
> $dbh->begin_work;
> $sth = $dbh->prepare_cached('INSERT INTO artists(id,name) VALUES(?,?)');
> $res = $sth->execute(1,'one');
> ok(!$res, 'insert duplicate id 1');
> $dbh->commit if($res);
> $dbh->rollback if(!$res);
> $sth->finish;
>
> $dbh->begin_work;
> $sth = $dbh->prepare_cached('INSERT INTO artists(id,name) VALUES(?,?)');
> $res = $sth->execute(2, 'two'); # <===== This fails: "not an error"
> ok($res, 'insert id 2');
> $dbh->commit if($res);
> $dbh->rollback if(!$res);
> $sth->finish;
>
> $dbh->disconnect;
> exit;
>
>
> --
> Mark Lawrence

Re: execute() failing with: Not an error!

On Wed Apr 02, 2008 at 04:53:36PM +0100, Tim Bunce wrote:
> On Wed, Apr 02, 2008 at 11:55:53AM +0200, Mark Lawrence wrote:
> > After hours of googling and testing I'm down to you guys as my last
> > resort. I have an issue that results in execute() failing with the
> > error:
> >
> > not an error(21) at dbdimp.c line 376
>
> > The test script below brings out the problem, everywhere except my
> > development environment. I get the same problem if I
> > use prepare() instead of prepare_cached().
> >
> > Would really appreciate it if someone can duplicate this, especially
> > against a target other than DBD::SQLite (that's all I'm able to try at
> > the moment). Any ideas before I start submitting bugs to RT?
>
> I believe it's an error from DBD::SQLite
>
> Google http://www.google.com/search?q=sqlite+error+%2B21 suggests
> http://www.sqlite.org/cvstrac/wiki?p=LibraryRoutineCalledOut OfSequence

And/or maybe http://rt.cpan.org/Ticket/Display.html?id=9792 and
http://rt.cpan.org/Ticket/Display.html?id=30558.

With the one-line patch to DBD::SQLite that's in ticket 30558 things
work again for me. I guess Debian had patched this long ago so I didn't
see the failure until the CPAN testers started sending in reports. What
an extremely useful service to module authors...

Although until Matt releases a new version I'm kinda stuck. Can't go
round patching everybodys machines.

Mark.
--
Mark Lawrence