SQLite Forum

[solved] disk I/O error on F2FS

[solved] disk I/O error on F2FS

(1.1) By Caleon on 2021-09-11 10:46:09 edited from 1.0 [link]

Hi, I'm looking for help with a problem (obviously)

sqlite Databases on my /home are basically unusable because they always fail with I/O error. Even a fresh database will only work for the first command, once it's been created on the disk it'll be unusable.

Example ```
$ sqlite3 new.db
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite> CREATE TABLE ex1 (a INTEGER);
sqlite> CREATE TABLE ex2 (a INTEGER);
Error: disk I/O error
sqlite> CREATE TABLE ex3 (a INTEGER);
Error: disk I/O error

The same commands work on other disks. I have some on ext4, tmpfs and some on f2fs.

The one that crashes has lots of options in fstab, I don't remember where I got these from:

`UUID=... /home f2fs rw,relatime,lazytime,background_gc=on,discard,no_heap,inline_xattr,inline_data,inline_dentry,flush_merge,extent_cache,mode=adaptive,active_logs=6,alloc_mode=default,fsync_mode=posix 0 0`

The other f2fs disk that does work has default options:

`UUID=... /other f2fs defaults,noatime 0 1`

So one of the mount options on /home breaks sqlite I think.

My OS is arch linux so I can't really track when I installed what version of sqlite but I did notice some weird behavior during the last weeks (firefox addons broken, discord autologin broken) that might be related. Thanks to Lutris for actually showing me an error message. That means I can't say what version introduced the problem but I think it's 3.36 because 3.35 is months older.

Additional info: Kernel 5.13.14-xanmod1 (right now)

Final question: is this a problem that can be fixed in sqlite or are my mount options just wrong?

(2) By Simon Slavin (slavin) on 2021-09-08 18:13:41 in reply to 1.0 [link]

Most likely that your hard disk is corrupt, and SQLite is running into it when trying to make temporary files.  Especially since you report problems with FireFox and Discord too.

In your situation I'd be looking for things like <code>fsck</code> and <code>ntfsfix</code>.  Not to mention backing up all my files on another drive.

(3) By anonymous on 2021-09-09 14:48:01 in reply to 1.0 [link]

> is this a problem that can be fixed in sqlite or are my mount options just wrong?

Things you could try to diagnose this problem further:

 - use `strace` to see _which_ system calls fail (and when), making SQLite return I/O Error
 - boot from a live CD or whatever and mount your `/home` with different options, see which combinations produce errors
 - actually, scratch that, you could always create a loop device, format it into f2fs and mount _that_ to avoid accidentally damaging your /home (there are many options to consider; write a script)
 - moreover, using a loop device will show whether the problem is with your partition (or the filesystem on it), or with f2fs options that you use for `/home`

(4.1) By Dan Kennedy (dan) on 2021-09-10 11:14:12 edited from 4.0 in reply to 1.0

Can't reproduce this. The only options you have that I'm not using are alloc_mode=default and fsync_mode=posix (kernel is too old here), but they're the default values anyway.

So, if you start with no database, create it, then query it, you get the error? i.e. the third command here reports an IO error, correct?

    rm a.db
    sqlite3 a.db "CREATE TABLE t1(x, y)"
    sqlite3 a.db "SELECT * FROM sqlite_schema"

If so, can you try running:

    rm a.db
    sqlite3 a.db "CREATE TABLE t1(x, y)"
    strace -o strace_error.log sqlite3 a.db "SELECT * FROM sqlite_schema"

and then post the contents of file "strace_error.log" for us?



(5) By Caleon on 2021-09-11 10:57:13 in reply to 4.1 [link]

It seems Simon was right. I fixed the fstab to include the file system check and tested a few times since then, no more problems.

Thanks for checking and sorry to have wasted your time.

(6) By Caleon on 2021-09-11 11:03:27 in reply to 2 [link]

Good guess, thanks for that. Fixed the problems I had noticed and maybe more.

Lesson learned: don't skip the file system check in fstab and/or understand what you actually set there...