A big problem with inserting image to BLOB field in MySQL Database...

[zheleznov]

Hello there. We were trying to get rid of this problem for 3 days now with no success.
We have a code that writes an image to QByteArray and then stores it in the MEDIUMBLOB field in MySQL database.
The problem is that we can't read that array back... AFTER we load the bytearray to the database, it's either corrupted or I don't know what else happens there, but we cannot read it correctly anymore. If we upload an image directly from mysql/phpmyadmin, everything is ok and the program reads the blob fine with the help of QPixmap::loadFromData(). But if we store the array to the blob by our program (not directly from phpmyadmin) - we cannot read it for some reason. We just get "QPixmap(null)". We tried a loooot of ways, reinstalling the program, the database, recompiling the MySQL drivers, a lot of different ways of reading the image (QImage, File, with buffer and without, etc) and no success. Looks like it doesn't store the array into blob properly... Would be very thankful for help! Code snippets in attachment.

QT version: 5.6.28 (also tried 5.5)
MySQL version:5.7
OS : Windows 10

---

Added after 1 45 minutes:

Take a look at the screenshot please.
To the left you can see the image that we tried to upload through our program, to the right - through the DB.
As you can see, the database doesn't also recognize our array as an image. What might be the problem? Encoding? Qt?..
This were two exact the same images.

NK2Z47FNCYs.jpg

[d_stranz]

Is the QIODevice::Text flag set on your QBuffer?

[wysota]

The problem might be that you are trying to insert binary data into a text string. IMO you should encode it in e.g. base64 and decode it back when inserting into the database.

[ChrisW67]

Going via QPixmap may result in the image being stored with different metadata or compressed differently when saved to the buffer... leading to a binary but not visible difference. Given that you already have the image in a file and you only wish to store it there really is no need to go via QPixmap and QBuffer at all.

This code works with a Mysql mediumblob. The in.png file and out.png file are bit-for-bit equal:

#include <QCoreApplication>
#include <QFile>
#include <QByteArray>
#include <QVariant>
#include <QSqlDatabase>
#include <QSqlQuery>
 
int main(int argc, char **argv)
{
    QCoreApplication app(argc, argv);
 
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setDatabaseName("test");
    db.setUserName("test");
    db.setPassword("test");
    if (db.open()) {
        QSqlQuery qry("delete from testdata");
 
        QFile in("in.png");
        if (in.open(QIODevice::ReadOnly)) {
            QByteArray data = in.readAll();
            qry.prepare("INSERT INTO testdata (firstname , secondname , userfoto) VALUES (?,?,?)");
            qry.bindValue(0, QVariant("Dummy"));
            qry.bindValue(1, QVariant("Dummy"));
            qry.bindValue(2, data);
            qry.exec();
        }
 
        QFile out("out.png");
        if (out.open(QIODevice::WriteOnly)) {
            qry.prepare("SELECT userfoto from testdata");
            qry.exec();
            qry.next();
            QByteArray data = qry.value(0).toByteArray();
            out.write(data);
        }
    }
    return 0;
}

#include <QCoreApplication>
#include <QFile>
#include <QByteArray>
#include <QVariant>
#include <QSqlDatabase>
#include <QSqlQuery>

int main(int argc, char **argv)
{
    QCoreApplication app(argc, argv);

    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setDatabaseName("test");
    db.setUserName("test");
    db.setPassword("test");
    if (db.open()) {
        QSqlQuery qry("delete from testdata");

        QFile in("in.png");
        if (in.open(QIODevice::ReadOnly)) {
            QByteArray data = in.readAll();
            qry.prepare("INSERT INTO testdata (firstname , secondname , userfoto) VALUES (?,?,?)");
            qry.bindValue(0, QVariant("Dummy"));
            qry.bindValue(1, QVariant("Dummy"));
            qry.bindValue(2, data);
            qry.exec();
        }

        QFile out("out.png");
        if (out.open(QIODevice::WriteOnly)) {
            qry.prepare("SELECT userfoto from testdata");
            qry.exec();
            qry.next();
            QByteArray data = qry.value(0).toByteArray();
            out.write(data);
        }
    }
    return 0;
}

To copy to clipboard, switch view to plain text mode 

CREATE TABLE `testdata` (
  `firstname` varchar(50) DEFAULT NULL,
  `secondname` varchar(50) DEFAULT NULL,
  `userfoto` mediumblob
)

CREATE TABLE `testdata` (
  `firstname` varchar(50) DEFAULT NULL,
  `secondname` varchar(50) DEFAULT NULL,
  `userfoto` mediumblob
)

To copy to clipboard, switch view to plain text mode 

Qt 5.5.1, Mysql 5.6.28, Linux

[zheleznov]

Going via QPixmap may result in the image being stored with different metadata or compressed differently when saved to the buffer... leading to a binary but not visible difference. Given that you already have the image in a file and you only wish to store it there really is no need to go via QPixmap and QBuffer at all.

This code works with a Mysql mediumblob. The in.png file and out.png file are bit-for-bit equal:

#include <QCoreApplication>
#include <QFile>
#include <QByteArray>
#include <QVariant>
#include <QSqlDatabase>
#include <QSqlQuery>
 
