Perl DBI considers setting SQLite DB cache_size as a write operation when subclassing DBI

Question

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

FATAL ERR: Failed to execute PRAGMA cache_size = 1000000: try to write a read-only database

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

Setting cache_size is not a write operation and does not interrupt if I access db directly through DBI, for example:

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

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

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

and now it dies with "DBD :: SQLite :: db do failed: try to write readonly database on / 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. However, I am surprised that he / did / worked last week on CentOS 6 and Perl 5.16. Over the weekend, IT professionals can upgrade to DBD :: SQLite or DBI.

Please do not change the title to "Suddenly getting an error in a program that worked for several months." This is a useless and non-specific headline.

Answer 1

TL DR - if transactions are enabled, then any command tries to write to the transaction log. Remove AutoCommit => 0 from the dbh connection flags if the database is read-only [You should not have any → begin_work () or INSERT / UPDATE calls but that never worked in db read-only mode :-)].

As it turned out, today I had the same problem after updating SQLite, DBI and DBD :: SQLite (so I don’t know exactly which one caused the problem), but in my case on select (which made it even more perplexing). It turned out that transactions were included in the original connection string:

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

and after tracking the code, I noticed that this is really a failure while 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 fixes my problem.