The curious case of the time-travelling SQLite CLI

Zecc

I have a Windows service (written in C#, and therefore using .Net) reading data off a SQLite database via System.Data.SQLite.
The database isn't very big, it has fewer than 10 columns and 2000 rows.

There is never more than a single process writing to the database. This is a separate executable — let's call it UpdateFidgetsDB.exe — which the previously mentioned service will run daily, late at night; or which I will occasionally manually start when I need to force the fidgets to update right away.
If more than one instance of this process tries to run at the same time, there is a global mutex which will be checked and the latter instances will exit early. No other known process writes to the database, as I'm pretty much the only person who knows it exists. The only reader is the Windows service I've mentioned, which makes no writes.

UpdateFidgetsDB.exe and the service use a class which uses ReaderWriterLockSlim to prevent multiple threads from writing to the database. This means nothing for separate processes, but as I said only one single instance of UpdateFidgetsDB.exe at a time will write to the database. Besides, the process itself is single-threaded, so the lock is mostly for paranoid reasons.

Even if there were no locks whatsoever, System.Data.SQLite should be able to handle it anyway, right?
At most I'd get a "database is locked" exception thrown at me.

The problem I have only happens in production; isn't that wonderful?
It did happen in dev at some point, because I was keeping a long-lived SQLiteConnection and commits weren't being made.
But since then all database access goes through these methods:

public class Database
{
	static SQLiteDateFormats DATE_FORMAT = SQLiteDateFormats.ISO8601;
	static DateTimeKind DATE_KIND = DateTimeKind.Utc;

	readonly string ConnectionString;

	public Database(string uri)
	{
		var builder = new SQLiteConnectionStringBuilder();
		builder.DataSource = uri;
		builder.DateTimeFormat = DATE_FORMAT;
		builder.DateTimeKind = DATE_KIND;
		builder.ForeignKeys = true;
		builder.JournalMode = SQLiteJournalModeEnum.Wal;
		ConnectionString = builder.ConnectionString;
	}

	public SQLiteConnection GetConnection()
	{
		return new SQLiteConnection(ConnectionString).OpenAndReturn();
	}

	public static string FormatDate(DateTime dt)
	{
		if(dt == null) return null;
		return SQLiteConvert.ToString(dt, DATE_FORMAT, DATE_KIND, null);
	}

	public static DateTime ParseDate(string value, DateTime defaultValue)
	{
		if (value == null) return defaultValue;
		return Convert.ToDateTime(value);
	}

	private ReaderWriterLockSlim _readerWriterLock = new ReaderWriterLockSlim();

	[System.Diagnostics.DebuggerStepThrough]
	public void InWriteTransaction(Action<SQLiteConnection> fn, bool prioritizeReads = true)
	{
		_readerWriterLock.EnterWriteLock();
		try
		{
			while (prioritizeReads && _readerWriterLock.WaitingReadCount > 0)
			{
				_readerWriterLock.ExitWriteLock();
				Thread.Sleep(10);
				_readerWriterLock.EnterWriteLock();
			}
			using (SQLiteConnection conn = GetConnection())
			using (SQLiteTransaction tx = conn.BeginTransaction(System.Data.IsolationLevel.Serializable))
			{
				try
				{
					fn(conn);
					tx.Commit();
				}
				catch(Exception ex)
				{
					Log.Error("Database transaction rolled back: {0}", ex.Message);
					tx.Rollback();
					throw;
				}
				conn.Close();
			}
		}
		finally
		{
			_readerWriterLock.ExitWriteLock();
		}
	}

	[System.Diagnostics.DebuggerStepThrough]
	public void InReadTransaction(Action<SQLiteConnection> fn)
	{
		_readerWriterLock.EnterReadLock();
		try
		{
			using (SQLiteConnection conn = GetConnection())
			{
				fn(conn);
			}
		}
		finally
		{
			_readerWriterLock.ExitReadLock();
		}
	}
}

You'll notice Database and its methods aren't static, but the "owning" class is:

public static class FidgetsUpdater
{
	private static Database sDatabase;
	
	static FigdetUpdater() {
		Database = new Database(Uri);
	}
	
	public static void UpdateFidgets(IEnumerable<Fidget> fidgets)
	{
		string updateDate = Database.FormatDate(DateTime.UtcNow);
		string sql =
			"INSERT INTO Fidgets (NaturalKey, FidgetName, KnobbinessLevel, UpdateDate) " +
			"VALUES (@naturalKey, @fidgetName, @knobbinessLevel, @updateDate)" +
			"ON CONFLICT(NaturalKey) DO UPDATE SET "+
			"FidgetName = @fidgetName, " +
			"KnobbinessLevel = @knobbinessLevel, " +
			"UpdateDate = @updateDate";
		int rowsAffected = 0;
		sDatabase.InWriteTransaction(conn =>
		{
			using(var cmd = conn.CreateCommand())
			{
				cmd.CommandText = sql;
				foreach(var fidget in fidgets)
				{
					cmd.Parameters.Clear();
					cmd.Parameters.AddWithValue("@naturalKey", fidget.NaturalKey);
					cmd.Parameters.AddWithValue("@fidgetName", fidget.Name);
					cmd.Parameters.AddWithValue("@knobbinessLevel", fidget.KnobbinessLevel);
					cmd.Parameters.AddWithValue("@updateDate", updateDate);
					rowsAffected += cmd.ExecuteQuery();
				}
			}
		});
		Log.Info($"{rowsAffected} fidgets have been updated");
	}
}

Oh but wait a minute, I still haven't said what the problem is, have I?

The problem is that UpdateFidgetsDB.exe can write to the database without stumbling on exceptions or deadlocks, and the service can read just as well. There are no rollbacks mentioned anywhere.

Wait, that's not the problem.

No, the problem is that the service won't read inconsistent data or fail to read the updates from UpdateFidgetsDB.exe.

Wait, that's also not the problem.

No, the problem is that sqlite3.exe, the official SQLite command line client as downloaded from sqlite.org earlier today, won't read the latest changes.

Yes, that's the problem.

So, um, yeah. Why won't sqlite3 see my changes?

SELECT MAX(UpdateDate) FROM Fidgets will return today's date if executed from the service, but will return a date from last month when I run using sqlite3.exe.

Even worse, if I try to access the column KnobbinessLevel, which is a relatively recent addition, it won't find that column at all!

This makes my life hard as I try to check what's in the database in production and I can't, because sqlite3 won't show me what my software sees, and I'm not comfortable putting other third party software in the client's server.
And no, I'm not going to create my own SQLite CLI, GUI or TUI or whatever, or add otherwise unnecessary functionality to my code.

A few things worth noting:

The database's journal mode is WAL, as you might have noticed above. pragma journal_mode; in sqlite3.exe agrees. I can see the -shm and -wal files coming and going in File Explorer and never a -journal file. This is very strong evidence that SQLite is using write-ahead logging and not a rollback journal.
Write-ahead means changes are written elsewhere first and only then in the database file, yes? Then why, oh why, is it that when I create a copy of the file and move it elsewhere, everyone including sqlite3.exe will see the most recent data? I mean sure I'm doing the copy when there're no -shm and -wal files, so changes have already been applied; but then pray tell me why doesn't sqlite3 see them in the original file?
Copying the .sqlite file (and just the .sqlite file) to make sqlite3 see the most recent data even works across machines.
sqlite> pragma main.integrity_check; --> ok
sqlite> pragma main.synchronous; --> 2 = « When synchronous is FULL (2), the SQLite database engine will use the xSync method of the VFS to ensure that all content is safely written to the disk surface prior to continuing. »
sqlite> PRAGMA main.wal_checkpoint(FULL); --> 0|0|0 « FULL: This mode blocks (invokes the busy-handler callback) until there is no database writer and all readers are reading from the most recent database snapshot. It then checkpoints all frames in the log file and syncs the database file. FULL blocks concurrent writers while it is running, but readers can proceed. » 0|0|0 = SQLITE_OK|zero modified pages written to the WAL file|zero pages moved from WAL to DB file. No -1 means this was NOT ran on a database that isn't in WAL mode.

I don't doubt it might be PEBKAC, but what am I missing?

Last time this happened I ended up deleting the file completely, and sqlite3 would still find the tables there when reopening a file of the same name!
I ended up having to use a file with a different name.

There's got to be a temporary something in memory or in a separate directory which is confusing sqlite3, right?

This is a client's server, and even though I have administrator privileges (hurray for blind trust!) I'm not comfortable with rebooting the machine just because.

Any ideas?

Edit: perfect post foiled by >s inside backticks.

Zecc

Btw, if one of the ideas is "stop using SQLite", I'm open to suggestions.

It needs to have nice .Net integration, not require separate installation (which I believe eliminates SQL Server Compact?), but still support foreign keys and triggers (which eliminates LiteDB) and be free as in I'm "my bosses are cheapskates and honestly so am I".

Mason_Wheeler

Given what you've said here, the obvious answer (which may not be correct, but should definitely be the first thing you check) is that the sqlite3 tool and the software are not actually pointing at the same database. Have you verified that this is not the case?

Zecc

@Mason_Wheeler That's so incredibly obvious it hasn't even crossed my mind.

Alas, no. sqlite3 and the .sqlite file are in the same directory. Powershell autocompletes the filename.

Mason_Wheeler

@Zecc So you've checked the sqlite3 tool. Now check the software's connection string and make sure it's the same DB.

Zecc

It is. If I create a copy of the file I've opened in sqlite3 into the same directory, sqlite will see the changes in the new file.

Something I haven't actually tried was renaming the new file into the old file's name to see what happens, but I don't think that's going to work. Even if it does, I won't be any wiser on what the problem is.

Ugh, I've got to leave, but I will check on this later.

SirTwist

@Zecc Is the file located somewhere subject to the VirtualStore? That means in the program directory if installed in either Program Files, or or somewhere under system32.

The service will be reading the real db, since it's probably running under one of the common service accounts, but the shell is 32-bit and therefore subject to virtualization. Try elevating sqlite3.exe.

Check for stuff in %userprofile%\AppData\Local\VirtualStore

Carnage

Since the datastores are files... One thing that springs to mind are old file handles so you get an ancient version of the file for the CLI. Or simply that the OS/Driver/whatnot isn't actually dumping stuff into the file as expected, perhaps a workaround for the silly windows file locking mechanism.

Zecc

@Mason_Wheeler said in The curious case of the time-travelling SQLite CLI:

@Zecc So you've checked the sqlite3 tool. Now check the software's connection string and make sure it's the same DB.

While I was out I fully digested what I think you were trying to say: that just because the file is the same the connection string might be different. The service is using the whole path, while I was using a relative path. It could possibly just be different enough for SQLite not to recognize it as the same file.

@SirTwist said in The curious case of the time-travelling SQLite CLI:

The service will be reading the real db, since it's probably running under one of the common service accounts

It had crossed my mind that I was using a different user than the one the service is running under. Thing is, what happens when I run FidgetUpdateDB myself? Shouldn't it affect the same file?

@SirTwist said in The curious case of the time-travelling SQLite CLI:

@Zecc Is the file located somewhere subject to the VirtualStore? That means in the program directory if installed in either Program Files, or or somewhere under system32.

The service will be reading the real db, since it's probably running under one of the common service accounts, but the shell is 32-bit and therefore subject to virtualization. Try elevating sqlite3.exe.

Check for stuff in %userprofile%\AppData\Local\VirtualStore

Ding ding ding. We have a winner. Elevating worked.

Also, looking into %userprofile%\AppData\Local\VirtualStore provided the first clue: a smaller file size. Opening the file elsewhere brought confirmation. It contains the old data.

Looks like I have some reading to do. I don't know much about Windows stuff like this.
Edit: oh, so it's basically just a mirror of C:\ProgramData\Initrode\Dumbapp because I apparently don't have permissions to access what's in the real C:\ProgramData? Even though I can apparently write new files in there? But I guess the new files are stored in the virtual store as well. But if I can't read the files in the actual C:\ProgramData then how is it able to create copies?

JBert

@Zecc said in The curious case of the time-travelling SQLite CLI:

Edit: oh, so it's basically just a mirror of C:\ProgramData\Initrode\Dumbapp because I apparently don't have permissions to access what's in the real C:\ProgramData? Even though I can apparently write new files in there? But I guess the new files are stored in the virtual store as well. But if I can't read the files in the actual C:\ProgramData then how is it able to create copies?

I thought the VirtualStore folder gets created on first write access to a file which you don't have write permissions for (at least not without elevation). You need read permissions to even know what's in the old file.

Once a file exists in the VirtualStore it will remain used by the non-elevated process. Mind you that removing it might not help if a non-elevated sqlite3 is always modifying the file (e.g. to write a "last opened" timestamp in there) because that would obviously force a new VirtualStore item to be created.

dkf

@Zecc said in The curious case of the time-travelling SQLite CLI:

@SirTwist said in The curious case of the time-travelling SQLite CLI:

Is the file located somewhere subject to the VirtualStore? That means in the program directory if installed in either Program Files, or or somewhere under system32.

The service will be reading the real db, since it's probably running under one of the common service accounts, but the shell is 32-bit and therefore subject to virtualization. Try elevating sqlite3.exe.

Check for stuff in %userprofile%\AppData\Local\VirtualStore

Ding ding ding. We have a winner. Elevating worked.

Also, looking into %userprofile%\AppData\Local\VirtualStore provided the first clue: a smaller file size. Opening the file elsewhere brought confirmation. It contains the old data.

Looks like I have some reading to do. I don't know much about Windows stuff like this.
Edit: oh, so it's basically just a mirror of C:\ProgramData\Initrode\Dumbapp because I apparently don't have permissions to access what's in the real C:\ProgramData? Even though I can apparently write new files in there? But I guess the new files are stored in the virtual store as well. But if I can't read the files in the actual C:\ProgramData then how is it able to create copies?

This reminds me a lot of HP-UX. Not in a good way either.

Mason_Wheeler

@Zecc said in The curious case of the time-travelling SQLite CLI:

Ding ding ding. We have a winner. Elevating worked.
Also, looking into %userprofile%\AppData\Local\VirtualStore provided the first clue: a smaller file size. Opening the file elsewhere brought confirmation. It contains the old data.

So... yeah. Tool was connecting to a different database than the software. Just by a different (and weirder!) mechanism than I suspected. Kudos to @SirTwist for thinking of this one.

pie_flavor

@Zecc said in The curious case of the time-travelling SQLite CLI:

but still support foreign keys and triggers (which eliminates LiteDB)

If there's only the one process, routing everything through the one service, then surely you can encode such functionality in the service?

Mason_Wheeler

@pie_flavor No you can't, and don't call me Shirley.

SirTwist

@Mason_Wheeler Thanks, I recognized it right away because I had run into the same thing myself. Whenever a supposedly identical file has different contents depending on the user, think virtualization.

The file permissions can be changed on the "real" file, if the app can't be corrected to write to the proper location, or ProgramData is correct for shared data/state. This can be done either during installation or by the program. I think your issue is that the file was created by a different account, which meant it was writable only by that user and members of Administrators. Even if your regular login is a member of this group, many of the permissions are removed for you login shell unless you turn UAC off (don't do that). Instead, use elevation.

