C++ SQLite throws SQLITE_BUSY error.

apoorv569 · 04-22-2021, 07:47 PM

I have a application that I'm making in C++ using wxWidgets for managing audio samples, and using SQLite for the database. When I insert a couple of files it handles them fine, but if I try to insert a lot of files at once, the database eventually throws SQLITE_BUSY error, and stops inserting anything to database. How do I handle this error and fix it, so the database can handle such load.

This is how my application looks like,
https://i.imgur.com/X7kS93e.png

I add files to by either double clicking items in the left browser panel, or dragging and dropping directories/files on to it from external file browser for example.

This is the insert function that inserts those files to the SQLite database,

Code:

void Database::InsertSample(int favorite, std::string filename,
                            std::string fileExtension, std::string samplePack,
                            std::string type, int channels, int length,
                            int sampleRate, int bitrate, std::string path,
                            int trashed)
{
    try
    {
        rc = sqlite3_open("sample.hive", &m_Database);

        std::string insert = "INSERT INTO SAMPLES (FAVORITE, FILENAME, \
                              EXTENSION, SAMPLEPACK, TYPE, CHANNELS, LENGTH, \
                              SAMPLERATE, BITRATE, PATH, TRASHED) \
                              VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";

        rc = sqlite3_prepare_v2(m_Database, insert.c_str(), insert.size(), &m_Stmt, NULL);

        rc = sqlite3_bind_int(m_Stmt, 1, favorite);
        rc = sqlite3_bind_text(m_Stmt, 2, filename.c_str(), filename.size(), SQLITE_STATIC);
        rc = sqlite3_bind_text(m_Stmt, 3, fileExtension.c_str(), fileExtension.size(), SQLITE_STATIC);
        rc = sqlite3_bind_text(m_Stmt, 4, samplePack.c_str(), samplePack.size(), SQLITE_STATIC);
        rc = sqlite3_bind_text(m_Stmt, 5, type.c_str(), type.size(), SQLITE_STATIC);
        rc = sqlite3_bind_int(m_Stmt, 6, channels);
        rc = sqlite3_bind_int(m_Stmt, 7, length);
        rc = sqlite3_bind_int(m_Stmt, 8, sampleRate);
        rc = sqlite3_bind_int(m_Stmt, 9, bitrate);
        rc = sqlite3_bind_text(m_Stmt, 10, path.c_str(), path.size(), SQLITE_STATIC);
        rc = sqlite3_bind_int(m_Stmt, 11, trashed);

        if (sqlite3_step(m_Stmt) != SQLITE_DONE)
        {
            wxLogWarning("No data inserted.");
        }

        rc = sqlite3_finalize(m_Stmt);

        if (rc != SQLITE_OK)
        {
            wxMessageDialog msgDialog(NULL,
                                      "Error! Cannot insert data into table.",
                                      "Error", wxOK | wxICON_ERROR);
            msgDialog.ShowModal();
            sqlite3_free(m_ErrMsg);
        }
        else
        {
            wxLogInfo("Data inserted successfully. %s", m_ErrMsg);
        }

        if (rc == SQLITE_BUSY)
            wxLogDebug("SQLITE_BUSY");
        if (rc == SQLITE_ABORT)
            wxLogDebug("SQLITE_ABORT");
        if (rc == SQLITE_NOMEM)
            wxLogDebug("SQLITE_NOMEM");
        if (rc == SQLITE_LOCKED)
            wxLogDebug("SQLITE_LOCKED");
        if (rc == SQLITE_IOERR)
            wxLogDebug("SQLITE_IOERR");
        if (rc == SQLITE_CORRUPT)
            wxLogDebug("SQLITE_CORRUPT");
        if (rc == SQLITE_READONLY)
            wxLogDebug("SQLITE_READONLY");
        if (rc == SQLITE_ERROR)
            wxLogDebug("SQLITE_ERROR");
        if (rc == SQLITE_PERM)
            wxLogDebug("SQLITE_PERM");
        if (rc == SQLITE_INTERNAL)
            wxLogDebug("SQLITE_INTERNAL");

        sqlite3_close(m_Database);
    }
    catch (const std::exception &exception)
    {
        wxLogDebug(exception.what());
    }
}

Those if (rc == SQLITE_BUSY) and all checks below it I added after I encountered the problem with items not getting inserted in the database, to see what is happening, that's when I discovered it throws SQLITE_BUSY.

Mill J · 04-22-2021, 10:41 PM

I've used SQLite with wxWidgets and never had a problem with SQLITE_BUSY until I started multithreading. It was an easy fix, basically set up a mutex to block the next call until the previous one unlocks the mutex again.

If multithreading isn't your problem. SQLite is known for not being the fastest SQL engine. Would it be possible for you to run several statements each time you open the DB? This would eliminate the constantly opening and closing the DB which take time.

apoorv569 · 04-23-2021, 02:12 PM

Quote:

Originally Posted by Mill J

I've used SQLite with wxWidgets and never had a problem with SQLITE_BUSY until I started multithreading. It was an easy fix, basically set up a mutex to block the next call until the previous one unlocks the mutex again.

If multithreading isn't your problem. SQLite is known for not being the fastest SQL engine. Would it be possible for you to run several statements each time you open the DB? This would eliminate the constantly opening and closing the DB which take time.

No I haven't explicitly made any part of my application multi-threaded, Though for my every update, delete, or select sql query function, I am opening and closing the database. Could that be the issue, for example as you can see in the insert function I shared in my original post there is favorite column, I have a function to get the value of that column like this,

Code:

int Database::GetFavoriteColumnValueByFilename(std::string filename)
{
    int value = 0;

    try
    {
        rc = sqlite3_open("sample.hive", &m_Database);

        std::string select = "SELECT FAVORITE FROM SAMPLES WHERE FILENAME = ?;";

        rc = sqlite3_prepare_v2(m_Database, select.c_str(), select.size(), &m_Stmt, NULL);

        rc = sqlite3_bind_text(m_Stmt, 1, filename.c_str(), filename.size(), SQLITE_STATIC);

        if (sqlite3_step(m_Stmt) == SQLITE_ROW)
        {
            wxLogInfo("Record found, fetching..");
            value = sqlite3_column_int(m_Stmt, 0);
        }

        rc = sqlite3_finalize(m_Stmt);

        if (rc != SQLITE_OK)
        {
            wxMessageDialog msgDialog(NULL, "Error! Cannot get favorite column value from table.",
                                      "Error", wxOK | wxICON_ERROR);
            msgDialog.ShowModal();
            sqlite3_free(m_ErrMsg);
        }
        else
        {
            wxLogInfo("Selected data from table successfully.");
        }

        sqlite3_close(m_Database);
    }
    catch (const std::exception &exception)
    {
        wxLogDebug(exception.what());
    }

    return value;
}

Every other function does open and close the database, but when inserting items to it, the application is in disabled state, so I don't think any other function can be called at that time.

Mill J · 04-23-2021, 07:51 PM

Some helpful info from https://sqlite.org/rescode.html#busy

Quote:

Originally Posted by https://sqlite.org/rescode.html

The SQLITE_BUSY result code indicates that the database file could not be written (or in some cases read) because of concurrent activity by some other database connection, usually a database connection in a separate process.

For example, if process A is in the middle of a large write transaction and at the same time process B attempts to start a new write transaction, process B will get back an SQLITE_BUSY result because SQLite only supports one writer at a time. Process B will need to wait for process A to finish its transaction before starting a new transaction. The sqlite3_busy_timeout() and sqlite3_busy_handler() interfaces and the busy_timeout pragma are available to process B to help it deal with SQLITE_BUSY errors.

An SQLITE_BUSY error can occur at any point in a transaction: when the transaction is first started, during any write or update operations, or when the transaction commits. To avoid encountering SQLITE_BUSY errors in the middle of a transaction, the application can use BEGIN IMMEDIATE instead of just BEGIN to start a transaction. The BEGIN IMMEDIATE command might itself return SQLITE_BUSY, but if it succeeds, then SQLite guarantees that no subsequent operations on the same database through the next COMMIT will return SQLITE_BUSY.

See also: SQLITE_BUSY_RECOVERY and SQLITE_BUSY_SNAPSHOT.

The SQLITE_BUSY result code differs from SQLITE_LOCKED in that SQLITE_BUSY indicates a conflict with a separate database connection, probably in a separate process, whereas SQLITE_LOCKED indicates a conflict within the same database connection (or sometimes a database connection with a shared cache).

Maybe you could further debug where you are getting the error by adding a private temp bool variable that would be set true before opening the DB and false after closing the DB. Each function that opens the database could then check to see if the bool variable is true and display an alert. Once you find out where the concurrent writes are coming from, it should be easy to fix.

apoorv569 · 04-24-2021, 09:40 AM

Quote:

Originally Posted by Mill J

Some helpful info from https://sqlite.org/rescode.html#busy

Maybe you could further debug where you are getting the error by adding a private temp bool variable that would be set true before opening the DB and false after closing the DB. Each function that opens the database could then check to see if the bool variable is true and display an alert. Once you find out where the concurrent writes are coming from, it should be easy to fix.

I changed some things, first I moved the creation of database and table to a separate method rather than being in the constructor, as the app was for no reason trying to re create the table when its really not necessary, I have IF NOT EXISTS statement but still why do something that is not needed, if I just want to access the methods from the database class, I don't need to re create stuff. Now the constructor is just this,

Code:

Database::Database(wxInfoBar& infoBar)
    : m_InfoBar(infoBar)
{

}

And I created this method to create the database and table,

Code:

void Database::CreateDatabase()
{
    bool IsOK = true;
    /* Create SQL statement */
    std::string sample = "CREATE TABLE IF NOT EXISTS SAMPLES("
        "FAVORITE       INT     NOT NULL,"
        "FILENAME       TEXT    NOT NULL,"
        "EXTENSION      TEXT    NOT NULL,"
        "SAMPLEPACK     TEXT    NOT NULL,"
        "TYPE           TEXT    NOT NULL,"
        "CHANNELS       INT     NOT NULL,"
        "LENGTH         INT     NOT NULL,"
        "SAMPLERATE     INT     NOT NULL,"
        "BITRATE        INT     NOT NULL,"
        "PATH           TEXT    NOT NULL,"
        "TRASHED        INT     NOT NULL);";

    try
    {
        if (sqlite3_open("sample.hive", &m_Database) != SQLITE_OK && IsOK)
        {
            wxLogDebug("Error opening DB");
            throw sqlite3_errmsg(m_Database);
        }
        else
        {
            IsOK = true;
            wxLogDebug("Opening DB..");
        }

        rc = sqlite3_exec(m_Database, sample.c_str(), NULL, 0, &m_ErrMsg);

        if (rc != SQLITE_OK)
        {
            wxMessageDialog msgDialog(NULL, "Error! Cannot create table.",
                                      "Error", wxOK | wxICON_ERROR);
            msgDialog.ShowModal();
            sqlite3_free(m_ErrMsg);
        }
        else
        {
            wxLogDebug("Table created successfully.");
        }

        rc = sqlite3_close(m_Database);
        IsOK = false;

        if (rc == SQLITE_OK)
            wxLogDebug("DB Closed..");
        else
            wxLogDebug("Error! Cannot close DB, Error code: %d, Error message: %s", rc, m_ErrMsg);
    }
    catch (const std::exception &exception)
    {
        wxLogDebug(exception.what());
    }
}

When I open the app, it prints this,

Code:

20:12:22: Debug: Opening DB..
20:12:22: Debug: Table created successfully.
20:12:22: Debug: DB Closed..

So the database opens and closes successfully..

Then in my main GUI class I added this (after defining all the GUI elements),

Code:

    // Initialize the database
    Database db(*m_InfoBar);
    db.CreateDatabase();

And the insert method is same, but I added few things to see if I can get any information,

Code:

void Database::InsertSample(int favorite, std::string filename,
                            std::string fileExtension, std::string samplePack,
                            std::string type, int channels, int length,
                            int sampleRate, int bitrate, std::string path,
                            int trashed)
{
    bool IsOK = true;
    try
    {
        // rc = sqlite3_open("sample.hive", &m_Database);
        if (sqlite3_open("sample.hive", &m_Database) != SQLITE_OK && IsOK)
        {
            wxLogDebug("Error opening DB");
            throw sqlite3_errmsg(m_Database);
        }
        else
        {
            IsOK = true;
            wxLogDebug("Opening DB..");
        }

        std::string insert = "INSERT INTO SAMPLES (FAVORITE, FILENAME, \
                              EXTENSION, SAMPLEPACK, TYPE, CHANNELS, LENGTH, \
                              SAMPLERATE, BITRATE, PATH, TRASHED) \
                              VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";

        rc = sqlite3_prepare_v2(m_Database, insert.c_str(), insert.size(), &m_Stmt, NULL);
        if (rc != SQLITE_OK)
            wxLogDebug("Cannot prepare sql statement..");

        rc = sqlite3_bind_int(m_Stmt, 1, favorite);
        rc = sqlite3_bind_text(m_Stmt, 2, filename.c_str(), filename.size(), SQLITE_STATIC);
        rc = sqlite3_bind_text(m_Stmt, 3, fileExtension.c_str(), fileExtension.size(), SQLITE_STATIC);
        rc = sqlite3_bind_text(m_Stmt, 4, samplePack.c_str(), samplePack.size(), SQLITE_STATIC);
        rc = sqlite3_bind_text(m_Stmt, 5, type.c_str(), type.size(), SQLITE_STATIC);
        rc = sqlite3_bind_int(m_Stmt, 6, channels);
        rc = sqlite3_bind_int(m_Stmt, 7, length);
        rc = sqlite3_bind_int(m_Stmt, 8, sampleRate);
        rc = sqlite3_bind_int(m_Stmt, 9, bitrate);
        rc = sqlite3_bind_text(m_Stmt, 10, path.c_str(), path.size(), SQLITE_STATIC);
        rc = sqlite3_bind_int(m_Stmt, 11, trashed);

        if (sqlite3_step(m_Stmt) != SQLITE_DONE)
        {
            wxLogDebug("No data inserted. Error code: %d", sqlite3_step(m_Stmt));
        }

        rc = sqlite3_finalize(m_Stmt);

        if (rc != SQLITE_OK)
        {
            // wxMessageDialog msgDialog(NULL,
            //                           "Error! Cannot insert data into table.",
            //                           "Error", wxOK | wxICON_ERROR);
            // msgDialog.ShowModal();
            wxLogDebug("Error! Cannot insert data into table. Error code: %d, Error Message: %s", rc, m_ErrMsg);
            sqlite3_free(m_ErrMsg);
        }
        else
        {
            wxLogDebug("Data inserted successfully. %s", m_ErrMsg);
        }

        if (rc == SQLITE_BUSY)
            wxLogDebug("SQLITE_BUSY");
        if (rc == SQLITE_ABORT)
            wxLogDebug("SQLITE_ABORT");
        if (rc == SQLITE_NOMEM)
            wxLogDebug("SQLITE_NOMEM");
        if (rc == SQLITE_LOCKED)
            wxLogDebug("SQLITE_LOCKED");
        if (rc == SQLITE_IOERR)
            wxLogDebug("SQLITE_IOERR");
        if (rc == SQLITE_CORRUPT)
            wxLogDebug("SQLITE_CORRUPT");
        if (rc == SQLITE_READONLY)
            wxLogDebug("SQLITE_READONLY");
        if (rc == SQLITE_ERROR)
            wxLogDebug("SQLITE_ERROR");
        if (rc == SQLITE_PERM)
            wxLogDebug("SQLITE_PERM");
        if (rc == SQLITE_INTERNAL)
            wxLogDebug("SQLITE_INTERNAL");

        sqlite3_close(m_Database);
        IsOK = false;
    }
    catch (const std::exception &exception)
    {
        wxLogDebug(exception.what());
    }
}

Now when adding files it prints this,

Code:

20:06:13: Debug: Adding file: bass01.ogg :: Extension: ogg
20:06:13: Debug: Opening DB..
zsh: segmentation fault (core dumped)  ./SampleHive

I tried this again,

Code:

20:07:19: Debug: Adding file: bass01.ogg :: Extension: ogg
20:07:19: Debug: Adding file: bass_acid01.ogg :: Extension: ogg
20:07:19: Debug: Opening DB..
20:07:19: Debug: No data inserted. Error code: 5
zsh: segmentation fault (core dumped)  ./SampleHive

And this was the output, looks like it adds a file, then tries to open the database, it does not open the database before adding file...

And I looked in the sqlite3.h file error code 5 is SQLITE_BUSY

Code:

#define SQLITE_ERROR        1   /* Generic error */
#define SQLITE_INTERNAL     2   /* Internal logic error in SQLite */
#define SQLITE_PERM         3   /* Access permission denied */
#define SQLITE_ABORT        4   /* Callback routine requested an abort */
#define SQLITE_BUSY         5   /* The database file is locked */                   //  <--------------
#define SQLITE_LOCKED       6   /* A table in the database is locked */
#define SQLITE_NOMEM        7   /* A malloc() failed */
#define SQLITE_READONLY     8   /* Attempt to write a readonly database */

Mill J · 04-24-2021, 10:57 AM

Not sure where you introduced the segfault, you might need to run through gdb/ddd to figure out which line crashes or revert to a prevoius working state. However this:

Code:

 
20:07:19: Debug: Adding file: bass01.ogg :: Extension: ogg
20:07:19: Debug: Adding file: bass_acid01.ogg :: Extension: ogg
20:07:19: Debug: Opening DB..

Confirms that this function gets concurrent calls. Setting up a mutex will likely be needed. A simple while loop for testing this theory could be:

Code:

while(isOK) {
   wxLogDebug("Waiting On Previous Transaction");
}

isOK = true;

//Open Database.. 

//Do Stuff

//Close Database..

isOK = false;

Needless to say the isOK needs to be defined as bool isOK = false; to avoid a forever loop. Of course a std::mutex would be ideal for production code since the while loop is not very power efficiant

apoorv569 · 04-24-2021, 11:54 AM

Quote:

Originally Posted by Mill J

Not sure where you introduced the segfault, you might need to run through gdb/ddd to figure out which line crashes or revert to a prevoius working state. However this:

Code:

 
20:07:19: Debug: Adding file: bass01.ogg :: Extension: ogg
20:07:19: Debug: Adding file: bass_acid01.ogg :: Extension: ogg
20:07:19: Debug: Opening DB..

Confirms that this function gets concurrent calls. Setting up a mutex will likely be needed. A simple while loop for testing this theory could be:

Code:

while(isOK) {
   wxLogDebug("Waiting On Previous Transaction");
}

isOK = true;

//Open Database.. 

//Do Stuff

//Close Database..

isOK = false;

Needless to say the isOK needs to be defined as bool isOK = false; to avoid a forever loop. Of course a std::mutex would be ideal for production code since the while loop is not very power efficiant

I have never used std::mutex, but looking around internet, I tried something but it is still the same, also tried adding the while loop you recommended, no luck..

Code:

void Database::InsertSample(int favorite, std::string filename,
                            std::string fileExtension, std::string samplePack,
                            std::string type, int channels, int length,
                            int sampleRate, int bitrate, std::string path,
                            int trashed)
{
    bool IsOK = false;

    std::mutex m;
    try
    {
        while (IsOK)
        {
            wxLogDebug("Waiting On Previous Transaction");
        }

        IsOK = true;
        // rc = sqlite3_open("sample.hive", &m_Database);
        m.lock();
        if (sqlite3_open("sample.hive", &m_Database) != SQLITE_OK && IsOK)
        {
            wxLogDebug("Error opening DB");
            throw sqlite3_errmsg(m_Database);
        }
        else
        {
            wxLogDebug("Opening DB..");
        }

        std::string insert = "INSERT INTO SAMPLES (FAVORITE, FILENAME, \
                              EXTENSION, SAMPLEPACK, TYPE, CHANNELS, LENGTH, \
                              SAMPLERATE, BITRATE, PATH, TRASHED) \
                              VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";

        rc = sqlite3_prepare_v2(m_Database, insert.c_str(), insert.size(), &m_Stmt, NULL);
        if (rc != SQLITE_OK)
            wxLogDebug("Cannot prepare sql statement..");

        rc = sqlite3_bind_int(m_Stmt, 1, favorite);
        rc = sqlite3_bind_text(m_Stmt, 2, filename.c_str(), filename.size(), SQLITE_STATIC);
        rc = sqlite3_bind_text(m_Stmt, 3, fileExtension.c_str(), fileExtension.size(), SQLITE_STATIC);
        rc = sqlite3_bind_text(m_Stmt, 4, samplePack.c_str(), samplePack.size(), SQLITE_STATIC);
        rc = sqlite3_bind_text(m_Stmt, 5, type.c_str(), type.size(), SQLITE_STATIC);
        rc = sqlite3_bind_int(m_Stmt, 6, channels);
        rc = sqlite3_bind_int(m_Stmt, 7, length);
        rc = sqlite3_bind_int(m_Stmt, 8, sampleRate);
        rc = sqlite3_bind_int(m_Stmt, 9, bitrate);
        rc = sqlite3_bind_text(m_Stmt, 10, path.c_str(), path.size(), SQLITE_STATIC);
        rc = sqlite3_bind_int(m_Stmt, 11, trashed);

        if (sqlite3_step(m_Stmt) != SQLITE_DONE)
        {
            wxLogDebug("No data inserted. Error code: %d", sqlite3_step(m_Stmt));
        }

        rc = sqlite3_finalize(m_Stmt);

        if (rc != SQLITE_OK)
        {
            // wxMessageDialog msgDialog(NULL,
            //                           "Error! Cannot insert data into table.",
            //                           "Error", wxOK | wxICON_ERROR);
            // msgDialog.ShowModal();
            wxLogDebug("Error! Cannot insert data into table. Error code: %d, Error Message: %s", rc, m_ErrMsg);
            sqlite3_free(m_ErrMsg);
        }
        else
        {
            wxLogDebug("Data inserted successfully. %s", m_ErrMsg);
        }

        if (rc == SQLITE_BUSY)
            wxLogDebug("SQLITE_BUSY");
        if (rc == SQLITE_ABORT)
            wxLogDebug("SQLITE_ABORT");
        if (rc == SQLITE_NOMEM)
            wxLogDebug("SQLITE_NOMEM");
        if (rc == SQLITE_LOCKED)
            wxLogDebug("SQLITE_LOCKED");
        if (rc == SQLITE_IOERR)
            wxLogDebug("SQLITE_IOERR");
        if (rc == SQLITE_CORRUPT)
            wxLogDebug("SQLITE_CORRUPT");
        if (rc == SQLITE_READONLY)
            wxLogDebug("SQLITE_READONLY");
        if (rc == SQLITE_ERROR)
            wxLogDebug("SQLITE_ERROR");
        if (rc == SQLITE_PERM)
            wxLogDebug("SQLITE_PERM");
        if (rc == SQLITE_INTERNAL)
            wxLogDebug("SQLITE_INTERNAL");

        sqlite3_close(m_Database);
        IsOK = false;
        m.unlock();
    }
    catch (const std::exception &exception)
    {
        wxLogDebug(exception.what());
    }
}

