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

Question

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

DBD :: SQLite :: db do failed: cannot VACUUM from 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 occurs when:

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

Answer 1

I assume that 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 do not need to abandon transactions in order to be able to VACUUM : you can use the following to enable AutoCommit for VACUUM , and after AutoCommit state of AutoCommit to what it was. Add error checking to your taste if you do not install RaiseError .

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

Name it:

 do_vacuum($dbh); 

Answer 2

DBI has auto-update enabled by default. Turn it off during connection:

 my $dbh = DBI->connect($dsn, $user, $pass, { AutoCommit => 0 });