QSqlQuery can not add QByteArray to BLOB column without CLIENT_COMPRESS option



  • Hi,

    I tried to insert a file into BLOB column in MariaDB like

    QSqlQuery query(db);
    query.prepare(
                    R"(INSERT INTO Thesis (UserId, Title, File)
                       VALUES (:userId, :title, :file)
                       ON DUPLICATE KEY UPDATE Title = :title)");
    query.bindValue(":userId", 1);
    query.bindValue(":title", "example");
    
    QByteArray ba;
    QFile f("/home/document.pdf");
    if(f.open(QIODevice::ReadOnly)) {
        ba = f.readAll();
        f.close();
    }
    
    query.bindValue(":file", ba, QSql::In | QSql::Binary);
    
    if (!query.exec()) {
        qWarning()<< Q_FUNC_INFO << query.lastError();
    }
    

    above code always generate errors

    QSqlError("2006", "QMYSQL3: Unable to execute statement", "MySQL server has gone away")
    

    but when I set CLIENT_COMPRESS connect option

    db.setConnectOptions("CLIENT_COMPRESS=1");
    

    then the above code works fine. I found that sometimes I also need to set additional options like

    db.setConnectOptions("MYSQL_OPT_RECONNECT=1;MYSQL_OPT_WRITE_TIMEOUT=1000;CLIENT_COMPRESS=1");
    

    to make it work, someone know what causes that issue?

    I increased max_allowed_packet option on the server but that was not necessary since file is < 2MB

    MariaDB [testdb]> SHOW VARIABLES LIKE '%packet%';
    +--------------------------+------------+
    | Variable_name            | Value      |
    +--------------------------+------------+
    | max_allowed_packet       | 268435456  |
    | slave_max_allowed_packet | 1073741824 |
    +--------------------------+------------+
    

    Software:
    10.1.26-MariaDB server
    Qt 5.10.1
    Debian 9


  • Lifetime Qt Champion

    Hi,

    Do you have the same problem if you use MySQL ?


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.