Perl DBI treats setting SQLite DB cache_size as a

Question

I have a Perl program that we have run successfully every day for almost the past 2 years, but which crashes today with the error message:

FATAL ERR: Can't do PRAGMA cache_size = 1000000: attempt to write a readonly database

The SQLite database in question is readonly, and always has been, and the code has always used PRAGMA cache_size = 1000000 on it immediately after opening its readonly connection.

Setting cache_size is not a write operation, and does not fail if I access the db directly thru the DBI, like this:

$dbh->do("PRAGMA cache_size = 1000000")

However, the code makes SqliteH::db a subclass of DBI::db, then calls this function from the subclass:

$self->SUPER::do("PRAGMA cache_size = 1000000")

and it now dies with "DBD::SQLite::db do failed: attempt to write a readonly database at /local/ifs_projects/prok/function/src/lib/SqliteH.pm line 329."

The code worked with CentOS 5, Perl 5.10.1, DBD::SQLite 1.29, and DBI 1.611. It does not work CentOS 6, Perl 5.16, DBD::SQLite 1.39, and DBI 1.627. I am however mystified that it /did/ work last week on CentOS 6 and Perl 5.16. IT may have upgraded DBD::SQLite or DBI over the weekend.

Please do not change the title to "Suddenly getting error on program that has worked for months" again. That is an unhelpful and nonspecific title.

Answer 1

TL;DR - if transactions are on, then any command attempts to write to the transaction log. Remove AutoCommit=>0 from the dbh connection flags if the database is read-only [You shouldn't have any ->begin_work() or INSERT/UPDATE calls either, but that never worked on a read-only db :-) ].

As it turns out, I had exactly the same problem today after updating SQLite, DBI and DBD::SQLite (so I don't know exactly which of them caused the problem), but in my case, on a select (which made it even more baffling). It turned out that transactions were turned on in the original connect string:

my $dbh=DBI->connect('dbi:SQLite:file.db','','',, {PrintError=>1,RaiseError=>1,AutoCommit=>0});

and, after tracing the code, I noticed that it was actually crashing trying to start a transaction.

  DB<4> $dbh->trace(15)
    DBI::db=HASH(0x18b9c38) trace level set to 0x0/15 (DBI @ 0x0/0) in DBI 1.627-ithread (pid 15740)
  DB<5> $sth= $dbh->prepare("SELECT key,value FROM annotation where accession=?")
    ...
  DB<6> $sth->execute('D3FET3')
    -> execute for DBD::SQLite::st (DBI::st=HASH(0x18ba340)~0x18ba178 'D3FET3') thr#10cd010
    sqlite trace: bind into 0x18ba268: 1 => D3FET3 (0) pos 0 at dbdimp.c line 1232
    sqlite trace: executing SELECT key,value FROM annotation where accession=? at dbdimp.c line 660
    sqlite trace: bind 0 type 3 as D3FET3 at dbdimp.c line 677
    sqlite trace: BEGIN TRAN at dbdimp.c line 774
    sqlite error 8 recorded: attempt to write a readonly database at dbdimp.c line 79
      !! ERROR: '8' 'attempt to write a readonly database' (err#1)
      <- execute= ( undef ) [1 items] at (eval 15)[/usr/local/packages/perl-5.16.1/lib/5.16.1/perl5db.pl:646] line 2 via  at -e line 1
    DBD::SQLite::st execute failed: attempt to write a readonly database at (eval 15)[/usr/local/packages/perl-5.16.1/lib/5.16.1/perl5db.pl:646] line 2.
    ...

Removing the AutoCommit=>0 flag in the connect() call fixed my problem.