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

[Dennis Volodomanov]

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

LocalAppDataFolder
= Full path to the folder that contains local
 (nonroaming) applications.

To my understanding, CommonAppDataFolder is virtualized to

\Users\%USER%\AppData\Local\VirtualStore\ProgramData\ ,

which is at least a form of protection. Also, normal users would not be
able to delete files from CommonAppDataFolder , which is another form of
protection.

I haven't hacked Windows for a long time, but my guess is:

1) you are running as administrator,
2) or you (partially) disabled protection.

Which is not the best way to test applications.

Only your installer / uninstaller should touch CommonAppDataFolder .

I suppose the merits and best practices of folder virtualization are
documented on MSDN somewhere.

You are certainly not the first one to bump into this, a popular search
engine gives me  909000  hits on q=windows7+programdata, for example

http://www.codingquestion.info/6732413/deleting-file-from-cprogramdata-in-windows-7-does-not-really-delete-the-file/post



Well, I was going by MSDN and I'm using CSIDL_COMMON_APPDATA which is 
defined by MSDN as: "The file system directory that contains application 
data for all users. A typical path is C:\Documents and Settings\All 
Users\Application Data. This folder is used for application data that is 
not user specific. For example, an application can store a spell-check 
dictionary, a database of clip art, or a log file in the 
CSIDL_COMMON_APPDATA folder. This information will not roam and is 
available to anyone using the computer." It doesn't actually mention 
that this folder is best used for read-only files, but I found a 
reference to this elsewhere.


I think I may be better off using the CSIDL_COMMON_DOCUMENTS, so that's 
what I'll switch to and try next. I can't use a personal folder, since 
the data also needs to be accessed by a service, which won't have user's 
credentials.


Thanks!

   Dennis

___
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 [solved]

[Marcus Grimm]

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 reading this:
http://support.microsoft.com/kb/927387/EN-US

It suggests that something similar can also apply on files in
certain programdata folders.



I think you just nailed it! From that link, Scenario 4 - I go into
that Virtual folder and lo and behold - there's only file there and
it's my app's database! I've killed that and then tried the usual
sqlite shell routine and it's all back to normal (e.g. there is no
data in the database file, which is all correct). Wow :)


Cool... :-)

Boy... isn't that crazy what WIndows occasionally does just to follow
some overzealous security constrains ?

Sometimes I really glorify the good old windows NT / Win2000 times.

Marcus



Thank you for solving this and hopefully this helps someone in the
future facing the same problem - if you are using Windows 7 (Ultimate
in my case) and you see a file which shouldn't be there, check the
link: http://support.microsoft.com/kb/927387/EN-US and see if any of
the scenarios listed there apply to you.

   Dennis

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
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

[Kees Nuyt]

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 somewhere else. Either in your
>> userprofile/appdata or in a completely separate dirtree that Microsoft
>> doesn't try to manage.
>>
>> HTH
>>
>
> No, the database is not in a protected folder, it's in the common 
> appdata folder (ProgramData on Windows7).

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

LocalAppDataFolder
= Full path to the folder that contains local
 (nonroaming) applications. 

To my understanding, CommonAppDataFolder is virtualized to

\Users\%USER%\AppData\Local\VirtualStore\ProgramData\ , 

which is at least a form of protection. Also, normal users would not be
able to delete files from CommonAppDataFolder , which is another form of
protection. 

I haven't hacked Windows for a long time, but my guess is:

1) you are running as administrator, 
2) or you (partially) disabled protection.

Which is not the best way to test applications.

Only your installer / uninstaller should touch CommonAppDataFolder .

I suppose the merits and best practices of folder virtualization are
documented on MSDN somewhere.

You are certainly not the first one to bump into this, a popular search
engine gives me  909000  hits on q=windows7+programdata, for example

http://www.codingquestion.info/6732413/deleting-file-from-cprogramdata-in-windows-7-does-not-really-delete-the-file/post

-- 
Regards,

Kees Nuyt

___
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 [solved]

[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 reading this:
http://support.microsoft.com/kb/927387/EN-US

It suggests that something similar can also apply on files in
certain programdata folders.



I think you just nailed it! From that link, Scenario 4 - I go into that 
Virtual folder and lo and behold - there's only file there and it's my 
app's database! I've killed that and then tried the usual sqlite shell 
routine and it's all back to normal (e.g. there is no data in the 
database file, which is all correct). Wow :)


Thank you for solving this and hopefully this helps someone in the 
future facing the same problem - if you are using Windows 7 (Ultimate in 
my case) and you see a file which shouldn't be there, check the link: 
http://support.microsoft.com/kb/927387/EN-US and see if any of the 
scenarios listed there apply to you.


   Dennis

___
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

[Marcus Grimm]

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 can also apply on files in
certain programdata folders.

Marcus

Am 2012-06-30 10:32, schrieb Dennis Volodomanov:

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 
Microsoft

doesn't try to manage.

HTH



No, the database is not in a protected folder, it's in the common
appdata folder (ProgramData on Windows7).

Dennis
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
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

[Dennis Volodomanov]

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 Microsoft
doesn't try to manage.

HTH



No, the database is not in a protected folder, it's in the common 
appdata folder (ProgramData on Windows7).


Dennis
___
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

[Kees Nuyt]

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 wow...your system is really hosed.
>>
>> Even anti-virus shouldn't cause that.  This would infer some sort of 
>> caching that is semi-permanent.
>>
>> Have you got a 2nd computer you can test this on?
>>
>> Would you be willing to share your app so others can check this?  As 
>> "House" used to say..."interesting".
>>
>>
>
>Not only my app, the sqlite shell will see it too. Regarding my second 
>message - I was talking about this same screwed-up folder, so yes, I can 
>create a new db in a new folder and it's fine. It's only when I try 
>anything in this folder that things go amok (at least it's localized to 
>this folder so far).
>
>I'll do testing on another machine and I'll do a full chkdsk here as 
>well tomorrow.
>
>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 
>rule this out somehow.
>
>I can share the app (not the source of course), sure, but I don't know 
>if that'll help in any way?


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 Microsoft
doesn't try to manage.

HTH

-- 
Regards,

Kees Nuyt

___
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

[Dennis Volodomanov]

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
http://en.wikipedia.org/wiki/The_X-Files
)



The real extension I use is ".krr", so it's unique, as far as I know, 
but thanks for the thought! And yes, I wouldn't be surprised if Moulder 
showed up at the door :)


I ran a full disk check (/f /v /r /x), but the issue is still there.

I'm out of ideas at this point and to continue development I'll need to 
hard-code a different path into the app, so that this bogus file is not 
read, but I'd really like to get to the bottom of this.


Imagine this happening on a client/user's machine...

   Dennis

___
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

[Donald Griggs]

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
)

___
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

[Mohd Radzi Ibrahim]

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

[Dennis Volodomanov]

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 attempt of running my app where I stopped it mid-way through debugging.


I suspect that Windows' FS is got a screw lose at this point. Hopefully 
a disk check will uncover this.


My suspicion is that I was debugging and stopped the app. Then I likely 
deleted the DB, as I do that often, but perhaps at this point the wal 
journal or the main db itself got corrupted in the file system table. 
And perhaps now when I try to create this DB in this folder, the OS is 
pulling out this old journal (or the main db) instead of the new one. I 
know it doesn't sound very plausible, but it's the best guess I've got 
at the moment :) I mean, to have executed "sqlite3 mydb.ext" and not see 
"mydb.ext" appear in a folder, but to actually have contents coming from 
a .dump, is certainly the OS doing something?


   Dennis

___
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

[Simon Slavin]

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 rule this out 
> somehow.

SQLite doesn't do anything weird like bypassing the file system.  I agree with 
everyone that there's something weird about your OS or hardware.  Run all the 
checks you can find.

Simon.

___
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

[Black, Michael (IS)]

Not supposed to be the contentsjust attributes...

Also note the 15-second default time window.  If you do it faster than 15 
seconds you won't see the effect.



http://support.microsoft.com/kb/172190



http://dfstream.blogspot.com/2012/02/file-system-tunneling-in-windows.html



Not sure if Windows 7 has it too but I would imagine so.



Can you turn it off and see what happens?



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@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 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 <i...@psunrise.com>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 wow...your system is really hosed.
>>
>> Even anti-virus shouldn't cause that.  This would infer some sort of
>> caching that is semi-permanent.
>>
>> Have you got a 2nd computer you can test this on?
>>
>> Would you be willing to share your app so others can check this?  As
>> "House" used to say..."interesting".
>>
>>
>>
> Not only my app, the sqlite shell will see it too. Regarding my second
> message - I was talking about this same screwed-up folder, so yes, I can
> create a new db in a new folder and it's fine. It's only when I try
> anything in this folder that things go amok (at least it's localized to
> this folder so far).
>
> I'll do testing on another machine and I'll do a full chkdsk here as well
> tomorrow.
>
> 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
> rule this out somehow.
>
> I can share the app (not the source of course), sure, but I don't know if
> that'll help in any way?
>
>
>   Dennis
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users<http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users<http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users%3Chttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
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

[Joel Lucsy]

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:

> 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 sort of
>> caching that is semi-permanent.
>>
>> Have you got a 2nd computer you can test this on?
>>
>> Would you be willing to share your app so others can check this?  As
>> "House" used to say..."interesting".
>>
>>
>>
> Not only my app, the sqlite shell will see it too. Regarding my second
> message - I was talking about this same screwed-up folder, so yes, I can
> create a new db in a new folder and it's fine. It's only when I try
> anything in this folder that things go amok (at least it's localized to
> this folder so far).
>
> I'll do testing on another machine and I'll do a full chkdsk here as well
> tomorrow.
>
> 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
> rule this out somehow.
>
> I can share the app (not the source of course), sure, but I don't know if
> that'll help in any way?
>
>
>   Dennis
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
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

[Dennis Volodomanov]

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 sort of 
caching that is semi-permanent.


Have you got a 2nd computer you can test this on?

Would you be willing to share your app so others can check this?  As 
"House" used to say..."interesting".





Not only my app, the sqlite shell will see it too. Regarding my second 
message - I was talking about this same screwed-up folder, so yes, I can 
create a new db in a new folder and it's fine. It's only when I try 
anything in this folder that things go amok (at least it's localized to 
this folder so far).


I'll do testing on another machine and I'll do a full chkdsk here as 
well tomorrow.


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 
rule this out somehow.


I can share the app (not the source of course), sure, but I don't know 
if that'll help in any way?


   Dennis

___
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

[Black, Michael (IS)]

You need to check your file system.



When you run the shell doing this on a new, empty directory (this is using the 
shell from the website)



sqlite3 mydb.ext



In another window you should NOT see anything.



then after doing .dump you should see a 0-length mydb.ext file appear.



.quit -- your zero-length mydb.ext should still be there.



How are you getting wal mode without asking for it?





Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: 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 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
"mydb.ext-shm" and "mydb.ext-wal" files there! Once I ".quit" there are
no "mydb*.*" at all.

Perhaps my drive or fat are dying or something?

Dennis

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
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

[Black, Michael (IS)]

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 sort of caching 
that is semi-permanent.



Have you got a 2nd computer you can test this on?



Would you be willing to share your app so others can check this?  As "House" 
used to say..."interesting".



I've got XP x64 and Windows 7 32-bit I can test.







Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Dennis Volodomanov [i...@psunrise.com]
Sent: Friday, June 29, 2012 8:57 AM
To: General Discussion of SQLite 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 file
in a different folder to the other test.

The OS is Windows 7 x64, SQLite 3.7.13 from the site. Local file,
persists across a reboot. Not sure about your question regarding 3rd
party software - there's a lot of software that I run, of course?
Antivirus, hmm, maybe... Need to try and get it to happen using the shell.

Dennis


On 29/06/2012 10:35 PM, Black, Michael (IS) wrote:
>
> Care to show all of your steps?  Not that my BS flag is waving but
> you're correct that this is very odd.
>
> What OS?
>
> What version sqlite?
>
> Shell from website or did you compile?
>
> Local file?
>
> Does it persist across a reboot?
>
> Are you running ANY 3rd party software?
>
> Try uninstalling your anti-virus.
>
> D:\>mkdir dennis
>
> D:\>cd dennis
>
> D:\dennis>dir
>  Volume in drive D is Apps
>  Volume Serial Number is C43B-2252
>
>  Directory of D:\dennis
>
> 06/29/2012  07:24 AM  .
> 06/29/2012  07:24 AM  ..
>0 File(s)  0 bytes
>2 Dir(s)  202,760,232,960 bytes free
>
> D:\dennis>sqlite3 mydbname.ext
> SQLite version 3.7.9 2011-11-01 00:52:41
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> pragma journal_mode=wal;
> wal
> sqlite> create table t(a);
> sqlite> insert into t values(1);
> sqlite> .quit
>
> D:\dennis>dir
>  Volume in drive D is Apps
>  Volume Serial Number is C43B-2252
>
>  Directory of D:\dennis
>
> 06/29/2012  07:25 AM  .
> 06/29/2012  07:25 AM  ..
> 06/29/2012  07:25 AM 2,048 mydbname.ext
>1 File(s)  2,048 bytes
>2 Dir(s)  202,760,228,864 bytes free
>
> D:\dennis>del mydbname.ext
>
> D:\dennis>sqlite3 mydbname.ext
> SQLite version 3.7.9 2011-11-01 00:52:41
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> .dump
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> COMMIT;
> sqlite> .quit
>
> D:\dennis>dir
>  Volume in drive D is Apps
>  Volume Serial Number is C43B-2252
>
>  Directory of D:\dennis
>
> 06/29/2012  07:25 AM  .
> 06/29/2012  07:25 AM  ..
> 06/29/2012  07:25 AM 0 mydbname.ext
>1 File(s)  0 bytes
>2 Dir(s)  202,760,232,960 bytes free
>
> D:\dennis>del mydbname.ext
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>
> 
> *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 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 this DB, which should be impossible, since the file
> wasn't there and I've issued no commands using the shell tool
>
> Weird?
>
> Dennis
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
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

[Dennis Volodomanov]

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 
"mydb.ext-shm" and "mydb.ext-wal" files there! Once I ".quit" there are 
no "mydb*.*" at all.


Perhaps my drive or fat are dying or something?

   Dennis

___
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

[Dennis Volodomanov]

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 file 
in a different folder to the other test.


The OS is Windows 7 x64, SQLite 3.7.13 from the site. Local file, 
persists across a reboot. Not sure about your question regarding 3rd 
party software - there's a lot of software that I run, of course? 
Antivirus, hmm, maybe... Need to try and get it to happen using the shell.


   Dennis


On 29/06/2012 10:35 PM, Black, Michael (IS) wrote:


Care to show all of your steps?  Not that my BS flag is waving but 
you're correct that this is very odd.


What OS?

What version sqlite?

Shell from website or did you compile?

Local file?

Does it persist across a reboot?

Are you running ANY 3rd party software?

Try uninstalling your anti-virus.

D:\>mkdir dennis

D:\>cd dennis

D:\dennis>dir
 Volume in drive D is Apps
 Volume Serial Number is C43B-2252

 Directory of D:\dennis

06/29/2012  07:24 AM  .
06/29/2012  07:24 AM  ..
   0 File(s)  0 bytes
   2 Dir(s)  202,760,232,960 bytes free

D:\dennis>sqlite3 mydbname.ext
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma journal_mode=wal;
wal
sqlite> create table t(a);
sqlite> insert into t values(1);
sqlite> .quit

D:\dennis>dir
 Volume in drive D is Apps
 Volume Serial Number is C43B-2252

 Directory of D:\dennis

06/29/2012  07:25 AM  .
06/29/2012  07:25 AM  ..
06/29/2012  07:25 AM 2,048 mydbname.ext
   1 File(s)  2,048 bytes
   2 Dir(s)  202,760,228,864 bytes free

D:\dennis>del mydbname.ext

D:\dennis>sqlite3 mydbname.ext
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;
sqlite> .quit

D:\dennis>dir
 Volume in drive D is Apps
 Volume Serial Number is C43B-2252

 Directory of D:\dennis

06/29/2012  07:25 AM  .
06/29/2012  07:25 AM  ..
06/29/2012  07:25 AM 0 mydbname.ext
   1 File(s)  0 bytes
   2 Dir(s)  202,760,232,960 bytes free

D:\dennis>del mydbname.ext

Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


*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 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 this DB, which should be impossible, since the file
wasn't there and I've issued no commands using the shell tool

Weird?

Dennis

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
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

[Dennis Volodomanov]

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 shell tool with '.quit', not just CTRL-C ing out 
of it.

First try the above two.  If they don't explain what's wrong,

Using the shell tool, delete one of the rows which has magically appeared.  Or even 
the whole table.  Make another row or table with different data in.  Then repeat 
the quit & delete procedure.

When you go back into the shell tool, do you get the same old bad data or the 
new bad data ?

Simon.


Yes, of course all files are removed, as far as the "dir" command is 
concerned (with hidden files/folders shown). And yes, I'm using ".quit".


I've tried deleting that row, but the stuffed up DB contains a trigger 
that I have long removed from my code, which prevents that (because it 
relies on something that has also been long removed from the db's 
structure). So, I've inserted a new row using the shell tool, then 
".quit", deleted all files, ran my app, which created a supposedly empty 
database (stepping through the code). After that quit my app, open the 
DB using the shell and my new row was there - eventhough it contains 
impossible data, which would have never been entered using the app 
(proving that the app is not inserting data on its own). So, it's the 
new bad data :)


   Dennis

___
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

[Black, Michael (IS)]

Care to show all of your steps?  Not that my BS flag is waving but you're 
correct that this is very odd.





What OS?

What version sqlite?

Shell from website or did you compile?

Local file?

Does it persist across a reboot?

Are you running ANY 3rd party software?

Try uninstalling your anti-virus.



D:\>mkdir dennis

D:\>cd dennis

D:\dennis>dir
 Volume in drive D is Apps
 Volume Serial Number is C43B-2252

 Directory of D:\dennis

06/29/2012  07:24 AM  .
06/29/2012  07:24 AM  ..
   0 File(s)  0 bytes
   2 Dir(s)  202,760,232,960 bytes free

D:\dennis>sqlite3 mydbname.ext
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> pragma journal_mode=wal;
wal
sqlite> create table t(a);
sqlite> insert into t values(1);
sqlite> .quit

D:\dennis>dir
 Volume in drive D is Apps
 Volume Serial Number is C43B-2252

 Directory of D:\dennis

06/29/2012  07:25 AM  .
06/29/2012  07:25 AM  ..
06/29/2012  07:25 AM 2,048 mydbname.ext
   1 File(s)  2,048 bytes
   2 Dir(s)  202,760,228,864 bytes free

D:\dennis>del mydbname.ext

D:\dennis>sqlite3 mydbname.ext
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;
sqlite> .quit

D:\dennis>dir
 Volume in drive D is Apps
 Volume Serial Number is C43B-2252

 Directory of D:\dennis

06/29/2012  07:25 AM  .
06/29/2012  07:25 AM  ..
06/29/2012  07:25 AM 0 mydbname.ext
   1 File(s)  0 bytes
   2 Dir(s)  202,760,232,960 bytes free

D:\dennis>del mydbname.ext



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


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 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 this DB, which should be impossible, since the file
wasn't there and I've issued no commands using the shell tool

Weird?

Dennis

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
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

[Simon Slavin]

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 .\mydbname.ext" from within that same folder, same DOS prompt
> 3) type .dump
> 4) see entries in this DB, which should be impossible, since the file wasn't 
> there and I've issued no commands using the shell tool

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 shell tool with '.quit', not just CTRL-C ing out 
of it.

First try the above two.  If they don't explain what's wrong,

Using the shell tool, delete one of the rows which has magically appeared.  Or 
even the whole table.  Make another row or table with different data in.  Then 
repeat the quit & delete procedure.

When you go back into the shell tool, do you get the same old bad data or the 
new bad data ?

Simon.
___
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

[Dennis Volodomanov]

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 mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Dennis Volodomanov]

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 this DB, which should be impossible, since the file 
wasn't there and I've issued no commands using the shell tool


Weird?

   Dennis

___
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

[Marcus Grimm]

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:\' on down, or are you relying
on some default folder being specified by something ? The usual trick is that 
your application is opening a database
from one folder, the shell tool is opening the database from another folder, 
and the one you're deleting is one or
none of them. Make sure everything specifies the full path, just until you've 
figured out this problem.


It's all fully-qualified paths, no default folders :) And yes, the db is 
deleted when everything is closed (and checked
using Task Manager).


It's probably best to start off by assuming that the SQLite shell tool does 
exactly what it's documented to do 100% of
the time. There are thousands of users of it out there and a bug like you 
describe would have been reported to this
list many times by now.

You should be able to use a database file as a messaging system. Put a row in 
it using the shell tool, then read it
out using your app and make sure it has the right value. Then put a row in it 
using your app and read it out using the
shell tool. If that's not working, they're opening different files, your app is 
buggy, or you have a hardware failure
of some sort.




What's weird here (I'll just re-instate it) is that when a DB is created from 
within the app, it seems to inherit some
entries from an already deleted DB. I'm not sure how that's possible, but it 
appears to be what I'm seeing (if the .dump
command in sqlite shell is reporting the truth, which I do assume it does).


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) ?

Marcus



I'll try to reproduce this using the shell tool (creating the DB) and see what 
happens.

Dennis

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



--
Marcus Grimm
MedCom GmbH Darmstadt, Rundeturmstr. 12, 64283 Darmstadt
Tel: +49(0)6151-95147-10
Fax: +49(0)6151-95147-20
web: www.medcom-online.de
--

___
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

[Dennis Volodomanov]

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 being specified by 
something ?  The usual trick is that your application is opening a database 
from one folder, the shell tool is opening the database from another folder, 
and the one you're deleting is one or none of them.  Make sure everything 
specifies the full path, just until you've figured out this problem.


It's all fully-qualified paths, no default folders :) And yes, the db is 
deleted when everything is closed (and checked using Task Manager).



It's probably best to start off by assuming that the SQLite shell tool does 
exactly what it's documented to do 100% of the time.  There are thousands of 
users of it out there and a bug like you describe would have been reported to 
this list many times by now.

You should be able to use a database file as a messaging system.  Put a row in 
it using the shell tool, then read it out using your app and make sure it has 
the right value.  Then put a row in it using your app and read it out using the 
shell tool.  If that's not working, they're opening different files, your app 
is buggy, or you have a hardware failure of some sort.




What's weird here (I'll just re-instate it) is that when a DB is created 
from within the app, it seems to inherit some entries from an already 
deleted DB. I'm not sure how that's possible, but it appears to be what 
I'm seeing (if the .dump command in sqlite shell is reporting the truth, 
which I do assume it does).


I'll try to reproduce this using the shell tool (creating the DB) and 
see what happens.


   Dennis

___
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

[Simon Slavin]

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 contains some 
> entries!

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 being specified by 
something ?  The usual trick is that your application is opening a database 
from one folder, the shell tool is opening the database from another folder, 
and the one you're deleting is one or none of them.  Make sure everything 
specifies the full path, just until you've figured out this problem.

> It's like the file is not created from scratch, but rather pulled from some 
> Windows cache, but only parts of it (doesn't make sense, as the integrity 
> check succeeds). So, the file would have all the proper tables, but the .dump 
> command in sqlite's utility would should an INSERT that's impossible to 
> happen as well mid-way through creating tables. I have table creation 
> surrounded by an explicit transaction and it happens before anything else can 
> happen and I've triple-stepped through my code just to make sure I'm not 
> crazy :) Possibly, this weird (can't say corrupt, just weird) file is what's 
> causing the strange SELECTs.
> 
> As a test, I've renamed the db inside my application, so that it's not 
> re-using the filename all the time and this time the .dump showed all the 
> correct CREATE TABLE statements, without any rubbish in between.

It's probably best to start off by assuming that the SQLite shell tool does 
exactly what it's documented to do 100% of the time.  There are thousands of 
users of it out there and a bug like you describe would have been reported to 
this list many times by now.

You should be able to use a database file as a messaging system.  Put a row in 
it using the shell tool, then read it out using your app and make sure it has 
the right value.  Then put a row in it using your app and read it out using the 
shell tool.  If that's not working, they're opening different files, your app 
is buggy, or you have a hardware failure of some sort.

Simon.
___
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

[Dennis Volodomanov]

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 matter - this is all on 
Windows 7 x64) and then let my application re-create it as normal, it 
already contains some entries! It's like the file is not created from 
scratch, but rather pulled from some Windows cache, but only parts of it 
(doesn't make sense, as the integrity check succeeds). So, the file 
would have all the proper tables, but the .dump command in sqlite's 
utility would should an INSERT that's impossible to happen as well 
mid-way through creating tables. I have table creation surrounded by an 
explicit transaction and it happens before anything else can happen and 
I've triple-stepped through my code just to make sure I'm not crazy :) 
Possibly, this weird (can't say corrupt, just weird) file is what's 
causing the strange SELECTs.


As a test, I've renamed the db inside my application, so that it's not 
re-using the filename all the time and this time the .dump showed all 
the correct CREATE TABLE statements, without any rubbish in between.


I don't know where to head at this point.

   Dennis

___
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

[Pavel Ivanov]

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 can't be such thing as "two writers, both
>> writing to the same DB". If one writer writes, another one is locked
>> out and waits. And btw checkpoint cannot be completed if there are
>> some application with transactions that started before last commit to
>> the database was made. Although partial checkpoint is possible in such
>> situation.
>
> Doesn't this suggest, though, that if the first writer crashes during a
> checkpoint, the second writer will be forever locked out? Or is there some
> internal mechanism that takes care of that?

Yes, internally SQLite uses OS-level file locks. When process crashes
or goes away by any other reason OS clears all locks it held, so other
writer sees database unlocked and is able to proceed.

Pavel
___
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

[Dennis Volodomanov]

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 either 'locked' or 'busy'.  As the programmer it's your job to 
be checking your result codes and taking appropriate action, but if your 
application does crash out and you haven't closed your connections properly, 
SQLite will notice this the next time it opens the database, and what you'll 
see is everything rolled back to the last COMMITted transaction.


Yes, I do check for those codes and I have loops in the code to re-try 
the statement until it succeeds or too many retries occurs (but this is 
never hit in my case). So, if the db is busy, I Sleep(1) and retry. I'm 
not using the built-in busy handler.



You should be able to test this, using the sqlite3 shell utility tool, 
downloadable from the SQLite download page.  You can open two sessions in two 
windows and try to get one to lock the other out, or crash out of one session 
to see what happens to the other.  Or you could run your own app in one window, 
and use the shell tool to try to mess with your application and make it crash.  
Use a dummy test copy of your database, of course.

There are any number of ways to get SQLite to return error codes.  What has us 
concerned is that you are reporting that you've found a way to make SQLite 
return the wrong result /without/ returning an error code.  That's supposed to 
be impossible.


I'd probably be best off to write a simple program to do this. The idea 
is to have this program write to the DB, do a count and read from the db 
and report what it's reading/writing along with sql result codes. Then, 
run two instances of the app on the same db and crash one of those 
instances at random. I don't think I can do the same using the sqlite3 
shell utility and my main app is too large for constant testing like this.


I also wonder if my increased WAL size has anything to do with it (5000 
pages) - flushing will take longer than default, so there is more chance 
of things going wrong during the flush, I suppose?





I had transactions in my code previously, but I removed them after switching to 
WAL, so at least that's one less worry.

SQLite can only access a database inside a transaction (not only changes, but 
also SELECT).  If you issue a SQL command without having first issued a BEGIN, 
SQLite wraps your command inside a BEGIN … END structure.
I understand that, I meant I had explicit transactions surrounding 
multiple statements.


Dennis

___
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

[Simon Slavin]

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 such thing as "two writers, both
>> writing to the same DB". If one writer writes, another one is locked
>> out and waits. And btw checkpoint cannot be completed if there are
>> some application with transactions that started before last commit to
>> the database was made. Although partial checkpoint is possible in such
>> situation.
> 
> Doesn't this suggest, though, that if the first writer crashes during a 
> checkpoint, the second writer will be forever locked out? Or is there some 
> internal mechanism that takes care of that?

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 either 'locked' or 'busy'.  As the programmer it's your job to 
be checking your result codes and taking appropriate action, but if your 
application does crash out and you haven't closed your connections properly, 
SQLite will notice this the next time it opens the database, and what you'll 
see is everything rolled back to the last COMMITted transaction.

You should be able to test this, using the sqlite3 shell utility tool, 
downloadable from the SQLite download page.  You can open two sessions in two 
windows and try to get one to lock the other out, or crash out of one session 
to see what happens to the other.  Or you could run your own app in one window, 
and use the shell tool to try to mess with your application and make it crash.  
Use a dummy test copy of your database, of course.

There are any number of ways to get SQLite to return error codes.  What has us 
concerned is that you are reporting that you've found a way to make SQLite 
return the wrong result /without/ returning an error code.  That's supposed to 
be impossible.

> I had transactions in my code previously, but I removed them after switching 
> to WAL, so at least that's one less worry.

SQLite can only access a database inside a transaction (not only changes, but 
also SELECT).  If you issue a SQL command without having first issued a BEGIN, 
SQLite wraps your command inside a BEGIN … END structure.

Simon.
___
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

[Dennis Volodomanov]

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, another one is locked
out and waits. And btw checkpoint cannot be completed if there are
some application with transactions that started before last commit to
the database was made. Although partial checkpoint is possible in such
situation.


Doesn't this suggest, though, that if the first writer crashes during a 
checkpoint, the second writer will be forever locked out? Or is there 
some internal mechanism that takes care of that?


I had transactions in my code previously, but I removed them after 
switching to WAL, so at least that's one less worry.


   Dennis

___
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

[Pavel Ivanov]

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, will the second
> writer pick up on that and checkpoint correctly? To put it simply - are
> checkpoints DB-specific or application-specific?

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, another one is locked
out and waits. And btw checkpoint cannot be completed if there are
some application with transactions that started before last commit to
the database was made. Although partial checkpoint is possible in such
situation.

Pavel
___
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

[Dennis Volodomanov]

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 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, will 
the second writer pick up on that and checkpoint correctly? To put it 
simply - are checkpoints DB-specific or application-specific?


   Dennis

___
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

[Pavel Ivanov]

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 tests (unless it occurs right away of course). I'll post back.
>>
>
> This hasn't occurred yet, but I did manage to replicate this in another way
> (using the new compilation options) by stopping (in debug)/crashing the
> second writer process. It appears (albeit hard to say decisively) that the
> entry in the table that was written by the crashed process (and thus it's
> wal is not flushed) is the one that comes up in count, but can't be selected
> by the other process. Does this make sense or is it just a coincidence that
> I'm seeing?

Such thing shouldn't ever happen, otherwise SQLite has a serious bug.

Pavel
___
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

[Dennis Volodomanov]

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 yet, but I did manage to replicate this in another 
way (using the new compilation options) by stopping (in debug)/crashing 
the second writer process. It appears (albeit hard to say decisively) 
that the entry in the table that was written by the crashed process (and 
thus it's wal is not flushed) is the one that comes up in count, but 
can't be selected by the other process. Does this make sense or is it 
just a coincidence that I'm seeing?


Thanks!

   Dennis

___
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

[Dennis Volodomanov]

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 were no 
transactions surrounding these particulars SQL statements, but perhaps 
they were causing some issue elsewhere which in turn lead to some 
strange state of the db - just guessing, of course. The database is 
accessed from multiple processes and multiple threads, so at any given 
time there are at least 3-4 readers and potentially 2 writers.


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.


Thanks!

   Dennis

___
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

[Pavel Ivanov]

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 application and work as expected from sqlite3 shell, right? The only
>> ideas I have left to try are execute "pragma integrity_check" on this
>> database and try to compile without SQLITE_OMIT_DEPRECATED and without
>> SQLITE_ENABLE_STAT3... Pavel
>
>
> Yes, I should've given more details from the start, sorry.
>
> I'll try re-compiling and see if I can reproduce this. The integrity_check
> returns "ok" (from the shell, haven't tried it from the application).

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
___
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

[Dennis Volodomanov]

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 execute "pragma 
integrity_check" on this database and try to compile without 
SQLITE_OMIT_DEPRECATED and without SQLITE_ENABLE_STAT3... Pavel 


Yes, I should've given more details from the start, sorry.

I'll try re-compiling and see if I can reproduce this. The 
integrity_check returns "ok" (from the shell, haven't tried it from the 
application).


   Dennis
___
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

[Pavel Ivanov]

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 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 difference between 1 and 2,
>> having read the docs a few times.
>>
>
> Just to add on to this, each thread opens up its own database connection
> (sqlite3_open_v2), so no threads are sharing sqlite3 handles.

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 execute
"pragma integrity_check" on this database and try to compile without
SQLITE_OMIT_DEPRECATED and without SQLITE_ENABLE_STAT3...

Pavel
___
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

[Dennis Volodomanov]

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 
would protect them. I'm not clear about the exact difference between 1 
and 2, having read the docs a few times.




Just to add on to this, each thread opens up its own database connection 
(sqlite3_open_v2), so no threads are sharing sqlite3 handles.


   Dennis

___
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

[Pavel Ivanov]

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 those, as I assumed that THREADSAFE=2 would
> protect them. I'm not clear about the exact difference between 1 and 2,
> having read the docs a few times.

>From http://www.sqlite.org/compile.html: "SQLITE_THREADSAFE=1 sets the
default threading mode to Serialized. SQLITE_THREADSAFE=2 sets the
default threading mode to Multi-threaded". And from
http://www.sqlite.org/threadsafe.html: "In serialized mode, SQLite can
be safely used by multiple threads with no restriction. In
Multi-thread mode, SQLite can be safely used by multiple threads
provided that no single database connection is used simultaneously in
two or more threads". In other words with SQLITE_THREADSAFE=1 SQLite
itself is responsible for protecting concurrent access to connection
with mutex, with SQLITE_THREADSAFE=2 developer using SQLite is
responsible for that. And you was just lucky not getting application
crash without mutex.


Pavel
___
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

[Dennis Volodomanov]

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 difference between 1 and 2, 
having read the docs a few times.


I think this may be the problem - I'll recompile with 1 and spend a bit 
of time testing. I'll get back with the results in either case.


Thank you all for the help!

   Dennis

___
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

[Pavel Ivanov]

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 results? 1 in the first statement and no rows in the second
>> one? Pavel
>
>
> No, I get correct results in sqlite3 shell now (haven't tried the SS).
>
> What I also failed to mention and which may have something to do with it, is
> that there's another SELECT COUNT() being executed from a different thread,
> which kicks in from time to time. Could that potentially cause any issues?
> So, I have one thread doing SELECT COUNT() and SELECT ... and another thread
> doing just a SELECT COUNT().

Do you have mutex surrounding statement execution in these threads?
You should use it or compile with SQLITE_THREADSAFE=1.


Pavel
___
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

[Simon Slavin]

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 the DB using sqlite3_open_v2() with these flags: 
> SQLITE_OPEN_CREATE|SQLITE_OPEN_READWRITE|SQLITE_OPEN_SHAREDCACHE - could the 
> shared cache play a role in this, along with read uncommitted?
> 
> Given that the result is ok in the shell, I assume it's the way I've compiled 
> the amalgamation (see the flags I've posted earlier) or the way I open it - 
> is there anything peculiar about either of those?

It should be completely impossible to get the results you describe, no matter 
what compilation options or flags you choose for SQLite, how you misuse 
transactions, or what kind of access modes you're using.  It's possible to make 
SQLite give error messages instead of getting the correct results, probably 
related to file locking.  But it's not meant to be possible to make SQLite give 
the wrong results and no error message, no matter how badly you mess things up.

It's possible that you've discovered a bug in SQLite, but the posts I'm seeing 
here, including my own, suspect you're doing something wrong in your use of the 
API: either overwriting memory, or duplicating handles, or something else which 
violates the conventions for using C APIs.

I think we will only be able to get further if you can produce a minimal 
example which demonstrates your problem.  Try making a little program that 
doesn't use threads.  If you can't duplicate the problem like that, try one 
that does use threads.  If you can't make a small program that duplicates your 
problem, it might be something about your big program that's doing it.

Simon.
___
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

[Dennis Volodomanov]

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 correct results in sqlite3 shell now (haven't tried the SS).

What I also failed to mention and which may have something to do with 
it, is that there's another SELECT COUNT() being executed from a 
different thread, which kicks in from time to time. Could that 
potentially cause any issues? So, I have one thread doing SELECT COUNT() 
and SELECT ... and another thread doing just a SELECT COUNT().


   Dennis

___
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

[Pavel Ivanov]

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. Pavel On Thu, Jun 21, 2012 at 10:22 AM, Dennis Volodomanov
>>  wrote:
>
>
> No, it's the same DB - I've got the path double-checked and it's all local
> and both SQLiteStudio and sqlite3 shell are copied into the same folder as
> well. Just to clear things up, I did re-try the statement in SS and this
> time it returns the expected "integer|0", so I must've done something wrong
> on the first run, sorry for the confusion.

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
___
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

[Dennis Volodomanov]

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, it's the same DB - I've got the path double-checked and it's all 
local and both SQLiteStudio and sqlite3 shell are copied into the same 
folder as well. Just to clear things up, I did re-try the statement in 
SS and this time it returns the expected "integer|0", so I must've done 
something wrong on the first run, sorry for the confusion.


   Dennis

___
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

[Pavel Ivanov]

> 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 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:
>
> 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 please recheck and report the exact steps to
>> reproduce the problem - show us all statements: CREATE TABLE, INSERT,
>> SELECTs. If you are reproducing it on existing data and can't reproduce it
>> on newly created one then show us a series of SELECTs with their results
>> showing the contradiction. Try to copy exactly what SQLiteStudio returns or
>> better yet to use sqlite3 command line utility and copy data it outputs to
>> terminal. Pavel
>
>
> Yes, I've picked up on ColA/ColC  and did execute the query on ColC.
>
> 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 the DB using sqlite3_open_v2() with these flags:
> SQLITE_OPEN_CREATE|SQLITE_OPEN_READWRITE|SQLITE_OPEN_SHAREDCACHE - could the
> shared cache play a role in this, along with read uncommitted?
>
> Given that the result is ok in the shell, I assume it's the way I've
> compiled the amalgamation (see the flags I've posted earlier) or the way I
> open it - is there anything peculiar about either of those?
>
> Thanks!
>
>
>   Dennis
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
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

[Dennis Volodomanov]

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 please recheck and report 
the exact steps to reproduce the problem - show us all statements: 
CREATE TABLE, INSERT, SELECTs. If you are reproducing it on existing 
data and can't reproduce it on newly created one then show us a series 
of SELECTs with their results showing the contradiction. Try to copy 
exactly what SQLiteStudio returns or better yet to use sqlite3 command 
line utility and copy data it outputs to terminal. Pavel 


Yes, I've picked up on ColA/ColC  and did execute the query on ColC.

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 the DB using sqlite3_open_v2() with these flags: 
SQLITE_OPEN_CREATE|SQLITE_OPEN_READWRITE|SQLITE_OPEN_SHAREDCACHE - could 
the shared cache play a role in this, along with read uncommitted?


Given that the result is ok in the shell, I assume it's the way I've 
compiled the amalgamation (see the flags I've posted earlier) or the way 
I open it - is there anything peculiar about either of those?


Thanks!

   Dennis

___
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

[Black, Michael (IS)]

At an absolute minimum you need to show us your code for this section.



Then...when we're stumpedyou need a stand-alone version you can send out.  
It will be worth your time to do so as you may catch your own error.



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Dennis Volodomanov [i...@psunrise.com]
Sent: Thursday, June 21, 2012 9:08 AM
To: General Discussion 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?
>
> Are any deletes being done?
>
>

Yes, the application is multi-threaded, but at this point, there's only
one thread left running (related to this issue) which is reading the
database. Nothing is writing into the DB at this point and the behavior
is still there after restarting the program, so all inserts/deletes were
already done in the previous session for sure.

Dennis
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
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

[Dennis Volodomanov]

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 
one thread left running (related to this issue) which is reading the 
database. Nothing is writing into the DB at this point and the behavior 
is still there after restarting the program, so all inserts/deletes were 
already done in the previous session for sure.


   Dennis
___
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

[Black, Michael (IS)]

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 Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Dennis Volodomanov [i...@psunrise.com]
Sent: Thursday, June 21, 2012 8:44 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] EXT : An interesting (strange) issue with selects

Michael,

Thanks for the reply. I know, it's usually the user :)

On 21/06/2012 11:31 PM, Black, Michael (IS) wrote:
>
> You don't show where you inserted your data.
>
> Are you postiive ColC is an integer and you didn't insert it as a string?
>
> You don't show a dump of your table which would be handy.
>
> What does "bomb" mean?  Your program gets a seg fault or such?
>
> What are you programming in, on what OS?
>
> I can tell you now if there isn't a simple answer you need to make a
> complete example so somebody else can reproduce the problem and see
> what you're doing wrong (in all likelihood it's probably you).
>
>

I'm using SQLite in C++ code (Windows, MSVC2008, amalgamation). The data
is inserted using sqlite3_bind_int64()/sqlite3_bind_int(). What I mean
by "bomb out" is that it executes this pair of statements tens of
thousands of times (count, then get one row) and works (e.g. "count"
returns more than one and "select" returns one row) and then, once in a
while, count returns more than one, but select doesn't get anything.

Dennis

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
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

[Pavel Ivanov]

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 rows.  What does this show:
>>
>>    SELECT typeof(ColA), quote(ColA) FROM CriticaltemsToProcess;
>>
>
> Pavel, Richard - thank you for replying.
>
> The data I've provided is just how I typed it, so it's actually written into
> the DB using sqlite3_bind_int64(). The SQL above returns "integer, 64".
> Please note, I'm not using the SQLite shell to execute it, so I'm again
> interpreting the results. I can try the shell as well, if that makes sense.
> I'm using SQLiteStudio v2.0.26 for this testing.

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 please recheck and report
the exact steps to reproduce the problem - show us all statements:
CREATE TABLE, INSERT, SELECTs. If you are reproducing it on existing
data and can't reproduce it on newly created one then show us a series
of SELECTs with their results showing the contradiction. Try to copy
exactly what SQLiteStudio returns or better yet to use sqlite3 command
line utility and copy data it outputs to terminal.


Pavel
___
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

[Tomas Vasko]

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 different from a numeric 0, so the
> >second query should return zero rows.  What does this show:
> >
> >SELECT typeof(ColA), quote(ColA) FROM CriticaltemsToProcess;
> >
> 
> Pavel, Richard - thank you for replying.
> 
> The data I've provided is just how I typed it, so it's actually
> written into the DB using sqlite3_bind_int64(). The SQL above
> returns "integer, 64". Please note, I'm not using the SQLite shell
> to execute it, so I'm again interpreting the results. I can try the
> shell as well, if that makes sense. I'm using SQLiteStudio v2.0.26
> for this testing.
> 
>Dennis
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-- 
   The more I see, The less I believe...
___
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

[Dennis Volodomanov]

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 CriticaltemsToProcess;



Pavel, Richard - thank you for replying.

The data I've provided is just how I typed it, so it's actually written 
into the DB using sqlite3_bind_int64(). The SQL above returns "integer, 
64". Please note, I'm not using the SQLite shell to execute it, so I'm 
again interpreting the results. I can try the shell as well, if that 
makes sense. I'm using SQLiteStudio v2.0.26 for this testing.


   Dennis

___
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

[Richard Hipp]

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 others, so I've
> decided to ask for your wisdom. This is the amalgamation 3.7.13.
>
> Let's consider the following setup - pragmas used (there's also a bunch of
> indexes, but I believe those shouldn't affect the problem):
>
> "PRAGMA journal_mode = WAL;";
> "PRAGMA synchronous = NORMAL;";
> "PRAGMA page_size = 4096;";
> "PRAGMA read_uncommitted = 1;";
>
> Also, some defines when building:
>
> #define SQLITE_OMIT_DEPRECATED
> #define SQLITE_DEFAULT_WAL_**AUTOCHECKPOINT 5000
> #define SQLITE_THREADSAFE 2
> #define SQLITE_ENABLE_STAT3
>
> Table:
>
> "CREATE TABLE TableA (ColA INTEGER, ColB INTEGER, ColC INTEGER)";
>
> 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):
>
> 1| 12| 0
>

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 CriticaltemsToProcess;


>
> Just as a side note, the same SQL/functions above work 99.9% of the time,
> but bomb out once in a while. Most likely this doesn't help, but thought
> I'd mention it.
>
> I appreciate you reading so far and hopefully you can help me out.
>
> Best regards,
>
>   Dennis
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
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

[Pavel Ivanov]

> 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):
>
> 1| 12| 0

It looks like you don't have 0 (as integer) in the ColC, but you have
string " 0" instead (note a space at the beginning). And this string "
0" will never be equal to integer 0. Thus your second query doesn't
return rows while first can count them.


Pavel


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 others, so I've decided to ask
> for your wisdom. This is the amalgamation 3.7.13.
>
> Let's consider the following setup - pragmas used (there's also a bunch of
> indexes, but I believe those shouldn't affect the problem):
>
> "PRAGMA journal_mode = WAL;";
> "PRAGMA synchronous = NORMAL;";
> "PRAGMA page_size = 4096;";
> "PRAGMA read_uncommitted = 1;";
>
> Also, some defines when building:
>
> #define SQLITE_OMIT_DEPRECATED
> #define SQLITE_DEFAULT_WAL_AUTOCHECKPOINT 5000
> #define SQLITE_THREADSAFE 2
> #define SQLITE_ENABLE_STAT3
>
> Table:
>
> "CREATE TABLE TableA (ColA INTEGER, ColB INTEGER, ColC INTEGER)";
>
> 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):
>
> 1| 12| 0
>
> Just as a side note, the same SQL/functions above work 99.9% of the time,
> but bomb out once in a while. Most likely this doesn't help, but thought I'd
> mention it.
>
> I appreciate you reading so far and hopefully you can help me out.
>
> Best regards,
>
>   Dennis
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
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

[Dennis Volodomanov]

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-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users