QSqlQuery crashes when inserting large bytea data

[Christian Ehrlicher]

150MB QByteArray will become 600MB char* when inserting into a postgresql database (due to escaping etc) when I remember correctly. Make sure you have that amount of contiguous memory available. Also please post a proper backtrace so we can see if it really crashes inside the QPsql code.

[xyfix]

sorry guys, I was caught up in something else but thanks for replying. I have chopped of the data in small pieces ( ~35 MB ) and tried to upload it via different threads. Only the first file gets uploaded to the database, but when I do this sequentially all of them get uploaded. I'm using std::future and currently only allow 2 thread at the same time. Below I have tried to give you a concise overview of my code, I read files from disks (movieFileList) and put them in a bytearray (base64) and upload them in the database. I can tell you that single upload to the database works because I use is every where in my application but the parallel upload doesn't work. For each thread I create a new connection (db.cpp ) which I close in the thread when I'm done. Can anyone see what I'm doing wrong?

mediafile.cpp

std::vector< QString > movieFileList;
std::vector< std::future > futures;

            for( auto filename : movieFileList )
            {
                        std::future< bool > futureDb = std::async( std::launch::async, [ & ]()->bool
                        {
                            QFile movie( filename );
                            bool result( false );

                            if( !movie.exists())
                                return result;
                            
                            MediaData mediaData;

                            mediaData.m_fileId = fileId; <-- int
                            mediaData.m_typeId = m_colorType; <-- enum
                            mediaData.m_creationtime = QDateTime::currentDateTime();

                            movie.open( QIODevice::ReadOnly );

                            mediaData.m_movie = std::move( movie.readAll().toBase64());
                            result = DAL::getInstance().Media()->addMediaRecord( mediaData );

                            movie.close();
                            Db::db()->database()->close();

                            return result;

                        });

                        futures.push_back( std::move( futureDb ));

                    while( futures.size() > 1 )
                    {
                        for( auto& future : futures )
                        {
                            if( future.wait_for( std::chrono::seconds(0)) == std::future_status::ready )
                            {
                                std::swap( future, futures.back());
                                futures.pop_back();
                                break;
                            }
                        }
                    }
            }
......

db.cpp

static QThreadStorage< Database* > s_databaseConnections;
QAtomicInt connectionCounter(0);
Db* Db::m_instance;
Db* DbSchema::m_db;

void Db::createInstance(Db* db)
{
    m_instance = db;
}

Db::Db(const char* name) :
      m_name(name),
      m_database( new Database( name ) )
{
    if( !s_databaseConnections.hasLocalData() )
         s_databaseConnections.setLocalData( m_database.get() );
}

Db::~Db()
{}

Database* Db::database() const
{

    if( s_databaseConnections.hasLocalData())
    {
        return s_databaseConnections.localData();
    }
    else
    {
        QString connectionName = QString("conn_%1").arg(++connectionCounter);
        QSqlDatabase dbConnection = QSqlDatabase::cloneDatabase(m_database->sqlDb(), connectionName);
        Database* database = new Database( QString::fromStdString( m_name ), dbConnection );
        database->open();
        s_databaseConnections.setLocalData( database );

        return database;
    }
}
......

tblmedia.cpp

int TblMedia::addMediaRecord(const MediaData &file )
{
    std::shared_ptr< SqlQueryStatement > statement = SqlQueryExecutor::execute( "INSERT INTO media ( fileid, typeid, , movie, creationtime ) "
                                     "VALUES (?,?,?,? )", SqlQueryData().add( file.m_fileId ).add( file.m_typeId ).add( file.m_movie ).add( file.m_creationtime ));

    return statement->getResult() ? statement->getLastInsertedId() : -1;
}

[Christian Ehrlicher]

You must not use a QSqlDatabase connection in a thread other than the one you created it in.

[xyfix]

Christian, you're absolutely right and that's why I try to create a connection in the thread that I start. I hoped by including the partial code of db.cpp it would illustrate that. I used the threadstorage class to create and hold the connection data. But what I see is that the future is in the ready status while the upload is not done, any ideas?

[Christian Ehrlicher]

@xyfix I've overseen s_databaseConnections.

You should try to make it work without threads first anyway.

[xyfix]

Yes, I did and that works ( just takes ages to upload :-) ) but eventually everything gets uploaded to the db. I have checked with octet_length whether the blob size in postgresql is correct and it is. I have also check with qDebug statements and saw that new connections are made to the database for each thread started.

[Christian Ehrlicher]

One more thing - cloneDatabse had some issues with the threadsafety. Can you, for testing, create a completely new db connection?

[xyfix]

Ok , I have replaced the cloneDatabase() with this . but no success.

    QString connectionName = QString("conn_%1").arg(++connectionCounter);

    QSqlDatabase dbConnection = QSqlDatabase::addDatabase("QPSQL", connectionName );
    dbConnection.setDatabaseName( m_database->sqlDb().databaseName());
    dbConnection.setHostName( m_database->sqlDb().hostName());
    dbConnection.setPort( m_database->sqlDb().port());
    dbConnection.setUserName( m_database->sqlDb().userName());
    dbConnection.setPassword( m_database->sqlDb().password());

edit: I have added some more qDebug statements to make sure that I'm sending blob data and i have verified that it's sending. But the file does not reach the database. of the 20 files in the for loop only 5 entries reach the database and they are all 0 Kb.

[Christian Ehrlicher]

@xyfix said in QSqlQuery crashes when inserting large bytea data:

DAL::getInstance().Media()->addMediaRecord( mediaData );

Are you sure you use the correct db connection here?

[xyfix]

@Christian-Ehrlicher , after splitting the large file into smaller pieces I don't get any crashes anymore, it's just that the parallel version does not upload any files ( all are 0 Kb) while the sequential version uploads all correctly. I have put a qDebug statement in the function that is called by SqlQueryExecutor::execute() and the database is the newly created one "conn_3", "conn_4".....

