1

I'm working on an applciation that uses the DBI(v1.643) and DBD::SQLite(v1.66) Perl modules to interact with a database file and fetch some information. It does this by running SELECT queries using the prepare() and execute() methods. However, I get the following error when the database file isn't writable:

***DBD::SQLite::st execute failed: attempt to write a readonly database***

The error goes away when I make the file writable, but why is this restriction placed? Until now, the application was using DBD::SQlite version 1.13 and didn't need the file to be writable. I agree that for operations like INSERT etc. which would modify the database, it makes sense to have the file to be writable. However, fetching information using SELECT queries shouldn't ideally require the file to be writable.

I ran some tests and found that up to version 1.37 (DBD::SQLite), the SELECT queries work even when the file isn't writable.

Below is how I connect to the database file(which is read-only):

my $dbh = DBI->connect( 'dbi:SQLite:dbname=<fileName>', undef, undef,
                                {
                                   RaiseError => 0,
                                   AutoCommit => 0,
                                   PrintError => 1
                                }
);

I would be grateful if someone helps me understand how to get around this :)

rick7morty
  • 31
  • 3
  • I don't see anything in the [changelog for DBD::SQLite 1.38_01](https://metacpan.org/dist/DBD-SQLite/changes#L468) that spells that out, but it _does_ say "MAY POSSIBLY BREAK YOUR OLD APPLICATIONS". Maybe some change in [SQLite 3.7.14](https://www.sqlite.org/changes.html)? – Jim Davis Jul 16 '21 at 16:09
  • I'm not seeing this behavior with those versions. Is it a complex query? Do you have the same behavior with a very simple query? – Jim Davis Jul 16 '21 at 16:16
  • @JimDavis I wasn't able to find anything related to file writability in the Changelog either. I ran a very simple query "SELECT * FROM ". I wonder how you aren't getting the same behavior... – rick7morty Jul 16 '21 at 16:45
  • @JimDavis I also wanted to mention that the application uses Perl 5.22.0. Not sure if that really matters. – rick7morty Jul 16 '21 at 17:48
  • I've tried this with 5.22.0 and still can't get it to fail. Can you boil it down to a minimal, reproducible example? Could something else in the program being updating anything? When you make the file writable, does the timestamp change / file get modified? – Jim Davis Jul 16 '21 at 19:13
  • 1
    Did you tell it the database should be opened in read-only mode? https://metacpan.org/pod/DBD::SQLite#Read-Only-Database If not do that and see if it helps. – Shawn Jul 16 '21 at 19:14
  • 1
    @JimDavis I have edited the question to add information on how the DBI connection is made. It seems like the AutoCommit attribute cannot be set to 0 for read-only database files as mentioned at [link](https://stackoverflow.com/questions/17793672/perl-dbi-treats-setting-sqlite-db-cache-size-as-a-write-operation-when-subclassi) . Both the methods i.e. setting `AutoCommit` to 1 and setting `sqlite_use_immediate_transaction` to false get rid of the error, although I neither understand why a SELECT operation would depend on commit nor how the second method works. – rick7morty Jul 18 '21 at 16:20
  • @Shawn I tried adding `ReadOnly=>1` but it didn't help :/ – rick7morty Jul 18 '21 at 16:22
  • @rick7morty - Nice catch! If I understand correctly, with `AutoCommit` off, it starts a transaction -- creating a transaction log in the database -- and has no way of knowing that all you're actually going to _do_ in that transaction is a `SELECT`. I don't grok the `sqlite_use_immediate_transaction` setting, though. – Jim Davis Jul 19 '21 at 03:51
  • @JimDavis Thanks for letting me know about the transaction log. Makes sense now. As for the `sqlite_use_immeditate_transaction`, looks like it was introduced to solve a locking problem and was set to false as default starting from version 1.38 onwards (which explains why I didn't get the error until 1.37). I don't understand it completely either since in my case, there's only one process trying to read the database file. If you're curious, you can track the issue [here](https://github.com/DBD-SQLite/DBD-SQLite/issues/84?_pjax=%23repo-content-pjax-container) which I hope will throw some light – rick7morty Jul 19 '21 at 08:22

0 Answers0