Promote SQLite implementation to stable

[Mazino-Urek]

Topic:
From version 4.4, SQlite has been added as an experimental to resume data capabilities. I can confirm multiple things:

1. It is very fast. Load clients considerably faster than previous implementation,
2. It hasn't caused any data corruption or loss,
3. It provides a better solution for data backup(for Linux systems at least). All the data is contained in a single file.

Suggestions:
Remove the experimental status so that more people are encouraged to use it. By doing this, it can be gradually promoted to the default status.

Thanks a lot, @glassez. This is a brilliant feature.

[thalieht]

I have been using it since day one without a single problem but i wouldn't consider myself a "heavy" user. 👍

[Mazino-Urek]

Right now, I have to a 100 of torrents and more than 1 terabytes of data seeding, spanning both SSD and HDD. This is not that much, but more than normal users. The experience has been seamless.

[glassez]

To be honest, I wasn't aware that anyone was using it in a productive environment. Well, if that's the case and you have positive feedback, then we can move on with it. We can start by removing the "experimental" label.
As for making it the default option, it's still worth a wait. There are some worries about its user friendliness, for example, it is "very difficult" to solve some problems for which previously it was possible to recommend user simply deleting problematic .fastresume files, etc., and in the case of SQLite, the user will have to deal with its tables (damn, now this can really become a problem for many users!).

[Mazino-Urek]

Ok, I will test the transition and report.

[xavier2k6]

I moved over to SQLite just over a month ago in my main system & I too haven't experienced any issues (I haven't went looking for them either just to be clear)

I suppose the handy thing about SQLite as well is that it gets updated with Qt even on the point releases in most cases. (which brings bug fixes.)

[Mazino-Urek]

And if we are going to bring it to the general public, it would be nice to test it artificially. We need to check the correctness of switching from .fastresume to SQLite and vice versa.

I have successfully switched from SQLite > Fast Resume > SQLite.
No data loss. No error.

[Forza-tng]

I like the sqlite idea. One comment for improvement is to use PRAGMA journal_mode=wal; to enable Write Ahead Logging as this is much friendlier for COW filesystems such as Btrfs and ZFS. It generally is a little faster too, though this is perhaps not the most important factor at the moment.

Steps to manually enable WAL:

• stop qBittorrent
• run sqlite3 torrents.db
• type PRAGMA journal_mode=wal;
• type VACCUUM;
• type .quit or use ctrl-d to close the database.
• start qBittorrent

Now you should see three files: torrents.db, torrents.db-shm and torrents.db-wal

[glassez]

#18048

[glassez]

FYI, I have resumed my work on improving the "session restore" stage, namely, I want to implement asynchronous handling that does not prevent the use of the application UI during this time (with some restrictions, of course). But at the same time, SQLite based way will become even more optimal, since I will replace the current method of reading resume data, which requires lookup for each row by id, with a more optimal method of sequential traverse all the rows.

[glassez]

Unfortunately there is a bug in SQLite based resume data storage. Ref.: #16810.

[andry81]

Can the sqlite db file be backed up in the middle of qbittorrent running? I know that the fast resume file you can just copy. Does that would work for the database?

[andry81]

It depends how you want to use time like in relative or absolute form, but both can be written as unix timestamp: 1h = 1:00:00 - can be relative like 1 hour after the last backup or can be in form of 1 hour after morning.

[glassez]

It depends how you want to use time like in relative or absolute form, but both can be written as unix timestamp

I just want to add some combobox with several predefined values of time intervals. I don't intend to do anything more advanced. So the only question is what could be the initial set of these predefined values to satisfy the majority of non-picky users.

[andry81]

If so, then just edit box with number in hours would be enough. It is simpler than combobox.

[Orhideous]

Can the sqlite db file be backed up in the middle of qbittorrent running?

@andry81 Yes, why not. It's SQLite, come on. With enabled WAL (IIRC, enabled by default) database is pretty consistent. Just rsync'n'forget. Or. if you prefer, attach another database (for backups), and then INSERT INTO… SELECT FROM.

[glassez]

With enabled WAL (IIRC, enabled by default) database is pretty consistent.

It's disabled by default, IIRC. But you can enable it even from outside of qBittorrent (while qBittorrent isn't running).
But the problem is still that the database file is read/written by the qBittorrent at arbitrary times, so you can either get corrupted data or interfere with the correct operation of qBittorrent.

[Orhideous]

