Prepared SQL query and bad_alloc



  • Hello everybody.

    I come to you today because I encounter some problems with my application. This app load CSV files into database. I used a thread to separate the GUI with all the work (file reading and parsing and the database interraction). Everything goes right on the test. But issues appear when we used the app with very big files (e.g. more than 2 Go files - about 10 milion lines and 50 columns CSV file). On Windows, the app crashed after a while indicating wrong argument pass to a runtime C function. I couln'd find where the problem were coming from.

    I split the file into different pieces, I the importation in DB worked well. But the thing is I didn't notice any memory leak during the importation of the very big file. The memory used by the process was stabled around 40 M according to the Windows Task Manager.

    I try the same on Linux Ubuntu, and it crashed also. But I get a further error message :
    terminate called after throwing an instance of 'std::bad_alloc'
    And the debugger did stop at the line where I bind a value into the QDatabaseQuery object.

    So the error occured during a prepared query (INSERT INTO) with about 50 columns/parameters inseted, and after a while, so maybe after around 8 milion executions. So my question is here: does the QDatabaseQuery stores somewhere all the bindvalue? Because, I can't see any other explanation after working a week on my code...

    PS: The DBMS used is PostgreSQL



  • Without knowing about the internal workings of QDatabaseQuery: You could try running valgrind or DrMemory to find leaks.


  • Lifetime Qt Champion

    Hi,

    Just a few questions that might help:

    are you on 32 or 64 bit ?

    are you reading the file sequentially or all at once ?

    Can you share the importation code ?



  • I'm on a 32 bits computer.
    And no the file is read line by line (otherwise, it will be a real problem for memory usage ;) )

    The way I'm going is the following (I can't really share the code since it is for a company) :
    I read a line in the file into a QString.
    I parse this QString and split it into a QStringList (The first line is used to build the create table and the insert into query with the columns header names).
    And then, I use this QStringList to bindValue into the prepared request.

    I made sure The QString and the QStringList have been cleared before reading a new line from the file.

    I'll look further with the memory analyser though I've never used a such tool. And i'll come back to you then.



  • I forget to tell that if I just read and parse the whole file without inserting into the DB (commenting all the SQL queries), the program run fine.
    So that's why it seams to come during the databse importation.


  • Lifetime Qt Champion

    Are you batching the inserts ? Or are you preparing the query with all the content from the file and only then execute it ?



  • I'm reading a line from the file, parse it, bind the values, and execute the query.
    Then I read the next line from the file, parse it, bind the values, and execute the query.
    etc.
    etc.


  • Lifetime Qt Champion

    Sounds right, can you show your code ?

    By the way, what version of Qt/OS are you using ?



  • Ok, I created a smal test that I run with Qt 5.2 under Windows 7. And I effectively notice the memory used by the program is constantly increasing until the work finish. Here is the code for the test.

    main.cpp
    @
    #include <QApplication>
    #include "Window.hpp"

    int main(int argc, char *argv[])
    {
    // Configuration de l'application.
    QApplication app(argc, argv);

    // Création du contrôleur principal.
    Window* window(new Window);
    window->show();
    
    return app.exec&#40;&#41;;
    

    }
    @

    Window.hpp
    @
    #include <QPushButton>

    class Window : public QWidget
    {
    Q_OBJECT

    private:
        QPushButton* button;
        
    public:
        Window(&#41;;
        
    public slots:
        void start(&#41;;
    

    };
    @

    Window.cpp
    @
    #include "Window.hpp"

    #include <QMessageBox>
    #include <QSqlDatabase>
    #include <QSqlQuery>
    #include <QVariant>
    #include <QVBoxLayout>

    Window::Window() :
    QWidget(),
    button(new QPushButton("Start"))
    {
    QVBoxLayout* layout(new QVBoxLayout);
    layout->addWidget(button);
    setLayout(layout);

    setMinimumSize(400, 200);
    
    connect(button, &QPushButton::clicked, this, &Window::start);
    

    }

    void Window::start()
    {
    // Database connection configuration...
    QSqlDatabase db(QSqlDatabase::addDatabase("QPSQL"));
    db.setHostName("");
    db.setPort(5432);
    db.setDatabaseName("
    ");
    db.setUserName("");
    db.setPassword("
    ");

    // Test configuration...
    int nbColumn(30);
    int nbRow(1000000);
    
    if (db.open())
    {
        // Queries preparation...
        QString requestCreate("CREATE TABLE test (id INTEGER, ");
        QString requestInsert("INSERT INTO test (id");
        QString requestValues(") VALUES (?");
        for (int col(0); col < nbColumn; ++col)
        {
            requestCreate += "column" + QString::number(col) + " varchar(100), ";
            requestInsert += ", column" + QString::number(col);
            requestValues += ", ?";
        }
        requestCreate += "CONSTRAINT pk_test PRIMARY KEY (id))";
        requestInsert += requestValues + ")";
        
        // Table creation...
        QSqlQuery query(requestCreate, db);
        query.exec&#40;&#41;;
        
        // Data insertion...
        QSqlQuery query2(db&#41;;
        query2.prepare(requestInsert&#41;;
        for (int row(1&#41;; row <= nbRow; ++row)
        {
            query2.bindValue(0, row);// Insert the id
            for (int col(1); col <= nbColumn; ++col)
            {
                query2.bindValue(col, "Data from the row " + QString::number(row) + " and column " + QString::number(col));
            }
            query2.exec(&#41;;
        }
        
        // Close the connection.
        db.close(&#41;;
    }
    

    }
    @

    If some can test and see if the memory usage grows up too. If it does, it means apparently, there is something stored all along in QSqlQuery (maybe QSlqDatabase ?) which is released when the object is destroyed.


  • Lifetime Qt Champion

    I don't have a PostgreSQL server at hand. Can you do the test with sqlite ?



  • I can try tomorow with MySQL. Can SQLite manager such big files? If it does, I can try tomorow too.
    It could come from the Postgres driver? I'll also try on Ubuntu to see if I get the same issue.


  • Lifetime Qt Champion

    AFAIK, it should.

    It can come from several places, first thing to do is determine whether it's specific to PostgreSQL then the rest



  • Ok, so I made few different test on Windows (seven - 32bits - Qt 5.2.1) :
    In the test configuration part of my code, I put nbColumn = 50 and nbRow = 500,000. I get those results:

    Postgres:

    • at the end of the task: around 120,000 K of memory used
    • after exit the start() method: around 18,000 K of memory used

    MySQL:

    • at the end of the task: around 58,000 K of memory used
    • after exit the start() method: around 6,000 K of memory used

    SQLite:

    • at the end of the task: around 120,000 K of memory used
    • after exit the start() method: around 16,000 K of memory used

    I did only 1 test for each because it take too much time. If some other people can to the test on another machine to see if they get the same result, it will be more relevant. I didn't have enough time to test on Ubuntu.



  • I have this same problem. It's difficult to diagnose because one would expect lots of selects, updates, inserts, and deletes to increase memory usage due to caching - but one would not expect the process to crash with an out-of-memory condition when memory fills up. My app is only about 800 lines and I don't call 'malloc' or new at all anywhere in my code. All variables are local stack variables or local static variables. Here's the error message I get:

    terminate called after throwing an instance of 'std::bad_alloc'
    what(): std::bad_alloc
    Aborted

    My system:

    Core i5 2500K processor
    16 GB of RAM
    Linux Mint 64 bit
    Postgresql 9.4.0
    GCC 4.9.2 64 bit
    Qt Creator 3.1.1
    Qt 5.2.1 (GCC 4.6.1, 64 bit)



  • I have this same problem. It's difficult to diagnose because one would expect lots of selects, updates, inserts, and deletes to increase memory usage due to caching - but one would not expect the process to crash with an out-of-memory condition when memory fills up. My app is only about 800 lines and I don't call 'malloc' or new at all anywhere in my code. All variables are local stack variables or local static variables. Here's the error message I get:

    terminate called after throwing an instance of 'std::bad_alloc'
    what(): std::bad_alloc
    Aborted

    My system:

    Core i5 2500K processor
    16 GB of RAM
    Linux Mint 64 bit
    Postgresql 9.4.0
    GCC 4.9.2 64 bit
    Qt Creator 3.1.1
    Qt 5.2.1 (GCC 4.6.1, 64 bit)



  • Upgraded to Qt 5.4.0 and the problem seems to have gone away.



  • Upgraded to Qt 5.4.0 and the problem seems to have gone away.


Log in to reply
 

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