Problem: data is not available to SELECT from new connection to SQLite DB with WAL mode (Write Ahead Log) on if previous connection hasn't been closed properly. Main question is: why data is lost and is there some way to get lost transactions?
I'm trying to store data in a SQLite table with WAL mode on. I'll describe 3 cases: case A results in transaction loss, cases B and C - don't.
INSERT (Connection 1)SELECT (Connection 1) // All data is present.SELECT (Connection 2) // All data is present.SELECT (Connection 3) // Some data is lost - select could return 2 out of 5 inserted transactions, for example. Or 0 out of 5 transactions. Totally random.INSERT (Connection 1)SELECT (Connection 1) // All data is present.SELECT (Connection 2) // All data is present.SELECT (Connection 3) // All data is present.INSERT (Connection 1)SELECT (Connection 1) // All data is present.SELECT (Connection 2) // All data is present.SELECT (Connection 3) // All data is present.To sum up, when i SELECT from the table before closing app, all data is present, but after incorrect closing of the application (for example if app crashed) some of the data i inserted is missing. However if i perform checkpoint before closing the app (or close connection before closing app)- all the data is available.
Extra info:
sqlite3_wal_hook to check whether transactions are actually commited to WAL-journal file and it shows that pages being successfully written to the log file.-wal file using Device File Explorer in Android Studio or by copying it to external storage from internal storage (/data/data/com.package.my/files) and most of the time it is either empty or doesn't exist.sqlite3_open_v2(db_name.c_str(), &handle, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX, nullptr);
It didn't make any difference. However, it lead to problems with reading from 2nd connection, so i use sqlite3_open without SQLITE_OPEN_FULLMUTEX.
Stack: android 7 - JNI - c++ 11 - sqlite 3.27.2
PRAGMA synchronous = EXTRA and FULL as suggested by @bwt - didn't help.Code:
int wal_hook(void* userdata, sqlite3* handle, const char* dbFilename, int nPages){
char* pChar;
pChar = (char*)userdata; // "test"
printf("Hello hook");
return SQLITE_OK;
}
// DB init (executed once on app start)
void initDB()
int32 rc = sqlite3_open(db_name.c_str(), &handle); // rc = 0
// check threadsafe mode
int stResult = sqlite3_threadsafe(); // stResult = 1
// register WAL hook
char* pointerContext = new char[4]();
pointerContext[0] = 't';
pointerContext[1] = 'e';
pointerContext[2] = 's';
pointerContext[3] = 't';
sqlite3_wal_hook(handle, wal_hook, pointerContext);
// turn WAL mode on
int32 rcWAL = sqlite3_exec(handle, "PRAGMA journal_mode=WAL;", processResults, &result, &errMsg); // rcWAL = 0
}
// close connection
int32 close() {
return sqlite3_close(handle);
}
// WAL checkpoint
sqlite3_exec(handle, "pragma wal_checkpoint;", processResults, &result, &errMsg);
// Insert
EventPtr persist(EventPtr obj) {
vector<DBData*> args;
int beginResult = sqlite3_exec(_connector->handle, "BEGIN TRANSACTION;", NULL, NULL, NULL);
try {
args.push_back(&obj->eventId);
// ... push more args
string query = "insert into events values(?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12,?13,?14);";
int32_t rc = _connector->executePreparedWOresult(query.c_str(),&args);
if(rc == SQLITE_DONE) {
int endResult = sqlite3_exec(_connector->handle, "END TRANSACTION;", NULL, NULL, NULL);
return obj;
}
} catch(...){ }
}
// SELECT
vector<EventPtr> readAll()
{
string query = "select * from Events;";
ResultSetPtr result= _connector->executePrepared(query.c_str(), NULL);
vector<EventPtr> vec;
for(int32_t i = 0; i < result->size(); i ++){
EventPtr event(new Event);
// init event
vec.push_back(EventPtr(event));
}
return vec;
}
// executePreparedWOresult
int32 executePreparedWOresult(const string& query, vector<DBData*> *args){
sqlite3_stmt *stmt;
cout << query ;
sqlite3_prepare_v2(handle, query.c_str(), query.size(), &stmt, NULL);
for(uint32 i = 0;args && i < args->size(); i ++){
switch(args->at(i)->getType()){
// statement bindings (sqlite3_bind_text, etc.)
}
}
int32 rc = sqlite3_step(stmt);
sqlite3_finalize(stmt);
return rc;
}
// executePrepared
ResultSetPtr executePrepared(const char *query, const vector<DBData*> *args){
ResultSetPtr res = ResultSetPtr(new ResultSet);
sqlite3_stmt *stmt;
int32_t rs = sqlite3_prepare_v2(handle, query, strlen(query), &stmt, NULL);
for(uint32 i = 0;args && i < args->size(); i ++){
switch(args->at(i)->getType()){
// statement bindings (sqlite3_bind_text, etc.)
}
}
int32 count = sqlite3_column_count(stmt);
int32 rc;
ResultRow row;
int32 rows = 0;
while((rc = sqlite3_step(stmt)) == SQLITE_ROW){
rows ++;
for(int32 i = 0; i < count; i++){
// multiple row.push_back-s for all columns
}
res->push_back(row);
row.clear();
}
sqlite3_finalize(stmt);
return res;
}
// LUA parts: --------------------------------------------------------------
// 1.
local query = [[ SELECT val from Parameters WHERE name = "column_name"]]
local period = 0
for row in db:nrows(query) do
if row["val"] ~= nil then
period = row["val"]
end
end
// 2.
local table = {}
if json ~= nil then
table["event_id"] = in_json["event_id"]
local query = [[ SELECT * FROM Events WHERE event_id = "%s" ]]
query = string.format(query, table["event_id"])
for row in db:nrows(query) do
table = row
end
else
json = {}
local query = [[ SELECT * FROM Events order by created DESC LIMIT 1; ]]
for row in db:nrows(query) do
table = row
end
end
// 3.
function getRow(con, sql)
local iRow = nil
for a in con:nrows(sql) do
iRow = a
end
return iRow
end
local termRow = getRow(db,[[SELECT value FROM parameters WHERE name='column_name']])
// 4.
local stmt = db:prepare("SELECT value FROM parameters WHERE name='column_name'")
local cnt = 0
for row in stmt:nrows() do
cnt = cnt + 1
end
stmt:finalize()
// 5.
local param = "N"
for Parameter in db_transport:nrows([[SELECT val FROM Parameters WHERE name = 'param']]) do
param = SParameter["val"]
end
It all comes down to the fact that I used two different instances of SQLite.
More detailed explanation why it is bad idea is listed here: https://www.sqlite.org/howtocorrupt.html#multiple_copies_of_sqlite_linked_into_the_same_application
Solution: Using dynamic libsqlite3.so on C++ side and dynamic lsqlite3.so that is linked to dynamic libsqlite3.so and liblua.so. That makes possible using the same instance of sqlite3 library on C++ and Lua sides.
After a discussion with a LuaSQLite3 developer the documentation on lua.sqlite.org concerning lsqlite3complete was updated.
http://lua.sqlite.org/index.cgi/doc/tip/doc/lsqlite3.wiki#overview