Re: [sqlite] An interesting (strange) issue with selects

On 30/06/2012 8:22 PM, Kees Nuyt wrote: Mind you, there is a difference between the "special folders" (=symbolic path names) : AppDataFolder = Full path to the Roaming folder for the current user and CommonAppDataFolder = Full path to application data for all users. and

Re: [sqlite] An interesting (strange) issue with selects [solved]

Am 2012-06-30 12:06, schrieb Dennis Volodomanov: On 30/06/2012 7:47 PM, Marcus Grimm wrote: Here is another theory: Maybe you run into an issue with Windows 7 Virtualization, I did run into a similar effect with the registry access/write sometime go - mainly with Windows 7 Home Editions, by

Re: [sqlite] An interesting (strange) issue with selects

On Sat, 30 Jun 2012 18:32:58 +1000, Dennis Volodomanov wrote: > On 30/06/2012 12:57 PM, Kees Nuyt wrote: >> >> Is the database file in a protected folder (that is, "\Program Files", >> or somewhere in the Windows system software tree) ? >> >> It shouldn't be. Data belongs

Re: [sqlite] An interesting (strange) issue with selects [solved]

On 30/06/2012 7:47 PM, Marcus Grimm wrote: Here is another theory: Maybe you run into an issue with Windows 7 Virtualization, I did run into a similar effect with the registry access/write sometime go - mainly with Windows 7 Home Editions, by reading this:

Re: [sqlite] An interesting (strange) issue with selects

Here is another theory: Maybe you run into an issue with Windows 7 Virtualization, I did run into a similar effect with the registry access/write sometime go - mainly with Windows 7 Home Editions, by reading this: http://support.microsoft.com/kb/927387/EN-US It suggests that something similar

Re: [sqlite] An interesting (strange) issue with selects

On 30/06/2012 12:57 PM, Kees Nuyt wrote: Is the database file in a protected folder (that is, "\Program Files", or somewhere in the Windows system software tree) ? It shouldn't be. Data belongs somewhere else. Either in your userprofile/appdata or in a completely separate dirtree that

Re: [sqlite] An interesting (strange) issue with selects

On Sat, 30 Jun 2012 00:54:44 +1000, Dennis Volodomanov wrote: >On 30/06/2012 12:19 AM, Black, Michael (IS) wrote: >> >> It persists across a reboot? >> >> You can create a database, delete it, reboot, and your app will still >> see the original table? >> >> All I can say is

Re: [sqlite] An interesting (strange) issue with selects