The output is still the same,

Code:

22:23:28: Debug: Adding file: bass01.ogg :: Extension: ogg
22:23:28: Debug: Opening DB..
zsh: segmentation fault (core dumped)  ./SampleHive

NevemTeve · 04-24-2021, 01:05 PM

That's what gdb is good for...
https://www.tutorialspoint.com/gnu_d...uick_guide.htm

Mill J · 04-24-2021, 01:06 PM

You will need to declare the mutex or isOK as members in the Database class in order for it to work. Declaring them as you have above make them local to the function and each call to the function will create/delete it's own instead of using a shared one for all calls.

Like I said: I don't know where it segfaults and you will need to fix that before continuing, but somthing like this this is what I had in mind:

Code:

class Database {
   private:
      bool IsOK = true;

};

void Database::InsertSample(int favorite, std::string filename,
                            std::string fileExtension, std::string samplePack,
                            std::string type, int channels, int length,
                            int sampleRate, int bitrate, std::string path,
                            int trashed)
{

    try
    {
        //If DB In Use, Wait 
        while (!IsOK)
        {
            wxLogDebug("Waiting On Previous Transaction");
        }
        
        //Get Lock
        IsOK = false;
    

        // rc = sqlite3_open("sample.hive", &m_Database);
        if (sqlite3_open("sample.hive", &m_Database) != SQLITE_OK)
        {
            wxLogDebug("Error opening DB");
            throw sqlite3_errmsg(m_Database);
        }
        else
        {
            wxLogDebug("Opening DB..");
        }

        std::string insert = "INSERT INTO SAMPLES (FAVORITE, FILENAME, \
                              EXTENSION, SAMPLEPACK, TYPE, CHANNELS, LENGTH, \
                              SAMPLERATE, BITRATE, PATH, TRASHED) \
                              VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";

        rc = sqlite3_prepare_v2(m_Database, insert.c_str(), insert.size(), &m_Stmt, NULL);
        if (rc != SQLITE_OK)
            wxLogDebug("Cannot prepare sql statement..");

        rc = sqlite3_bind_int(m_Stmt, 1, favorite);
        rc = sqlite3_bind_text(m_Stmt, 2, filename.c_str(), filename.size(), SQLITE_STATIC);
        rc = sqlite3_bind_text(m_Stmt, 3, fileExtension.c_str(), fileExtension.size(), SQLITE_STATIC);
        rc = sqlite3_bind_text(m_Stmt, 4, samplePack.c_str(), samplePack.size(), SQLITE_STATIC);
        rc = sqlite3_bind_text(m_Stmt, 5, type.c_str(), type.size(), SQLITE_STATIC);
        rc = sqlite3_bind_int(m_Stmt, 6, channels);
        rc = sqlite3_bind_int(m_Stmt, 7, length);
        rc = sqlite3_bind_int(m_Stmt, 8, sampleRate);
        rc = sqlite3_bind_int(m_Stmt, 9, bitrate);
        rc = sqlite3_bind_text(m_Stmt, 10, path.c_str(), path.size(), SQLITE_STATIC);
        rc = sqlite3_bind_int(m_Stmt, 11, trashed);

        if (sqlite3_step(m_Stmt) != SQLITE_DONE)
        {
            wxLogDebug("No data inserted. Error code: %d", sqlite3_step(m_Stmt));
        }

        rc = sqlite3_finalize(m_Stmt);

        if (rc != SQLITE_OK)
        {
            // wxMessageDialog msgDialog(NULL,
            //                           "Error! Cannot insert data into table.",
            //                           "Error", wxOK | wxICON_ERROR);
            // msgDialog.ShowModal();
            wxLogDebug("Error! Cannot insert data into table. Error code: %d, Error Message: %s", rc, m_ErrMsg);
            sqlite3_free(m_ErrMsg);
        }
        else
        {
            wxLogDebug("Data inserted successfully. %s", m_ErrMsg);
        }

        if (rc == SQLITE_BUSY)
            wxLogDebug("SQLITE_BUSY");
        if (rc == SQLITE_ABORT)
            wxLogDebug("SQLITE_ABORT");
        if (rc == SQLITE_NOMEM)
            wxLogDebug("SQLITE_NOMEM");
        if (rc == SQLITE_LOCKED)
            wxLogDebug("SQLITE_LOCKED");
        if (rc == SQLITE_IOERR)
            wxLogDebug("SQLITE_IOERR");
        if (rc == SQLITE_CORRUPT)
            wxLogDebug("SQLITE_CORRUPT");
        if (rc == SQLITE_READONLY)
            wxLogDebug("SQLITE_READONLY");
        if (rc == SQLITE_ERROR)
            wxLogDebug("SQLITE_ERROR");
        if (rc == SQLITE_PERM)
            wxLogDebug("SQLITE_PERM");
        if (rc == SQLITE_INTERNAL)
            wxLogDebug("SQLITE_INTERNAL");

        sqlite3_close(m_Database);
        IsOK = true;
    }
    catch (const std::exception &exception)
    {
        wxLogDebug(exception.what());
    }
}

