4

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');
Sinan Ünür
  • 116,958
  • 15
  • 196
  • 339
Galaxy
  • 1,862
  • 1
  • 17
  • 25
  • 1
    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');
    – Galaxy Aug 20 '09 at 01:48

2 Answers2

11

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);
Sinan Ünür
  • 116,958
  • 15
  • 196
  • 339
1

The DBI has autocommit turned on by default. Turn it off during the connect:

my $dbh = DBI->connect($dsn, $user, $pass, { AutoCommit => 0 });
Chas. Owens
  • 64,182
  • 22
  • 135
  • 226
  • 1
    Chas. I think you have it reversed. `AutoCommit` needs to be on for `$dbh->do('VACCUM')` to occur outside of a transaction. – Sinan Ünür Aug 20 '09 at 01:52