bgcolor # Status Created By Subsys Due Date SCR Assigned Svr Pri Title _Description _Remarks #e8e8bd 2867 active 2008 Jan anonymous Pending 2 2 doesn't build on Cygwin - wrong sqlite3 exe suffix The new Makefile used $(EXE), which doesn't seem to be defined (typo?) _2008-Jan-02 11:12:39 by anonymous:_ {linebreak} Same on mingw: Following patch fixes things:
--- sqlite-3.5.4/Makefile.in Thu Dec 13 19:17:42 2007 +++ sqlite-3.5.4-mingw-fix/Makefile.in Wed Jan 2 11:37:50 2008 @@ -322,7 +322,7 @@ -o $@ $(TOP)/src/shell.c libsqlite3.la \ $(LIBREADLINE) $(TLIBS) -sqlite3$(EXE): $(TOP)/src/shell.c sqlite3.c sqlite3.h +sqlite3$(TEXE): $(TOP)/src/shell.c sqlite3.c sqlite3.h $(LTLINK) $(READLINE_FLAGS) -o $@ \ -DSQLITE_MAX_SQL_LENGTH=1000000000 \ -USQLITE_THREADSAFE -DSQLITE_THREADSAFE=0 \ @@ -577,7 +577,7 @@ -e 's,$$,\\n",' \ $(TOP)/tool/spaceanal.tcl >spaceanal_tcl.h $(LTLINK) -DTCLSH=2 -DSQLITE_TEST=1 $(TEMP_STORE)\ - -o sqlite3_analyzer$(EXE) $(TESTSRC) $(TOP)/src/tclsqlite.c \ + -o sqlite3_analyzer$(TEXE) $(TESTSRC) $(TOP)/src/tclsqlite.c \ libtclsqlite3.la $(LIBTCL)#f2dcdc 2873 active 2008 Jan anonymous Pending 1 1 HAVE_USLEEP, HAVE_FDATASYNC=1 detected but not used by configure; make I noticed that a couple of open source projects were not picking up usleep() for recent sqlite builds and used the coarser grained sleep() instead. Around 11 months ago something changed in sqlite's build process. It seems that both -DHAVE_USLEEP=1, -DHAVE_FDATASYNC=1 and -DOS_UNIX=1 are detected correctly by configure but not used by the generated Makefile. As result, UNIX builds of sqlite3 via ./configure do not use usleep() and fdatasync() and do not define OS_UNIX. I don't know whether the lack of fdatasync() versus the default fsync() affects anyone. Please apply the following patch which corrects the problem with "./configure && make". Thank you.
Index: configure =================================================================== RCS file: /sqlite/sqlite/configure,v retrieving revision 1.45 diff -u -3 -p -r1.45 configure --- configure 27 Nov 2007 14:50:07 -0000 1.45 +++ configure 5 Jan 2008 07:41:00 -0000 @@ -18520,9 +18520,9 @@ if test "$TARGET_EXEEXT" = ".exe"; then OS_UNIX=0 OS_WIN=0 OS_OS2=1 - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_OS2=1" + CFLAGS="$CFLAGS -DOS_OS2=1" if test "$ac_compiler_gnu" == "yes" ; then - TARGET_CFLAGS="$TARGET_CFLAGS -Zomf -Zexe -Zmap" + CFLAGS="$CFLAGS -Zomf -Zexe -Zmap" BUILD_CFLAGS="$BUILD_CFLAGS -Zomf -Zexe" fi else @@ -18530,14 +18530,14 @@ if test "$TARGET_EXEEXT" = ".exe"; then OS_WIN=1 OS_OS2=0 tclsubdir=win - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_WIN=1" + CFLAGS="$CFLAGS -DOS_WIN=1" fi else OS_UNIX=1 OS_WIN=0 OS_OS2=0 tclsubdir=unix - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_UNIX=1" + CFLAGS="$CFLAGS -DOS_UNIX=1" fi @@ -19392,7 +19392,7 @@ fi echo "$as_me:$LINENO: result: $ac_cv_func_usleep" >&5 echo "${ECHO_T}$ac_cv_func_usleep" >&6 if test $ac_cv_func_usleep = yes; then - TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_USLEEP=1" + CFLAGS="$CFLAGS -DHAVE_USLEEP=1" fi @@ -19491,7 +19491,7 @@ fi echo "$as_me:$LINENO: result: $ac_cv_func_fdatasync" >&5 echo "${ECHO_T}$ac_cv_func_fdatasync" >&6 if test $ac_cv_func_fdatasync = yes; then - TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_FDATASYNC=1" + CFLAGS="$CFLAGS -DHAVE_FDATASYNC=1" fi Index: configure.ac =================================================================== RCS file: /sqlite/sqlite/configure.ac,v retrieving revision 1.31 diff -u -3 -p -r1.31 configure.ac --- configure.ac 27 Nov 2007 14:50:07 -0000 1.31 +++ configure.ac 5 Jan 2008 07:41:00 -0000 @@ -310,9 +310,9 @@ if test "$TARGET_EXEEXT" = ".exe"; then OS_UNIX=0 OS_WIN=0 OS_OS2=1 - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_OS2=1" + CFLAGS="$CFLAGS -DOS_OS2=1" if test "$ac_compiler_gnu" == "yes" ; then - TARGET_CFLAGS="$TARGET_CFLAGS -Zomf -Zexe -Zmap" + CFLAGS="$CFLAGS -Zomf -Zexe -Zmap" BUILD_CFLAGS="$BUILD_CFLAGS -Zomf -Zexe" fi else @@ -320,14 +320,14 @@ if test "$TARGET_EXEEXT" = ".exe"; then OS_WIN=1 OS_OS2=0 tclsubdir=win - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_WIN=1" + CFLAGS="$CFLAGS -DOS_WIN=1" fi else OS_UNIX=1 OS_WIN=0 OS_OS2=0 tclsubdir=unix - TARGET_CFLAGS="$TARGET_CFLAGS -DOS_UNIX=1" + CFLAGS="$CFLAGS -DOS_UNIX=1" fi AC_SUBST(BUILD_EXEEXT) @@ -565,13 +565,13 @@ AC_SUBST(TARGET_DEBUG) ######### # Figure out whether or not we have a "usleep()" function. # -AC_CHECK_FUNC(usleep, [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_USLEEP=1"]) +AC_CHECK_FUNC(usleep, [CFLAGS="$CFLAGS -DHAVE_USLEEP=1"]) #-------------------------------------------------------------------- # Redefine fdatasync as fsync on systems that lack fdatasync #-------------------------------------------------------------------- -AC_CHECK_FUNC(fdatasync, [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_FDATASYNC=1"]) +AC_CHECK_FUNC(fdatasync, [CFLAGS="$CFLAGS -DHAVE_FDATASYNC=1"]) ######### # Generate the output files._2008-Jan-05 08:24:29 by anonymous:_ {linebreak} It appears that http://www.sqlite.org/sqlite3-3.5.4.bin.gz and http://www.sqlite.org/sqlite-3.5.4.so.gz use sleep and fsync even though usleep and fdatasync are available on Linux. On the Linux man page, it claims that fdatasync is more efficient than fsync: "Unfortunately, fsync() will always initiate two write operations: one for the newly written data and another one in order to update the modification time stored in the inode. If the modification time is not a part of the transaction concept fdatasync() can be used to avoid unnecessary inode disk write operations." #f2dcdc 2874 active 2008 Jan anonymous Pending 1 1 THREADSAFE #define HAVE_LOCALTIME_R, HAVE_GMTIME_R in os_unix.c The precompiled shared sqlite3 library for Linux on sqlite.org which appears to be built with pthread support is using localtime and gmtime which are not threadsafe. For THREADSAFE builds could either configure be changed to detect the functions gmtime_r and localtime_r or change os_unix.c to explicitly #define HAVE_LOCALTIME_R and HAVE_GMTIME_R? #e8e8bd 2875 active 2008 Jan anonymous Pending 3 2 LIKE does not work with lowercase swedish characters Swedish letters å,ä,ö is not supported by the LIKE statement. When trying to perform a query like SELECT * FROM table WHERE name LIKE "å%" we will not get a match for names starting on Å (which is uppercase for å). To recreate: ============================================== SQLite version 3.5.4 Enter ".help" for instructions sqlite> CREATE TABLE TestingTable(Name varchar(20)); sqlite> INSERT INTO TestingTable values ('Sweden'); sqlite> INSERT INTO TestingTable values ('sweden'); sqlite> INSERT INTO TestingTable values ('Åland'); sqlite> INSERT INTO TestingTable values ('åland'); sqlite> SELECT * FROM TestingTable; Sweden sweden Åland åland sqlite> SELECT * FROM TestingTable WHERE Name LIKE "swe%"; Sweden sweden sqlite> SELECT * FROM TestingTable WHERE Name LIKE åla%"; åland ============================================================ #f2dcdc 2878 active 2008 Jan anonymous Fixed 1 1 Memory leaks with latest CVS [4693] This SQL leaks memory with CVS [4693]: CREATE TABLE x(id integer primary key, a TEXT NULL); INSERT INTO x (a) VALUES ('first'); CREATE TABLE tempx(id integer primary key, a TEXT NULL); INSERT INTO tempx (a) VALUES ('t-first'); CREATE VIEW tv1 AS SELECT x.id, tx.id FROM x JOIN tempx tx ON tx.id=x.id; One leak is caused by "CREATE TABLE tempx", a second one by "CREATE VIEW tv1". The above SQL is a digest of select7.test, select7-2.1. _2008-Jan-14 17:51:11 by anonymous:_ {linebreak} I have tested again with CVS [4711] and it does no longer show the original leaks. I therefore consider this issue fixed and I will now close this ticket. ---- _2008-Jan-14 23:56:34 by anonymous:_ {linebreak} Doing a fulltest with -MSQLITE_MEMDEUG I see reports of memory leaks. I assume these are of little or minimal interest at present because of the amount of code flux. If you do want details, please let me know (I'll recheck this ticket tomorrow I guess). ---- _2008-Jan-15 08:23:56 by anonymous:_ {linebreak} Thanks for the follow-up. I am not running the original test-suite but have have ported a great number of them to Delphi. If you could just let me know which tests caused the leaks you fixed in [4712] I'd be more than glad the port these test as well and let you know my findings. ---- _2008-Jan-19 00:56:17 by anonymous:_ {linebreak} A test from 20 minutes ago passes cleanly. This could be closed. #cfe8bd 2879 active 2008 Jan anonymous Pending anonymous 4 3 VACUUM enters temporary sequence numbers in sqlite_sequence I have two TEMPORARY tables added to a database, that both contain an INTEGER PRIMARY KEY AUTOINCREMENT field. After issuing a VACUUM command, the maximum value of these fields is added to the sqlite_sequence table. And stay there after the connection closes. #cfe8bd 2882 active 2008 Jan anonymous Pending 3 3 fulltest failure: ./testfixture: wrong # args: should be "cksum db" exclusive-ioerr-2.2.1... Ok ./testfixture: wrong # args: should be "cksum db" while executing "ifcapable vacuum { do_ioerr_test ioerr-2 -cksum true -sqlprep { BEGIN; CREATE TABLE t1(a, b, c); INSERT INTO t1 VALUES(1, randstr(50,..." (file "../test/ioerr.test" line 58) invoked from within "source $testdir/ioerr.test" (file "../test/exclusive3.test" line 50) invoked from within "source $testfile" ("foreach" body line 5) invoked from within "foreach testfile [lsort -dictionary [glob $testdir/*.test]] { set tail [file tail $testfile] if {[lsearch -exact $EXCLUDE $tail]>=0} continue ..." ("for" body line 7) invoked from within "for {set Counter 0} {$Counter<$COUNT && $nErr==0} {incr Counter} { if {$Counter%2} { set ::SETUP_SQL {PRAGMA default_synchronous=off;} } else ..." (file "..//test/all.test" line 85) _2008-Jan-14 23:25:49 by anonymous:_ {linebreak} The latest code seems to have fixed this. I would close this but I don't see how to do that. #cfe8bd 2886 active 2008 Jan anonymous Pending 3 3 testfixture: -fPIC needed when building extension(s) (this fix/change is probably needed in older versions too, i meant to send this in earlier) -fPIC is needed when building extensions (some platforms don't need this or don't care --- x86-64 does) diff --git a/test/loadext.test b/test/loadext.test index 81e152f..2a7fa2e 100644 --- a/test/loadext.test +++ b/test/loadext.test @@ -64,7 +64,7 @@ if {![file exists $testextension]} { set srcdir [file dir $testdir]/src set testextsrc $srcdir/test_loadext.c if {[catch { - exec gcc -Wall -I$srcdir -I. -g -shared $testextsrc -o $testextension + exec gcc -Wall -fPIC -I$srcdir -I. -g -shared $testextsrc -o $testextension } msg]} { puts "Skipping loadext tests: Test extension not built..." puts $msg #f2dcdc 2893 active 2008 Jan anonymous Pending 1 1 incorrect integer range tests recently a function that performs integer range tests was added to the cvs (check-in [4706]), but if i am correct there is a problem in the return value of the function in the file vdbemem.c: static i64 doubleToInt64(double r){ ... if( r<(double)minInt ){ return minInt; }else if( r>(double)maxInt ){ return minInt; <-- is this correct, shouldn't it be maxInt? }else{ return (i64)r; } } _2008-Jan-16 17:33:56 by drh:_ {linebreak} See the remarks on ticket #2280. The code duplicates the behavior of the FPU on x86. ---- _2008-Jan-16 18:21:28 by anonymous:_ {linebreak} did you mean ticket #2880? didn't read that ticket before, but since there was no comment regarding that behavior in the function it seemed (to my eyes) that it was a mistake. maybe adding a small comment in there would clarify this issue ---- _2008-Jan-16 18:39:42 by anonymous:_ {linebreak} Just because the double to int overflow behavior happens to be that way with GCC on x86, is it desirable? #f2dcdc 2897 active 2008 Jan anonymous Pending 1 1 String or BLOB exceed size limit This error was shown after attemp to read script from SQLite 3.5.4 shell in order to recreate old DB. Details: 1. Database was created with SQLite 3.3.4. Around 20 standard fieds and one BLOB. 2. The only one existed table was dumped with shell of SQLite 3.5.4. SQL script seems to be coorrect. 3. Opened SQLite 3.5.4 and read script in new DB. The error "String or BLOB exceed size limit" are sown for several lines. Many records missing. 4. Attempted to dump table with shell of version 3.3.6 (have no more 3.3.4 shell) and read into new DB with 3.5.4 shell The same errors are shown. The same steps was attempted with 3.3.6. shell only. All seems to be correct. _2008-Jan-17 20:23:25 by drh:_ {linebreak} This size limit on BLOBs in SQLite version 3.5.4 is 1GB. How big is your blob, exactly? ---- _2008-Jan-17 22:22:24 by anonymous:_ {linebreak} BLOB in each record is no more than few MB. Mostly it is few KB (e-client and news application). Whole DB have around 200MB. ---- _2008-Jan-18 02:28:11 by drh:_ {linebreak} This issue is probably resolved by check-in [4636], then. ---- _2008-Jan-18 14:28:13 by anonymous:_ {linebreak} If directive SQLITE_MAX_SQL_LENGTH is not defined it is set to 1,000,000 (10^6) in amalgamation code of 3.5.4. #f2dcdc 2898 active 2008 Jan anonymous Pending 1 1 Latest CVS for 3.5.4 fails to build test1.c gcc -pipe -O3 -g -Wall -DSQLITE_DISABLE_DIRSYNC=1 -I. -I../src -DNDEBUG -I/usr/include -DSQLITE_THREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -DTCLSH=1 -DSQLITE_TEST=1 -DSQLITE_CRASH_TEST=1 -DSQLITE_NO_SYNC=1 -DTEMP_STORE=1 -o .libs/testfixture ../src/attach.c ../src/btree.c ../src/build.c ../src/date.c ../src/expr.c ../src/func.c ../src/insert.c ../src/malloc.c ../src/os.c ../src/os_os2.c ../src/os_unix.c ../src/os_win.c ../src/pager.c ../src/pragma.c ../src/prepare.c ../src/printf.c ../src/select.c ../src/test1.c ../src/test2.c ../src/test3.c ../src/test4.c ../src/test5.c ../src/test6.c ../src/test7.c ../src/test8.c ../src/test9.c ../src/test_autoext.c ../src/test_async.c ../src/test_btree.c ../src/test_config.c ../src/test_hexio.c ../src/test_malloc.c ../src/test_md5.c ../src/test_onefile.c ../src/test_schema.c ../src/test_server.c ../src/test_tclvar.c ../src/test_thread.c ../src/tokenize.c ../src/utf.c ../src/util.c ../src/vdbe.c ../src/vdbeapi.c ../src/vdbeaux.c ../src/vdbemem.c ../src/where.c parse.c ../src/tclsqlite.c ./.libs/libsqlite3.so -L/usr/lib64 -ltcl8.4 -ldl -lpthread -lieee -lm -Wl,--rpath -Wl,/common/pkgs/sqlite-3.5.4.3/lib ../src/build.c: In function 'sqlite3RefillIndex': ../src/build.c:2275: warning: cast to pointer from integer of different size ../src/func.c: In function 'trimFunc': ../src/func.c:919: warning: cast from pointer to integer of different size ../src/func.c: In function 'sqlite3RegisterBuiltinFunctions': ../src/func.c:1464: warning: cast to pointer from integer of different size ../src/func.c:1483: warning: cast to pointer from integer of different size ../src/insert.c: In function 'sqlite3GenerateConstraintChecks': ../src/insert.c:1200: warning: cast to pointer from integer of different size ../src/insert.c:1034: warning: 'j2' may be used uninitialized in this function ../src/insert.c: In function 'sqlite3Insert': ../src/insert.c:373: warning: 'regFromSelect' may be used uninitialized in this function ../src/test1.c: In function 'test_collate_func': ../src/test1.c:2085: warning: cast from pointer to integer of different size ../src/test1.c: In function 'test_collate_needed_cb': ../src/test1.c:2209: warning: cast to pointer from integer of different size ../src/test1.c: In function 'alignmentCollFunc': ../src/test1.c:2258: warning: cast from pointer to integer of different size ../src/test1.c:2259: warning: cast from pointer to integer of different size ../src/test8.c: In function 'echoBestIndex': ../src/test8.c:722: warning: 'nRow' may be used uninitialized in this function ../src/vdbe.c: In function 'sqlite3VdbeExec': ../src/vdbe.c:502: warning: 'pOut' may be used uninitialized in this function ../src/vdbe.c:501: warning: 'pIn3' may be used uninitialized in this function ../src/vdbe.c:501: warning: 'pIn2' may be used uninitialized in this function ../src/vdbe.c:501: warning: 'pIn1' may be used uninitialized in this function ../src/vdbeaux.c: In function 'sqlite3VdbeChangeP4': ../src/vdbeaux.c:529: warning: cast from pointer to integer of different size ../src/vdbemem.c: In function 'sqlite3ValueText': ../src/vdbemem.c:911: warning: cast from pointer to integer of different size /tmp/ccsuOeus.o: In function `reset_prng_state': /build/work/sqlite-3.5.4.3/bld/../src/test1.c:4280: undefined reference to `sqlite3ResetPrngState' /tmp/ccsuOeus.o: In function `restore_prng_state': /build/work/sqlite-3.5.4.3/bld/../src/test1.c:4267: undefined reference to `sqlite3RestorePrngState' /tmp/ccsuOeus.o: In function `save_prng_state': /build/work/sqlite-3.5.4.3/bld/../src/test1.c:4254: undefined reference to `sqlite3SavePrngState' collect2: ld returned 1 exit status make: *** [testfixture] Error 1 _2008-Jan-17 23:54:58 by anonymous:_ {linebreak} Problem appears to be here in libsqlite.3.so.0.8.6 as shown by: nm -A .libs/libsqlite3.so.0.8.6 | grep sqlite3ResetPrngState which shows no entry point. And: nm -A .libs/random.o | grep sqlite3ResetPrngState which also shows no entry point. ---- _2008-Jan-17 23:56:55 by anonymous:_ {linebreak} Ah... It appears -DSQLITE_TEST should be passed when building test1.c and left off when building prior to install. ---- _2008-Jan-21 20:16:00 by anonymous:_ {linebreak} In the makefile the right flag appears to be set, it's just not making it through to the compile for some reason. ---- _2008-Jan-21 20:16:24 by anonymous:_ {linebreak} Still fails the same based on today's cvs update. ---- _2008-Jan-23 03:14:49 by anonymous:_ {linebreak} This bug fixed as of latest cvs pull #cfe8bd 2901 active 2008 Jan anonymous Pending 3 3 ROLLBACK and COMMIT statements should not expire Currently, whenever a statement changes the schema of the database, all prepared statements will be expired, no matter whether they actually need to be prepared again or not. This is especially problematic for ROLLBACK statements in a multi-statement transaction. Currently there is no way to guaranty that a multi-statement transaction can at least be rolled back in case of an error, because one has to (re)prepare the ROLLBACK statement to roll back the transaction, which can fail because of OOM (in a multi-threaded application). #cfe8bd 2902 active 2008 Jan anonymous Pending drh 3 3 Add watch support to SQLite SQLite currently provides only TRIGGERs and the update_hook() as a way for applications to stay informed about changes to the database. But both of these alternatives do not provide enough details about the actual changes to the underlying database file(s). We've prepared a patch for SQLite 3.5.x to allow applications to install a watch_hook into the database, that will be invoked everytime the database is changed with exact details about the change that was performed. _2008-Jan-22 16:06:59 by anonymous:_ {linebreak} Great idea and nice job. This functionality is very useful. ---- _2008-Jan-31 18:27:16 by anonymous:_ {linebreak} Any chance to get this committed for the next release (i.e. 3.5.6)? ---- _2008-Jan-31 19:38:35 by drh:_ {linebreak} Unlikely, for two reasons: 1: I am unconvinced that this patch solves a problem that needs solving. It is vitally important to a project like SQLite that we work to avoid clutter and cruft. That means that any change must have a compelling rational or else it is rejected. 2: The patches are against version 3.5.4. There were many changes to the core for 3.5.5 and the patches no longer work. ---- _2008-Jan-31 20:58:55 by anonymous:_ {linebreak} We can (and will) port the changes to 3.5.5, so the second point will be done. First the first point, I'm not sure how many projects will actually need this functionality, but I guess there are quite a lot of projects that would benefit, and for the others, there's zero overhead due to this patch. ---- _2008-Jan-31 21:21:30 by drh:_ {linebreak} There is a lot of overhead for me because if I accept this patch, that means I have to maintain it forever. Most of the work is in maintenance, not coming up with the original patch. #e8e8bd 2903 active 2008 Jan anonymous Pending 2 2 tclinstaller.tcl fails on path and permissions issue When compiling using custom PREFIX, pointing to private directory, tclinstaller.tcl fails, because it tries to remove contents from /usr/share/tcl8.4/sqlite3.
./configure --prefix=/my/private/sqlite/sqlite-3.5.4 ... # success make ... # success make install ... tclsh ./tclinstaller.tcl 3.5 error deleting "/usr/share/tcl8.4/sqlite3": not owner while executing "file delete -force $LIBDIR/sqlite3" (file "./tclinstaller.tcl" line 17) make: *** [tcl_install] Error 1I've found two work-arounds: 1: If you run make install as root. 2: If you use ./configure --disable-tcl _2008-Jan-28 17:47:39 by anonymous:_ {linebreak} I also ran into this problem. make install as root will end up copying files into the system's library directory and is almost certainly not what you want if you specified your own --prefix. #e8e8bd 2905 active 2008 Jan anonymous Pending pweilbacher 2 2 mutex_os2.c - incorrect mutex implementation The OS/2 version of sqlite3_mutex_alloc() is badly broken. It creates named mutexes which, by design, are global rather than process-specific as intended. This might be minimally acceptable except that the function reuses the same name every time it attempts to create a SQLITE_MUTEX_FAST or SQLITE_MUTEX_RECURSIVE. The result is that every call returns the exact same semaphore to every thread in every process using sqlite3. Once this mutex is owned by one process, other processes calling sqlite3_mutex_enter() will be blocked. Much the same is true for the static mutexes. Every process ends up using the exact same SQLITE_MUTEX_STATIC_MASTER, SQLITE_MUTEX_STATIC_MEM, etc. There's another flaw that is fairly minor compared with the above: in an attempt to avoid concurrency when creating the static mutexes, this function uses an API call that is thoroughly deprecated. The attached patch remedies all of these issues. Since the logic that protects the creation of the static mutexes may not be self-evident, here's an explanation: The existence (or non-existence) of a given named mutex is itself a semaphore. If the isInit flag is false, the code attempts to create a mutex whose name is unique to that process. If the attempt is successful, there are two possibilities: (1) either the current thread is the first to reach this code & may proceed; (2) or while the current thread was making its preparations, another thread created the mutex, did the init, then closed the mutex. Testing isInit immediately after creating the mutex determines which possibility is valid. If mutex creation fails due to a duplicate name, then another thread is currently performing the init. In this case, the current thread simply has to wait a while until the other thread is done & isInit becomes true. Submitted by Rich Walsh (richATe-vertise.com) #f2dcdc 2907 active 2008 Jan anonymous Pending 1 1 Issues of sqlite3 with Windows Mobile 5/6 hi. we are currently using sqlite3 for our mobile application. it has been running without a hitch on pocket pc 2003 and previous versions. come windows mobile 5 and 6 we have been getting errors, although not consistent yet. one example is 'EXCEPTION_DATATYPE_MISALIGNMENT'. another is 'SELECT STATMENTS TO THE LEFT AND RIGHT OF UNION ARE NOT EQUAL'. i was wondering if you have any known compatibility issues of your product with this version of windows mobile. thanks in advance. _2008-Jan-28 13:26:26 by anonymous:_ {linebreak} EXCEPTION_DATATYPE_MISALIGNMENT is thrown when you try to use and Odd pointer address. I wrote a custom allocator for WinCE/ARM platform, and I have to take care about memory alignment (I used to align at 2 bytes, and at that time it solved the problem) #cfe8bd 2908 active 2008 Jan anonymous Pending 3 3 Add support to examine whether statements modify the database Currently there is no way to check whether a compiled statement will modify the database when being executed. Of course, there is the work-around of misusing the authorizer callback for this purpose, but this is kinda error prone and causes quite some overhead for such a simple purpose. #e8e8bd 2911 active 2008 Jan anonymous Pending 2 2 Adding parentheses to a FROM clause Hi, Parentheses in a FROM statement seem to mess with the ability to use table aliases in the "what" part. Here is an example: Start SQLite: $ sqlite3 employee.db SQLite version 3.5.4 Enter ".help" for instructions create a couple of tables and populate them with test data: sqlite> create table person (id integer, name text, employerid integer); sqlite> create table employer (id integer, name text); sqlite> insert into person (id, name, employerid) values (1, "Dave", 1); sqlite> insert into employer (id, name) values (1, "ACME"); Run a simple query with *no parentheses* in the FROM statement: sqlite> select b.id from person as a inner join employer as b on a.employerid = b.id; 1 Everything works as expected. Now, repeat that query *with parentheses*: sqlite> select b.id from (person as a inner join employer as b on a.employerid = b.id); SQL error: no such column: b.id There you have it. This may be related to ticket #1822, although that ticket deals with aliases and subqueries. This problem seems to be more fundamental. Many thanks, -- Dave #cfe8bd 2914 active 2008 Jan anonymous Pending 3 3 ATTACH returns SQLITE_ERROR when it means SQLITE_BUSY I'm seeing the same behavior as in #2096, with SQLite 3.5.4. ATTACH DATABASE fails with SQLITE_ERROR rather than SQLITE_BUSY when the database to be attached, or the main database of the connection being attached to, is EXCLUSIVE-locked by another database connection. For added confusion, sqlite3_errmsg() says "database is locked" when the ATTACH is done via sqlite3_exec(), but "SQL logic error or missing database" when the ATTACH is done via sqlite3_step(). As a result of this bug, it is difficult to distinguish between fatal and transient ATTACH errors, particularly when sqlite3_step() is used. I am attaching a test program that demonstrates the problem. #f2dcdc 2916 active 2008 Feb anonymous Pending 1 1 sqlitedll-3_5_5.zip is older 3.5.4 binary sqlitedll-3_5_5.zip in download section is same with old 3.5.4 binary. _2008-Feb-01 12:13:04 by anonymous:_ {linebreak} Yes , I can confirm it #cfe8bd 2613 active 2007 Sep anonymous Pending drh 3 3 replace doesn't work with blobs containing \x0, otherwise it does The replace expression function does not work with blobs in case of contained zero terminator character; but it does if there is not this special character included. I expected the function to work similar like substr with blob-safety in case of type is blob only. X'nnnn' is of type blob, so following example should have returned a blob type result X'0102FF0405' in the 2nd and 3rd line. How to get to this result?
SQLite version 3.4.2 Enter ".help" for instructions sqlite> select hex(replace(X'0102030405',X'03',X'FF')); 0102FF0405 sqlite> select hex(replace(X'0102000405',X'00',X'FF')); sqlite> select typeof(replace(X'0102000405',X'00',X'FF')); null sqlite>_2007-Sep-03 04:21:12 by anonymous:_ {linebreak} Replace was designed to work with strings. However, working with blobs would be an interesting extension. ---- _2007-Oct-18 06:13:10 by anonymous:_ {linebreak} I've seen a similar situation where I can't reliably store stings with nulls in the middle of them as TEXT. I can convert them to blobs, in which case length(...) works correctly. I if convert them back to strings, length(...) treats them as C-strings. Is this the expected behavior? I notice the entire column is preserved even when it's has TEXT affinity, I can append data to it as a string, cast back to a blob and see everything (am I explaining this poorly?) This all seems a bit counter intuitive in some ways. Perhaps strings shouldn't treat NULL characters as special? ---- _2007-Oct-27 16:45:41 by anonymous:_ {linebreak} Treatment of length operator is - as fas as I know - dependent on type: {linebreak} As text it is the length number of UTF-8 characters and as blob it is the number of bytes. As long as all the UTF-8 characters out of the lower half ASCII char-set (127 of them), this is identical beside the fact of different 0-terminator interpretation. {linebreak} To append is something different than using the replace operator. My suggestion would be to make the replace operator work with bytes (not UTF-8) in case of all 3 parameters are of type blob. {linebreak} Another suggestion: the UTF aware functions are Private declared and not usable from within a loadable extension dll/so. This should be changed. ---- _2008-Jan-28 19:36:39 by anonymous:_ {linebreak} Will there come a solution for this with the next release? It is really not fair to handle a blob only like text which cannot contain a zero terminator. With this unique useful function a zero-containing blob could be formed into a normal text string without loosing the part behind the zero terminator. It would be really a step forward without too much effort. #e8e8bd 2627 active 2007 Sep anonymous Pending 3 2 Improper parsing of nested JOIN SQLite has a problem with multiple nested JOINs. The only way to get it workig is to remove the surrounding brackets. Removing the brackets unfortunately do not work in other DB systems such as MS SQL, mysql etc. This does not work: Select ContactPhone.* From (ContactPhone LEFT OUTER JOIN ContactLocation ON ContactPhone.PHNLCT_ID = ContactLocation.LCT_ID) LEFT OUTER JOIN ContactItem ON ContactLocation.LCTITM_ID = ContactItem.ITM_ID (It complains about LCT_ID or similar) This works after removing the brackets: Select ContactPhone.* From ContactPhone LEFT OUTER JOIN ContactLocation ON ContactPhone.PHNLCT_ID = ContactLocation.LCT_ID LEFT OUTER JOIN ContactItem ON ContactLocation.LCTITM_ID = ContactItem.ITM_ID All other major DB systems require the surrounding brackets. Do you think it is possible to fix it? Apart from this little little SQLite is an awesome project. Thank you Jakub Klos _2007-Sep-06 13:07:32 by anonymous:_ {linebreak} I don't have access to MS SQL Server, but MySQL and Oracle have no issue with the query without parentheses:
create table x1(a int, b int); create table x2(c int, d int); create table x3(e int, f int); mysql> select x1.* from x1 left join x2 on x1.a=x2.c left join x3 on x2.d=x3.e; Empty set (0.00 sec)---- _2007-Sep-06 19:03:33 by anonymous:_ {linebreak} MSSQL also has no problems without the parens. As a matter of fact, the only DB that I know of that requires them is MS Access (JET). ---- _2007-Sep-06 20:11:31 by anonymous:_ {linebreak} I guess he had no luck filing a JET bug. ---- _2007-Sep-11 17:22:28 by anonymous:_ {linebreak} True, MS access requires the parens but all other major DBs support the query syntax with the parens. So why SQLite does not like it? It should simply ignore them if possible. Thank you #c8c8c8 2632 review 2007 Sep anonymous Works_As_Designed danielk1977 5 2 sqlite3* not available within loadable extension
sqlite3* db = context->s.db;is the required variable for use with sqlite3_prepare, sqlite3_step and sqlite3_finalize etc. (That's my work-around){linebreak} What is needed only for loadable extension developers to stay aligned with new versions is the re-arrangement of existing struct sqlite3_context {Mem s} and struct Mem {sqlite3 *db} to become able to simply *typecast* sqlite3_context* into a sqlite3*; all the other internals of Mem would stay private to vdbe even when version numbers will grow.{linebreak} And now to answer the question "why the hell is he asking for this?" here my explanation by an example of what I made running:{linebreak}
select execSQL("select 'iniTable' as Title;select * from iniTable", '|', X'0D0A', X'0D0A'||'----'||X'0D0A' ); iniTable ---- Common|Integer|4711 Common|Float|3.1416927 Common|Double|3.1416927 Common|Text|Dies ist Text Common|Blob|X'0D0A'Think about an application which has to create a whole InMemory-Database table structure inclusive triggers and views:
select execSQL(stmt) from StatementTable where Usage='InMemory';And all within one and the same transaction. It is really that easy! This could answer also some points in the wishlist of the SQLite3 web site; all to do this is reordering Mem s in sqlite3_context and sqlite3*db in struct Mem to the beginning of the structures.{linebreak}Doing this with the release of version 3.5 is really a good moment, isn't it? _2007-Sep-07 11:54:38 by danielk1977:_ {linebreak} The usual approach is to pass the sqlite3* handle to sqlite3_create_function() as the 5th argument and then retrieve it from within the user function implementation using sqlite3_user_data(). If you depend on the definitions of internal data structures, you're living dangerously. ;) ---- _2007-Sep-07 12:54:56 by anonymous:_ {linebreak} Is then the following correct and will work in the same expected way (same pointer within ld-ex-function)? Even with sqlite_auto_extension called at DLL load time? (Win32 with Amalgamation and BDS2006){linebreak}
#includeThank's for the smilie and your hint. Of cause, I do not want to live dangerous. This was the purpose of my posting to get this fixed officially with the next release. But if you say it is not worth the "small change" of reordering and I'm doing it correct this way, then that's fine. I started similar to your proposal by using a static variable and assigned it from within sqlite3_extension_init but this would not work with more than one db-connection (the last one would win). The other open point for me is the right place and methode to link the same source code as used for the Loadable Extension Functions to become a DLL (for SQlite3.exe .load) as auto-loaded extensions into a specialized made of SQLite3.DLL for my applications. Do you have another trick than modifying the sqlite3_open-functions with compiler switches? This would be great. Even if above statements work, do you think the reordering makes sense for an easier transfer for the sqlite3* into the loadable extension function? The 5th argument would be kept free for other purposes. What is your estimation of likelyhood for that proposed change? What's against it? ---- _2007-Sep-07 14:11:38 by danielk1977:_ {linebreak} The code above is correct as far as I can see. As far as reordering structures to allow tricky casting, there's no particular argument against, other than it makes the code a bit tricky to follow. And limits the ways in which we can change the contents of the currently opaque sqlite3_context structure in the future. Something like: sqlite3 *sqlite3_get_connection(sqlite3_context *p){ return p->s.db; } might be a chance I guess. ---- _2007-Sep-10 18:58:09 by anonymous:_ {linebreak} That is a very good idea. Now I have a case, where I would like to make use of both, the db connection AND the user data:{linebreak} I have two database selections (by a list of keys) and I would like to used one function to compare "equal" and another "unequal". In this case I could use the same function body and use the userdata to distinguish between the cases equal and unequal while using the db connection to query the selection with the given keys.{linebreak} Using 2 functions which are sharing the same body of a 3rd function will be my workaround until this new API function becomes available with the next 3.5.x version. Many thanks. ---- _2007-Sep-10 19:09:51 by anonymous:_ {linebreak} I agree. It is very reasonable to want to use an sqlite3* connection from within a registered function as well. Scenario - perform an SQL query on same connection from within a user-defined function. Setting user data just for this common action is awkward. ---- _2008-Jan-08 14:46:14 by anonymous:_ {linebreak} when will this function become usable? will it come with 3.5.5 ? {linebreak} sqlite3 *sqlite3_get_connection(sqlite3_context *p) #cfe8bd 2634 active 2007 Sep anonymous Pending 3 3 .schema uses incorrect ORDER BY giving wrong dependency order When the schema is exported, views are sorted by name instead of by dependency. If there are nested views, the schema may be invalid when used to re-create the database. sqlite3 create table t ( f text ); create view v2 as select f from t; create view v1 as select f from v2; .output test.txt .schema .exit sqlite3 .read test.txt SQL error near line 2: no such table: main.v2 _2007-Sep-07 15:33:06 by anonymous:_ {linebreak} Use .dump instead as a workaround. Unlike .schema, .dump does not use ORDER BY in its queries on sqlite_master and it outputs its rows in order of entry.SQLITE_EXTENSION_INIT1 int sqlite3_extension_init( sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi ) { SQLITE_EXTENSION_INIT2(pApi) sqlite3_create_function(db, "execSQL", -1, SQLITE_UTF8, db, execSqlFunc, 0, 0); ... } static void execSqlFunc( sqlite3_context *context, int argc, sqlite3_value **argv ) { sqlite3 *db = sqlite3_user_data(context ); /* instead of using context's internals ( sqlite3 *db = context->s.db ); or waiting for reordering of them and using typecase ( sqlite3 *db = (sqlite3*)context; ) respective ( sqlite3 *db = *(sqlite3**)context; )? */ ... }
SQLite version 3.5.0 Enter ".help" for instructions sqlite> create table t ( f text ); sqlite> create view v2 as select f from t; sqlite> create view v1 as select f from v2; sqlite> sqlite> .schema CREATE TABLE t ( f text ); CREATE VIEW v1 as select f from v2; CREATE VIEW v2 as select f from t; sqlite> sqlite> .dump BEGIN TRANSACTION; CREATE TABLE t ( f text ); CREATE VIEW v2 as select f from t; CREATE VIEW v1 as select f from v2; COMMIT;Suggested patch:
Index: src/shell.c =================================================================== RCS file: /sqlite/sqlite/src/shell.c,v retrieving revision 1.167 diff -u -3 -p -r1.167 shell.c --- src/shell.c 7 Sep 2007 01:12:32 -0000 1.167 +++ src/shell.c 7 Sep 2007 15:28:24 -0000 @@ -1411,8 +1411,7 @@ static int do_meta_command(char *zLine, "SELECT sql FROM " " (SELECT * FROM sqlite_master UNION ALL" " SELECT * FROM sqlite_temp_master) " - "WHERE tbl_name LIKE shellstatic() AND type!='meta' AND sql NOTNULL " - "ORDER BY substr(type,2,1), name", + "WHERE tbl_name LIKE shellstatic() AND type!='meta' AND sql NOTNULL", callback, &data, &zErrMsg); zShellStatic = 0; } @@ -1421,8 +1420,7 @@ static int do_meta_command(char *zLine, "SELECT sql FROM " " (SELECT * FROM sqlite_master UNION ALL" " SELECT * FROM sqlite_temp_master) " - "WHERE type!='meta' AND sql NOTNULL AND name NOT LIKE 'sqlite_%'" - "ORDER BY substr(type,2,1), name", + "WHERE type!='meta' AND sql NOTNULL AND name NOT LIKE 'sqlite_%'", callback, &data, &zErrMsg ); }after patch:
SQLite version 3.5.0 Enter ".help" for instructions sqlite> create table t ( f text ); sqlite> create view v2 as select f from t; sqlite> create view v1 as select f from v2; sqlite> .schema CREATE TABLE t ( f text ); CREATE VIEW v2 as select f from t; CREATE VIEW v1 as select f from v2; sqlite> .q#f2dcdc 2652 active 2007 Sep drh Pending 1 1 Aggregate function cannot be used from within a subquery The following SQL fails: CREATE TABLE t1(x,y); INSERT INTO t1 VALUES(1,2); CREATE TABLE t2(z); INSERT INTO t2 VALUES(1); SELECT (SELECT y FROM t1 WHERE x=min(z)) FROM t2; Problem reported on the mailing list. _2007-Sep-23 16:01:09 by anonymous:_ {linebreak} Your syntax appears to be incorrect.{linebreak} SQLite v3.4.2 CREATE TABLE t1(x,y); CREATE TABLE t2(z); INSERT INTO t1 VALUES(1,21); INSERT INTO t1 VALUES(2,22); INSERT INTO t1 VALUES(3,23); INSERT INTO t2 VALUES(3); INSERT INTO t2 VALUES(2); INSERT INTO t2 VALUES(1); What you wanted to do: SELECT y FROM t1 WHERE x=(SELECT min(z) FROM t2); 21 -- works as expected What you did: SELECT (SELECT y FROM t1 WHERE x=min(z)) FROM t2; SQL error near line []: misuse of aggregate function min() #f2dcdc 2664 active 2007 Sep danielk1977 Pending 1 1 attaching the same db twice in shared-cache mode fails The following SQL script can cause an assert() to fail in shared-cache mode. ATTACH 'db' AS aux1; ATTACH 'db' AS aux2; CREATE TABLE aux1.abc(a, b, c); CREATE TABLE aux2.abc(a, b, c); See also #2653 #f2dcdc 2684 active 2007 Oct anonymous Pending 1 1 Accessing sqlite from an NT service will lock the complete databse. Accessing sqlite from a NT service (application 1) will lock the complete database. Any other process trying to open an sqlite db (application 2) will get error "80004005 unable to lock database" If application 1 runs as normal application, started by local user, this problem doesnt occur and both applications can open the db. _2007-Oct-02 15:48:05 by anonymous:_ {linebreak} SQLite has no knowledge of Windows services. How do you propose to work around this Windows anachronism? ---- _2007-Oct-02 17:20:38 by anonymous:_ {linebreak} Suggesion: Try running the service in the same account as the other program that needs to access the database. Anachronism? Service is just another word for daemon. -knu- ---- _2007-Oct-02 17:33:56 by anonymous:_ {linebreak} Re: Anachronism, the OP suggested there was something fundamentally different about file access using a service. You've pointed out that it's just a file permissions issue. ---- _2007-Oct-05 14:45:07 by drh:_ {linebreak} Two points: 1: The error message "80004005 unable to lock database" is not generated by SQLite. There must be some middleware someplace that is producing this message. The problem might be in that middleware and not in SQLite. 2: None of the SQLite developers run windows. Consequently any fixes for this problem will need to come from the community. Please append patches to this ticket if you find a fix. Or close the ticket if you discover that the problem is outside of SQLite. #e8e8bd 2708 active 2007 Oct anonymous Pending 4 2 SQL error:disk I/O error I cross-compile sqlite to embedded Linux,but after I insert data to the table ,it failed.the warning is "SQL error:disk I/O error". _2007-Oct-09 05:12:28 by anonymous:_ Why do you think it is SQLite error ?? ---- _2007-Oct-09 05:46:06 by danielk1977:_ {linebreak} We'll need a bit more data than that to figure this out. Did earlier SQLite versions work? Can you post the entire output of the compile process so that we can see if there are any clues there? Can you run strace so that we can see if there really is an IO error, or at least when SQLite believes there to be one? #f2dcdc 2715 active 2007 Oct anonymous Pending 1 1 no authorization needed to remove authorizer there should be a new auth code created and the auth function should be consulted for permission for removal. _2007-Oct-10 01:08:48 by drh:_ {linebreak} I'm assuming that this feature request comes from {quote: RockShox} and that the development language is Tcl. No. If your adversary has the ability to invoke the interface that removes an authorizer, then you system is already pwned. What you really need is the ability to [interp alias] the eval method into a safe interpreter. That way you can: *: Open the database in the main interpreter *: Set up the authorizer in the main interpreter to invoke a script in the main interpreter *: Set up the [interp alias] so that the safe interpreter can do [db eval ...] but not [db auth ...] It seems like an "-interp" option on the "eval" method of the database connection object would likely be the right interface. Or perhaps there should be separate "safeeval" method. Either way, it has been years and years since I have done anything with safe interpreters so I will have to look into what needs to be done to make that happen. ---- _2007-Oct-17 20:11:23 by anonymous:_ {linebreak} ok i think i agree with that. currently you cannot use an interp alias since the target command runs in the target interp and all your variables and commands are in the wrong scope. this means one needs to load sqlite again in the new interp, and sqlite will not load in a safe interp so a regular interp is required. to be useful, a -interp flag would need to execute in the current scope of the interp and not the global scope. #f2dcdc 2721 active 2007 Oct anonymous Pending 2 1 if db file is in a folder with non-ansi character some functions fail If database file is located in directory with some non-ANSI characters (in my case with a Russian subdirectory c:\Мои документы\Data_Jobs), or it's name is non-ansi. Some functions fail to execute sql. For example (with defined UNICODE):
TCHAR sql[512]; _stprintf(sql, _T("INSERT INTO tab_SurveyedPoints (name, comment, code,") _T("coordinatetype, b, l, h, solutiontype, sigmah, sigmav)") _T(" VALUES ('%s','%s','%s',0,%lf,%lf,%lf,0,%lf,%lf);"), point.m_name.c_str(), point.m_description.c_str(), point.m_code.c_str(), point.m_coordinates.b, point.m_coordinates.l, point.m_coordinates.h, point.m_sigmah, point.m_sigmav); int rc1 = sqlite3_prepare16(m_db, sqlfmt, -1, &stmt, (const void**)&pszTail); rc != SQLITE_OKBut if I move the file to c:\My documents\Data_Jobs this works ok. It's improbable behaviour, but I can't work around yet. Although, prepare() functions work ok as well in both cases. Yuri Noyanov. _2007-Oct-11 19:33:34 by drh:_ {linebreak} All string arguments to SQLite, and especially filename arguments, must be UTF-8 or UTF-16 (depending on the function). If you use string parameters which are not UTF-8 or UTF-16 (as appropriate) then the behavior of SQLite is undefined and probably not what you want. ---- _2007-Oct-12 04:25:56 by anonymous:_ {linebreak} but ALL programs to handle SQLite DBs (SQLIteBrowser, SQLite Control) fail to handle the files as well. Till I move the file to different directory !!! ---- _2007-Oct-12 04:27:54 by anonymous:_ {linebreak} Also I must note, that I CAN open the database, I CAN execute some SQLs with sqlite_prepare function OK. But sqlite_prepare16 FAILS if I just rename my database !!! ---- _2007-Oct-12 04:31:46 by anonymous:_ {linebreak} Also note to make my issue clearer: sqlite_prepare16() with the same code either works OK either doesn't work. depends on database filename or folder path. The database is opened OK in both cases (I used utf8 conversion). sql_prepare() works ok in both cases. ---- _2007-Oct-13 06:37:43 by anonymous:_ {linebreak} That appears to be only with INSERT sql statement. Both SELECT and UPDATE work fine with sqlite_prepare16. #f2dcdc 2725 active 2007 Oct anonymous Pending 1 1 memory leak in sqlite3_open_v2() when it fails only happens with flags = SQLITE_OPEN_READWRITE; and when res = sqlite3_open_v2(sourcename, &conn, flags, NULL); seems to leak 674 bytes per call _2007-Oct-15 07:07:07 by danielk1977:_ {linebreak} Are you calling sqlite3_close(conn) after the error occurs? All calls to sqlite3_open_v2() need to be matched by a call to sqlite3_close(), even if an error occurs. #cfe8bd 2753 active 2007 Nov anonymous New drh 3 3 Master journal files sometimes not deleted In the 3.4.1 amalgamation, in vdbeCommit, the master journal file is created, and deleted at the end or if there is an error. But it looks like there is one case where it gets closed but not deleted. The code is: for(i=0; rc==SQLITE_OK && i
+#ifndef SQLITE_DONT_DEFINE_XOPEN_SOURCE #if !defined(_XOPEN_SOURCE) && !defined(__DARWIN__) && SQLITE_THREADSAFE # define _XOPEN_SOURCE 500 /* Needed to enable pthread recursive mutexes */ #endif +#endif_2007-Dec-01 09:23:15 by anonymous:_ {linebreak} Also when using Python, it sets _XOPEN_SOURCE to 600. No idea what the 500 vs 600 difference is about. ---- _2007-Dec-01 15:58:28 by anonymous:_ {linebreak} I've used a couple of different Linux OSes and _XOPEN_SOURCE is not needed. Maybe it's for OSes more than 5 years old. Recursive mutexes are pretty much standard these days since the popularity of Java which uses them extensively. ---- _2007-Dec-01 17:21:05 by drh:_ {linebreak} See also tickets #2673, #2681, and #2741. ---- _2007-Dec-02 02:08:26 by anonymous:_ {linebreak} On Linux, PTHREAD_MUTEX_RECURSIVE is the same as PTHREAD_MUTEX_RECURSIVE_NP: PTHREAD_MUTEX_RECURSIVE = PTHREAD_MUTEX_RECURSIVE_NP, Since PTHREAD_MUTEX_RECURSIVE_NP is always available, you could avoid defining _XOPEN_SOURCE and use this code instead:
- pthread_mutexattr_settype(&recursiveAttr, PTHREAD_MUTEX_RECURSIVE); + pthread_mutexattr_settype(&recursiveAttr, +#ifdef linux + PTHREAD_MUTEX_RECURSIVE_NP +#else + PTHREAD_MUTEX_RECURSIVE +#endif + );---- _2007-Dec-02 02:17:22 by anonymous:_ {linebreak} A quick google search reveals how various projects deal with this recursive mutex declaration problem (in no particular order): *: #define _XOPEN_SOURCE 500 and use PTHREAD_MUTEX_RECURSIVE *: #define _XOPEN_SOURCE 600 and use PTHREAD_MUTEX_RECURSIVE *: #define _GNU_SOURCE and use PTHREAD_MUTEX_RECURSIVE *: don't define anything and use PTHREAD_MUTEX_RECURSIVE_NP on linux, and PTHREAD_MUTEX_RECURSIVE elsewhere. Unfortunately, since PTHREAD_MUTEX_RECURSIVE is an enum on Linux, so you can't use the #ifdef PTHREAD_MUTEX_RECURSIVE compile-time technique. #cfe8bd 2340 active 2007 May anonymous Pending 3 3 "./configure && make testfixture" link problem Until the (extremely welcome) sqlite3 blob I/O functionality was checked in, it was possible to run this command on a clean source tree: ./configure && make testfixture But it now produces these unresolved externals: ./src/tclsqlite.c:297: warning: excess elements in struct initializer ./src/tclsqlite.c:297: warning: (near initialization for `IncrblobChannelType') /tmp/ccmyzR97.o: In function `test_blob_read': /sqlite/cvs/sqlite/./src/test1.c:1545: undefined reference to `_sqlite3_blob_read' /tmp/ccmyzR97.o: In function `test_blob_write': /sqlite/cvs/sqlite/./src/test1.c:1592: undefined reference to `_sqlite3_blob_write' /tmp/ccb5dcDK.o: In function `incrblobClose': /sqlite/cvs/sqlite/./src/tclsqlite.c:156: undefined reference to `_sqlite3_blob_close' /tmp/ccb5dcDK.o: In function `incrblobInput': /sqlite/cvs/sqlite/./src/tclsqlite.c:194: undefined reference to `_sqlite3_blob_bytes' /sqlite/cvs/sqlite/./src/tclsqlite.c:202: undefined reference to `_sqlite3_blob_read' /tmp/ccb5dcDK.o: In function `incrblobOutput': /sqlite/cvs/sqlite/./src/tclsqlite.c:226: undefined reference to `_sqlite3_blob_bytes' /sqlite/cvs/sqlite/./src/tclsqlite.c:235: undefined reference to `_sqlite3_blob_write' /tmp/ccb5dcDK.o: In function `incrblobSeek': /sqlite/cvs/sqlite/./src/tclsqlite.c:264: undefined reference to `_sqlite3_blob_bytes' /tmp/ccb5dcDK.o: In function `DbObjCmd': /sqlite/cvs/sqlite/./src/tclsqlite.c:322: undefined reference to `_sqlite3_blob_open' collect2: ld returned 1 exit status make: *** [testfixture.exe] Error 1 The 2 warnings related to src/tclsqlite.c:297 also seem to indicate a problem. I'm using Tcl 8.4. _2007-May-06 21:16:01 by anonymous:_ {linebreak} Workaround - hack the generated Makefile to use: # Compiler options needed for programs that use the TCL library. # TCC += -I/usr/local/include -DSQLITE_OMIT_INCRBLOB=1 #f2dcdc 2350 active 2007 May anonymous Pending 1 1 Temp files not deleted on WinCE When using SQLite 3.3.17 on WinCE device temp files end up being accumulated in Temp directory, eventually filling up device's open file quota (999?) and making further operation that requires creation of files impossible. I have noticed that after a while all queries start failing on the device although if I copied the database to PC and tried the same query I had no problems with it. Started tracing through the code and noticed that =sqlite3WinOpenExclusive()= is consistently failing to create a file (=CreateFileW()= would always fail). It turned out that Temp directory was full of 0 length temp files -- after deleting all of them device started working again for a while. Then started looking for the cause -- =winceDestroyLock()= never gets to execute =DeleteFileW()= and delete the file even though =pFile->zDeleteOnClose= is set because check on the top of the =winceDestroyLock()= function (=if (pFile->hMutex)=) always fails thanks to =pFile->hMutex= being 0. =sqlite3WinOpenReadWrite()= calls =winceCreateLock()= on temporary file (=zFilename=) but =sqlite3WinOpenExclusive()= doesn't hence =winceDestroyLock()= cannot delete temporary file. =sqlite3PagerClose()= contains this piece of commented out code: /* Temp files are automatically deleted by the OS ** if( pPager->tempFile ){ ** sqlite3OsDelete(pPager->zFilename); ** } */ Comment is not true for WinCE as WinCE doesn't support =FILE_ATTRIBUTE_TEMPORARY= and =FILE_FLAG_DELETE_ON_CLOSE= flags. Perhaps it should be surrounded with =#if OS_WINCE/#endif= instead of being commented out? ---- _2007-May-14 00:09:20 by anonymous:_ {linebreak} Tried re-enabling that piece of code in =sqlite3PagerClose()= and it doesn't help. Looks like even file handles for temporary files never get closed on WinCE so not only number of temporary file in =Temp= directory just keeps growing but also filed descriptor for each of them remains open, draining system resources (consistent with observed behavior of rapid and nasty system performance deterioration). It appears that =sqlite3VdbeFreeCursor()= always exits immediately because =pCx= is always =NULL= and thus =sqlite3BtreeClose()= never gets called: void sqlite3VdbeFreeCursor(Vdbe *p, Cursor *pCx){ if( pCx==0 ){ return; } if( pCx->pCursor ){ sqlite3BtreeCloseCursor(pCx->pCursor); } if( pCx->pBt ){ sqlite3BtreeClose(pCx->pBt); } =sqlite3BtreeClose()= then calls =sqlite3PagerClose()= which probably explains why re-enabling that code doesn't help -- =sqlite3PagerClose()= itself never gets called. ---- _2007-May-14 05:16:59 by anonymous:_ {linebreak} Did some comparison between operation of the SQLite on WinXP and WinCE. Comment in previous remark about =sqlite3VdbeFreeCursor()= always exiting immediately because =pCx= is always =NULL= is not correct. =pCX= is =NULL= most of the time but this is true for both WinXP and WinCE. WinXP has no problems getting rid of its temp files thanks to its support for =FILE_ATTRIBUTE_TEMPORARY= and =FILE_FLAG_DELETE_ON_CLOSE= flags. WinCE also does get its temp file descriptors closed (previous statement incorrect on that account too), it's just that its temp files do not get cleaned up automatically. Initial statement that this was caused because of =winceDestroyLock()= actually is correct. Temp files are opened with =sqlite3WinOpenExclusive()= which DOESN'T use mutex (instead of with =sqlite3WinOpenReadWrite()= which DOES use mutex) so part of =winClose()= that was supposed to take care of deleting files after handle is closed (i.e. calling =winceDestroyLock()=) doesn't work: static int winClose(OsFile **pId){ winFile *pFile; int rc = 1; if( pId && (pFile = (winFile*)*pId)!=0 ){ int rc, cnt = 0; OSTRACE2("CLOSE %d\n", pFile->h); do{ rc = CloseHandle(pFile->h); }while( rc==0 && cnt++ < MX_CLOSE_ATTEMPT && (Sleep(100), 1) ); #if OS_WINCE winceDestroyLock(pFile); #endif =OS_WINCE= part should go something like this (although probably not exactly like this as this feels like an ugly hack): #if OS_WINCE if (pFile->hMutex){ winceDestroyLock(pFile); }else{ if( pFile->zDeleteOnClose ){ DeleteFileW(pFile->zDeleteOnClose); sqliteFree(pFile->zDeleteOnClose); pFile->zDeleteOnClose = 0; } } #endif ---- _2007-May-14 05:20:33 by anonymous:_ {linebreak} Tried the code from the previous remark and it does clean up =Temp= directory! I also tried uncommenting that piece of code in =sqlite3PagerClose()= mentioned in the first remark and it looks like that temp files it tries to delete are never there. I'm guessing that that piece of code can remain commented out. Will keep an eye on it -- in case temp files still eventually start accumulating again even after the fix from previous remark is applied I'll reconsider. #cfe8bd 2352 active 2007 May anonymous Pending 5 3 timeout just 500 msec to soon After upgrading from 3.3.12 to 3.3.17, the setting of a timeout behaves differently. It occurs exactly 500 msecs sooner. Of course assuming the database is locked and the timeout is set to a value larger than 500 msecs. #cfe8bd 2363 active 2007 May anonymous Pending 3 3 Couldn't build 3.3.17 on Cygwin/Vista I didn't see anything quite resembling this on a cursory scan thru the buglist; pardon my goof if it's a duplicate. When I tried to build on Cygwin on Vista, things got to the install phase, and then the Makefile tried to execute cc sqlite3.c -o sqlite3 which didn't appear to be a defined rule in the Makefile. This then failed because of a lack of a main program, and the install failed. After a quick scan through the Makefile, I decided to change the target for the install rule to install: sqlite3$(TEXE) libsqlite3.la sqlite3.h ${HAVE_TCL:1=tcl_install} which seemed to remedy the problem. Suggested fix: modify Makefile as shown above. _2007-May-23 14:29:20 by anonymous:_ {linebreak} Another vote for this #e8e8bd 2371 active 2007 May anonymous Pending 2 2 sqlite3_errcode() and sqlite3_errmsg() return unexpected results The manual says that sqlite3_step() directly returns explicit error code if the query has been prepared with the new API sqlite3_prepare_v2(). Subsequently, the functions sqlite3_errcode() and sqlite3_errmsg() should return the correct appropriate error values as well, which they don't - instead something not matching the error is returned. One has to call sqlite3_reset() to get the correct values which should be unnecessary. See the example output below : sqlite3_step: result rc = 19, errcode = 1, errmsg = SQL logic error or missing database sqlite3_reset: result rc = 19, errcode = 19, errmsg = PRIMARY KEY must be unique Code for this example: #include
got = write(id->h, pBuf, cnt);to do things like:
#ifdef SQLITE3_RETRY_EINTR_SYSCALLS do { #endif // SQLITE3_RETRY_EINTR_SYSCALLS got = write(id->h, pBuf, cnt); #ifdef SQLITE3_RETRY_EINTR_SYSCALLS } while ( (got < 0) && (errno == EINTR) ); #endif // SQLITE3_RETRY_EINTR_SYSCALLSby leaving SQLITE3_RETRY_EINTR_SYSCALLS undefined, this should not affect the current code behaviour and should address the issues ticket author says and he can compile with those 'paranoic' checks. []'s ---- _2007-Jun-05 22:25:06 by anonymous:_ {linebreak} I did pass my name (visible for the developers), but I'm not logged in. That why it's shown as anonymous (just as you are). Note that my co-workers have already decided it would be seen up as a negative point for this DBMS. At least for us, because it's a requirement for us to survive these errors. So if we ever are going to use SQLite, we're definitely going to use those changes. Hey, I'm a code-review nazi, don't blame me. It's just my job :-) ---- _2007-Jun-06 00:58:16 by anonymous:_ {linebreak} I think that the retrying would destroy the function expected of =sqlite3_interrupt=. Is there a problem in the following methods? *: Re-execute the SQLite API. *: Mask the signals while calling the API of SQLite. *: Mask the signals in the threads that call the API of SQLite. ---- _2007-Jun-06 01:04:47 by anonymous:_ {linebreak} As long as #ifdef SQLITE3_RETRY_EINTR_SYSCALLS is not enabled by default, knock yourselves out. ---- _2007-Jun-06 06:38:51 by anonymous:_ {linebreak} sqlite3_interrupt() knows nothing about UNIX signals. It just sets a flag that sqlite's VDBE checks from time to time to see if it should gracefully abort an SQL operation. shell.c happens to install an SIGINT signal handler that happens to call sqlite3_interrupt(), but that's unrelated. sqlite3_interrupt() could be called via any other means. ---- _2007-Jun-06 10:47:27 by anonymous:_ {linebreak} See also the SA_RESTART flag for sigaction(), which automatically will restart the systemcall instead of returning EINTR. Unfortunately not available everywhere, and not very consistent either : see Linus reply at http://lkml.org/lkml/2005/7/23/119 . Applications should still be able to handle EINTR returns gracefully. They're not errors per se (that would be EIO or similar), you can safely restart the system call. I haven't seen endless loops yet, but it's true that on some OS you might want this solution. But you still have to deal with the error (which might cause a rollback in SQLite). ---- _2007-Jun-06 14:51:14 by anonymous:_ {linebreak} I would be in favor of optionally compiling SQLite with a #define SQLITE_USE_SA_RESTART _or_ #define SQLITE_RETRY_ON_EINTR, where they are mutually exclusive and disabled by default. SQLITE_RETRY_ON_EINTR should have a maximum retry limit on buggy OSes, though. #f2dcdc 2408 active 2007 Jun anonymous Pending 2 1 BLOBs not output correctly in .mode insert (shell.c - isnumber) The method {linebreak} static int isNumber(const char *z, int *realnum) {linebreak} {linebreak} from shell.c is wrong. {linebreak} Steps to reproduce: {linebreak} 1. Get the file www.smatei.3x.ro/project1.zip {linebreak} 2. extract project1.db from the zip file {linebreak} 3. execute {linebreak} sqlite3 project1.db {linebreak} sqlite>.mode insert {linebreak} sqlite> select ID, Name, Color, Active, Priority, PrioritySource, IndexOrder, Language, 0 from Keywords; {linebreak} INSERT INTO Keywords VALUES(42,#####,0,1,0,0,42,'',0); {linebreak} INSERT INTO Keywords VALUES(41,'######',0,1,0,0,43,'',0);{linebreak} If you look at the 42 item, the string next to 42 is not enclosed by the string delimiter '. {linebreak} This is because the method isnumber returns that the string is number. It is not a number, it is a string in Hebrew (I inserted ### instead of the real strings). {linebreak} A fix for this might be to set the first parameter unsigned char {linebreak} static int isNumber(unsigned const char *z, int *realnum) {linebreak} but I am not sure, because I haven't written C code for a long time. {linebreak} If you have any more questions, please ask. {linebreak} Best Regards, {linebreak} Stefan _2007-Jun-11 14:46:03 by drh:_ {linebreak} Please show me what you get from the following query: SELECT ID, quote(cast(Name AS BLOB)) FROM Keywords WHERE ID IN (41,42); I need this information in order to track down the problem. ---- _2007-Jun-11 14:50:33 by anonymous:_ {linebreak} The output is 41|X'D791D7A8D799D799D7A7D793D790D7A0D7A1'{linebreak} 42|X'D7A1D798D7A4D7A1' ---- _2007-Jun-11 16:29:46 by drh:_ {linebreak} When I do this: CREATE TABLE t1(x); INSERT INTO t1 VALUES(cast(X'D791D7A8D799D799D7A7D793D790D7A0D7A1' as text)); INSERT INTO t1 VALUES(cast(X'D7A1D798D7A4D7A1' AS text)); .mode insert xyz SELECT * FROM t1; I see the Hebrew characters, properly quoted. Can you suggest another way to reproduce the problem? ---- _2007-Jun-11 16:34:55 by anonymous:_ SQLite version 3.3.17 Enter ".help" for instructions sqlite> create table t1(b blob); sqlite> insert into t1 values(X'0102030405060708090a0dABCD'); sqlite> .dump BEGIN TRANSACTION; CREATE TABLE t1(b blob); INSERT INTO "t1" VALUES(X'0102030405060708090A0DABCD'); COMMIT; sqlite> .mode insert sqlite> select * from t1; INSERT INTO table VALUES(' «Í'); sqlite> ---- _2007-Jun-11 20:07:09 by anonymous:_ {linebreak} If I recall correctly, sqlite3.exe assumes all I/O to and from the console is UTF-8. Windows consoles are either MBCS or UNICODE depending on the build settings. Sqlite3.exe was not compiled as UNICODE so anything inserted into a sqlite3 database will be inserted as MBCS. So if you insert into a database using a 3rd party application that does proper UTF-8, and then query it from the command-line, it will look wrong. Likewise, anything you insert from the command-line will look wrong when queried from an application that uses UTF-8. ---- _2007-Jun-11 20:42:18 by anonymous:_ {linebreak} .mode insert treats BLOBs as strings, which is a problem since it stops outputting at the first nil character. CREATE TABLE t1(a blob); INSERT INTO t1 VALUES(X'00000008090A0D0D0A00'); .mode insert whatever select * from t1; ---- _2007-Jun-12 07:39:12 by anonymous:_ {linebreak} Hi, My problem is not the way the string is displayed in the console. The application that reads the database reads it correctly. The problem is when I try to dump a certain table in a file using the following sequence: .output "file.txt"{linebreak} .mode insert whatever{linebreak} select f1, f2, f3 from t1;{linebreak} The result is a file that some Hebrew strings are not enclosed in ' (apostrophe). After debugging the code, I saw that the method isnumber does not return 0 for that string. It considers all the characters digits. This problem occurs only on Windows. We tried this on Mac and Linux, and it worked fine. Best Regards,{linebreak} Stefan{linebreak} #f2dcdc 2409 active 2007 Jun anonymous New drh 1 1 Database malformed with SQLite3.3.17 on WindowsXP I encountered a problem with SQLite3.3.17 on Windows XP. Under certain situation, database file got seriously corrupted. SQLite version: 3.3.17 Windows Binary Platform:Windows XP SP2(Japanese) Code wrtten in: Visual C++ 6.0 Here are the procedures to reproduce the problem: 1) Run a program SQLiteCrush.exe. This program updates 'test.db' repeatedly. Insert data to work table, copy them into items table, then delete records from work. 2) Open 'test.db' from sqlite3.exe. 3) Do '.read check.sql' repeatedly. check.sql is made from many lines of 'pragma integrity_check;'. 4) Keep doing 1 -3 for several minuites, and 'pragma integrity_check' starts to report something like "rowid 91667 missing from index sqlite_autoindex_link_1". So far, I didn't see the database corrupted with SQLite 3.3.7. Also, without 3), the database was not corrupted. Instead of 'pragma integrity_check', issueing many select statements also make it currupted. _2007-Jun-12 02:51:26 by anonymous:_ {linebreak} I did more tests to make it clear from which version it happens. With 3.3.8, I couldn't reproduce the problem. With 3.3.9, I can reproduce the problem. It seems there's some change between these two that causes the problem... ---- _2007-Jun-12 03:06:47 by anonymous:_ {linebreak} Can you try it against the latest version in CVS, or 3.3.17? A lot of code has changed since 3.3.9. ---- _2007-Jun-12 05:00:50 by anonymous:_ {linebreak} I Already tried 3.3.17. It happenes. Where can I get the latest CVS precompiled binary for windows platform ? tamagawa ---- _2007-Jun-13 14:25:47 by drh:_ {linebreak} The problem was introduced in SQLite version 3.3.0, specifically check-in [2848]. There are related problems that go back even further in time, we believe. The root of the problem is a logic error in my design of the pager layer. We are working on a fix now, as well as a set of test cases that will ensure that similar errors do not reappear in the future. I will also soon publish instructions on how to work around the problem in effected versions of SQLite. The problem can be easily reproduced by running the script below using the "testfixture" program that we use for testing SQLite.
#f2dcdc 2413 active 2007 Jun anonymous Pending drh 1 1 1 bug and 2 suggestions in lemon Hello, ... {linebreak} Sorry for my english :-) and if i post this with Severity/Priority error. {linebreak} I found some not serious bug and have some suggetions. {linebreak} ============================================================================={linebreak} BUG FIX: {linebreak} lemon.c for Win32. It not found lempar.c - backslash-bug. {linebreak} function: {linebreak} PRIVATE char *pathsearch(argv0,name,modemask); {linebreak} PATCH: {linebreak} ---- CUT --------------------------------------------------------------------{linebreak} --- C:/lemon.c Wed Jun 13 15:02:37 2007 {linebreak} +++ D:/Den/Lemon/lemon.c Wed Jun 13 16:25:22 2007 {linebreak} @@ -2911,7 +2911,11 @@ {linebreak} c = *cp; {linebreak} *cp = 0; {linebreak} path = (char *)malloc( strlen(argv0) + strlen(name) + 2 ); {linebreak} - if( path ) sprintf(path,"%s/%s",argv0,name); {linebreak} + #ifdef __WIN32__ {linebreak} + if( path ) sprintf(path,"%s\\%s",argv0,name); {linebreak} + #else {linebreak} + if( path ) sprintf(path,"%s/%s",argv0,name); {linebreak} + #endif {linebreak} *cp = c; {linebreak} }else{ {linebreak} extern char *getenv(); {linebreak} @@ -2920,11 +2924,19 @@ {linebreak} path = (char *)malloc( strlen(pathlist)+strlen(name)+2 ); {linebreak} if( path!=0 ){ {linebreak} while( *pathlist ){ {linebreak} - cp = strchr(pathlist,':'); {linebreak} + #ifdef __WIN32__ {linebreak} + cp = strchr(pathlist,';'); {linebreak} + #else {linebreak} + cp = strchr(pathlist,':'); {linebreak} + #endif {linebreak} if( cp==0 ) cp = &pathlist[strlen(pathlist)]; {linebreak} c = *cp; {linebreak} *cp = 0; {linebreak} - sprintf(path,"%s/%s",pathlist,name); {linebreak} + #ifdef __WIN32__ {linebreak} + sprintf(path,"%s\\%s",pathlist,name); {linebreak} + #else {linebreak} + sprintf(path,"%s/%s",pathlist,name); {linebreak} + #endif {linebreak} *cp = c; {linebreak} if( c==0 ) pathlist = ""; {linebreak} else pathlist = &cp[1]; {linebreak} ---- CUT --------------------------------------------------------------------{linebreak} ============================================================================= {linebreak} SUGGESTION 1: {linebreak} Why we allocate parser with mallocProc parameter of ParseAlloc function {linebreak} and free with freeProc of ParseFree function? {linebreak} We do this because we want what parser is user-allocatable {linebreak} with USER-DEFINED-MEMORY-ALOCATION-WAY but not with "malloc"/"free" from stdlib... am i right? {linebreak} If so... why we still allocate memory for parser stack with "realloc" function? {linebreak} It's bad for solutions where is no stdlib. {linebreak} My suggestion is {linebreak} FIRST WAY: {linebreak} To add to yyParser struct 3 variables like {linebreak} void *mem_alloc_fn; {linebreak} void *mem_realloc_fn; {linebreak} void *mem_free_fn; {linebreak} and add 3 directives like {linebreak} %memory_alloc {linebreak} %memory_realloc {linebreak} %memory_free {linebreak} and if it declared - use it for allocating/free/reallocating memory in parser. {linebreak} and {linebreak} - void *ParseAlloc(void *(*mallocProc)(size_t)); {linebreak} will now as void *ParseAlloc(); {linebreak} - void ParseFree(void *pParser, void (*freeProc)(void*)); {linebreak} will now as void ParseFree(void *pParser); {linebreak} OR SECOND WAY (very simple): {linebreak} To add to yyParser struct 1 variable like {linebreak} void *mem_realloc_fn; {linebreak} - void *ParseAlloc(void *(*mallocProc)(size_t)); {linebreak} will now as void *ParseAlloc(void *(*mallocProc)(size_t), void *(*reallocProc)(void *, size_t)); {linebreak} store reallocProc in mem_realloc_fn in yyParser {linebreak} and in yyGrowStack something like this: {linebreak} ... yyGrowStack (...) {linebreak} { {linebreak} .... {linebreak} if(pParser->mem_realloc_fn != NULL) {linebreak} { {linebreak} pNew = pParser->mem_realloc_fn(p->yystack, newSize*sizeof(pNew[0])); {linebreak} } {linebreak} else {linebreak} { {linebreak} pNew = realloc(p->yystack, newSize*sizeof(pNew[0])); {linebreak} } {linebreak} .... {linebreak} } {linebreak} and use it for reallocating memory in parser. {linebreak} In this ways - memory allocating in parser is under FULL user control. {linebreak} ============================================================================= {linebreak} SUGGESTION 2: {linebreak} I build lemon with VC 8.0 with option /Wp64 (Detect 64-Bit Portability Issues) {linebreak} and have warnings. Type int, size_t, pointer and unsigned long have diferent size on x32 and x64 platforms. {linebreak} Can you fix type difference, please? {linebreak} Only you can choice better way for this - type conversion OR change type of 'warning' variables. {linebreak} WARNINGS: {linebreak} d:\den\lemon\lemon.c(1331) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(1337) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(1455) : warning C4113: 'int (__cdecl *)()' differs in parameter lists from 'int (__cdecl *)(const void *,const void *)' {linebreak} d:\den\lemon\lemon.c(1578) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1578) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1581) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1581) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1586) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1586) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1588) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1588) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1590) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1590) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1592) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1592) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1595) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1595) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1596) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1596) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1624) : warning C4311: 'type cast' : pointer truncation from 'char **' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1624) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1628) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1628) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1629) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1629) : warning C4312: 'type cast' : conversion from 'unsigned long' to 'char **' of greater size {linebreak} d:\den\lemon\lemon.c(1658) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(1661) : warning C4267: '+=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(1774) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1774) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1785) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1785) : warning C4311: 'type cast' : pointer truncation from 'char *' to 'unsigned long' {linebreak} d:\den\lemon\lemon.c(1883) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(2722) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(3171) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(3173) : warning C4018: '>=' : signed/unsigned mismatch {linebreak} d:\den\lemon\lemon.c(3184) : warning C4267: '+=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(3340) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(3346) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} d:\den\lemon\lemon.c(3542) : warning C4267: '=' : conversion from 'size_t' to 'int', possible loss of data {linebreak} Ups ... drh, sorry - title change. #f2dcdc 2414 active 2007 Jun anonymous Unable_to_fix 1 1 Unable t I designed a tool in C# using the Sqlite.Net.dll and sqlite3.dll v 3.2.5. The call to sqlite3_step after the sqlite3_prepare function, causes a huge delay to return (~7MINS), sometimes it doesn't return at all. This happens when I'm using v3.2.5 but when I replace it with v 3.3.7, everything works normal. I have tried so many combination of things to get it to work on v3.2.5 (this is the version of the libraries the hardware uses), which includes setting the PRAGMA legacy_file_format to 1, but after every save it reverts back to 0. i will appreciate if I can get a response with any suggestions. Thanks in advance. _2007-Jun-13 15:58:00 by anonymous:_ {linebreak} Please post the schema and the SELECT command that is slow under 3.2.5 and is fast under 3.3.7 so it can be reproduced using the sqlite3 commandline shell. #e8e8bd 2440 active 2007 Jun rse Pending 2 2 pkg-config(1) script "sqlite.pc" does not provide Autoconf's LIBS On some platforms it isn't sufficient to link a library just against "-lsqlite". For instance under Solaris one needs "-lsqlite -lrt" because of the use of "fdatasync()". The SQLite Autoconf glue already contains the necessary check for this in order to correctly build SQLite and especially link its sqlite(1). But this information is not passed through to the applications which use pkg-config(1) to build against SQLite. Possible fix from OpenPKG's "sqlite" package is following:# Prepare the database: # file delete -force test.db test.db-journal sqlite3 db test.db db eval { CREATE TABLE t1 ( x TEXT UNIQUE NOT NULL, y BLOB ); } # Open a second connection to the database that will be # used to lock the database file. # set DB2 [sqlite3 db2 test.db] if {$DB2==""} { set DB2 [sqlite3_connection_pointer db2] } # A small cache will cause an early cache spill. # db eval {PRAGMA cache_size=10} # Acquire read lock on the database file using the second connection. # set STMT [sqlite3_prepare $DB2 {SELECT rowid FROM sqlite_master} -1 TAIL] sqlite3_step $STMT # Insert a short record into the index (10 bytes) and a large record # into the table (15K). The index record goes in Ok, but during the # insert into the table, SQLite attempts to upgrade to an EXCLUSIVE # lock to do a cache flush. When this happens, the cache is left in # an inconsistent state. # set zShort [string repeat 0123456789 1] set zLong [string repeat 0123456789 1500] db eval {BEGIN} set rc [catch { db eval {INSERT INTO t1 VALUES($zShort, $zLong)} } msg] puts "rc=$rc msg=$msg" sqlite3_finalize $STMT db eval {COMMIT} db close sqlite3 db test.db puts [db eval {PRAGMA integrity_check}]
Index: sqlite3.pc.in --- sqlite3.pc.in.orig 2004-07-19 06:25:47 +0200 +++ sqlite3.pc.in 2007-06-20 18:09:00 +0200 @@ -8,5 +8,5 @@ Name: SQLite^M Description: SQL database engine^M Version: @VERSION@^M -Libs: -L${libdir} -lsqlite3^M +Libs: -L${libdir} -lsqlite3 @LIBS@^M Cflags: -I${includedir}^M#f2dcdc 2479 active 2007 Jun anonymous Pending 1 1 WinCE regression on some systems. Any db open fails. Because Windows CE is a modular system, meaning many parts of it can be optionally ommited by the system builder, some don't include the CP_UTF8 conversion algorithms for MultiByteToWideChar and family. I believe Windows 95 and early 98 systems can also lack this encoding if not updated with a later Internet Explorer version. Solution is to just use the sqlite internal functions that already know how to do the same thing. Attached is an untested patch to os_win.c (I don't have a windows machine nor a cross-compiler set up) to show where the problem is and a possible (sub-optimal) solution. I believe the right thing to do would be to just drop the utf8ToUnicode and unicodeToUtf8 functions, add the sqlite3Utf8to16 equivalent to utf.c and use them instead. ~Nuno Lucas _2007-Jun-29 14:54:11 by anonymous:_ {linebreak} The title is wrong. It should say "Any db open using the UTF-8 API", as using the open16 API will work. #f2dcdc 2487 active 2007 Jul anonymous Pending 1 1 SQLite database locked error on NFS mounted home dir I have a c program using the provided API. My home directory is NFS mounted, Im using SQLite 3.3.17. I open a new database using "sqlite3_open", then strcpy () a SQL command to create a table, and run "sqlite3_exec" with this string. I get a return code of 5=database locked. I then tried to manually (command line using sqlite3) create a table within a database in my home dir, that fails too. =========== x@y> sqlite3 db2 SQLite version 3.3.17 Enter ".help" for instructions sqlite> create table test (Lastname varchar); SQL error: database is locked sqlite> ============== If I try this on my local machine (a Mac), it works fine, but I need it to work in my home directory mounted via NFS as that is where the output of our program goes _2007-Jul-06 19:04:15 by anonymous:_ {linebreak} If you're using a Mac, compile sqlite with SQLITE_ENABLE_LOCKING_STYLE in os_unix.c ---- _2007-Jul-07 11:51:10 by drh:_ {linebreak} This is a problem with your NFS implementation - it does not appear to support posix advisory locking. There is nothing much that SQLite can do about this. Anonymous above suggests making use of the dot-locking mechanism contributed by Apple. This might be an effective work-around. But remember that there is performance impact. Also remember that an SQLite database that uses dot-locking is subtly imcompatible with a standard SQLite database. The file format itself is the same, but if two processes try to access the database file at the same time and one uses dot-locks and the other uses posix advisory locks, you will end up with corruption. ---- _2007-Jul-07 12:44:09 by anonymous:_ {linebreak} It's very odd that Apple does not fix their Mac OSX POSIX locks for NFS given their resources. #f2dcdc 2491 active 2007 Jul anonymous Pending 1 1 Mingw Warnings w/ 3.4.0 Amalgamation When compiling the 3.4.0 amalgamation sqlite3.c file w/ no defines, you get the following warnings: sqlite3/sqlite3.c: In function `sqlite3BtreeFindCell':{linebreak} sqlite3/sqlite3.c:23249: warning: unused variable `data'{linebreak} sqlite3/sqlite3.c: In function `vxprintf':{linebreak} sqlite3/sqlite3.c:8488: warning: 'xtype' might be used uninitialized in this function{linebreak} sqlite3/sqlite3.c: In function `sqlite3BtreeOpen':{linebreak} sqlite3/sqlite3.c:19488: warning: 'nameLen' might be used uninitialized in this function{linebreak} sqlite3/sqlite3.c: In function `getOverflowPage':{linebreak} sqlite3/sqlite3.c:25386: warning: 'rc' might be used uninitialized in this function{linebreak} sqlite3/sqlite3.c: In function `sqlite3Select':{linebreak} sqlite3/sqlite3.c:56300: warning: 'pEList' might be used uninitialized in this function{linebreak} sqlite3/sqlite3.c:56301: warning: 'pTabList' might be used uninitialized in this function{linebreak} sqlite3/sqlite3.c: At top level:{linebreak} sqlite3/sqlite3.c:16020: warning: 'sqlite3GenericAllocationSize' defined but not used{linebreak} sqlite3/sqlite3.c:6188: warning: 'sqlite3Utf16Substr' declared `static' but never defined{linebreak} sqlite3/sqlite3.c:6307: warning: 'sqlite3Get2byte' declared `static' but never defined{linebreak} sqlite3/sqlite3.c:6309: warning: 'sqlite3Put2byte' declared `static' but never defined{linebreak} sqlite3/sqlite3.c:23248: warning: 'sqlite3BtreeFindCell' defined but not used{linebreak} sqlite3/sqlite3.c:63547: warning: 'sqlite3ParserAlloc' defined but not used{linebreak} sqlite3/sqlite3.c:63673: warning: 'sqlite3ParserFree' defined but not used{linebreak} sqlite3/sqlite3.c:65286: warning: 'sqlite3Parser' defined but not used{linebreak} I know the uninitialized warnings are false warnings but the defined functions that aren't used seem to be an error in building the amalgamation. #e8e8bd 2498 active 2007 Jul anonymous Pending 3 2 sqlite memory org on linux (related ticket #2473)... he sample programme that I run(wrote) in tty1 and there I operate the command of ps at tty2, there seems two items from the programme of ps command. This error was not at the version 3.3.13 but now it is happening at sqlite versions although i change nothing from the programme, If I turn to old versions, there is seen only one item again. When I upgrade to version 3.3.13 or later, there is seen two items again Is it normal or there is any mistake? (excuse my poor english) _2007-Jul-11 16:44:22 by anonymous:_ {linebreak} So you are seeing 2 processes instead of 1 on Linux? Linux 2.4 and earlier kernels show threads as seperate processes with unique process IDs. Is your program creating any threads? The only place where SQLite creates threads is the function below - but it joins with the thread right away.
/* ** This procedure attempts to determine whether or not threads ** can override each others locks then sets the ** threadsOverrideEachOthersLocks variable appropriately. */ static void testThreadLockingBehavior(int fd_orig){ int fd; struct threadTestData d[2]; pthread_t t[2]; fd = dup(fd_orig); if( fd<0 ) return; memset(d, 0, sizeof(d)); d[0].fd = fd; d[0].lock.l_type = F_RDLCK; d[0].lock.l_len = 1; d[0].lock.l_start = 0; d[0].lock.l_whence = SEEK_SET; d[1] = d[0]; d[1].lock.l_type = F_WRLCK; pthread_create(&t[0], 0, threadLockingTest, &d[0]); pthread_create(&t[1], 0, threadLockingTest, &d[1]); pthread_join(t[0], 0); pthread_join(t[1], 0); close(fd); threadsOverrideEachOthersLocks = d[0].result==0 && d[1].result==0; }If you post a small C program demonstrating what you're seeing, someone may be able to offer a suggestion. ---- _2007-Jul-11 16:47:10 by anonymous:_ {linebreak} I suppose it's not inconceivable that the join failed. Perhaps these pthread_join calls' return codes should be examined for errors. ---- _2007-Jul-11 18:53:36 by anonymous:_ {linebreak} If you're playing games with tty's and you've got an early Linux 2.6 kernel, it's possible that processes are dying because of http://lkml.org/lkml/2004/10/21/119. It was, last I checked, fixed in 2.6.10. The SIGHUP being generated might also interfer with a =pthread_join()=, although =pthread_join()= doesn't say anything about ever generating =EINTR=... c. ---- _2007-Jul-12 06:12:28 by anonymous:_ {linebreak} my example program is very simple, i not use threading-multithreading structure... If I turn to old versions of sqlite, there is seen only one item again, when I upgrade to version 3.3.13 or later, there is seen two items again Is it. note: /lib/libpthread.so.0 linked to /lib/libpthread-0.10.so (size 55468 byte) ---- _2007-Jul-12 11:36:37 by anonymous:_ {linebreak} Your description of the problem isn't clear enough, so the answers you're getting are just guesses. You may have more luck by describing the problem (with as much detail as possible) in your native language and hoping someone in the SQLite community can add a translation. I know you're doing your best with the english you speak, but it's not working well enough for someone to help with your problem. Adding code samples and command-line output would also help considerably, since that sort of this is mostly language independent. #f2dcdc 2508 active 2007 Jul anonymous Pending 1 1 utf8ToUnicode() does not work on some WinCE devices On some WinCE devices first call to =MultiByteToWideChar()= in =utf8ToUnicode()= always fails. Tried calling =GetLastError()= after it fails and it returns error code 87 -- =ERROR_INVALID_PARAMETER=. To fix this had to change code page from =CP_UTF8= to =CP_ACP= -- no idea why this works. Original =utf8ToUnicode()= ---- static WCHAR *utf8ToUnicode(const char *zFilename) { int nChar; WCHAR *zWideFilename; nChar = MultiByteToWideChar(CP_UTF8, 0, zFilename, -1, NULL, 0); zWideFilename = sqliteMalloc( nChar*sizeof(zWideFilename[0]) ); if( zWideFilename==0 ){ return 0; } nChar = MultiByteToWideChar(CP_UTF8, 0, zFilename, -1, zWideFilename, nChar); if( nChar==0 ){ sqliteFree(zWideFilename); zWideFilename = 0; } return zWideFilename; } ---- Fixed =utf8ToUnicode()= ---- static WCHAR *utf8ToUnicode(const char *zFilename) { int nChar; WCHAR *zWideFilename; nChar = MultiByteToWideChar(CP_ACP, 0, zFilename, -1, NULL, 0); if( nChar == 0 ) { DWORD dwError = GetLastError(); OSTRACE2("MultiByteToWideChar() failed, last error: %d\n", dwError); return 0; } zWideFilename = sqliteMalloc( nChar*sizeof(zWideFilename[0]) ); if( zWideFilename==0 ){ return 0; } nChar = MultiByteToWideChar(CP_ACP, 0, zFilename, -1, zWideFilename, nChar); if( nChar==0 ){ sqliteFree(zWideFilename); zWideFilename = 0; } return zWideFilename; } ---- _2007-Jul-17 23:56:10 by anonymous:_ {linebreak} =unicodeToUtf8()= needs to be fixed the same way. Before: ---- static char *unicodeToUtf8(const WCHAR *zWideFilename){ int nByte; char *zFilename; nByte = WideCharToMultiByte(CP_UTF8, 0, zWideFilename, -1, 0, 0, 0, 0); zFilename = sqliteMalloc( nByte ); if( zFilename==0 ){ return 0; } nByte = WideCharToMultiByte(CP_UTF8, 0, zWideFilename, -1, zFilename, nByte, 0, 0); if( nByte == 0 ){ sqliteFree(zFilename); zFilename = 0; } return zFilename; } ---- After: ---- static char *unicodeToUtf8(const WCHAR *zWideFilename){ int nByte; char *zFilename; nByte = WideCharToMultiByte(CP_ACP, 0, zWideFilename, -1, NULL, 0, NULL, NULL); if ( nByte == 0 ) { DWORD dwError = GetLastError(); OSTRACE2("WideCharToMultiByte() failed, last error = %d\n", dwError); return 0; } zFilename = sqliteMalloc( nByte ); if( zFilename==0 ){ return 0; } nByte = WideCharToMultiByte(CP_ACP, 0, zWideFilename, -1, zFilename, nByte, 0, 0); if( nByte == 0 ){ sqliteFree(zFilename); zFilename = 0; } return zFilename; } ---- Note that while original code with =CP_UTF8= works on Windows and SOME WinCE devices, this modified code works well and Windows and all WinCE devices I've tested so far. ---- _2007-Jul-18 16:01:21 by anonymous:_ {linebreak} Why not using the conversions from SQLite internals ? It can change a UTF-16 to UTF-8 and vice-versa. Or using UTF-16 variants in windows ce should be the best case. ---- _2007-Aug-09 20:47:04 by anonymous:_ Why not using the conversions from SQLite internals ? It can change a UTF-16 to UTF-8 and vice-versa. Or using UTF-16 variants in windows ce should be the best case. Not so simple. =unicodeToUtf8()= is used a lot internally regardless of what whether you use UTF-16 or UTF-8 yourself. For example, =unicodeToUtf8()= is used by =sqlite3WinTempFileName()= which is in turn used by =sqlite3PagerOpentemp()= -- I think you get the idea. ---- _2007-Dec-20 00:29:33 by anonymous:_ {linebreak} We've found that using CP_UTF8 fails on WinCE kernels that don't include SYSGEN_CORELOC (http://msdn2.microsoft.com/en-us/library/ms903883.aspx). To make the code handle any device it should be changed to: static WCHAR *utf8ToUnicode(const char *zFilename) { int nChar; WCHAR *zWideFilename; nChar = MultiByteToWideChar(CP_UTF8, 0, zFilename, -1, NULL, 0); if( nChar == 0 ) { nChar = MultiByteToWideChar(CP_ACP, 0, zFilename, -1, NULL, 0); if( nChar == 0 ) { DWORD dwError = GetLastError(); OSTRACE2("MultiByteToWideChar() failed, last error: %d\n", dwError); return 0; } } zWideFilename = sqliteMalloc( nChar*sizeof(zWideFilename[0]) ); if( zWideFilename==0 ) { return 0; } nChar = MultiByteToWideChar(CP_UTF8, 0, zFilename, -1, zWideFilename, nChar); if( nChar==0 ) { nChar = MultiByteToWideChar(CP_ACP, 0, zFilename, -1, zWideFilename, nChar); if( nChar==0 ) { sqliteFree(zWideFilename); zWideFilename = 0; } } return zWideFilename; } #f2dcdc 2509 active 2007 Jul anonymous New 1 1 SQLITE_DATE SELECT CAST(MyDate AS DATE), CAST(MyTime AS TIME) FROM MyData I hope, it will result/return DATE, TIME. Please support to SQLITE_DATE and SQLITE_TIME. Thanks. #f2dcdc 2510 active 2007 Jul anonymous Pending 1 1 Vacuum modified FTS2 rowids VACUUM modifies FTS2 rowids. Here is the test: drop table if exists a; create virtual table a using fts2 (t); insert into a (t) values ('one'); insert into a (t) values ('two'); insert into a (t) values ('three'); select rowid, * from a; delete from a where t = 'two'; vacuum; select rowid, * from a; Unfortunately there is no workaround since table a is auto-generated by the FTS2 module. _2007-Jul-17 14:05:58 by anonymous:_ {linebreak} http://www.sqlite.org/cvstrac/chngview?cn=4157 ---- _2007-Jul-17 14:24:29 by anonymous:_ {linebreak} Yes, this behavior has been recently documented, but there is no user workaround like PRIMARY KEY for FTS2 rowids. Therefore I consider this as a bug which should be fixed in fts2.c. ---- _2007-Jul-17 14:55:57 by anonymous:_ {linebreak} Should virtual tables be VACUUMable? What exactly is being vacuumed here - an internal table? ---- _2007-Jul-17 16:34:55 by shess:_ {linebreak} I agree, I think this is a bug. Rather severe, too, the entire fts system implicitely depends on rowids not changing, this means that vacuum will break fts tables (fts1 or fts2).
drop table if exists t; create virtual table t using fts2; insert into t (content) values ('This is a test'); insert into t (content) values ('This is a string'); insert into t (content) values ('That was a test'); insert into t (content) values ('A random string'); select content from t where t MATCH 'test'; delete from t where content = 'This is a string'; vacuum; select content from t where t MATCH 'test';The first select outputs 'This is a test' and 'That was a test'. The second select outputs 'This is a test', and 'A random string'. ---- _2007-Jul-17 17:27:21 by anonymous:_ {linebreak} This patch seems to address the FTS2 VACUUM problem and passes all fts2 tests. It adds an INTEGER PRIMARY KEY docid column to the hidden %_content table. Note: this new table format is not backwards compatible with existing FTS2 databases. -Joe Wilson
Index: ext/fts2/fts2.c =================================================================== RCS file: /sqlite/sqlite/ext/fts2/fts2.c,v retrieving revision 1.40 diff -u -3 -p -r1.40 fts2.c --- ext/fts2/fts2.c 2 Jul 2007 10:16:50 -0000 1.40 +++ ext/fts2/fts2.c 17 Jul 2007 17:19:49 -0000 @@ -1769,9 +1769,9 @@ typedef enum fulltext_statement { */ static const char *const fulltext_zStatement[MAX_STMT] = { /* CONTENT_INSERT */ NULL, /* generated in contentInsertStatement() */ - /* CONTENT_SELECT */ "select * from %_content where rowid = ?", + /* CONTENT_SELECT */ "select * from %_content where docid = ?", /* CONTENT_UPDATE */ NULL, /* generated in contentUpdateStatement() */ - /* CONTENT_DELETE */ "delete from %_content where rowid = ?", + /* CONTENT_DELETE */ "delete from %_content where docid = ?", /* BLOCK_INSERT */ "insert into %_segments values (?)", /* BLOCK_SELECT */ "select block from %_segments where rowid = ?", @@ -1860,14 +1860,14 @@ static struct fulltext_vtab *cursor_vtab static const sqlite3_module fts2Module; /* forward declaration */ /* Return a dynamically generated statement of the form - * insert into %_content (rowid, ...) values (?, ...) + * insert into %_content (docid, ...) values (?, ...) */ static const char *contentInsertStatement(fulltext_vtab *v){ StringBuffer sb; int i; initStringBuffer(&sb); - append(&sb, "insert into %_content (rowid, "); + append(&sb, "insert into %_content (docid, "); appendList(&sb, v->nColumn, v->azContentColumn); append(&sb, ") values (?"); for(i=0; i---- _2007-Jul-18 00:13:56 by shess:_ {linebreak} BTW, AFAICT this only happens for sqlite3.4. Older versions don't seem to have the problem. ---- _2007-Jul-18 01:31:49 by anonymous:_ {linebreak} The rowid changing after VACUUM predates 3.4.0...nColumn; ++i) @@ -1878,7 +1878,7 @@ static const char *contentInsertStatemen /* Return a dynamically generated statement of the form * update %_content set [col_0] = ?, [col_1] = ?, ... - * where rowid = ? + * where docid = ? */ static const char *contentUpdateStatement(fulltext_vtab *v){ StringBuffer sb; @@ -1893,7 +1893,7 @@ static const char *contentUpdateStatemen append(&sb, v->azContentColumn[i]); append(&sb, " = ?"); } - append(&sb, " where rowid = ?"); + append(&sb, " where docid = ?"); return stringBufferData(&sb); } @@ -2027,15 +2027,15 @@ static int sql_step_leaf_statement(fullt return rc; } -/* insert into %_content (rowid, ...) values ([rowid], [pValues]) */ -static int content_insert(fulltext_vtab *v, sqlite3_value *rowid, +/* insert into %_content (docid, ...) values ([docid], [pValues]) */ +static int content_insert(fulltext_vtab *v, sqlite3_value *docid, sqlite3_value **pValues){ sqlite3_stmt *s; int i; int rc = sql_get_statement(v, CONTENT_INSERT_STMT, &s); if( rc!=SQLITE_OK ) return rc; - rc = sqlite3_bind_value(s, 1, rowid); + rc = sqlite3_bind_value(s, 1, docid); if( rc!=SQLITE_OK ) return rc; for(i=0; i nColumn; ++i){ @@ -2047,7 +2047,7 @@ static int content_insert(fulltext_vtab } /* update %_content set col0 = pValues[0], col1 = pValues[1], ... - * where rowid = [iRowid] */ + * where docid = [iRowid] */ static int content_update(fulltext_vtab *v, sqlite3_value **pValues, sqlite_int64 iRowid){ sqlite3_stmt *s; @@ -2075,7 +2075,7 @@ static void freeStringArray(int nString, free((void *) pString); } -/* select * from %_content where rowid = [iRow] +/* select * from %_content where docid = [iRow] * The caller must delete the returned array and all strings in it. * null fields will be NULL in the returned array. * @@ -2101,10 +2101,10 @@ static int content_select(fulltext_vtab values = (const char **) malloc(v->nColumn * sizeof(const char *)); for(i=0; i nColumn; ++i){ - if( sqlite3_column_type(s, i)==SQLITE_NULL ){ + if( sqlite3_column_type(s, i+1)==SQLITE_NULL ){ values[i] = NULL; }else{ - values[i] = string_dup((char*)sqlite3_column_text(s, i)); + values[i] = string_dup((char*)sqlite3_column_text(s, i+1)); } } @@ -2120,7 +2120,7 @@ static int content_select(fulltext_vtab return rc; } -/* delete from %_content where rowid = [iRow ] */ +/* delete from %_content where docid = [iRow ] */ static int content_delete(fulltext_vtab *v, sqlite_int64 iRow){ sqlite3_stmt *s; int rc = sql_get_statement(v, CONTENT_DELETE_STMT, &s); @@ -2870,7 +2870,7 @@ static int fulltextCreate(sqlite3 *db, v if( rc!=SQLITE_OK ) return rc; initStringBuffer(&schema); - append(&schema, "CREATE TABLE %_content("); + append(&schema, "CREATE TABLE %_content(docid INTEGER PRIMARY KEY, "); appendList(&schema, spec.nColumn, spec.azContentColumn); append(&schema, ")"); rc = sql_exec(db, spec.zDb, spec.zName, stringBufferData(&schema)); @@ -3731,8 +3731,8 @@ static int fulltextFilter( TRACE(("FTS2 Filter %p\n",pCursor)); - zSql = sqlite3_mprintf("select rowid, * from %%_content %s", - idxNum==QUERY_GENERIC ? "" : "where rowid=?"); + zSql = sqlite3_mprintf("select * from %%_content %s", + idxNum==QUERY_GENERIC ? "" : "where docid=?"); sqlite3_finalize(c->pStmt); rc = sql_prepare(v->db, v->zDb, v->zName, &c->pStmt, zSql); sqlite3_free(zSql);
SQLite version 3.3.7 Enter ".help" for instructions sqlite> CREATE TABLE t(a); sqlite> INSERT INTO "t" VALUES('one'); sqlite> INSERT INTO "t" VALUES('two'); sqlite> INSERT INTO "t" VALUES('three'); sqlite> select rowid, * from t; 1|one 2|two 3|three sqlite> delete from t where a = 'one'; sqlite> select rowid, * from t; 2|two 3|three sqlite> vacuum; sqlite> select rowid, * from t; 1|two 2|three SQLite version 3.2.0 Enter ".help" for instructions sqlite> CREATE TABLE t(a); sqlite> INSERT INTO "t" VALUES('one'); sqlite> INSERT INTO "t" VALUES('two'); sqlite> INSERT INTO "t" VALUES('three'); sqlite> select rowid, * from t; 1|one 2|two 3|three sqlite> delete from t where a = 'one'; sqlite> select rowid, * from t; 2|two 3|three sqlite> vacuum; sqlite> select rowid, * from t; 1|two 2|three---- _2007-Jul-18 15:59:24 by anonymous:_ {linebreak} As you may know, INTEGER PRIMARY KEY indexes are the ROWID, so I must supect they would change after a VACUUM. The best workaround is to put docid as INTEGER, then adding a PRIMARY KEY index for the docid column. #e8e8bd 2511 active 2007 Jul anonymous Pending drh 3 2 Inconsistent Pragma output Pragma output is inconsistent when setting the value. Most do not generate any output and silently set the value, while others generate a singleton row with the set value. Here is a list of pragmas that generate output while setting the values: sqlite> PRAGMA locking_mode = NORMAL; normal sqlite> PRAGMA max_page_count = 100000; 100000 The following do not generate any output upon query: PRAGMA case_sensitive_like; PRAGMA incremental_vacuum; Sqlite was built from almagamation using the following configuration flags: --enable-threadsafe --disable-tcl --enable-tempstore #f2dcdc 2512 active 2007 Jul shess Pending 1 1 FTS virtual table name quoting problem All table names should be quoted in the FTS module code. with TRACE enabled in ext/fts2/fts2.c:
sqlite> create virtual table "a b c" using fts2 (t); FTS2 Create FTS2 sql: CREATE TABLE main.a b c_content(c0t) SQL error: vtable constructor failed: a b c_2007-Jul-18 06:44:21 by anonymous:_ {linebreak} A similar problem shows if a FTS column has the same name as the FTS table: CREATE VIRTUAL TABLE a USING fts2 (a); Returns "vtable constructor failed: a.". #f2dcdc 2517 active 2007 Jul anonymous Pending dflam 1 1 exception on reading text in vista but not xp My companies sqlite 3.1 db works perfectly on Win XP but when we moved to Vista (I'm using Vista 64)it is trowing an exception when I access a text field that contains this data: 'A/C Pressure Sensor, raw1 = A/C on, 0 = A/C off (A/C status determines which IACTx cell is used)' Interestingly when I view data I've inserted using sqliteman3 it has unprintable characters added to it. (A/C status determines which IACTx cell is used)9 If I define the field as Char[512] this artifact goes away. But reading your literature this isn't supposed to make a difference because everything is char. I've changed the values in the error column, but the error seems to be depending on length rather than value. Any help appreciatied! Jim _2007-Jul-19 15:13:41 by drh:_ {linebreak} We will be better able to help you with your problem on the SQLite mailing list. See http://www.sqlite.org/support.html for instructions on joining the mailing list. #cfe8bd 2530 active 2007 Jul anonymous Pending 2 3 Unable to write to windows share, even with exclusive lock It has been mentioned that the file locking does not work on windows shared network drives (Samba or SMB drives from Windows or Linux). It seems that an exclusive lock should be a workaround for this problem if you need to write to a shared drive. Currently a more complicated locking is being attempted and failing on network drives. With an exclusive lock, SQLite could resort to simply holding a open write or append enabled file handle to the database as a more primitive locking system that is more likely to work on network drive. No other process could open the database but that would be expected with an exclusive lock. The following case should then function: grudy@gamma:~$ mount | grep Files //winserver/FileDump on /mnt/Files type cifs (rw,mand,noexec,nosuid,nodev) grudy@gamma:~$ touch /mnt/Files/i_have_write_permissions.txt; rm /mnt/Files/i_have_write_permissions.txt grudy@gamma:~$ sqlite3 /mnt/Files/foo.sqlite SQLite version 3.3.17 Enter ".help" for instructions sqlite> PRAGMA locking_mode = EXCLUSIVE; exclusive sqlite> create table bar (foobar); SQL error: database is locked sqlite> #e8e8bd 2539 active 2007 Jul anonymous Pending 2 2 WinCE: Temporary etilqs_ files are not removed from temporary folder Hi, when temporary etilqs_* files are created during SQLite work on Windows CE devices, they are not removed at all. Temporary folder at CE devices: /Application Data/Volatile I've research that it winClose(os_win.c) function has been changed at do not remove this file, assuming it to be removed at winceDestroyLock(os_win.c), so if no lock was happened then files will stay here forever. Has fixed it in my local copy, with hope that it will be fixed when new cool versions of SQLite will be available. My fix at os_win.c:
static int winClose(OsFile **pId){ winFile *pFile; int rc = 1; if( pId && (pFile = (winFile*)*pId)!=0 ){ int rc, cnt = 0; OSTRACE2("CLOSE %d\n", pFile->h); do{ rc = CloseHandle(pFile->h); }while( rc==0 && cnt++ < MX_CLOSE_ATTEMPT && (Sleep(100), 1) ); #if OS_WINCE winceDestroyLock(pFile); // fix begin if( pFile->zDeleteOnClose ){ DeleteFileW(pFile->zDeleteOnClose); sqliteFree(pFile->zDeleteOnClose); } // fix end #endif OpenCounter(-1); sqliteFree(pFile); *pId = 0; } return rc ? SQLITE_OK : SQLITE_IOERR; }Thanks, Fedor _2007-Jul-28 16:41:41 by anonymous:_ {linebreak} The solution is to revert checkin 3836 and re-open ticket #2294.
Looking at the wince locking mechanism, the only time we ever use the zDeleteOnClose flag is when we've opened a database for exclusive access in sqlite3WinOpenExclusive. To save time and resources (and because its not necessary) we never bother creating a locking mechanism for exclusively-opened files. So pFile->hMutex is NULL when hitting winceDestroyLock(), and the file is never deleted.
Is it possible that the original poster of #2294 was trying to close the same connection on multiple threads at the same time? ---- _2007-Jul-31 05:32:39 by anonymous:_ {linebreak} This is actually a duplicate of #2533 ---- _2007-Sep-21 14:20:05 by anonymous:_ {linebreak} So when the fix of [3836] was applied, the code to delete the file was only put in the section that is called when we have a mutex. I wonder, if the deletion of the file should also take place if there was no mutex. Works for me at least: static void winceDestroyLock(winFile *pFile){ if (pFile->hMutex){ /* Acquire the mutex */ winceMutexAcquire(pFile->hMutex); /* The following blocks should probably assert in debug mode, but they are to cleanup in case any locks remained open */ if (pFile->local.nReaders){ pFile->shared->nReaders --; } if (pFile->local.bReserved){ pFile->shared->bReserved = FALSE; } if (pFile->local.bPending){ pFile->shared->bPending = FALSE; } if (pFile->local.bExclusive){ pFile->shared->bExclusive = FALSE; } /* De-reference and close our copy of the shared memory handle */ UnmapViewOfFile(pFile->shared); CloseHandle(pFile->hShared); * if( pFile->zDeleteOnClose ){ * DeleteFileW(pFile->zDeleteOnClose); * sqliteFree(pFile->zDeleteOnClose); * pFile->zDeleteOnClose = 0; * } /* Done with the mutex */ winceMutexRelease(pFile->hMutex); CloseHandle(pFile->hMutex); pFile->hMutex = NULL; } + else + { + if( pFile->zDeleteOnClose ){ + DeleteFileW(pFile->zDeleteOnClose); + sqliteFree(pFile->zDeleteOnClose); + pFile->zDeleteOnClose = 0; + } + } } The code marked with * was put there in #f2dcdc 2543 active 2007 Jul anonymous Pending 1 1 Chinese charset not support?? when i create a table. the table name is " " (chinese) after this "alter table add column aaa text null" error why??/ thank you #f2dcdc 2140 active 2007 Jan anonymous Pending 3 1 sqlite doesn't link to readline sqlite relies on another library to link to libreadline, causing this error with LDFLAGS=-Wl,--as-needed: gcc -O2 -march=i686 -pipe -DOS_UNIX=1 -DHAVE_USLEEP=1 -DHAVE_FDATASYNC=1 -I. -I./src -DNDEBUG -DTHREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_CURSOR -DHAVE_READLINE=1 -I/usr/include/readline -o .libs/sqlite3 ./src/shell.c ./.libs/libsqlite3.so -lpthread -lncurses /tmp/cclOD1M7.o: In function `process_input': shell.c:(.text+0x37a5): undefined reference to `readline' shell.c:(.text+0x37c0): undefined reference to `add_history' /tmp/cclOD1M7.o: In function `main': shell.c:(.text+0x3f01): undefined reference to `read_history' shell.c:(.text+0x3f1a): undefined reference to `stifle_history' shell.c:(.text+0x3f22): undefined reference to `write_history' collect2: ld returned 1 exit status #cfe8bd 2165 active 2007 Jan anonymous Pending drh 4 3 pager performance and checksum pager.c Embedd the 2 byte pager_pagehash() result into the page, near the beginning of the page. Use the intire page to calculate the pager_pagehash exclusive of the two byte page_hash data embedded in the page. That way a simple xor as in CHECK_PAGE of the entire page including the 2 byte pager_pagehash is all that is needed to validate a page. Also you could include the "4 byte" random at the beginning and at the end... But that would be a bit of overkill. The sampling of only every 200 bytes is interesting. Review change the SQLITE_CHECK_PAGES ifdef to a SQLITE_OMIT_PAGE_CHECK.. As on disk page validity is very important. Could this be integrated into a pragma setting? _2007-Jan-12 18:08:49 by anonymous:_ {linebreak} uint16_t pg_chkval(Dpage *pg, uint32_t pg_size) { register int i; register uint16_t val = 0; register uint16_t *bw = (uint16_t *) pg; for(i= 0; i < pg_size;i=i+2 ) val= val^ *bw++ ; return val; } uint16_t pg_calcval(Dpage *pg, uint32_t page_size) { int i; register uint16_t val = 0; register uint16_t *bw = (uint16_t *) pg; /* Scan up to location where chk val is stored */ for(i= 0; i < 8;i=i+2 ) val= val^ *bw++ ; val = val^ 0; bw ++ ; /* Now scan the tail of the block */ for(i=10; i < page_size;i=i+2 ) val= val^ *bw++ ; return val; } #c8c8c8 2171 review 2007 Jan anonymous Fixed drh 3 3 Fix a major leakage of token minors in lemon generated parsers Fix a major leakage of token minors in lemon generated parsers due to the fact that lemon was not genmerating destructor code for elements in the RHS of rules without C-code. This is a copy of a fix mafe to the lemon parser used by the wireshark project http://wireshark.org http://anonsvn.wireshark.org/viewvc/viewvc.py/trunk/tools/lemon/lemon.c?r1=20442&r2=20441&pathrev=20442
--- trunk/tools/lemon/lemon.c 2007/01/15 19:11:29 20441 +++ trunk/tools/lemon/lemon.c 2007/01/15 19:48:06 20442 @@ -3245,6 +3245,8 @@ for(i=0; iIt appears that the change in [3593] causes yy_destructor() to be called more than once for tokens, while the one posted here (and applied to wireshark) does not. in the meanwhile I changed the wireshark copy back to havenrhs; i++) used[i] = 0; lhsused = 0; + if (! rp->code) rp->code = "\n"; + append_str(0,0,0,0); for(cp=rp->code; *cp; cp++){ if( safe_isalpha(*cp) && (cp==rp->code || (!safe_isalnum(cp[-1]) && cp[-1]!='_')) ){ @@ -3875,7 +3877,7 @@ /* Generate code which execution during each REDUCE action */ for(rp=lemp->rule; rp; rp=rp->next){ - if( rp->code ) translate_code(lemp, rp); + translate_code(lemp, rp); } for(rp=lemp->rule; rp; rp=rp->next){ struct rule *rp2;
if (! rp->code) rp->code = "\n"; append_str(0,0,0,0); for(cp=rp->code; *cp; cp++){but there's an issue with this the, generated parser gets
#line 0 "./dtd_grammar.lemon"instead of the number pointing to the line where the rule is. I'll keep investigating and keep you POSTed (literally :-) #f2dcdc 2183 active 2007 Jan anonymous Pending drh 1 1 OMIT_SHARED_CACHE: AV and crash with FTS2 INSERT Given that SQLite is compiled with -DSQLITE_ENABLE_FTS2=1 -DSQLITE_OMIT_SHARED_CACHE=1 the following code crashes after about 273 insertions with Access violation: Read of address 0x00000014 at btree.c, line 3451: if( pCur->idx>=pPage->nCell ){ Here is the code to reproduce: int main(int argc, char* argv[]) { sqlite3_stmt *pStmt; int i; check( sqlite3_open( "test_fts2.db3", &db) ); check( sqlite3_exec( db, "CREATE VIRTUAL TABLE FTS USING FTS2 (Content);", 0, 0, 0)); check( sqlite3_exec( db, "BEGIN TRANSACTION;", 0, 0, 0)); check( sqlite3_prepare( db, "INSERT INTO FTS (Content) VALUES ('Far out in the uncharted backwaters of the unfashionable end of the Western Spiral arm of the Galaxy lies a small unregarded yellow sun.');", -1, &pStmt, NULL)); for( i = 1; i < 1000; i++) { printf( "%d\n", i); check( sqlite3_step( pStmt) ); check( sqlite3_reset( pStmt) ); } check( sqlite3_exec( db, "COMMIT;", 0, 0, 0)); check( sqlite3_finalize( pStmt )); check( sqlite3_close( db )); printf ("Done"); scanf ("*%s"); return 0; } Could this be related to ticket #2032? #cfe8bd 2203 active 2007 Jan anonymous Pending 2 3 table_info pragma "default" column format changed? Beginning with SQLite3 3.3.8, it looks like the format of the 'default' value returned by the table_info pragma has changed. Before, it used to be a bare string: dev:~> sqlite3 SQLite version 3.3.7 Enter ".help" for instructions sqlite> create table testings (a integer primary key, b string default 'Tester', c string default NULL); sqlite> pragma table_info(testings); 0|a|integer|0||1 1|b|string|0|Tester|0 2|c|string|0||0 After 3.3.8, the 'defaults' column is now a SQL-quoted string: dev:~> sqlite3 SQLite version 3.3.11 Enter ".help" for instructions sqlite> create table testings (a integer primary key, b string default 'Tester', c string default NULL); sqlite> pragma table_info(testings); 0|a|integer|0||1 1|b|string|0|'Tester'|0 2|c|string|0|NULL|0 Now, I think I do prefer the latter, where the default is a SQL-quoted string. However, this seems a rather significant change to make mid-stream, in a minor point release. It broke all Ruby on Rails applications that use sqlite3, for instance, because Rails reads that default value to determine how to default the value of each new record. Was this intentional? Or is this a bug? I'd love to see this behavior reverted and saved for a release with a more significant release number. _2007-Jan-29 22:01:54 by anonymous:_ {linebreak} One of your fellow Railers requested this change: Ticket #2078 ---- _2007-Jan-29 22:10:55 by drh:_ {linebreak} See also ticket #1919 which might also be an issue here. ---- _2007-Jan-29 22:33:19 by anonymous:_ {linebreak} Anonymous, you make it sound as if anyone associated with Rails can make a request of the sqlite3 team and have it be automatically assumed to be sanctioned by the Rails core team. Whoever did the original request did not do so under the umbrella of Rails core. If that change was the one that resulted in this behavior, it most definitely should not have been recommended, and would not have been blessed by any of the core team. At this point, though, I'm not interested in blame. I just want to see what can be done to make sqlite3 work with Rails again, preferably in a way that is backwards compatible with previous sqlite3 releases. ---- _2007-Jan-30 05:20:56 by anonymous:_ {linebreak} I agree the feature should be fixed due to backwards compatability, but Rails should try to accomodate both pragma variants since they are both "in the wild". You could base your decision on the sqlite version string, for instance. ---- _2007-Jan-30 18:39:44 by anonymous:_ {linebreak} Just FYI, there's another related ticket at the Rails trac at http://dev.rubyonrails.org/ticket/6523, and a Debian bug report with a patch at http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=397531. #e8e8bd 2215 active 2007 Feb anonymous Pending 1 2 error messages in virtual table are not propagated I'm trying to return a customized error message in xBestIndex in my virtual table implementation. Rather than copying my implementation here the problem can be reproduced by changing the fulltextBestIndex method from fts1. For example: /* Decide how to handle an SQL query. */ static int fulltextBestIndex(sqlite3_vtab *pVTab, sqlite3_index_info *pInfo){ int i; TRACE(("FTS1 BestIndex\n")); pVTab->zErrMsg = sqlite3_mprintf ("THIS IS AN ERROR MESSAGE"); return SQLITE_ERROR; for(i=0; i
static void winDlError(sqlite3_vfs *pVfs, int nBuf, char *zBufOut){ int error = GetLastError(); #if OS_WINCE if( error>0x7FFFFFF ){ sqlite3_snprintf(nBuf, zBufOut, "OsError 0x%x", error); }else{ sqlite3_snprintf(nBuf, zBufOut, "OsError %d", error); } #else if( isNT() ){ LPWSTR zWinTemp = NULL; DWORD dwLen = FormatMessageW( FORMAT_MESSAGE_ALLOCATE_BUFFER | FORMAT_MESSAGE_FROM_SYSTEM | FORMAT_MESSAGE_IGNORE_INSERTS, NULL, error, 0, (LPWSTR) &zWinTemp, 0, 0 ); if (dwLen > 0) { char * zOut = unicodeToUtf8(zWinTemp); LocalFree(zWinTemp); sqlite3_snprintf(nBuf, zBufOut, "%s", zOut); free(zOut); } }else{ LPSTR zWinTemp = NULL; DWORD dwLen = FormatMessageA( FORMAT_MESSAGE_ALLOCATE_BUFFER | FORMAT_MESSAGE_FROM_SYSTEM | FORMAT_MESSAGE_IGNORE_INSERTS, NULL, error, 0, (LPSTR) &zWinTemp, 0, 0 ); if (dwLen > 0) { char * zOut = mbcsToUtf8(zWinTemp); LocalFree(zWinTemp); sqlite3_snprintf(nBuf, zBufOut, "%s", zOut); free(zOut); } } #endif }#f2dcdc 2842 active 2007 Dec anonymous Pending 1 1 .import does not recongnise NULL values .import function fails to see NULL values in csv files as NULL values...instead they are treated as the string "NULL". This is with .mode list and separator , But behaves similarly for .mode csv Also if one outputs a table with NULL values to a file, then re-imports that file, again .import does not recognise the values as NULL, but as "NULL". Everything here also applies to empty strings in files, e.g. instead of "NULL" using nothing... This is a showstopper for us since we want to import a large amount of data with many tables containing NULL values. I can't see any valid reason for .import not to recognise the same syntax as the command line. Note that something like: sqlite3 my.db insert into MY_TABLE values (1,"foo","bar",NULL) ..works fine. It is just .import that appears to be broken. _2007-Dec-14 16:39:51 by rdc:_ {linebreak} .import only inserts string values into database tables. If your column has a declared type that changes the columns affinity to numeric or integer, then those strings will be converted to numeric values by the SQLIte library. The workaround is to simply insert a unique string where ever you want a NULL value, and then run an update that replaces those strings with real NULL values. If you inserted the string 'NULL' then do this after the .import update t set field = null where field = 'NULL'; You will have to repeat this for each field in your table that might contain the 'NULL' string. #e8e8bd 2857 active 2007 Dec anonymous Pending 2 2 GROUP BY cost estimate wrong with WHERE clause There seems to be an issue with the sqlite cost heuristic with an INDEX present on GROUP BY with certain types of WHERE clauses. Given the database formed by running these statements: create table stuff(a,b,c,d); insert into stuff values(1,2,3,4); create temp view v1 as select random()%100, random()%100, random()%1000, random()%10000 from stuff x, stuff y; insert into stuff select * from v1; insert into stuff select * from v1; insert into stuff select * from v1; insert into stuff select * from v1; insert into stuff select * from v1; create index stuff_b on stuff(b); create index stuff_c on stuff(c); create index stuff_d on stuff(d); analyze; Using sqlite.org's sqlite3-3.5.4.bin, this query takes 47 seconds: select c from stuff where a=23 group by c; while this query takes just 2 seconds: select c from stuff where a=23 group by +c; It is more efficient in this case to do a full table scan instead of using the INDEX on column c. _2007-Dec-23 23:14:06 by anonymous:_ {linebreak} The queries above both run in a couple of seconds with this naive patch:
--- src/where.c 12 Dec 2007 17:42:53 -0000 1.266 +++ src/where.c 23 Dec 2007 22:48:37 -0000 @@ -1514,6 +1514,12 @@ static double bestIndex( flags = 0; } + if( pWC && pWC->nTerm>0 && pOrderBy ){ + /* Reduce cost if both an ORDER/GROUP BY exists with a WHERE. */ + cost /= 100; /* A very rough guess. */ + WHERETRACE(("... WHERE + ORDER BY decreases cost to: %.9g\n", cost)); + } + /* If the table scan does not satisfy the ORDER BY clause, increase ** the cost by NlogN to cover the expense of sorting. */ if( pOrderBy ){But it has not been tested on queries with more than one table. Its logic could be flawed. ---- _2007-Dec-24 00:09:00 by drh:_ {linebreak} The complaint is centered around these two queries: /* 1 */ SELECT c FROM stuff WHERE a=23 GROUP BY c; /* 2 */ SELECT c FROM stuff WHERE a=23 GROUP BY +c; Query 1 runs in about 40 seconds and query 2 in about 1.5 seconds on my macbook. But with the patch, both queries run in about 1.5 seconds. Fair enough. But now consider these two queries: /* 3 */ SELECT c FROM stuff WHERE a!=23 GROUP BY c; /* 4 */ SELECT c FROM stuff WHERE a!=23 GROUP BY +c; In this case, query 3 runs in 42 seconds on an unpatched version of 3.5.4 and query 4 runs in about 109 seconds. So in cases where the WHERE clause is not particularly selective, the first version is faster than the second by a good margin. On a patched version of 3.5.4, both queries 3 and 4 run in about 110 seconds. So it seems to me that the patch is robbing Peter to pay Paul. It makes ORDER BY queries with very selective WHERE clauses run faster but at the expense of making queries with unselective WHERE clauses running slower. But notice this: in the current (unpatched) implementation, the programmer at least has the ability to select a different algorithm by the judicious placement of a "+" sign. After the patch, this is no longer possible. The patch forces the second algorithm to be used in all cases, even cases where it is slower. It seems to me to be better to leave things as they are since the current approach at least allows the programmer to override SQLite's algorithm selection if SQLite chooses incorrectly. The only way, it seems to me, to automatically choose the correct algorithm is to devise some test that will determine (in advance) whether or not the WHERE clause weeds out many or few rows from the result set. I'm thinking that determination is going to be very hard (or impossible) to do without first doing a full table scan. ---- _2007-Dec-24 05:40:47 by anonymous:_ {linebreak} It think it would be surprising to average users that _adding_ an index (on column C in this case) may significantly _decrease_ query performance for some queries. It was surprising to me, at least. In my opinion, a query being 20 times slower in a default bad guess situation is worse than a query only being 2.5 times slower with a default bad guess in a worst case scenario. It's a question of relative magnitude of the difference. This is why I think that the database should err on the side of the WHERE clause having a more selective bias. (Side note: the query timings difference is less pronounced if you use PRAGMA temp_store=memory, in which case query 3 running on an unpatched 3.5.4 takes just 50% more time to run than query 4 on my machine.) But you raise a good point in that if there's a wrong guess in the selectivity bias it would be nice to be able to manually override it. How much do you hate this type of syntax that some other databases use? select c from stuff where a!=23 group by /*+stuff_c*/ c; SQLite does not currently offer a way to pick a specific index. I think it would be quite useful. ---- _2007-Dec-24 17:05:16 by anonymous:_ {linebreak} Another option is to collect WHERE clause statistics in a table like create table sqlite_stat2( where_clause_md5 BLOB primary key, where_clause TEXT, rows_examined INT, rows_true INT ); where the last 2 columns are cumulative for each query. The statistics option could be enabled/disabled via a PRAGMA sqlite_collect_statistics. The where_clause column could be a string generated fairly easily from the walking the parse tree of the resolved Select statement's pWhere. This way the where_clause is normalized and a single query with many subselects could generate more than 1 where_clause, and different queries that happen to use the same normalized where clause would update the same entry in the stat2 table. where_clause normalization would strip off aliases and only refer to the original table and column names. For example the 2 queries below: -- CREATE TABLE t1(a, b); -- CREATE TABLE t2(b, c); SELECT t1.a*c as AC, t2.b-a as BA FROM t1, t2 WHERE AC>BA; SELECT *, t1.a Foo FROM t2, t1 WHERE Foo*c > t2.b - t1.a; would generate the same normalized where_clause string "(T1.A*T2.C)>(T2.B-T1.A)". The table information is already encoded within it. The generated VDBE code would have to generate Mem counters that would be incremented by each WHERE test, and lazily updated at the end of transactions or periodically written to the stat2 table to minimize disk use, as this information is not critical. One could also manually set the stat2 table with statistical values they would like their queries to use even if PRAGMA sqlite_collect_statistics=off; Any time the schema is changed, the entire sqlite_stat2 table would be cleared. #e8e8bd 2859 active 2007 Dec anonymous Pending drh 3 2 Inconsistent column names with DISTINCT Given the following SQL:{linebreak} CREATE TABLE foo(a,b); INSERT INTO foo (a, b) VALUES (1,2); SQLite returns inconsistent column names when using the DISTINCT clause:{linebreak} SELECT DISTINCT foo.A, foo.B FROM foo; foo.A|foo.B 1|2 SELECT DISTINCT a, b FROM foo; a|b 1|2 SELECT DISTINCT * FROM foo; a|b 1|2 SELECT DISTINCT foo.* FROM foo; a|b 1|2 Compared with SELECT without DISTINCT:{linebreak} SELECT foo.A, foo.B FROM foo; a|b 1|2 SELECT a, b FROM foo; a|b 1|2 SELECT * FROM foo; a|b 1|2 SELECT foo.* FROM foo; a|b 1|2 #cfe8bd 2863 active 2007 Dec anonymous Pending 2 3 test cast-3.14, cast-3.18 and cast-3.24 fail test cast-3.{14,18,24} fail on freebsd-6.3-PRERELEASE2: cast-3.14...^M Expected: [9223372036854774784]^M Got: [9223372036854773760]^M cast-3.18...^M Expected: [-9223372036854774784]^M Got: [-9223372036854773760]^M cast-3.24...^M Expected: [9223372036854774784]^M Got: [9223372036854773760]^M I used tcl8.4 from ports with no threads and here was the config line: ../sqlite-3.5.4/configure --prefix=/home/marc/local --with-tcl=/usr/local/lib/tcl8.4/ This was built on an ibm t30 laptop #e8e8bd 2865 active 2007 Dec anonymous Pending 1 2 FTS3 does not build with amalgamation in CVS Grab the latest CVS sources, then run: ./configure make sqlite3.c grep sqlite3Fts3Init sqlite3.c extern int sqlite3Fts3Init(sqlite3*); rc = sqlite3Fts3Init(db); If you compile sqlite3.c with -DSQLITE_ENABLE_FTS3, then sqlite3Fts3Init is unresolved. For some reason, sqlite3Fts3Init and fts3.c was not included in the sqlite3.c amalg. It used to work correctly in 3.5.4. _2007-Dec-30 18:17:57 by anonymous:_ {linebreak} Nevermind, "make sqlite3.c" has never built with the fts3 sources in 3.5.4 or before. You have to run ext/fts3/mkfts3amal.tcl ---- _2007-Dec-30 18:20:56 by anonymous:_ {linebreak} It seems that the sqlite3+fts3 amalg can only be built from main.mk, not Makefile. #cfe8bd 2547 active 2007 Aug danielk1977 Pending 5 3 Changing db encoding of an attached db can confuse shared cache mode. This is quite obscure, but in shared-cache mode: 1) Open db A, attach empty db B. 2) Using another connection from the same thread, set the encoding of B to be different from that of A. Add some data to B. 3) Using the original connection, access database B. It assumes the encoding of A (and therefore mangling any text data). The correct response is to return an error - "attached databases must use the same text encoding as main database". #cfe8bd 2558 active 2007 Aug anonymous Pending 2 3 Multiple JOIN USING() gives incorrect results I'm having a problem joining multiple tables with USING. It appears to work, but the results are incorrect. Here is an example to illustrate the problem. I believe the three SELECT statements should be equivalent, but they produce three different results. .header on .mode column CREATE TABLE Main (pk INTEGER PRIMARY KEY, name VARCHAR); CREATE TABLE OptA (pk INTEGER PRIMARY KEY, alpha VARCHAR); CREATE TABLE OptB (pk INTEGER PRIMARY KEY, beta VARCHAR); INSERT INTO Main VALUES (1, 'One'); INSERT INTO Main VALUES (2, 'Two'); INSERT INTO Main VALUES (3, 'Three'); INSERT INTO Main VALUES (4, 'Four'); INSERT INTO OptA VALUES (1, 'Alpha1'); INSERT INTO OptA VALUES (4, 'Alpha4'); INSERT INTO OptB VALUES (2, 'Beta2'); INSERT INTO OptB VALUES (4, 'Beta4'); SELECT * FROM Main LEFT JOIN OptA USING (pk) LEFT JOIN OptB USING (pk); SELECT * FROM Main LEFT JOIN OptB USING (pk) LEFT JOIN OptA USING (pk); SELECT Main.pk, name, alpha, beta FROM Main LEFT JOIN OptA ON Main.pk = OptA.pk LEFT JOIN OptB ON Main.pk = OptB.pk; Joining Main, OptA, and OptB omits Beta2: pk name alpha beta ---------- ---------- ---------- ---------- 1 One Alpha1 2 Two 3 Three 4 Four Alpha4 Beta4 Joining Main, OptB, and OptA omits Alpha1: pk name beta alpha ---------- ---------- ---------- ---------- 1 One 2 Two Beta2 3 Three 4 Four Beta4 Alpha4 Only by using ON instead of USING do we get the correct results: pk name alpha beta ---------- ---------- ---------- ---------- 1 One Alpha1 2 Two Beta2 3 Three 4 Four Alpha4 Beta4 I think this is basically the same issue as ticket #1637, but it's a more serious example. In that one, the query simply failed to compile. In this case, it seems to work, but gives you the wrong results. I've also tried this script in PostgreSQL 8.0.13. All three queries give (the same) correct results. _2007-Aug-08 17:34:27 by anonymous:_ {linebreak} The problem is that SQLite is transforming SELECT * FROM Main LEFT JOIN OptA USING (pk) LEFT JOIN OptB USING (pk); into SELECT Main.pk, name, alpha, beta FROM Main LEFT JOIN OptA ON Main.pk = OptA.pk LEFT JOIN OptB ON OptA.pk = OptB.pk; Here is a workaround to this bug that makes use of a subquery: select * from (SELECT * FROM Main LEFT JOIN OptA USING (pk)) LEFT JOIN OptB USING (pk); Conceivably all LEFT JOIN chains could be transformed into the above form, but that would decrease performance due to the intermediate result set of the subquery. Having it work without the subquery is tricky since sqlite must deduce that the last USING (pk) is equivalent to the first pk in the chain of joined tables, namely Main.pk, and not OptA.pk. Joe Wilson #e8e8bd 2580 active 2007 Aug anonymous Pending anonymous 1 2 Can't open a query if text to search is Greek for example: SELECT * FROM mytable WHERE mycolumn LIKE '%some greek text%' I get wrong results, using the 3.4.2 version. No problem instead using other earlier version. I tested only in Windows. #e8e8bd 2288 active 2007 Apr anonymous Pending 4 2 FTS does not support REPLACE Simple to replicate: CREATE VIRTUAL TABLE fts_table USING fts2(text); INSERT OR REPLACE INTO fts_table (rowid, text) VALUES (1, 'text1'); INSERT OR REPLACE INTO fts_table (rowid, text) VALUES (1, 'text2'); The first insert succeeds, the second fails. Also occurs with fts1. _2007-Apr-10 15:27:10 by anonymous:_ {linebreak} http://www.mail-archive.com/sqlite-users%40sqlite.org/msg23865.html #f2dcdc 2294 active 2007 Apr anonymous Pending 2 1 segfault when destroying lock on WinCE with threads DestroyLock emulation on WinCE platform releases the zDeleteOnClose file outside the mutex acquire section. This lead to frequent segfault when working with several databases concurrently. Patch simply consists in moving code: if( pFile->zDeleteOnClose ){ DeleteFileW(pFile->zDeleteOnClose); sqliteFree(pFile->zDeleteOnClose); pFile->zDeleteOnClose=NULL; } from winClose() (os_win.c:980) to winceDestroyLock(), inside scope of winceMutexAcquire(pFile->hMutex): /* De-reference and close our copy of the shared memory handle */ UnmapViewOfFile(pFile->shared); CloseHandle(pFile->hShared); + if( pFile->zDeleteOnClose ){ + DeleteFileW(pFile->zDeleteOnClose); + sqliteFree(pFile->zDeleteOnClose); + pFile->zDeleteOnClose=NULL; + } /* Done with the mutex */ winceMutexRelease(pFile->hMutex); CloseHandle(pFile->hMutex); pFile->hMutex = NULL; _2007-Apr-13 00:21:33 by anonymous:_ {linebreak} Check in [3836] fixes it for me. eTcl regression tests, related to running several sqlite database concurrently in several threads, are now passed while they were frequently segfaulting without it. To help being confident with this _blind_ commit, let's mention that exactly same patch has been introduced in eTcl built since a couple of monthes, to fix issue reported by WM2003 users, and all reported a correct fix. However, I did suggest the patch, so testing and feedback from others may help :-) Also, note that [3836] has a typo, requesting feedback in ticket #2249 instead of #2294 ---- _2007-Sep-25 03:24:09 by anonymous:_ {linebreak} The fix doesn't work and should be reverted. Temporary files do not create locks, so when they are closed and hMutex is null, the winceDestroyLock() file is never called and the temporary files are not cleaned up properly. #cfe8bd 2297 active 2007 Apr anonymous Pending drh 3 3 uninitialized var (with patch) Warnings with amalgamation and NDEBUG. _2007-Apr-12 21:21:29 by drh:_ {linebreak} I looked at the suggested changes and I didn't find any cases where it really was possible to use an uninitialized variable, at least not in a harmful way. Did I overlook something, or is this ticket just a request to silence compiler warnings? ---- _2007-Apr-13 00:08:36 by anonymous:_ {linebreak} vdbe.c with n, n64, payloadSize and payloadSize64{linebreak} sqlite3BtreeKeySize,sqlite3BtreeLast return are not checked. You can not be sure the pointer passed as second argument will be init depending on the return of restoreOrClearCursorPosition (btree.c).{linebreak} page.c with ro{linebreak} Compiled with -DNDEBUG, the return of sqlite3OsOpenReadWrite is not checked before making a move with 'ro'. For sContext.zAuthContext in delete.c/update.c, you're the one. gcc (compiler in general) warnings are quite usefull, i don't think it's a good idea to ignore them and accumulate danger. Perhaps one day, one line in a subroutine will modify some tricky behavior and (re)raise a previous checked warning, making it completely normal and 'under control'. #f2dcdc 2303 active 2007 Apr anonymous Pending 1 1 Encrypted databases: No page cache due to problem in pagerSharedLock With codec encryption enabled, =pagerSharedLock= always invalidates the page cache, even if no changes have occured since the cache was last valid and it would be safe to retain the cached pages. This in fact disables the newly improved page cache for encrypted databases and slows down performance. The problem occurs because =pagerSharedLock= reads the change counter directly from the database file without codec decryption. Since the codec always encrypts full pages, the 4 bytes at offset 24 are read as encrypted data and do not match =Pager->iChangeCount=. To solve, codecs would be required to store the 4 bytes at offset 24 of page 1 unencrypted. This would, however, render those 4 bytes vulnerable to attacks. It would therefore be more secure if =pagerSharedLock= could decrypt page one prior to extracting the change counter. Check-in [3844] does not fix the problem to reset the cache if the codec is changed but the database file is not. The following procedure for opening an encrypted database no longer works with the improved page cache: *: Open an encrypted database. Do not set a key yet as we (pretend to) believe that the database is not encrypted. *: Access the DB for reading. This returns =SQLITE_NOTADB=, so we conclude that the DB is encrypted. *: Attach the proper codec using =sqlite3CodecAttach=. *: Access the DB again. *Problem:* This still returns =SQLITE_NOTADB= because the old page cache is still in use and is not reloaded. The codec change is not detected because the pager checks the unencrypted DB file instead of the decrypted page. The file of course did not change, but the decrypted page did because of the new codec. The cache should therefore be cleared. A workaround would be possible if =sqlite3CodecAttach= could reset the page cache. Unfortunately, the method to do so (=pager_reset=) is static to pager.c. It seems that there once was an external function =sqlite3PagerReset= (it is still defined in pager.h), but its implementation is unfortunately no longer available. Could this be fixed in a way that =pagerSharedLock= checks the decrypted page 1 to see if the database has been modified or, alternatively, by reverting the static =pager_reset= back to the external =sqlite3PagerReset=? #f2dcdc 2320 active 2007 Apr anonymous Pending drh 1 1 sqlite3_open(sFN_with_umlaut) Do it in a standard MS Visual Studio Project:
#ifdef SQLITE_UNICODE_UPPERLOWERFUNCS #define WCHAR_T_SIZE sizeof(wchar_t) #if (WCHAR_T_SIZE == 2) #define MAXUPPERLOWERCHAR_AVAIL 0x0000ffff #else // (WCHAR_T_SIZE == 4) #define MAXUPPERLOWERCHAR_AVAIL 0x7fffffff #endif // (WCHAR_T_SIZE == 2) #define TOLOWERSQLFUNC(c) unicode_tolower #define TOUPPERSQLFUNC(c) unicode_toupper int unicode_tolower(const int c) { wchar_t buff [2]; if (c > MAXUPPERLOWERCHAR_AVAIL) return c; buff[0] = (wchar_t) c; buff[1] = 0; _wcslwr(buff); return (int) buff[0]; } int unicode_toupper(const int c) { wchar_t buff [2]; if (c > MAXUPPERLOWERCHAR_AVAIL) return c; buff[0] = (wchar_t) c; buff[1] = 0; _wcsupr(buff); return (int) buff[0]; } #else // SQLITE_UNICODE_UPPERLOWERFUNCS #define TOLOWERSQLFUNC(c) (c > 255 ? c : tolower(c)) #define TOUPPERSQLFUNC(c) (c > 255 ? c : toupper(c)) #endif // SQLITE_UNICODE_UPPERLOWERFUNCS /* ** Implementation of the upper() and lower() SQL functions. */ static void upperFunc(sqlite3_context *context, int argc, sqlite3_value **argv){ const unsigned char *zIn, *zInTerm; unsigned char *z, *zOut; int c, l; if( argc<1 || SQLITE_NULL==sqlite3_value_type(argv[0]) ) return; zIn = sqlite3_value_text(argv[0]); if( zIn==0 ) return; l = sqlite3_value_bytes(argv[0]); zInTerm = &zIn[l]; /* When converting case, the maximum growth results from ** translating a 1-byte UTF-8 character to a 4-byte UTF-8 character. */ zOut = sqliteMalloc( l * 4 ); z = zOut; while( zIn