int main(int argc, char **argv)
{
    QCoreApplication app(argc, argv);
 
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setDatabaseName("test");
    db.setUserName("test");
    db.setPassword("test");
    if (db.open()) {
        QSqlQuery qry("delete from testdata");
 
        QFile in("in.png");
        if (in.open(QIODevice::ReadOnly)) {
            QByteArray data = in.readAll();
            qry.prepare("INSERT INTO testdata (firstname , secondname , userfoto) VALUES (?,?,?)");
            qry.bindValue(0, QVariant("Dummy"));
            qry.bindValue(1, QVariant("Dummy"));
            qry.bindValue(2, data);
            qry.exec();
        }
 
        QFile out("out.png");
        if (out.open(QIODevice::WriteOnly)) {
            qry.prepare("SELECT userfoto from testdata");
            qry.exec();
            qry.next();
            QByteArray data = qry.value(0).toByteArray();
            out.write(data);
        }
    }
    return 0;
}

#include <QCoreApplication>
#include <QFile>
#include <QByteArray>
#include <QVariant>
#include <QSqlDatabase>
#include <QSqlQuery>

int main(int argc, char **argv)
{
    QCoreApplication app(argc, argv);

    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setDatabaseName("test");
    db.setUserName("test");
    db.setPassword("test");
    if (db.open()) {
        QSqlQuery qry("delete from testdata");

        QFile in("in.png");
        if (in.open(QIODevice::ReadOnly)) {
            QByteArray data = in.readAll();
            qry.prepare("INSERT INTO testdata (firstname , secondname , userfoto) VALUES (?,?,?)");
            qry.bindValue(0, QVariant("Dummy"));
            qry.bindValue(1, QVariant("Dummy"));
            qry.bindValue(2, data);
            qry.exec();
        }

        QFile out("out.png");
        if (out.open(QIODevice::WriteOnly)) {
            qry.prepare("SELECT userfoto from testdata");
            qry.exec();
            qry.next();
            QByteArray data = qry.value(0).toByteArray();
            out.write(data);
        }
    }
    return 0;
}

To copy to clipboard, switch view to plain text mode 

CREATE TABLE `testdata` (
  `firstname` varchar(50) DEFAULT NULL,
  `secondname` varchar(50) DEFAULT NULL,
  `userfoto` mediumblob
)

CREATE TABLE `testdata` (
  `firstname` varchar(50) DEFAULT NULL,
  `secondname` varchar(50) DEFAULT NULL,
  `userfoto` mediumblob
)

To copy to clipboard, switch view to plain text mode 

Qt 5.5.1, Mysql 5.6.28, Linux

No way! This code worked out for us , though we have already tried working through the file, but I guess we didn't read it through the file! Will try implementing this in our program, Biggest Thanks to you! Will keep you informed...

Best regards.

---

Added after 32 minutes:

Damn, for some reason it doesn't work in our program. The same problem occurs... I think the only way to understand what happens is to upload our project here so you can see the whole process.

---

Added after 34 minutes:

https://dropmefiles.com/4lBK5
Here is the project. Would be VERY thankful if someone could take a look and say what is the problem...

[ChrisW67]

I ran your code in my environment using Qt 5.4.1 and (with minor mods) Qt 4.8.6. Both produced the issue you complain of: the stored binary data is not the same as the loaded binary data. As wysota suggested, it appears the binary data has been treated as character data at some point: but not obviously in your code.
The original PNG file starts with the standard 8 byte header:

.. .. 89 50 4e 47 0d 0a 1a 0a

.. .. 89 50 4e 47 0d 0a 1a 0a

To copy to clipboard, switch view to plain text mode 

The output "PNG" has been mangled to start:

ef bf bd 50 4e 47 0d 0a 1a 0a

ef bf bd 50 4e 47 0d 0a 1a 0a

To copy to clipboard, switch view to plain text mode 

The first three bytes of the output; "ef bf bd" is the UTF-8 encoding of the Unicode U+FFFD code point. That code point is used to substitute for a part of string that is invalid in the encoding being used to interpret it. If the original binary was interpreted (incorrectly) as UTF-8 then the "89" byte would be stored as U+FFFD in a UTF-16 string which would become the bytes we see if output as UTF-8.

Your insert and select statement strings are compounds of a Mysql "USE test;" and the SQL. The database you are connected to is set by the QSqlDatabase connection you use and need not be specified again. Removing the "USE test;" from both statements makes the code behave. I can only think that the switch of connection caused by "USE", or the compound statement itself confused the Qt bindValue() or Mysql, or both.

[zheleznov]

I ran your code in my environment using Qt 5.4.1 and (with minor mods) Qt 4.8.6. Both produced the issue you complain of: the stored binary data is not the same as the loaded binary data. As wysota suggested, it appears the binary data has been treated as character data at some point: but not obviously in your code.
The original PNG file starts with the standard 8 byte header:

.. .. 89 50 4e 47 0d 0a 1a 0a

.. .. 89 50 4e 47 0d 0a 1a 0a

To copy to clipboard, switch view to plain text mode 

The output "PNG" has been mangled to start:

ef bf bd 50 4e 47 0d 0a 1a 0a

ef bf bd 50 4e 47 0d 0a 1a 0a

To copy to clipboard, switch view to plain text mode 

The first three bytes of the output; "ef bf bd" is the UTF-8 encoding of the Unicode U+FFFD code point. That code point is used to substitute for a part of string that is invalid in the encoding being used to interpret it. If the original binary was interpreted (incorrectly) as UTF-8 then the "89" byte would be stored as U+FFFD in a UTF-16 string which would become the bytes we see if output as UTF-8.

Your insert and select statement strings are compounds of a Mysql "USE test;" and the SQL. The database you are connected to is set by the QSqlDatabase connection you use and need not be specified again. Removing the "USE test;" from both statements makes the code behave. I can only think that the switch of connection caused by "USE", or the compound statement itself confused the Qt bindValue() or Mysql, or both.

Thank you, this is what fixed our problem now... You are a Man among men