Unexpected results from sqlite database inspector

zpt8mjs 2016-03-03 17:19:32 UTC #1

I’ve used the of sqlite database inspector in the past to report on action history in ActionHistory.db without any problems. I’m attempting to create a property to find the oldest access time of a file in each of my relays cache to determine approximately how long I can maintain data in the relay cache before it gets purged out. In order to do this I am running relevance using the of sqlite database inspector against DownloadCache.db

The fixlet debugger seems to be mangling the results related to the data types

But, what I get from the command line tool sqlite3.exe is more in line with what I would expect to see.

If I look at the schema for that table it shows that the SHA1 is a text and the others are integer

Why would I be getting these strange results?

Incidentally, I can get around the issue by changing the query a little.

jgstew 2016-03-04 06:53:16 UTC #2

This seems like an Integer overflow problem. Trying to put a 64bit integer into a 32bit integer, or it is an unsigned int being placed into a signed int (but that seems less likely) or similar.

This is a bug. You should file a PMR with IBM on this. Include a link to the this forum post in the PMR as well as other details. Then open an RFE with a reference to that PMR as well as a link to this forum post. Then let me go bug some developers directly to open an actual bug. See here: How to ask for IBM product help: PMRs, RFEs, and more

CC: @AlanM

BigFixNinja 2016-03-08 21:41:34 UTC #3

Somewhat similar results:

zpt8mjs 2016-03-08 22:20:06 UTC #4

I’ve opened the PMR on this issue, PMR 72946,442,000.

Why would I open an RFE? I’m not looking for added functionality. I’m just looking for it to work as designed

AlanM 2016-03-08 23:19:49 UTC #5

The issue may be simply that the SQLite that wrote the DB (in the server) is 64 bit while the inspector in the client is using a 32 bit version of SQLite. This may be something that a later version of the SQLite code has addressed as the string that comes out of the statement comes directly from the library.

BigFixNinja 2016-03-09 00:06:22 UTC #6

I think @jgstew is giving advice to ensure all bases are covered in getting maximum visibility on the issue within the IBM BigFix org

PMR received and will escalate to Dev.

@AlanM is this SQLLite componentry you are referring to an integrated part of the BigFix Relay build?

AlanM 2016-03-09 00:18:58 UTC #7

Its part of the product yes.

zpt8mjs 2016-03-09 14:24:28 UTC #8

Could this also be impacting the algorithm used to manage content in the relay cache?

We have a few different relay configurations with cache sizes that are small, medium and large. I would expect the oldest ‘LastAccessed’ time be older in the large cache than in the medium or small cache. That is to say that large caches should be able to hold data for a longer duration, but that is not the case, they all seem to have the same oldest ‘Last Accessed’ time according to the DownloadCache.db