"file is not a database" issue

[yzhgu]

Win10 + vs2019 + wxsqlite4.9.2 I wrote a program, which will create daily SQLite db. But sometimes (not each time) it throws exception "file is not a database" in inserting table into db after open it. My code is shown below:

wxSQLite3Database   m_Db;
//blabla
//on next day
m_Db.Close();
wxSQLite3CipherSQLCipher ct;
ct.InitializeVersionDefault(WXSQLITE_CIPHER_SQLCIPHER);
m_Db.Open(newDbName, ct, wxT(“blabla”), WXSQLITE_OPEN_READWRITE | WXSQLITE_OPEN_CREATE);
if (m_Db.IsOpen())
  log_info(“db opened successfully”);

//insert some tables into the db, while exception was caught here

The program is running in multi-thread mode, 2 threads will open the db on new day. I have defined SQLITE_THREADSAFE=1 in building wxsqlite3. I can see in the log that the db is opened successfully, but I got exception "file is not a database" in inserting table into db after open it, and after that “database is locked” exception.

[230310.08:41:49.732][9068:7556][info] db D:\XProject\L2Shield\PCS\log\PSMLog\Sql\PSCHQSQL_20230309.db closed [230310.08:41:50.572][9068:7556][info] db D:\XProject\L2Shield\PCS\log\PSMLog\Sql\PSCHQSQL_20230310.db opened successfully [230310.08:41:49.732][9068:10468][info] db D:\XProject\L2Shield\PCS\log\PSMLog\Sql\PSCHQSQL_20230309.db closed [230310.08:41:51.408][9068:10468][info] db D:\XProject\L2Shield\PCS\log\PSMLog\Sql\PSCHQSQL_20230310.db opened successfully [230310.08:41:52.257][9068:10468][critical] insertPDIData Exception: 26-26 : file is not a database[26]: file is not a database [230310.08:42:57.956][9068:7556][critical] insertHMIData Exception: 5-5 : database is locked[5]: database is locked

This is happened only sometimes during create db on new day, and will work fine after restarting the program. What did I do wrong?

[utelle]

I suspect that you have a timing problem between your threads. Both threads are allowed to create the database at the beginning of a new day. This is problematic, because especially the SQLCipher scheme takes quite some time to initialize the encryption parameters (key derivation). So, it can easily happen that both threads think that they are creating a new database.

A better design would be if each thread tries to open an existing database, and only if that fails, a new database should be created by exactly one thread that guards its operation for example with a mutex. Thereafter both threads retry to open the new, now already existing database file.

This is not an issue of SQLite3 Multiple Ciphers but an issue of how threads and concurrent database creation are handled by your application.

[yzhgu]

Thanks for your help. Actually I have set named mutex before opening db. Maybe the m_Db.Open() function returns when the db is not comletely created.

[utelle]

Actually I have set named mutex before opening db.

That is not enough, unfortunately.

Maybe the m_Db.Open() function returns when the db is not comletely created.

Well, SQLite creates a file in the OS file system, but it will be empty, until actually content is written to the database. That is, you will have to lock the file until your application has created for example the necessary tables using CREATE TABLE statements.

As long as the database file is completely empty, any other thread opening a connection to this file, will assume that the database was not yet created. For databases using the SQLCipher scheme, opening and setting up the key will take quite some time - this is by design of the SQLCipher scheme to make it time consuming to test passphrases to crack an encrypted database.

As I wrote in my previous post you should dedicate a separate thread to the creation and initialization of a new database that locks the database until it can be safely accessed by other threads.

[yzhgu]

Thank you, now I understand the philosophy behind, this post can be closed.

[utelle]

Another idea to "create" a new database each day would be the following:

1. Create an empty database template that only holds the database schema (tables, views etc) and maybe a few static data.
2. At the beginning of a day copy the template to the new database file of the day.
3. Open the new copy (which is already initialized) as usual from your threads.

Step 1 has to be performed only once. Step 2 will be fast, because you only copy an (almost) empty, relatively small database file.

[yzhgu]

Thanks for your advice, I will consider it carefully.