Skip Menu |

This queue is for tickets about the DBD-SQLite CPAN distribution.

Report information
The Basics
Id: 30558
Status: resolved
Priority: 0/
Queue: DBD-SQLite

People
Owner: Nobody in particular
Requestors: claco [...] cpan.org
Cc:
AdminCc:

Bug Information
Severity: Critical
Broken in:
  • 1.13
  • 1.14
Fixed in: 1.12



Subject: INSERT After PK Failure Also Fails Using Prepared
Using prepared statements/bind params, an INSERT with a duplicate pk value in the database fails do to a PK violation with the following error: DBD::SQLite::st execute failed: column id is not unique(19) at dbdimp.c line 403 This is of course, expected. However, the very next INSERT statement using a unique pk value executed on the same statement handle also fails with the following, slightly different error: DBD::SQLite::st execute failed: column id is not unique(21) at dbdimp.c line 376 This does not happen if one uses regular sql statements using $dbh->do instead of the prepared statement. On win32, this only happens in 1.13/1.14 and worked fine in 1.12. One some other platforms, rather than an error, a core dump might happen instead. Attached is the test script containing a bare bones example of the issue.
Subject: test.pl
#!/usr/bin/perl -wT use strict; use warnings; use DBI; unlink 'test.db'; my $dbh = DBI->connect('dbi:SQLite:test.db', undef, undef, { AutoCommit => 1, PrintError => 0, PrintWarn => 0, RaiseError => 1 }); $dbh->do('CREATE TABLE test (id VARCHAR(10) NOT NULL, name VARCHAR(20) NOT NULL, PRIMARY KEY(id));'); $dbh->do('INSERT INTO test (id, name) VALUES (\'1\', \'a\');'); my $sth = $dbh->prepare('INSERT INTO test (id, name) VALUES (?, ?)'); eval { $sth->execute('1', 'a'); }; warn $@; ## the following line barfs on win32 1.13/1.14 with: ## DBD::SQLite::st execute failed: column id is not unique(21) at dbdimp.c line 376 at test.pl line 26 $sth->execute('2', 'b');
From: ntyni [...] iki.fi
On Wed Nov 07 14:49:59 2007, CLACO wrote: Show quoted text
> Using prepared statements/bind params, an INSERT with a duplicate pk > value in the database fails do to a PK violation with the following error: > > DBD::SQLite::st execute failed: column id is not unique(19) at dbdimp.c > line 403 > > This is of course, expected. However, the very next INSERT statement > using a unique pk value executed on the same statement handle also fails > with the following, slightly different error: > > DBD::SQLite::st execute failed: column id is not unique(21) at dbdimp.c > line 376
Hi, the same issue (with a crash) has been reported as Debian bug #450744: http://bugs.debian.org/450744 . The reporter, Kevin Ryde, writes: -- clip -- I suspect that when sqlite_st_execute gets an "insert" error like this it ends up freeing the underlying "sqlite3_stmt" object, so that a further execute of it bombs. I think the free is done by the following line in sqlite_st_execute (the first one, at "return -5"), /* There are bug reports that say this should be sqlite3_reset() */ sqlite3_finalize(imp_sth->stmt); I think the comment is right, under gdb you can see the stmt pointer value getting used again on the next execute of the same perl-level $sth. Actually I saw that stmt space getting overwritten with the error message string "column x is not unique ...", which made me wonder if was some sort of pointer juggling mixup, but I think it's just coincidence that it's related data ending up there after the space has been free()ed and then handed out by malloc() again. (Results vary if for instance you turn on $dbh->trace()s.) -- clip -- I can verify that the attached patch (against 1.14) fixes the crash. The test suite still passes with the change. Looking at the SQLite API docs, it looks pretty clear to me that sqlite3_reset() is the right thing to do. Cheers, -- Niko Tyni / Debian Perl Group ntyni@iki.fi
diff --git a/dbdimp.c b/dbdimp.c index 8dcab51..1ed8ebd 100644 --- a/dbdimp.c +++ b/dbdimp.c @@ -398,8 +398,7 @@ sqlite_st_execute (SV *sth, imp_sth_t *imp_sth) if (imp_sth->retval == SQLITE_ROW) { continue; } - /* There are bug reports that say this should be sqlite3_reset() */ - sqlite3_finalize(imp_sth->stmt); + sqlite3_reset(imp_sth->stmt); sqlite_error(sth, (imp_xxh_t*)imp_sth, imp_sth->retval, (char*)sqlite3_errmsg(imp_dbh->db)); return -5; }
Subject: Re: [rt.cpan.org #30558] INSERT After PK Failure Also Fails Using Prepared
Date: Mon, 12 Nov 2007 10:05:38 -0500
To: bug-DBD-SQLite [...] rt.cpan.org
From: "Christopher H. Laco" <claco [...] chrislaco.com>
I can confirm that the mentioned patch does fix things for me. I guess the funny part is the fact that the source already contains: /* There are bug reports that say this should be sqlite3_reset() */ In fact, the current patch only catches one instance of this issue. Line 422 has the same comment. -=Chris
Download signature.asc
application/pgp-signature 187b

Message body not shown because it is not plain text.

On Mon Nov 12 10:07:43 2007, claco@chrislaco.com wrote: Show quoted text
> I can confirm that the mentioned patch does fix things for me.
+1 for me on: a) being caught by this b) the patch fixing things for me c) having spent *way* too many hours hunting this problem down, through everyone reporting different scenarios and me not being able to find the right keywords with google. Matt, are you listening? Likely to be a new release anytime soon? If you need someone to help test and/or patch please let me know. You don't have a mailing list for this module but I hang out on dbi-users. There are quite few old bugs in RT that need attention (cleaning out) which I'd also be happy to work on. Cheers, Mark. -- Mark Lawrence
To the best of my knowledge, this now works in 1.19_07. Please investigate and confirm. If anyone wants to get more involved with maintaining DBD::SQLite, please let me know and I'll get you commit access.