5

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.

Improve this question
6
  • Use .log stderr to get more error information. Commented Feb 6, 2017 at 15:02
  • @CL. .log stderr produces no addtional output Commented Feb 6, 2017 at 15:13
  • Before you try to write anything. Commented Feb 6, 2017 at 15:22
  • @CL. I understand, see my edit above. This is what I get. Commented Feb 6, 2017 at 15:46
  • Update your sqlite3 tool. Commented Feb 6, 2017 at 16:11

2 Answers 2

Reset to default
6

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

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

EFS now supports Lock upgrades and downgrades. See aws.amazon.com/about-aws/whats-new/2017/03/…
Thanks @user1071840, I am glad to hear this for my use case as well. I will update my answer as not to lead people astray that find it.
I'm still encountering this issue on my AWS setup, anything special you defined in you EFS/Grafana setup to make it work?
Sounds like a question for AWS support.
5

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.

Improve this answer

2 Comments

Good to know, it is possible after all to run sqlite on EFS.
Possible, but still a bad idea generally: sqlite.org/howtocorrupt.html

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.