CODEWITHSUNDEEP
perlsqlite
Spiros
Spiros

Reputation: 2281

DBD::SQLite issue with I/O errors over mounted filesystem

Consider the following Perl code:

#!/usr/env/ perl
use strict;
use warnings;
use DBI;

my $filename = 'moo.sqlite';
my $dbh = DBI->connect("dbi:SQLite:dbname=$filename","","");
my $sql = 'INSERT into moo values (1)';
my $sth = $dbh->prepare( $sql );
my $rv = $sth->execute();
print $rv;

produces this error when run:

DBD::SQLite::db prepare failed: disk I/O error at test.pl line 16.
Can't call method "execute" on an undefined value at test.pl line 18.

The SQLite file is located on a SMBFS/CIFS network mounted share.

I have done some tracing and it appears that the failure occurs when a lock is attempted on the file:

open("moo.sqlite\0", 0x202, 0x1A4)       = 3 0
fcntl(0x3, 0x1, 0x0)         = 0 0
fcntl(0x3, 0x2, 0x1)         = 0 0

While I understand that network locking is problematic, wouldnt the same problem occur when interactively running the 'sqlite3' binary? Currently, I am able to work on the file with no problems when I do so.

Is this something that DBI is causing?

For your info:

Upvotes: 3

Views: 2026

Answers (1)

user1126070
user1126070

Reputation: 5069

SQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds. Other processes just wait on the writer to finish then continue about their business. Other embedded SQL database engines typically only allow a single process to connect to the database at once.

You could try this, it may help:

my $dbh = DBI->connect("dbi:SQLite:dbname=$filename","","", "", "", {
  sqlite_use_immediate_transaction => 1,
});

the problem is that CIFS driver could be configured to honour/allow locking. If that part is not configured correctly, the locking will not work.

You could try to change the locking settings in samba: http://oreilly.com/openbook/samba/book/ch05_05.html

The best candidate to try it out: "fake oplocks = yes"

If it is not working you could try to setup an ODBC datasource on the windows machine and access the database through that.

Upvotes: 2

Related Questions