Why does sqlite3 not work on Amazon Elastic File System?

Question

When I try to create a base with sqlite3 on a EFS directory, this results in an error:

$ sqlite3 foo.db SQLite version 3.7.17 2013-05-20 00:56:22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .log stderr sqlite> CREATE TABLE foo (int bar); Error: disk I/O error

The Sqlite3 database in question is supposed to story meta data only and will be updated infrequently. Concurrent access is not required. However, it is required that if the process creating the Database dies, the process can be restarted on a different host and carry on where the previous instance quit.

Amazon claims that EFS is a "file system that [is] accessible to Amazon EC2 instances via a file system interface (using standard operating system file I/O APIs) and that support full file system access semantics (such as strong consistency and file locking)." Thus, I'm assuming it is fit for the task at hand.

The mount options in /etc/fstab are:

eu-west-1a.fs-ID.efs.eu-west-1.amazonaws.com:/ /efs nfs4 nfsvers=4.1,rsize=1048576,wsize=1048576,hard,timeo=600,retrans=2 0 0

I understand that it is often generally discouraged to put databases on NFS. However, I believe, given the language used by both Amazon and SQLite, developers will keep trying.

Answer 1

Update (March 6, 2017):

EFS now supports NFS v4.1 lock upgrades and downgrades:

https://aws.amazon.com/about-aws/whats-new/2017/03/amazon-elastic-file-system-amazon-efs-now-supports-nfsv4-lock-upgrading-and-downgrading/

From the docs:

Lock upgrades and downgrades: Amazon EFS returns NFS4ERR_LOCK_NOTSUPP if the client attempts to upgrade or downgrade an existing lock.

Note

Because lock upgrades and downgrades are not supported, use cases that require this feature, such as those using SQLite or IPython, are also not supported in Amazon EFS.

See:

http://docs.aws.amazon.com/efs/latest/ug/efs-ug.pdf

and also:

http://docs.aws.amazon.com/efs/latest/ug/nfs4-unsupported-features.html

Answer 2

By default sqlite runs on "unix" VFS which uses unsupported by Amazon EFS lock upgrades. But you can use sqlite databases on Amazon EFS if you change VFS to "unix-excl", which uses exclusive file locks and doesn't require upgrades.

There are several ways to specify VFS. If you are using command line, just add "-vfs unix-excl" option:

ubuntu@ip-1-1-1-1 /efs> sqlcipher foo.db -vfs unix-excl
SQLCipher version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE foo (int bar);
sqlite> .exit

If using from API, then you need to register VFS before calling open function:

sqlite3_vfs_register(sqlite3_vfs_find("unix-excl"), 1);
sqlite3_open("foo.db", &db);

If using PHP PDO, then you're stuck, unless you are willing to recompile pdo_sqlite PHP extension.