@glassez It would be nice to be able to switch storage type in WebUI.
I'm already testing this feature in production on 25k+ torrents.
Manually added this to [Session] section:

Session\ResumeDataStorageType=SQLite

It takes ≈5 min to convert all data from BT_backup folder.

kah@qbittorrent-7f5566f786-pkl5r:~/qBittorrent$ du -hs torrents.db
400M    torrents.db
kah@qbittorrent-7f5566f786-pkl5r:~/qBittorrent$ du -hs BT_backup/
560M    BT_backup/

So far so good.

---

P.S. Found #16822, fixed DB manually

sqlite> .schema torrents
CREATE TABLE `torrents` (`id` INTEGER PRIMARY KEY,`torrent_id` BLOB NOT NULL UNIQUE,`queue_position` INTEGER NOT NULL DEFAULT -1,`name` TEXT,`category` TEXT,`tags` TEXT,`target_save_path` TEXT,`content_layout` TEXT NOT NULL,`ratio_limit` INTEGER NOT NULL,`seeding_time_limit` INTEGER NOT NULL,`has_outer_pieces_priority` INTEGER NOT NULL,`has_seed_status` INTEGER NOT NULL,`operating_mode` TEXT NOT NULL,`stopped` INTEGER NOT NULL,`libtorrent_resume_data` BLOB NOT NULL,`metadata` BLOB);
sqlite> ALTER TABLE torrents ADD download_path TEXT
sqlite> .schema torrents
CREATE TABLE `torrents` (`id` INTEGER PRIMARY KEY,`torrent_id` BLOB NOT NULL UNIQUE,`queue_position` INTEGER NOT NULL DEFAULT -1,`name` TEXT,`category` TEXT,`tags` TEXT,`target_save_path` TEXT,`content_layout` TEXT NOT NULL,`ratio_limit` INTEGER NOT NULL,`seeding_time_limit` INTEGER NOT NULL,`has_outer_pieces_priority` INTEGER NOT NULL,`has_seed_status` INTEGER NOT NULL,`operating_mode` TEXT NOT NULL,`stopped` INTEGER NOT NULL,`libtorrent_resume_data` BLOB NOT NULL,`metadata` BLOB, download_path TEXT);

[victorhooi]

I just did an upgrade from qBittorrent 4.3.0 to 4.4.3.1.

I edited my qBittorrent.conf file under the [BitTorrent] section to add the line:

Session\ResumeDataStorageType=SQLite

When I started up qBittorrent again, the web UI wouldn't load.

From the logfiles - it seemed to be in some kind of boot-loop, where qBitorrent would keep restarting every few seconds.

I didn't see any torrents.db file get created.

Has anybody seen this? Any suggestions to fix?

[glassez]

I just did an upgrade from qBittorrent 4.3.0 to 4.4.3.1.

So you didn't run qBittorrent after upgrade and before you changed the storage type? Doesn't seem like a good idea. There is quite a big gap between these versions. This "double upgrade" could make it crazy.

Try to make backup of your data, then disable SQLite and run qBittorrent to see what's happening with default storage type.

[andry81]

Another issue on the way.

There a need to convert saved paths while migrating to new directory/storage/OS. There is already pretty many variants (half is ugly and does not work but anyway): #9912

With a binary database file a simple conversion of value in the database without a tool would be a problem. The bencodes is simple enough to edit it directly.

[andry81]

I don't see why would anyone need to work with a switched db for a while to change states etc.

You don't have to. But eventually it happens and the application data becomes altered. Why? Because application can run after conversion, because fastresume can has versioning differences to sql-db, because not all data does conversion and so on. So, you can not guarantee that all completely and correctly would be converted. So either you use one or use another.

[ghost]

If someone is so eager to change the save path they may do it as following:

1. Pause all torrents.
2. Change to fastresume style resume data and restart qBt to convert sql to fastresume.
3. Quit qBt and make required changes to fastresume file like they used to do until now.
4. Start qbt again, make sure everything is working as expected, change to sql db.
5. Restart qBt and see fastresume get converted to sql-db.

or they can implement a tool for migration from linux to windows and etc but that should not be considered a blocker for this option to be set as default.

[andry81]

If someone is so eager to change the save path they may do it as following:

That scenario can be broken in the future. Even more, I think the fastresume would be dropped from some time point because of increasing effort to support both.

[ghost]