I just tested it. I created a new folder and text document under ProgramData with my user account, and can read/write with my regular account. The Admin user still can't write to it unles explicitly run as elevated.

You can add "Run as a different user" to start menu items by enabling the policy 'User Configuration\Administrative Templates\Start Menu and Taskbar\Show "Run as different user" command on Start' in the local group policy (run gpedit.msc elevated to find this). You can also shift-right-click on an executable to access this. "Run as Administrator" and "Run as different user" are not the same thing, even if the same login is used.

Edit to add: The registry is also subject to virtualization under. See https://docs.microsoft.com/en-us/windows/win32/sysinfo/registry-virtualization . Note that permissions can be applied to the registry to override this as well.

error

@SirTwist said in The curious case of the time-travelling SQLite CLI:

Instead, use elevation.

Wouldn't it be better to fix the permissions issue, either by editing ACLs or using a different location?

Also, I'd rather the OS fail with E_PERM than pretend to work but actually do something weird.

SirTwist

@error said in The curious case of the time-travelling SQLite CLI:

Wouldn't it be better to fix the permissions issue, either by editing ACLs or using a different location?

It depends. If he's only using the shell for dev/debugging purposes and the db is normally only accessed by a service, then elevation is preferred, as it will keep the db safe from accidental or casual tampering. He could just add himself to the ACL manually for that, or use an elevated prompt. If it's to be accessed by multiple users, then the installer should probably set the ACL.