And here is how you would do a mutex

Code:

class Database {
   private:
      std::mutex m;

};



void Database::InsertSample(int favorite, std::string filename,
                            std::string fileExtension, std::string samplePack,
                            std::string type, int channels, int length,
                            int sampleRate, int bitrate, std::string path,
                            int trashed)
{

    try
    
        //Get Lock
        m.lock();
    

        // rc = sqlite3_open("sample.hive", &m_Database);
        if (sqlite3_open("sample.hive", &m_Database) != SQLITE_OK)
        {
            wxLogDebug("Error opening DB");
            throw sqlite3_errmsg(m_Database);
        }
        else
        {
            wxLogDebug("Opening DB..");
        }

        std::string insert = "INSERT INTO SAMPLES (FAVORITE, FILENAME, \
                              EXTENSION, SAMPLEPACK, TYPE, CHANNELS, LENGTH, \
                              SAMPLERATE, BITRATE, PATH, TRASHED) \
                              VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";

        rc = sqlite3_prepare_v2(m_Database, insert.c_str(), insert.size(), &m_Stmt, NULL);
        if (rc != SQLITE_OK)
            wxLogDebug("Cannot prepare sql statement..");

        rc = sqlite3_bind_int(m_Stmt, 1, favorite);
        rc = sqlite3_bind_text(m_Stmt, 2, filename.c_str(), filename.size(), SQLITE_STATIC);
        rc = sqlite3_bind_text(m_Stmt, 3, fileExtension.c_str(), fileExtension.size(), SQLITE_STATIC);
        rc = sqlite3_bind_text(m_Stmt, 4, samplePack.c_str(), samplePack.size(), SQLITE_STATIC);
        rc = sqlite3_bind_text(m_Stmt, 5, type.c_str(), type.size(), SQLITE_STATIC);
        rc = sqlite3_bind_int(m_Stmt, 6, channels);
        rc = sqlite3_bind_int(m_Stmt, 7, length);
        rc = sqlite3_bind_int(m_Stmt, 8, sampleRate);
        rc = sqlite3_bind_int(m_Stmt, 9, bitrate);
        rc = sqlite3_bind_text(m_Stmt, 10, path.c_str(), path.size(), SQLITE_STATIC);
        rc = sqlite3_bind_int(m_Stmt, 11, trashed);

        if (sqlite3_step(m_Stmt) != SQLITE_DONE)
        {
            wxLogDebug("No data inserted. Error code: %d", sqlite3_step(m_Stmt));
        }

        rc = sqlite3_finalize(m_Stmt);

        if (rc != SQLITE_OK)
        {
            // wxMessageDialog msgDialog(NULL,
            //                           "Error! Cannot insert data into table.",
            //                           "Error", wxOK | wxICON_ERROR);
            // msgDialog.ShowModal();
            wxLogDebug("Error! Cannot insert data into table. Error code: %d, Error Message: %s", rc, m_ErrMsg);
            sqlite3_free(m_ErrMsg);
        }
        else
        {
            wxLogDebug("Data inserted successfully. %s", m_ErrMsg);
        }

        if (rc == SQLITE_BUSY)
            wxLogDebug("SQLITE_BUSY");
        if (rc == SQLITE_ABORT)
            wxLogDebug("SQLITE_ABORT");
        if (rc == SQLITE_NOMEM)
            wxLogDebug("SQLITE_NOMEM");
        if (rc == SQLITE_LOCKED)
            wxLogDebug("SQLITE_LOCKED");
        if (rc == SQLITE_IOERR)
            wxLogDebug("SQLITE_IOERR");
        if (rc == SQLITE_CORRUPT)
            wxLogDebug("SQLITE_CORRUPT");
        if (rc == SQLITE_READONLY)
            wxLogDebug("SQLITE_READONLY");
        if (rc == SQLITE_ERROR)
            wxLogDebug("SQLITE_ERROR");
        if (rc == SQLITE_PERM)
            wxLogDebug("SQLITE_PERM");
        if (rc == SQLITE_INTERNAL)
            wxLogDebug("SQLITE_INTERNAL");

        sqlite3_close(m_Database);
        
        //Unlock
        m.unlock();
    }
    catch (const std::exception &exception)
    {
        wxLogDebug(exception.what());
        m.unlock();
    }
}

Is this code on github, sourceforge, etc by any chance? Or rather will it be open-souce or a personal project?

apoorv569 · 04-24-2021, 03:08 PM

Quote:

Originally Posted by Mill J

You will need to declare the mutex or isOK as members in the Database class in order for it to work. Declaring them as you have above make them local to the function and each call to the function will create/delete it's own instead of using a shared one for all calls.

Like I said: I don't know where it segfaults and you will need to fix that before continuing, but somthing like this this is what I had in mind:

Code:

class Database {
   private:
      bool IsOK = true;

};