On 30/06/2012 10:32 AM, Donald Griggs wrote: Regarding: Could it be that the .ext is used by the OS or other apps with some caching scheme? Well, this symptom is so amazingly strange, it undeniably belongs in the Ext Files. (To those outside the U.S -- this is just a joke on

Re: [sqlite] An interesting (strange) issue with selects

Regarding: > Could it be that the .ext is used by the OS or other apps with some > caching scheme? Well, this symptom is so amazingly strange, it undeniably belongs in the Ext Files. (To those outside the U.S -- this is just a joke on http://en.wikipedia.org/wiki/The_X-Files )

Re: [sqlite] An interesting (strange) issue with selects

Could it be that the .ext is used by the OS or other apps with some caching scheme? Try different extension... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An interesting (strange) issue with selects

Never heard about tunnelling before this, but I tried to turn it off and it has no effect. I've also (numerously) deleted the whole folder and created it from scratch and I'd still get that contents. Interestingly, I now keep getting a different contents than before - it's now from the last

Re: [sqlite] An interesting (strange) issue with selects

On 29 Jun 2012, at 3:54pm, Dennis Volodomanov wrote: > Most likely - it is my box that's causing this. Unless SQLite does any sort > of real low-level disk access, bypassing standard OS, then it's unlikely that > it somehow caused this to happen, but it would be good to

Re: [sqlite] An interesting (strange) issue with selects

...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Joel Lucsy [jjlu...@gmail.com] Sent: Friday, June 29, 2012 10:50 AM To: i...@psunrise.com; General Discussion of SQLite Database Subject: EXT :Re: [sqlite] An interesting (strange) issue with selects There is a "bug" that I've

Re: [sqlite] An interesting (strange) issue with selects

There is a "bug" that I've read about on a Windows machines sporting the NTFS filesystem that when a file is deleted and recreated within a certain period of time, the original file is retrieved rather than a new one. On Fri, Jun 29, 2012 at 10:54 AM, Dennis Volodomanov wrote:

Re: [sqlite] An interesting (strange) issue with selects

On 30/06/2012 12:19 AM, Black, Michael (IS) wrote: It persists across a reboot? You can create a database, delete it, reboot, and your app will still see the original table? All I can say is wow...your system is really hosed. Even anti-virus shouldn't cause that. This would infer some

Re: [sqlite] An interesting (strange) issue with selects

: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Dennis Volodomanov [i...@psunrise.com] Sent: Friday, June 29, 2012 9:19 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] An interesting (strange) issue with selects To further confuse things: I've

Re: [sqlite] An interesting (strange) issue with selects

ite Database Subject: EXT :Re: [sqlite] An interesting (strange) issue with selects Michael, It works using the shell with one simple table, so I'm investigating this further to see if I can get this to break using the shell (by adding pragmas, triggers, indexes, from my code). This is on a new

Re: [sqlite] An interesting (strange) issue with selects

To further confuse things: I've deleted all files from that old folder. Then using the shell: "sqlite3 mydb.ext" and ".dump" - shows the usual rubbish. The interesting bit here now is that there is no "mydb.ext" file in that folder (checked using a second command prompt), but there are

Re: [sqlite] An interesting (strange) issue with selects

- *From:* sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Dennis Volodomanov [i...@psunrise.com] *Sent:* Friday, June 29, 2012 6:47 AM *To:* General Discussion of SQLite Database *Subject:* EXT :Re: [sqlite] An interesting (strange) issue with s

Re: [sqlite] An interesting (strange) issue with selects

On 29/06/2012 9:52 PM, Simon Slavin wrote: When you're deleting the database file, look for any other files in the same folder with names that start with "mydbname" and anything after that, including any extension. (Marcus's theory) Tell us what they're called. Make sure you're quitting the

Re: [sqlite] An interesting (strange) issue with selects

n...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Dennis Volodomanov [i...@psunrise.com] Sent: Friday, June 29, 2012 6:47 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] An interesting (strange) issue with selects Ok, tried using the shell and the result is

Re: [sqlite] An interesting (strange) issue with selects

On 29 Jun 2012, at 12:47pm, Dennis Volodomanov wrote: > Ok, tried using the shell and the result is the same as using my app. > > What I did is: > > 1) delete the database file ("mydbname.ext") from the DOS prompt, make sure > the file is not there > 2) run "sqlite3

Re: [sqlite] An interesting (strange) issue with selects

On 29/06/2012 9:45 PM, Marcus Grimm wrote: Just a quess: Are you using wal mode ? And how to you "delete" the DB ? Are you removing also any journal or -wal files (if any) ? Yes, WAL mode and I do clear out all files. Dennis ___ sqlite-users

Re: [sqlite] An interesting (strange) issue with selects

Ok, tried using the shell and the result is the same as using my app. What I did is: 1) delete the database file ("mydbname.ext") from the DOS prompt, make sure the file is not there 2) run "sqlite3 .\mydbname.ext" from within that same folder, same DOS prompt 3) type .dump 4) see entries in

Re: [sqlite] An interesting (strange) issue with selects

On 29.06.2012 13:42, Dennis Volodomanov wrote: On 29/06/2012 9:25 PM, Simon Slavin wrote: Your app or the shell tool is running while you delete the file, or do you quit, delete, then restart them ? When you specify the database file to open are you specifying a full path, from the 'C:\'

Re: [sqlite] An interesting (strange) issue with selects