LaoC

@Zecc said in The curious case of the time-travelling SQLite CLI:

Looks like I have some reading to do. I don't know much about Windows stuff like this.
Edit: oh, so it's basically just a mirror of C:\ProgramData\Initrode\Dumbapp because I apparently don't have permissions to access what's in the real C:\ProgramData? Even though I can apparently write new files in there? But I guess the new files are stored in the virtual store as well. But if I can't read the files in the actual C:\ProgramData then how is it able to create copies?

I didn't even know this existed. From MS:

To summarise, the hidden VirtualStore folder is part of the virtualization technology that was introduced in Windows Vista. This was intended as a compatibility feature to allow old programs to continue working when using User Account Control and running as a Standard User. It is surprising that you are encountering problems seven years later¹. This was a temporary fix intended to be removed in some later release of Windows².

Also, the shell is 32-bit?! Let me guess: there was more commandline shit that broke when they 64-bitted all the things than shit that worked, so they decided they'd rather compativirtuawrap the whole thing, so everybody thought the old shit was working fine and nobody bothered to fix it and the temporary shim became a permanent fixture?

¹ The answer is from 2013
² ORLY

Edit: typo

Zecc

@pie_flavor said in The curious case of the time-travelling SQLite CLI:

@Zecc said in The curious case of the time-travelling SQLite CLI:

but still support foreign keys and triggers (which eliminates LiteDB)

If there's only the one process, routing everything through the one service, then surely you can encode such functionality in the service?

Sure. I could also write my own storage format with my own indices and constraint checks.

This is about minimizing time and effort better applied elsewhere.

Zecc

@SirTwist said in The curious case of the time-travelling SQLite CLI:

@error said in The curious case of the time-travelling SQLite CLI:

Wouldn't it be better to fix the permissions issue, either by editing ACLs or using a different location?

It depends. If he's only using the shell for dev/debugging purposes and the db is normally only accessed by a service, then elevation is preferred, as it will keep the db safe from accidental or casual tampering. He could just add himself to the ACL manually for that, or use an elevated prompt. If it's to be accessed by multiple users, then the installer should probably set the ACL.

^This. All of it.

Zecc

@Zecc said in The curious case of the time-travelling SQLite CLI:

It had crossed my mind that I was using a different user than the one the service is running under. Thing is, what happens when I run FidgetUpdateDB myself? Shouldn't it affect the same file?

FidgetUpdateDB self-elevates, so that's why it was using the expected file.

dfdub

@SirTwist said in The curious case of the time-travelling SQLite CLI:

You can add "Run as a different user" to start menu items by enabling the policy 'User Configuration\Administrative Templates\Start Menu and Taskbar\Show "Run as different user" command on Start' in the local group policy (run gpedit.msc elevated to find this). You can also shift-right-click on an executable to access this. "Run as Administrator" and "Run as different user" are not the same thing, even if the same login is used.

Way too complicated. Just enable developer mode in the Windows 10 settings app. It will then let you enable that setting from the same settings page.