void Database::InsertSample(int favorite, std::string filename,
                            std::string fileExtension, std::string samplePack,
                            std::string type, int channels, int length,
                            int sampleRate, int bitrate, std::string path,
                            int trashed)
{

    try
    {
        //If DB In Use, Wait 
        while (!IsOK)
        {
            wxLogDebug("Waiting On Previous Transaction");
        }
        
        //Get Lock
        IsOK = false;
    

        // rc = sqlite3_open("sample.hive", &m_Database);
        if (sqlite3_open("sample.hive", &m_Database) != SQLITE_OK)
        {
            wxLogDebug("Error opening DB");
            throw sqlite3_errmsg(m_Database);
        }
        else
        {
            wxLogDebug("Opening DB..");
        }

        std::string insert = "INSERT INTO SAMPLES (FAVORITE, FILENAME, \
                              EXTENSION, SAMPLEPACK, TYPE, CHANNELS, LENGTH, \
                              SAMPLERATE, BITRATE, PATH, TRASHED) \
                              VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";

        rc = sqlite3_prepare_v2(m_Database, insert.c_str(), insert.size(), &m_Stmt, NULL);
        if (rc != SQLITE_OK)
            wxLogDebug("Cannot prepare sql statement..");

        rc = sqlite3_bind_int(m_Stmt, 1, favorite);
        rc = sqlite3_bind_text(m_Stmt, 2, filename.c_str(), filename.size(), SQLITE_STATIC);
        rc = sqlite3_bind_text(m_Stmt, 3, fileExtension.c_str(), fileExtension.size(), SQLITE_STATIC);
        rc = sqlite3_bind_text(m_Stmt, 4, samplePack.c_str(), samplePack.size(), SQLITE_STATIC);
        rc = sqlite3_bind_text(m_Stmt, 5, type.c_str(), type.size(), SQLITE_STATIC);
        rc = sqlite3_bind_int(m_Stmt, 6, channels);
        rc = sqlite3_bind_int(m_Stmt, 7, length);
        rc = sqlite3_bind_int(m_Stmt, 8, sampleRate);
        rc = sqlite3_bind_int(m_Stmt, 9, bitrate);
        rc = sqlite3_bind_text(m_Stmt, 10, path.c_str(), path.size(), SQLITE_STATIC);
        rc = sqlite3_bind_int(m_Stmt, 11, trashed);

        if (sqlite3_step(m_Stmt) != SQLITE_DONE)
        {
            wxLogDebug("No data inserted. Error code: %d", sqlite3_step(m_Stmt));
        }

        rc = sqlite3_finalize(m_Stmt);

        if (rc != SQLITE_OK)
        {
            // wxMessageDialog msgDialog(NULL,
            //                           "Error! Cannot insert data into table.",
            //                           "Error", wxOK | wxICON_ERROR);
            // msgDialog.ShowModal();
            wxLogDebug("Error! Cannot insert data into table. Error code: %d, Error Message: %s", rc, m_ErrMsg);
            sqlite3_free(m_ErrMsg);
        }
        else
        {
            wxLogDebug("Data inserted successfully. %s", m_ErrMsg);
        }

        if (rc == SQLITE_BUSY)
            wxLogDebug("SQLITE_BUSY");
        if (rc == SQLITE_ABORT)
            wxLogDebug("SQLITE_ABORT");
        if (rc == SQLITE_NOMEM)
            wxLogDebug("SQLITE_NOMEM");
        if (rc == SQLITE_LOCKED)
            wxLogDebug("SQLITE_LOCKED");
        if (rc == SQLITE_IOERR)
            wxLogDebug("SQLITE_IOERR");
        if (rc == SQLITE_CORRUPT)
            wxLogDebug("SQLITE_CORRUPT");
        if (rc == SQLITE_READONLY)
            wxLogDebug("SQLITE_READONLY");
        if (rc == SQLITE_ERROR)
            wxLogDebug("SQLITE_ERROR");
        if (rc == SQLITE_PERM)
            wxLogDebug("SQLITE_PERM");
        if (rc == SQLITE_INTERNAL)
            wxLogDebug("SQLITE_INTERNAL");

        sqlite3_close(m_Database);
        IsOK = true;
    }
    catch (const std::exception &exception)
    {
        wxLogDebug(exception.what());
    }
}

And here is how you would do a mutex

Code:

class Database {
   private:
      std::mutex m;

};



