Open sqlite database on network drive

OlivierLdff

Hi,
I'm trying to open a sqlite database with QSqlDatabase from a network drive mounted in my ubuntu 20 system.

const QString path = "/run/user/1000/gvfs/smb-share:server=mynas.local,share=drive/db.sqlite";
auto db = QSqlDatabase::addDatabase("QSQLITE", path);
db.open(); // Fail with "unable to open database file Error opening database"

This code is working as expected anywhere else.
Any idea what i'm missing? I'm able to manipulate file on this drive with QFile without any issue.
Also using DBBrowser For SQLite I don't have any issue (it is not using QSqlDatabase).

Thanks for any hint.
Have a nice day.

jsulm

@OlivierLdff said in Open sqlite database on network drive:

This code is working as expected anywhere else.

Where is it not working? Or do you mean local path is working?

artwaw

@OlivierLdff
Since addDatabase(const QString &type, const QString &connectionName = QLatin1String(defaultConnection)) please try:

const QString path = "/run/user/1000/gvfs/smb-share:server=mynas.local,share=drive/db.sqlite";
auto db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName(path);
if (!db.open()) {
    qDebug() << db.lastError().text();
}

Connection name != database name (or, in case of SQLite, database file name).

OlivierLdff

@jsulm The code is working for example if I use path ~/db.sqlite, or in any other directory. Only this network drive with the strange path fail.

@artwaw I tried your solution with no luck. It still fails with the same error.

Is there a way to enable more logs from QSqlDatabase?

artwaw

@OlivierLdff if you tried what I posted you should have error text in the console. Please paste it.
"more logs" can be obtained by examining the value of QSqlDatabase::lastError() which is of class QSqlError

Christian Ehrlicher

@artwaw said in Open sqlite database on network drive:

"/run/user/1000/gvfs/smb-share:server=mynas.local,share=drive/db.sqlite"

I doubt this is a valid file name for a sqlite database.

OlivierLdff

@artwaw So it seems I'm not even allowed to do touch db.sqlite once the file have been removed. Maybe this is an issue with my NAS?
Anyway I tried another name to see, foo.sqlite. So you were right, opening work now!

Error:  QSqlError("10", "Unable to fetch row", "disk I/O error")
Error:  QSqlError("", "Parameter count mismatch", "")

File foo.sqlite gets created on my NAS but size is 0 bytes.

@Christian-Ehrlicher I can open this location with a QFile, and write to it without any problems.

Christian Ehrlicher

@OlivierLdff said in Open sqlite database on network drive:

I can open this location with a QFile, and write to it without any problems.

But sqlite can not handle this path since fopen() will for sure not accept it. Try it on the command line with sqlite3 executable.

OlivierLdff

@Christian-Ehrlicher I should have started with that.

sqlite3 /run/user/1000/gvfs/smb-share:server=mynas.local,share=drive/foo.sqlite
sqlite> create table test(one varchar(10), two smallint);
Error: disk I/O error

So could this be related to my network drive?

artwaw

@OlivierLdff size will be zero until you write anything to the db (create table for example), that is only to be expected.
Like @Christian-Ehrlicher wrote - SQLite driver uses fopen() so the path variable need to conform.

For network resources (and I'd like to point out that there is a strong advocacy against using sqlite over network) you might want to consider URI notation https://sqlite.org/uri.html

OlivierLdff

@artwaw I tried reading from an existing sqlite file, no problem.
But writing doesn't seems to work, and always result in to disk I/O error. (even with prepending file: to the path)

So is my issue a known limitation of sqlite?

KroMignon

@OlivierLdff said in Open sqlite database on network drive:

So is my issue known limitation of sqlite?

I don't think so, I think you don't have write access.

OlivierLdff

@KroMignon Is there something special to get them?
I'm able to write QFile at this location.

KroMignon

@OlivierLdff said in Open sqlite database on network drive:

Is there something special to get them?
I'm able to write QFile at this location.

Your file path looks uncommon to me.
I am not sure it can be handled directly.
Does this works?

touch /run/user/1000/gvfs/smb-share:server=mynas.local,share=drive/foo.sqlite

OlivierLdff

@KroMignon said in Open sqlite database on network drive:

touch /run/user/1000/gvfs/smb-share:server=mynas.local,share=drive/foo.sqlite

Yes it works.

From what I see this issue isn't Qt related as I tought at the beginning.

artwaw

@OlivierLdff might be this is somehow related to the way SQLite driver operates. I'd either mount the share to be accessible via usual path (like /media/share) or ditch sqlite in favour of psql/mysql.
Please also note two things:

smb share might have performance issues with sqlite, this might lead to either transfer data loss or file being garbled (at the extreme);
at the same time psql/mysql might be actually faster than sqlite over smb

Of course, it all depends on how extensively you use that db.
Part of the performance penalties can be alleviated by using nfs instead. In both cases it is recommended to turn off any kind of read/write caching for sqlite over network as caching introduces potential errors in the data. SQLite is designed to compete with file system operations (fopen, read, seek, etc) hence using it over the network is always tricky (I agree that for lightweight use it works most of the time... except where it does not and debugging those issues is never easy).

OlivierLdff

@artwaw Thanks a lot for the informations.
For my particular case, I just need to populate a database with some data, then close it.
I think it will be way easier to write the sqlite database in some tmp/ folder, then save it on my NAS with a copy operation.

Thanks a lot for your support have a nice day.