Why does $dbh->do('VACUUM') fail with Perl's DBD::SQLite?

Question

I want to do VACUUM at a certain time on a SQLite database under Perl, but it always says

DBD::SQLite::db do failed: cannot VACUUM from within a transaction

So how do I do this?

my %attr = ( RaiseError => 0, PrintError => 1, AutoCommit => 0 );
my $dbh = DBI->connect('dbi:SQLite:dbname='.$file'','',\%attr) 
    or die $DBI::errstr;

I am using AutoCommit => 0. And the error happens while:

$dbh->do('DELETE FROM soap');
$dbh->do('DELETE FROM result');
$dbh->commit; 
$dbh->do('VACUUM');

Answer 1

I am assuming you have AutoCommit => 0 in the connect call because the following works:

#!/usr/bin/perl

use strict;
use warnings;

use DBI;

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

$dbh->do('VACUUM');

$dbh->disconnect;

You don't have to give up on transactions to be able to VACUUM: You can use the following so that AutoCommit is turned on for VACUUM and after the VACUUM the AutoCommit state is reverted back to whatever it was. Add error checking to taste if you do not set RaiseError.

sub do_vacuum {
    my ($dbh) = @_;
    local $dbh->{AutoCommit} = 1;
    $dbh->do('VACUUM');
    return;
}

Call it:

do_vacuum($dbh);