std::shared_ptr<SqlQueryStatement> SqlQueryExecutor::execute( const QString& query, const SqlQueryData& sqlData)
{
qDebug() << "execute db " << Db::db()->database()->sqlDb().connectionName();
    std::shared_ptr< SqlQueryStatement > statement( new SqlQueryStatement( Db::db()->database(), query, sqlData ));

    return statement;
}

[Christian Ehrlicher]

@xyfix said in QSqlQuery crashes when inserting large bytea data:

DAL::getInstance().Media()->addMediaRecord( mediaData );

I still don't understand how this function can fetch the thread local database since you don't pass it - how do you access the db in there?

[xyfix]

@Christian-Ehrlicher, I hope this explains a couple of things along with the previous posted code. The Database class has the SqlDatabase object

dal.h

    static DAL& getInstance()
    {
        static DAL instance;
        return instance;
    }

    TblMedia*    Media()  { return m_dbMedia.get(); }

    std::unique_ptr< TblMedia >  m_dbMedia;

dal.cpp

DAL::DAL()
{
    m_dbMedia.reset( new TblMedia());
}

tblmedia.h

class TblExaminationMedia
{
public:

    explicit TblMedia();

    int addMediaRecord( const MediaData& file );
}

tblmedia.cpp

int TblExaminationMedia::addExaminationMediaRecord(const ExaminationMediaData &file )
{
    std::shared_ptr< SqlQueryStatement > statement = SqlQueryExecutor::execute( "INSERT INTO media ( fileid, typeid, movie, creationtime ) "
                                     "VALUES (?,?,?,? )", SqlQueryData().add( file.m_fileId ).add( file.m_typeId ).add( file.m_movie ).add( file.m_creationtime ));

    return statement->getResult() ? statement->getLastInsertedId() : -1;
}

sqlqueryexecutor.h

    std::shared_ptr< SqlQueryStatement > execute( const QString& query, const SqlQueryData& sqlData = SqlQueryData());

sqlqueryexecutor.cpp

std::shared_ptr<SqlQueryStatement> SqlQueryExecutor::execute( const QString& query, const SqlQueryData& sqlData)
{
    std::shared_ptr< SqlQueryStatement > statement( new SqlQueryStatement( Db::db()->database(), query, sqlData ));

    return statement;
}

[Christian Ehrlicher]

Looks a little bit over-designed but in theory it's fine. I would start using a memory analyzer like e.g. valgrind. Or really use a simple QSqlQuery + bindValue() in the thread just to be sure.

[xyfix]

Ok, keep you updated. I also debugged all the way when qsqlquery.exec() is called and looked at the database connection that is used and it's the right one "conn_3" or "conn_4".... so on connection level it looks ok

[SGaist]

I may have missed it but are you sure you are creating the connections within the thread and not before ?

[Christian Ehrlicher]

@SGaist It should - it's a QThreadLocal storage.

[xyfix]

@SGaist , I have carefully placed QDebug statements within the thread and I see the "conn_%1" connections being used. I just found out that it "crashes" when qsqlquery.exec() is executed but nothing gets printed with lastError(). This only happens when I use threads without the threads it's ok and using the same code.

[SGaist]

Rather than using a counter, I would use the thread ID to build the name, thus would have the added benefit of ensuring that you are not creating the same connection twice.

Same goes for the debug messages, add the main thread and current thread ID just to be sure your code executed in the context you expect.

[xyfix]

I followed the advice of @Christian-Ehrlicher and tried this approach, directly creating a connection in the thread and using qsqlquery in that thread so it doesn't use my code, but this also doesn't upload anything to the db while the connection is OK

           std::vector< std::future< bool > > futures;

            for( auto filename : fileList())
            {
qDebug() << "file " << filename;
                    std::future< bool > futureDb = std::async( std::launch::async, [ this, &filename ]()->bool
                    {
                        QFile movie( filename );
                        bool result( false );

                        if( !movie.exists())
                            return result;                       

                        movie.open( QIODevice::ReadOnly );


                        QString connectionName = QString("conn_%1").arg( int( QThread::currentThreadId()));

                        QSqlDatabase dbConnection = QSqlDatabase::addDatabase("QPSQL", connectionName );
                        dbConnection.setDatabaseName( "MYDB" );
                        dbConnection.setHostName( "localhost" );
                        dbConnection.setPort( 5432 );
                        dbConnection.setUserName( "testuser" );
                        dbConnection.setPassword( "test123" );

                        if( !dbConnection.open())
                            qDebug() << "unable to open db";

                        QString str = QString( "INSERT INTO media ( fileid, typeid, movie, creationtime ) "
                                 "VALUES ( :val1,:val2,:val3,:val4 )");

                        QSqlQuery qry( dbConnection );
                        qry.prepare( str );
                        qry.bindValue( ":val1", 5 );
                        qry.bindValue( ":val2", 2 );
                        qry.bindValue( ":val3", movie.readAll().toBase64());
                        qry.bindValue( ":val4", QDateTime::currentDateTime());

                        qry.exec();

qDebug() << filename << " stored in db result " << result.getError().text();

                        movie.close();
                        dbConnection.close();

                        return result;

                    });

                    futures.push_back( std::move( futureDb ));


                    while( futures.size() > 1 )
                    {
                        for( auto& future : futures )
                        {
                            if( future.wait_for( std::chrono::seconds(0)) == std::future_status::ready )
                            {
                                std::swap( future, futures.back());
                                futures.pop_back();

                                break;
                            }
                        }
                    }

[SGaist]

One thing I would add is the check that open was successful. The fact that the file exists does not mean you can access it.