QSqlQuery can not add QByteArray to BLOB column without CLIENT_COMPRESS option

QLab

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

SGaist

Hi,

Do you have the same problem if you use MySQL ?