QSqlQuery::exec() freezes over OpenVPN

petar

In normal situation, locally or over LAN, when executing:

query = QSqlQuery()
success = query.exec("some SELECT SQL command")

when query fails it returns false and everything is ok. I can choose what to do next. I usually have some loop with some delay and try more times and it executes finally.

When executing this over OpenVPN, expecting 25,000+ records, query.exec() just freezes and freezes my desktop app. QSqlQuery::exec() is a black box, it certainly has some built-in timeout, because over LAN it relatively quickly returns false. One would expect it to do the same over OpenVPN. The loop here doesn't help, because exec() freezes.

Moreover, after freeze, I have to kill the app and reconnect OpenVPN in order for connection to database and queries to work. Even psql doesn't work any more.

As I can see, QSqlQuery::exec() doesn't let us parameterize timeout. It is hardcoded. But in this situation it wouldn't help, because the timeout activates locally or over LAN, but doesn't get the chance to activate over OpenVPN.

This only happens with SELECT queries over OpenVPN that expect 25,000+ records in that query. For smaller queries, it works ok.

JonB

@petar
I'm not defending whether this is right or not, and if you get a resolution to this behaviour on this forum that's great. But if you do not and just want to get it working, you could add a LIMIT 25000 to your calls and read in "pages" at a time, better than freezing machine!

If there's anything else it might be under QSqlQuery::setConnectOptions(), though I don't know of anything which would help, but you might investigate.

petar

Thanks JonB,

I investigated Postgres QSqlQuery::setConnectOptions() but like you, I couldn't find anything of help.

Yes, if I don't get a resolution to this behaviour, I will use LIMIT.

I think it is a Qt bug. QSqlQuery::exec() shouldn't freeze under any circumstances.

Christian Ehrlicher

@petar said in QSqlQuery::exec() freezes over OpenVPN:

I think it is a Qt bug. QSqlQuery::exec() shouldn't freeze under any circumstances.

I think if you call QSqlQuery::exec() in the gui thread when you expect so many records you're doing something wrong. QSqlQuery::exec() is blocking so if you don't want to block your ui thread, move it to another thread.

petar

Ok, and then what. Suppose the query in another thread succeeds. How can I use that query in main thread and attach it to a QSqlQueryModel model and display data in QTableView?

If the query freezes in another thread, I suppose I can handle that with some timeout. Is there a way to kill the thread that left frozen?

A.A.SEZEN

I think you should focus on OpenVPN for the solution.
There is no query timeout in this sense mentioned in the SQLite documents.
However, the problem of freezing with OpenVPN is mentioned in internet searches.

JonB

@petar

Ok, and then what. Suppose the query in another thread succeeds. How can I use that query in main thread and attach it to a QSqlQueryModel model and display data in QTableView?

I presume that you would put your QSqlQueryModel in its own background thread while keeping the QTableView in the main, GUI thread. Can someone confirm that the model signals to the view slots would work fine across threads (hmm....), or does one have to do something else?

petar

@A-A-SEZEN
Ok, it is PostgreSQL but maybe it's irrelevant. What i wanted to say is that it is a (serious) bug in Qt. QSqlQuery::exec() shouldn't freeze under any circumstances. It's not a problem to me if the query returns false because of too many records or whatever. But it freezes my whole app. QSqlQuery::exec() has to be somehow implemented that it can't freeze.

petar

@JonB
Maybe you are right. I don't know if it is doable.

But the whole point is that if QSqlQuery::exec() would work ok, then there would be no need for another thread, because query (for 25-50,000 records) takes only few seconds over OpenVPN, and that is acceptable, and if query returns false, i can retrieve fewer records (LIMIT, or segment it,...) in another try or do whatever I want, but don't freeze the whole app for God's sake.

For example, what if the network (over OpenVPN) is gone right before I start QSqlQuery::exec(), should it really freeze?

Or just return false.

JonB

@petar
The Qt SQL stuff uses drivers to do the low-level stuff. It may not be a "fault" in QSqlQuery::exec(), it may be the underlying protocol talking to SQL over OpenVPN which is what "freezes", and there is nothing the Qt level can do about that.

petar

@JonB
Aren't those drivers a part of Qt (eg. QPSQL for Postgres), it's not external driver.

JonB

@petar
Yes, but ultimately they must leverage some lower-level protocol code (e.g. ODBC, or whatever for MySQL or Postgres), and that is where it may be "freezing" over OpenVPN? I was just saying it may not be QSqlQuery::exec() code itself, which may be built atop other levels, which freezes, and it may not be able to do anything about it, which you are asking it to do.

A.A.SEZEN

@petar said in QSqlQuery::exec() freezes over OpenVPN:

Ok, and then what. Suppose the query in another thread succeeds. How can I use that query in main thread and attach it to a QSqlQueryModel model and display data in QTableView?
If the query freezes in another thread, I suppose I can handle that with some timeout. Is there a way to kill the thread that left frozen?

Yes.

void MainWindow::threadStart()
{
    threadQuery * tQuery = new threadQuery;
    connect(tQuery, &threadQuery::record, this, &MainWindow::record);
    tQuery->start();
}

void MainWindow::record(QSqlQueryModel *model)
{
    ui->tableView->setModel(model);
}

void threadQuery::run()
{
    QSqlQuery q(conn);
    if(q.exec("SELECT * FROM table")) {
        model->setQuery(q);
        emit this->record(model);
    }

}

petar

@A-A-SEZEN
Ok, thanks, but what about this limit from the documentation?

It says: "A connection can only be used from within the thread that created it. Moving connections between threads or creating queries from a different thread is not supported."

How can I further work with this query/model in my main thread?

What happens when your code in thread:

if(q.exec("SELECT * FROM table")) {

(precisely exec() function) freezes?

A.A.SEZEN

@petar
Your application will not freeze due to Thread.
You know the integrity of your code.

JonB

@A-A-SEZEN

    QSqlQuery q(conn);
    if(q.exec("SELECT * FROM table")) {
        model->setQuery(q);

I don't know, but have you verified whether this does not execute the query twice?

petar

@JonB
No, only once.

petar

@A-A-SEZEN
Can I use thread for queries working with QSqlQuery and QSqlQueryModel? I am not sure. What about sharing connections (that is not allowed) between threads, and sharing queries?

jsulm

@petar said in QSqlQuery::exec() freezes over OpenVPN:

Aren't those drivers a part of Qt (eg. QPSQL for Postgres), it's not external driver.

Those use client libraries provided by the SQL database system like libmysqlclient for MySQL. To see whether this is an issue in Qt or database system you can try to execute exactly the same query over VPN with the client application of your database system.

petar

@jsulm
Good point. I checked the same query over OpenVPN with LIMIT 25,000 - 200,000 records in Qt and in plain psql. In Qt QSqlQuery::exec() almost always freezes, while psql never.