[sqlite] Using the SQLite Online Backup AP
In the description of the backup API, (http://www.sqlite.org/backup.html) , it is said "It cannot be used to copy data to or from in-memory databases." which is followed by Example 1: Loading and Saving In-Memory Databases Isn't this contradictory ? If sqlite backup API is not meant for use with in-memory databases, the example should be removed,else it could be misleading.. Kind Regards, Sreekumar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT COUNT (DISTINCT column_name FROM table)
GNU C (Sourcery CodeBench 2011.03-95) version 4.5.2 (mips-linux-gnu) compiled by GNU C version 4.3.2, GMP version 4.3.2, MPFR version 2.4.2, MPC version 0.8.1 On Mon, Mar 5, 2012 at 6:12 PM, Richard Hipp <d...@sqlite.org> wrote: > On Mon, Mar 5, 2012 at 7:04 AM, Sreekumar TP <sreekumar...@gmail.com> > wrote: > > > The crash is a result of -O3 flag used during compiling the library. > > > > A compiler bug, then. OK. Good to know. SQLite has previously uncovered > bugs in GCC, MSVC, and LLVM. Which compiler are you using? > > > > > > > -Sreekumar > > > > > > > > On Sat, Mar 3, 2012 at 2:00 PM, Sreekumar TP <sreekumar...@gmail.com> > > wrote: > > > > > Its does not crash on x86. I havent built the shell for my mips device. > > > > > > Sreekumar > > > On Mar 3, 2012 10:26 AM, "Dan Kennedy" <danielk1...@gmail.com> wrote: > > > > > >> On 03/03/2012 10:30 AM, Sreekumar TP wrote: > > >> > > >>> Could someone throw some light on this issue too? > > >>> > > >> > > >> I can't see from the stack trace why this is crashing. > > >> > > >> Does it crash if you run the query from the sqlite shell? > > >> > > >> Maybe try building the shell without optimizations, and > > >> then running it under valgrind. > > >> > > >> Dan. > > >> > > >> > > >> > > >>> Sreekumar > > >>> On Mar 2, 2012 10:05 AM, "Sreekumar TP"<sreekumar...@gmail.com> > > wrote: > > >>> > > >>> The backtrace > > >>>> === > > >>>> > > >>>> > > >>>> Program received signal SIGSEGV, Segmentation fault. > > >>>> 0x2b657288 in sqlite3Parser (yyp=0x2d401e40, yymajor=119, > yyminor=..., > > >>>> pParse=0x2d4035c8) at sqlite3.c:106740 > > >>>> 106740 }while( yymajor!=YYNOCODE&& yypParser->yyidx>=0 ); > > >>>> #0 0x2b657288 in sqlite3Parser (yyp=0x2d401e40, yymajor=119, > > >>>> yyminor=..., > > >>>> pParse=0x2d4035c8) at sqlite3.c:106740 > > >>>> yyminorunion = { > > >>>> yyinit = 735636932, > > >>>> yy0 = { > > >>>> z = 0x2bd8edc4 "FROM dir_table", > > >>>> n = 4 > > >>>> }, > > >>>> yy4 = 735636932, > > >>>> yy90 = { > > >>>> a = 735636932, > > >>>> b = 0x4 > > >>>> }, > > >>>> yy118 = { > > >>>> pExpr = 0x2bd8edc4, > > >>>> zStart = 0x4, > > >>>> zEnd = 0x2b697000 "" > > >>>> }, > > >>>> yy203 = 0x2bd8edc4, > > >>>> yy210 = 196 '\304', > > >>>> yy215 = { > > >>>> value = 735636932, > > >>>> mask = 4 > > >>>> }, > > >>>> yy259 = 0x2bd8edc4, > > >>>> yy292 = { > > >>>> pLimit = 0x2bd8edc4, > > >>>> pOffset = 0x4 > > >>>> }, > > >>>> yy314 = 0x2bd8edc4, > > >>>> yy322 = 0x2bd8edc4, > > >>>> yy342 = { > > >>>> eOperator = { > > >>>> z = 0x2bd8edc4 "FROM dir_table", > > >>>> n = 4 > > >>>> }, > > >>>> not = 728330240 > > >>>> }, > > >>>> yy384 = 0x2bd8edc4, > > >>>> yy387 = 0x2bd8edc4 > > >>>> } > > >>>> yyact = 21104640 > > >>>> yyendofinput = 0 > > >>>> yypParser = 0x2d401e40 > > >>>> #1 0x2b65a210 in sqlite3RunParser (pParse=0x2d4035c8, > zSql=0x2bd8eda4 > > >>>> "SELECT COUNT(distinct Alb_Name) FROM dir_table", > pzErrMsg=0x2bd8cbf8) > > >>>> at > > >>>> sqlite3.c:107465 > >
Re: [sqlite] SELECT COUNT (DISTINCT column_name FROM table)
The crash is a result of -O3 flag used during compiling the library. -Sreekumar On Sat, Mar 3, 2012 at 2:00 PM, Sreekumar TP <sreekumar...@gmail.com> wrote: > Its does not crash on x86. I havent built the shell for my mips device. > > Sreekumar > On Mar 3, 2012 10:26 AM, "Dan Kennedy" <danielk1...@gmail.com> wrote: > >> On 03/03/2012 10:30 AM, Sreekumar TP wrote: >> >>> Could someone throw some light on this issue too? >>> >> >> I can't see from the stack trace why this is crashing. >> >> Does it crash if you run the query from the sqlite shell? >> >> Maybe try building the shell without optimizations, and >> then running it under valgrind. >> >> Dan. >> >> >> >>> Sreekumar >>> On Mar 2, 2012 10:05 AM, "Sreekumar TP"<sreekumar...@gmail.com> wrote: >>> >>> The backtrace >>>> === >>>> >>>> >>>> Program received signal SIGSEGV, Segmentation fault. >>>> 0x2b657288 in sqlite3Parser (yyp=0x2d401e40, yymajor=119, yyminor=..., >>>> pParse=0x2d4035c8) at sqlite3.c:106740 >>>> 106740 }while( yymajor!=YYNOCODE&& yypParser->yyidx>=0 ); >>>> #0 0x2b657288 in sqlite3Parser (yyp=0x2d401e40, yymajor=119, >>>> yyminor=..., >>>> pParse=0x2d4035c8) at sqlite3.c:106740 >>>> yyminorunion = { >>>> yyinit = 735636932, >>>> yy0 = { >>>> z = 0x2bd8edc4 "FROM dir_table", >>>> n = 4 >>>> }, >>>> yy4 = 735636932, >>>> yy90 = { >>>> a = 735636932, >>>> b = 0x4 >>>> }, >>>> yy118 = { >>>> pExpr = 0x2bd8edc4, >>>> zStart = 0x4, >>>> zEnd = 0x2b697000 "" >>>> }, >>>> yy203 = 0x2bd8edc4, >>>> yy210 = 196 '\304', >>>> yy215 = { >>>> value = 735636932, >>>> mask = 4 >>>> }, >>>> yy259 = 0x2bd8edc4, >>>> yy292 = { >>>> pLimit = 0x2bd8edc4, >>>> pOffset = 0x4 >>>> }, >>>> yy314 = 0x2bd8edc4, >>>> yy322 = 0x2bd8edc4, >>>> yy342 = { >>>> eOperator = { >>>> z = 0x2bd8edc4 "FROM dir_table", >>>> n = 4 >>>> }, >>>> not = 728330240 >>>> }, >>>> yy384 = 0x2bd8edc4, >>>> yy387 = 0x2bd8edc4 >>>> } >>>> yyact = 21104640 >>>> yyendofinput = 0 >>>> yypParser = 0x2d401e40 >>>> #1 0x2b65a210 in sqlite3RunParser (pParse=0x2d4035c8, zSql=0x2bd8eda4 >>>> "SELECT COUNT(distinct Alb_Name) FROM dir_table", pzErrMsg=0x2bd8cbf8) >>>> at >>>> sqlite3.c:107465 >>>> nErr = 0 >>>> i = 36 >>>> pEngine = 0x2d401e40 >>>> tokenType = 119 >>>> lastTokenParsed = >>>> enableLookaside = 1 '\001' >>>> db = 0x6a14b0 >>>> mxSqlLen = 10 >>>> #2 0x2b65ac68 in sqlite3Prepare (db=0x6a14b0, zSql=0x2bd8eda4 "SELECT >>>> COUNT(distinct Alb_Name) FROM dir_table", nBytes=-1, saveSqlFlag=0, >>>> pReprepare=0x0, ppStmt=0x2bd8cca8, pzTail=0x0) at sqlite3.c:90212 >>>> pParse = 0x2d4035c8 >>>> zErrMsg = 0x0 >>>> rc = >>>> i = >>>> #3 0x2b65b468 in sqlite3LockAndPrepare (db=, >>>> zSql=, nBytes=-1, ppStmt=, >>>> pzTail=0x0) at sqlite3.c:90304 >>>> rc = >>>> #4 sqlite3_prepare (db=, zSql=>>> out>, >>>> nBytes=-1, ppStmt=, pzTail=0x0) at sqlite3.c:24831 >>>> rc = 21104640 >>>> #5 0x00466730 in sql_stmt (db=9, stmt=0xffc0>>> of bounds>) at /localhome/user/a/b/c/d/e/f/g/**h/k.c:496 >>>> errmsg = 0x0 >>>> retval = >>>> __PRETTY_FUNCTION__ = "\000\000\000\000\000\000\000\**000" >>>> >>>> >>>> >
Re: [sqlite] SELECT COUNT (DISTINCT column_name FROM table)
Its does not crash on x86. I havent built the shell for my mips device. Sreekumar On Mar 3, 2012 10:26 AM, "Dan Kennedy" <danielk1...@gmail.com> wrote: > On 03/03/2012 10:30 AM, Sreekumar TP wrote: > >> Could someone throw some light on this issue too? >> > > I can't see from the stack trace why this is crashing. > > Does it crash if you run the query from the sqlite shell? > > Maybe try building the shell without optimizations, and > then running it under valgrind. > > Dan. > > > >> Sreekumar >> On Mar 2, 2012 10:05 AM, "Sreekumar TP"<sreekumar...@gmail.com> wrote: >> >> The backtrace >>> === >>> >>> >>> Program received signal SIGSEGV, Segmentation fault. >>> 0x2b657288 in sqlite3Parser (yyp=0x2d401e40, yymajor=119, yyminor=..., >>> pParse=0x2d4035c8) at sqlite3.c:106740 >>> 106740 }while( yymajor!=YYNOCODE&& yypParser->yyidx>=0 ); >>> #0 0x2b657288 in sqlite3Parser (yyp=0x2d401e40, yymajor=119, >>> yyminor=..., >>> pParse=0x2d4035c8) at sqlite3.c:106740 >>> yyminorunion = { >>> yyinit = 735636932, >>> yy0 = { >>> z = 0x2bd8edc4 "FROM dir_table", >>> n = 4 >>> }, >>> yy4 = 735636932, >>> yy90 = { >>> a = 735636932, >>> b = 0x4 >>> }, >>> yy118 = { >>> pExpr = 0x2bd8edc4, >>> zStart = 0x4, >>> zEnd = 0x2b697000 "" >>> }, >>> yy203 = 0x2bd8edc4, >>> yy210 = 196 '\304', >>> yy215 = { >>> value = 735636932, >>> mask = 4 >>> }, >>> yy259 = 0x2bd8edc4, >>> yy292 = { >>> pLimit = 0x2bd8edc4, >>> pOffset = 0x4 >>> }, >>> yy314 = 0x2bd8edc4, >>> yy322 = 0x2bd8edc4, >>> yy342 = { >>> eOperator = { >>> z = 0x2bd8edc4 "FROM dir_table", >>> n = 4 >>> }, >>> not = 728330240 >>> }, >>> yy384 = 0x2bd8edc4, >>> yy387 = 0x2bd8edc4 >>> } >>> yyact = 21104640 >>> yyendofinput = 0 >>> yypParser = 0x2d401e40 >>> #1 0x2b65a210 in sqlite3RunParser (pParse=0x2d4035c8, zSql=0x2bd8eda4 >>> "SELECT COUNT(distinct Alb_Name) FROM dir_table", pzErrMsg=0x2bd8cbf8) at >>> sqlite3.c:107465 >>> nErr = 0 >>> i = 36 >>> pEngine = 0x2d401e40 >>> tokenType = 119 >>> lastTokenParsed = >>> enableLookaside = 1 '\001' >>> db = 0x6a14b0 >>> mxSqlLen = 10 >>> #2 0x2b65ac68 in sqlite3Prepare (db=0x6a14b0, zSql=0x2bd8eda4 "SELECT >>> COUNT(distinct Alb_Name) FROM dir_table", nBytes=-1, saveSqlFlag=0, >>> pReprepare=0x0, ppStmt=0x2bd8cca8, pzTail=0x0) at sqlite3.c:90212 >>> pParse = 0x2d4035c8 >>> zErrMsg = 0x0 >>> rc = >>> i = >>> #3 0x2b65b468 in sqlite3LockAndPrepare (db=, >>> zSql=, nBytes=-1, ppStmt=, >>> pzTail=0x0) at sqlite3.c:90304 >>> rc = >>> #4 sqlite3_prepare (db=, zSql=>> out>, >>> nBytes=-1, ppStmt=, pzTail=0x0) at sqlite3.c:24831 >>> rc = 21104640 >>> #5 0x00466730 in sql_stmt (db=9, stmt=0xffc0>> of bounds>) at /localhome/user/a/b/c/d/e/f/g/**h/k.c:496 >>> errmsg = 0x0 >>> retval = >>> __PRETTY_FUNCTION__ = "\000\000\000\000\000\000\000\**000" >>> >>> >>> >>> >>> >>> >>> >>> >>> On Thu, Mar 1, 2012 at 11:42 PM, Dan Kennedy<danielk1...@gmail.com>** >>> wrote: >>> >>> On 03/01/2012 10:54 PM, Sreekumar TP wrote: >>>> >>>> version 3.7.7.1 >>>>> The query works on x86, but fails on MIPS processor! >>>>> >>>>> >>>> Are you able to post a stack trace? Ideally generated by >>>> the gdb "where full" command. Thanks. >>>> >>>
Re: [sqlite] SELECT COUNT (DISTINCT column_name FROM table)
Could someone throw some light on this issue too? Sreekumar On Mar 2, 2012 10:05 AM, "Sreekumar TP" <sreekumar...@gmail.com> wrote: > The backtrace > === > > > Program received signal SIGSEGV, Segmentation fault. > 0x2b657288 in sqlite3Parser (yyp=0x2d401e40, yymajor=119, yyminor=..., > pParse=0x2d4035c8) at sqlite3.c:106740 > 106740 }while( yymajor!=YYNOCODE && yypParser->yyidx>=0 ); > #0 0x2b657288 in sqlite3Parser (yyp=0x2d401e40, yymajor=119, yyminor=..., > pParse=0x2d4035c8) at sqlite3.c:106740 > yyminorunion = { > yyinit = 735636932, > yy0 = { > z = 0x2bd8edc4 "FROM dir_table", > n = 4 > }, > yy4 = 735636932, > yy90 = { > a = 735636932, > b = 0x4 > }, > yy118 = { > pExpr = 0x2bd8edc4, > zStart = 0x4 , > zEnd = 0x2b697000 "" > }, > yy203 = 0x2bd8edc4, > yy210 = 196 '\304', > yy215 = { > value = 735636932, > mask = 4 > }, > yy259 = 0x2bd8edc4, > yy292 = { > pLimit = 0x2bd8edc4, > pOffset = 0x4 > }, > yy314 = 0x2bd8edc4, > yy322 = 0x2bd8edc4, > yy342 = { > eOperator = { > z = 0x2bd8edc4 "FROM dir_table", > n = 4 > }, > not = 728330240 > }, > yy384 = 0x2bd8edc4, > yy387 = 0x2bd8edc4 > } > yyact = 21104640 > yyendofinput = 0 > yypParser = 0x2d401e40 > #1 0x2b65a210 in sqlite3RunParser (pParse=0x2d4035c8, zSql=0x2bd8eda4 > "SELECT COUNT(distinct Alb_Name) FROM dir_table", pzErrMsg=0x2bd8cbf8) at > sqlite3.c:107465 > nErr = 0 > i = 36 > pEngine = 0x2d401e40 > tokenType = 119 > lastTokenParsed = > enableLookaside = 1 '\001' > db = 0x6a14b0 > mxSqlLen = 10 > #2 0x2b65ac68 in sqlite3Prepare (db=0x6a14b0, zSql=0x2bd8eda4 "SELECT > COUNT(distinct Alb_Name) FROM dir_table", nBytes=-1, saveSqlFlag=0, > pReprepare=0x0, ppStmt=0x2bd8cca8, pzTail=0x0) at sqlite3.c:90212 > pParse = 0x2d4035c8 > zErrMsg = 0x0 > rc = > i = > #3 0x2b65b468 in sqlite3LockAndPrepare (db=, > zSql=, nBytes=-1, ppStmt=, > pzTail=0x0) at sqlite3.c:90304 > rc = > #4 sqlite3_prepare (db=, zSql=, > nBytes=-1, ppStmt=, pzTail=0x0) at sqlite3.c:24831 > rc = 21104640 > #5 0x00466730 in sql_stmt (db=9, stmt=0xffc0 of bounds>) at /localhome/user/a/b/c/d/e/f/g/h/k.c:496 > errmsg = 0x0 > retval = > __PRETTY_FUNCTION__ = "\000\000\000\000\000\000\000\000" > > > > > > > > > On Thu, Mar 1, 2012 at 11:42 PM, Dan Kennedy <danielk1...@gmail.com>wrote: > >> On 03/01/2012 10:54 PM, Sreekumar TP wrote: >> >>> version 3.7.7.1 >>> The query works on x86, but fails on MIPS processor! >>> >> >> Are you able to post a stack trace? Ideally generated by >> the gdb "where full" command. Thanks. >> >> Dan. >> >> >> >>> >>> Sreekumar >>> >>> >>> >>> On Thu, Mar 1, 2012 at 7:00 PM, Dan Kennedy<danielk1...@gmail.com> >>> wrote: >>> >>> On 03/01/2012 05:48 PM, Sreekumar TP wrote: >>>> >>>> In my system, the statement causes sqlite3parser function to crash. >>>>> My compiler is mips , little endian, gcc version is 4.5.2 >>>>> >>>>> >>>> SQLite version? 3.7.10 seems Ok here. >>>> >>>> Dan. >>>> >>>> ___ >>>> 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<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<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<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] SELECT COUNT (DISTINCT column_name FROM table)
The backtrace === Program received signal SIGSEGV, Segmentation fault. 0x2b657288 in sqlite3Parser (yyp=0x2d401e40, yymajor=119, yyminor=..., pParse=0x2d4035c8) at sqlite3.c:106740 106740 }while( yymajor!=YYNOCODE && yypParser->yyidx>=0 ); #0 0x2b657288 in sqlite3Parser (yyp=0x2d401e40, yymajor=119, yyminor=..., pParse=0x2d4035c8) at sqlite3.c:106740 yyminorunion = { yyinit = 735636932, yy0 = { z = 0x2bd8edc4 "FROM dir_table", n = 4 }, yy4 = 735636932, yy90 = { a = 735636932, b = 0x4 }, yy118 = { pExpr = 0x2bd8edc4, zStart = 0x4 , zEnd = 0x2b697000 "" }, yy203 = 0x2bd8edc4, yy210 = 196 '\304', yy215 = { value = 735636932, mask = 4 }, yy259 = 0x2bd8edc4, yy292 = { pLimit = 0x2bd8edc4, pOffset = 0x4 }, yy314 = 0x2bd8edc4, yy322 = 0x2bd8edc4, yy342 = { eOperator = { z = 0x2bd8edc4 "FROM dir_table", n = 4 }, not = 728330240 }, yy384 = 0x2bd8edc4, yy387 = 0x2bd8edc4 } yyact = 21104640 yyendofinput = 0 yypParser = 0x2d401e40 #1 0x2b65a210 in sqlite3RunParser (pParse=0x2d4035c8, zSql=0x2bd8eda4 "SELECT COUNT(distinct Alb_Name) FROM dir_table", pzErrMsg=0x2bd8cbf8) at sqlite3.c:107465 nErr = 0 i = 36 pEngine = 0x2d401e40 tokenType = 119 lastTokenParsed = enableLookaside = 1 '\001' db = 0x6a14b0 mxSqlLen = 10 #2 0x2b65ac68 in sqlite3Prepare (db=0x6a14b0, zSql=0x2bd8eda4 "SELECT COUNT(distinct Alb_Name) FROM dir_table", nBytes=-1, saveSqlFlag=0, pReprepare=0x0, ppStmt=0x2bd8cca8, pzTail=0x0) at sqlite3.c:90212 pParse = 0x2d4035c8 zErrMsg = 0x0 rc = i = #3 0x2b65b468 in sqlite3LockAndPrepare (db=, zSql=, nBytes=-1, ppStmt=, pzTail=0x0) at sqlite3.c:90304 rc = #4 sqlite3_prepare (db=, zSql=, nBytes=-1, ppStmt=, pzTail=0x0) at sqlite3.c:24831 rc = 21104640 #5 0x00466730 in sql_stmt (db=9, stmt=0xffc0 ) at /localhome/user/a/b/c/d/e/f/g/h/k.c:496 errmsg = 0x0 retval = __PRETTY_FUNCTION__ = "\000\000\000\000\000\000\000\000" On Thu, Mar 1, 2012 at 11:42 PM, Dan Kennedy <danielk1...@gmail.com> wrote: > On 03/01/2012 10:54 PM, Sreekumar TP wrote: > >> version 3.7.7.1 >> The query works on x86, but fails on MIPS processor! >> > > Are you able to post a stack trace? Ideally generated by > the gdb "where full" command. Thanks. > > Dan. > > > >> >> Sreekumar >> >> >> >> On Thu, Mar 1, 2012 at 7:00 PM, Dan Kennedy<danielk1...@gmail.com> >> wrote: >> >> On 03/01/2012 05:48 PM, Sreekumar TP wrote: >>> >>> In my system, the statement causes sqlite3parser function to crash. >>>> My compiler is mips , little endian, gcc version is 4.5.2 >>>> >>>> >>> SQLite version? 3.7.10 seems Ok here. >>> >>> Dan. >>> >>> ___ >>> 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<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<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<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] SELECT COUNT (DISTINCT column_name FROM table)
yes, will post the trace. Sreekumar On Mar 1, 2012 11:42 PM, "Dan Kennedy" <danielk1...@gmail.com> wrote: > On 03/01/2012 10:54 PM, Sreekumar TP wrote: > >> version 3.7.7.1 >> The query works on x86, but fails on MIPS processor! >> > > Are you able to post a stack trace? Ideally generated by > the gdb "where full" command. Thanks. > > Dan. > > > >> >> Sreekumar >> >> >> >> On Thu, Mar 1, 2012 at 7:00 PM, Dan Kennedy<danielk1...@gmail.com> >> wrote: >> >> On 03/01/2012 05:48 PM, Sreekumar TP wrote: >>> >>> In my system, the statement causes sqlite3parser function to crash. >>>> My compiler is mips , little endian, gcc version is 4.5.2 >>>> >>>> >>> SQLite version? 3.7.10 seems Ok here. >>> >>> Dan. >>> >>> ___ >>> 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<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<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<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] SELECT COUNT (DISTINCT column_name FROM table)
version 3.7.7.1 The query works on x86, but fails on MIPS processor! Sreekumar On Thu, Mar 1, 2012 at 7:00 PM, Dan Kennedy <danielk1...@gmail.com> wrote: > On 03/01/2012 05:48 PM, Sreekumar TP wrote: > >> In my system, the statement causes sqlite3parser function to crash. >> My compiler is mips , little endian, gcc version is 4.5.2 >> > > SQLite version? 3.7.10 seems Ok here. > > Dan. > > __**_ > 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> > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT COUNT (DISTINCT column_name FROM table)
In my system, the statement causes sqlite3parser function to crash. My compiler is mips , little endian, gcc version is 4.5.2 -Sreekumar On Thu, Mar 1, 2012 at 2:59 PM, Simon Davies <simon.james.dav...@gmail.com>wrote: > On 1 March 2012 09:22, Sreekumar TP <sreekumar...@gmail.com> wrote: > > doesnt work either. > > > > In what way does it fail to live up to your expectations? > > Regards, > Simon > ___ > 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] SELECT COUNT (DISTINCT column_name FROM table)
doesnt work either. On Thu, Mar 1, 2012 at 2:50 PM, Petite Abeille <petite.abei...@gmail.com>wrote: > > On Mar 1, 2012, at 9:46 AM, Sreekumar TP wrote: > > > Is SELECT COUNT (DISTINCT column_name FROM table) syntax supported ? > > try: > > select count( distinct foo ) from bar > > ___ > 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] SELECT COUNT (DISTINCT column_name FROM table)
Hi, Is SELECT COUNT (DISTINCT column_name FROM table) syntax supported ? The above causes a crash in sqlite3Parser function.. -Regards, Sreekumar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked in multi process scenario
Can this situation be handled in sqlite - by upgrading the lock to a writer lock ? Since both applications use the same WAL file for read and writes, it shouldnt be a problem , because all changes will be in linear sequence ? Sreekumar On Fri, Feb 10, 2012 at 10:49 PM, Sreekumar TP <sreekumar...@gmail.com>wrote: > Though the example of $ is very intuitive, I am not suggesting that we > drop one of the transaction and block the database forever (as it is > happening now). Instead, it could be serialized such that two $100 > transactions are committed to the db. > > > > On Fri, Feb 10, 2012 at 10:33 PM, Igor Tandetnik <itandet...@mvps.org>wrote: > >> On 2/10/2012 9:57 AM, Sreekumar TP wrote: >> >>> The last transaction should always be the final one. In a a >>> multiprocess/threaded application how can one make assumptions on the >>> order >>> of updates? >>> >> >> There are two updates in my example: >> >> >> update t set count = count + 1; >> update t set count = count + 10; >> >> Do you feel it unreasonable to assume that, after these two statements >> are executed successfully, in any order, the value of count should increase >> by 11? >> >> If two $100 deposits to your bank account are made by different parties >> at approximately the same time, I think you'd be pretty upset if the >> account balance didn't increase by precisely $200. >> >> -- >> Igor Tandetnik >> >> __**_ >> 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> >> > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked in multi process scenario
Though the example of $ is very intuitive, I am not suggesting that we drop one of the transaction and block the database forever (as it is happening now). Instead, it could be serialized such that two $100 transactions are committed to the db. On Fri, Feb 10, 2012 at 10:33 PM, Igor Tandetnik <itandet...@mvps.org>wrote: > On 2/10/2012 9:57 AM, Sreekumar TP wrote: > >> The last transaction should always be the final one. In a a >> multiprocess/threaded application how can one make assumptions on the >> order >> of updates? >> > > There are two updates in my example: > > > update t set count = count + 1; > update t set count = count + 10; > > Do you feel it unreasonable to assume that, after these two statements are > executed successfully, in any order, the value of count should increase by > 11? > > If two $100 deposits to your bank account are made by different parties at > approximately the same time, I think you'd be pretty upset if the account > balance didn't increase by precisely $200. > > -- > Igor Tandetnik > > __**_ > 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> > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked in multi process scenario
There is no recovery from this situation- If you try to rollback, you get the following error -"cannot rollback savepoint, SQL statments in progress" or if you dont use SAVEPOINT - "cannot rollback, no transaction is active " If you start the transaction with BEGIN IMMEDIATE in App1, the writer in App2 gets the following error " database is locked" Sreekumar On Fri, Feb 10, 2012 at 8:13 PM, Igor Tandetnik <itandet...@mvps.org> wrote: > Marc L. Allen <mlal...@outsitenetworks.com> wrote: > > I see. So, the implied commit doesn't occur until you finalize? > > Or reset. > > > As a result, the subsequent update in step 5 was added to his > > non-finalized select? > > The update was attempted within the same transaction. > > > Still.. what is the correct way to handle the explicit scenario? I > mean, having one process do a BEGIN SELECT UPDATE and another > > do BEGIN UPDATE is perfectly reasonable, isn't it? How do you protect > from a problem? Detect the error, rollback, and try > > again? > > That's one way. The other is for the first connection to start its > transaction with BEGIN IMMEDIATE, thus marking itself as a writer from the > start. > -- > Igor Tandetnik > > ___ > 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] Database locked in multi process scenario
I took traces of the lock/unlock pattern - After App1 SELECT -- fcntl -1212610880 7 SETLK WRLCK 124 1 0 0 WAL806F9D8: acquire EXCLUSIVE-READ-LOCK[1] cnt=1 ok fcntl -1212610880 7 SETLK UNLCK 124 1 0 0 WAL806F9D8: release EXCLUSIVE-READ-LOCK[1] cnt=1 fcntl -1212610880 7 SETLK RDLCK 124 1 0 0 WAL806F9D8: acquire SHARED-READ-LOCK[1] ok After App2 UPDATE - fcntl -1212344640 7 SETLK RDLCK 124 1 0 0 WAL806F570: acquire SHARED-READ-LOCK[1] ok fcntl -1212344640 7 SETLK WRLCK 120 1 0 0 WAL806F570: acquire EXCLUSIVE-WRITE-LOCK cnt=1 ok WAL806F570: frame write ok fcntl -1212344640 7 SETLK UNLCK 120 1 0 0 WAL806F570: release EXCLUSIVE-WRITE-LOCK cnt=1 fcntl -1212344640 7 SETLK UNLCK 124 1 0 0 WAL806F570: release SHARED-READ-LOCK[1] After App1 SELECT -- fcntl -1212610880 7 SETLK WRLCK 120 1 0 0 WAL806F9D8: acquire EXCLUSIVE-WRITE-LOCK cnt=1 ok fcntl -1212610880 7 SETLK UNLCK 120 1 0 0 WAL806F9D8: release EXCLUSIVE-WRITE-LOCK cnt=1 error @ 225, 1,5,database is locked After App2 UPDATE ( a second UPDATE ) - fcntl -1212344640 7 SETLK WRLCK 124 1 0 -1 fcntl-failure-reason: RDLCK 124 1 15295 WAL806F570: acquire EXCLUSIVE-READ-LOCK[1] cnt=1 failed fcntl -1212344640 7 SETLK WRLCK 125 1 0 0 WAL806F570: acquire EXCLUSIVE-READ-LOCK[2] cnt=1 ok fcntl -1212344640 7 SETLK UNLCK 125 1 0 0 WAL806F570: release EXCLUSIVE-READ-LOCK[2] cnt=1 fcntl -1212344640 7 SETLK RDLCK 125 1 0 0 WAL806F570: acquire SHARED-READ-LOCK[2] ok fcntl -1212344640 7 SETLK WRLCK 120 1 0 0 WAL806F570: acquire EXCLUSIVE-WRITE-LOCK cnt=1 ok fcntl -1212344640 7 SETLK UNLCK 120 1 0 0 WAL806F570: release EXCLUSIVE-WRITE-LOCK cnt=1 fcntl -1212344640 7 SETLK UNLCK 125 1 0 0 WAL806F570: release SHARED-READ-LOCK[2] Additional Info - You can also see that acquiring an EXCLUSIVE-READ-LOCK FAILED during the a second update by App2. The fcntl error translated means "resource temporarily unavailable" - Sreekumar On Fri, Feb 10, 2012 at 8:31 PM, Marc L. Allen <mlal...@outsitenetworks.com>wrote: > One last question or series (I hope)... > > From my background, I'm used to SQL statements blocking until appropriate > locks are acquired. From what I've seen, it looks like sqlite doesn't > block, but returns BUSY, is that correct? > > If two processes start a BEGIN IMMEDIATE, will one return a BUSY or will > it block and wait? > > If it returns BUSY, how does sqlite3_exec() handle it? > > (Btw.. I know there are books on sqlite, so if you want to point me to one > that answers all these questions, or if I'm missing it from the online > docs, just let me know. I just haven't found them.) > > Thanks > > > -Original Message- > > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > > boun...@sqlite.org] On Behalf Of Marc L. Allen > > Sent: Friday, February 10, 2012 9:45 AM > > To: General Discussion of SQLite Database > > Subject: Re: [sqlite] Database locked in multi process scenario > > > > Thanks so much for clarifying that. I was unaware of the BEGIN > > IMMEDIATE. Sorry.. new to sqlite, used to MySQL and MSSQL. > > > > > -Original Message- > > > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > > > boun...@sqlite.org] On Behalf Of Igor Tandetnik > > > Sent: Friday, February 10, 2012 9:43 AM > > > To: sqlite-users@sqlite.org > > > Subject: Re: [sqlite] Database locked in multi process scenario > > > > > > Marc L. Allen <mlal...@outsitenetworks.com> wrote: > > > > I see. So, the implied commit doesn't occur until you finalize? > > > > > > Or reset. > > > > > > > As a result, the subsequent update in step 5 was added to his > > > > non-finalized select? > > > > > > The update was attempted within the same transaction. > > > > > > > Still.. what is the correct way to handle the explicit scenario? I > > > > mean, having one process do a BEGIN SELECT UPDATE and another do > > > BEGIN > > > > UPDATE is perfectly reasonable, isn't it? How do you protect from > > a > > > problem? Detect the error, rollback, and try again? > > > > > > That's one way. The other is for the first connection to start its > > > transaction with BEGIN IMMEDIATE, thus marking itself as a writer > > from > > > the start. > > > -- > > > Igor Tandetnik > > > > > > ___ > > > 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked in multi process scenario
The last transaction should always be the final one. In a a multiprocess/threaded application how can one make assumptions on the order of updates? Sreekumar On Fri, Feb 10, 2012 at 8:16 PM, Igor Tandetnik <itandet...@mvps.org> wrote: > Sreekumar TP <sreekumar...@gmail.com> wrote: > > How is this different from two threads each with a db connection in a > > single process? > > If each thread uses its own separate connection, it should be no different > - you would observe the same issue. > > > Moreover the journal mode is WAL. Hence the writer should be able to > append > > changes to the WAL file as there are no other write transaction. > > Your problem is with a transaction stat starts as a reader, and later > tries to become a writer. This is only possible if the reader is observing > the most recent state of the database, that is, if there were no writes > since it started. > > Consider: > > // initial setup > create table t(count integer); > insert into t values (0); > > /* 1 */ select count from t; > /* 2 */ update t set count = count + 10; > /* 1 */ update t set count = count + 1; // (!) > /* 1 */ select count from t; // (!!) > > /* 1 */ and /* 2 */ mark operations performed by two separate > transactions. Imagine that such a sequence were possible, and the update at > (!) succeeded. What value should count have after this update? If it's 11, > then a select at (!!) would effectively observe a change written by a > different transaction, violating transaction isolation. If it's 1, then an > observer in yet third connection could see the count go up, then down - > which is surprising as the update statements only ever increment it. > > Neither outcome is particularly appealing, so the sequence is prohibited > altogether. > > There are several ways in which transactions that start as readers and > later promote themselves to writers may cause problems. It's best to avoid > such situations: if you know that you may need to write eventually, start > your transaction with BEGIN IMMEDIATE, then it would be marked as a writer > from the outset. > -- > Igor Tandetnik > > ___ > 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] Database locked in multi process scenario
In the real code, there is no sleep/wait or pause. It so happens that the write of the app2 is scheduled in between. What you are suggesting is that at any point of time only one process can have a transaction open in a database? Sreekumar On Feb 10, 2012 7:12 PM, "Simon Slavin" <slav...@bigfraud.org> wrote: > > On 10 Feb 2012, at 1:32pm, Sreekumar TP wrote: > > > well, the 'wait' is a simulation of what happens in the real code. > > > > The error is fatal to the application as it never ever recovers from it > > even though the writer has finalized and terminated. > > In a multi-process environment I recommend that you do not pause for such > a long time between the first _step() and the _reset() or _finalize(). You > can _bind() a statement then wait a long time to execute it, but once you > have done your first _step() you want to get through the data and release > the database for other processes. > > If you still have the database locked and another process tries to modify > it, one process or the other will have to deal with a BUSY, or a LOCKED, or > something like that. In your own setup, it turns out to be process 1. But > a slightly different setup would make process 2 see a BUSY instead. > > Simon. > ___ > 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] Database locked in multi process scenario
Hi Simon, well, the 'wait' is a simulation of what happens in the real code. The error is fatal to the application as it never ever recovers from it even though the writer has finalized and terminated. Sreekumar On Feb 10, 2012 6:57 PM, "Simon Slavin" <slav...@bigfraud.org> wrote: > > On 10 Feb 2012, at 11:47am, Sreekumar TP wrote: > > > I have a 'database is locked' issued which can be reproduced as follows. > > > > I have two applications opening the database in WAL mode. The threading > > mode is SERIALIZED. Environment is PC/Linux. > > > > > > > > Step1: Launch App1 followed by App 2 ( same executables) > > Step 2: App1 Prepares a SELECT statement and executes the statement. > > Step 3: The statement is not reset and finalized. > > Step 4: App1 wait on keyboard input > > Step 5 : App2 prepares a statement to update a few records. The statement > > is executed, reset and finalized > > Step 6: Go back to App1, prepare an UPDATE/INSERT statement and execute > it. > > Sreekumar, imagine what would happen if two users both got to step 4 at > the same point at the same time. There are no circumstances under which > either of them could proceed. Whichever of them tried to proceed would > invalidate the SELECT that the other one was still in the middle of. > > When writing your software, imagine that the database is locked from the > 'execute' until you have done the 'finalize'. You want to do the finalize > as soon as possible, to unlock the database for other users. You can't > wait for a user to type something: their phone might ring or something. > > Simon. > ___ > 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] Database locked in multi process scenario
How is this different from two threads each with a db connection in a single process? Moreover the journal mode is WAL. Hence the writer should be able to append changes to the WAL file as there are no other write transaction. Sreekumar On Feb 10, 2012 6:22 PM, "Richard Hipp" <d...@sqlite.org> wrote: > On Fri, Feb 10, 2012 at 6:47 AM, Sreekumar TP <sreekumar...@gmail.com > >wrote: > > > I have a 'database is locked' issued which can be reproduced as follows. > > > > I have two applications opening the database in WAL mode. The threading > > mode is SERIALIZED. Environment is PC/Linux. > > > > > > > > Step1: Launch App1 followed by App 2 ( same executables) > > Step 2: App1 Prepares a SELECT statement and executes the statement. > > Step 3: The statement is not reset and finalized. > > Step 4: App1 wait on keyboard input > > Step 5 : App2 prepares a statement to update a few records. The statement > > is executed, reset and finalized > > Step 6: Go back to App1, prepare an UPDATE/INSERT statement and execute > it. > > > > At this point, App1 is still holding a read transaction open that points to > an image of the database from an earlier point in time. App1 is not > allowed to write to the database because that would cause the database > content to fork. Any database connection must be in a transaction that > shows the very latest content of the database in order for it to write > without forking. > > > > > > Result: 'database is locked' error is thrown by App 1 > > > > App2 should have released the exclusive lock after it has reset and > > finalized the statement.Hence App1 should not have encountered > SQLITE_BUSY > > when writing. > > > > > > What could be the cause ? > > > > Kind Regards, > > Sreekumar > > ___ > > 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Database locked in multi process scenario
I have a 'database is locked' issued which can be reproduced as follows. I have two applications opening the database in WAL mode. The threading mode is SERIALIZED. Environment is PC/Linux. Step1: Launch App1 followed by App 2 ( same executables) Step 2: App1 Prepares a SELECT statement and executes the statement. Step 3: The statement is not reset and finalized. Step 4: App1 wait on keyboard input Step 5 : App2 prepares a statement to update a few records. The statement is executed, reset and finalized Step 6: Go back to App1, prepare an UPDATE/INSERT statement and execute it. Result: 'database is locked' error is thrown by App 1 App2 should have released the exclusive lock after it has reset and finalized the statement.Hence App1 should not have encountered SQLITE_BUSY when writing. What could be the cause ? Kind Regards, Sreekumar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Nested transaction
so if stmt1 & stmt2 are executed on different threads , but on the same connection, they would not block each other? Sreekumar On Feb 6, 2012 8:08 PM, "Igor Tandetnik" <itandet...@mvps.org> wrote: > Sreekumar TP <sreekumar...@gmail.com> wrote: > > well, if stmt1 is a write transaction, it would aquire an exclusive lock. > > if stmt2 is a read transaction, it would fail acquiring a shared lock > > The concept of a transaction exists on a per-connection basis, not > per-statement. Transaction isolation applies to statements running on two > separate connections. Multilple statements running on the same connection > never block each other. > -- > Igor Tandetnik > > ___ > 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] Nested transaction
well, if stmt1 is a write transaction, it would aquire an exclusive lock. if stmt2 is a read transaction, it would fail acquiring a shared lock since the exclusive lock is not released. . unless sqlite decides to 'downgrade' the exclusive lock to a 'shared' lock. Sreekumar On Feb 6, 2012 7:07 PM, "Igor Tandetnik" <itandet...@mvps.org> wrote: > Sreekumar TP <sreekumar...@gmail.com> wrote: > > Why is this treated as a a single transaction? > > Well, because that's how SQLite works. Why shouldn't it be? > -- > Igor Tandetnik > > ___ > 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] Nested transaction
Why is this treated as a a single transaction? Sreekumar On Feb 6, 2012 6:27 PM, "Igor Tandetnik" <itandet...@mvps.org> wrote: > Sreekumar TP <sreekumar...@gmail.com> wrote: > > I have a nested transaction as follows - > > > > sqlite3_prepare_v2(db, stmt1..) > > sqlite3_prepare_v2(db, stmt2..) > > > > sqlite3_step(stmt1) > > sqlite3_step(stmt2) > > sqlite3_reset(stmt2) > > sqlite3_step(stmt1) > > There is only one transaction here - an implicit one, started by > sqlite3_step(stmt1). > > > (1) SQLITE , as I understand does not support nested transaction > > Correct. > > > hence the > > the above statement should have caused error > > Should not. The conclusion doesn't follow from the premise. > > > (2) A transaction is complete only when sqlite3_reset is called. > > To be precise, an implicit transaction is committed when there are no more > outstanding statements on a connection, that is, when the last active > statement is reset or finalized. See > > http://sqlite.org/lang_transaction.html > > in particular the part about implicit transactions. > > > Hence the > > sqlite3_step(stmt2) should have returned a "database locked" error. > > Should not. The conclusion doesn't follow from the premise. > > > Is SQLITE doing something under the hood ? > > SQLite is doing a lot under the hood. Is there anything in particular > you'd like to know about? > -- > Igor Tandetnik > > ___ > 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] Nested transaction
Hi, I have a nested transaction as follows - sqlite3_prepare_v2(db, stmt1..) sqlite3_prepare_v2(db, stmt2..) sqlite3_step(stmt1) sqlite3_step(stmt2) sqlite3_reset(stmt2) sqlite3_step(stmt1) (1) SQLITE , as I understand does not support nested transaction, hence the the above statement should have caused error (2) A transaction is complete only when sqlite3_reset is called. Hence the sqlite3_step(stmt2) should have returned a "database locked" error. However,the above statements succeed if stmt1 is a read and stmt2 is a write. Is SQLITE doing something under the hood ? (like upgrading the locks for ex ). -Regards, Sreekumar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] database disk image is malformed
-My journal mode is set to WAL, synchronous mode is NORMAL and Checkpoint mode is Manual. Transactions are written to the WAL file, therefore, even if the WAL file is lost, I should be able to retrieve the database without corruption.(sacrificing some data ofcourse) ? Moreover, I do know that there was no checkpoint in progress when the power was turned OFF. Hence syncs should not be a problem ? -Sreekumar On Mon, Jan 23, 2012 at 3:00 PM, Roger Binns <rog...@rogerbinns.com> wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 23/01/12 01:03, Sreekumar TP wrote: > > is it possible to find out if sqlite was in the middle of a transaction > > when the power off occurred ? > > WAL mode has the transactions in a separate file. Normal mode alters the > database and keeps the original data in a rollback journal. > > However unless you identify the underlying cause (eg disk lying about > syncs) it won't help since you have to assume all data you see is a lie. > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.11 (GNU/Linux) > > iEYEARECAAYFAk8dKLYACgkQmOOfHg372QTuBQCgwFLnZ5108QRfelWm11qDiRjY > szYAn0tgEw0klQqRllRSyepezxt550ha > =SRbO > -END PGP SIGNATURE- > ___ > 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] database disk image is malformed
Yes, this is a case of corruption. The problem occurred during a power cycle test. I have the synchronous mode set to NORMAL. By examining the header of the DB ,is it possible to find out if sqlite was in the middle of a transaction when the power off occurred ? -Sreekumar On Mon, Jan 23, 2012 at 2:21 PM, Roger Binns <rog...@rogerbinns.com> wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 23/01/12 00:13, Sreekumar TP wrote: > > What can I infer from these logs ? > > Your database is corrupted. Here is a list of possible causes: > > http://www.sqlite.org/howtocorrupt.html > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.11 (GNU/Linux) > > iEYEARECAAYFAk8dH4kACgkQmOOfHg372QThhwCgyPS+Sl28MEMHln4I6iRcm5VL > 2ZkAn0tIpqfbGbkIZwUs3myTbwzv+jdw > =Czsj > -END PGP SIGNATURE- > ___ > 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] database disk image is malformed
Hi, I have get the following error when a sql statment is executed ( query ) - "database disk image is malformed". On running the quick_check pragma on the db, I get the following log - "*** in database main *** On tree page 1595 cell 5: Rowid 2104 out of order (max larger than parent max of 2068) On tree page 1597 cell 4: Rowid 2331 out of order (max larger than parent max of 2070) On tree page 1620 cell 1: Rowid 5412 out of order (max larger than parent max of 2072) On tree page 1621 cell 1: Rowid 2073 out of order (min less than parent min of 5412) On tree page 1762 cell 1: Rowid 5409 out of order (max larger than parent max of 2318) Page 1766: btreeInitPage() returns error code 11 On tree page 541 cell 21: Child page depth differs Page 1127: btreeInitPage() returns error code 11 On tree page 1413 cell 0: 2nd reference to page 1127 On tree page 1413 cell 1: Rowid 5418 out of order (max larger than parent max of 2331) On tree page 541 cell 23: Child page depth differs On tree page 1812 cell 0: Rowid 2332 out of order (min less than parent min of 5418) Page 1802 is never used Page 1806 is never used" What can I infer from these logs ? -Sreekumar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] diacritic insensitive search
Hi, I have ICU integrated with SQLITE. How do I specify a diacritic insensitive search ?. ex:- when I search for all strings that begin with 'e', the result set should return all strings that begin with 'e' as well as the accentuated 'e'. -Sreekumar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] library routine called out of sequence
Hi, To close the thread - issue was because of a missing sqlite_reset call. -Sreekumar -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tito Ciuro Sent: 23 December 2011 19:52 To: General Discussion of SQLite Database Cc: General Discussion of SQLite Database Subject: Re: [sqlite] library routine called out of sequence Hi Sreekumar, Do you have a db connection shared across threads? This is not advised . Try opening a db connection per thread instead. -- Tito On Dec 23, 2011, at 15:06, Sreekumar TP <sreekumar...@gmail.com> wrote: > Hi, > > yes, I know. I have a multithreaded app. One db conection.I will try > to reproduce the problem with a simple app. > > However, the difference between the working and non working case is > that in the working case statements are prepared each time. In the non > working case, its reused. > > The very first call to bind throws this error. > > Sreekumar > On Dec 23, 2011 2:51 PM, "Pavel Ivanov" <paiva...@gmail.com> wrote: >> >> Devil is in the details. What you described in general is okay. But I >> guess the exact code you have has some problems. To find them we >> should see the code. >> >> An just some first check: do you have a single-threaded application >> accessing database? >> >> >> Pavel >> >> >> On Fri, Dec 23, 2011 at 3:32 AM, Sreekumar TP >> <sreekumar...@gmail.com> > wrote: >>> Hi , >>> >>> I have a strange problem. I do the following in sequence - >>> >>> 1. open database >>> 2. set pragmas >>> 3.create table >>> 4. prepare a paremetrised statement ( SELECT ..) 5. bind values to >>> the statement 6. step >>> >>> At 5, I get the error "library routine called out of sequence". >>> >>> If I replace the parameterised statement with a 'non-parameterised' >>> statement, I have no issues. >>> >>> If I use a parameterized INSERT statement , I do not get the error. >>> >>> Any suggestions? >>> >>> -Sreekumar >>> ___ >>> 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 ___ 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] library routine called out of sequence
I am 99% certain that it is the very first call. unless the step and reset executed once before.(the bind , step, reset is in a loop). I will double check this. Sreekumar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] library routine called out of sequence
Hi, yes, I know. I have a multithreaded app. One db conection.I will try to reproduce the problem with a simple app. However, the difference between the working and non working case is that in the working case statements are prepared each time. In the non working case, its reused. The very first call to bind throws this error. Sreekumar On Dec 23, 2011 2:51 PM, "Pavel Ivanov" <paiva...@gmail.com> wrote: > > Devil is in the details. What you described in general is okay. But I > guess the exact code you have has some problems. To find them we > should see the code. > > An just some first check: do you have a single-threaded application > accessing database? > > > Pavel > > > On Fri, Dec 23, 2011 at 3:32 AM, Sreekumar TP <sreekumar...@gmail.com> wrote: > > Hi , > > > > I have a strange problem. I do the following in sequence - > > > > 1. open database > > 2. set pragmas > > 3.create table > > 4. prepare a paremetrised statement ( SELECT ..) > > 5. bind values to the statement > > 6. step > > > > At 5, I get the error "library routine called out of sequence". > > > > If I replace the parameterised statement with a 'non-parameterised' > > statement, I have no issues. > > > > If I use a parameterized INSERT statement , I do not get the error. > > > > Any suggestions? > > > > -Sreekumar > > ___ > > 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
[sqlite] library routine called out of sequence
Hi , I have a strange problem. I do the following in sequence - 1. open database 2. set pragmas 3.create table 4. prepare a paremetrised statement ( SELECT ..) 5. bind values to the statement 6. step At 5, I get the error "library routine called out of sequence". If I replace the parameterised statement with a 'non-parameterised' statement, I have no issues. If I use a parameterized INSERT statement , I do not get the error. Any suggestions? -Sreekumar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Database usage across processes
Hi, If I share a database across two process , both have sqlite threading mode to serialized, do I still need to serialize the access to the database ? -Sreekumar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL file size
Hello Richard, *The prgama PRAGMA journal_size_limit = **N , *does not seem to limit the size of the WAL log file. Is there any precondition to be satisfied before calling the pragma ? -Sreekumar On Wed, Nov 30, 2011 at 7:04 PM, Teg <t...@djii.com> wrote: > Hello Richard, > > When does the WAL get trimmed down? If my transaction completes, does > the WAL file get deleted? I've seen the WAL grow to 4 times my actual > DB size so, I don't really want 160 Gigs of WAL hanging out when the > process that generated it is only run once a month. > > > Wednesday, November 30, 2011, 10:02:34 AM, you wrote: > > RH> On Wed, Nov 30, 2011 at 4:41 AM, Sreekumar TP <sreekumar...@gmail.com > >wrote: > > RH> Because we have experimentally determined that it is faster to > overwrite an > RH> existing file than to append to a file. So we don't truncate the WAL > file > RH> on a checkpoint, which makes subsequent writes to the WAL file go > faster. > > RH> SQLite will truncate the WAL file down to the size specified by the > "PRAGMA > RH> journal_size_limit" setting, if you have set the journal_size_limit. > > > >> > >> -Sreekumar > >> ___ > >> sqlite-users mailing list > >> sqlite-users@sqlite.org > >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> > > > > > > > -- > Best regards, > Tegmailto:t...@djii.com > > ___ > 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] Checkpoint time
There is another reader but sharing the same connection. So checkpoint process cannot be blocked by another reader. On Thu, Dec 1, 2011 at 8:54 PM, Yves Goergen <nospam.l...@unclassified.de>wrote: > On 01.12.2011 18:58 CE(S)T, Sreekumar TP wrote: > > During the measurement of manual checkpoint times, I see the following > > behaviour - > > > > # of WAL frames checkpointed is over 1000 > > size of DB has not changed after checkpoint.(measured using fstat) > > Time for the checkpoint if around 500 ms. > > > > Why is it that eventhough 1000+ frames are checkpointed, the DB size has > > not increased ? > > All records inserted are new and unique records and insertions start from > > empty database. > > Could it be that the pages were not actually written to the database > because there was a reader lock on them? Did any other process access > the database (and thus hold any locks) while you did the checkpointing? > > (Wild guess. I've only learned about WAL and started using it yesterday. > I've read the whole WAL documentation page [1] though.) > > [1] http://sqlite.org/wal.html > > -- > Yves Goergen "LonelyPixel" <nospam.l...@unclassified.de> > Visit my web laboratory at http://beta.unclassified.de > ___ > 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] Checkpoint time
Hello, During the measurement of manual checkpoint times, I see the following behaviour - # of WAL frames checkpointed is over 1000 size of DB has not changed after checkpoint.(measured using fstat) Time for the checkpoint if around 500 ms. Why is it that eventhough 1000+ frames are checkpointed, the DB size has not increased ? All records inserted are new and unique records and insertions start from empty database. -Sreekumar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL file size
Hi Richard, How does sqlite determine the right size for the WAL file? Application can ofcourse indicate the maximum size. -Sreekumar On Wed, Nov 30, 2011 at 4:02 PM, Richard Hipp <d...@sqlite.org> wrote: > On Wed, Nov 30, 2011 at 4:41 AM, Sreekumar TP <sreekumar...@gmail.com > >wrote: > > > Hello, > > > > I have sqlite configure to do manual checkpoint. I do checkpoint every > few > > hundred records. I expect the WAL to reduce in size and become zero when > > there are no more inserts done. > > However, I see that the WAL file size is static and does not reduce in > > size. Why isnt manual checkpoint reducing the size of WAL file. > > > > Because we have experimentally determined that it is faster to overwrite an > existing file than to append to a file. So we don't truncate the WAL file > on a checkpoint, which makes subsequent writes to the WAL file go faster. > > SQLite will truncate the WAL file down to the size specified by the "PRAGMA > journal_size_limit" setting, if you have set the journal_size_limit. > > > > > > -Sreekumar > > ___ > > 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] WAL file size
Hello, I have sqlite configure to do manual checkpoint. I do checkpoint every few hundred records. I expect the WAL to reduce in size and become zero when there are no more inserts done. However, I see that the WAL file size is static and does not reduce in size. Why isnt manual checkpoint reducing the size of WAL file. -Sreekumar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Inserting Multiple Rows in a single statement
Hi, I do have multiple insertions bounded by BEGIN-COMMIT . I am looking at possibilities of making the insertions faster. -Sreekumar On Tue, Nov 29, 2011 at 4:36 PM, Donald Griggs <dfgri...@gmail.com> wrote: > Sreekumar, > > Regarding: > > > > Is it possible to insert multiple rows using a single statement ? > > > > You might want to let us know your reasons for requesting this. > > If it's speed of insertion you're after, then be sure to put many INSERT's > into each transaction. That is, be sure to surround a batch of, say, 1000 > INSERT's with BEGIN and END statements. > > http://sqlite.org/lang_transaction.html > ___ > 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] Inserting Multiple Rows in a single statement
Hello, Is it possible to insert multiple rows using a single statement ? Kind Regards, Sreekumar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compacting Database
And if the pages are defragmented heavily, new inserts could result in database size increase until vacuum is performed.? Sreekumar On Nov 18, 2011 9:38 PM, "Simon Slavin" <slav...@bigfraud.org> wrote: > > On 18 Nov 2011, at 3:51pm, Sreekumar TP wrote: > > > So if my database grew to 20Mb and later lots of records were deleted > > amounting to 50% of the database size, the size on disk will still be > 20Mb. > > Correct? > > > > Now if I start inserting records, sqlite will use the remaing 50% of disk > > space until it reaches a point where no more free pages or space in pages > > are available to insert data. Is this inference valid? > > Yes and yes. First it will use up the about 10Mb of space you free. If > you're still inserting once all that space is used up (for the second time > !), only then will SQLite make the file bigger. > > Simon. > ___ > 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] Compacting Database
Hi, So if my database grew to 20Mb and later lots of records were deleted amounting to 50% of the database size, the size on disk will still be 20Mb. Correct? Now if I start inserting records, sqlite will use the remaing 50% of disk space until it reaches a point where no more free pages or space in pages are available to insert data. Is this inference valid? Sreekumar On Nov 18, 2011 9:09 PM, "Simon Slavin" <slav...@bigfraud.org> wrote: > > On 18 Nov 2011, at 3:18pm, Sreekumar TP wrote: > > > I have auto vacuum switched off and do not perform a manual vacuum. In > this > > scenario will the database size keep increasing when records are inserted > > even as records are deleted. > > No, you're okay. Deleting records makes space available in the database > file and SQLite will know about it. If SQLite wants to store more data, > and it fits in the space which is now available, then SQLite will store it > in the existing free space rather than making the file longer. > > > As I understand from the documentation, > > > > "When auto-vacuum is disabled and data is deleted data from a database, > the > > database file remains the same size. Unused database file pages are added > > to a "freelist <http://www.sqlite.org/fileformat2.html#freelist>" and > > reused for subsequent inserts. So no database file space is lost. > However, > > the database file does not shrink. " > > WIthout VACUUM, if you INSERT 200 rows, then DELETE 100 of them, the > filesize won't shrink. The space will be available, but only to SQLite > when it's trying to store data in that same database file. > > VACUUM (including auto-vacuum) will shrink the database file down and > return any free space so it can be used by other files. Since databases > rarely shrink, VACUUM doesn't usually matter very much. But if you have a > routine which deletes many records (e.g. you have an end-of-year procedure > which deletes a year's worth of expired data) then it can be worth doing a > 'VACUUM' after your 'DELETE FROM ...'. > > (The above explanation is slightly simplified since SQLite splits a file > into pages, and each page can only be used for certain kinds of data. And > even 'UPDATE' can free up space if the new data is shorter than the old > data. But the effects of this are small and you get the general idea.) > > Simon. > ___ > 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] Compacting Database
Hi, I have auto vacuum switched off and do not perform a manual vacuum. In this scenario will the database size keep increasing when records are inserted even as records are deleted. As I understand from the documentation, "When auto-vacuum is disabled and data is deleted data from a database, the database file remains the same size. Unused database file pages are added to a "freelist <http://www.sqlite.org/fileformat2.html#freelist>" and reused for subsequent inserts. So no database file space is lost. However, the database file does not shrink. " Database size will keep increasing if sqlite allocates new pages for records inserted instead of re-using the pages which have records deleted, but not in the free list. Could you clarify ? -Sreekumar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3_step
Hi, I do not have a ORDER BY , only a WHERE clause. So sorting should not be the cause for the overhead. -Sreekumar On Mon, Oct 17, 2011 at 9:08 AM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 17 Oct 2011, at 4:22am, Sreekumar TP wrote: > > > In case of a prepared statement that uses SELECT > > , the first sqlite3_step statement consumes a lot of time (order of > > magnitude can be 100 or more) > > when compared to the subsequent executions of step used to iterate > through > > the results. Does the first execution of step cache the entire result set > > and tje subsequent steps get the results from the cache? > > The first step has to make a lot of decisions about what plan to follow in > retrieving the rows: Which index is the best to use ? Is it going to be > necessary to sort the rows even after that index ? These things do take > some extra time. > > If it turns out that one of the available indexes will produce the rows in > exactly the order wanted, then it will not be necessary to store all the > rows which should be returned. SQLite just stores the query plan: it > remembers how to find each next row, rather than remembering the data in > those rows. > > On the other hand, if it is necessary for SQLite to sort the rows itself, > because no good index has been created, then yes, it will have to sort the > rows in order to figure out which one to return first, and having sorted > them it will store them so that subsequent _step()s can just return the next > row. > > Simon. > ___ > 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] Sqlite3_step
Hi, In case of a prepared statement that uses SELECT , the first sqlite3_step statement consumes a lot of time (order of magnitude can be 100 or more) when compared to the subsequent executions of step used to iterate through the results. Does the first execution of step cache the entire result set and tje subsequent steps get the results from the cache? Sreekumar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Quickest way to delete all contents of a table
Hi, Which would be the quick way to clear all records in a table ( assumming 100,000 records) ? 1. Delete * from table_name 2. DROP TABLE table_name followed by CREATE TABLE -Sreekumar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite+ICU library and usage of LIKE
To conclude on this topic, I figured out the usage of '_' . --Original Message-- From: Igor Tandetnik Sender: sqlite-users-boun...@sqlite.org To: sqlite-users@sqlite.org ReplyTo: General Discussion of SQLite Database Subject: Re: [sqlite] Sqlite+ICU library and usage of LIKE Sent: Sep 7, 2011 17:54 sreekumar...@gmail.com wrote: > The _ operator( match any single char in the string) does not seem to work.. > % is ok.. Show your data and your statement. Explain what outcome you observe, and how it differs from your expectations. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Sent from BlackBerry® on Airtel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite+ICU library and usage of LIKE
The _ operator( match any single char in the string) does not seem to work.. % is ok.. Any clue? Sent from BlackBerry® on Airtel -Original Message- From: "Michael Stephenson" <domehead...@gmail.com> Sender: sqlite-users-boun...@sqlite.org Date: Tue, 6 Sep 2011 12:55:02 To: 'General Discussion of SQLite Database'<sqlite-users@sqlite.org> Reply-To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] Sqlite+ICU library and usage of LIKE "It's rather pointless to use a LIKE operator with no wildcards." Except that it performs a case-insensitive comparison, so a quick-and-dirty way to accomplish that. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Tuesday, September 06, 2011 11:52 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Sqlite+ICU library and usage of LIKE On 9/6/2011 11:41 AM, Sreekumar TP wrote: > If I modify the statement to return all strings which match 'м' , No > strings are fetched. > > zSQL = sqlite3_snprintf(1024,temp2,"SELECT * FROM l1 WHERE data LIKE 'м' > ;"); You probably want LIKE 'м%'. It's rather pointless to use a LIKE operator with no wildcards. -- Igor Tandetnik ___ 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] Sqlite+ICU library and usage of LIKE
Some elementary issue in my code, works fine now. Tks -Sreekumar 2011/9/6 Sreekumar TP <sreekumar...@gmail.com> > I get the following error when I use % or _ > > error @ 101, 1,1,unrecognized token: "'м" > > > > On Tue, Sep 6, 2011 at 9:21 PM, Igor Tandetnik <itandet...@mvps.org>wrote: > >> On 9/6/2011 11:41 AM, Sreekumar TP wrote: >> >>> If I modify the statement to return all strings which match 'м' , No >>> strings >>> are fetched. >>> >>> zSQL = sqlite3_snprintf(1024,temp2,"**SELECT * FROM l1 WHERE data LIKE >>> 'м' >>> ;"); >>> >> >> You probably want LIKE 'м%'. It's rather pointless to use a LIKE operator >> with no wildcards. >> -- >> Igor Tandetnik >> >> __**_ >> 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> >> > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite+ICU library and usage of LIKE
I get the following error when I use % or _ error @ 101, 1,1,unrecognized token: "'м" On Tue, Sep 6, 2011 at 9:21 PM, Igor Tandetnik <itandet...@mvps.org> wrote: > On 9/6/2011 11:41 AM, Sreekumar TP wrote: > >> If I modify the statement to return all strings which match 'м' , No >> strings >> are fetched. >> >> zSQL = sqlite3_snprintf(1024,temp2,"**SELECT * FROM l1 WHERE data LIKE >> 'м' >> ;"); >> > > You probably want LIKE 'м%'. It's rather pointless to use a LIKE operator > with no wildcards. > -- > Igor Tandetnik > > __**_ > 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> > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite+ICU library and usage of LIKE
Hi, I am using ICU library to collate and pattern match strings in Russian language. I use C api to intialise the icu library . sqlite3IcuInit(db); zSQL = sqlite3_snprintf(1024,temp1,"SELECT icu_load_collation('ru_RU', 'RUSSIAN');"); sqlite3_prepare_v2(db,(const char *)zSQL,-1,, NULL); further for Pattern matching- zSQL = sqlite3_snprintf(1024,temp2,"SELECT * FROM l1 WHERE data LIKE 'марта' ;"); The statement return exactly one matched string. If I modify the statement to return all strings which match 'м' , No strings are fetched. zSQL = sqlite3_snprintf(1024,temp2,"SELECT * FROM l1 WHERE data LIKE 'м' ;"); sqlite3_prepare_v2(db,zSQL,-1,, NULL); What could be going wrong ? -Sreekumar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WAL mode and Network filesystems
Well, my system configuration is such that the RFS is mounted via NFS server. All the processes that access the DB will be on the same CPU. --Original Message-- From: Pavel Ivanov To: Sreekumar TP To: General Discussion of SQLite Database Subject: Re: [sqlite] WAL mode and Network filesystems Sent: Aug 26, 2011 20:08 http://www.sqlite.org/wal.html Disadvantage #2: All processes using a database must be on the same host computer; WAL does not work over a network filesystem. So as long as all users of your database are on the same host it seems that WAL will work even if file is on NFS. But then what's the point of putting database file on NFS if all its users will be on the same host? Local drive will work as well (even faster). Pavel On Fri, Aug 26, 2011 at 10:28 AM, <sreekumar...@gmail.com> wrote: > I understand that WAL mode of sqlite is not supported over network file > systems. Does this mean that if my DB is in a filesystem mounted on a NFS > server will also not work in WAL mode? If so what is the bottleneck? > Sent from BlackBerry® on Airtel >___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > Sent from BlackBerry® on Airtel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] WAL mode and Network filesystems
I understand that WAL mode of sqlite is not supported over network file systems. Does this mean that if my DB is in a filesystem mounted on a NFS server will also not work in WAL mode? If so what is the bottleneck? Sent from BlackBerry® on Airtel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXT :Re: Last record in db
I understand now. How does sqlite insert a record ? More specifically how does sqlite update the B-tree with the new record . Is there a linkage made between the newly inserted record and the previous one ? Sent from BlackBerry® on Airtel -Original Message- From: Simon SlavinSender: sqlite-users-boun...@sqlite.org Date: Mon, 22 Aug 2011 17:32:51 To: General Discussion of SQLite Database Reply-To: General Discussion of SQLite Database Subject: Re: [sqlite] EXT :Re: Last record in db On 22 Aug 2011, at 5:13pm, Igor Tandetnik wrote: > On 8/22/2011 9:52 AM, Black, Michael (IS) wrote: >> No...if you use autoincrement you can guarantee that "last" will be the last >> record inserted. > > There's no contradiction. "Last" is still defined only for ordered sets > - you just chose a particular ordering, by rowid. And even if you do that, it's easy to break the expected ordering: > .schema t1 CREATE TABLE t1(id INT,data TEXT); > select * from t1 ; id data -- -- 1 one 2 two 3 tre > delete from t1 where data = 'two'; > insert into t1 (it, data) values (2, 'second'); Now the 'last' record is not the one with the highest value in the id column. Igor is right. The question from the original poster doesn't mean anything in SQL. SQL has no concept of an order for rows, so it has no idea which row is 'first' or 'last'. If you, the programmer have your own idea what is first or last, write your own numbers into the database. Simon. ___ 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] Last record in db
Is 'last' valid only for 'ordered' set of records? --Original Message-- From: Igor Tandetnik Sender: sqlite-users-boun...@sqlite.org To: sqlite-users@sqlite.org ReplyTo: General Discussion of SQLite Database Subject: Re: [sqlite] Last record in db Sent: Aug 22, 2011 18:51 sreekumar...@gmail.com wrote: > Let's say there is a table in a db that holds a few thousands of records.. > Records are inserted and deleted from the table. At any given point I should > be able to retrieve the 'last' record.. Last by what ordering? > 'Last' is probably the record which is stored at the node with max depth? What's a "node" or a "depth" in this context? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Sent from BlackBerry® on Airtel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Last record in db
Let's say there is a table in a db that holds a few thousands of records.. Records are inserted and deleted from the table. At any given point I should be able to retrieve the 'last' record.. 'Last' is probably the record which is stored at the node with max depth? --Original Message-- From: Igor Tandetnik Sender: sqlite-users-boun...@sqlite.org To: sqlite-users@sqlite.org ReplyTo: General Discussion of SQLite Database Subject: Re: [sqlite] Last record in db Sent: Aug 22, 2011 18:33 sreekumar...@gmail.com wrote: > 1.What's the fastest way to retrieve the last record in the DB. a) Define "last". b) From which table in the DB? > 2. Sqlite3_step takes you to the next record. Is there an equivalent for > navigating backwards ? Not really, but see http://www.sqlite.org/cvstrac/wiki/wiki?p=ScrollingCursor -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Sent from BlackBerry® on Airtel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Last record in db
Hi, 1.What's the fastest way to retrieve the last record in the DB. 2. Sqlite3_step takes you to the next record. Is there an equivalent for navigating backwards ? Sent from BlackBerry® on Airtel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-src-3070701.zip compilation issues
Pls list the compilation errors.. --Original Message-- From: Raja Kondu Sender: sqlite-users-boun...@sqlite.org To: sqlite-users@sqlite.org ReplyTo: General Discussion of SQLite Database Subject: [sqlite] sqlite-src-3070701.zip compilation issues Sent: Aug 11, 2011 4:36 AM Hi, I have downloaded the SQLite version 3.7.7.1 from the *Legacy Source Code Distribution Formats . * ** Here I am just compiling the source code from the "src" folder and I am seeing lot of compilation errors by saying the header files missing and some of the header files are really missing. Could some one please tell me is there any issue in this specific release ? Earlier I use to compile the 3.6.17 version and now I am trying with the latest version 3.7.7.1. Please help me to get rid of these compilation errors. -- Thanks, Raja Kondu. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Sent from BlackBerry® on Airtel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In memory v/s tmpfs
Oh Yes. I missed that point ! On Wed, Aug 10, 2011 at 11:57 PM, Richard Hipp <d...@sqlite.org> wrote: > On Wed, Aug 10, 2011 at 2:13 PM, Sreekumar TP <sreekumar...@gmail.com > >wrote: > > > But I sqlite can still check the number of db connections open > > > > > No it cannot. > > In unix, there is no way for one process to know whether or not another > process has a particular file open. And if there is a mechanism to do that > in windows, I'm not familiar with it. > > And when you think about it, any mechanism that allowed one process to spy > on the file connections of another unrelated process would probably be > considered a security risk and hence undesirable. > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] In memory v/s tmpfs
Ok.. But I sqlite can still check the number of db connections open and optimise the checking=> as long as there is only one writer, do not check for hot journals. Can a database have two connections opened with two different journal modes ? On Wed, Aug 10, 2011 at 11:35 PM, Pavel Ivanov <paiva...@gmail.com> wrote: > > If you have one reader and many writers, consider PRAGMA > journal_mode=WAL; > > Richard meant "one writer and many readers" of course. > > >> If the other process opens the db connection as read_only, will the hot > >> journal check be still done (during queries operations of the second > >> process)? > >> > > > > How is the second process suppose to know whether or not the other > process > > used read_only? > > And even those who opened db connection as read_only will check for > hot journal before executing any query. > > > Pavel > > On Wed, Aug 10, 2011 at 1:44 PM, Richard Hipp <d...@sqlite.org> wrote: > > On Wed, Aug 10, 2011 at 1:35 PM, Sreekumar TP <sreekumar...@gmail.com > >wrote: > > > >> Ok, its getting a bit clear. > >> If there is only one process that is writing to the database, but has > more > >> than one process that reads the database, the locking mode can still be > >> exclusive ? > >> > > > > PRAGMA locking_mode=EXCLUSIVE; means "exclusive'. Only one process can > > access. > > > > If you have one reader and many writers, consider PRAGMA > journal_mode=WAL; > > > > > > > >> If the other process opens the db connection as read_only, will the hot > >> journal check be still done (during queries operations of the second > >> process)? > >> > > > > How is the second process suppose to know whether or not the other > process > > used read_only? > > > > > >> > >> > >> > >> > >> On Wed, Aug 10, 2011 at 10:54 PM, Richard Hipp <d...@sqlite.org> wrote: > >> > >> > On Wed, Aug 10, 2011 at 1:09 PM, Simon Slavin <slav...@bigfraud.org> > >> > wrote: > >> > > >> > > > >> > > On 10 Aug 2011, at 6:01pm, Sreekumar TP wrote: > >> > > > >> > > > Thanks for the explanation. The journal mode was OFF which means > >> there > >> > > is > >> > > > no journal file created. So why is it the check still performed ? > >> > > > >> > > Because the journal mode might have been 'ON' the last time that > >> > database > >> > > was used. > >> > > > >> > > >> > More importantly, the journal mode might be 'ON' in the other process > >> that > >> > crashed in the middle of a transaction. > >> > > >> > > >> > > >> > > > >> > > > On Wed, Aug 10, 2011 at 10:24 PM, Richard Hipp <d...@sqlite.org> > >> wrote: > >> > > > > >> > > >> On Wed, Aug 10, 2011 at 12:40 PM, Sreekumar TP < > >> > sreekumar...@gmail.com > >> > > >>> wrote: > >> > > >> > >> > > >>> The 179148 failures on 'access' system call is due to access > check > >> of > >> > > two > >> > > >>> files - the journal file and the wal-file. > >> > > > >> > > Well, the file is obviously not being opened 179148 times. I > thought > >> it > >> > > checked for these files only when sqlite3_open() was being run. Is > it > >> > > checking for these files every time a command is executed ? Surely > >> that > >> > is > >> > > unnecessary and a source of a large reduction in speed. > >> > > > >> > > >> > SQLite must check for hot journals at the start of each transaction. > >> > > >> > Or, more precisely, SQLite must check for hot journals whenever it > starts > >> > reading the database again after a period of time when another process > >> > might > >> > have been writing to the database (and might have crashed while > writing). > >> > > >> > That's when PRAGMA locking_mode=EXCLUSIVE blocks the hot-journal > checks. > >> > If > >> > your process has exclusive access to the database, it is not possible > for > >> > another process to write to the database (and crash in the middle of > >
Re: [sqlite] In memory v/s tmpfs
Ok, its getting a bit clear. If there is only one process that is writing to the database, but has more than one process that reads the database, the locking mode can still be exclusive ? If the other process opens the db connection as read_only, will the hot journal check be still done (during queries operations of the second process)? On Wed, Aug 10, 2011 at 10:54 PM, Richard Hipp <d...@sqlite.org> wrote: > On Wed, Aug 10, 2011 at 1:09 PM, Simon Slavin <slav...@bigfraud.org> > wrote: > > > > > On 10 Aug 2011, at 6:01pm, Sreekumar TP wrote: > > > > > Thanks for the explanation. The journal mode was OFF which means there > > is > > > no journal file created. So why is it the check still performed ? > > > > Because the journal mode might have been 'ON' the last time that > database > > was used. > > > > More importantly, the journal mode might be 'ON' in the other process that > crashed in the middle of a transaction. > > > > > > > > On Wed, Aug 10, 2011 at 10:24 PM, Richard Hipp <d...@sqlite.org> wrote: > > > > > >> On Wed, Aug 10, 2011 at 12:40 PM, Sreekumar TP < > sreekumar...@gmail.com > > >>> wrote: > > >> > > >>> The 179148 failures on 'access' system call is due to access check of > > two > > >>> files - the journal file and the wal-file. > > > > Well, the file is obviously not being opened 179148 times. I thought it > > checked for these files only when sqlite3_open() was being run. Is it > > checking for these files every time a command is executed ? Surely that > is > > unnecessary and a source of a large reduction in speed. > > > > SQLite must check for hot journals at the start of each transaction. > > Or, more precisely, SQLite must check for hot journals whenever it starts > reading the database again after a period of time when another process > might > have been writing to the database (and might have crashed while writing). > > That's when PRAGMA locking_mode=EXCLUSIVE blocks the hot-journal checks. > If > your process has exclusive access to the database, it is not possible for > another process to write to the database (and crash in the middle of > writing). > > > > > > > Simon. > > ___ > > 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In memory v/s tmpfs
The journal mode was always OFF when the code executes. The db was/is never opened with a journal. I havent analysed when exactly these calls are made, but definitely, it quite a lot.. On Wed, Aug 10, 2011 at 10:39 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 10 Aug 2011, at 6:01pm, Sreekumar TP wrote: > > > Thanks for the explanation. The journal mode was OFF which means there > is > > no journal file created. So why is it the check still performed ? > > Because the journal mode might have been 'ON' the last time that database > was used. > > > On Wed, Aug 10, 2011 at 10:24 PM, Richard Hipp <d...@sqlite.org> wrote: > > > >> On Wed, Aug 10, 2011 at 12:40 PM, Sreekumar TP <sreekumar...@gmail.com > >>> wrote: > >> > >>> The 179148 failures on 'access' system call is due to access check of > two > >>> files - the journal file and the wal-file. > > Well, the file is obviously not being opened 179148 times. I thought it > checked for these files only when sqlite3_open() was being run. Is it > checking for these files every time a command is executed ? Surely that is > unnecessary and a source of a large reduction in speed. > > Simon. > ___ > 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] In memory v/s tmpfs
Thanks for the explanation. The journal mode was OFF which means there is no journal file created. So why is it the check still performed ? On Wed, Aug 10, 2011 at 10:24 PM, Richard Hipp <d...@sqlite.org> wrote: > On Wed, Aug 10, 2011 at 12:40 PM, Sreekumar TP <sreekumar...@gmail.com > >wrote: > > > The 179148 failures on 'access' system call is due to access check of > two > > files - the journal file and the wal-file. > > The journal mode was OFF as also WAL mode. > > Why is sqlite checking access permissions for this file 179140 times? > > Removing or optimising this will make it faster by 20% atleast! > > > > > SQLite must check for the existence of hot journals that were left behind > by > other processes that were writing to the database but crashed before they > finished. If SQLite did not check for these, and if other processes did > crash in the middle of a transaction, the database file would go corrupt. > > If your process is the only one accessing the database file, you can > disable > the journal-checks by doing: > > PRAGMA locking_mode=EXCLUSIVE; > > > > > > > > > > access("/dev/shm/test.db-journal", F_OK) = -1 ENOENT (No such file or > > directory) > > > > access("/dev/shm/test.db-wal", F_OK) = -1 ENOENT (No such file or > > directory) > > > > -Sreekumar > > On Wed, Aug 10, 2011 at 5:28 PM, Sreekumar TP <sreekumar...@gmail.com > > >wrote: > > > > > Hi, > > > > > > I have the results from the tests (below). Alot of the time is spent > in > > > checking file permissions and locking the file (40 %). > > > > > > > > > Inmem > > > > > > % time seconds usecs/call callserrors syscall > > > > > > -- --- --- - - > > > > > > 28.530.124727 1118524 write > > > > > > 23.420.102382 0414624 gettimeofday > > > > > > 20.780.090840 1 76513 read > > > > > > 13.720.059977 0191255 _llseek > > > > > > > > > > > > Db in tmpfs > > > > > > % time seconds usecs/call callserrors syscall > > > > > > -- --- --- - - > > > > > > 21.830.257073 1263306 write > > > > > > 21.180.249488 1179148179148 access > > > > > > 20.610.242725 0509292 fcntl64 > > > > > > 13.040.153551 0448720 _llseek > > > > > > 9.440.94 1189370 read > > > > > > 8.160.096124 0414624 gettimeofday > > > 2.550.033750 8 fdatasync > > > > > > On Tue, Aug 9, 2011 at 7:34 PM, Pavel Ivanov <paiva...@gmail.com> > > wrote: > > > > > >> > Journal mode is WAL > > >> > > >> I believe in-memory database can't have journal mode WAL. So you > > >> compare completely different settings. > > >> > > >> > > >> Pavel > > >> > > >> > > >> On Tue, Aug 9, 2011 at 5:15 AM, <sreekumar...@gmail.com> wrote: > > >> > > > >> > Journal mode is WAL > > >> > > > >> > > > >> > --Original Message-- > > >> > From: Roger Binns > > >> > Sender: sqlite-users-boun...@sqlite.org > > >> > To: General Discussion of SQLite Database > > >> > ReplyTo: General Discussion of SQLite Database > > >> > Subject: Re: [sqlite] In memory v/s tmpfs > > >> > Sent: Aug 9, 2011 2:42 PM > > >> > > > >> > -BEGIN PGP SIGNED MESSAGE- > > >> > Hash: SHA1 > > >> > > > >> > On 08/08/2011 06:34 PM, sreekumar...@gmail.com wrote: > > >> >> From the point of view of performance, I expected similar > performance > > , > > >> tmpfs being a little slower due to filesystem overhead. However, the > > >> operations on tmpfs was much slower than expected. > > >> > > > >> > Using tmpfs requires many kernel calls which is considerably more > > effort > > >> > than the occasional malloc ca
Re: [sqlite] In memory v/s tmpfs
The 179148 failures on 'access' system call is due to access check of two files - the journal file and the wal-file. The journal mode was OFF as also WAL mode. Why is sqlite checking access permissions for this file 179140 times? Removing or optimising this will make it faster by 20% atleast! access("/dev/shm/test.db-journal", F_OK) = -1 ENOENT (No such file or directory) access("/dev/shm/test.db-wal", F_OK) = -1 ENOENT (No such file or directory) -Sreekumar On Wed, Aug 10, 2011 at 5:28 PM, Sreekumar TP <sreekumar...@gmail.com>wrote: > Hi, > > I have the results from the tests (below). Alot of the time is spent in > checking file permissions and locking the file (40 %). > > > Inmem > > % time seconds usecs/call callserrors syscall > > -- --- --- - - > > 28.530.124727 1118524 write > > 23.420.102382 0414624 gettimeofday > > 20.780.090840 1 76513 read > > 13.720.059977 0191255 _llseek > > > > Db in tmpfs > > % time seconds usecs/call callserrors syscall > > -- --- --- - - > > 21.830.257073 1263306 write > > 21.180.249488 1179148179148 access > > 20.610.242725 0509292 fcntl64 > > 13.040.153551 0448720 _llseek > > 9.440.94 1189370 read > > 8.160.096124 0414624 gettimeofday > 2.550.033750 8 fdatasync > > On Tue, Aug 9, 2011 at 7:34 PM, Pavel Ivanov <paiva...@gmail.com> wrote: > >> > Journal mode is WAL >> >> I believe in-memory database can't have journal mode WAL. So you >> compare completely different settings. >> >> >> Pavel >> >> >> On Tue, Aug 9, 2011 at 5:15 AM, <sreekumar...@gmail.com> wrote: >> > >> > Journal mode is WAL >> > >> > >> > --Original Message-- >> > From: Roger Binns >> > Sender: sqlite-users-boun...@sqlite.org >> > To: General Discussion of SQLite Database >> > ReplyTo: General Discussion of SQLite Database >> > Subject: Re: [sqlite] In memory v/s tmpfs >> > Sent: Aug 9, 2011 2:42 PM >> > >> > -BEGIN PGP SIGNED MESSAGE- >> > Hash: SHA1 >> > >> > On 08/08/2011 06:34 PM, sreekumar...@gmail.com wrote: >> >> From the point of view of performance, I expected similar performance , >> tmpfs being a little slower due to filesystem overhead. However, the >> operations on tmpfs was much slower than expected. >> > >> > Using tmpfs requires many kernel calls which is considerably more effort >> > than the occasional malloc call. Additionally files have to be locked, >> > journals made etc (you didn't mention your journal setting). >> > >> > Roger >> > -BEGIN PGP SIGNATURE- >> > Version: GnuPG v1.4.11 (GNU/Linux) >> > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ >> > >> > iEYEARECAAYFAk5A+g0ACgkQmOOfHg372QTR8ACgqNeeuOxHRy7+hMH5RY/OAyV2 >> > Wq0AoMaSRtoFN4obCgmgHlpHthd9z5Zp >> > =pkJt >> > -END PGP SIGNATURE- >> > ___ >> > sqlite-users mailing list >> > sqlite-users@sqlite.org >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > >> > >> > Sent from BlackBerry® on Airtel >> > ___ >> > 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] In memory v/s tmpfs
Hi, I have the results from the tests (below). Alot of the time is spent in checking file permissions and locking the file (40 %). Inmem % time seconds usecs/call callserrors syscall -- --- --- - - 28.530.124727 1118524 write 23.420.102382 0414624 gettimeofday 20.780.090840 1 76513 read 13.720.059977 0191255 _llseek Db in tmpfs % time seconds usecs/call callserrors syscall -- --- --- - - 21.830.257073 1263306 write 21.180.249488 1179148179148 access 20.610.242725 0509292 fcntl64 13.040.153551 0448720 _llseek 9.440.94 1189370 read 8.160.096124 0414624 gettimeofday 2.550.033750 8 fdatasync On Tue, Aug 9, 2011 at 7:34 PM, Pavel Ivanov <paiva...@gmail.com> wrote: > > Journal mode is WAL > > I believe in-memory database can't have journal mode WAL. So you > compare completely different settings. > > > Pavel > > > On Tue, Aug 9, 2011 at 5:15 AM, <sreekumar...@gmail.com> wrote: > > > > Journal mode is WAL > > > > > > --Original Message-- > > From: Roger Binns > > Sender: sqlite-users-boun...@sqlite.org > > To: General Discussion of SQLite Database > > ReplyTo: General Discussion of SQLite Database > > Subject: Re: [sqlite] In memory v/s tmpfs > > Sent: Aug 9, 2011 2:42 PM > > > > -BEGIN PGP SIGNED MESSAGE- > > Hash: SHA1 > > > > On 08/08/2011 06:34 PM, sreekumar...@gmail.com wrote: > >> From the point of view of performance, I expected similar performance , > tmpfs being a little slower due to filesystem overhead. However, the > operations on tmpfs was much slower than expected. > > > > Using tmpfs requires many kernel calls which is considerably more effort > > than the occasional malloc call. Additionally files have to be locked, > > journals made etc (you didn't mention your journal setting). > > > > Roger > > -BEGIN PGP SIGNATURE- > > Version: GnuPG v1.4.11 (GNU/Linux) > > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ > > > > iEYEARECAAYFAk5A+g0ACgkQmOOfHg372QTR8ACgqNeeuOxHRy7+hMH5RY/OAyV2 > > Wq0AoMaSRtoFN4obCgmgHlpHthd9z5Zp > > =pkJt > > -END PGP SIGNATURE- > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > Sent from BlackBerry® on Airtel > > ___ > > 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] In memory v/s tmpfs
Journal mode is WAL --Original Message-- From: Roger Binns Sender: sqlite-users-boun...@sqlite.org To: General Discussion of SQLite Database ReplyTo: General Discussion of SQLite Database Subject: Re: [sqlite] In memory v/s tmpfs Sent: Aug 9, 2011 2:42 PM -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/08/2011 06:34 PM, sreekumar...@gmail.com wrote: > From the point of view of performance, I expected similar performance , tmpfs > being a little slower due to filesystem overhead. However, the operations on > tmpfs was much slower than expected. Using tmpfs requires many kernel calls which is considerably more effort than the occasional malloc call. Additionally files have to be locked, journals made etc (you didn't mention your journal setting). Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk5A+g0ACgkQmOOfHg372QTR8ACgqNeeuOxHRy7+hMH5RY/OAyV2 Wq0AoMaSRtoFN4obCgmgHlpHthd9z5Zp =pkJt -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Sent from BlackBerry® on Airtel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] In memory v/s tmpfs
HI, I performed an experiment where I do db operations on an in-memory database and compared it with the results from same operations on a database in tmpfs.(Same db structure,records etc) >From the point of view of performance, I expected similar performance , tmpfs >being a little slower due to filesystem overhead. However, the operations on >tmpfs was much slower than expected. What could be a possible explanation for this behaviour? One difference int configurations is that the sqlite lib is built for multithreading in the tmpfs scenario. -Sreekumar Sent from BlackBerry® on Airtel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Wal index file size
Hello, Is there a relation between the wal index file size and the wal log file ? How does sqlite allocate the max size for the index file. Could figure out scanning thru the source code. However, if someone has already figured out, please share. Sent from BlackBerry® on Airtel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite-Wal file location
I think database will go corrupt if the power goes off during the process of checkpoint. Database will remain intact even if wal file is lost due to power failure. For embedded devices its a balance between flash life, db performance and ram memory usage. Sent from BlackBerry® on Airtel -Original Message- From: Richard Hipp <d...@sqlite.org> Sender: drhsql...@gmail.com Date: Mon, 1 Aug 2011 08:47:09 To: <sreekumar...@gmail.com>; General Discussion of SQLite Database<sqlite-users@sqlite.org> Subject: Re: [sqlite] Sqlite-Wal file location On Mon, Aug 1, 2011 at 8:45 AM, <sreekumar...@gmail.com> wrote: > Hi, > > Thanks for the detailed explanation. > My original question was more in relation to the .db-wal file. I guess the > same discussion is applicable to the db-wal file also?. In my project, in > all probability one writer and multiple readers. Hence the chances of > corruption is greatly reduced. > One problem I have noticed is that I have to create (an empty) a file with > the same name as the database file in the tmpfs for correct working . > If the WAL file is in volatile storage and you lose power or reset, your database will likely go corrupt. > > > Sent from BlackBerry® on Airtel > > -Original Message- > From: Richard Hipp <d...@sqlite.org> > Sender: sqlite-users-boun...@sqlite.org > Date: Mon, 1 Aug 2011 07:07:52 > To: General Discussion of SQLite Database<sqlite-users@sqlite.org> > Reply-To: General Discussion of SQLite Database <sqlite-users@sqlite.org> > Subject: Re: [sqlite] Sqlite-Wal file location > > On Sun, Jul 31, 2011 at 11:51 PM, Sreekumar TP <sreekumar...@gmail.com > >wrote: > > > Hello, > > > > Inorder to suit the needs of my embedded device, I have changed the > > location > > of the .db-wal file from the location of the db file to tmpfs. Does > sqlite > > make assumptions(persistence etc) based on the location of the file ? > > > > An early prototype of WAL did exactly what you describe. But we then > encountered database corruption during testing when two separate processes > tried to access the same database file where one of the processes was in a > chroot jail and the other was not. Both processes used the same name for > the -shm file, but because of the chroot jail, they in fact used two > separate -shm files. Fossil uses SQLite in WAL mode and it often runs in a > chroot jail, so this is not an uncommon scenario. > > The only way we have found to ensure that all processes use the same -shm > file is to put the -shm file in the same directory as the database file. > > For an embedded project, you can put the -shm file wherever you want as > long > as all processes agree to use the same file. If you mess up, and two or > more processes use different -shm files for the same database, database > corruption will result. You have been warned. > > Note that -shm does not have to be a file. We just need an area of shared > memory common to all processes accessing the database. On unix and > windows, > we found this most convenient to implement as a file using mmap(). But if > you have some other mechanism on your embedded device that would work > better, you are welcomed to use that. > > > > > >___ > > 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 >___ > 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] Sqlite-Wal file location
Hi, Thanks for the detailed explanation. My original question was more in relation to the .db-wal file. I guess the same discussion is applicable to the db-wal file also?. In my project, in all probability one writer and multiple readers. Hence the chances of corruption is greatly reduced. One problem I have noticed is that I have to create (an empty) a file with the same name as the database file in the tmpfs for correct working . Sent from BlackBerry® on Airtel -Original Message- From: Richard Hipp <d...@sqlite.org> Sender: sqlite-users-boun...@sqlite.org Date: Mon, 1 Aug 2011 07:07:52 To: General Discussion of SQLite Database<sqlite-users@sqlite.org> Reply-To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Subject: Re: [sqlite] Sqlite-Wal file location On Sun, Jul 31, 2011 at 11:51 PM, Sreekumar TP <sreekumar...@gmail.com>wrote: > Hello, > > Inorder to suit the needs of my embedded device, I have changed the > location > of the .db-wal file from the location of the db file to tmpfs. Does sqlite > make assumptions(persistence etc) based on the location of the file ? > An early prototype of WAL did exactly what you describe. But we then encountered database corruption during testing when two separate processes tried to access the same database file where one of the processes was in a chroot jail and the other was not. Both processes used the same name for the -shm file, but because of the chroot jail, they in fact used two separate -shm files. Fossil uses SQLite in WAL mode and it often runs in a chroot jail, so this is not an uncommon scenario. The only way we have found to ensure that all processes use the same -shm file is to put the -shm file in the same directory as the database file. For an embedded project, you can put the -shm file wherever you want as long as all processes agree to use the same file. If you mess up, and two or more processes use different -shm files for the same database, database corruption will result. You have been warned. Note that -shm does not have to be a file. We just need an area of shared memory common to all processes accessing the database. On unix and windows, we found this most convenient to implement as a file using mmap(). But if you have some other mechanism on your embedded device that would work better, you are welcomed to use that. > ___ > 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite-Wal file location
Hello, Inorder to suit the needs of my embedded device, I have changed the location of the .db-wal file from the location of the db file to tmpfs. Does sqlite make assumptions(persistence etc) based on the location of the file ? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question on Shared Cache Mode
Hello, (1) In SQLite versio 3.5.o an above we can enable the "shared cache" mode. Is the shared cache shared between two processes ? OR is shared only between the threads in the process ? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users