how to debug random "database disk image is malformed" error? about rqlite

[otoolep]

There was a previous issue like this in the past, it turned out to be a bug in SQLite. You can read about it: https://www.philipotoole.com/how-i-found-a-bug-in-sqlite/

Can you read that blog post, and see how it compares to what you're seeing? Is the issue transient?

[otoolep]

It's only at query time? Does it still happen if there is zero write traffic on your cluster?

[jasonbigl]

Thanks, I read that post and it's not relavant I think, I'm using disk files and the disk io is very low.

Yes the error only happens at query time, I'm not sure if it happens when there is no insert, actually I don't know how to reproduce it exactly, it just happened randomly and now it's gone...

I'm still digging this and want to ask if there's any possible reason, but since I'm using a pretty new version(v8.22.1), I think the sqlite bug should be addressed in this release, right?

Let me do some more tests to see if I can reproduce it in new cluster.

[otoolep]

I didn't think you were hitting the same bug, but I wanted to show you how we debugged the previous "random database malformed issue".

If your issue only happens at query time, and comes and goes, that is good news in the sense that it indicates the database isn't actually corrupted. It's just a bug reading the data. Yes, the previous bug was with in-memory databases, but ultimately it was also a query time bug.

To help you debug this, I would need a lot more information, starting with the requests you are sending to the cluster (the SQL), the rate you are reading and writing, and SQL for your reads. A copy of the SQLite database under the node that you're seeing this on, along with a query that brings it out would help.

[otoolep]

Check if you hit this issue by reading the SQLite file directly (it will be in the data directory of the node). Execute the queries in a tight loop using, perhaps, the SQLite shell. See if you can reproduce that way.

[jasonbigl]

Yes, I was reading the sqlite file directly, the sqlite file is stored on a memory-backed filesystem, like this doc:
https://rqlite.io/docs/guides/performance/#use-a-memory-backed-filesystem

I switched to the rqlite http client, the errors are still there but are way less(like only 5~10 times per hour, but when I reading the sqlite file directly, the errors are like ~300 per hour)

then I use the server disk instead of memory-backed filesystem, after watching the monitor for over 2 days, I can confirm the error disappear....I have tried to reproduce this issue on another cluster but no luck, it just happen randomly...

I'll keep digging this until I can find a way to reproduce it, as for now we can close this since it maybe the sqlite or memory issue, not rqlite.

[otoolep]

Well, AFAIK, putting SQLite on a memory-backed file system like that doc described doesn't change how SQLite works. That might indicate it's something at the file system level, or in the OS. Hard to say.

Then again, reading from a memory-backed filesystem may be much faster, so it could be bringing out a race condition. I would like to get to the root cause of this issue, but unless you supply me information such as the queries you are running, it's going to be difficult. If you can reproduce this with the SQLite file directly (you say you can) you should open start a discussion on the SQLite forum. But you'll need to supply a lot more information about the SQLite database and your schema to help them debug.

If you don't want to do that, we should close the issue , as I won't be able to debug any further.

[jasonbigl]

thank you for the help! sure I can provide the query I'm runing, it's a very simple query:
select * from offer where id='xxx' where xxx is a UUID-V4 value.

the reading rate is about 50/s, but there are also some node query 2~5/s, and they also have this error. all reading is directly query sqlite files on the non-voters.
the write rate is pretty low, I believe it's lower than 1/s

the sqlite files I'm quering contains some sensitive information, I'm droping those data and testing if I can see the error again, once I see the error, I will provide you the sqlite file.

May I ask do you need anything else except the sqlite file?

[otoolep]

Try running https://www.sqlite.org/pragma.html#pragma_integrity_check on your SQLite file, see if anything pops up.

If you can get this to happen when reading the SQLite file directly, I would start a thread on https://sqlite.org/forum/forum. I'm not really going to be much help if it's SQLite-specific.

You say you cannot get this to happen unless you're using SQLite on a memory-backed file system. That implies that it's something to do with file system perhaps, and not rqlite.

[jasonbigl]

ok, thanks, pragma integrity_check return ok, I'll keep testing this, now we can close this