LinuxQuestions.org
Help answer threads with 0 replies.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 04-22-2021, 07:47 PM   #1
apoorv569
Member
 
Registered: Jun 2020
Distribution: Arch Linux
Posts: 60

Rep: Reputation: 0
Question C++ SQLite throws SQLITE_BUSY error.


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.
 
Old 04-22-2021, 10:41 PM   #2
Mill J
Senior Member
 
Registered: Feb 2017
Location: @127.0.0.1
Distribution: Mint, Void, MX, Haiku, PMOS, Plasma Mobile, and many others
Posts: 1,215
Blog Entries: 2

Rep: Reputation: 513Reputation: 513Reputation: 513Reputation: 513Reputation: 513Reputation: 513
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.

 
Old 04-23-2021, 02:12 PM   #3
apoorv569
Member
 
Registered: Jun 2020
Distribution: Arch Linux
Posts: 60

Original Poster
Rep: Reputation: 0
Quote:
Originally Posted by Mill J View Post
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.
 
Old 04-23-2021, 07:51 PM   #4
Mill J
Senior Member
 
Registered: Feb 2017
Location: @127.0.0.1
Distribution: Mint, Void, MX, Haiku, PMOS, Plasma Mobile, and many others
Posts: 1,215
Blog Entries: 2

Rep: Reputation: 513Reputation: 513Reputation: 513Reputation: 513Reputation: 513Reputation: 513
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.
 
Old 04-24-2021, 09:40 AM   #5
apoorv569
Member
 
Registered: Jun 2020
Distribution: Arch Linux
Posts: 60

Original Poster
Rep: Reputation: 0
Quote:
Originally Posted by Mill J View Post
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 */

Last edited by apoorv569; 04-24-2021 at 09:45 AM.
 
Old 04-24-2021, 10:57 AM   #6
Mill J
Senior Member
 
Registered: Feb 2017
Location: @127.0.0.1
Distribution: Mint, Void, MX, Haiku, PMOS, Plasma Mobile, and many others
Posts: 1,215
Blog Entries: 2

Rep: Reputation: 513Reputation: 513Reputation: 513Reputation: 513Reputation: 513Reputation: 513
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
 
Old 04-24-2021, 11:54 AM   #7
apoorv569
Member
 
Registered: Jun 2020
Distribution: Arch Linux
Posts: 60

Original Poster
Rep: Reputation: 0
Quote:
Originally Posted by Mill J View Post
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
 
Old 04-24-2021, 01:05 PM   #8
NevemTeve
Senior Member
 
Registered: Oct 2011
Location: Budapest
Distribution: Debian/GNU/Linux, AIX
Posts: 4,170
Blog Entries: 1

Rep: Reputation: 1536Reputation: 1536Reputation: 1536Reputation: 1536Reputation: 1536Reputation: 1536Reputation: 1536Reputation: 1536Reputation: 1536Reputation: 1536Reputation: 1536
That's what gdb is good for...
https://www.tutorialspoint.com/gnu_d...uick_guide.htm
 
Old 04-24-2021, 01:06 PM   #9
Mill J
Senior Member
 
Registered: Feb 2017
Location: @127.0.0.1
Distribution: Mint, Void, MX, Haiku, PMOS, Plasma Mobile, and many others
Posts: 1,215
Blog Entries: 2

Rep: Reputation: 513Reputation: 513Reputation: 513Reputation: 513Reputation: 513Reputation: 513
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?

Last edited by Mill J; 04-24-2021 at 01:13 PM. Reason: BugFix
 
Old 04-24-2021, 03:08 PM   #10
apoorv569
Member
 
Registered: Jun 2020
Distribution: Arch Linux
Posts: 60

Original Poster
Rep: Reputation: 0
Quote:
Originally Posted by Mill J View Post
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.
 
Old 04-24-2021, 04:56 PM   #11
Mill J
Senior Member
 
Registered: Feb 2017
Location: @127.0.0.1
Distribution: Mint, Void, MX, Haiku, PMOS, Plasma Mobile, and many others
Posts: 1,215
Blog Entries: 2

Rep: Reputation: 513Reputation: 513Reputation: 513Reputation: 513Reputation: 513Reputation: 513
Quote:
Originally Posted by apoorv569 View Post
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.
 
Old 04-24-2021, 05:39 PM   #12
apoorv569
Member
 
Registered: Jun 2020
Distribution: Arch Linux
Posts: 60

Original Poster
Rep: Reputation: 0
Quote:
Originally Posted by Mill J View Post
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.
 
  


Reply

Tags
c++, sqlite3


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
LXer: Sqlite-Commander - A ncurses based tool to display the records and tables of a sqlite database LXer Syndicated Linux News 0 01-02-2011 08:11 AM
LXer: Adobe throws weight behind SQLite LXer Syndicated Linux News 0 02-27-2008 12:11 AM
.htaccess with Basic authentication throws MySQL error in Error Log shailesh_mishra Linux - Security 6 01-17-2007 10:15 AM
Basic authentication with .htaccess throws MySQL error in Error Log shailesh_mishra Linux - Networking 2 01-12-2007 01:16 PM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 01:34 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration