[sqlite] Getting a crash on 32-bit Linux

[Dan Kennedy]

On 04/14/2015 10:00 PM, Ron Aaron wrote:
> Just updated with the version from sqlite.org and have the same problem:

The line numbers still don't match the 3.8.9 amalgamation on the 
website. Are you running [make sqlite3.c] yourself or downloading 
sqlite-amalgamation-3080900.zip or sqlite3-autoconf-3080900.tar.gz?

Are you able to share the test app with us so that we can reproduce the 
crash?

What is output if you run [where full 7] in gdb?

Thanks,
Dan.





>
>
> #0  findInodeInfo (pFile=0x9434118, ppInode=0x9434120) at sqlite3.c:26091
> #1  0x085d3234 in fillInUnixFile (pVfs=0x89422b0 
> , h=5, pId=0x9434118, zFilename=0x9434208 
> "/home/ron/proj/8th/test.db", ctrlFlags=0)
> at sqlite3.c:30092
> #2  0x084fa69e in unixOpen (pVfs=0x89422b0 , 
> zPath=0x9434208 "/home/ron/proj/8th/test.db", pFile=0x9434118, flags=262,
> pOutFlags=0xd074) at sqlite3.c:30697
> #3  0x0851a453 in sqlite3OsOpen (pVfs=0x89422b0 
> , zPath=0x9434208 "/home/ron/proj/8th/test.db", 
> pFile=0x9434118, flags=262,
> pFlagsOut=0xd074) at sqlite3.c:16093
> #4  0x08515c88 in sqlite3PagerOpen (pVfs=0x89422b0 
> , ppPager=0x91c6af0, zFilename=0x92a3cf0 
> "test.db", nExtra=76, flags=0,
> vfsFlags=262, xReinit=0x8516110 ) at sqlite3.c:45520
> #5  0x085148f2 in sqlite3BtreeOpen (pVfs=0x89422b0 
> , zFilename=0x92a3cf0 "test.db", db=0x9192fd0, 
> ppBtree=0x919318c, flags=0,
> vfsFlags=262) at sqlite3.c:54119
> #6  0x0850de97 in openDatabase (zFilename=0x91a1610 "test.db", 
> ppDb=0x91a2028, flags=6, zVfs=0x0) at sqlite3.c:127475
> #7  0x0850d73a in sqlite3_open (zFilename=0x91a1610 "test.db", 
> ppDb=0x91a2028) at sqlite3.c:127601
>
>
> On 4/14/15 17:31, Ron Aaron wrote:
>> I'm using the one from fossil...
>>
>>
>> On 4/14/15 17:12, Richard Hipp wrote:
>>> On 4/14/15, Ron Aaron  wrote:
 I just updated to the 3.8.9 version of SQLite for my project, and am
 compiling for 32-bit linux (on a 64-bit box).


 I'm getting a consistent crash in findInodeInfo, with this backtrace:


  #0  findInodeInfo (pFile=0x9438118, ppInode=0x9438120) at
  sqlite3.c:26091
>>> Corresponds to this source code line:
>>> https://www.sqlite.org/src/info/25b80a3d?ln=710
>>>
  #1  0x085d3234 in fillInUnixFile (pVfs=0x89422b0
  , h=5, pId=0x9438118, zFilename=0x9438208
  "/home/ron/proj/8th/test.db", ctrlFlags=0)
   at sqlite3.c:30092
>>> Corresponds to this source code line:
>>> https://www.sqlite.org/src/info/25b80a3d?ln=4711
>>>
>>> So I think you must be using a non-standard build of the sqlite3.c
>>> amalgamation file, huh?
>>>
  #2  0x084fa69e in unixOpen (pVfs=0x89422b0 
 ,
  zPath=0x9438208 "/home/ron/proj/8th/test.db", pFile=0x9438118,
  flags=262, pOutFlags=0xd064)
   at sqlite3.c:30697
  #3  0x0851a453 in sqlite3OsOpen (pVfs=0x89422b0
  , zPath=0x9438208
  "/home/ron/proj/8th/test.db", pFile=0x9438118, flags=262,
  pFlagsOut=0xd064)
   at sqlite3.c:16093
  #4  0x08515c88 in sqlite3PagerOpen (pVfs=0x89422b0
  , ppPager=0x91c6af0, zFilename=0x92a7cf0
  "test.db", nExtra=76, flags=0, vfsFlags=262,
   xReinit=0x8516110 ) at sqlite3.c:45520
  #5  0x085148f2 in sqlite3BtreeOpen (pVfs=0x89422b0
  , zFilename=0x92a7cf0 "test.db", 
 db=0x9192fd0,
  ppBtree=0x919318c, flags=0, vfsFlags=262)
   at sqlite3.c:54119
  #6  0x0850de97 in openDatabase (zFilename=0x91a1610 "test.db",
  ppDb=0x91a2028, flags=6, zVfs=0x0) at sqlite3.c:127475
  #7  0x0850d73a in sqlite3_open (zFilename=0x91a1610 "test.db",
  ppDb=0x91a2028) at sqlite3.c:127601


 Nothing has changed in my test code, and I haven't got a similar 
 problem
 on 32-bit OS X;  however, it's certainly conceivable I'm not using the
 right flags when compiling SQLite:

  gcc   -I.  -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_AUTHORIZATION
  -DSQLITE_OMIT_DEPRECATED -DSQLITE_ENABLE_LOCKING_STYLE=0
  -DSQLITE_SYSTEM_MALLOC=1 -DSQLITE_OMIT_COMPILEOPTION_DIAGS
  -DSQLITE_OMIT_TRACE -O0 -c -o sqlite3.o sqlite3.c


 The crash occurs whether I work in a chroot lin 32 environment or a
 virtual machine running 'native'.  The test application is statically
 linked.


 Any clues would be appreciated!


 Thanks,

 Ron



 -- 
 Ron Aaron, CTO
 Aaron High-Tech, Ltd.
 +972.52.652.5543
 
 ___
 sqlite-users mailing list
 sqlite-users at mailinglists.sqlite.org
 http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

>>
>

[sqlite] Getting a crash on 32-bit Linux

[Ron Aaron]

On 4/14/15 21:06, Richard Hipp wrote:
> On 4/14/15, Ron Aaron  wrote:
>> Something is very wrong, then.  The file I downloaded from the
>> sqlite.org site, and unzipped, has these:
> Yes.  My previous email had the wrong version of sqlite3..  Version
> 3.8.9 states:
>
> Size:  5507061 bytes
> Lines:  154633
> MD5: a99b92d9e510a8107999c474dca017d7
> SHA1: 49f1c3ae347e1327b5aaa6c7f76126bdf09c6f42
>
> So apparently you have the right sqlite3.c.  And yet line 26091 of
> that file is a blank line that is in between two procedures.  It is
> not a part of the findInodeInfo() procedure.  The findInodeInfo()
> function is 528 lines further down in the file.
>
> Do you have any idea why the line numbers on your stack track do not
> align with line numbers in the source file?  Are you sure you used the
> correct source code file in your build?

Given that my lin32 compile is done in a schroot environment, it could 
perhaps be the case that an incorrect sqlite is getting picked up, 
though I don't see how.

I'll have to investigate this further tomorrow; I thank you all for your 
time, and I'll let you know what I determine.

Best regards,
Ron

>
>>
>> ~/src/sqlite-amalgamation-3080900$ ls -l
>> total 11856
>> -rw-r--r--@ 1 ron  staff   147581 Apr  8 17:38 shell.c
>> -rw-r--r--@ 1 ron  staff  5507061 Apr  8 17:38 sqlite3.c
>> -rw-r--r--@ 1 ron  staff   376915 Apr  8 17:38 sqlite3.h
>> -rw-r--r--@ 1 ron  staff27763 Apr  8 17:38 sqlite3ext.h
>>
>>
>> ~/src/sqlite-amalgamation-3080900$ md5 sqlite3.c
>> MD5 (sqlite3.c) = a99b92d9e510a8107999c474dca017d7
>>
>>
>> On 4/14/15 20:41, Richard Hipp wrote:
>>> On 4/14/15, Ron Aaron  wrote:
 On 4/14/15 18:13, Dan Kennedy wrote:
> On 04/14/2015 10:00 PM, Ron Aaron wrote:
>> Just updated with the version from sqlite.org and have the same
>> problem:
> The line numbers still don't match the 3.8.9 amalgamation on the
> website. Are you running [make sqlite3.c] yourself or downloading
> sqlite-amalgamation-3080900.zip or sqlite3-autoconf-3080900.tar.gz?
 sqlite-amalgamation-3080900.zip
>>> The sqlite3.c file in that ZIP archive should be 5516034 bytes in
>>> size, 154880 lines long, and have an MD5 hash of
>>> bef3e840d1e44e0e453a165f5b468ad7 and a SHA1 hash of
>>> 5ac3fb37f936c0748e1834ac633ce422870ea274.  Please verify that you are
>>> using the correct sqlite3.c source file.
>>>
>>> D. Richard Hipp
>>> drh at sqlite.org
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> --
>> Ron Aaron, CTO
>> Aaron High-Tech, Ltd.
>> +972.52.652.5543
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>

-- 
Ron Aaron, CTO
Aaron High-Tech, Ltd.
+972.52.652.5543

[sqlite] Getting a crash on 32-bit Linux

[Ron Aaron]

Yes:


I issued the command:  unzip ~/Downloads/sqlite-amalgamation-3080900.zip


file reports: Downloads/sqlite-amalgamation-3080900.zip: Zip archive 
data, at least v1.0 to extract


md5 sum: 02e9c3a6daa8b8587cf6bef828c2e33f


On 4/14/15 21:05, Nelson, Erik - 2 wrote:
> Ron Aaron wrote on Tuesday, April 14, 2015 1:51 PM
>
>> Something is very wrong, then.  The file I downloaded from the sqlite.org 
>> site, and unzipped, has these:
>> -rw-r--r--@ 1 ron  staff  5507061 Apr  8 17:38 sqlite3.c
> Can you confirm that this file came from the .zip file, not the .gz file?
>
>>> On 4/14/15 18:13, Dan Kennedy wrote:
 On 04/14/2015 10:00 PM, Ron Aaron wrote:
> Just updated with the version from sqlite.org and have the same problem:
 The line numbers still don't match the 3.8.9 amalgamation on the
 website. Are you running [make sqlite3.c] yourself or downloading
 sqlite-amalgamation-3080900.zip or sqlite3-autoconf-3080900.tar.gz?
>>> sqlite-amalgamation-3080900.zip
>> The sqlite3.c file in that ZIP archive should be 5516034 bytes in
>> size, 154880 lines long, and have an MD5 hash of
>> bef3e840d1e44e0e453a165f5b468ad7 and a SHA1 hash of
>> 5ac3fb37f936c0748e1834ac633ce422870ea274.  Please verify that you are
>> using the correct sqlite3.c source file.
> --
> This message, and any attachments, is for the intended recipient(s) only, may 
> contain information that is privileged, confidential and/or proprietary and 
> subject to important terms and conditions available at 
> http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
> recipient, please delete this message.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Ron Aaron, CTO
Aaron High-Tech, Ltd.
+972.52.652.5543

[sqlite] Getting a crash on 32-bit Linux

[Ron Aaron]

Something is very wrong, then.  The file I downloaded from the 
sqlite.org site, and unzipped, has these:


~/src/sqlite-amalgamation-3080900$ ls -l
total 11856
-rw-r--r--@ 1 ron  staff   147581 Apr  8 17:38 shell.c
-rw-r--r--@ 1 ron  staff  5507061 Apr  8 17:38 sqlite3.c
-rw-r--r--@ 1 ron  staff   376915 Apr  8 17:38 sqlite3.h
-rw-r--r--@ 1 ron  staff27763 Apr  8 17:38 sqlite3ext.h


~/src/sqlite-amalgamation-3080900$ md5 sqlite3.c
MD5 (sqlite3.c) = a99b92d9e510a8107999c474dca017d7


On 4/14/15 20:41, Richard Hipp wrote:
> On 4/14/15, Ron Aaron  wrote:
>>
>> On 4/14/15 18:13, Dan Kennedy wrote:
>>> On 04/14/2015 10:00 PM, Ron Aaron wrote:
 Just updated with the version from sqlite.org and have the same problem:
>>> The line numbers still don't match the 3.8.9 amalgamation on the
>>> website. Are you running [make sqlite3.c] yourself or downloading
>>> sqlite-amalgamation-3080900.zip or sqlite3-autoconf-3080900.tar.gz?
>> sqlite-amalgamation-3080900.zip
> The sqlite3.c file in that ZIP archive should be 5516034 bytes in
> size, 154880 lines long, and have an MD5 hash of
> bef3e840d1e44e0e453a165f5b468ad7 and a SHA1 hash of
> 5ac3fb37f936c0748e1834ac633ce422870ea274.  Please verify that you are
> using the correct sqlite3.c source file.
>
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 
Ron Aaron, CTO
Aaron High-Tech, Ltd.
+972.52.652.5543

[sqlite] Best way to temporarily store data before processing

[David Cotter]

does this awesome sounding temp table then merge instantly when you commit it 
even if it?s huge?

> On Apr 14, 2015, at 1:01 PM, Scott Hess  wrote:
> 
> On Tue, Apr 14, 2015 at 9:37 AM, Jim Callahan
>  wrote:
>> My recollection is that SQLite has a "temp" or "tmp" namespace available
>> for intermediate tables -- it was on my todo list, but I never got around
>> to exploring that option.
> 
> CREATE TEMP TABLE ... works very well for things like this.  If you
> use a distinct table name you can just refer to it directly, or you
> can say temp.tablename (vs main.tablename).
> 
> You can also create temp databases, where SQLite arranges for the
> underlying file to go away on close (POSIX deletes after opening,
> Windows uses DELETE-ON-CLOSE flags).  I believe you pass NULL at the
> filename to sqlite3_open().  I haven't tried out whether you can also
> do "ATTACH NULL AS mydb", but I'd bet you can.
> 
> The main advantage of temporary tables over a :memory: database is
> that you don't have to worry about blowing out memory, because it can
> spill to disk easily.  There's no journal (because temporary), so it
> should be faster than a staging table in your database.  SQLite also
> optimizes things if your temp tables can be handled entirely in the
> cache, they'll never hit disk (and even if they hit an OS write, I
> don't think they're ever sync'ed so the OS might never write things to
> disk).
> 
> The main dis-advantage is that you can't checkpoint things.  If you
> crash or shutdown, when you start back up the data is gone and you
> have to start over.
> 
> -scott

[sqlite] Getting a crash on 32-bit Linux

[Ron Aaron]

On 4/14/15 18:13, Dan Kennedy wrote:
> On 04/14/2015 10:00 PM, Ron Aaron wrote:
>> Just updated with the version from sqlite.org and have the same problem:
>
> The line numbers still don't match the 3.8.9 amalgamation on the 
> website. Are you running [make sqlite3.c] yourself or downloading 
> sqlite-amalgamation-3080900.zip or sqlite3-autoconf-3080900.tar.gz?
sqlite-amalgamation-3080900.zip

>
> Are you able to share the test app with us so that we can reproduce 
> the crash?
I can't do that, unfortunately.  I'll try to make an independent test 
scenario.  I do appreciate your efforts!
>
> What is output if you run [where full 7] in gdb?

A pile of output:

ok> "test.db" db:open
CALL_or_compile: 0x92014b8 xt 0x82e5990 name open

Program received signal SIGSEGV, Segmentation fault.
findInodeInfo (pFile=0x928a110, ppInode=0x928a118) at sqlite3.c:26091
26091
(gdb) where full 7
#0  findInodeInfo (pFile=0x928a110, ppInode=0x928a118) at sqlite3.c:26091
 rc = 0
 fd = 5
 fileId = {dev = 613319152320380928, ino = 4432731307}
 statbuf = {st_dev = 25, __pad1 = 420, __st_ino = 85073599, 
st_mode = 33188, st_nlink = 1, st_uid = 1000, st_gid = 1000, st_rdev = 
0, __pad2 = 420,
   st_size = 0, st_blksize = 4096, st_blocks = 16, st_atim = 
{tv_sec = 1429027578, tv_nsec = 801386751}, st_mtim = {tv_sec = 1429027578,
 tv_nsec = 801386751}, st_ctim = {tv_sec = 1429027578, 
tv_nsec = 801386751}, st_ino = 85073599}
 pInode = 0x0
#1  0x085d31c4 in fillInUnixFile (pVfs=0x89422b0 , 
h=5, pId=0x928a110, zFilename=0x928a200 "/home/ron/proj/8th/test.db", 
ctrlFlags=0)
 at sqlite3.c:30092
 pLockingStyle = 0x8917cec 
 pNew = 0x928a110
 rc = 0
#2  0x084fa62e in unixOpen (pVfs=0x89422b0 , 
zPath=0x928a200 "/home/ron/proj/8th/test.db", pFile=0x928a110, flags=262,
 pOutFlags=0xd288) at sqlite3.c:30697
 p = 0x928a110
 fd = 5
 openFlags = 32834
 eType = 256
 noLock = 0
 rc = 0
 ctrlFlags = 0
 isExclusive = 0
 isDelete = 0
 isCreate = 4
 isReadonly = 0
 isReadWrite = 2
 syncDir = 0
 zTmpname = 
"\226\b\370d\230\b\033\000\000\000S\f6\b\000`\236\000\310d\230\b\002\000\000\000F\000\000\000C\000\000\000H\000\000\000\234`\240\b\000\000\375\367`\t\375\367I\000\000\000s\000\000\000\000\300\222\b\033\000\000\000\370d\230\b\200\002\000\000\000\340(\t\000`\236\b\002\000\000\000\230\320\377\377\221\267\065\b\221\267\065\b\001\000\000\000X\250\375\367\221\267\065\b@?\b<\276\226\bT\320\377\377\370d\230\b\240*\226\b\330\320\377\377T\320\377\377\000\300\222\b
 
\031\226\b\350\320\377\377d\320\377\377\212\273\065\b 
\031\226\b\001\000\000\000\344\320\377\377\350\320\377\377\324\320\377\377\240*\226\b\244\320\377\377\000\300\222\b\344\320\377\377
 
\031\226\b\264\320\377\377"...
 zName = 0x928a200 "/home/ron/proj/8th/test.db"
#3  0x0851a3e3 in sqlite3OsOpen (pVfs=0x89422b0 , 
zPath=0x928a200 "/home/ron/proj/8th/test.db", pFile=0x928a110, flags=262,
 pFlagsOut=0xd288) at sqlite3.c:16093
 rc = 153570696
#4  0x08515c18 in sqlite3PagerOpen (pVfs=0x89422b0 
, ppPager=0x9286008, zFilename=0x926e248 
"test.db", nExtra=76, flags=0,
 vfsFlags=262, xReinit=0x85160a0 ) at sqlite3.c:45520
 fout = 262
 pPtr = 0x928a21c "/home/ron/proj/8th/test.db-journal"
 pPager = 0x928a008
 rc = 0
 tempFile = 0
 memDb = 0
 readOnly = 0
 journalFileSize = 80
---Type  to continue, or q  to quit---
 zPathname = 0x9274d88 "/home/ron/proj/8th/test.db"
 nPathname = 26
 useJournal = 1
 pcacheSize = 48
 szPageDflt = 1024
 zUri = 0x926e250 ""
 nUri = 1
#5  0x08514882 in sqlite3BtreeOpen (pVfs=0x89422b0 
, zFilename=0x926e248 "test.db", db=0x9192b48, 
ppBtree=0x9192d04, flags=0,
 vfsFlags=262) at sqlite3.c:54119
 pBt = 0x9286008
 p = 0x919fa08
 mutexOpen = 0x0
 rc = 0
 nReserve = 8 '\b'
 zDbHeader = 
"\000\263'Q\bH+\031\t'\264\202\b\001\000\000\000\t\270O\b\350a!\t\000\300\222\b'\264\202\b\000\300\222\b\000\000\000\000H+\031\t\000\300\222\b~\347P\bH+\031\t\000\000\000\000'\264\202\b\001\000\000\000\000\300\222\b\000\000\000\000\001\000\000\000\374\002\000\200\000\000\000\000Ha!\t\a\000\000\000\032\264\202"
 isTempDb = 0
 isMemdb = 0
#6  0x0850de27 in openDatabase (zFilename=0x919f9d0 "test.db", 
ppDb=0x91a2028, flags=6, zVfs=0x0) at sqlite3.c:127475
 db = 0x9192b48
 rc = 0
 isThreadsafe = 0
 zOpen = 0x926e248 "test.db"
 zErrMsg = 0x0
(More stack frames follow...)
(gdb)

[sqlite] Getting a crash on 32-bit Linux

[Nelson, Erik - 2]

Ron Aaron wrote on Tuesday, April 14, 2015 1:51 PM

>Something is very wrong, then.  The file I downloaded from the sqlite.org 
>site, and unzipped, has these:
>-rw-r--r--@ 1 ron  staff  5507061 Apr  8 17:38 sqlite3.c

Can you confirm that this file came from the .zip file, not the .gz file?

>> On 4/14/15 18:13, Dan Kennedy wrote:
>>> On 04/14/2015 10:00 PM, Ron Aaron wrote:
 Just updated with the version from sqlite.org and have the same problem:
>>> The line numbers still don't match the 3.8.9 amalgamation on the
>>> website. Are you running [make sqlite3.c] yourself or downloading
>>> sqlite-amalgamation-3080900.zip or sqlite3-autoconf-3080900.tar.gz?
>> sqlite-amalgamation-3080900.zip
> The sqlite3.c file in that ZIP archive should be 5516034 bytes in
> size, 154880 lines long, and have an MD5 hash of
> bef3e840d1e44e0e453a165f5b468ad7 and a SHA1 hash of
> 5ac3fb37f936c0748e1834ac633ce422870ea274.  Please verify that you are
> using the correct sqlite3.c source file.

--
This message, and any attachments, is for the intended recipient(s) only, may 
contain information that is privileged, confidential and/or proprietary and 
subject to important terms and conditions available at 
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
recipient, please delete this message.

[sqlite] Getting a crash on 32-bit Linux

[Ron Aaron]

Just updated with the version from sqlite.org and have the same problem:


#0  findInodeInfo (pFile=0x9434118, ppInode=0x9434120) at sqlite3.c:26091
#1  0x085d3234 in fillInUnixFile (pVfs=0x89422b0 , 
h=5, pId=0x9434118, zFilename=0x9434208 "/home/ron/proj/8th/test.db", 
ctrlFlags=0)
 at sqlite3.c:30092
#2  0x084fa69e in unixOpen (pVfs=0x89422b0 , 
zPath=0x9434208 "/home/ron/proj/8th/test.db", pFile=0x9434118, flags=262,
 pOutFlags=0xd074) at sqlite3.c:30697
#3  0x0851a453 in sqlite3OsOpen (pVfs=0x89422b0 , 
zPath=0x9434208 "/home/ron/proj/8th/test.db", pFile=0x9434118, flags=262,
 pFlagsOut=0xd074) at sqlite3.c:16093
#4  0x08515c88 in sqlite3PagerOpen (pVfs=0x89422b0 
, ppPager=0x91c6af0, zFilename=0x92a3cf0 
"test.db", nExtra=76, flags=0,
 vfsFlags=262, xReinit=0x8516110 ) at sqlite3.c:45520
#5  0x085148f2 in sqlite3BtreeOpen (pVfs=0x89422b0 
, zFilename=0x92a3cf0 "test.db", db=0x9192fd0, 
ppBtree=0x919318c, flags=0,
 vfsFlags=262) at sqlite3.c:54119
#6  0x0850de97 in openDatabase (zFilename=0x91a1610 "test.db", 
ppDb=0x91a2028, flags=6, zVfs=0x0) at sqlite3.c:127475
#7  0x0850d73a in sqlite3_open (zFilename=0x91a1610 "test.db", 
ppDb=0x91a2028) at sqlite3.c:127601


On 4/14/15 17:31, Ron Aaron wrote:
> I'm using the one from fossil...
>
>
> On 4/14/15 17:12, Richard Hipp wrote:
>> On 4/14/15, Ron Aaron  wrote:
>>> I just updated to the 3.8.9 version of SQLite for my project, and am
>>> compiling for 32-bit linux (on a 64-bit box).
>>>
>>>
>>> I'm getting a consistent crash in findInodeInfo, with this backtrace:
>>>
>>>
>>>  #0  findInodeInfo (pFile=0x9438118, ppInode=0x9438120) at
>>>  sqlite3.c:26091
>> Corresponds to this source code line:
>> https://www.sqlite.org/src/info/25b80a3d?ln=710
>>
>>>  #1  0x085d3234 in fillInUnixFile (pVfs=0x89422b0
>>>  , h=5, pId=0x9438118, zFilename=0x9438208
>>>  "/home/ron/proj/8th/test.db", ctrlFlags=0)
>>>   at sqlite3.c:30092
>> Corresponds to this source code line:
>> https://www.sqlite.org/src/info/25b80a3d?ln=4711
>>
>> So I think you must be using a non-standard build of the sqlite3.c
>> amalgamation file, huh?
>>
>>>  #2  0x084fa69e in unixOpen (pVfs=0x89422b0 ,
>>>  zPath=0x9438208 "/home/ron/proj/8th/test.db", pFile=0x9438118,
>>>  flags=262, pOutFlags=0xd064)
>>>   at sqlite3.c:30697
>>>  #3  0x0851a453 in sqlite3OsOpen (pVfs=0x89422b0
>>>  , zPath=0x9438208
>>>  "/home/ron/proj/8th/test.db", pFile=0x9438118, flags=262,
>>>  pFlagsOut=0xd064)
>>>   at sqlite3.c:16093
>>>  #4  0x08515c88 in sqlite3PagerOpen (pVfs=0x89422b0
>>>  , ppPager=0x91c6af0, zFilename=0x92a7cf0
>>>  "test.db", nExtra=76, flags=0, vfsFlags=262,
>>>   xReinit=0x8516110 ) at sqlite3.c:45520
>>>  #5  0x085148f2 in sqlite3BtreeOpen (pVfs=0x89422b0
>>>  , zFilename=0x92a7cf0 "test.db", 
>>> db=0x9192fd0,
>>>  ppBtree=0x919318c, flags=0, vfsFlags=262)
>>>   at sqlite3.c:54119
>>>  #6  0x0850de97 in openDatabase (zFilename=0x91a1610 "test.db",
>>>  ppDb=0x91a2028, flags=6, zVfs=0x0) at sqlite3.c:127475
>>>  #7  0x0850d73a in sqlite3_open (zFilename=0x91a1610 "test.db",
>>>  ppDb=0x91a2028) at sqlite3.c:127601
>>>
>>>
>>> Nothing has changed in my test code, and I haven't got a similar 
>>> problem
>>> on 32-bit OS X;  however, it's certainly conceivable I'm not using the
>>> right flags when compiling SQLite:
>>>
>>>  gcc   -I.  -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_AUTHORIZATION
>>>  -DSQLITE_OMIT_DEPRECATED -DSQLITE_ENABLE_LOCKING_STYLE=0
>>>  -DSQLITE_SYSTEM_MALLOC=1 -DSQLITE_OMIT_COMPILEOPTION_DIAGS
>>>  -DSQLITE_OMIT_TRACE -O0 -c -o sqlite3.o sqlite3.c
>>>
>>>
>>> The crash occurs whether I work in a chroot lin 32 environment or a
>>> virtual machine running 'native'.  The test application is statically
>>> linked.
>>>
>>>
>>> Any clues would be appreciated!
>>>
>>>
>>> Thanks,
>>>
>>> Ron
>>>
>>>
>>>
>>> -- 
>>> Ron Aaron, CTO
>>> Aaron High-Tech, Ltd.
>>> +972.52.652.5543
>>> 
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>

-- 
Ron Aaron, CTO
Aaron High-Tech, Ltd.
+972.52.652.5543

[sqlite] Getting a crash on 32-bit Linux

[Ron Aaron]

I'm using the one from fossil...


On 4/14/15 17:12, Richard Hipp wrote:
> On 4/14/15, Ron Aaron  wrote:
>> I just updated to the 3.8.9 version of SQLite for my project, and am
>> compiling for 32-bit linux (on a 64-bit box).
>>
>>
>> I'm getting a consistent crash in findInodeInfo, with this backtrace:
>>
>>
>>  #0  findInodeInfo (pFile=0x9438118, ppInode=0x9438120) at
>>  sqlite3.c:26091
> Corresponds to this source code line:
> https://www.sqlite.org/src/info/25b80a3d?ln=710
>
>>  #1  0x085d3234 in fillInUnixFile (pVfs=0x89422b0
>>  , h=5, pId=0x9438118, zFilename=0x9438208
>>  "/home/ron/proj/8th/test.db", ctrlFlags=0)
>>   at sqlite3.c:30092
> Corresponds to this source code line:
> https://www.sqlite.org/src/info/25b80a3d?ln=4711
>
> So I think you must be using a non-standard build of the sqlite3.c
> amalgamation file, huh?
>
>>  #2  0x084fa69e in unixOpen (pVfs=0x89422b0 ,
>>  zPath=0x9438208 "/home/ron/proj/8th/test.db", pFile=0x9438118,
>>  flags=262, pOutFlags=0xd064)
>>   at sqlite3.c:30697
>>  #3  0x0851a453 in sqlite3OsOpen (pVfs=0x89422b0
>>  , zPath=0x9438208
>>  "/home/ron/proj/8th/test.db", pFile=0x9438118, flags=262,
>>  pFlagsOut=0xd064)
>>   at sqlite3.c:16093
>>  #4  0x08515c88 in sqlite3PagerOpen (pVfs=0x89422b0
>>  , ppPager=0x91c6af0, zFilename=0x92a7cf0
>>  "test.db", nExtra=76, flags=0, vfsFlags=262,
>>   xReinit=0x8516110 ) at sqlite3.c:45520
>>  #5  0x085148f2 in sqlite3BtreeOpen (pVfs=0x89422b0
>>  , zFilename=0x92a7cf0 "test.db", db=0x9192fd0,
>>  ppBtree=0x919318c, flags=0, vfsFlags=262)
>>   at sqlite3.c:54119
>>  #6  0x0850de97 in openDatabase (zFilename=0x91a1610 "test.db",
>>  ppDb=0x91a2028, flags=6, zVfs=0x0) at sqlite3.c:127475
>>  #7  0x0850d73a in sqlite3_open (zFilename=0x91a1610 "test.db",
>>  ppDb=0x91a2028) at sqlite3.c:127601
>>
>>
>> Nothing has changed in my test code, and I haven't got a similar problem
>> on 32-bit OS X;  however, it's certainly conceivable I'm not using the
>> right flags when compiling SQLite:
>>
>>  gcc   -I.  -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_AUTHORIZATION
>>  -DSQLITE_OMIT_DEPRECATED -DSQLITE_ENABLE_LOCKING_STYLE=0
>>  -DSQLITE_SYSTEM_MALLOC=1 -DSQLITE_OMIT_COMPILEOPTION_DIAGS
>>  -DSQLITE_OMIT_TRACE -O0 -c -o sqlite3.o sqlite3.c
>>
>>
>> The crash occurs whether I work in a chroot lin 32 environment or a
>> virtual machine running 'native'.  The test application is statically
>> linked.
>>
>>
>> Any clues would be appreciated!
>>
>>
>> Thanks,
>>
>> Ron
>>
>>
>>
>> --
>> Ron Aaron, CTO
>> Aaron High-Tech, Ltd.
>> +972.52.652.5543
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>

-- 
Ron Aaron, CTO
Aaron High-Tech, Ltd.
+972.52.652.5543

[sqlite] Getting a crash on 32-bit Linux

[Ron Aaron]

I just updated to the 3.8.9 version of SQLite for my project, and am 
compiling for 32-bit linux (on a 64-bit box).


I'm getting a consistent crash in findInodeInfo, with this backtrace:


#0  findInodeInfo (pFile=0x9438118, ppInode=0x9438120) at
sqlite3.c:26091
#1  0x085d3234 in fillInUnixFile (pVfs=0x89422b0
, h=5, pId=0x9438118, zFilename=0x9438208
"/home/ron/proj/8th/test.db", ctrlFlags=0)
 at sqlite3.c:30092
#2  0x084fa69e in unixOpen (pVfs=0x89422b0 ,
zPath=0x9438208 "/home/ron/proj/8th/test.db", pFile=0x9438118,
flags=262, pOutFlags=0xd064)
 at sqlite3.c:30697
#3  0x0851a453 in sqlite3OsOpen (pVfs=0x89422b0
, zPath=0x9438208
"/home/ron/proj/8th/test.db", pFile=0x9438118, flags=262,
pFlagsOut=0xd064)
 at sqlite3.c:16093
#4  0x08515c88 in sqlite3PagerOpen (pVfs=0x89422b0
, ppPager=0x91c6af0, zFilename=0x92a7cf0
"test.db", nExtra=76, flags=0, vfsFlags=262,
 xReinit=0x8516110 ) at sqlite3.c:45520
#5  0x085148f2 in sqlite3BtreeOpen (pVfs=0x89422b0
, zFilename=0x92a7cf0 "test.db", db=0x9192fd0,
ppBtree=0x919318c, flags=0, vfsFlags=262)
 at sqlite3.c:54119
#6  0x0850de97 in openDatabase (zFilename=0x91a1610 "test.db",
ppDb=0x91a2028, flags=6, zVfs=0x0) at sqlite3.c:127475
#7  0x0850d73a in sqlite3_open (zFilename=0x91a1610 "test.db",
ppDb=0x91a2028) at sqlite3.c:127601


Nothing has changed in my test code, and I haven't got a similar problem 
on 32-bit OS X;  however, it's certainly conceivable I'm not using the 
right flags when compiling SQLite:

gcc   -I.  -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_AUTHORIZATION
-DSQLITE_OMIT_DEPRECATED -DSQLITE_ENABLE_LOCKING_STYLE=0
-DSQLITE_SYSTEM_MALLOC=1 -DSQLITE_OMIT_COMPILEOPTION_DIAGS
-DSQLITE_OMIT_TRACE -O0 -c -o sqlite3.o sqlite3.c


The crash occurs whether I work in a chroot lin 32 environment or a 
virtual machine running 'native'.  The test application is statically 
linked.


Any clues would be appreciated!


Thanks,

Ron



-- 
Ron Aaron, CTO
Aaron High-Tech, Ltd.
+972.52.652.5543

[sqlite] Best way to temporarily store data before processing

[f-prot]

Yes!!!





From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at 
mailinglists.sqlite.org] on behalf of Jonathan Moules 
[j.mou...@hrwallingford.com]
Sent: 14 April 2015 03:42 PM
To: 'Joseph T.'; 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Best way to temporarily store data before processing

Thanks for the thoughts, useful to know.

In relation to your question Joseph, historically the scripts were written to 
only put the raw data into the database and then the Views read it, but that 
was too slow. So now I've got an extra step to turn the raw data into easily 
Viewable data, with the bonus it uses considerably less disk space. The 
alternative of hard-coding the analysis and getting Python to write only the 
Processed Tables seemed like an ugly design, so I went with this.

Cheers



-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Joseph T.
Sent: Tuesday, April 14, 2015 12:17 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Best way to temporarily store data before processing


Mr. Moules, why not skip the raw tables entirely? Or failing that a separate in 
memory db would probably be the best and quickest option.



Sent from my Samsung Epic? 4G TouchJonathan Moules  wrote:Hi List, I'm wondering if anyone can offer me a "best 
practice" way of doing this.

I'm doing some log analysis using Python/SQLite. Python parses a log file and 
splits the raw data from each line in the log into one of about 40 tables in an 
SQLite database (I'll call them Raw Tables).

Once a log file has been processed like this, I run some SQL which takes the 
data from the Raw Tables and aggregates/processes it into about 10 different 
"Prepared Tables" which are read with a bunch of Views. The 
aggregation/processing doesn't take long, and the SQL for it is simple.

I'd like to update the Prepared Tables after each log file is read because 
there are thousands of files and I don't want to have to rely on having GB of 
disk space sitting around for temporary Raw Tables.

Once the Prepared Tables have been created, there's no real need to keep the 
data in the Raw Tables.

The Prepared Tables don't have to be in the same database as the Raw Tables. 
I'm happy to use ATTACH.

So my question:
What's the best way to do this with the minimum overhead?

Options that have come to mind (probably missed a lot):
- Some sort of empty template database for the Raw Tables which is 
copied/cloned/overwritten for each file processed.
- And/Or use "DELETE FROM Raw_Tables" to truncate it after each 
file (there are no indexes).
- And/Or place it into :memory:.
- And/Or just CREATE the Raw Tables for each file?
- And/Or do it within the Prepared Tables database and use "DELETE 
FROM Raw_Tables". (That file you wouldn't want in :memory: of course).


Thoughts welcome, thanks for your time,
Jonathan



HR Wallingford and its subsidiaries uses faxes and emails for confidential and 
legally privileged business communications. They do not of themselves create 
legal commitments. Disclosure to parties other than addressees requires our 
specific consent. We are not liable for unauthorised disclosures nor reliance 
upon them.
If you have received this message in error please advise us immediately and 
destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom Registered in 
England No. 02562099


___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


This message has been scanned for viruses by MailControl - www.mailcontrol.com



Click 
https://www.mailcontrol.com/sr/OtIwePPILzPGX2PQPOmvUtzroFmuoUEuyo!5uyiNNRAv6vRKBGbHjbCMljIjlDona9wHUfmUgVH3KeH4!zzvzA==
 to report this email as spam.



HR Wallingford and its subsidiaries uses faxes and emails for confidential and 
legally privileged business communications. They do not of themselves create 
legal commitments. Disclosure to parties other than addressees requires our 
specific consent. We are not liable for unauthorised disclosures nor reliance 
upon them.
If you have received this message in error please advise us immediately and 
destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom
Registered in England No. 02562099


___
sqlite-users mailing list

[sqlite] Best way to temporarily store data before processing

[Jonathan Moules]

Thanks for the thoughts, useful to know.

In relation to your question Joseph, historically the scripts were written to 
only put the raw data into the database and then the Views read it, but that 
was too slow. So now I've got an extra step to turn the raw data into easily 
Viewable data, with the bonus it uses considerably less disk space. The 
alternative of hard-coding the analysis and getting Python to write only the 
Processed Tables seemed like an ugly design, so I went with this.

Cheers



-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Joseph T.
Sent: Tuesday, April 14, 2015 12:17 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Best way to temporarily store data before processing


Mr. Moules, why not skip the raw tables entirely? Or failing that a separate in 
memory db would probably be the best and quickest option.



Sent from my Samsung Epic? 4G TouchJonathan Moules  wrote:Hi List, I'm wondering if anyone can offer me a "best 
practice" way of doing this.

I'm doing some log analysis using Python/SQLite. Python parses a log file and 
splits the raw data from each line in the log into one of about 40 tables in an 
SQLite database (I'll call them Raw Tables).

Once a log file has been processed like this, I run some SQL which takes the 
data from the Raw Tables and aggregates/processes it into about 10 different 
"Prepared Tables" which are read with a bunch of Views. The 
aggregation/processing doesn't take long, and the SQL for it is simple.

I'd like to update the Prepared Tables after each log file is read because 
there are thousands of files and I don't want to have to rely on having GB of 
disk space sitting around for temporary Raw Tables.

Once the Prepared Tables have been created, there's no real need to keep the 
data in the Raw Tables.

The Prepared Tables don't have to be in the same database as the Raw Tables. 
I'm happy to use ATTACH.

So my question:
What's the best way to do this with the minimum overhead?

Options that have come to mind (probably missed a lot):
- Some sort of empty template database for the Raw Tables which is 
copied/cloned/overwritten for each file processed.
- And/Or use "DELETE FROM Raw_Tables" to truncate it after each 
file (there are no indexes).
- And/Or place it into :memory:.
- And/Or just CREATE the Raw Tables for each file?
- And/Or do it within the Prepared Tables database and use "DELETE 
FROM Raw_Tables". (That file you wouldn't want in :memory: of course).


Thoughts welcome, thanks for your time,
Jonathan



HR Wallingford and its subsidiaries uses faxes and emails for confidential and 
legally privileged business communications. They do not of themselves create 
legal commitments. Disclosure to parties other than addressees requires our 
specific consent. We are not liable for unauthorised disclosures nor reliance 
upon them.
If you have received this message in error please advise us immediately and 
destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom Registered in 
England No. 02562099


___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


This message has been scanned for viruses by MailControl - www.mailcontrol.com



Click 
https://www.mailcontrol.com/sr/OtIwePPILzPGX2PQPOmvUtzroFmuoUEuyo!5uyiNNRAv6vRKBGbHjbCMljIjlDona9wHUfmUgVH3KeH4!zzvzA==
 to report this email as spam.



HR Wallingford and its subsidiaries uses faxes and emails for confidential and 
legally privileged business communications. They do not of themselves create 
legal commitments. Disclosure to parties other than addressees requires our 
specific consent. We are not liable for unauthorised disclosures nor reliance 
upon them.
If you have received this message in error please advise us immediately and 
destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom
Registered in England No. 02562099

[sqlite] Odd results with ICU extensions?

[Philip Warner]

I've been using SqlCipher (built locally with ICU extensions) and can do the 
following:

sqlite> select icu_load_collation('[][][]','q');
sqlite> select * from foo order by f1 collate q;

Which worries me.

I assume this is an Sqlite+ICU problem.

Does anyone have any insights into what is going on here? I had expected an 
error message.

[sqlite] SQLite to SQL Server

[Drago, William @ CSG - NARDA-MITEQ]

Thanks for the tip. I've used dump before to move data around between SQLite 
databases. I should have realized that I could have used it to move data to SQL 
Server.

--
Bill Drago
Senior Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin
> Sent: Monday, April 13, 2015 6:24 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQLite to SQL Server
>
>
> On 13 Apr 2015, at 10:38pm, Drago, William @ CSG - NARDA-MITEQ
>  wrote:
>
> > Is there a convenient way to transfer data from SQLite to SQL Server?
> I'm presently writing code to do a row by row transfer of all my
> tables, but the thought crossed my mind that maybe there's a better
> way.
>
> If you're just doing it once, use the SQLite shell tool to '.dump' the
> database to a text file of SQL commands, then read the text file into a
> fresh database in SQL Server.
>
> You may have to do some minor editing of the text file to make it
> conform to SQL Server's notion of SQL rather than SQLite's notion of
> SQL.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

[sqlite] Getting a crash on 32-bit Linux

[Richard Hipp]

On 4/14/15, Ron Aaron  wrote:
> Something is very wrong, then.  The file I downloaded from the
> sqlite.org site, and unzipped, has these:

Yes.  My previous email had the wrong version of sqlite3..  Version
3.8.9 states:

Size:  5507061 bytes
Lines:  154633
MD5: a99b92d9e510a8107999c474dca017d7
SHA1: 49f1c3ae347e1327b5aaa6c7f76126bdf09c6f42

So apparently you have the right sqlite3.c.  And yet line 26091 of
that file is a blank line that is in between two procedures.  It is
not a part of the findInodeInfo() procedure.  The findInodeInfo()
function is 528 lines further down in the file.

Do you have any idea why the line numbers on your stack track do not
align with line numbers in the source file?  Are you sure you used the
correct source code file in your build?

>
>
> ~/src/sqlite-amalgamation-3080900$ ls -l
> total 11856
> -rw-r--r--@ 1 ron  staff   147581 Apr  8 17:38 shell.c
> -rw-r--r--@ 1 ron  staff  5507061 Apr  8 17:38 sqlite3.c
> -rw-r--r--@ 1 ron  staff   376915 Apr  8 17:38 sqlite3.h
> -rw-r--r--@ 1 ron  staff27763 Apr  8 17:38 sqlite3ext.h
>
>
> ~/src/sqlite-amalgamation-3080900$ md5 sqlite3.c
> MD5 (sqlite3.c) = a99b92d9e510a8107999c474dca017d7
>
>
> On 4/14/15 20:41, Richard Hipp wrote:
>> On 4/14/15, Ron Aaron  wrote:
>>>
>>> On 4/14/15 18:13, Dan Kennedy wrote:
 On 04/14/2015 10:00 PM, Ron Aaron wrote:
> Just updated with the version from sqlite.org and have the same
> problem:
 The line numbers still don't match the 3.8.9 amalgamation on the
 website. Are you running [make sqlite3.c] yourself or downloading
 sqlite-amalgamation-3080900.zip or sqlite3-autoconf-3080900.tar.gz?
>>> sqlite-amalgamation-3080900.zip
>> The sqlite3.c file in that ZIP archive should be 5516034 bytes in
>> size, 154880 lines long, and have an MD5 hash of
>> bef3e840d1e44e0e453a165f5b468ad7 and a SHA1 hash of
>> 5ac3fb37f936c0748e1834ac633ce422870ea274.  Please verify that you are
>> using the correct sqlite3.c source file.
>>
>> D. Richard Hipp
>> drh at sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> --
> Ron Aaron, CTO
> Aaron High-Tech, Ltd.
> +972.52.652.5543
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Getting a crash on 32-bit Linux

[Richard Hipp]

On 4/14/15, Ron Aaron  wrote:
>
>
> On 4/14/15 18:13, Dan Kennedy wrote:
>> On 04/14/2015 10:00 PM, Ron Aaron wrote:
>>> Just updated with the version from sqlite.org and have the same problem:
>>
>> The line numbers still don't match the 3.8.9 amalgamation on the
>> website. Are you running [make sqlite3.c] yourself or downloading
>> sqlite-amalgamation-3080900.zip or sqlite3-autoconf-3080900.tar.gz?
> sqlite-amalgamation-3080900.zip

The sqlite3.c file in that ZIP archive should be 5516034 bytes in
size, 154880 lines long, and have an MD5 hash of
bef3e840d1e44e0e453a165f5b468ad7 and a SHA1 hash of
5ac3fb37f936c0748e1834ac633ce422870ea274.  Please verify that you are
using the correct sqlite3.c source file.

D. Richard Hipp
drh at sqlite.org

[sqlite] Best way to temporarily store data before processing

[Scott Hess]

On Tue, Apr 14, 2015 at 9:37 AM, Jim Callahan
 wrote:
> My recollection is that SQLite has a "temp" or "tmp" namespace available
> for intermediate tables -- it was on my todo list, but I never got around
> to exploring that option.

CREATE TEMP TABLE ... works very well for things like this.  If you
use a distinct table name you can just refer to it directly, or you
can say temp.tablename (vs main.tablename).

You can also create temp databases, where SQLite arranges for the
underlying file to go away on close (POSIX deletes after opening,
Windows uses DELETE-ON-CLOSE flags).  I believe you pass NULL at the
filename to sqlite3_open().  I haven't tried out whether you can also
do "ATTACH NULL AS mydb", but I'd bet you can.

The main advantage of temporary tables over a :memory: database is
that you don't have to worry about blowing out memory, because it can
spill to disk easily.  There's no journal (because temporary), so it
should be faster than a staging table in your database.  SQLite also
optimizes things if your temp tables can be handled entirely in the
cache, they'll never hit disk (and even if they hit an OS write, I
don't think they're ever sync'ed so the OS might never write things to
disk).

The main dis-advantage is that you can't checkpoint things.  If you
crash or shutdown, when you start back up the data is gone and you
have to start over.

-scott

[sqlite] Best way to temporarily store data before processing

[Jim Callahan]

?There are too many unknowns to give a definitive answer to your question.
Any solution you chose is going to create some stress somewhere (in your
"Prepared Tables" database, RAM, the file system and so forth.

Just from my own limited experience. It is very easy while hacking to
CREATE TABLE and DELETE FROM in your main database; the price one pays is
that running .VACUUM to clean up the mess becomes time consuming.

RAM would be fastest, but also the riskiest; any memory leak at all in your
programs handling "gigabytes" of data and you run the risk of having to
reboot the system. RAM files would also be at risk from power
interruptions. If you were sure your data would "only" be 100 megabytes
then you would have a substantial safety margin in RAM.

Using RAM from the outset might fall in the category of "premature
optimization". Usually one wants something that is robust and has the
option of leaving an audit trail -- if that solution is too slow then look
at putting parts of it in RAM. One typically needs an option of audit
trails because there is always screwy data out there -- that sooner or
later shows up and break one's system.

A separate database using ATTACH makes sense, but the question would be
whether to have a template database or to create the files. This is
somewhat a matter of personal preference. I found it was not that hard to
.DUMP the template databases and create an "all code" solution. From my
perspective, I found an "all code" solution to be easier to maintain
because I did not have to rely on what may or may not be in (a version of)
a  template database (and if necessary, I could change the template
databases in the same project code I was working on), but your
circumstances and preferences may be different. This also depends on the
expressiveness of your scripting language. I was using Windows batch
scripts which did not loop very well; so I had to use some ingenuity to do
repetitive batch operations. Python is a more expressive language.

My recollection is that SQLite has a "temp" or "tmp" namespace available
for intermediate tables -- it was on my todo list, but I never got around
to exploring that option.

Jim Callahan
Orlando, FL








On Tue, Apr 14, 2015 at 5:40 AM, Jonathan Moules  wrote:

> Hi List,
> I'm wondering if anyone can offer me a "best practice" way of doing this.
>
> I'm doing some log analysis using Python/SQLite. Python parses a log file
> and splits the raw data from each line in the log into one of about 40
> tables in an SQLite database (I'll call them Raw Tables).
>
> Once a log file has been processed like this, I run some SQL which takes
> the data from the Raw Tables and aggregates/processes it into about 10
> different "Prepared Tables" which are read with a bunch of Views. The
> aggregation/processing doesn't take long, and the SQL for it is simple.
>
> I'd like to update the Prepared Tables after each log file is read because
> there are thousands of files and I don't want to have to rely on having GB
> of disk space sitting around for temporary Raw Tables.
>
> Once the Prepared Tables have been created, there's no real need to keep
> the data in the Raw Tables.
>
> The Prepared Tables don't have to be in the same database as the Raw
> Tables. I'm happy to use ATTACH.
>
> So my question:
> What's the best way to do this with the minimum overhead?
>
> Options that have come to mind (probably missed a lot):
> - Some sort of empty template database for the Raw Tables
> which is copied/cloned/overwritten for each file processed.
> - And/Or use "DELETE FROM Raw_Tables" to truncate it after
> each file (there are no indexes).
> - And/Or place it into :memory:.
> - And/Or just CREATE the Raw Tables for each file?
> - And/Or do it within the Prepared Tables database and use
> "DELETE FROM Raw_Tables". (That file you wouldn't want in :memory: of
> course).
>
>
> Thoughts welcome, thanks for your time,
> Jonathan
>
> 
>
> HR Wallingford and its subsidiaries uses faxes and emails for confidential
> and legally privileged business communications. They do not of themselves
> create legal commitments. Disclosure to parties other than addressees
> requires our specific consent. We are not liable for unauthorised
> disclosures nor reliance upon them.
> If you have received this message in error please advise us immediately
> and destroy all copies of it.
>
> HR Wallingford Limited
> Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom
> Registered in England No. 02562099
>
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Best way to temporarily store data before processing

[Hick Gunter]

For native SQLite tables, DROP TABLE is much faster than DELETE FROM.

-Urspr?ngliche Nachricht-
Von: Jonathan Moules [mailto:J.Moules at hrwallingford.com]
Gesendet: Dienstag, 14. April 2015 11:40
An: 'sqlite-users at mailinglists.sqlite.org'
Betreff: [sqlite] Best way to temporarily store data before processing

Hi List,
I'm wondering if anyone can offer me a "best practice" way of doing this.

I'm doing some log analysis using Python/SQLite. Python parses a log file and 
splits the raw data from each line in the log into one of about 40 tables in an 
SQLite database (I'll call them Raw Tables).

Once a log file has been processed like this, I run some SQL which takes the 
data from the Raw Tables and aggregates/processes it into about 10 different 
"Prepared Tables" which are read with a bunch of Views. The 
aggregation/processing doesn't take long, and the SQL for it is simple.

I'd like to update the Prepared Tables after each log file is read because 
there are thousands of files and I don't want to have to rely on having GB of 
disk space sitting around for temporary Raw Tables.

Once the Prepared Tables have been created, there's no real need to keep the 
data in the Raw Tables.

The Prepared Tables don't have to be in the same database as the Raw Tables. 
I'm happy to use ATTACH.

So my question:
What's the best way to do this with the minimum overhead?

Options that have come to mind (probably missed a lot):
- Some sort of empty template database for the Raw Tables which is 
copied/cloned/overwritten for each file processed.
- And/Or use "DELETE FROM Raw_Tables" to truncate it after each 
file (there are no indexes).
- And/Or place it into :memory:.
- And/Or just CREATE the Raw Tables for each file?
- And/Or do it within the Prepared Tables database and use "DELETE 
FROM Raw_Tables". (That file you wouldn't want in :memory: of course).


Thoughts welcome, thanks for your time,
Jonathan



HR Wallingford and its subsidiaries uses faxes and emails for confidential and 
legally privileged business communications. They do not of themselves create 
legal commitments. Disclosure to parties other than addressees requires our 
specific consent. We are not liable for unauthorised disclosures nor reliance 
upon them.
If you have received this message in error please advise us immediately and 
destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom Registered in 
England No. 02562099


___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] SQLite to SQL Server

[Igor Korot]

Hi,

On Tue, Apr 14, 2015 at 10:31 AM, Drago, William @ CSG - NARDA-MITEQ
 wrote:
> Thanks for the tip. I've used dump before to move data around between SQLite 
> databases. I should have realized that I could have used it to move data to 
> SQL Server.

And you can use bcp (freebcp from freeTDS package) to move the data
from the text file in the SQL Server.
For 'bcp' I don't think any editing is required You may need to
check the result though. ;-)

Thank you.

>
> --
> Bill Drago
> Senior Engineer
> L3 Narda-MITEQ
> 435 Moreland Road
> Hauppauge, NY 11788
> 631-272-5947 / William.Drago at L-3COM.com
>
>
>> -Original Message-
>> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
>> users-bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin
>> Sent: Monday, April 13, 2015 6:24 PM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] SQLite to SQL Server
>>
>>
>> On 13 Apr 2015, at 10:38pm, Drago, William @ CSG - NARDA-MITEQ
>>  wrote:
>>
>> > Is there a convenient way to transfer data from SQLite to SQL Server?
>> I'm presently writing code to do a row by row transfer of all my
>> tables, but the thought crossed my mind that maybe there's a better
>> way.
>>
>> If you're just doing it once, use the SQLite shell tool to '.dump' the
>> database to a text file of SQL commands, then read the text file into a
>> fresh database in SQL Server.
>>
>> You may have to do some minor editing of the text file to make it
>> conform to SQL Server's notion of SQL rather than SQLite's notion of
>> SQL.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
> attachments are solely for the use of the addressee and may contain 
> information that is privileged or confidential. Any disclosure, use or 
> distribution of the information contained herein is prohibited. In the event 
> this e-mail contains technical data within the definition of the 
> International Traffic in Arms Regulations or Export Administration 
> Regulations, it is subject to the export control laws of the U.S.Government. 
> The recipient should check this e-mail and any attachments for the presence 
> of viruses as L-3 does not accept any liability associated with the 
> transmission of this e-mail. If you have received this communication in 
> error, please notify the sender by reply e-mail and immediately delete this 
> message and any attachments.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Getting a crash on 32-bit Linux

[Richard Hipp]

On 4/14/15, Ron Aaron  wrote:
> I just updated to the 3.8.9 version of SQLite for my project, and am
> compiling for 32-bit linux (on a 64-bit box).
>
>
> I'm getting a consistent crash in findInodeInfo, with this backtrace:
>
>
> #0  findInodeInfo (pFile=0x9438118, ppInode=0x9438120) at
> sqlite3.c:26091

Corresponds to this source code line:
https://www.sqlite.org/src/info/25b80a3d?ln=710

> #1  0x085d3234 in fillInUnixFile (pVfs=0x89422b0
> , h=5, pId=0x9438118, zFilename=0x9438208
> "/home/ron/proj/8th/test.db", ctrlFlags=0)
>  at sqlite3.c:30092

Corresponds to this source code line:
https://www.sqlite.org/src/info/25b80a3d?ln=4711

So I think you must be using a non-standard build of the sqlite3.c
amalgamation file, huh?

> #2  0x084fa69e in unixOpen (pVfs=0x89422b0 ,
> zPath=0x9438208 "/home/ron/proj/8th/test.db", pFile=0x9438118,
> flags=262, pOutFlags=0xd064)
>  at sqlite3.c:30697
> #3  0x0851a453 in sqlite3OsOpen (pVfs=0x89422b0
> , zPath=0x9438208
> "/home/ron/proj/8th/test.db", pFile=0x9438118, flags=262,
> pFlagsOut=0xd064)
>  at sqlite3.c:16093
> #4  0x08515c88 in sqlite3PagerOpen (pVfs=0x89422b0
> , ppPager=0x91c6af0, zFilename=0x92a7cf0
> "test.db", nExtra=76, flags=0, vfsFlags=262,
>  xReinit=0x8516110 ) at sqlite3.c:45520
> #5  0x085148f2 in sqlite3BtreeOpen (pVfs=0x89422b0
> , zFilename=0x92a7cf0 "test.db", db=0x9192fd0,
> ppBtree=0x919318c, flags=0, vfsFlags=262)
>  at sqlite3.c:54119
> #6  0x0850de97 in openDatabase (zFilename=0x91a1610 "test.db",
> ppDb=0x91a2028, flags=6, zVfs=0x0) at sqlite3.c:127475
> #7  0x0850d73a in sqlite3_open (zFilename=0x91a1610 "test.db",
> ppDb=0x91a2028) at sqlite3.c:127601
>
>
> Nothing has changed in my test code, and I haven't got a similar problem
> on 32-bit OS X;  however, it's certainly conceivable I'm not using the
> right flags when compiling SQLite:
>
> gcc   -I.  -DSQLITE_THREADSAFE=0 -DSQLITE_OMIT_AUTHORIZATION
> -DSQLITE_OMIT_DEPRECATED -DSQLITE_ENABLE_LOCKING_STYLE=0
> -DSQLITE_SYSTEM_MALLOC=1 -DSQLITE_OMIT_COMPILEOPTION_DIAGS
> -DSQLITE_OMIT_TRACE -O0 -c -o sqlite3.o sqlite3.c
>
>
> The crash occurs whether I work in a chroot lin 32 environment or a
> virtual machine running 'native'.  The test application is statically
> linked.
>
>
> Any clues would be appreciated!
>
>
> Thanks,
>
> Ron
>
>
>
> --
> Ron Aaron, CTO
> Aaron High-Tech, Ltd.
> +972.52.652.5543
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Best way to temporarily store data before processing

[Jonathan Moules]

Hi List,
I'm wondering if anyone can offer me a "best practice" way of doing this.

I'm doing some log analysis using Python/SQLite. Python parses a log file and 
splits the raw data from each line in the log into one of about 40 tables in an 
SQLite database (I'll call them Raw Tables).

Once a log file has been processed like this, I run some SQL which takes the 
data from the Raw Tables and aggregates/processes it into about 10 different 
"Prepared Tables" which are read with a bunch of Views. The 
aggregation/processing doesn't take long, and the SQL for it is simple.

I'd like to update the Prepared Tables after each log file is read because 
there are thousands of files and I don't want to have to rely on having GB of 
disk space sitting around for temporary Raw Tables.

Once the Prepared Tables have been created, there's no real need to keep the 
data in the Raw Tables.

The Prepared Tables don't have to be in the same database as the Raw Tables. 
I'm happy to use ATTACH.

So my question:
What's the best way to do this with the minimum overhead?

Options that have come to mind (probably missed a lot):
- Some sort of empty template database for the Raw Tables which is 
copied/cloned/overwritten for each file processed.
- And/Or use "DELETE FROM Raw_Tables" to truncate it after each 
file (there are no indexes).
- And/Or place it into :memory:.
- And/Or just CREATE the Raw Tables for each file?
- And/Or do it within the Prepared Tables database and use "DELETE 
FROM Raw_Tables". (That file you wouldn't want in :memory: of course).


Thoughts welcome, thanks for your time,
Jonathan



HR Wallingford and its subsidiaries uses faxes and emails for confidential and 
legally privileged business communications. They do not of themselves create 
legal commitments. Disclosure to parties other than addressees requires our 
specific consent. We are not liable for unauthorised disclosures nor reliance 
upon them.
If you have received this message in error please advise us immediately and 
destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom
Registered in England No. 02562099

[sqlite] Best way to temporarily store data before processing

[Joseph T.]

Mr. Moules, why not skip the raw tables entirely? Or failing that a separate in 
memory db would probably be the best and quickest option.



Sent from my Samsung Epic? 4G TouchJonathan Moules  wrote:Hi List,
I'm wondering if anyone can offer me a "best practice" way of doing this.

I'm doing some log analysis using Python/SQLite. Python parses a log file and 
splits the raw data from each line in the log into one of about 40 tables in an 
SQLite database (I'll call them Raw Tables).

Once a log file has been processed like this, I run some SQL which takes the 
data from the Raw Tables and aggregates/processes it into about 10 different 
"Prepared Tables" which are read with a bunch of Views. The 
aggregation/processing doesn't take long, and the SQL for it is simple.

I'd like to update the Prepared Tables after each log file is read because 
there are thousands of files and I don't want to have to rely on having GB of 
disk space sitting around for temporary Raw Tables.

Once the Prepared Tables have been created, there's no real need to keep the 
data in the Raw Tables.

The Prepared Tables don't have to be in the same database as the Raw Tables. 
I'm happy to use ATTACH.

So my question:
What's the best way to do this with the minimum overhead?

Options that have come to mind (probably missed a lot):
??? - Some sort of empty template database for the Raw Tables which is 
copied/cloned/overwritten for each file processed.
??? - And/Or use "DELETE FROM Raw_Tables" to truncate it after each 
file (there are no indexes).
??? - And/Or place it into :memory:.
??? - And/Or just CREATE the Raw Tables for each file?
??? - And/Or do it within the Prepared Tables database and use "DELETE 
FROM Raw_Tables". (That file you wouldn't want in :memory: of course).


Thoughts welcome, thanks for your time,
Jonathan



HR Wallingford and its subsidiaries uses faxes and emails for confidential and 
legally privileged business communications. They do not of themselves create 
legal commitments. Disclosure to parties other than addressees requires our 
specific consent. We are not liable for unauthorised disclosures nor reliance 
upon them.
If you have received this message in error please advise us immediately and 
destroy all copies of it.

HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom
Registered in England No. 02562099


___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite to SQL Server

[R.Smith]

On 2015-04-13 11:38 PM, Drago, William @ CSG - NARDA-MITEQ wrote:
> All,
>
> Is there a convenient way to transfer data from SQLite to SQL Server? I'm 
> presently writing code to do a row by row transfer of all my tables, but the 
> thought crossed my mind that maybe there's a better way.

Depends, a row by row transfer is not bad, it is what all transfers will 
boil down to. You might use the SQL Server native connector 
(sqlncli.dll) and use the api to bind the variables (just like in 
SQLite) for very fast transfers. Or you might compile a list of INSERT 
statements in MSSQL format, still quite fast.

If you are doing this as a once-off, there are tools that can do that 
for you (as far as exporting to SQL goes anyway). If you need to do this 
all the time, best is to make connector in your system. In the end, the 
data has to go from whatever form into SQL and get fed into whichever 
SQL engine you choose because the receiving engine needs the opportunity 
to do validation and constraint checking depending on its version of the 
schema.

Programmatically adjusting mutative schemata to have the same meaning 
when it transmits to  MSSQL (or any other SQL engine) is a rabbit hole 
I'd avoid.

I realize these statements are more vague than helpful at this point - 
if you could be more specific with the use case, I'm sure lots of people 
here have walked the route and will have options.

[sqlite] SQLite to SQL Server

[Simon Slavin]

On 13 Apr 2015, at 10:38pm, Drago, William @ CSG - NARDA-MITEQ  wrote:

> Is there a convenient way to transfer data from SQLite to SQL Server? I'm 
> presently writing code to do a row by row transfer of all my tables, but the 
> thought crossed my mind that maybe there's a better way.

If you're just doing it once, use the SQLite shell tool to '.dump' the database 
to a text file of SQL commands, then read the text file into a fresh database 
in SQL Server.

You may have to do some minor editing of the text file to make it conform to 
SQL Server's notion of SQL rather than SQLite's notion of SQL.

Simon.