SQL SELECT query returning old values?

Posted by Kahenraz on Wed 12 Oct 2016 02:22 PM — 4 posts, 19,862 views.

Kahenraz Wed 12 Oct 2016 02:22 PM #0

I have a simple query pulling information from a live database where the values can change. The problem is that the data I get can lag or fail to update at all despite the database being up to date.

For example, see this query where I pull a value:

db_object = db_object or assert(sqlite3.open("some_db.db"))

local q = "SELECT thing FROM table WHERE field = something"

for row in db_object:nrows(q) do
   print(row.thing)
end -- for

I have a dataviewer Valentina Studio which is also connected and every time I pull a value with that it's up to date. But when my trigger fires and pulls the data then sometimes it's really old.

I'm reusing the same database connection rather than opening/closing a new one for each fire of the trigger. But I've also tried opening/closing the connection each query but the data is still old.

Any idea why this is happening? I can't use SQLite if the data I'm getting isn't reliable.

Nick Gammon Australia Forum Administrator Wed 12 Oct 2016 08:44 PM #1

I've never seen that happen. Can you make up a minimal test that proves it?

Nick Gammon Australia Forum Administrator Wed 12 Oct 2016 08:55 PM #2

I think SQLite3 would be unusable if this happened generally. I can only think you are using it in an unusual way, for example keeping the database on a network drive, and the operating system serving up cached copies of the file to SQLite3.

Quote:

I have a dataviewer Valentina Studio which is also connected and every time I pull a value with that it's up to date.

Where is the modification being made? In Valentina Studio (which I haven't heard of) or elsewhere in the MUSHclient code?

Nick Gammon Australia Forum Administrator Thu 13 Oct 2016 06:43 AM #3

I just want to point out that SQLite3 is (or claims to be) the most widely-deployed and used database engine, with billions of copies in use. For example, in Macs, iPhone, Android devices, Windows 10, many web browsers, and so on. See: Most Widely Deployed and Used Database Engine

The interface in MUSHclient is just a wrapper to call the various functions in the engine. The library itself is updated every few versions of MUSHclient in order to get speed improvements, extra features, and bug fixes.

The likelihood that you have somehow got a situation where the database engine "fails to update at all" is extremely low. It is probably a coding error.