SQLite3 database issues

I am having various random issues with the new SQLite3 database in NWN-EE.

Here is a list of the issues with some additional info:

  1. Random crash shortly after loading a module in dedicated server:
Working Directory For Game Install Is: /home/sh/nwn
Working Directory For Your Resources Is: /home/sh/.local/share/Neverwinter Nights
SQL ERROR [5]: database is locked in "pragma auto_vacuum=full" while executing (unknown)
SQL ERROR [5]: database is locked in "pragma auto_vacuum=full" while executing (unknown)
SQL ERROR [5]: database is locked in "pragma auto_vacuum=full" while executing (unknown)
terminate called after throwing an instance of 'sqlite::errors::busy'
  what():  database is locked
Starting NWNX 8193.34 [9cfc0f5814]
  Base address is:   0x0000556a293b4000
  g_pExoBase is:     0x0000556a2a08d3d0
  Corrected 7254 ASLR addresses
Neverwinter Nights Server
Build: 8193.34 [8507d5a9]
Copyright BioWare Corp 1998-2004
Registering crash signal handlers.

Server: Loading...
Server: Running...

 NWNX Signal Handler:
 NWNX 8193.34 (9cfc0f5814) has crashed. Fatal error: Program aborted (6).

    ./nwnx_patch.so(_ZN7NWNXLib8Platform13GetStackTraceB5cxx11Eh+0x67) [0x7f19a02f7467]
    ./nwnx_patch.so(nwnx_signal_handler+0xad) [0x7f19a0244e7d]
    /lib/x86_64-linux-gnu/libc.so.6(<UNKNOWN>) [0x7f199fc53090]
    /lib/x86_64-linux-gnu/libc.so.6(gsignal+0xcb) [0x7f199fc5300b]
    /lib/x86_64-linux-gnu/libc.so.6(abort+0x12b) [0x7f199fc32859]
    /lib/x86_64-linux-gnu/libstdc++.so.6(<UNKNOWN>) [0x7f19a002f911]
    /lib/x86_64-linux-gnu/libstdc++.so.6(<UNKNOWN>) [0x7f19a003b38c]
    /lib/x86_64-linux-gnu/libstdc++.so.6(<UNKNOWN>) [0x7f19a003b3f7]
    /lib/x86_64-linux-gnu/libstdc++.so.6(<UNKNOWN>) [0x7f19a003b6a9]
    ./nwserver-linux(<UNKNOWN>) [0x556a29485ad1]
    ./nwserver-linux(_ZN4Task5QueueISt10shared_ptrINS_8CExoTaskISt4pairINSt7__cxx1112basic_stringIcSt11char_traitsIcESaIcEEESt6vectorIN6NWSync7CNWSync28ResRefBatchInsertEntryResultESaISD_EEEEEEEEvT_+0x196f) [0x556a2956f22f]
    ./nwserver-linux(_ZN13CExoArrayListIjE7SetSizeEi+0x1f15d) [0x556a295ad85d]
    ./nwserver-linux(_ZN13CExoArrayListIjE7SetSizeEi+0x20778) [0x556a295aee78]
    ./nwserver-linux(_ZN13CExoArrayListIjE7SetSizeEi+0x1105b) [0x556a2959f75b]
    ./nwserver-linux(_ZN16CNetworkProfiler24AddMessageToFrameProfileEhj+0x3e9) [0x556a294e6039]
    ./nwserver-linux(_ZN16CNetworkProfiler24AddMessageToFrameProfileEhj+0x5988) [0x556a294eb5d8]
    ./nwserver-linux(_ZN16CNetworkProfiler24AddMessageToFrameProfileEhj+0x6dc2) [0x556a294eca12]
    ./nwserver-linux(<UNKNOWN>) [0x556a2949ac65]
    /lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xf3) [0x7f199fc34083]
    ./nwserver-linux(_start+0x2a) [0x556a2949ef0a]
