QSqlQuery crashes when inserting large bytea data

[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.