void Database::InsertSample(int favorite, std::string filename,
                            std::string fileExtension, std::string samplePack,
                            std::string type, int channels, int length,
                            int sampleRate, int bitrate, std::string path,
                            int trashed)
{

    try
    
        //Get Lock
        m.lock();
    

        // rc = sqlite3_open("sample.hive", &m_Database);
        if (sqlite3_open("sample.hive", &m_Database) != SQLITE_OK)
        {
            wxLogDebug("Error opening DB");
            throw sqlite3_errmsg(m_Database);
        }
        else
        {
            wxLogDebug("Opening DB..");
        }

        std::string insert = "INSERT INTO SAMPLES (FAVORITE, FILENAME, \
                              EXTENSION, SAMPLEPACK, TYPE, CHANNELS, LENGTH, \
                              SAMPLERATE, BITRATE, PATH, TRASHED) \
                              VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";

        rc = sqlite3_prepare_v2(m_Database, insert.c_str(), insert.size(), &m_Stmt, NULL);
        if (rc != SQLITE_OK)
            wxLogDebug("Cannot prepare sql statement..");

        rc = sqlite3_bind_int(m_Stmt, 1, favorite);
        rc = sqlite3_bind_text(m_Stmt, 2, filename.c_str(), filename.size(), SQLITE_STATIC);
        rc = sqlite3_bind_text(m_Stmt, 3, fileExtension.c_str(), fileExtension.size(), SQLITE_STATIC);
        rc = sqlite3_bind_text(m_Stmt, 4, samplePack.c_str(), samplePack.size(), SQLITE_STATIC);
        rc = sqlite3_bind_text(m_Stmt, 5, type.c_str(), type.size(), SQLITE_STATIC);
        rc = sqlite3_bind_int(m_Stmt, 6, channels);
        rc = sqlite3_bind_int(m_Stmt, 7, length);
        rc = sqlite3_bind_int(m_Stmt, 8, sampleRate);
        rc = sqlite3_bind_int(m_Stmt, 9, bitrate);
        rc = sqlite3_bind_text(m_Stmt, 10, path.c_str(), path.size(), SQLITE_STATIC);
        rc = sqlite3_bind_int(m_Stmt, 11, trashed);

        if (sqlite3_step(m_Stmt) != SQLITE_DONE)
        {
            wxLogDebug("No data inserted. Error code: %d", sqlite3_step(m_Stmt));
        }

        rc = sqlite3_finalize(m_Stmt);

        if (rc != SQLITE_OK)
        {
            // wxMessageDialog msgDialog(NULL,
            //                           "Error! Cannot insert data into table.",
            //                           "Error", wxOK | wxICON_ERROR);
            // msgDialog.ShowModal();
            wxLogDebug("Error! Cannot insert data into table. Error code: %d, Error Message: %s", rc, m_ErrMsg);
            sqlite3_free(m_ErrMsg);
        }
        else
        {
            wxLogDebug("Data inserted successfully. %s", m_ErrMsg);
        }

        if (rc == SQLITE_BUSY)
            wxLogDebug("SQLITE_BUSY");
        if (rc == SQLITE_ABORT)
            wxLogDebug("SQLITE_ABORT");
        if (rc == SQLITE_NOMEM)
            wxLogDebug("SQLITE_NOMEM");
        if (rc == SQLITE_LOCKED)
            wxLogDebug("SQLITE_LOCKED");
        if (rc == SQLITE_IOERR)
            wxLogDebug("SQLITE_IOERR");
        if (rc == SQLITE_CORRUPT)
            wxLogDebug("SQLITE_CORRUPT");
        if (rc == SQLITE_READONLY)
            wxLogDebug("SQLITE_READONLY");
        if (rc == SQLITE_ERROR)
            wxLogDebug("SQLITE_ERROR");
        if (rc == SQLITE_PERM)
            wxLogDebug("SQLITE_PERM");
        if (rc == SQLITE_INTERNAL)
            wxLogDebug("SQLITE_INTERNAL");

        sqlite3_close(m_Database);
        
        //Unlock
        m.unlock();
    }
    catch (const std::exception &exception)
    {
        wxLogDebug(exception.what());
        m.unlock();
    }
}

Is this code on github, sourceforge, etc by any chance? Or rather will it be open-souce or a personal project?

I tried both while loop and mutex, made them member variables as you said, the while loop, continuously printed "Waiting on previous transaction", I waited for 10-15 seconds but it didn't stop, and the mutex also didn't help, but I got a new error

Code:

01:32:19: Debug: Adding file: bass01.ogg :: Extension: ogg
01:32:19: Debug: Adding file: bass_acid01.ogg :: Extension: ogg
01:32:19: Debug: Opening DB..
01:32:19: Debug: No data inserted. Error code: 5
01:32:19: Debug: Error! Cannot insert data into table. Error code: 5, Error Message: (null)
free(): invalid pointer
zsh: abort (core dumped)  ./SampleHive

The segfault started occuring when I moved the database creation from constructor to separate method, before it just threw SQLITE_BUSY but the app didn't crash.

Yes the project is FOSS and available on Gitlab, but the changes I'm doing right now are not included in the latest commit, here is the link to the project - https://gitlab.com/apoorv569/sample-hive.

Mill J · 04-24-2021, 04:56 PM

Quote:

Originally Posted by apoorv569

I tried both while loop and mutex, made them member variables as you said, the while loop, continuously printed "Waiting on previous transaction", I waited for 10-15 seconds but it didn't stop, and the mutex also didn't help, but I got a new error

Code:

01:32:19: Debug: Adding file: bass01.ogg :: Extension: ogg
01:32:19: Debug: Adding file: bass_acid01.ogg :: Extension: ogg
01:32:19: Debug: Opening DB..
01:32:19: Debug: No data inserted. Error code: 5
01:32:19: Debug: Error! Cannot insert data into table. Error code: 5, Error Message: (null)
free(): invalid pointer
zsh: abort (core dumped)  ./SampleHive

The segfault started occuring when I moved the database creation from constructor to separate method, before it just threw SQLITE_BUSY but the app didn't crash.

Yes the project is FOSS and available on Gitlab, but the changes I'm doing right now are not included in the latest commit, here is the link to the project - https://gitlab.com/apoorv569/sample-hive.

Neat app. Got it cloned and built on Mint 19.3, Just a note: had to build wxSVG from source since it's not in the Mint repos. I can repoduce the issue you are trying to fix. Maybe you could create and push a branch with your latest code to gitlab that could be used to collaborate on this issue? I don't have a gitlab acount but I should be able to make one. I've just never had a reason to do so before.

apoorv569 · 04-24-2021, 05:39 PM

Quote:

Originally Posted by Mill J

Neat app. Got it cloned and built on Mint 19.3, Just a note: had to build wxSVG from source since it's not in the Mint repos. I can repoduce the issue you are trying to fix. Maybe you could create and push a branch with your latest code to gitlab that could be used to collaborate on this issue? I don't have a gitlab acount but I should be able to make one. I've just never had a reason to do so before.

Thanks. I see, I will add a note in README about Mint needing wxSVG to compile from source. I only have one branch ATM, which is called testing, so I don't really have any problem pushing to this branch only. I have pushed the latest commit, it does not have much just a few changes mostly what I shared here in this thread. Appreciate you helping me.