QSqlTableModel - Unable to find table <TABLE_NAME>

t.vanbesien

Hi :)

I can't make QSqlTableModel work. Here's how I initialize the db (it works):

    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setDatabaseName("???");
    db.setUserName("???");
    db.setPassword("???");
    bool ok = db.open();
    if(ok)
        qDebug() << "Connected to database";
    else {
        qDebug() << "Database connection failed";
        return -1;
    }

Here's the simple QSqlTableModel constructor I'm trying to use:

DbOrderModel::DbOrderModel(QObject* parent)
    : QObject{parent}
{
    _model = new QSqlTableModel(parent);
    qDebug() << QSqlDatabase::database().tables();
    _model->setTable("Orders");
    if(_model->lastError().isValid())
        qDebug() << "Error selecting data: " << _model->lastError().text();
    _model->setEditStrategy(QSqlTableModel::OnManualSubmit);
    _model->select(); // Initial data fetch
}

Here's the output from qDebug():

Connected to database
QList("Jobs", "Orders")
Error selecting data:  "Unable to find table Orders"

You can clearly see that the table exists in the ouput of QSqlDatabase::database().tables() but still it fails to select it.

I can access the table from a CLI, using the same credentials.

What surprises me is that if I use a QSqlQuery it does work! Like this:

    QSqlQueryModel orders_model;
    orders_model.setQuery("SELECT * FROM Orders");
    for(int i = 0; i < orders_model.rowCount(); ++i) {
        int id = orders_model.record(i).value("id").toInt();
        QString quality = orders_model.record(i).value("quality").toString();
        qDebug() << "Item n°" << i << " ID: " << id << " Quality: " << quality << '\n';
    }

So yeah, I wonder if this is an actual bug in Qt?

I'm using Qt 6.5 LTS (MinGW 64 bit). Here's a minimal working example but you need to have the mysql driver set up for your Qt, and a MySQL database running and available too (also replace the credentials with yours of course), you need a table named "Orders" with an "id" column:

#include "MainWindow.hpp"

#include <QApplication>
#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQueryModel>
#include <QSqlRecord>
#include <QSqlTableModel>

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);
    MainWindow w;
    w.show();

    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setDatabaseName("???");
    db.setUserName("???");
    db.setPassword("???");
    bool ok = db.open();
    if(ok)
        qDebug() << "Connected to database";
    else {
        qDebug() << "Database connection failed";
        return -1;
    }

    // Checking tables found in selected schema
    qDebug() << QSqlDatabase::database().tables();

    // WORKS AS EXPECTED
    QSqlQueryModel orders_model;
    orders_model.setQuery("SELECT * FROM Orders");
    for(int i = 0; i < orders_model.rowCount(); ++i) {
        int id = orders_model.record(i).value("id").toInt();
        qDebug() << "Item n°" << i << " ID: " << id;
    }

    // DOESN'T WORK AS EXPECTED
    QSqlTableModel table_model;
    table_model.setTable("Orders");
    if(table_model.lastError().isValid())
        qDebug() << "error: " << table_model.lastError().text();
    table_model.select();
    if(table_model.lastError().isValid())
        qDebug() << "error: " << table_model.lastError().text();
    qDebug() << "ROW COUNT= " << table_model.rowCount();
    for(int i = 0; i < table_model.rowCount(); ++i) {
        int id = table_model.record(i).value("id").toInt();
        qDebug() << "Item n°" << i << " ID: " << id;
    }

    return a.exec();
}

Here's the output:

Connected to database
QList("Jobs", "Orders")
Item n° 0  ID:  1
Item n° 1  ID:  2
Item n° 2  ID:  3
Item n° 3  ID:  4
Item n° 4  ID:  5
Item n° 5  ID:  6
Item n° 6  ID:  7
Item n° 7  ID:  8
Item n° 8  ID:  9
Item n° 9  ID:  10
error:  "Unable to find table Orders"
error:  "Unable to find table Orders"
ROW COUNT=  0

JonB

@t-vanbesien
Ignoring for now the lastError() immediately after setTable() (which does not "select any data" despite your comment), you have not said what actually happens when you do the first select()? Make sure there is at least one row, do you get any back? And print out/show us any ,error message from the select() even if it's the same as from the setTable().

t.vanbesien

@JonB Hey I remember you! You answered one of my questions a year ago haha

Yes you are right setTable() is not select()! I didn't include it because select() just doesn't do anything when the previous setTable() fails.

But anyway I edited the minimal reproductible example for clarity.

Here's the ouput of the (edited) minimal example:

Connected to database
QList("Jobs", "Orders")
Item n° 0  ID:  1
Item n° 1  ID:  2
Item n° 2  ID:  3
Item n° 3  ID:  4
Item n° 4  ID:  5
Item n° 5  ID:  6
Item n° 6  ID:  7
Item n° 7  ID:  8
Item n° 8  ID:  9
Item n° 9  ID:  10
error:  "Unable to find table Orders"
error:  "Unable to find table Orders"
ROW COUNT=  0

@JonB said in Unable to find table Orders:

(which does not "select any data" despite your comment),

I meant that the subsequent select() fails to select the data. Bear with me, English isn't my first language ^^

JonB

@t-vanbesien
I cannot hejlp further. Your code looks reasonable. I have certainly used all this with MySQL under Linux at Qt 5.12/15 and it worked as it should. If it is a Qt6 issue I do not have that.

t.vanbesien

@JonB Alright, thanks for trying :)

I'll just use a QSqlQueryModel since it works. It doesn't really matter anyway for what I'm trying to do.

Christian Ehrlicher

I can't reproduce it with Qt6.dev either. Please provide a minimal, compilable example which also creates the table. The MainWindow stuff can be thrown away. Do you have some case-sensitive settings in your mysql config?

t.vanbesien

@Christian-Ehrlicher Here's the example you asked for (you still have to input the db credentials though), it creates the table and insert a value into it too.

#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>
#include <QSqlQueryModel>
#include <QSqlRecord>
#include <QSqlTableModel>

int main()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setDatabaseName("?");
    db.setUserName("?");
    db.setPassword("?");
    bool ok = db.open();
    if(ok)
        qDebug() << "Connected to database";
    else {
        qDebug() << "Database connection failed";
        return -1;
    }

    const QString table_name = "some_table";
    const QString creation_query = "CREATE TABLE IF NOT EXISTS " + table_name
                                   + " (id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100))";
    {
        QSqlQuery query;
        bool success = query.exec(creation_query);
        if(!success) {
            qDebug() << "Failed to create table:" << query.lastError().text();
            return -1;
        }
        else
            qDebug() << "Table created successfully";
    }

    // Checking tables found in selected schema
    qDebug() << QSqlDatabase::database().tables();

    // Adding row for testing
    const QString insert_query = "INSERT INTO " + table_name + " (name) VALUES (\"some_name\")";
    {
        QSqlQuery query;
        bool success = query.exec(insert_query);
        if(!success) {
            qDebug() << "Failed to insert row:" << query.lastError().text();
            return -1;
        }
        else
            qDebug() << "Row inserted successfully";
    }

    // WORKS AS EXPECTED
    QSqlQueryModel orders_model;
    orders_model.setQuery("SELECT * FROM " + table_name);
    for(int i = 0; i < orders_model.rowCount(); ++i) {
        int id = orders_model.record(i).value("id").toInt();
        qDebug() << "Item n°" << i << " ID: " << id;
    }

    // DOESN'T WORK AS EXPECTED
    QSqlTableModel table_model;
    table_model.setTable(table_name);
    if(table_model.lastError().isValid())
        qDebug() << "error: " << table_model.lastError().text();
    table_model.select();
    if(table_model.lastError().isValid())
        qDebug() << "error: " << table_model.lastError().text();
    qDebug() << "ROW COUNT= " << table_model.rowCount();
    for(int i = 0; i < table_model.rowCount(); ++i) {
        int id = table_model.record(i).value("id").toInt();
        qDebug() << "Item n°" << i << " ID: " << id;
    }

    return 0;
}

@Christian-Ehrlicher said in Unable to find table Orders:

I can't reproduce it with Qt6.dev either. Please provide a minimal, compilable example which also creates the table. The MainWindow stuff can be thrown away. Do you have some case-sensitive settings in your mysql config?

I don't believe I do. I'm using the latest MySQL Docker official image without any special configuration. This one here.

More information: I'm on Windows, using MinGW 64 bits. The MySQL database is running on the docker engine on localhost.

Christian Ehrlicher

This testcase also works as expected for me. The only way I see is to
a) update to Qt 6.7.0 to see if the problem still persists (I think so)
b) Debug QSqlTableModel to see the acutal query created and sent to the db.

How did you compile the mysql plugin? What exact Qt version do you use?

t.vanbesien

@Christian-Ehrlicher I compiled the plugin following this guide, but it was a while ago I don't remember the details.

I'm using Qt 6.5.0, the kit I use is MinGW 64 bit. I'm on Windows.

I will try, tomorrow, to recompile the plugin, and if it fails I will try a more recent version of Qt to see if it fixes the issue.

@Christian-Ehrlicher said in Unable to find table Orders:

b) Debug QSqlTableModel to see the acutal query created and sent to the db.

When I use the lastQuery() function I get an empty string.

Christian Ehrlicher

@t-vanbesien said in Unable to find table Orders:

When I use the lastQuery() function I get an empty string.

I meant you have to debug the model, not printing some error states.

I'm using Qt 6.5.0, the kit I use is MinGW 64 bit. I'm on Windows.

So you are using another mysql client library version than the server version? Make sure it's the same. 6.5.0 QMySQL plugin could not be compiled against MySQL 8.3.0 libs: https://bugreports.qt.io/browse/QTBUG-121183

t.vanbesien

@Christian-Ehrlicher

@Christian-Ehrlicher said in Unable to find table Orders:

So you are using another mysql client library version than the server version? Make sure it's the same. 6.5.0 QMySQL plugin could not be compiled against MySQL 8.3.0 libs

Alright that's it! I made a new database container using MySQL 8.0 instead of 8.3 and it works as expected. It must be because I built the driver using this previous version of the MySQL client and then at some point I must have made a new container using the latest MySQL image instead of the one compatible with the driver I built. Thanks!