Even if it gets dropped (and it should be), people should not be mingling with a softwares internal use data and the software developers should not be changing how their software works to entertain this. At best it may provide a way(in the unforeseen future) to do such a change easily using the application interface or api.

[andry81]

Even if it gets dropped (and it should be), people should not be mingling with a softwares internal use data and the software developers should not be changing how their software works to entertain this.

But they are and the link above is proved it: #9912

At best it may provide a way(in the unforeseen future) to do such a change easily using the application interface or api.

But there is no such an API which could grant access as easily as just patch the fastresume files. And sql-db would definitely does complicate the API and people has to dive into internal details more hardly.

[ireun]

Could https://github.com/qbittorrent/qBittorrent/wiki/Explanation-of-Options-in-qBittorrent#Advanced explain the differences between fastresume and SQL? There is no explanation whatsoever.

[mzso]

Topic: From version 4.4, SQlite has been added as an experimental to resume data capabilities. I can confirm multiple things:

1. It is very fast. Load clients considerably faster than previous implementation,

2. It hasn't caused any data corruption or loss,

3. It provides a better solution for data backup(for Linux systems at least). All the data is contained in a single file.

Suggestions: Remove the experimental status so that more people are encouraged to use it. By doing this, it can be gradually promoted to the default status.

Thanks a lot, @glassez. This is a brilliant feature.

Does it only store the data from the .resume.dat files, or also the .torrent file data?

[glassez]

resume.dat

You most likely refer to .fastresume files, right?
All the data that is stored in legacy BT_backup directory are stored in database when SQLite storage is selected.

[mzso]

Yes. Looks neat. Though as you said troubleshooting might be rather difficult. (without some sort of easy to use database editor)

[gennaios]

I've been using it for a little while w/o issues. I'm looking a bit at the code but haven't gone thru it much yet. Do you think there should be any column indices? In the sqlite3 shell, one can enter at the prompt .expert and then a query and it'll suggest them.

[glassez]

A suggestion: looking at the code, it seems like there should be an index on torrents.name?

Why does it need?

[gennaios]

I didn't quite understand the code at first. It seems like the only index needed might be for torrents.queue_position. Such would speed up I believe order by queue_position and update torrents set queue_position = ? where queue_position = ?.

[KyleSanderson]

So, the migration on this is brutal. If the process is interrupted, the existing session torrents are ignored, and you're left with whatever was imported in the single swing. I'd highly recommend adding a column bit in a table saying the migration was successful, and the old session directory can be ignored.

[KyleSanderson]

Is anyone else seeing torrents not coming back when the client crashes? Seems like a ton of session state is lost when this happens.

[Mazino-Urek]

You might want to open an issue ticket. Seems like a legit problem.

[klepp0906]

just curious, looking forward in expectation that one day this becomes the default.

I currently backup in several ways. 1 being a daily macrium image of my system drive which obviously includes %localappdata% and as such the fastresume files.

I also run a robocopy script on sundays that ships some choice critical data to the cloud. one of those items is the %localappdata%\qBittorrent folder.

Several times ive had hiccups which resulted in qbittorrent trying to recheck 15000 or so torrents amounting to ~50TB of data. Pretty much a non starter had i not had a backup of the BT_backup folder to pull from.

Now purely as a user who doesnt know much about anything, i have heard during my travels about sqlite databases and their fickleness when being written to/read from while open in regard to data loss and/or corruption. I believe i was advised to get my calibre library out of my dropbox folder for example, lest I risk the aforementioned.

in an extremely long winded way, what im getting at is; would my current backup methodology present an issue with the live database or the validity of the backed up data (considering both macrium and the robocopy script are ran while qbittorrent is active/open/running) were i to switch to the sql database?

i'd be lying if i said the speed improvement wouldnt be very welcome and is enticing considering the volume of torrents i deal with. Not at the expense of introducing more risk as far as data issues go though.

[Forza-tng]

just curious, looking forward in expectation that one day this becomes the default.

I currently backup in several ways. 1 being a daily macrium image of my system drive which obviously includes %localappdata% and as such the fastresume files.

I also run a robocopy script on sundays that ships some choice critical data to the cloud. one of those items is the %localappdata%\qBittorrent folder.

Several times ive had hiccups which resulted in qbittorrent trying to recheck 15000 or so torrents amounting to ~50TB of data. Pretty much a non starter had i not had a backup of the BT_backup folder to pull from.