On 29/06/2012 9:25 PM, Simon Slavin wrote: Your app or the shell tool is running while you delete the file, or do you quit, delete, then restart them ? When you specify the database file to open are you specifying a full path, from the 'C:\' on down, or are you relying on some default folder

Re: [sqlite] An interesting (strange) issue with selects

On 29 Jun 2012, at 11:57am, Dennis Volodomanov wrote: > What I found is that after I delete the database file (using Windows explorer > or from the command-line, doesn't matter - this is all on Windows 7 x64) and > then let my application re-create it as normal, it already

Re: [sqlite] An interesting (strange) issue with selects

Ok, back on this topic. I've discovered one issue that is possibly the cause of this whole weirdness, but I can't explain what's going on. Perhaps someone has seen this happen. What I found is that after I delete the database file (using Windows explorer or from the command-line, doesn't

Re: [sqlite] An interesting (strange) issue with selects

On Sat, Jun 23, 2012 at 10:36 PM, Dennis Volodomanov wrote: > On 24/06/2012 12:29 PM, Pavel Ivanov wrote: >> >> AFAIK, checkpoints are application-specific, but SQLite prohibits >> second writer until first one committed its transaction and released >> database lock. So there

Re: [sqlite] An interesting (strange) issue with selects

On 24/06/2012 12:55 PM, Simon Slavin wrote: Depending on the order and timing of how the two threads/processes run, something will eventually happen to your second writer. It will probably reach whatever timeout you've set (which defaults to zero) and then return a result code indicating

Re: [sqlite] An interesting (strange) issue with selects

On 24 Jun 2012, at 3:36am, Dennis Volodomanov wrote: > On 24/06/2012 12:29 PM, Pavel Ivanov wrote: >> AFAIK, checkpoints are application-specific, but SQLite prohibits >> second writer until first one committed its transaction and released >> database lock. So there can't be

Re: [sqlite] An interesting (strange) issue with selects

On 24/06/2012 12:29 PM, Pavel Ivanov wrote: AFAIK, checkpoints are application-specific, but SQLite prohibits second writer until first one committed its transaction and released database lock. So there can't be such thing as "two writers, both writing to the same DB". If one writer writes,

Re: [sqlite] An interesting (strange) issue with selects

On Sat, Jun 23, 2012 at 10:18 PM, Dennis Volodomanov wrote: > It does raise an interesting question though - how is this handled in SQLite > internally? When there are two writers, both writing to the same DB (WAL > mode) and one of them crashes before reaching a checkpoint,

Re: [sqlite] An interesting (strange) issue with selects

On 24/06/2012 11:38 AM, Pavel Ivanov wrote: Such thing shouldn't ever happen, otherwise SQLite has a serious bug. Pavel It could be just my code of course. I guess I need to write a simple console app that simulates this to see if this guess is valid or not in the first place. It does

Re: [sqlite] An interesting (strange) issue with selects

On Sat, Jun 23, 2012 at 9:21 PM, Dennis Volodomanov wrote: > On 22/06/2012 9:48 AM, Dennis Volodomanov wrote: >> >> I'll see if the new compilation options still make this happen, but it >> takes a couple of hours for each test due to data volume and I'd need to run >> a few

Re: [sqlite] An interesting (strange) issue with selects

On 22/06/2012 9:48 AM, Dennis Volodomanov wrote: I'll see if the new compilation options still make this happen, but it takes a couple of hours for each test due to data volume and I'd need to run a few tests (unless it occurs right away of course). I'll post back. This hasn't occurred

Re: [sqlite] An interesting (strange) issue with selects

On 22/06/2012 9:33 AM, Pavel Ivanov wrote: I believe result of integrity_check won't depend on compilation flags, at least not on those you define. Maybe there's some bug surfacing when you use those compilation flags... Pavel I've also removed all transactions from the latest code. There

Re: [sqlite] An interesting (strange) issue with selects

On Thu, Jun 21, 2012 at 7:21 PM, Dennis Volodomanov wrote: > On 22/06/2012 2:15 AM, Pavel Ivanov wrote: >> >> Then it should be okay to do such queries concurrently. So you are saying >> that two SELECTs you initially showed us give contradicting results from >> your

Re: [sqlite] An interesting (strange) issue with selects

On 22/06/2012 2:15 AM, Pavel Ivanov wrote: Then it should be okay to do such queries concurrently. So you are saying that two SELECTs you initially showed us give contradicting results from your application and work as expected from sqlite3 shell, right? The only ideas I have left to try are

Re: [sqlite] An interesting (strange) issue with selects

On Thu, Jun 21, 2012 at 11:48 AM, Dennis Volodomanov wrote: > On 22/06/2012 1:37 AM, Dennis Volodomanov wrote: >> >> On 22/06/2012 1:29 AM, Pavel Ivanov wrote: >>> >>> Do you have mutex surrounding statement execution in these threads? You >>> should use it or compile with

Re: [sqlite] An interesting (strange) issue with selects

On 22/06/2012 1:37 AM, Dennis Volodomanov wrote: On 22/06/2012 1:29 AM, Pavel Ivanov wrote: Do you have mutex surrounding statement execution in these threads? You should use it or compile with SQLITE_THREADSAFE=1. Pavel No, I don't have mutexes for those, as I assumed that THREADSAFE=2

Re: [sqlite] An interesting (strange) issue with selects

On Thu, Jun 21, 2012 at 11:37 AM, Dennis Volodomanov wrote: > On 22/06/2012 1:29 AM, Pavel Ivanov wrote: >> >> Do you have mutex surrounding statement execution in these threads? You >> should use it or compile with SQLITE_THREADSAFE=1. Pavel > > > No, I don't have mutexes for

Re: [sqlite] An interesting (strange) issue with selects

On 22/06/2012 1:29 AM, Pavel Ivanov wrote: Do you have mutex surrounding statement execution in these threads? You should use it or compile with SQLITE_THREADSAFE=1. Pavel No, I don't have mutexes for those, as I assumed that THREADSAFE=2 would protect them. I'm not clear about the exact

Re: [sqlite] An interesting (strange) issue with selects

On Thu, Jun 21, 2012 at 11:02 AM, Dennis Volodomanov wrote: > On 22/06/2012 12:57 AM, Pavel Ivanov wrote: >> >> OK. And now when you execute the original statements (SELECT COUNT(...) >> and SELECT ... WHERE ColC = 0) in SQLiteStudio and sqlite3 shell you still >> get the same

Re: [sqlite] An interesting (strange) issue with selects

On 21 Jun 2012, at 3:22pm, Dennis Volodomanov wrote: > Strangely enough (albeit expected), sqlite3 shell returns the expected > results (e.g. 1, 1 and "integer|0") for the queries. The "integer|64" could > be an SQLiteStudio-specific thing, I don't know. > > I'm opening

Re: [sqlite] An interesting (strange) issue with selects

On 22/06/2012 12:57 AM, Pavel Ivanov wrote: OK. And now when you execute the original statements (SELECT COUNT(...) and SELECT ... WHERE ColC = 0) in SQLiteStudio and sqlite3 shell you still get the same results? 1 in the first statement and no rows in the second one? Pavel No, I get

Re: [sqlite] An interesting (strange) issue with selects

On Thu, Jun 21, 2012 at 10:37 AM, Dennis Volodomanov wrote: > On 22/06/2012 12:30 AM, Pavel Ivanov wrote: >> >> Maybe SQLiteStudio, sqlite3 shell and your app don't use the same database >> file? I don't think there's any sane reason for SQLiteStudio to convert 0 to >> 64.

Re: [sqlite] An interesting (strange) issue with selects

On 22/06/2012 12:30 AM, Pavel Ivanov wrote: Maybe SQLiteStudio, sqlite3 shell and your app don't use the same database file? I don't think there's any sane reason for SQLiteStudio to convert 0 to 64. Pavel On Thu, Jun 21, 2012 at 10:22 AM, Dennis Volodomanov wrote: No,

Re: [sqlite] An interesting (strange) issue with selects

> Strangely enough (albeit expected), sqlite3 shell returns the expected > results (e.g. 1, 1 and "integer|0") for the queries. The "integer|64" could > be an SQLiteStudio-specific thing, I don't know. Maybe SQLiteStudio, sqlite3 shell and your app don't use the same database file? I don't think

Re: [sqlite] An interesting (strange) issue with selects

On 22/06/2012 12:00 AM, Pavel Ivanov wrote: You apparently executed above query on different dataset than you initially posted. 64 is not something quote(ColA) can return when column contains integer value 1 (and I guess Richard meant you should execute that query for ColC, not ColA). So

Re: [sqlite] An interesting (strange) issue with selects

of SQLite Database Subject: EXT :Re: [sqlite] An interesting (strange) issue with selects On 22/06/2012 12:02 AM, Black, Michael (IS) wrote: > > Are you multi-threaded? > > It sounds like the database is being changed during your run...how is > that being done? Inside your program? >

Re: [sqlite] An interesting (strange) issue with selects

On 22/06/2012 12:02 AM, Black, Michael (IS) wrote: Are you multi-threaded? It sounds like the database is being changed during your run...how is that being done? Inside your program? Are any deletes being done? Yes, the application is multi-threaded, but at this point, there's only

Re: [sqlite] An interesting (strange) issue with selects

Are you multi-threaded? It sounds like the database is being changed during your run...how is that being done? Inside your program? Are any deletes being done? Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman

Re: [sqlite] An interesting (strange) issue with selects

On Thu, Jun 21, 2012 at 9:38 AM, Dennis Volodomanov wrote: > On 21/06/2012 11:23 PM, Richard Hipp wrote: >> >> Looks like your data is a string:  ' 0'  -  that is a space followed by >> ascii '0'.  That is different from a numeric 0, so the second query should >> return zero

Re: [sqlite] An interesting (strange) issue with selects

tu je cela seria http://www.jamesgunn.com/pg-porn/ stk je vlavo druhe zospodu On Thu, Jun 21, 2012 at 11:38:50PM +1000 Dennis Volodomanov wrote: > On 21/06/2012 11:23 PM, Richard Hipp wrote: > >Looks like your data is a string: ' 0' - that is a space > >followed by ascii '0'. That is

Re: [sqlite] An interesting (strange) issue with selects

On 21/06/2012 11:23 PM, Richard Hipp wrote: Looks like your data is a string: ' 0' - that is a space followed by ascii '0'. That is different from a numeric 0, so the second query should return zero rows. What does this show: SELECT typeof(ColA), quote(ColA) FROM

Re: [sqlite] An interesting (strange) issue with selects

On Thu, Jun 21, 2012 at 9:15 AM, Dennis Volodomanov wrote: > Hello all, > > I've been using SQLite for quite a few years, but have just recently > started exploring WAL mode (may or may not be related to WAL) and I'm > experiencing an interesting issue that perhaps is known to

Re: [sqlite] An interesting (strange) issue with selects

> Now the problem - the first SQL returns SQLITE_ROW and 1 as > sqlite3_column_int(), while the second doesn't find any items: > > SELECT COUNT(ColA) FROM TableA; > > SELECT ColA FROM CriteriaItemsToProcess WHERE ColC=0 LIMIT 1; > > The data in the table is like this (that's the only one row): > >

Re: [sqlite] An interesting (strange) issue with selects

On 21/06/2012 11:15 PM, Dennis Volodomanov wrote: SELECT ColA FROM CriteriaItemsToProcess WHERE ColC=0 LIMIT 1; Of course the table name here should be TableA (shortened for convenience). Dennis ___ sqlite-users mailing list

[sqlite] An interesting (strange) issue with selects

Hello all, I've been using SQLite for quite a few years, but have just recently started exploring WAL mode (may or may not be related to WAL) and I'm experiencing an interesting issue that perhaps is known to others, so I've decided to ask for your wisdom. This is the amalgamation 3.7.13.