From the FAQ entry you linked to:

"Because a prepared statement can be invalidated by another process changing the database schema, all code that uses the sqlite3_prepare()/sqlite3_step()/sqlite3_finalize() API should be prepared to handle SQLITE_SCHEMA errors. An example of one approach to this follows: (snip code example of how to handle and recover this problem in C)"

If the code in the DBD::SQLite perl module is using these API calls to provide prepared statement handles, it needs to use the above approach.

So I'd say, file a bug on that module, giving pointers to your script above, the relevant FAQ entry and - if you're feeling generous - a patch.

Edit: OK, I looked into it a little more and it's not quite that simple :-(

The suggested code in the FAQ simply re-runs the query. Since the DBI interface exposes prepared statements to the perl level, perhaps the error (and handling) needs to be propogated to the perl level too. Doesn't feel like there's a simple solution to me, since you may have already processed some rows (e.g. printed them out). It might be possible to keep track of how many rows you've returned and skip them, but I'm not sure if that would be worse (since I guess with a changing DB you could end up missing some rows or duplicating them).

Maybe the best thing to do is to schedule some downtime for when ANALYZE is going to be run.

In reply to Re: Upgraded SQLite: "database changed" error by jbert
in thread Upgraded SQLite: "database changed" error by bsb

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


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.