Now purely as a user who doesnt know much about anything, i have heard during my travels about sqlite databases and their fickleness when being written to/read from while open in regard to data loss and/or corruption. I believe i was advised to get my calibre library out of my dropbox folder for example, lest I risk the aforementioned.

in an extremely long winded way, what im getting at is; would my current backup methodology present an issue with the live database or the validity of the backed up data (considering both macrium and the robocopy script are ran while qbittorrent is active/open/running) were i to switch to the sql database?

i'd be lying if i said the speed improvement wouldnt be very welcome and is enticing considering the volume of torrents i deal with. Not at the expense of introducing more risk as far as data issues go though.

Sqlite3 is generally crash safe. However Dropbox etc is a different thing. It is not a local filesystem, and most databases won't work well on online storage.

Check that you have WAL mode enabled. You should see a torrents.db-wal file when running.

[klepp0906]

just curious, looking forward in expectation that one day this becomes the default.
I currently backup in several ways. 1 being a daily macrium image of my system drive which obviously includes %localappdata% and as such the fastresume files.
I also run a robocopy script on sundays that ships some choice critical data to the cloud. one of those items is the %localappdata%\qBittorrent folder.
Several times ive had hiccups which resulted in qbittorrent trying to recheck 15000 or so torrents amounting to ~50TB of data. Pretty much a non starter had i not had a backup of the BT_backup folder to pull from.
Now purely as a user who doesnt know much about anything, i have heard during my travels about sqlite databases and their fickleness when being written to/read from while open in regard to data loss and/or corruption. I believe i was advised to get my calibre library out of my dropbox folder for example, lest I risk the aforementioned.
in an extremely long winded way, what im getting at is; would my current backup methodology present an issue with the live database or the validity of the backed up data (considering both macrium and the robocopy script are ran while qbittorrent is active/open/running) were i to switch to the sql database?
i'd be lying if i said the speed improvement wouldnt be very welcome and is enticing considering the volume of torrents i deal with. Not at the expense of introducing more risk as far as data issues go though.

Sqlite3 is generally crash safe. However Dropbox etc is a different thing. It is not a local filesystem, and most databases won't work well on online storage.

Check that you have walk mode enabled. You should see a torrents.db-wal file when running.

oh no, im not talking about running it from within dropbox. i'm simply talking about backing up the database to another location (in this case a dropbox folder via script) while qbittorrent is running in its default location (%localappdata%)

[mohkale]

I really like the sqlite implementation but another issue I'd like to raise is that there's no way to combine qbit databases from multiple instances together. For some context I normally run qbit on my home server but also have an instance on my laptop that I take while travelling. More often than not I sync my laptop back to my main server when I get back. I just tried to do the same with qbit by just incrementing the torrent ids and sqlite inserting the records but that failed :/. Any tips for how this is supposed to be done? With torrents and resume files it's generally as easy as copying them across 🤔.

[mzso]

Well, if your goal is merger you can change back to resume files for the merger and to sqlite again after.
If you want to figure out how to merge sqlite remsume files, I have no idea.

[andry81]

If you want to figure out how to merge sqlite remsume files, I have no idea.

They have hash number as a name. You can compare file lists in Total Commander (SHIFT-F2) and just add them. Qbt shall attempt to merge the trackers.

[mohkale]

Well, if your goal is merger you can change back to resume files for the merger and to sqlite again after.

Sure, but is that the only way to do this? Ideally I'd like some option to import this kinda metadata? Maybe something in the UI that says export torrents and then that gives you a dump file that you can reimport through the UI as well.

[mzso]

Seeing as there isn't any we're stuck with this for the time being.

[glassez]

I just tried to do the same with qbit by just incrementing the torrent ids and sqlite inserting the records but that failed :/.

Any details?

[mohkale]

# Create a working copy of the torrents database
cp torrents.db torrents.scratch.db

# Generate an SQL dump of the torrent database from the other host
sqlite3 torrents.otherhost.db .dump > import.sql

# Delete the junk create table and metadata records, increment the torrent id counters to avoid primary key conflicts with existing torrents.db (you can use the same dump as above to figure out what the largest value is)
vim import.sql

# Update the metadata in our scratch database
sqlite3 torrents.scratch.db < import.sql

# If all good, sync back to our primary database file.
mv -f torrents.scratch.db torrents.db

Note: This didn't work. I'm not exactly sure why 🙃.

[glassez]

So what happens after that? How exactly "This didn't work"? Are there any error messages, etc.?