Please go to https://beamdog.atlassian.net/servicedesk/ and report this crash to the Neverwinter Nights bug reporter.

Please describe the conditions within which you crashed, indicate your current game version, and attach the following files:

- The error report file at /home/sh/.local/share/Neverwinter Nights/nwserver-crash-1680722397.log.

Random bug that happens maybe 1 of 10 (re)starts.

nwserverlog1.txt had just these two lines:

Your cryptographic public identity is: abcdefgh.....
SQL ERROR: [5] database is locked in "pragma auto_vacuum=full" while executing (unknown)

I thought that maybe this issue is caused by the database file becoming too big. So I recently split the database file to two. One for main gameplay data and one for logs. (was 120mb before, now is 24mb and 80mb for logs). But it didn’t really help.

  1. SQL lite commands fails due to the “UNIQUE constraint failed”
nwserverLog11677653341.txt:SQL ERROR: [1555] abort at 14 in [INSERT INTO table2(account,varname) VALUES ('Q??????6','set')]: UNIQUE constraint failed: table2.account, table2.varname while executing (unknown)

This happened a few times for now. I believe this is caused by prior SELECT sql command failed (without any error, see 3) ) as the script where this was executed from was checking whether the specified account had a record in db under this varname (the table structure is just account+varname with no value, I am checking the existence of the record using this code:

int HasAccVariable(string sAccount, string sVarName)
    sqlquery sql = SqlPrepareQueryCampaign("arkhdb","SELECT varname FROM table2 WHERE account='"+sAccount+"' AND varname='"+sVarName+"'");
    return SqlStep(sql);

this must return false for the insertion to be executed. So it makes no sense that I would get this error which means that the given account already have the value set.

  1. Some SQL commands randomly failing without any error.

In attempt to find out the cause for issue 2) I added some debugging into the insertion function that is related to above. After some time I polled all logs with grep and found multiple cases of sql command failed without any engine error.

void AddAccVariable(string sAccount, string sVarName)
sqlquery sql = SqlPrepareQueryCampaign("arkhdb","INSERT INTO table2(account,varname) VALUES ('"+sAccount+"','"+sVarName+"')");
    if(!SqlStep(sql)) WriteTimestampedLogEntry("ERROR with database, could not insert data, account: "+sAccount+", varname: "+sVarName);

and the debug messages in log:

nwserverLog1.txt:[Sat Apr  8 19:06:24] ERROR with database, could not insert data, account: U??????X, varname: djinn
nwserverLog1.txt:[Sat Apr  8 20:01:19] ERROR with database, could not insert data, account: U??????L, varname: 1epic
nwserverLog1.txt:[Sat Apr  8 20:29:49] ERROR with database, could not insert data, account: U??????J, varname: hc

and many more in other days - but there are no engine errors in neither log for this issue, the SqlStep just fails silently it seems and for no apparent reason.

Did anyone noticed similar issues with SQLite3 db? Any idea how to workaround this? Is the new embedded SQLite database considered unreliable and I should avoid it and use mysql instead?

Just for your information: SqlStep() only returns TRUE if there’s data to return, as per its description.

// Executes the given query and fetches a row; returning true if row data was
// made available; false otherwise. Note that this will return false even if
// the query ran successfully but did not return data.
// You need to call SqlPrepareQuery() and potentially SqlBind* before calling this.
// Example:
//   sqlquery n = SqlPrepareQueryObject(GetFirstPC(), "select widget from widgets;");
//   while (SqlStep(n))
//     SendMessageToPC(GetFirstPC(), "Found widget: " + SqlGetString(n, 0));
int SqlStep(sqlquery sqlQuery)

If you want to get any errors, you have to call SqlGetError() after the SqlStep()

Aah, I thought it returns TRUE if the sql command threw no error - it seemed logical since SqlStep has to be used for INSERT/UPDATE commands as well (contrary to the way how SQL worked in nwnx). Ok so the third issue is in fact a non-issue.