When reading VARCHAR column in MariaDB, invalid QVariant is returned.

DeanLee

OS : Windows 10 64bit
Qt Version : 6.4.0
MariaDB version : 11.4.3

My DB and Table Create Code :

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
DROP DATABASE IF EXISTS `MyDatabase`;
CREATE DATABASE IF NOT EXISTS `MyDatabase` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */;
USE `MyDatabase`;

DROP TABLE IF EXISTS `org_data_class`;
CREATE TABLE IF NOT EXISTS `org_data_class` (
  `index` int(11) NOT NULL AUTO_INCREMENT,
  `equip` varchar(50) NOT NULL,
  `command` varchar(50) NOT NULL,
  PRIMARY KEY (`index`) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=584 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

My Code :

   QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
   db.setHostName("127.0.0.1");
   db.setDatabaseName("MyDatabase");
   db.setPort(11136);
   db.setUserName("root");
   db.setPassword("MyTestDatabase");
   qDebug() << db.open();
   QSqlQuery q("SELECT * FROM org_data_class");
   while(q.next())
   {
       qDebug() << q.isValid() << q.isActive() << q.lastError();
       qDebug() << q.value(0) << q.value(1) << q.value(2);
   }

and this is my result :

true // db.open()
true true QSqlError("", "", "") // isValid(), isActive(), lastError()
QVariant(double, 1) QVariant(Invalid) QVariant(Invalid)  // query result
true true QSqlError("", "", "")
QVariant(double, 2) QVariant(Invalid) QVariant(Invalid) 
true true QSqlError("", "", "")
QVariant(double, 3) QVariant(Invalid) QVariant(Invalid)
...

As you can see from the result, the database was opened normally, and QSqlError shows no error. isValid() or isActive() both return true, and it is possible to find a specific field, and the row matches the actual number of data.
However, only a specific field cannot get the value, and QVariant is invalid, and when converted to toString() or toQByteArray(), there is only an empty string (i.e. "").
I can retrieve an integer index from the same table in the same database without any problem, so it seems like there is a problem with retrieving the text of VARCHAR. I searched for this but couldn't find anything. I tried building the MySQL plugin with both Mingw and MSVC, but both resulted in the same result.
What should I try?

Christian Ehrlicher

@DeanLee said in When reading VARCHAR column in MariaDB, invalid QVariant is returned.:

What should I try?

First a more recent Qt version.
Then make sure you use the correct mariadb client libraries (and not the mysql ones) as they now more and more diverge (sadly).
Then please provide a minimal, compilable example of your problem - including the create table and insert statements. Could be done in 30 lines in a main.cpp

DeanLee

@Christian-Ehrlicher Sadly, update to the latest Qt is not possible for several reasons. All I can do is look through the list of issues for 6.4.0.
And I definitely built the plugin using mariadb's connector.
I have written the sample source code below. Still the result is the same.

#include <QCoreApplication>
#include <QtSql>

int main(int argc, char *argv[])
{
    QCoreApplication a(argc, argv);

    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("127.0.0.1");
    db.setPort(11136);
    db.setUserName("root");
    db.setPassword("longandcomplexpassword");
    qDebug() << db.open();

    QSqlQuery query("CREATE DATABASE `MyDatabase`;");
    db.close();
    db.setDatabaseName("MyDatabase");
    if(!db.open())
    {
        qDebug() << "Failed to open database : " << db.lastError();
        return 0;
    }

    query.prepare("CREATE TABLE `org_data_class` ("
                  "`index` int(11) NOT NULL AUTO_INCREMENT,"
                  "`equip` varchar(50) NOT NULL,"
                  "`command` varchar(50) NOT NULL,"
                  "PRIMARY KEY (`index`) USING BTREE"
                  ")DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;");
    if(!query.exec())
    {
        qDebug() << "Failed to create table : " << query.lastError();
        return 0;
    }

    query.prepare("INSERT INTO `org_data_class` (`equip`, `command`) VALUES"
                  "('AAA', 'CMD1'), ('BBB', 'CMD2'), ('CCC', 'CMD3');");
    if(!query.exec())
    {
        qDebug() << "Failed to insert data : " << query.lastError();
        return 0;
    }

    QSqlQuery q("SELECT * FROM org_data_class");
    while(q.next())
    {
        qDebug() << db.lastError();
        qDebug() << q.isValid() << q.isActive() << q.lastError();
        qDebug() << q.value(0) << q.value(1) << q.value(2);
    }
    return a.exec();
}

and the output of this code :

true
QSqlError("", "", "")
true true QSqlError("", "", "")
QVariant(double, 1) QVariant(Invalid) QVariant(Invalid)
QSqlError("", "", "")
true true QSqlError("", "", "")
QVariant(double, 2) QVariant(Invalid) QVariant(Invalid)
QSqlError("", "", "")
true true QSqlError("", "", "")
QVariant(double, 3) QVariant(Invalid) QVariant(Invalid)

Christian Ehrlicher

I tried with MySQL Server 8.3 client libs + MySQL 8.3 Server, MySQL 8.3 Client libs + MariaDB 10.11 Server, MariaDB C Connector 3.3.2 client libs + MySQL 8.3 Server and MariaDB C Connector 3.3.2 and MariaDB 10.11 Server and all works as expected. I only modifed your example to not create a db in the first place and therefore also don't call db.close() with an open query (which is really a strange usecase and I'm not sure we support it but I guess it's only for the testcase):

int main(int argc, char* argv[])
{
    QCoreApplication a(argc, argv);
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("192.168.178.64");
    db.setUserName("testuser");
    db.setPassword("testuser");
    db.setDatabaseName("testdb");
    if (!db.open())
    {
        qDebug() << "Failed to open database : " << db.lastError();
        return 0;
    }
    QSqlQuery query(db);
    query.exec("DROP TABLE `org_data_class`");
    query.prepare("CREATE TABLE `org_data_class` ("
        "`index` int(11) NOT NULL AUTO_INCREMENT,"
        "`equip` varchar(50) NOT NULL,"
        "`command` varchar(50) NOT NULL,"
        "PRIMARY KEY (`index`) USING BTREE"
        ")DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;");
    if (!query.exec())
    {
        qDebug() << "Failed to create table : " << query.lastError();
        return 0;
    }
    query.prepare("INSERT INTO `org_data_class` (`equip`, `command`) VALUES"
        "('AAA', 'CMD1'), ('BBB', 'CMD2'), ('CCC', 'CMD3');");
    if (!query.exec())
    {
        qDebug() << "Failed to insert data : " << query.lastError();
        return 0;
    }
    QSqlQuery q("SELECT * FROM org_data_class");
    while (q.next())
    {
        qDebug() << db.lastError();
        qDebug() << q.isValid() << q.isActive() << q.lastError();
        qDebug() << q.value(0) << q.value(1) << q.value(2);
    }
    return 0;
}

If this testcase does not work for you the only way I see is to try to backport all changes to the Qt mysql plugin to see if this helps somehow (but I can't remember of such a change tbh) or debug by yourself what's going wrong.

DeanLee

@Christian-Ehrlicher I solved the problem. After digging deeper, I found that in the function QMYSQLResult::reset(const QString& query) in the source code qsql_mysql.cpp, the field type of all data was set to the default value MYSQL_TYPE_DECIMAL(0). Also, I saw that it was loading another libmariadb.dll from the list of loaded dlls, which I had not specified. Eventually, I decided to start over, and after deleting Qt, MariaDB, MariaDB connector, and environment variables, rebuilding, installing, and building the plugin, it finally worked fine. I still don't know exactly what the problem was, but thank you for your attention.