bgcolor # Type Status Created By Subsys Changed Assigned Svr Pri Title _Description _Remarks #f2dcdc 2916 code active 2008 Feb anonymous 2008 Feb 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 #f2dcdc 2907 code active 2008 Jan anonymous 2008 Jan 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) #f2dcdc 2898 code active 2008 Jan anonymous 2008 Jan 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 #f2dcdc 2897 code active 2008 Jan anonymous 2008 Jan 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. #c8c8c8 2896 code closed 2008 Jan anonymous 2008 Jan 1 1 FTS3 misses document, but finds non-matching ones instead The following script presents a severe bug in FTS3 where it does not find a document even though it is in the index. Instead it finds other documents which do not match the search criteria. The log below was generated by the original SQLite3.exe 3.5.4 downloaded from the web-site. It shows that the entry 'A-123' is present in the table but is not matched by the query. It also demonstrates that non-matching entries are found instead. DROP TABLE IF EXISTS fts3; CREATE VIRTUAL TABLE fts3 using fts3 (a); INSERT INTO fts3 VALUES ('A-123'); SELECT * FROM fts3; A-123 SELECT * FROM fts3 WHERE fts3 MATCH 'A-123'; INSERT INTO fts3 VALUES ('A-12'); SELECT * FROM fts3 WHERE fts3 MATCH 'A-123'; A-12 INSERT INTO fts3 VALUES ('A-1'); SELECT * FROM fts3 WHERE fts3 MATCH 'A-123'; A-12 A-1 Here is the SQL only (without output, for easy reproduction): DROP TABLE IF EXISTS fts3; CREATE VIRTUAL TABLE fts3 using fts3 (a); INSERT INTO fts3 VALUES ('A-123'); SELECT * FROM fts3; SELECT * FROM fts3 WHERE fts3 MATCH 'A-123'; INSERT INTO fts3 VALUES ('A-12'); SELECT * FROM fts3 WHERE fts3 MATCH 'A-123'; INSERT INTO fts3 VALUES ('A-1'); SELECT * FROM fts3 WHERE fts3 MATCH 'A-123'; _2008-Jan-17 19:36:18 by anonymous:_ {linebreak} Sorry, I must have been dreaming: This is of course *not a bug* but the expected behavior. The '-' character is supposed to exclude the word following it. My sincere apologies if this has caused doubt or uncertainties to anyone! #c8c8c8 2895 build closed 2008 Jan anonymous 2008 Jan 3 1 Please describe how to run an indvidual test out of the test suite Please describe how to run an individual test out of the test suite? Thanks. For example, how to run the test incrvacuum-ioerr-1.31.4 _2008-Jan-17 17:14:12 by drh:_ {linebreak} Tickets are for reporting bugs, not asking questions about how to compile or operate SQLite. For questions of that nature, please post comments on the SQLite mailing list. See http://www.sqlite.org/support.html. PS: You cannot run individual tests. You have to run an entire script. #cfe8bd 2894 code fixed 2008 Jan anonymous 2008 Jan 1 1 virtual table calls Next() and Column() after Eof() returns true I saw this problem in my virtual table implementation. When I had a query that used a left outer join on a column that could be NULL, Next() and Column() were called after Eof() had returned true. I tried to reproduce the problem using test_schema from the source distribution which resulted in a segmentation fault, and I assume it's the same defect. Here is a sample script: .load test_schema.so create virtual table x using schema; -- this produces results select * from x where dflt_value is null; -- this produces a segmentation fault select * from x lhs left outer join x rhs on lhs.dflt_value = rhs.dflt_value; #f2dcdc 2893 code active 2008 Jan anonymous 2008 Jan 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? #c8c8c8 2890 build closed 2008 Jan anonymous 2008 Jan 1 1 Assistance request fixing six 3.5.4 i/o test failures Failures include exclusive-ioerr-2.280.4 exclusive-ioerr-2.281.4 exclusive-ioerr-2.282.4 incrvacuum-ioerr-1.31.4 io-4.1 io-4.2.3 Please let me know how to run individual tests, so I can debug these and submit fix suggestions. Thanks. _2008-Jan-15 21:50:52 by drh:_ {linebreak} These errors do not appear when the tests are run individually. They are only reproducible when you run the complete test suite. ---- _2008-Jan-16 03:15:50 by anonymous:_ {linebreak} Please give an example of how to run one individual test. Thanks. #cfe8bd 2883 code fixed 2008 Jan anonymous 2008 Jan 1 1 compile error - missing data I have original sqlite 3.2.8 source code in external SVN and a dashboard has encountered the following compile error win gcc 4.x: "shell.c:364: error: format not a string literal and no format arguments" See http://mail.kde.org/pipermail/kde-dashboard/2008-January/008494.html Any SQLite newer is addected as well. The patch below fixes misused fprintf. Index: shell.c{linebreak} ==================================================================={linebreak} --- shell.c (revision 761314){linebreak} +++ shell.c (working copy){linebreak} @@ -361,7 +361,7 @@{linebreak} output_c_string(p->out, z);{linebreak} }{linebreak} if( bSep ){{linebreak} - fprintf(p->out, p->separator);{linebreak} + fprintf(p->out, "%s", p->separator);{linebreak} }{linebreak} }{linebreak} #f2dcdc 2881 build active 2008 Jan anonymous 2008 Jan 1 1 Latest sqlite-3.5.4 build fail on latest Fedora 2.6.23.12-52.fc7 Two test cases fail. io-4.1... Expected: [3] Got: [2] io-4.2.1... Ok io-4.2.2... Ok io-4.2.3... Expected: [3] Got: [2] io-4.3.1... Ok Let me know how to run individual test cases and how this might be fixed. Here's how I built sqlite using latest CVS. If something is wrong here, let me know and I'll rebuild/retest. I'm building on latest Fedora fc7. Thanks. _______ net1#uname -a Linux net1.coolsurf.com 2.6.23.12-52.fc7 #1 SMP Tue Dec 18 20:27:10 EST 2007 x86_64 x86_64 x86_64 GNU/Linux net1#build_sqlite mkdir -p /build/work/sqlite-3.5.4 cd /build/work/sqlite-3.5.4 unset CDPATH export CFLAGS='-pipe -O3 -g -DSQLITE_DISABLE_DIRSYNC=1 -Wall' rm -rf bld cvs -d :pserver:anonymous@www.sqlite.org:/sqlite -r update . mkdir bld cd bld ../configure --prefix=/common/pkgs/sqlite-3.5.4 --enable-tcl --with-tcl=/usr/lib64 --enable-threadsafe --enable-threads-override-locks make groupadd vuser || /bin/true useradd -M -g vuser -d /vhost/davidfavor.com/users/david -s /bin/zsh david || /bin/true useradd -M -g vuser -d /vhost/livefeast.com/users/yemiah -s /bin/zsh yemiah || /bin/true chown david:vuser -R .. su -c "make test" david _2008-Jan-11 17:18:52 by anonymous:_ {linebreak} Same tests still fail with CVS of today around 11AM CST. ---- _2008-Jan-11 17:41:55 by drh:_ {linebreak} FWIW, both those test cases pass on SuSE 10.1. I do not understand why they are failing on Fedora. But in any event, the tests in question are verifying logic that implements an optimization that is not used on Fedora, ever. So the failures are of no consequence. If those are the only two tests that fail, then you can safely use the build for whatever it is you are trying to do. ---- _2008-Jan-11 19:16:19 by anonymous:_ {linebreak} Failures when 'make fulltest' built with CFLAGS of '-pipe -O3 -g -Wall -DSQLITE_DISABLE_DIRSYNC=1 -DSQLITE_MEMDEBUG' exclusive-malloc-1.transient.746...make: *** [fulltest] Segmentation fault Failures when 'make fulltest' built with CFLAGS of '-pipe -O3 -g -Wall -DSQLITE_DISABLE_DIRSYNC=1' Skipping malloc tests: not compiled with -DSQLITE_MEMDEBUG... 6 errors out of 61998 tests Failures on these tests: exclusive-ioerr-2.280.4 exclusive-ioerr-2.281.4 exclusive-ioerr-2.282.4 incrvacuum-ioerr-1.31.4 io-4.1 io-4.2.3 All memory allocations freed - no leaks Maximum memory usage: 14376554 bytes Pre 3.5.x builds work fine on Fedora. If you're open to debugging all these, I'd like to go through and resolve all these one by one, so Fedora has a clean build/fulltest. Please let me know how to run each test individually and I'll try to figure out the problem with each. Thanks. #cfe8bd 2880 code fixed 2008 Jan anonymous 2008 Jan 1 1 problem with reals bigger then 1e18 We've been reported (and confirmed) that there might be an isue using the Windows sqlite3 dll on large numbers. When defining fields as real then inserting data like 1e18 goes well, but 1e19 generates an error about being not a valid float. If you enter the data using sqlite3.exe there's no problem, retrieving the data is also working well. The problem arises when calling sqlite3step, the prepare does not give any error. We've checked and the same error appears using sqlite administrator (which is a Delphi development too, but using ZEOS). Since it goes wrong within sqlite3step this might be a sqlite isue? albert drent aducom software _2008-Jan-09 13:56:01 by drh:_ {linebreak} You write: "If you enter the data using sqlite3.exe there's no problem." But sqlite3.exe uses the sqlite3_prepare() and sqlite3_step() interface the same as anything else. So if sqlite3.exe is working, that suggests the problem is in your program, and not in the SQLite core. If you can provide a specific example of what is not working we can investigate this problem further. But based on the information provide, we are unable to reproduce the problem and suspect the problem is in the application, not in SQLite. ---- _2008-Jan-09 15:48:49 by anonymous:_ {linebreak} Delphi has been known to futz with the FPU registers. It's possible that Delphi is changing the FPU accuracy and causing this problem. ---- _2008-Jan-09 19:49:57 by anonymous:_ {linebreak} I don't know where the problem lies and will investigate further. It's not a fpu isue since it's a ansistring containing the string, being prepared and executed. There hardly any Delphi involved here. I'll create a small app in Delphi showing the problem and report this issue on Delphi forums too. The string is somewhat like 'insert into demo (myval) value (1e19)' Will come back here. Albert ---- _2008-Jan-10 13:47:28 by anonymous:_ {linebreak} Thanks to sasa: he found out the following It appears to be a problem in used C compiler. Following script is tested in sqlite 3-5-4 shell: create table t1 (ID integer primary key, v1 double); insert into t1(v1) values(1e18); insert into t1(v1) values(1e19); This works correctly on linux compiled with GNU compiler 4.1.2. On windows with MinGW 3.2.0 works correctly too, however it fails when shell is compiled with BCC 5.5.1. Without detail check I can only assume there is a problem in representing and using 8 and 6 bytes floats together in SQLite without explicit casting BCC may require. Or this may be a limitation of BCC 5.5.1 or a bug. Without detail check those are most probably causes. ---- _2008-Jan-10 14:01:30 by drh:_ {linebreak} SQLite does not use any "6 byte floats". I'm not sure what a 6 byte float is. The code in SQLite is suppose to be ANSI C and casts that are not required by ANSI C are generally omitted. If you find an exception to this please let us know. If BCC requires casts above and beyond what is required by ANSI C, then that is a bug in BCC. I assume then, that this is a bug in BCC. I do not have access to BCC so there is nothing I can do to fix it. If you can suggest patches that will work around the BCC bug (for example, some unnecessary explicit casts) we will consider adding them to the core. But I am afraid it will be up to you to supply those patches, since we do not have BCC available to experiment with and SQLite works perfectly for every compiler we have access to. ---- _2008-Jan-10 18:34:56 by anonymous:_ {linebreak} Funny thing is that if the dll is created with a gnu compiler it seems to work. Can you tell me with what compiler the dll (w.o. tcl binding) has been build? ---- _2008-Jan-10 20:16:12 by anonymous:_ {linebreak} This is the reply of sasa, it's way ahead of me... Unfortunately, I have no more time to test any DLL since I do not using any I did not compiled myself. As I understand vac, that one from official SQLite site have this bug. In the signature of DLL can be seen which compiler was used for compilation. BCC 5.5.1 is free and can freely be downloaded from official Borland site by anyone. This is I prepared to add to the ticket, but you can add yourself. I have spent all my spare time for now. Since it do not break anything it will be probably included in official version:
/* ** The MEM structure is already a MEM_Real. Try to also make it a ** MEM_Int if we can. */ SQLITE_PRIVATE void sqlite3VdbeIntegerAffinity(Mem *pMem){ assert( pMem->flags & MEM_Real ); assert( pMem->db==0 || sqlite3_mutex_held(pMem->db->mutex) ); pMem->u.i = pMem->r; // <- This causing termination for values >= 1e19 if( ((double)pMem->u.i)==pMem->r ){ pMem->flags |= MEM_Int; } }This conversion line causing BCC 5.5.1 run-time to raise a conversion error if values are >= 1e19. It is necessary a clean way to convert double to integer/int64. If nothing else, this will solve the problem (code is not essential for correct functionality):
/* ** The MEM structure is already a MEM_Real. Try to also make it a ** MEM_Int if we can. */ SQLITE_PRIVATE void sqlite3VdbeIntegerAffinity(Mem *pMem){ assert( pMem->flags & MEM_Real ); assert( pMem->db==0 || sqlite3_mutex_held(pMem->db->mutex) ); #ifndef __BORLANDC__ pMem->u.i = pMem->r; if( ((double)pMem->u.i)==pMem->r ){ pMem->flags |= MEM_Int; } #endif }[ Bijgewerkt do jan 10 2008, 08:49 ] SZUTILS - link ---- _2008-Jan-11 10:23:37 by anonymous:_ {linebreak} In "sqlite - Check-in [4705]" you have tryed to "attempt to work around this bug in the Borland compiler + ** by moving the value into a temporary variable first so that if + ** the assignment into the integer really does corrupt the right-hand + ** side value, it will corrupt a temporary variable that we do not + ** care about." First of all, thank you for attempt to solve the issue. I will first write several facts: 1. Statically linked SQLite 3.5.4 commandline shell (aka sqlite3.exe) is compiled and SQL script worked fine on Windows with MinGW 3.2.3, however not with BCC 5.5.1 (without any special compiler options). 2. Library (aka sqlite3.dll) compiled with MinGW 2.95 (from official SQLite site), MinGW 3.2.3, BCC 5.5.1 and probably other compilers for Windows does FAIL executing provided SQL script only with large floats >=1e19. With MinGW 3.2.3, library was created from official SQLite 3.5.4 distribution with following script: sh configure make dll 3. Both (integer and double) have 8 byte size in BCC. BCC is ANSI C complient compiler. 4. It is not the fact that BCC corrupt right-hand side value. It fail trying to convert values >=1e19 and assign it to int64 variable. This will raise invalid floating-point operation exception for most C compilers. 5. Since front-end application use only DLL (compiled by several different C compilers, including provided from offical SQLite site) to execute an SQL command by sqlite_exec(), it is nothing which connect the bug and the application itself (made in Delphi or C). Current attempt in 4705 will not work from two reasons: 1. The same conversion logic is used. 2. Variable definition in the middle of the function in not allowed by ANSI C. This will fail not only on BCC, but on any ANSI C compiler. According to upper, conclusion is clear that this is not a bug in BCC, but in all C compiles for Windows - or in conversion double to integer currently used. Thank you for your time. Sasa Zeman ---- _2008-Jan-11 13:22:27 by drh:_ {linebreak} The purpose of the sqlite3VdbeIntegerAffinity() function is to convert a floating point value into a 64-bit integer value if and only if that conversion can be done without loss of precision. Perhaps Sasa Zeman or somebody else can suggest a way to do that successfully on BCC 5.5.1. (The current implementation appears to work on every other compiler.) Or perhaps this is not a compiler issue at all, but an instance of Delphi messing with FPU registers, as was suggested by the anonymous comment on 2008-Jan-09. Please note that I do not own a machine that runs windows, and I am unwilling to purchase one for the purpose of fixing this issue. So I cannot download and install BCC 5.5.1 or Delphi. And thus, I cannot reproduce the problem here. So if you want this problem resolved, you are going to need to suggest code for resolving it. Completely disabling the sqlite3VdbeIntegerAffinity() function is not an acceptable resolution because that breaks quite a few other things. ---- _2008-Jan-11 14:00:05 by anonymous:_ {linebreak} AFAIK it is a general compiler isue on Windows. Reading Sasa's comment this is NOT a Delphi isue since the problem lies in SQLite3.dll. In fact, the tests who are shown here are all done in C++. What I would like to know is how the Windows dll on this website and which DOES have this problem, is created and with what compiler. Appearantly you haven't created this one since you do not own a Windows compiler (?). Then we are in pursuit of an answer and solution. Ignoring the problem is not an option IMHO and would be bad news to all on the Windows community. The Borland C++ compiler with the patch here (SASA does this work?) is completely FREE and doesn't need to be purchased. But if this isue cannot be resolved, and I'm not a C++ guru, then there should be a note here that on Windows the precision is limited to 10e18??? There are now three people busy with this, sure there must be a solution in conjunction with all of us? It's in everyones benefit if this isue can be resolved in stead of ignored. ---- _2008-Jan-11 14:36:11 by drh:_ {linebreak} The windows binaries on the SQLite website are cross-compiled on a Linux box using Mingw-GCC. ---- _2008-Jan-11 17:49:26 by anonymous:_ {linebreak} Which steps do I need to take to reproduce the problem? It seems to work just fine with sqlite3.exe 3.5.0 on Windows XP: sqlite> create table t1 (ID integer primary key, v1 double); sqlite> insert into t1(v1) values(1e18); sqlite> insert into t1(v1) values(1e19); sqlite> select * from t1; 1|1.0e+18 2|1.0e+19 Could someone please post a little C-code which exhibits the problem? Thanks! ---- _2008-Jan-11 18:10:29 by anonymous:_ {linebreak} Thank you for your respond. In order to reproduce the problem, you can create very simple C program using the DLL, which execute upper script by each line, using sqlite3_exec() command. Thank you for your time. Sasa ---- _2008-Jan-11 19:05:51 by anonymous:_ {linebreak} The following: + static const i64 maxInt = (((i64)0x7fffffff)<<32)|0xffffffff; + static const i64 minInt = ((i64)0x80000000)<<32; + + if( r<(double)minInt ){ + return minInt; + }else if( r>(double)maxInt ){ runs faster on my compiler if you do this: + static const double maxInt = (((i64)0x7fffffff)<<32)|0xffffffff; + static const double minInt = ((i64)0x80000000)<<32; + + if( r
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." #c8c8c8 2868 code closed 2008 Jan anonymous 2008 Jan 1 1 Encryption user can't link latest SQLite I have licensed the encryption package and have it working with an earlier version of SQLite. I downloaded the current stable release, copied it over the previous version and compiled it. It compiled fine, but now the pager has unresolved externals, it seems many of the function names have changed. Please advise. Thanks Tim _2008-Jan-03 14:00:20 by drh:_ {linebreak} The license is perpetual with unlimited free updates. You just have to send us private email and we will send you back the latest code. #c8c8c8 2861 doc closed 2007 Dec anonymous 2008 Jan 1 1 How do I connect to Crystal Reports - I am using vs2003 & the Finisar Not sure of the right venue for this question. My apologies if this is not the right place. I am developing a simple accounting application for a non-profit organisation. I am using vs2003 and programming in c#. To date I have used Access (Jet) database files and the Crystal Report Generator built in to VS2003. I thought SQLite would be a better choice than the Access database - simple to change my c# code using the Finisar data provider, but I am at a loss to see how I can use this provider in Crystal. Is there any way to get this provider onto the list of ADO DB providers in Crystal - or any other approach I could use. If I cannot use Crystal is there an alternative for producing reports which can link to SQLite. Any help much appreciated. Roger Lovelock _2007-Dec-29 13:58:55 by anonymous:_ {linebreak} Not a bug. Better ask this on the mailing list. To subscribe to the list, send a message to:
capi3-13-4... Ok capi3-13-5...*** glibc detected *** capi3-14.1-misuse... Ok capi3-15.1... Error: error code SQLITE_TOOBIG (18) does not match sqlite3_errcode SQLITE_OK (0) capi3-15.2... Expected: [2] Got: [0] capi3-15.3... Expected: [SQLITE_OK] Got: [SQLITE_MISUSE] capi3-15.4... Error: (21) library routine called out of sequence capi3-15.5... Error: (21) library routine called out of sequence capi3-15.6... Error: (21) library routine called out of sequence capi3-15.7... Error: (21) library routine called out of sequence capi3-15.8... Error: (21) library routine called out of sequence capi3-16.1... Error: (21) library routine called out of sequence capi3-16.2... Error: (21) library routine called out of sequence capi3-16.3... Error: (21) library routine called out of sequence capi3-16.4... Error: (21) library routine called out of sequence capi3-17.1... Error: (21) library routine called out of sequence capi3-17.2... Ok capi3-17.3...*** glibc detected *** ./testfixture: double free or corruption (!prev): 0x0812d650 ***#cfe8bd 2846 build fixed 2007 Dec anonymous 2007 Dec 1 1 --disable-tcl does not work rm -rf tsrc mkdir -p tsrc cp ./src/alter.c ./src/analyze.c ./src/attach.c ./src/auth.c ./src/btmutex.c ./src/btree.c ./src/btree.h ./src/build.c ./src/callback.c ./src/complete.c ./src/date.c ./src/delete.c ./src/expr.c ./src/func.c ./src/hash.c ./src/hash.h ./src/insert.c ./src/journal.c ./src/legacy.c ./src/loadext.c ./src/main.c ./src/malloc.c ./src/mem1.c ./src/mem2.c ./src/mem3.c ./src/mem4.c ./src/mutex.c ./src/mutex_os2.c ./src/mutex_unix.c ./src/mutex_w32.c ./src/os.c ./src/os_unix.c ./src/os_win.c ./src/os_os2.c ./src/pager.c ./src/pager.h ./src/parse.y ./src/pragma.c ./src/prepare.c ./src/printf.c ./src/random.c ./src/select.c ./src/shell.c ./src/sqlite.h.in ./src/sqliteInt.h ./src/table.c ./src/tclsqlite.c ./src/tokenize.c ./src/trigger.c ./src/utf.c ./src/update.c ./src/util.c ./src/vacuum.c ./src/vdbe.c ./src/vdbe.h ./src/vdbeapi.c ./src/vdbeaux.c ./src/vdbeblob.c ./src/vdbefifo.c ./src/vdbemem.c ./src/vdbeInt.h ./src/vtab.c ./src/where.c ./ext/fts1/fts1.c ./ext/fts1/fts1.h ./ext/fts1/fts1_hash.c ./ext/fts1/fts1_hash.h ./ext/fts1/fts1_porter.c ./ext/fts1/fts1_tokenizer.h ./ext/fts1/fts1_tokenizer1.c sqlite3.h ./src/btree.h ./src/btreeInt.h ./src/hash.h ./src/sqliteLimit.h ./src/mutex.h opcodes.h ./src/os.h ./src/os_common.h ./src/sqlite3ext.h ./src/sqliteInt.h ./src/vdbe.h parse.h ./ext/fts1/fts1.h ./ext/fts1/fts1_hash.h ./ext/fts1/fts1_tokenizer.h ./src/vdbeInt.h tsrc cp: warning: source file `./src/btree.h' specified more than once cp: warning: source file `./src/hash.h' specified more than once cp: warning: source file `./src/sqliteInt.h' specified more than once cp: warning: source file `./src/vdbe.h' specified more than once cp: warning: source file `./ext/fts1/fts1.h' specified more than once cp: warning: source file `./ext/fts1/fts1_hash.h' specified more than once cp: warning: source file `./ext/fts1/fts1_tokenizer.h' specified more than once cp: warning: source file `./src/vdbeInt.h' specified more than once rm tsrc/sqlite.h.in tsrc/parse.y cp parse.c opcodes.c keywordhash.h tsrc tclsh ./tool/mksqlite3c.tcl make: tclsh: Command not found make: *** [sqlite3.c] Error 127 _2007-Dec-17 00:45:26 by anonymous:_ {linebreak} Seems the same problem as #2845 #f2dcdc 2844 build active 2007 Dec anonymous 2007 Dec 4 1 lemon is being built without respecting LDFLAGS lemon is being built without respecting LDFLAGS. I'm attaching a patch which fixes this bug. In other words, why should we fix this? What problem is it causing? _2007-Dec-17 16:22:19 by drh:_ {linebreak} Why is this important? What LDFLAGS settings might a user want to carry through into lemon? ---- _2007-Dec-17 18:00:59 by anonymous:_ {linebreak} > Why is this important? It is considered to be be good practice to respect user's LDFLAGS. A user might want to have all executables and libraries built with identical LDFLAGS. > What LDFLAGS settings might a user want to carry through into lemon? A user might have LDFLAGS="-Wl,-O1,--hash-style=gnu,--sort-common" You can read http://lwn.net/Articles/192082/. Users can also use some other flags. > In other words, why should we fix this? What problem is it causing? It slightly increases the size of lemon executable and it slightly decreases performance. ---- _2007-Dec-17 18:04:31 by drh:_ {linebreak} lemon is used as an intermediate build tool in part of the SQLite build process. It is not a deliverable. If it runs a little slower or uses a little more memory, nobody cares. We only care if it gets the wrong answer. Is it ever possible that the lack of LDFLAGS support might result in lemon getting the wrong answer? ---- _2007-Dec-17 18:27:33 by anonymous:_ {linebreak} Can you comment on Lemon bug in #2835? It produces 2 different sqlite3.c files depending on your malloc implementation. ---- _2007-Dec-17 19:19:01 by anonymous:_ {linebreak} > lemon is used as an intermediate build tool in part of the > SQLite build process. It is not a deliverable. If it runs a > little slower or uses a little more memory, nobody cares. CFLAGS are respected when lemon is being built, so for consistency LDFLAGS also should be respected. (The comment above was not created by me.) #f2dcdc 2842 code active 2007 Dec anonymous 2007 Dec 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. #f2dcdc 2841 todo active 2007 Dec anonymous 2007 Dec 1 1 The sqlite mailing list has become overrun by trolls The sqlite mailing list is very useful. The S/N is at times a little high but nonetheless quite manageable. Recently (see the DeviceSQL thread) it got really bad. Would moderation be unacceptable during these periods of time where people feel the need to protect their ego's? The sqlite mailing list is primarily about sqlite (well, and lemon), not a marketing vector for other products? Surely they have their own lists and resources for that? #c8c8c8 2836 build closed 2007 Dec anonymous 2007 Dec 1 1 Undefined symbol 'OP_StackDepth' There is a error on current cvs version(12/12/07) with "Undefined symbol 'OP_StackDepth'" message. opcodes.h and opcodes.c havn't 'OP_StackDepth' definition currently. Sorry for my short English. _2007-Dec-13 07:41:07 by danielk1977:_ {linebreak} opcodes.c and opcodes.h are auto-generated files (generated based on vdbe.c). Likely your copies have not been regenerated since yesterdays modifications. Try running "make clean" before rebuilding. If that fails, manually remove opcodes.c and opcodes.h from your build directory. #cfe8bd 2835 code fixed 2007 Dec anonymous 2007 Dec 1 1 Lemon memory read/write errors
$ cat lemonbug.y phrase ::= foo AND A B C foo. phrase ::= foo AND A B C bar. foo ::= BIRD | CAT | DOG. bar ::= CAT | DOG. $ ./lemon lemonbug.y 1 parsing conflicts. $ head -23 lemonbug.out State 0: phrase ::= * foo AND A B C foo phrase ::= * foo AND A B C bar foo ::= * BIRD| CAT| DOG BIRD shift 2 shift 2 phrase accept foo shift 5 State 1: phrase ::= foo AND A B C * foo phrase ::= foo AND A B C * bar foo ::= * BIRD| CAT| DOG bar ::= * CAT| DOG BIRD shift 2 shift 2 reduce 134589256 ** Parsing conflict ** CAT shift 4 foo shift 9 bar shift 10 valgrind output: ==11245== Invalid read of size 1 ==11245== at 0x8049109: SetAdd (lemon.c:4080) ==11245== by 0x804B083: FindFirstSets (lemon.c:649) ==11245== by 0x8051919: main (lemon.c:1464) ==11245== Address 0x416980F is 0 bytes after a block of size 7 alloc'd ==11245== at 0x401F396: malloc (in /usr/lib/valgrind/x86-linux/vgpreload_memcheck.so) ==11245== by 0x804AEF3: SetNew (lemon.c:4057) ==11245== by 0x804AF7D: FindFirstSets (lemon.c:617) ==11245== by 0x8051919: main (lemon.c:1464) ==11245== ==11245== Invalid write of size 1 ==11245== at 0x804910C: SetAdd (lemon.c:4081) ==11245== by 0x804B083: FindFirstSets (lemon.c:649) ==11245== by 0x8051919: main (lemon.c:1464) ==11245== Address 0x416980F is 0 bytes after a block of size 7 alloc'd ==11245== at 0x401F396: malloc (in /usr/lib/valgrind/x86-linux/vgpreload_memcheck.so) ==11245== by 0x804AEF3: SetNew (lemon.c:4057) ==11245== by 0x804AF7D: FindFirstSets (lemon.c:617) ==11245== by 0x8051919: main (lemon.c:1464) ==11245== ==11245== Invalid read of size 4 ==11245== at 0x8048BF4: actioncmp (lemon.c:365) ==11245== by 0x8048DBC: merge (lemon.c:1577) ==11245== by 0x8048E6D: msort (lemon.c:1623) ==11245== by 0x80512C1: FindActions (lemon.c:966) ==11245== by 0x805194E: main (lemon.c:1479) ==11245== Address 0x416B298 is 4 bytes after a block of size 36 alloc'd ==11245== at 0x401F396: malloc (in /usr/lib/valgrind/x86-linux/vgpreload_memcheck.so) ==11245== by 0x804ABF2: State_new (lemon.c:4503) ==11245== by 0x8051015: getstate (lemon.c:759) ==11245== by 0x805113A: buildshifts (lemon.c:829) ==11245== by 0x805104E: getstate (lemon.c:766) ==11245== by 0x805113A: buildshifts (lemon.c:829) ==11245== by 0x805104E: getstate (lemon.c:766) ==11245== by 0x805113A: buildshifts (lemon.c:829) ==11245== by 0x805104E: getstate (lemon.c:766) ==11245== by 0x805113A: buildshifts (lemon.c:829) ==11245== by 0x805104E: getstate (lemon.c:766) ==11245== by 0x805113A: buildshifts (lemon.c:829) ==11245==_2007-Dec-12 18:00:29 by anonymous:_ {linebreak} Another grammar with parsing conflicts, different valgrind error:
article ::= blocks FIN. blocks ::= block. blocks ::= blocks block. block ::= NEWLINE. block ::= stanza. block ::= heading. block ::= stanza heading. heading ::= HEADING_START text HEADING_END. stanza ::= text. stanza ::= stanza NEWLINE text. text ::= textpiece. text ::= text textpiece. textpiece ::= TEXT. textpiece ::= LINK. ==11727== Conditional jump or move depends on uninitialised value(s) ==11727== at 0x804ADBB: CompressTables (lemon.c:3947) ==11727== by 0x8051A2C: main (lemon.c:1482) 3925 void CompressTables(lemp) 3926 struct lemon *lemp; 3927 { 3928 struct state *stp; 3929 struct action *ap, *ap2; 3930 struct rule *rp, *rp2, *rbest; 3931 int nbest, n; 3932 int i; 3933 int usesWildcard; 3934 3935 for(i=0; i---- _2007-Dec-12 18:14:11 by anonymous:_ {linebreak} "./lemon src/parse.y" also produces: ==12904== Conditional jump or move depends on uninitialised value(s) ==12904== at 0x80523AF: CompressTables (lemon.c:3947) ==12904== by 0x804B015: main (lemon.c:1482) ---- _2007-Dec-12 19:00:41 by anonymous:_ {linebreak} This appears to remedy the Conditional jump error for parse.y. The read/write errors in first grammar remains.nstate; i++){ 3936 stp = lemp->sorted[i]; 3937 nbest = 0; 3938 rbest = 0; 3939 usesWildcard = 0; 3940 3941 for(ap=stp->ap; ap; ap=ap->next){ 3942 if( ap->type==SHIFT && ap->sp==lemp->wildcard ){ 3943 usesWildcard = 1; 3944 } 3945 if( ap->type!=REDUCE ) continue; 3946 rp = ap->x.rp; 3947 if( rp->lhsStart ) continue;
--- tool/lemon.c 5 Oct 2007 16:16:36 -0000 1.51 +++ tool/lemon.c 12 Dec 2007 19:02:37 -0000 @@ -2100,6 +2100,7 @@ to follow the previous rule."); psp->prevrule = 0; }else{ int i; + rp->lhsStart = 0; rp->ruleline = psp->tokenlineno; rp->rhs = (struct symbol**)&rp[1]; rp->rhsalias = (char**)&(rp->rhs[psp->nrhs]);---- _2007-Dec-13 23:44:45 by anonymous:_ {linebreak} I originally thought this reading the unitialized memory for rp->lhsStart lemon parser bug was completely inconsequential, but now I see if I apply the following patch and run lemon against parse.y it generates a completely different parse.c file. If the lemon is built with the following patch and your run parse.y against it, lemon will generate a parse.c that is 275868 bytes in size:
diff -u -3 -p -r1.51 lemon.c --- tool/lemon.c 5 Oct 2007 16:16:36 -0000 1.51 +++ tool/lemon.c 13 Dec 2007 23:37:39 -0000 @@ -2100,6 +2100,7 @@ to follow the previous rule."); psp->prevrule = 0; }else{ int i; + rp->lhsStart = 0xffff; // *** change to zero and rerun rp->ruleline = psp->tokenlineno; rp->rhs = (struct symbol**)&rp[1]; rp->rhsalias = (char**)&(rp->rhs[psp->nrhs]);While if the rp->lhsStart value is set to 0, it will produce a completely different parse.c file of 122960 bytes. #c8c8c8 2834 event closed 2007 Dec anonymous 2007 Dec 2 1 Is Version 3.1.3 not compatible with 3.5.3 ? I created this table "CREATE TABLE CALCULATION (sceneid INTEGER, calcid INTEGER, data BLOB, timestamp INTEGER, PRIMARY KEY (sceneid, calcid))" with the sqlite version 3.1.3. And now I have updated to version 3.5.3 and it is not possible to read the data from table CALCULATION. But if I create the table only with one primary key (Primary key (sceneid)), than it is possible to read the data. Is it a bug in version 3.5.3 oder in 3.1.3? How can I read the data with a double primary key? _2007-Dec-11 18:43:51 by drh:_ {linebreak} All versions of SQLite are compatible back through version 3.0.0. If you think otherwise, please provide details and we will investigate. I generate a database using your schema and SQLite version 3.1.3 and it works just fine with SQLite version 3.5.3. Unable to recreate the problem... #cfe8bd 2833 doc fixed 2007 Dec anonymous 2007 Dec 5 1 typo on SQLite home page As pointed out by silvestru@molddata.md on the mailing list the phrase "This the homepage for SQLite - ..." should be changed to "This is the homepage for SQLite - ...". We fixed this yesterday. And the homepage is going to be completely replaced tomorrow morning, at which point the issue will become moot. #cfe8bd 2832 code fixed 2007 Dec anonymous 2007 Dec 1 1 Data corruption with UPDATE or DELETE clause ... On any UPDATE or DELETE operation, if a side effect causes other rows in the same table to be deleted, a stack leak can occur. In rare circumstances, database corruption can result. Side effect deletes might be caused by an OR REPLACE clause or by triggers. This bug appears to be in all prior versions of SQLite 3. The original problem description follows: ----- My scenario is rather simple - I have products table, and details table. Producs have unique column called code. Then I have transform table to renumber my products table. I used update or replace constraint, and my data got corrupted. Here's example, I will try to rewrite column names into english, sorry if I make some typo here: sql "create table products (code text primary key, former_code text, typy text, name text, warehouse text)" sql "create table transform (code text, name text, new_code text, former_code text, type text)" no indices (there are in newer version of my code, but simpler the case the better) And now sql code, which corrupts my data (contains a bit of REBOL code):
sql trim/lines { update or replace products set code = (select new_code from transform where transform.code=products.code), type = (select type from transform where transform.code=products.code) where exists (select code from transform where transform.code=products.code) }So - I just wonder - I am not fluent in SQL, so I am not sure I should update primary key in products that way? I expect this code would either update codes found for transformation, or it would fail, because such code to translate to would be already present on table, so that would be caught by "or replace" constraint, and such item would be replaced. So - what you suggest now? Is that a bug? Or am I behaving really badly to my data? :-) Tried with 3.5.2, 3.5.3, Windows Vista 32bit .... PS: This text is from my original post to ml - tried to subscribe to ml two times, no luck receiving any message, so posting directly to bugbase. Sorry if the bug is already known ... _2007-Dec-11 14:34:41 by drh:_ {linebreak} What makes you think your data is being corrupted, as opposed to merely transformed in exactly the way you have requested? In other words, why do you think this is a bug in SQLite? ---- _2007-Dec-12 08:22:19 by anonymous:_ {linebreak} If you can see that instead of product code you get characters you even can't write on your keyboard probably, then I think data is being corrupted :-) Here's a screenshot: {link: http://www.xidys.com/pekr/sqlite-insert-or-update-data-corruption.jpg corrupted data?} Cheers, Petr ---- _2007-Dec-12 08:35:14 by danielk1977:_ {linebreak} Can you post a database file and the SQL statement that is causing the corruption? This is likely an encoding problem. Are you inserting UTF-8 text into the database? And handling the returned data as UTF-8? ---- _2007-Dec-12 08:56:10 by anonymous:_ {linebreak} OK, before I post some data, I will try to describe what I did: In products table I have product numbers starting with "9", which are going to be translated into other numbers: 9xxxx.xx ---> 1xxxx.xx 9xxxx.xxA --> 1xxxx.xxA 9xxxx.xxBZ--> 9xxxx.xx I import all data from Excel .csv files. Encoding should be OK imo, as data live in one file. However, I noticed "bug" in my logic. Simply put, to make my life easier, I imported 9xxxx.xxBZ codes into my transform table, thinking that when those codes are at the end of the table, those are OK. But - my update clause (posted in my first request), does not "join data" that safe way, so actually what happens is - if the clause reaches 9xxxx.xxBZ code, it updates it to 9xxxx.xx code, but this code is then once again translated to 1xxxx.xx later in the process. If I don't import 9xxxx.xxBZ codes to the end of the table, my data remain being OK. Where can I send my database? I don't want it being public. Thanks a lot ... Petr ---- _2007-Dec-12 10:25:21 by anonymous:_ {linebreak} OK, here is a reduced recordset without sensitive data. {link: http://www.xidys.com/pekr/inventura.zip corruption data example} Just open the database and apply query as stored in query.txt ---- _2007-Dec-12 11:11:11 by anonymous:_ {linebreak} I can confirm the database corruption as provided in the sample. Before running the SQL in query.txt, =pragma integrity_check= returns 'ok', afterwards it returns these errors: rowid 156 missing from index sqlite_autoindex_products_1 rowid 159 missing from index sqlite_autoindex_products_1 rowid 160 missing from index sqlite_autoindex_products_1 rowid 162 missing from index sqlite_autoindex_products_1 rowid 163 missing from index sqlite_autoindex_products_1 Tested with CVS [4603]. ---- _2007-Dec-12 15:41:33 by anonymous:_ {linebreak} Regarding the test case in Check-in [4614], it produces the same expected result for both 3.5.3 and the latest CVS. No sign of database corruption or valgrind error for 3.5.3 on Linux. Are you certain this isolates the problem? ---- _2007-Dec-12 15:56:02 by drh:_ {linebreak} tkt2832.test provokes the problem if you use the OP_StackDepth opcode from check-in [4612]. The change in [4612], together with additional enhancements that will go in as part of this fix, is designed to head off this kind of bug in the future by making the VDBE very unforgiving to the kinds of code generator errors that caused this problem. #cfe8bd 2829 code fixed 2007 Dec anonymous 2007 Dec 1 1 Win32: temp files left over in temp area Seems that temp tables generated on the fly for large group by or order by queries do not have their files removed on CloseHandle. This is because the CreateFile is not done with the right flag. Result is that a lot of files are left over in temp directory, even when queries are run properly. This patch seems to fix it (added: SQLITE_OPEN_DELETEONCLOSE): if( flags & (SQLITE_OPEN_TEMP_DB | SQLITE_OPEN_TEMP_JOURNAL - | SQLITE_OPEN_SUBJOURNAL) ){ + | SQLITE_OPEN_SUBJOURNAL | SQLITE_OPEN_DELETEONCLOSE) ){ _2007-Dec-10 19:54:25 by drh:_ {linebreak} Yikes! I just checked and I have 174260 SQLite temp files in my /var/tmp folder! And cleaning up such a mess is challenging. You cannot do "rm etilqs_*" because the argument list is too long. Even "ls etilqs_*" doesn't work. Here is what I'm doing: ls -U | grep etliqs_ | while read i; do rm $i; done That will have to run for 20 minutes or so to clear out all of the dead files. This problem is serious enough to warrant a new release once we get it fixed. ---- _2007-Dec-10 20:06:32 by anonymous:_ {linebreak} I don't see these temp file on UNIX - which OS is affected by this bug and under what circumstance? ---- _2007-Dec-10 20:50:20 by drh:_ {linebreak} Hmmm. I finally got my /var/tmp folder cleaned up. But now when I rerun the regression suite, no new temp files appear. Perhaps those 174260 temp files came from some older bug that has now been fixed. ---- _2007-Dec-10 21:24:27 by anonymous:_ {linebreak} Did you by chance catch a glimpse of the date of the most recent undeleted tmp files on UNIX? ---- _2007-Dec-11 03:31:38 by drh:_ {linebreak} Sadly, I did not observe the mtimes on any of the files in /var/tmp. Recall that ls was not working very well for me (probably due to the large number of files). I was having to use the -U open (to prevent sorting) in order to get ls to work at all. And I did not have the presence of mind to add a -l to that. Oh well.... ---- _2007-Dec-11 05:26:45 by anonymous:_ {linebreak} find is your friend: find /var/tmp -type f -iname "etliqs_*" -print0 | xargs -r0 rm (yes, you can simplify this a whole lot if you make some assumptions but why bother be unsafe?) #cfe8bd 2827 code fixed 2007 Dec anonymous 2007 Dec 1 1 make test: vacuum2.test did not close all files: 1 Latest unmodified CVS source tree. Run "make test" to see the error.
vacuum2-1.1... Ok vacuum2-2.1... Ok vacuum2-2.1... Ok vacuum2-3.1... Ok vacuum2-3.2... Ok vacuum2-3.3... Ok vacuum2-3.4... Ok vacuum2-3.5... Ok vacuum2-3.6... Ok vacuum2-3.7... Ok vacuum2-3.13... Ok vacuum2-3.14... Ok vacuum2-3.15... Ok vacuum2-3.16... Ok vacuum2-3.17... Ok vacuum2.test did not close all files: 1 ... 1 errors out of 38209 tests Failures on these tests: vacuum2.test All memory allocations freed - no leaks Maximum memory usage: 14164890 bytes make: *** [test] Error 1Note: this does *not* happen if you only run vacuum2.test directory:
$ ./testfixture test/vacuum2.test vacuum2-1.1... Ok vacuum2-2.1... Ok vacuum2-2.1... Ok vacuum2-3.1... Ok vacuum2-3.2... Ok vacuum2-3.3... Ok vacuum2-3.4... Ok vacuum2-3.5... Ok vacuum2-3.6... Ok vacuum2-3.7... Ok vacuum2-3.13... Ok vacuum2-3.14... Ok vacuum2-3.15... Ok vacuum2-3.16... Ok vacuum2-3.17... Ok 0 errors out of 16 tests All memory allocations freed - no leaks Maximum memory usage: 85541 bytes#c8c8c8 2824 code closed 2007 Dec anonymous 2007 Dec 1 1 enable shared cache problem System info: -Celeron M. -Windows Xp. -Single process. -No thread. Code: int WINAPI WinMain(HINSTANCE h1, HINSTANCE h2, LPSTR lpCMD, int nSCMD) { sqlite3* db; sqlite3_stmt * stm; sqlite3_enable_shared_cache(1); sqlite3_open("db", &db); sqlite3_prepare_v2(db, "ATTACH DATABASE db2 AS d", 24, &stm, 0); sqlite3_step(stm); } The problem: the sqlite3_step(stm) execute for an infinite time. MA _2007-Dec-06 07:56:12 by danielk1977:_ {linebreak} I just tried the following program with the cvs version: #include "sqlite3.h" int main (int argc, char **argv){ sqlite3* db; sqlite3_stmt *stm; sqlite3_enable_shared_cache(1); sqlite3_open("db", &db); sqlite3_prepare_v2(db, "ATTACH DATABASE db2 AS d", 24, &stm, 0); sqlite3_step(stm); return 0; } And the sqlite3_step() did not block for any amount of time. Can you confirm that the code above produces the bug in your environment? Thanks. ---- _2007-Dec-07 13:59:02 by anonymous:_ {linebreak} I have ran more test but the problem is never arised. I think it was generated from code or configuration properties from my system, outside sqlite. MA #c8c8c8 2823 code closed 2007 Dec anonymous 2007 Dec 1 1 query not return values I have a database with this description:
and contain this data:CREATE TABLE [coletores] ( [coletor] INTEGER NOT NULL ON CONFLICT ABORT PRIMARY KEY, [senha] NVARCHAR2(20) NOT NULL ON CONFLICT ABORT); CREATE UNIQUE INDEX [SenhaUnica] ON [coletores] ([senha]);
When i run the query: "select coletor, senha from coletores where senha = '43043553'" query not return values. Works when I try it. Perhaps you have some whitespace on one side of the '43043553' value in the database. Whitespace is significant to SQLite. #cfe8bd 2820 code fixed 2007 Dec anonymous 2007 Dec 1 1 rollback doesn't work Code:coletor | senha 1 43043553
prints: database table is locked In sqlite rolback is called (I'v checked in older version), but in test.db there is no table 't'. Tested on win2k. _2007-Dec-03 16:31:50 by anonymous:_ {linebreak} What is the desired outcome? ---- _2007-Dec-03 18:28:38 by anonymous:_ {linebreak} No error. Or if error is reported during delete, nothing should be deleted. #c8c8c8 2819 code closed 2007 Dec anonymous 2007 Dec 2 1 Blocking and crashing First it was blocking when using perl lib DBI, i tried to update the packages and finally reinstall them and now i get this:#include#include "sqlite3.h" sqlite3 *db; static int callback(void *NotUsed, int argc, char **argv, char **azColName){ sqlite3_exec(db, "DROP TABLE t", 0, 0, 0); printf("%s\n", sqlite3_errmsg(db)); return 0; } int main() { sqlite3_open("test.db", &db); sqlite3_exec(db, "CREATE TABLE IF NOT EXISTS t(a INT)", 0, 0, 0); sqlite3_exec(db, "select name, type from sqlite_master", &callback, 0, 0); sqlite3_close(db); return 0; }
mush@server:~$ /usr/bin/sqlite3 ./Bureau/lexfix/lexFix/test.dat SQLite version 3.3.10 Enter ".help" for instructions sqlite> .schema /usr/bin/sqlite3: symbol lookup error: /usr/bin/sqlite3: undefined symbol: sqlite3_enable_load_extension mush@server:~$ /usr/bin/sqlite3 ./Bureau/lexfix/lexFix/test.dat /usr/bin/sqlite3: symbol lookup error: /usr/bin/sqlite3: undefined symbol: sqlite3_enable_load_extensionI think this is a support request, not a bug report. Please take your comments to the SQLite mailing list. #c8c8c8 2817 code closed 2007 Dec danielk1977 2007 Dec danielk1977 1 1 Temp-tables with the same name as database tables can cause corruption Suspect a problem in the CREATE INDEX statement here: CREATE TEMP TABLE abc(a, b, c); CREATE TABLE main.abc(a, b, c); CREATE INDEX main.abc_i ON abc(a, b, c); The attached script demonstrates the problem. #f2dcdc 2813 build active 2007 Nov anonymous 2007 Nov 1 1 compile error on Windows CE environment: visual c++ 2005 window ce 6.0 customize sdk sqlite-amalgamation-3_5_3 I get error: Error 27 error C2040: 'localtime' : 'tm *(const time_t *)' differs in levels of indirection from 'int ()' d:\SubProjects\Sqlite\sqlite3.c 18574 but if I add code in line 7095: struct tm *__cdecl localtime(const time_t *t); then Success! #f2dcdc 2810 code active 2007 Nov anonymous 2007 Nov 1 1 Unregistered collation problems with simple subselects As discussed on the mailing-list: Imagine that a SQLite3 database opened in a custom application with a registered a collation sequence named "unknown" has created the following table: CREATE TABLE a (b COLLATE unknown); Now open this table in the default SQLite3 CLI. Up to here, everything works as expected. Simple queries like "SELECT * FROM a;" work fine. But subselects, in their most basic form and with no sorting or comparisons, result in an error: sqlite> INSERT INTO a VALUES ('one'); sqlite> SELECT * FROM a, (SELECT * FROM a); SQL error: no such collation sequence: unknown sqlite> SELECT * FROM (SELECT * FROM a); SQL error: no such collation sequence: unknown sqlite> SELECT *, * FROM a; one|one This is surprising because the collation sequence should not matter to the queries. In fact, the union without the subselect works just fine and without errors. To demonstrate, here is the explain output of a table with a registered collation sequence. No mention of the collation name here: sqlite> CREATE TABLE b (b collate nocase); sqlite> EXPLAIN SELECT * FROM b, (SELECT * FROM b); 0|Goto|0|17| 1|Integer|0|0| 2|OpenRead|0|3| 3|SetNumColumns|0|1| 4|Integer|0|0| 5|OpenRead|2|3| 6|SetNumColumns|2|1| 7|Rewind|0|14| 8|Rewind|2|13| 9|Column|0|0| 10|Column|2|0| 11|Callback|2|0| 12|Next|2|9| 13|Next|0|8| 14|Close|0|0| 15|Close|2|0| 16|Halt|0|0| 17|Transaction|0|0| 18|VerifyCookie|0|4| 19|TableLock|0|3|b 20|Goto|0|1| 21|Noop|0|0| #f2dcdc 2809 code active 2007 Nov anonymous 2007 Nov 1 1 PRAGMA collation_list shows unregistered collations As presented on the mailing list: Imagine that a SQLite3 database opened in a custom application with a registered a collation sequence named "unknown" has created the following table: CREATE TABLE a (b COLLATE unknown); Now open this table in the default SQLite3 CLI. Up to here, everything works as expected. Next issue "PRAGMA collation_list;" and notice that "unknown" lists next to the other registered collations, even though "unknown" is not registered with the default SQLite3 CLI: sqlite> PRAGMA collation_list; 0|unknown 1|NOCASE 2|BINARY Responses from the mailing list indicate that this is not the expected behaviour. "PRAGMA collation_list;" should list registered collations only. _2007-Nov-28 16:12:17 by anonymous:_ {linebreak} I don't think this is a bug. If the CLI is not aware of the collation, it should not process the query that makes use of the collation because it would certainly be wrong if it simply ignored the collation. This is not unlike a user-registered SQL function that does not exist in the CLI. I would not expect or want the sqlite3 CLI to ignore the unknown function, nor would I want the CLI to process queries ignoring the custom collation. #cfe8bd 2805 code fixed 2007 Nov anonymous 2007 Nov 1 1 Checkin [4573] Home grown recursive mutexes Checkin [4573] would only work on uniprocessors, not SMP hardware. You cannot implement recursive mutexes without some sort of memory barrier or atomic "test and set" instruction. The reason being the each SMP CPU has its own memory image that is not synchronized with main memory until there is a memory barrier or lock/spinlock. Take a look at NPTL or the previous incarnation, LinuxThreads, to see how difficult it is to implement a reliable and efficient recursive mutex on different CPU architectures. It simply can't be done without dropping into architecture-specific assembler. _2007-Nov-28 17:46:31 by anonymous:_ {linebreak} Solaris 2.6 was mentioned in the original homegrown recursive mutex implementation. Be aware that some Sparc machines offer cache coherency, while others do not. So it's a run-time hardware-specific thing, not an OS thing or a compile-time thing. Here is a fairly comprehensive list of cache coherency info (circa 2003) for various CPUs: http://linux.derkeiler.com/Mailing-Lists/Kernel/2003-09/3187.html #c8c8c8 2804 code closed 2007 Nov anonymous 2007 Dec 1 1 PRAGMA legacy_file_format=OFF setting lost with VACUUM It seems that the PRAGMA legacy_file_format=OFF and the ability to use DESC indexes is lost after two VACUUMs and reconnects.
$ rm -f foo.db $ ./sqlite3-3.5.3.bin foo.db SQLite version 3.5.3 Enter ".help" for instructions sqlite> PRAGMA legacy_file_format; 1 sqlite> PRAGMA legacy_file_format=OFF; sqlite> PRAGMA legacy_file_format; 0 sqlite> CREATE TABLE abc(a,b,c); sqlite> CREATE INDEX abc_i on abc(b desc, c asc, a desc); sqlite> explain query plan select * from abc order by b desc, c asc, a desc; 0|0|TABLE abc WITH INDEX abc_i ORDER BY sqlite> vacuum; sqlite> .qIn the next connection we see that the legacy file format reverted back to 1, but the DESC index is still picked up...
$ ./sqlite3-3.5.3.bin foo.db SQLite version 3.5.3 Enter ".help" for instructions sqlite> PRAGMA legacy_file_format; 1 sqlite> explain query plan select * from abc order by b desc, c asc, a desc; 0|0|TABLE abc WITH INDEX abc_i ORDER BY sqlite> vacuum; sqlite> .qBut if connected to another time, the DESC index is not picked up...
$ ./sqlite3-3.5.3.bin foo.db SQLite version 3.5.3 Enter ".help" for instructions sqlite> PRAGMA legacy_file_format; 1 sqlite> explain query plan select * from abc order by b desc, c asc, a desc; 0|0|TABLE abcActually, this works correctly, though the documentation could be clearer. The legacy_file_format pragma does not tell you the file format of the current database. Rather, it tells you what file format will be used when creating new databases (for example, using ATTACH). The behavior shown above is correct. There is no pragma to tell you what the file format of the current database is. The documentation has been clarified. Two new test cases have been inserted into the test suite to verify that the operation is correct. #c8c8c8 2802 code closed 2007 Nov anonymous 2007 Dec 1 1 SELECT UNION and EXCEPT seem to have a memory allocation bug It seems that SELECT UNION and EXCEPT operators work wrong. It looks like memory access overflow or integer overflow. Well, I have a large text file 'text2.text' (~1.5mg), which contains: 20091208{linebreak} 20216510{linebreak} 20149599{linebreak} 20250484{linebreak} 20211910{linebreak} 20199980{linebreak} ........{linebreak} 20183528{linebreak} 20151994{linebreak} 20179184{linebreak} 20077607{linebreak} 182500 lines in total. The meaning of these numbers is a date in YYYYMMDD format. Numbers are random and unsorted at all. The next file is 'test1.text' is the same as 'test2.text' but without the last 1000 lines. The 'diff' between these files shows the difference in last 1000 lines. Than I execute script: @echo off{linebreak} if exist test.db del test.db{linebreak} rem{linebreak} rem Load data into table 'a'{linebreak} rem It prints 181500{linebreak} sqlite3 test.db "create table a (i1 text)"{linebreak} sqlite3 test.db ".import test1.text a"{linebreak} sqlite3 test.db "select count(*) from a"{linebreak} rem{linebreak} rem Load data into table 'b'{linebreak} rem It prints 182500{linebreak} sqlite3 test.db "create table b (i1 text)"{linebreak} sqlite3 test.db ".import test2.text b"{linebreak} sqlite3 test.db "select count(*) from b"{linebreak} rem{linebreak} rem PROBLEM IS HERE{linebreak} rem Now I am trying to merge the tables 'a' and 'b' via UNION.{linebreak} rem It should print '182500' but it prints '16384' !!!{linebreak} sqlite3 test.db "create table c (i1 text)"{linebreak} sqlite3 test.db "insert into c select * from b union select * from a"{linebreak} sqlite3 test.db "select count(*) from c"{linebreak} rem{linebreak} rem Just a check that EXCEPT also has the same bug.{linebreak} rem It should print 1000, but it prints '0' !!!{linebreak} sqlite3 test.db "select count(*) from (select * from c except select * from b)"{linebreak} I suggest that UNION and EXCEPT has some kind of memory overflow. And also it depends on how strong the numbers in the source text file are shuffled and how wide is the range of the numbers. I can attach my files 'test1.text' and 'test2.text' if needed. My system: CPU Intel Core 2 Duo, 1.06GHz, 2GB RAM. Operating system: Windows XP SP2 (Version 5.1.2600) _2007-Nov-27 23:45:42 by drh:_ {linebreak} UNION and EXCEPT both imply DISTINCT. Since you are dealing with dates, it seems unlikely that you really have 182500 distinct dates. That would span 500 years and your sample data seems to all be from the early part of the 21st century. 16384 distinct dates would span 44 years, which seems more reasonable, given your sample data. The fact that the number is a power of two probably results from a poor-quality PRNG used to generate the same data. You can use UNION ALL to to get the union of two tables without the implied DISTINCT. There is no way to omit the implied DISTINCT from EXCEPT. You will probably need to refactor your query to use the NOT IN operator. ---- _2007-Dec-05 20:10:08 by anonymous:_ {linebreak} Actually, in the real data, when I've got this situation on EXCEPT and UNION, there are some extra fields in the database. Except 'date', there were text fields: 'amount', 'owner', 'descr', 'labels' (it's personal cash accouting database). When the test data was generatated, all fields could have duplicate values, but 'amount' field was generated like: $amount = sprintf( "%d.%02d", rand( 1, 10000 ), rand( 0, 99 ) ); so, it can take 9999999 possible values, which is more than 16384 for sure. And the behaviour of the EXCEPT was completely the same as I've described the the original message of this bug report. That's why I've decided that the key of the problem is just in the amount of data, not in values, and I've reduced the database structure for the bug report to make it more clear (I left only one field 'date'), but the 'bug' (I mean this strange behaviour of the EXCEPT and UNION) still happens in the reduced database structure (with only one field 'date'). So DISTINCT function will not merge all values of 'amount' field into 16384 unique values. What do you think? #f2dcdc 2791 code active 2007 Nov anonymous 2007 Nov 1 1 Allow building FTS[123] as part of sqlite library with configure See attached patch. #c8c8c8 2782 code closed 2007 Nov anonymous 2007 Nov 1 1 bind_text(...) into transaction In Transaction i insert some values, using sqlite3_bind_text(...) The first row has undef value(some times it's empty), but other rows have normal values. _2007-Nov-21 13:50:25 by drh:_ {linebreak} If you can describe some kind of problem with SQLite, we will be happy to look into the matter. Your description above sounds like a bug in your own code though. What about this problem makes you think it is a bug in SQLite? #c8c8c8 2778 code closed 2007 Nov anonymous 2007 Nov adixon 1 1 Error 0 CREATE PROCEDURE Driver_Daily_Activity { @comp_code varchar(8), @begin_date datetime, @end_date datetime, @emp_short_name varchar(8), @emp_id int, @driver_time_in output, @driver_time_out output, @driver_beaktime_in output, @driver_beaktime_out output, @driver_lunchtime_in output, @driver_lunchtime_out output, @total_break_time output } AS select @driver_time_in =driver_datetime_in,@driver_time_out =driver_datetime_out from driver_daily_log with (nolock) where company_code=@comp_code and emp_id=@emp_id and driver_datetime_in >=@begin_date and driver_datetime_out<@end_date and driver_datetime_out is not null select @driver_breaktime_in=substring((CAST(break_time_in AS varchar(20))),12,8) ,@driver_breaktime_out=substring((CAST(break_time_out AS varchar(20))),12,8) from driver_daily_break_log with (nolock) where company_code=@comp_code and emp_id=@emp_id and driver_status='OB'. select @driver_lunchtime_in=substring((CAST(break_time_in AS varchar(20))),12,8) ,@driver_lunchtime_out=substring((CAST(break_time_out AS varchar(20))),12,8) from driver_daily_break_log with (nolock) where company_code=@comp_code and emp_id=@emp_id and driver_status='OL'. select @total_break_time =sum(total_break_time) from driver_daily_break_log with (nolock) where company_code=@comp_code and emp_id=@emp_id. This procedure is giving me the following error : SQL State 42000: Error 0 : Syntax error or access violation SQL identifiers may not contain the "@" character. #c8c8c8 2777 code closed 2007 Nov anonymous 2007 Nov shess 1 1 FTS[23] compile bug You need to #include "sqlite3.h" to pick up the sqlite3_malloc() declaration: ./ext/fts3/fts3_hash.c: In function "fts3HashMalloc": ./ext/fts3/fts3_hash.c:38: warning: initialization makes pointer from integer without a cast Also, can you move the following: #ifndef SQLITE_ENABLE_BROKEN_FTS2 #error fts2 has a design flaw and has been deprecated. #endif to be after this line: #if !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_FTS2) in fts2.c? That way you can build fts3 while having fts2 objects in the Makefile without defining -DSQLITE_ENABLE_BROKEN_FTS2 for an fts3-only build. _2007-Nov-23 18:21:04 by anonymous:_ {linebreak} This error still exists in FTS2 and FTS3: ./ext/fts3/fts3_hash.c: In function "fts3HashMalloc": ./ext/fts3/fts3_hash.c:38: warning: initialization makes pointer from integer without a cast On some 64-bit systems this will cause FTS to crash because an int is smaller than a pointer. Nevermind - it was fixed. #f2dcdc 2776 warn active 2007 Nov anonymous 2007 Nov 5 1 mailing list sqlite-users-digest doesn't work. It remembers registered addresses but doesn't send any emails. #cfe8bd 2774 code fixed 2007 Nov shess 2007 Nov shess 1 1 Crash merging empty OR queries in fts3. create virtual table test using fts2(a); insert into test (a) values ('alpha'); insert into test (a) values ('beta'); select a from test where test match ('a:gamma* OR a:delta*'); sqlite3: ../sqlite/ext/fts3/fts3.c:478: dataBufferAppend: Assertion `nSource>0 && pSource!=((void *)0)' failed. "Workaround" is to compile with -DNDEBUG=1, but the person who reported this to me was using the fts2 dll from sqlite.org. Fix on the way. #c8c8c8 2773 code closed 2007 Nov danielk1977 2007 Nov 2 1 sqlite crashes in sqlite3_step when compiled with SQLITE_THREADS=1 When running the self-test of pysqlite2 [1], it crashes. This only happens when sqlite3 is compiled with the --enable-threads option, but not with --disable-threads. #0 0xbb987124 in sqlite3_step () from /usr/pkg/lib/libsqlite3.so.0 #1 0xbb9a7307 in _sqlite_step_with_busyhandler () from /usr/pkg/lib/python2.4/site-packages/pysqlite2/_sqlite.so #2 0xbb9a437b in pysqlite_cursor_executescript () from /usr/pkg/lib/python2.4/site-packages/pysqlite2/_sqlite.so #3 0xbbb45baa in PyCFunction_Call () from /usr/pkg/lib/libpython2.4.so.1.0 The reason for the crash is that v->db is NULL. In the non-threaded case, it all works. [1]
the test version is 3.5.1 thank you! 1. in os_win.c line:1165 /* samsting_xie change the last line: zFilename to zName. the zFilename is not declared, so i change it to zName, maybe it's write error? the old last line is: && !winceCreateLock(zFilename, pFile) */ 2. in os_win.c line:1341 return SQLITE_OK; /* samsting_xie add the last line: return SQLITE_OK; if OS_WINCE defined 1, then this function is not return value. it's maybe return a unknown value. */Duplicate. See, for example, #2718, #2711, #2710, #2702, #2700, #2683. #cfe8bd 2733 code fixed 2007 Oct anonymous 2007 Oct 1 1 OP_Real and OP_Int64 inefficient for constants Let's say I have a table with 50 million rows: CREATE TABLE t1(a real); If I perform a query like: select -3.14159265 * a from t1; then the string "-3.14159265" will be converted to a double 50 million times via OP_Real -> sqlite3VdbeRealValue -> sqlite3AtoF. explain select -3.14 * a from t1; ... 5|Real|0|0|-3.14 -- converts string to double each loop pass! 6|Column|0|0|# t1.a 7|RealAffinity|0|0| 8|Multiply|0|0| 9|Callback|1|0| 10|Next|0|5| ... This is actually quite computationally expensive and unnecessary. If OP_Real and OP_Int64 would store the double/int64 values into P1/P2 at expression parse time then they would not have to be reparsed to a double/int64 each pass in the loop at vdbe runtime. _2007-Oct-18 03:37:47 by anonymous:_ {linebreak} Proof of concept patch: http://www.mail-archive.com/sqlite-users%40sqlite.org/msg28390.html http://marc.info/?l=sqlite-users&m=119266402408308&q=p3 #c8c8c8 2731 code closed 2007 Oct anonymous 2007 Oct 1 1 sqlite3ExprCode TK_UMINUS inefficient with large negative floats The follow change in expr.c makes my program (which deals with large negative floating point numbers) run 14 times faster. 14 seconds originally to just 1 second after this patch. Assuming the fix is correct, can you please apply it? make test runs with 0 errors with this. Thanks.
Index: src/expr.c =================================================================== RCS file: /sqlite/sqlite/src/expr.c,v retrieving revision 1.313 diff -u -3 -p -r1.313 expr.c --- src/expr.c 18 Sep 2007 15:55:07 -0000 1.313 +++ src/expr.c 16 Oct 2007 23:54:17 -0000 @@ -1879,7 +1879,10 @@ void sqlite3ExprCode(Parse *pParse, Expr assert( pLeft ); if( pLeft->op==TK_FLOAT || pLeft->op==TK_INTEGER ){ Token *p = &pLeft->token; - char *z = sqlite3MPrintf(pParse->db, "-%.*s", p->n, p->z); + char *z = sqlite3_malloc(p->n+2); + z[0] = '-'; + memcpy(z+1, p->z, p->n); + z[p->n+1] = 0; if( pLeft->op==TK_FLOAT ){ sqlite3VdbeOp3(v, OP_Real, 0, 0, z, p->n+1); }else{_2007-Oct-17 01:34:39 by drh:_ {linebreak} FWIW, the patch is not correct. It does not work if sqlite3_malloc fails and returns a NULL pointer. But it isn't too hard to fix. I'm curious, though, what kind of program you are running where this makes a 14x speed improvement. One wonders if you couldn't do 100x faster using sqlite3_bind_double() instead. We could go through and make patches like this one all over the place, in order to make this or that special case run a little faster. But that would make the library footprint larger. The sqlite3MPrintf() call in this context is there for reasons of space efficiency, not CPU time. Some years ago, we when through this huge effort to reduce the footprint of the library by replacing the expanded string construction code like you are inserting with instances of sqlite3MPrintf(). I am reluctant to undo that effort for a single special case that could probably be handled more efficiently using sqlite3_bind_double(). ---- _2007-Oct-17 02:21:17 by anonymous:_ {linebreak} I have a script to generate ASCII SQL statements which I use in conjunction with the sqlite3 shell and MySQL to populate huge tables. I use the multi-insert patch on the mailing list for tables that hold millions of rows of negative numbers, with anywhere from 3 to 20 columns. http://www.mail-archive.com/sqlite-users%40sqlite.org/msg28337.html INSERT INTO BIGTABLE VALUES (-334712687065.09, -334712687065.12, -334712687065.13), (-334712687065.09, -334712687065.12, -334712687065.13), ... 10 thousand rows ... (-334712687065.09, -334712687065.12, -334712687065.13), (-334712687065.09, -334712687065.12, -334712687065.13); With the mulit-insert patch and the patch in this ticket, it is 25% faster for inserting than using the equivalent INSERT commands within a transaction: BEGIN INSERT INTO BIGTABLE VALUES(-334712687065.09, -334712687065.12, -334712687065.13); ... 10 thousand rows ... INSERT INTO BIGTABLE VALUES(-334712687065.09, -334712687065.12, -334712687065.13); COMMIT; I have to have no more than 10,000 rows per insert because it blows the stack due to sqlite3Select -> multiSelect recursion. I had to bump a few other sqliteLimits as well to get more than a few thousand rows at a time. I suppose the replacement of the sqlite3MPrintf could be useful in other scenarios as well. ---- _2007-Oct-17 02:27:30 by anonymous:_ {linebreak} The bind double approach is not 100X faster, by the way. It's only around 1.3X faster than the individual ASCII inserts in my tests. But it doesn't matter for my purposes, because it's not MySQL compatible anyway. ---- _2007-Oct-17 03:04:11 by anonymous:_ {linebreak} If the string were not pre-prepended with a '-' char, but instead parsed into a double, and the double simply negated it would be much faster and avoid a needless memory allocation/deallocation. To facilitate this, OP_Real would have to be changed or a new OP code created to accommodate an 8 byte double argument - possibly via straddling P1 and P2. ---- _2007-Oct-23 16:00:39 by anonymous:_ {linebreak} Fixed by Check-in [4507] #f2dcdc 2729 doc active 2007 Oct anonymous 2007 Oct 1 1 Lemon: %fallback, %wildcard, and @X uncodumented I noticed that the lemon documentation does not mention the %fallback and %wildcard directives. Both are in the code and are apparently doing useful work in SQLite's parse.y. Can other users benefit from them as well? The symbol @X is also undocumented. From a source code comment I read that it "If the argument is of the form @X then substituted the token number of X, not the value of X". A short documentation example would help to understand where and how it can be useful to apply this syntax. Are there other nice but undocumented Lemon goodies lacking documentation? #f2dcdc 2725 code active 2007 Oct anonymous 2007 Oct 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 2722 code fixed 2007 Oct anonymous 2007 Oct 1 1 data mis-alignment in UTF16 collation callback arguments We're using a Unicode build of SQLite, and found that internally, the 'NOCASE' collation was not Unicode aware -- it would convert any wide strings to UTF-8 when needed, and compare those. For performance, we would like to implement a WNOCASE for wide strings. However, upon implementing this, we found that the pointers passed to our collation function are not wide character (short-) aligned. On our platform we receive data mis-alignment errors when comparing the strings. We have already implemented similiar sqlite wide string functions (WLIKE, WCONTAINS), and we never receive mis-aligned strings there (are those guaranteed to be aligned); it would be helpful if this restriction could be applied to collation callbacks. #f2dcdc 2721 code active 2007 Oct anonymous 2007 Dec 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. #c8c8c8 2720 code closed 2007 Oct anonymous 2007 Oct 1 1 WinCE - Can't write Application which was working correctly with one of the last sqlite 3.3.16 fails on 3.5.1 to write anything to the database - no create tables and no inserts if the db existed beforehand. It seems to happen always, with either UTF-8 or UTF-16 encoding. _2007-Oct-11 07:26:49 by anonymous:_ {linebreak} I meant, "...one of the last 3.3.x (I think 3.3.16)..." ---- _2007-Oct-11 12:20:13 by drh:_ {linebreak} The developers have no ability to compile or run on winCE, much less any ability to debug on winCE. All winCE support is community provided. There have been many patches to the winCE code since 3.5.1 was released. The writer of this ticket previously reported two other problems that have already been fixed. Several other problems have also been fixed since 3.5.1. We are guessing that this "Can't write" problem will probably go away if the writer will simply get the latest code out of CVS. If not, then there is nothing really we can do about it unless we have patches to fix the problem. #c8c8c8 2719 code closed 2007 Oct anonymous 2007 Oct 3 1 WinCE winFullPathName doesn't return a value #if OS_WINCE /* WinCE has no concept of a relative pathname, or so I am told. */ sqlite3_snprintf(pVfs->mxPathname, zFull, "%s", zRelative);
#if OS_WINCE if( (flags & (SQLITE_OPEN_READWRITE|SQLITE_OPEN_MAIN_DB)) == (SQLITE_OPEN_READWRITE|SQLITE_OPEN_MAIN_DB) && !winceCreateLock(zFilename, pFile) ){ CloseHandle(h); free(zConverted); return SQLITE_CANTOPEN; } if( dwFlagsAndAttributes & FILE_FLAG_DELETE_ON_CLOSE ){ pFile->zDeleteOnClose = zConverted; }else #endifThat zFilename of the winceCreateLock call doesn't exist. I guess it should be zName instead Duplicate of #2700 #f2dcdc 2716 new active 2007 Oct anonymous 2007 Oct 5 1 Create Clear Command I want a command caled "clear" like in MySQL. This command should erase the screen and then put the sqlite pointer on top of the screen _2007-Oct-11 07:41:45 by anonymous:_ {linebreak} How about a cookie instead? ---- _2007-Oct-30 08:02:04 by anonymous:_ {linebreak} Clearing the screen and moving the cursor are platform-dependent operations. On Unix they are not only platform-dependent, but also terminal-dependent. Thus such a feature does not really belong in the cross-platform and minimalistic sqlite3 shell (in my opinion). #f2dcdc 2715 code active 2007 Oct anonymous 2007 Oct 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. #cfe8bd 2711 code fixed 2007 Oct anonymous 2007 Oct 1 1 WinCE: flags incorrectly checked One last fix for WinCE: checkin 4479 is correct in the omission of certain flags for WinCE, but the flags are still being checked for later on in winOpen:
if( dwFlagsAndAttributes & FILE_FLAG_DELETE_ON_CLOSE ){ pFile->zDeleteOnClose = zConverted; }else ...Since that flag is no longer set, it needs to read:
if( flags & (SQLITE_OPEN_TEMP_DB | SQLITE_OPEN_TEMP_JOURNAL | SQLITE_OPEN_SUBJOURNAL) ){ pFile->zDeleteOnClose = zConverted; }else ...Finally, on WinCE, since FILE_FLAG_RANDOM_ACCESS is always set, it doesn't make sense to set FILE_FLAG_SEQUENTIAL_SCAN (for journals). At least on CE, it hampered performance in our tests. #cfe8bd 2710 code fixed 2007 Oct anonymous 2007 Oct 1 1 Typo in code specific to WinCE (checkin 4479) Checkin [4479] introduces a typo in code specific to WiNCE, returning SQLTIE_OK instead of SQLITE_OK _2007-Oct-09 15:21:50 by anonymous:_ {linebreak} Also, checkin 4479 is correct in the omission of certain flags for WinCE, but the flags are still being checked for later on in winOpen: if( dwFlagsAndAttributes & FILE_FLAG_DELETE_ON_CLOSE ){ pFile->zDeleteOnClose = zConverted; }else ... Since that flag is no longer set, it needs to read: if( flags & (SQLITE_OPEN_TEMP_DB | SQLITE_OPEN_TEMP_JOURNAL | SQLITE_OPEN_SUBJOURNAL) ){ pFile->zDeleteOnClose = zConverted; }else ... #cfe8bd 2702 code fixed 2007 Oct anonymous 2007 Oct 1 1 Errors in CreateFileW function use on WinCE CreateFileW function in WindowsCE uses only subspace of flags of desktop windows' version and reports errors, when passed flag that can't be dispatched. For 3.5.1 this means that any attempt to open/create temporary file on WCE fails.{linebreak} Also there is bad argument name passed in winOpen to winceCreateLock.{linebreak} Also, there is 'return' missing in winFullPathname when building for WCE, so any attempt to open file fails. Here is the patch solving all(for me) filesystem errors on WindowsCE.
sqlite-amalgamation-3_5_1.ce.patch ---------------------------------- --- ./sqlite3.c.orig Wed Oct 3 18:08:44 2007 +++ ./sqlite3.c Mon Oct 8 17:04:16 2007 @@ -18730,17 +18730,24 @@ } if( flags & (SQLITE_OPEN_TEMP_DB | SQLITE_OPEN_TEMP_JOURNAL | SQLITE_OPEN_SUBJOURNAL) ){ +#if OS_WINCE + dwFlagsAndAttributes = FILE_ATTRIBUTE_HIDDEN; +#else dwFlagsAndAttributes = FILE_ATTRIBUTE_TEMPORARY | FILE_ATTRIBUTE_HIDDEN | FILE_FLAG_DELETE_ON_CLOSE; +#endif }else{ dwFlagsAndAttributes = FILE_ATTRIBUTE_NORMAL; } if( flags & (SQLITE_OPEN_MAIN_DB | SQLITE_OPEN_TEMP_DB)){ dwFlagsAndAttributes |= FILE_FLAG_RANDOM_ACCESS; - }else{ + } +#if !OS_WINCE + else{ dwFlagsAndAttributes |= FILE_FLAG_SEQUENTIAL_SCAN; } +#endif if( isNT() ){ h = CreateFileW((WCHAR*)zConverted, dwDesiredAccess, @@ -18786,7 +18793,7 @@ #if OS_WINCE if( (flags & (SQLITE_OPEN_READWRITE|SQLITE_OPEN_MAIN_DB)) == (SQLITE_OPEN_READWRITE|SQLITE_OPEN_MAIN_DB) - && !winceCreateLock(zFilename, pFile) + && !winceCreateLock(zName, pFile) ){ CloseHandle(h); free(zConverted); @@ -18957,6 +18964,7 @@ #if OS_WINCE /* WinCE has no concept of a relative pathname, or so I am told. */ sqlite3_snprintf(pVfs->mxPathname, zFull, "%s", zRelative); + return SQLITE_OK; #endif #if !OS_WINCE && !defined(__CYGWIN__)---------------------------------- Thank you! #cfe8bd 2700 code fixed 2007 Oct anonymous 2007 Oct 1 1 invalid name in winOpen when compiling under WinCE in os_win.c:1165, WinCE specific code to create lock still refers to former zFilename variable whereas argument is now named zName. --- os_win.c-orig 2007-10-07 22:04:02.000000000 +0200 +++ os_win.c 2007-10-07 23:32:51.000000000 +0200 @@ -1162,7 +1162,7 @@ #if OS_WINCE if( (flags & (SQLITE_OPEN_READWRITE|SQLITE_OPEN_MAIN_DB)) == (SQLITE_OPEN_READWRITE|SQLITE_OPEN_MAIN_DB) - && !winceCreateLock(zFilename, pFile) + && !winceCreateLock(zName, pFile) ){ CloseHandle(h); free(zConverted); #c8c8c8 2692 build closed 2007 Oct anonymous 2007 Oct 1 1 COmpilation of Sqlite amalgamation v3.4.2. failed Version 3.4.2 (amalgamation) could not be compiled under MS DevStudio 6 the compilation errors are: sqlite3.c(6187) : error C2133: 'sqlite3UpperToLower' : unknown size sqlite3.c(9310) : error C2133: 'sqlite3OpcodeNames' : unknown size sqlite3.c(47159) : error C2133: 'sqlite3IsIdChar' : unknown size _2007-Oct-04 18:19:44 by drh:_ {linebreak} Duplicate of #2574. #c8c8c8 2691 code closed 2007 Oct anonymous 2007 Oct 1 1 SQLITE_OMIT_AUTHORIZATION compilation error sqlite3.Int.h does not compile with SQLITE_OMIT_AUTHORIZATION. Rationale: sqlite3Int.h, line 1768: # define sqlite3AuthRead(a,b,c) is missing a 4th parameter like this: # define sqlite3AuthRead(a,b,c,d) thanks. #c8c8c8 2688 build closed 2007 Oct anonymous 2007 Oct 3 1 SQLITE_OMIT_AUTHORIZATION build failure Version 3.5.1 does not compile when specifying -DSQLITE_OMIT_AUTHORIZATION. The problem lies on line 6872 (of the amalgamation): =# define sqlite3AuthRead(a,b,c)= should be: =# define sqlite3AuthRead(a,b,c,d)= thanks. #cfe8bd 2685 code fixed 2007 Oct anonymous 2007 Oct anonymous 2 1 Compilation under MSVC gives warning on TryEnterCriticalSection If SQLite is compiled under MSVC 8.0 for instance it gives following warning while compiling mutex_w32.c: src\mutex_w32.c(147) : warning C4013: 'TryEnterCriticalSection' undefined; assuming extern returning int This warning shall be treated as error, if correct behavior of try mutex is exected. Solution: to declare #define _WIN32_WINNT 0x0400 before # include
@@ -1162,13 +1168,13 @@ #if OS_WINCE if( (flags & (SQLITE_OPEN_READWRITE|SQLITE_OPEN_MAIN_DB)) == (SQLITE_OPEN_READWRITE|SQLITE_OPEN_MAIN_DB) - && !winceCreateLock(zFilename, &f) + && !winceCreateLock(zName, pFile)#c8c8c8 2682 code closed 2007 Oct anonymous 2007 Oct 1 1 SQLite Text Problem I am using SQLite 3 as backend database in my asp application. I am using SQLite3 ODBC driver (latest version). Table Design ------------ CREATE TABLE log ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, from_value TEXT NULL, to_value TEXT NULL ); INSERT 10 RECORDS IN TO DATABASE -------------------------------- Const adOpenStatic = 3 Const adLockOptimistic = 3 Dim SQLString ,objConnection Dim fromVal,toval Set objConnection = CreateObject("ADODB.Connection") for a = 1 to 10 objConnection.Open "DSN=Test;uid=;pwd=;" fromVal = "FROM Value : "& a toVal = "To Value : "& a SQLString = "INSERT INTO log(from_value, to_value) VALUES ('"& fromVal & "' ,'"&toVal& "')" objConnection.Execute SQLString objConnection.Close next Access records in the log table ------------------------------- Dim LogRs,SQLStmt SQLStmt = "SELECT * FROM log " LogRs.ActiveConnection = "DSN=Test;uid=;pwd=;" LogRs.Source= SQLStmt LogRs.CursorType = 3'ADOPENSTATIC LogRs.CursorLocation = 2 'ADUSESERVER LogRs.LockType = 3 'ADLOCKOPTIMISTIC LogRs.Open() RecordCount = LogRs.RecordCount If I use datatype of from_value/to_value as TEXT ,I get RecordCount as zero which is wrong. If I use datatype of from_value/to_value as Varchar(1600) ,I get RecordCount as 10 which is correct. If I use datatype of from_value/to_value as Varchar(1600) and tried to put more than 1600 charcters in it ,SQLite stores data in DB. While retrieving the data I get the error Arguments are of the wrong type,are out of acceptable range,or are in conflict with one another. I enabled ODBC trace log ,but its empty after running my application.So I am not sure where it is ODBC problem or SQLite ODBC driver error? If I open table (database) in SQLite Studio I could see data without any error. If I use MS Access with same table design and same code, I dont get any error . The code works fine as expected . _2007-Oct-01 22:04:18 by drh:_ {linebreak} There are multiple ODBC drivers for SQLite, none of which are support by this site. Your problem appears to be in the ODBC driver. If you find evidence to the contrary, please reopen this ticket. #cfe8bd 2678 code fixed 2007 Sep anonymous 2007 Oct 1 1 amalgamation zip file should include sqlite3ext.h It is not possible to build portable external loadable modules using just sqlite-amalgamation-3_5_0.zip - instead you need the entire sqlite3 source tree. If sqlite3ext.h were bundled with the amalgamation, this would not be necessary. _2007-Sep-28 19:44:00 by anonymous:_ {linebreak} Actually, a better solution would be to get rid of sqlite3ext.h altogether, fold its functionality into sqlite3.h and when people go to compile a module they #define whether they wish to compile an external library or not. #c8c8c8 2676 code closed 2007 Sep anonymous 2007 Sep 1 1 TEMP_STORE does not appear to be set by the compile time switch The compile time switch -DTEMP_STORE=2 does not appear to change TEMP_STORE. When a database is created after compiling the library I tried to view temp_store by using PRAGMA temp_store which returns the value 0. If this is true that would force all temp storage to a file. I want temp storage to be in memory. It's more complex than that. See the table at: http://www.sqlite.org/pragma.html#pragma_temp_store #c8c8c8 2675 new closed 2007 Sep anonymous 2007 Sep 1 1 TEMP_STORE does not appear to be set by the compile time switch The compile time switch -DTEMP_STORE=2 does not appear to change TEMP_STORE. When a database is created after compiling the library I tried to view temp_store by using PRAGMA temp_store which returns the value 0. If this is true that would force all temp storage to a file. I want temp storage to be in memory. See #2676 #c8c8c8 2667 code closed 2007 Sep anonymous 2007 Oct 1 1 no such function: hex no such function: regexp I am using SQLite 3 with pysqlite. I have few triggers that uses regexp and hex functions. on linux it worked fine. Once I switch to Windows I am getting no such functions errors. _2007-Oct-01 13:35:49 by drh:_ {linebreak} The regexp function is not supported by SQLite. hex() is supported but we are unable to find anything wrong with it. #c8c8c8 2665 code closed 2007 Sep anonymous 2007 Sep 3 1 Dissapearing exclusive lock when DB is copied... In our application, we occasionally need to make copies of a running database. To insure consistancy, the program does something like this (in python): # we're about to lock the db{linebreak} dbcursor.execute('begin exclusive'){linebreak} # the db is now locked{linebreak} shutil.copy('database.db','other-name.db'){linebreak} # WHOAH, what happened to our lock on database.db??????{linebreak} the comments indicate the locking status at each time. I've got a workaround, which amounts to: # we're about to lock the db{linebreak} dbcursor.execute('begin exclusive'){linebreak} # the db is now locked{linebreak} os.system('cp database.db other-name.db'){linebreak} # database.db is still locked, but other-name.db is not locked{linebreak} which is more or less what I'd expect. Is this copying of databases totally evil? Why does a copy made by a process that has an exclusive lock on the origin of the copy destroy the exclusive lock? Why doesn't a copy by a different process destroy it? _2007-Sep-25 18:30:59 by anonymous:_ {linebreak} The problem is shutil.copy('database.db','other-name.db') must be unlocking the file or doing something altogether wrong. Run strace/truss to confirm. ---- _2007-Sep-25 18:32:06 by drh:_ {linebreak} On unix, when you close a file, all locks on that file held by the same process are cleared - even locks that were created by separate file descriptors. You are probably closing the file at the conclusion of your copy, which is clearing the locks. This "feature" of unix is brought to you by The Open Group. After a while, you learn to clearly see the differences between those parts of unix that were designed by Thompson and Richie and those parts developed by committee. Would that there was more of the former and less of the latter... ---- _2007-Sep-25 18:52:38 by anonymous:_ {linebreak} How can we get the UNIX file descriptor for the sqlite3* connection from the sqlite3 API so that we do not have to reopen and ultimately close the file, thus clearing the locks? ---- _2007-Sep-25 19:12:05 by anonymous:_ {linebreak} I've run strace, and the results are exactly what you'd expect; the shutil.copy() command opens the FD for creating. opens the destination FD, and writes out a copy. Also; the bug dissapears if you replace the shutil.copy() with something like: os.system('python -e "shutil.copy(...."') so it's not the mechanics of shutil.copy, but instead is because of the same processness of doing the copy. Sorry for not including that earlier! ---- _2007-Sep-26 12:09:00 by anonymous:_ {linebreak} Maybe you could consider introducing new API - sqlite3_backup(sqlite3*, const char*), or new SQL command BACKUP(FileName) which would backup main database to specified file using (exclusive?) lock. I don't see far to SQLite internals, but I hope this change could also provide functionality for saving in-memory databases to disk. #f2dcdc 2664 code active 2007 Sep danielk1977 2007 Sep 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 #c8c8c8 2662 code closed 2007 Sep anonymous 2007 Sep 1 1 Serious Performance Degradation from 3.3.4 to 3.3.5 & latest versions Starting from 3.3.5, SQLite suffers a serious performance degradation for the following schema and query. 3.3.4 returnes results *immedialtely*, all later versions take considerable time (multiple seconds) to execute the query. The database is about 6 MB in size. Schema: CREATE TABLE MTV20030105AdLink (AdvertisementID Numeric, AdRelationShipID Numeric, AgencyID Numeric, AdvertiserID Numeric, ProductID Numeric); CREATE TABLE MTV20030105AdType (ADTypeID Numeric, AdTypeName char(50)); CREATE TABLE MTV20030105Adjacency (AdInstID Numeric, ProductID Numeric); CREATE TABLE MTV20030105Advertiser (AdvertiserID Numeric, AdvertiserName char(50)); CREATE TABLE MTV20030105Agency (AgencyID Numeric, AgencyName char(50)); CREATE TABLE MTV20030105Products (ProductID Numeric, ProductName char(50)); CREATE TABLE MTV20030105Programs (ProgramID Numeric, ProgramName char(50)); CREATE TABLE MTV20030105Spots (AdInstID Numeric, AdvertisementID Numeric, KeyNumber char(15), StationID Numeric, SpotDateTime DateTime, Duration Numeric, BreakNumber Numeric, NumAdsInBreak Numeric, PositionInBreak Numeric, PromoBefore Numeric, PromoAfter Numeric, AdTypeID Numeric, CommentID Numeric, ProgramID Numeric, MarketInd Numeric); CREATE TABLE MTV20030105Stations (StationID Numeric, NetworkID Numeric, NetworkName char(50), CallSign char(50), LeadMarketInd Numeric); CREATE INDEX MTV20030105AdLink_i1 ON MTV20030105AdLink(ProductID); CREATE INDEX MTV20030105Spots_i1 ON MTV20030105Spots (AdvertisementID); Query: select SpotDateTime,NetworkName,CallSign,KeyNumber,Duration,ProgramName from MTV20030105Spots,MTV20030105Stations,MTV20030105Programs where MTV20030105Spots.AdvertisementID in (select AdvertisementID from MTV20030105AdLink where productid=90887) and MTV20030105Stations.StationID=MTV20030105Spots.StationID and MTV20030105Programs.ProgramID=MTV20030105Spots.ProgramID; "ANALYZE;" was not executed with any version of SQLite, and running it does not speed up the query with recent versions. I can provide the test database if required, just let me know. _2007-Sep-24 14:49:29 by anonymous:_ {linebreak} This is not a bug. This question belongs on the mailing list. If you run "EXPLAIN QUERY PLAN" on your SELECT you can see that your schema is not optimal, and results in full table scans for stations and programs: 0|1|TABLE MTV20030105Stations 1|2|TABLE MTV20030105Programs 2|0|TABLE MTV20030105Spots WITH INDEX MTV20030105Spots_i1 0|0|TABLE MTV20030105AdLink WITH INDEX MTV20030105AdLink_i1 If you change your schema as follows, it will use the indexes effectively: CREATE TABLE MTV20030105AdLink (AdvertisementID Numeric, AdRelationShipID Numeric, AgencyID Numeric, AdvertiserID Numeric, ProductID Numeric); CREATE TABLE MTV20030105AdType (ADTypeID INTEGER PRIMARY KEY, AdTypeName char(50)); CREATE TABLE MTV20030105Adjacency (AdInstID INTEGER PRIMARY KEY, ProductID Numeric); CREATE TABLE MTV20030105Advertiser (AdvertiserID INTEGER PRIMARY KEY, AdvertiserName char(50)); CREATE TABLE MTV20030105Agency (AgencyID INTEGER PRIMARY KEY, AgencyName char(50)); CREATE TABLE MTV20030105Products (ProductID INTEGER PRIMARY KEY, ProductName char(50)); CREATE TABLE MTV20030105Programs (ProgramID INTEGER PRIMARY KEY, ProgramName char(50)); CREATE TABLE MTV20030105Spots (AdInstID Numeric, AdvertisementID Numeric, KeyNumber char(15), StationID Numeric, SpotDateTime DateTime, Duration Numeric, BreakNumber Numeric, NumAdsInBreak Numeric, PositionInBreak Numeric, PromoBefore Numeric, PromoAfter Numeric, AdTypeID Numeric, CommentID Numeric, ProgramID Numeric, MarketInd Numeric); -- assumes that StationID is a unique identifier CREATE TABLE MTV20030105Stations (StationID INTEGER PRIMARY KEY, NetworkID Numeric, NetworkName char(50), CallSign char(50), LeadMarketInd Numeric); CREATE INDEX MTV20030105AdLink_i1 ON MTV20030105AdLink(ProductID); CREATE INDEX MTV20030105Spots_i1 ON MTV20030105Spots (AdvertisementID); explain query plan select SpotDateTime,NetworkName,CallSign,KeyNumber,Duration,ProgramName from MTV20030105Spots,MTV20030105Stations,MTV20030105Programs where MTV20030105Spots.AdvertisementID in (select AdvertisementID from MTV20030105AdLink where productid=90887) and MTV20030105Stations.StationID=MTV20030105Spots.StationID and MTV20030105Programs.ProgramID=MTV20030105Spots.ProgramID; 0|0|TABLE MTV20030105Spots WITH INDEX MTV20030105Spots_i1 1|1|TABLE MTV20030105Stations USING PRIMARY KEY 2|2|TABLE MTV20030105Programs USING PRIMARY KEY 0|0|TABLE MTV20030105AdLink WITH INDEX MTV20030105AdLink_i1 ---- _2007-Sep-24 15:12:03 by anonymous:_ {linebreak} This query is faster: explain query plan select SpotDateTime,NetworkName,CallSign,KeyNumber,Duration,ProgramName from (select AdvertisementID from MTV20030105AdLink where productid=90887) ads, MTV20030105Spots, MTV20030105Stations, MTV20030105Programs where MTV20030105Spots.AdvertisementID = ads.AdvertisementID and MTV20030105Stations.StationID=MTV20030105Spots.StationID and MTV20030105Programs.ProgramID=MTV20030105Spots.ProgramID; 0|0|TABLE MTV20030105AdLink WITH INDEX MTV20030105AdLink_i1 1|1|TABLE MTV20030105Spots WITH INDEX MTV20030105Spots_i1 2|2|TABLE MTV20030105Stations USING PRIMARY KEY 3|3|TABLE MTV20030105Programs USING PRIMARY KEY ---- _2007-Sep-24 15:45:58 by anonymous:_ {linebreak} Point well taken ;-) For completeness, here is the query plan produced by SQLite 3.3.4: 0|0|TABLE MTV20030105Spots WITH INDEX MTV20030105Spots_i1 1|1|TABLE MTV20030105Stations 2|2|TABLE MTV20030105Programs 0|0|TABLE MTV20030105AdLink WITH INDEX MTV20030105AdLink_i1 The table scan order is different which results in much faster execution times. If anyone knows if this change was intentional in later versions, I'd be glad to read about it on the mailing list. Also, out of plain curiosity: While I realize that the presented DB schema is not defined adequately, would anyone still consider that the new scan order behavior breaks backwards compatibility? For very large tables, the query becomes unusable after 3.3.4. Meet you at the mailing list ... ---- _2007-Sep-24 18:18:28 by anonymous:_ {linebreak} The table/index scan order has changed many times from release to release, and has never been guaranteed. When the query optimizer was first introduced many queries relying on the join order specified in the FROM clause were slower and had to be reworked. Your previous defined schema was just lucky in this regard. If you have proper indexing you would not have had this performance degradation. For that matter, it would not hurt if you used ANALYZE as well. #c8c8c8 2657 build closed 2007 Sep anonymous 2007 Oct anonymous 1 1 fail to compile I fail to compile. It is a "win32 static library" project. I simply create an empty project with "not using MFC" option and then add all files included in "sqlite-source-3_5_0.zip" into the project. Debug configuration is fine, but I cannot compile release. Information: ...sqlite-source-3_5_0\btree.c(3111) : fatal error C1001: INTERNAL COMPILER ERROR (compiler file 'F:\9782\vc98\p2\src\P2\regasg.c', line 646) Please choose the Technical Support command on the Visual C++ Help menu, or open the Technical Support help file for more information Error executing cl.exe. _2007-Sep-21 00:05:42 by anonymous:_ {linebreak} I have the same problem. I am using the preprocessed source code in sqlite-source-3_5_0.zip with Visual Studio 6.0 SP6. Compiler version: Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 12.00.8804 for 80x86 The offending line of btree.c is line 3111, which looks fairly innocent to me: pCur->aOverflow[iIdx] = nextPage; After investigating, it looks like this error is only emitted when global optimizations (/Og) are enabled, which is one of the switches set by optimizing for maximum speed. As a workaround, you can insert the line #pragma optimize("g", off) before line 3026 and the line #pragma optimize( "", on) after line 3156, which are the beginning and ending of the function static int accessPayload(BtCursor,int,int,unsigned char,int,int) This will disable global optimizations for the function, but leave them enabled for the rest of the file. {link: http://msdn2.microsoft.com/en-us/library/y19zxzb2(vs.71).aspx MSDN} has more information about error C1001, but it is not quite the same as what the VS6 docs say: {quote: This error is most often generated [due to] ... Failure of the code generator to find a way to generate correct code for a construct. This is most often caused by the interaction of an expression and an optimization option. The optimization has generated a tree which the compiler does not know how to handle. Such a problem can often be fixed by removing one or more optimization options when compiling the particular function containing the line indicated in the error message. } ---- _2007-Oct-12 18:53:47 by drh:_ {linebreak} In other words, there is a bug in MSVC++ that prevents it from correctly compiling SQLite. Shouldn't this be a problem reported to Microsoft? What exactly can we (the SQLite developers) do about this? Is there some simple code change that we can make to work around the bug in MSVC++? #c8c8c8 2656 doc closed 2007 Sep anonymous 2007 Oct 2 1 missing documentation: END TRANSACTION The documentation lists END [TRANSACTION] as a legal command, but does not describe it. It should be documented that is is the same as COMMIT [TRANSACTION]. Reference: http://www.mail-archive.com/sqlite-users@sqlite.org/msg03000.html _2007-Oct-01 13:32:34 by drh:_ {linebreak} The END TRANSACTION syntax works but it is unsupported. If I document it that means I will have to begin supporting it, which is something I do not want to do. ---- _2007-Oct-01 17:41:52 by anonymous:_ {linebreak} The END DOCUMENTATION *syntax* is documented - but it is not described what it does. While this might be intentional, I know about a novice user who was confused if he had to call END DOCUMENTATION after either COMMIT or ROLLBACK. If you decide not to document END DOCUMENTATION, you might want to think about not documenting it at all, in other words, remove the syntax declaration. ---- _2007-Oct-01 17:46:07 by drh:_ {linebreak} OK. We have added documentation for END TRANSACTION. ---- _2007-Oct-02 06:30:17 by anonymous:_ {linebreak} Thanks! #c8c8c8 2654 code closed 2007 Sep anonymous 2007 Oct jadams 1 1 sqlite3_prepare return 1(fail) result = sqlite3_exec( db, "create table MyTable_1sadsd ( ID integer primary key autoincrement, name nvarchar(32),comp nvarchar(32), file_content blob )",NULL, NULL, errmsg );//ok result=sqlite3_prepare(db,"insert into MyTable_1sadsd ( name,comp,file_content ) values ( 'aaaa','aaaaaa1',? )",-1,&stat,&zLeftover );//fail //the result==1 so fail; //but result = sqlite3_exec( db, "create table MyTable( ID integer primary key autoincrement, name nvarchar(32),comp nvarchar(32), file_content blob )",NULL, NULL, errmsg );//ok result=sqlite3_prepare(db,"insert into MyTable ( name,comp,file_content ) values ( 'aaaa','aaaaaa1',? )",-1,&stat,&zLeftover );//ok //is ok ,because MyTable is shorter than MyTable_1sadsd ; //why ? tks It's not clear from the report. There's nothing wrong with the SQL or th e C code shown. Maybe some other process is changing the schema or locking the dataabase or something. If you can post a complete program that demonstrates the problem, we will be able to help you. #f2dcdc 2652 code active 2007 Sep drh 2007 Sep 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() #c8c8c8 2650 build closed 2007 Sep anonymous 2007 Sep 1 1 lack ; in os_win.c:1329 Hi, I want just to inform you that it misses one; in the file os_win.c (line 1329). in winFullPathname, #if defined(__CYGWIN__) cygwin_conv_to_full_win32_path(zRelative, zFull); return SQLITE_OK <--- lack ';' #endif Regards YoCarBo Duplicate of #2642 and #2647 #cfe8bd 2648 build fixed 2007 Sep anonymous 2007 Sep 1 1 compilation error for PocketPC/WindowsCE function winOpen has some type errors under OS_WINCE preprocessor
#if OS_WINCE if( (flags & (SQLITE_OPEN_READWRITE|SQLITE_OPEN_MAIN_DB)) == (SQLITE_OPEN_READWRITE|SQLITE_OPEN_MAIN_DB) && !winceCreateLock(zFileName, &f) ){ CloseHandle(h); free(zConverted); return SQLITE_CANTOPEN; } if( dwFlagsAndAttributes & FILE_FLAG_DELETE_ON_CLOSE ){ pFile->zDeleteOnClose = zConverted; }else #endifThe line 18511 should be: && !winceCreateLock(zName, pFile) Also it's have mistake in typing FILE_FLAG_DELETE_ON_CLOSE (with last underlines - FILE_FLAG_DELETEONCLOSE in line 18517). Please change the sources to be compiled for WindowsCE. BR. Yuri Noyanov #c8c8c8 2647 code closed 2007 Sep anonymous 2007 Sep 1 1 sqlite-3.5.0 Cygwin: Compilation fails on src/os_win.c - ';' missing There is a ';' symbol missing on line 1329 in file os_win.c (winFullPathname function) after return statement:
#if defined(__CYGWIN__) cygwin_conv_to_full_win32_path(zRelative, zFull); return SQLITE_OK #endifDuplicate of Ticket #2642 #c8c8c8 2646 code closed 2007 Sep anonymous 2007 Sep a.rottmann 4 1 sqlite3_get_table loses one table row sqlite3_get_table seems to loose one row. this patch fixes the problem for me. If it is wrong, please fix the bug in a right way. --- sqlite-3.4.2/src/table.c 2007-06-12 15:18:01.000000000 +0300 +++ sqlite-3.4.2-x/src/table.c 2007-09-14 13:55:32.000000000 +0300 @@ -77,6 +77,7 @@ static int sqlite3_get_table_cb(void *pA } p->azResult[p->nData++] = z; } + p->nRow++; }else if( p->nColumn!=nCol ){ sqlite3SetString(&p->zErrMsg, "sqlite3_get_table() called with two or more incompatible queries", Can you take a look at the documentation here and confirm that you are interpreting it correctly? http://www.sqlite.org/capi3ref.html#sqlite3_free_table In particular, this bit: "Notice that there is an extra row of data containing the column headers. But the *nrow return value is still 3. *ncolumn is set to 2. In general, the number of values inserted into azResult will be ((*nrow) + 1)*(*ncolumn)." The code looks like it matches this description to me. If you find that it does not, please re-open this ticket. Thanks. #c8c8c8 2644 code closed 2007 Sep anonymous 2007 Sep 1 1 sqlite Will you tell me why sqlite program is running on my laptop computer? Is it part of another program. Your answer and time is really appreciated. Thank you. _2007-Sep-13 18:00:08 by drh:_ {linebreak} SQLite is used by Mcaffee Anti-virus, AOL Mail, Skype, Adobe Lightroom, and countless other programs, most of which we are completely unaware of. This website is for reporting bugs against SQLite, not for getting help with figuring out what it is or why it is running on your computer. Please use the SQLite mailing list for such help. #cfe8bd 2643 code fixed 2007 Sep anonymous 2007 Sep 1 1 bestIndex: Assertion `flags!=0' failed after index creation I get an assertion crash on a database using sqlite 3.3.12 and also 3.4.2:
sqlite> create index idx_media_items_p1 on media_items (p1); sqlite> select count(1) from media_items where p1 is not null; lt-sqlite3: ./src/where.c:1486: bestIndex: Assertion `flags!=0' failed. Aborted (core dumped) Program received signal SIGABRT, Aborted. [Switching to Thread 47454430964672 (LWP 24045)] 0x00002b28d839247b in raise () from /lib/libc.so.6 (gdb) bt #0 0x00002b28d839247b in raise () from /lib/libc.so.6 #1 0x00002b28d8393da0 in abort () from /lib/libc.so.6 #2 0x00002b28d838bbf6 in __assert_fail () from /lib/libc.so.6 #3 0x00002b28d7fd3360 in bestIndex () from /home/steve/dev/sqlite-3.4.2/.libs/libsqlite3-3.4.2.so.0 #4 0x00002b28d7fd3f7c in sqlite3WhereBegin () from /home/steve/dev/sqlite-3.4.2/.libs/libsqlite3-3.4.2.so.0 #5 0x00002b28d7fb3bdc in sqlite3Select () from /home/steve/dev/sqlite-3.4.2/.libs/libsqlite3-3.4.2.so.0 #6 0x00002b28d7fa2568 in yy_reduce () from /home/steve/dev/sqlite-3.4.2/.libs/libsqlite3-3.4.2.so.0 #7 0x00002b28d7fa4e52 in sqlite3Parser () from /home/steve/dev/sqlite-3.4.2/.libs/libsqlite3-3.4.2.so.0 #8 0x00002b28d7fb5719 in sqlite3RunParser () from /home/steve/dev/sqlite-3.4.2/.libs/libsqlite3-3.4.2.so.0 #9 0x00002b28d7fa8e1d in sqlite3Prepare () from /home/steve/dev/sqlite-3.4.2/.libs/libsqlite3-3.4.2.so.0 #10 0x00002b28d7fa92f6 in sqlite3_prepare () from /home/steve/dev/sqlite-3.4.2/.libs/libsqlite3-3.4.2.so.0 #11 0x00002b28d7fd70df in sqlite3_exec () from /home/steve/dev/sqlite-3.4.2/.libs/libsqlite3-3.4.2.so.0 #12 0x0000000000406a74 in process_input () #13 0x0000000000407bc2 in main ()core: http://skrul.com/core.gz I can send the db file upon request. _2007-Sep-13 17:09:29 by anonymous:_ {linebreak} Two additional notes: This does not crash on 3.3.4, plus this particular database was created using the "attach database" command from another database. ---- _2007-Sep-13 17:18:42 by drh:_ {linebreak} I need to know the schema for the media_items table. ---- _2007-Sep-13 17:22:13 by anonymous:_ {linebreak}
steve@steve-linux:~/dev/sqlite-3.4.2/.libs$ ./sqlite3 /home/steve/dev/songbird/db/flat2.db SQLite version 3.4.2 Enter ".help" for instructions sqlite> .schema CREATE TABLE media_items ( media_item_id integer primary key autoincrement, guid text unique not null, created integer not null, updated integer not null, content_url text not null, content_mime_type text, content_length integer, hidden integer not null check(hidden in (0, 1)), media_list_type_id integer, p1 text, p1_sort text, p2 text, p2_sort text, p3 text, p3_sort text, p4 text, p4_sort text, p5 text, p5_sort text, p6 text, p6_sort text, p7 text, p7_sort text, p8 text, p8_sort text, p9 text, p9_sort text, p10 text, p10_sort text , p11 text); CREATE INDEX idx_media_items_p1 on media_items (p1); CREATE INDEX idx_media_items_p11 on media_items (p11); CREATE INDEX idx_media_items_p2 on media_items (p2); sqlite>---- _2007-Sep-13 17:24:37 by drh:_ {linebreak} I also want to see a ".dump" of the sqlite_stat1 table. ---- _2007-Sep-13 17:25:52 by anonymous:_ {linebreak}
sqlite> .dump sqlite_stat1 BEGIN TRANSACTION; ANALYZE sqlite_master; INSERT INTO "sqlite_stat1" VALUES('media_items','idx_media_items_p11','61799 1'); INSERT INTO "sqlite_stat1" VALUES('media_items','sqlite_autoindex_media_items_1','61799 1'); COMMIT; sqlite>---- _2007-Sep-13 18:03:19 by anonymous:_ {linebreak} Great! I applied those changes to my sqlite 3.4.2 where.c and it fixed the problem. Thanks for the unbelievably fast fix! #cfe8bd 2640 code fixed 2007 Sep anonymous 2007 Sep 1 1 sqlite doesn't treat global identifiers correctly in a subquery Apparently, sqlite doesn't treat global identifiers correctly in a subquery; in particular, with my database filling, the following query gives a wrong result (3 rows) whereas changing the innermost p.person_id into d.person_id (which are equal by the preceding condition) gives the correct result (56 rows). select distinct p.name from persons p, directors d where d.person_id=p.person_id and not exists ( select * from directors d1 where d1.person_id=p.person_id except select * from writers w ) _2007-Sep-12 11:36:42 by danielk1977:_ {linebreak} Can you provide a sample populated database? This would be a lot quicker to look into that way... If it's too large to attach to this ticket, you can mail it to me: dan@sqlite.org ---- _2007-Sep-12 14:30:33 by anonymous:_ {linebreak}
CREATE TABLE persons(person_id, name); INSERT INTO "persons" VALUES(1,'fred'); INSERT INTO "persons" VALUES(2,'barney'); INSERT INTO "persons" VALUES(3,'wilma'); INSERT INTO "persons" VALUES(4,'pebbles'); INSERT INTO "persons" VALUES(5,'bambam'); CREATE TABLE directors(person_id); INSERT INTO "directors" VALUES(5); INSERT INTO "directors" VALUES(3); CREATE TABLE writers(person_id); INSERT INTO "writers" VALUES(2); INSERT INTO "writers" VALUES(3); INSERT INTO "writers" VALUES(4); select distinct p.name from persons p, directors d where d.person_id=p.person_id and not exists ( select * from directors d1 where d1.person_id=p.person_id except select * from writers w ); wilma select distinct p.name from persons p, directors d where d.person_id=p.person_id and not exists ( select * from directors d1 where d1.person_id=d.person_id except select * from writers w ); wilma bambam#c8c8c8 2637 code closed 2007 Sep anonymous 2007 Sep 1 1 Segfault in pthread_getspecific () Hi, I am trying to run Trac 0.11-dev with Apache 2.2.3-3.2ubuntu0.1/mod_python and libsqlite3 3.4.2. Accessing Trac over Apache causes segfault in libsqlite3. This has something to do with threading (models), I assume. I am not sure if this is an configuration issue, but I couldn't find any help after hard googling. I tried also libqlite 3.3.x with the same result. I marked the bug with severity 1, since there is no known workaround AFAIK. I compiled libsqlite3 to get the symbolic traceback:
#0 0xb7c521fa in pthread_getspecific () from /lib/tls/i686/cmov/libpthread.so.0 #1 0xb7d07f01 in sqlite3Insert (pParse=0x0, pTabList=0xb7d39bc8, pList=0xbfe16c98, pSelect=0xb7cebe8c, pColumn=0xb7cdf298, onError=1) at ./src/insert.c:1564 #2 0xb7d1e40e in sqlite3_get_table_cb (pArg=0xb7cdf298, nCol=1, argv=0xa582000, colv=0xb7d39bc8) at ./src/table.c:107 #3 0xb7cebe8c in sqlite3AuthRead (pParse=0xb7cebe8c, pExpr=0xbfe16c98, pTabList=0xb7d39bc8) at ./src/auth.c:118 #4 0xb7cebf18 in sqlite3AuthRead (pParse=0xb7d39bc8, pExpr=0xa582000, pTabList=0x1) at ./src/auth.c:140 #5 0xb7d118b6 in sqlite3Parser (yyp=0xa6ff558, yymajor=_2007-Sep-11 14:47:34 by anonymous:_ {linebreak} Also, Trac works fine if started using tracd standalone daemon tool ---- _2007-Sep-11 14:47:34 by anonymous:_ {linebreak} Could you try sqlite 3.5? The threading code has been completely redone. ---- _2007-Sep-11 14:57:17 by anonymous:_ {linebreak} No dice. libsqlite 3.5.0:, yyminor= {z = 0xffffffff , dyn = 0, n = 0}, pParse=0xbfe16e34) at parse.y:468 #6 0xb7d11fc1 in sqlite3Parser (yyp=0xa6ff558, yymajor= , yyminor= {z = 0xffffffff , dyn = 0, n = 1609611034}, pParse=0xbfe16e38) at parse.y:651 #7 0xb7d2f625 in getMask (pMaskSet=0x1, iCursor=1) at ./src/where.c:289 #8 0xb7d12243 in sqlite3Parser (yyp=0xbfe16ef8, yymajor= , yyminor= {z = 0xbfe16f08 "\222\023ӷ\232cӷ\200dӷ", dyn = 0, n = 0}, pParse=0x0) at parse.y:706 #9 0xb7d12347 in sqlite3Parser (yyp=0xb7cdffdc, yymajor= , yyminor= {z = 0xbfe1734c "X�o\n", dyn = 0, n = 0}, pParse=0xbfe16f78) at parse.y:735 #10 0xb7d1279c in sqlite3Parser (yyp=0xa6ff558, yymajor= , yyminor= {z = 0xa43a300 "system", dyn = 0, n = 86104312}, pParse=0xb7d39bc8) at parse.y:768 #11 0xb7d1288f in sqlite3Parser (yyp=0xbfe1734c, yymajor= , yyminor= {z = 0xa760dc8 "�>u\no", dyn = 0, n = 86104336}, pParse=0xbfe17048) at parse.y:812 #12 0xb7cf46da in sqlite3BtreeClose (p=0x0) at ./src/btree.c:1236 #13 0xb7d16c56 in base_vprintf (xRealloc=0, useInternal=-1210868792, zInitBuf=0x1 , nInitBuf=-1211228996, zFormat=0xa760dc8 "�>u\no", ap=0xb7d36480 "CREATE TABLE sqlite_master(\n type text,\n name text,\n tbl_name text,\n rootpage integer,\n sql text\n)") at ./src/printf.c:785 #14 0xb7d173ce in base_vprintf (xRealloc=0xbfe16f28, useInternal=-1210882944, zInitBuf= , nInitBuf=-1075743924, zFormat= , ---Type to continue, or q to quit--- ap=0xe9e1744a ) at ./src/printf.c:589 #15 0xb7d179d6 in base_vprintf (xRealloc=0xbfe17038, useInternal=0, zInitBuf= , nInitBuf=-1075743924, zFormat= , ap=0xbfe17090 "\030r��") at ./src/printf.c:548 #16 0xb7d0d75d in pager_playback (pPager=0x68, isHot=16777221) at ./src/pager.c:1474 #17 0xb7d1b000 in prepSelectStmt (pParse=0x5cc2c, p=0xb7cdd000) at ./src/select.c:1368 #18 0xb7d1191f in sqlite3Parser (yyp=0xa6ff558, yymajor= , yyminor= {z = 0xffffffff , dyn = 0, n = 0}, pParse=0xa73708c) at parse.y:480 #19 0xb7d11fc1 in sqlite3Parser (yyp=0xa6ff558, yymajor= , yyminor= {z = 0xffffffff , dyn = 0, n = 87668806}, pParse=0xbfe17488) at parse.y:651 #20 0xb6810cef in statement_create () from /usr/lib/python2.5/site-packages/pysqlite2/_sqlite.so #21 0xb680c7ad in connection_call () from /usr/lib/python2.5/site-packages/pysqlite2/_sqlite.so #22 0xb7547987 in PyObject_Call () from /usr/lib/libpython2.5.so.1.0 #23 0xb7547c03 in ?? () from /usr/lib/libpython2.5.so.1.0 #24 0x0a681a70 in ?? () #25 0x0a4154ec in ?? () #26 0x00000000 in ?? ()
#0 0xb7c521fa in pthread_getspecific () from /lib/tls/i686/cmov/libpthread.so.0 #1 0xb7d07f01 in sqlite3Insert (pParse=0x0, pTabList=0xb7d39bc8, pList=0xbfe16c98, pSelect=0xb7cebe8c, pColumn=0xb7cdf298, onError=1) at ./src/insert.c:1465 #2 0xb7d1e40e in sqlite3Select (pParse=0xb7cdf298, p=0x1, eDest=171204008, iParm=-1210868792, pParent=0x0, parentTab=0, pParentAgg=0xbfe16ca8, aff=0xb7cebf18 "�\205�\017\225�\017���\215�&") at ./src/select.c:2915 #3 0xb7cebe8c in attachFunc (context=0x0, argc=0, argv=0x0) at ./src/attach.c:82 #4 0xb7cebf18 in attachFunc (context=0x0, argc=-1211232372, argv=0x1) at ./src/attach.c:114 #5 0xb7d118b6 in sqlite3PagerWrite (pDbPage=0xa8fa470) at ./src/pager.c:4156 #6 0xb7d11fc1 in sqlite3PagerOpen (pVfs=0xa8fa470, ppPager=0xb7d36480, zFilename=0xffffffff , nExtra=-1075745228, flags=-1075745224, vfsFlags=1) at ./src/pager.c:2110 #7 0xb7d2f625 in sqlite3VdbeHalt (p=0xa8fa470) at ./src/vdbeaux.c:1142 #8 0xb7d12243 in yy_find_shift_action (pParser=0x0, iLookAhead=1 '\001') at parse.c:1442 #9 0xb7d12347 in yy_destructor (yymajor=---- _2007-Sep-11 14:58:54 by anonymous:_ {linebreak} Just a shot in the dark - do you have the correct system header files for your version of GLIBC? ---- _2007-Sep-11 15:02:42 by anonymous:_ {linebreak} sqlite 3.5 does not call pthread_getspecific ---- _2007-Sep-11 15:59:51 by anonymous:_ {linebreak} After a little fight, I rollbacked everything on my system to older versions. I'll see whether this problem reappears when Trac 0.11 is released. Meanwhile you can close the ticket, since I cannot provide anymore further info. It must be some sort of configuration issue. Glibc headers might be a good guess. Maybe the latest 3.5.0 was not installed correctly. ---- _2007-Sep-11 16:17:03 by drh:_ {linebreak} Anonymous users can close tickets in CVSTrac. You could have closed this ticket yourself. #c8c8c8 2636 code closed 2007 Sep anonymous 2007 Sep 1 1 Bug in sqlite3_prepare_v2 If we prepare two statements using sqlite3_prepare_v2, lets say DELETE FROM table WHERE ID = ?;{linebreak} and{linebreak} UPDATE table SET ID = ID - ? WHERE ID > ?; at the same time, bind values to it Then execute each of the statements, they get executed, but the changes to the database from the second (UPDATE) statement does not show up in the database. _2007-Sep-11 07:21:12 by danielk1977:_ {linebreak} Can you post a short program to demonstrate the problem? From the report, I'm not sure exactly how to reproduce it. _2007-Sep-12: by drh:_ {linebreak} Unable to reproduce and original submitter is not responding. #cfe8bd 2633 build fixed 2007 Sep anonymous 2007 Sep 1 1 building with gcc3.3.4 - undefined reference to `sqlite3KeywordCode' I'm using gcc 3.3.4 with glibc 2.3.2 trying to cross compile for arm. My configure command line: ../sqlite-3.4.2/configure --disable-readline --disable-tcl --host=arm-linux --prefix=/usr/local/opt/crosstool/arm-linux/gcc-3.3.4-glibc-2.3.2/arm-linux/ During the build I have to compile lemon and mkkeywordhash for my native system and restart the build. Otherwise the build goes smoothly until I get here: ... ranlib .libs/libsqlite3.a creating libsqlite3.la (cd .libs && rm -f libsqlite3.la && ln -s ../libsqlite3.la libsqlite3.la) ./libtool --mode=link gcc -I/usr/local/opt/crosstool/arm-linux/gcc-3.3.4-glibc-2.3.2/arm-linux/include -I. -I../sqlite-3.4.2/src -DNDEBUG -DTHREADSAFE=0 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -L/usr/local/opt/crosstool/arm-linux/gcc-3.3.4-glibc-2.3.2/arm-linux/lib -DHAVE_READLINE=0 \ -o sqlite3 ../sqlite-3.4.2/src/shell.c libsqlite3.la \ gcc -I/usr/local/opt/crosstool/arm-linux/gcc-3.3.4-glibc-2.3.2/arm-linux/include -I. -I../sqlite-3.4.2/src -DNDEBUG -DTHREADSAFE=0 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -DHAVE_READLINE=0 -o .libs/sqlite3 ../sqlite-3.4.2/src/shell.c -L/usr/local/opt/crosstool/arm-linux/gcc-3.3.4-glibc-2.3.2/arm-linux/lib ./.libs/libsqlite3.so -Wl,--rpath -Wl,/usr/local/opt/crosstool/arm-linux/gcc-3.3.4-glibc-2.3.2/arm-linux//lib ./.libs/libsqlite3.so: undefined reference to `sqlite3KeywordCode' ./.libs/libsqlite3.so: undefined reference to `keywordCode' collect2: ld returned 1 exit status make: *** [sqlite3] Error 1 I'm trying to build the shared libraries for use on an arm system. I'd just build the sqlite3.c amalgamation into my apps, but I had a grocery list of build errors in gcc 4.1.2 on my native system and I didn't think my gcc3.3.4 cross-compiler would fair much better. So I'd like to at least get the shared libs working. Any help would be appreciated. I figured out that the build does not warn you if keywordhash.h is not created. Perhaps it should or the build will fail. So after compiling the mkkeywordhash tool for the native machine: gcc -g -o mkkeywordhash ../sqlite-3.4.2/tool/mkkeywordhash.c I then had run: ./mkkeywordhash >keywordhash.h ... manually before restarting the build. Build completes without errors now, but build tools/code generators are inherently non-cross-compiler friendly when they are part of the main build process and have to be compiled into machine executable form before being run. Any possibility of moving these to an interpreter-based language that doesn't require compiling? #c8c8c8 2626 code closed 2007 Sep anonymous 2007 Sep 1 1 The Amalgamation compile error when i compile The Amalgamation 3.4.2 with visual studio 2003 in window, i encounted 3 errors : 'sqlite3IsIdChar':unknown size 'sqlite3OpcodeNames':unkown size 'sqlite3UpperToLower':unkown size i think it is caused by following source code in sqlite3.c: #ifndef SQLITE_PRIVATE # define SQLITE_PRIVATE static #endif it should be: #ifndef SQLITE_PRIVATE # define SQLITE_PRIVATE #endif is that right? It looks like vs2003 doesn't like this kind of forward decl: static int aArray[]; ... static int aArray[] = {1, 2, 3}; But is fine with this: int aArray[]; ... int aArray[] = {1, 2, 3}; By defining SQLITE_PRIVATE as an empty string, you are avoiding constructs like the first block above. It's not ideal, but not harmful either. ---- _2007-Sep-06 13:11:51 by anonymous:_ {linebreak} This is fixed in 3.5. #cfe8bd 2624 code fixed 2007 Sep anonymous 2007 Sep 1 1 Invalid documentation references in sqlite.h.in Documentation in sqlite.h.in contains invalid references to the following functions and #defines: *: SQLITE_LOCK_READ -> _SHARED *: sqlite3_register_vfs -> vfs_register *: sqlite3_unregister_vfs -> vfs_unregister *: sqlite3_find_vfs -> vfs_find *: sqltie3_blob_size -> sqltie3_blob_bytes *: sqlite3_finalise -> sqlite3_finalize ( s vs. z) *: sqlite_column_text -> missing 3 *: sqltie3_blob_size -> ti mixed up #cfe8bd 2623 code fixed 2007 Sep anonymous 2007 Sep 1 1 zeroblob(-1) returns non-NULL from sqlite3_column_blob() Please consider this psydo-code: sqlite3_prepare ("select zeroblob(-1)", DB, &Stmt); sqlite3_step(Stmt); sqlite3_column_blob(Stmt, 0) == NULL; // Error here! sqlite3_column_blob() used to return NULL for "zeroblob(-1)" as well as any other negative length value for SQLite 3.4.2. After the 3.5.0 changes it returns an arbitrary non-NULL value. sqlite3_column_bytes() returns 0 both before and after the changes. I suppose that the pre 3.5.0 NULL result is correct and meaningful. Or is it in fact undefined? #c8c8c8 2618 code closed 2007 Sep anonymous 2007 Sep linus 4 1 Dabase is Locked I have copied my executables and binaries to my target with Sqlite Database. But When i try to access the Database I get, Error:Database is Locked and memory fault occurs. _2007-Sep-04 03:13:48 by drh:_ {linebreak} SQLite version 2.8.0 dates back to February of 2003. We might consider doing a patch on a branch of version 2.8.17 if someone happens to find a very serious bug and explains the bug clearly. This ticket does neither. Please take further discussion to the mailing list. Tnx. #cfe8bd 2616 code fixed 2007 Sep anonymous 2007 Sep 1 1 Flags parameter not honoured in openDatabase() The flags parameter of openDatabase() is not honored. At least I did not find any further reference to it in the function body. As a result, calling sqlite3_open_v2 with the SQLITE_OPEN_READWRITE flag creates a new database file even though, as advertised in the documentation, it should not. #cfe8bd 2615 code fixed 2007 Sep anonymous 2007 Sep 1 1 winRandomness without effect due to < vs. > mixup os_win.c line 1424 checks that if( sizeof(LPSYSTEMTIME)>=nBuf ), supposedly to make sure that the supplied buffer is never overwritten. However, the comparison should be <= instead. The current implementation prevents that the buffer is filled at all because the check always fails. #c8c8c8 2614 code closed 2007 Sep anonymous 2007 Sep 1 1 Custom VFS still calls default xRandomness If a default VFS is registered, any custom VFS's xRandomness callback is never invoked. The only reference to the xRandomness call is hard coded in random.c, line 65: sqlite3OsRandomness(sqlite3_vfs_find(0), 256, k); This makes it impossible to implement custom random seeds for any VFS other than the default. Correct. This fact has now been made explicit in the documentation. #cfe8bd 2612 code fixed 2007 Sep anonymous 2007 Sep 1 1 enterMutex() misnamer enterMutex() in mem1.c is wrongly named as enterMem(). This results in linker errors due to missing enterMutex() function. #cfe8bd 2611 code fixed 2007 Sep anonymous 2007 Sep 2 1 AV calling sqlite3_vfs_register() Calling sqlite3_vfs_register() as the first thing in an application raises an AV. Rationale: vfsList in os.c is not initialized until the first call of sqlite3_vfs_find(). Thus calling sqlite3_vfs_find() before sqlite3_vfs_register() functions as a workaround, but can be easily forgotten. #c8c8c8 2609 code closed 2007 Sep anonymous 2007 Sep 1 1 Is non-thread safe binaries dangerous for multi-threaded app design ? Can accessing two threads ( one is the main app one ) to one connection silently corrupt in-memory data so that next commit to write the wrong ones to the disk, for the non-threadsafe binaries ? _2007-Sep-01 20:45:04 by anonymous:_ {linebreak} Forgot to mention: only the main app thread writes ( calls COMMIT), the other thread only scrolls & reads the data. ---- _2007-Sep-01 20:57:07 by anonymous:_ {linebreak} You have to compile sqlite with -DTHREADSAFE=1 if you are to use it in multiple threads in the same program. This question really belongs on the mailing list, not the bug ticket system. ---- _2007-Sep-01 22:13:40 by anonymous:_ {linebreak} Then this issue should be noted on the top of official site, and there should also be thread-safe binaries in the download area. #f2dcdc 2607 event active 2007 Aug anonymous 2007 Sep 2 1 Data loss, continuation to Re: [sqlite] how to flush database to disk? See that mailing list. The originator message : ======================== I've just lost a couple of days' worth of data when my app crashed. (Well, the data wasn't a total loss thanks to backup plans, but the database itself essentially reverted to its state of 2 days ago.) This is despite my app doing a COMMIT after every modification of the DB. It's acting as though all the changes were held in memory, or somehow journaled, and when the crash happened, the changes were all lost or rolled back. What I need is a way to force the database to save its data to disk while my app is running, so that in the event of a crash, I lose little or no data. How can I do this? I presume that closing the database would do the trick, but is there a less heavy-handed way? =========== The exact like that data loss occured at me too. Three times, non-repropucible regualrilly. What common in these losses ? {Editing+committing} in the main thread then {navigating, bof/eof checking, reading data} from within different threads then return to the main thread for {editing+committing}. _2007-Aug-31 19:38:26 by drh:_ {linebreak} The COMMIT does not actually occur until you call sqlite3_reset() and/or sqlite3_finalize() on all your prepared statements. Any prepared statement that has not been reset or finalized is still running, is incomplete, and is thus still holding the transaction open. I'm guessing that you have an unreset and unfinialized statement in your application. I wonder what would happen if we changed the definition of COMMIT so that it returned an error if there were active prepared statements. This is, technically, an incompatibility. But we are coming up on a release with several other minor incompatibilities, so now might be a good time to insert such a change. ---- _2007-Aug-31 19:45:36 by drh:_ {linebreak} I looked in the code, and it turns out we already do this. Perhaps the application is not checking the return code from the COMMIT to see that it is failing? ---- _2007-Aug-31 20:40:47 by anonymous:_ {linebreak} No error reports came from COMMIT. The data loss were noticed after UPDATE & COMMIT after massive reading of results of SQL addressing the same virtual (ATTACHed) tables, from within another thread. ---- _2007-Aug-31 20:53:48 by anonymous:_ {linebreak} Is it possible that a pending transaction survive app shutdown & then OS restart ? Is yes, then any DB error would cause rollback to the data on last BEGIN, isn't ? ---- _2007-Aug-31 21:00:18 by anonymous:_ {linebreak} But me committed each smallest change to dat,a then saw these refreshed data in the tables. And on the next day these data were present. Only reading (with full scrolling ) the affected virtual tables from within another thread then new editing then committing caused the loss. ---- _2007-Sep-01 23:12:31 by anonymous:_ {linebreak} You mention virtual tables. Their data is not maintained by the SQLite engine, but by your own module. If that doesn't implement ACID, you're out of luck. By definition:{linebreak} {quote: A virtual table is an interface to an external storage or computation engine that appears to be a table but does not actually store information in the database file.} references:{linebreak}{link: http://www.sqlite.org/lang_createvtab.html CreateVirtualTable}{linebreak} {wiki: VirtualTables VirtualTables} ---- _2007-Sep-04 04:51:15 by anonymous:_ {linebreak} Me was wrong. These were't true virtual tables. Me used a LEFT OUTER query to several tables residing in different ATTACHed databases. ---- _2007-Sep-04 04:52:01 by anonymous:_ {linebreak} were't => were not, above. #cfe8bd 2602 code fixed 2007 Aug anonymous 2007 Aug 1 1 ATTACH leaks memory Attaching a non-existing file to an empty database leaks memory. The scenario (Win32): *: Create a new empty database. *: sqlite3_prepare_v2 ( "ATTACH DATABASE 'C:\NonExistantFolder\NonExistantFile.db3' AS a;'); returns SQLITE_OK *: sqlite3_step(); returns SQLITE_ERROR *: sqlite3_finalize(); returns SQLITE_ERROR *: Close database and application. This returns a report about a single leak between 77 and 92 bytes in size. FYI: I also receive a 2nd, but smaller (21-28 bytes), leak when executing an FTS1 SELECT on an attached database. If you can confirm the 1st leak I will provide more information on the 2nd, if it still persists. _2007-Aug-30 14:21:25 by drh:_ {linebreak} I am unable to replicate the problem on Linux. My tests show all allocated memory is freed. Both SQLite's own internal accounting reports this, and valgrind too. We observe that the windows OS driver calls malloc() directly, bypassing SQLites internal memory allocator and its accounting procedures. And we do not know of anything like valgrind for windows. So perhaps there is a leak in the windows OS driver someplace. How do you see this leak, Ralf? What tools are you using? Do you get any indication where the leak is occurring? Can you see anything in the windows OS driver that might be causing the problem? ---- _2007-Aug-30 15:37:03 by anonymous:_ {linebreak} Found it: The if block in os_win.c, starting at line 1136, does not free zConverted after the else. The fix is to move the free(zConverted); line up into the 1st if block: if( h==INVALID_HANDLE_VALUE ){ free(zConverted); if( flags & SQLITE_OPEN_READWRITE ){ return winOpen(0, zName, id, ((flags|SQLITE_OPEN_READONLY)&~SQLITE_OPEN_READWRITE), pOutFlags); }else{ return SQLITE_CANTOPEN; } } #c8c8c8 2601 code closed 2007 Aug anonymous 2007 Oct 1 1 Problem with opening database with Unicode filename Problem with opening database with Unicode filename - it doesn't open the database with Unicode filename or path. The description contains insufficient information for us to act on the problem. #cfe8bd 2600 code fixed 2007 Aug anonymous 2007 Aug 1 1 #ifdef SQLITE_MUTEX_WIN32 naming error in mutex_w32.c? Line 23 in mutex_w32.c checks #ifdef SQLITE_MUTEX_WIN32. However, SQLITE_MUTEX_WIN32 is never defined in the SQLite core. Instead, SQLITE_MUTEX_WIN is (notice the missing 32 postfix). mutex_w32.c line 182 #endif /* SQLITE_MUTEX_WIN */ makes me believe that this is just a spelling error? If there are plans for extra support of non-Win32 platforms (Win64, Windows 3.1), it would be reasonable to choose _WIN32 over just _WIN. But then I suppose that this would also be true for the OS_WIN #define as well? #cfe8bd 2599 code fixed 2007 Aug anonymous 2007 Aug 1 1 OS check in mutex.h has no effect OS check in mutex.h (starting from line 34) dependes on OS autodetection in os.h. However, in sqliteInt.h the statement #include "mutex.h" is in line 76, way before #include "os.h" is followed in line 297. OS autodetection is therefore not taken into account for mutex.h and the library is compiled without mutexes, at least in Win32. It did work before mutex.c was split up for different OSes. #cfe8bd 2597 code fixed 2007 Aug anonymous 2007 Oct 1 1 tclsqlite "db eval" with array-name doesn't have "*" entry. (tcl8.5a7) This problem was caused on tcl cvs head(tcl8.5a7) only. Tcl8.4 and 8.5a6 doesn't have the problem. package require sqlite3 sqlite db :memory: db eval "create table t (c1, c2, c3)" db eval "PRAGMA empty_result_callbacks = on" db eval "select * from t" val "" puts [array get val] I expected a value "* {c1 c2 c3}", but the val(*) entry didn't exist. It seems to me that "Tcl_DecrRefCount(pStar);" cause the problem... _2007-Aug-30 03:18:14 by anonymous:_ {linebreak} I'm sorry for the bad format. package require sqlite3{linebreak} sqlite db :memory:{linebreak} db eval "create table t (c1, c2, c3)"{linebreak} db eval "PRAGMA empty_result_callbacks = on"{linebreak} db eval "select * from t" val ""{linebreak} puts [array get val]{linebreak} #c8c8c8 2593 code closed 2007 Aug anonymous 2007 Aug 1 1 btshared unlock bug You have want to avoid a race condition by zeroing p->locked within the mutex. Otherwise another thread may acquire the lock, set locked to 1 (while still holding the mutex) and then this thread outside the mutex will unintentionally clear it. @@ -191,6 +193,7 @@ p->wantToLock--; if( p->wantToLock==0 ){ + p->locked = 0; sqlite3_mutex_leave(p->pBt->mutex); - p->locked = 0; } } } Haven't looked at the code, there may be other occurances of this. _2007-Aug-28 17:57:26 by drh:_ {linebreak} The p->locked variable is protected by a separate mutex, specifically the mutex at p->pSqlite->mutex. The database handle, the thing that sqlite3_open() returns, is at p->pSqlite. Each such handle has its own mutex. Associated with each database handle is one or more Btree structures, one for each open database. There are typically two of these, one for the main database and another for the TEMP database. New Btree structures are added for each ATTACH. Each Btree is tied to a single database handle so it is protected by the same mutex as the database handle. "p" is a pointer to a Btree in this instance. The guts of a database are contained in a Btshared structure. Btree points to Btshared. But if shared cache is enabled, two or more Btree structures from different database handles might point to the same Btshared. So Btshared needs its own mutex. This new mutex covers only the Btshared. The Btree structure is covered by the original database handle mutex. See also #2578 #c8c8c8 2592 warn closed 2007 Aug anonymous 2007 Aug 1 1 The Entry C:\Windows\Temp\sqlite_z6onAYdsVGqLG3Q is Invalid Everytime I reboot and check disk I get " The Entry C:\Windows\Temp\sqlite_z6onAYdsVGqLG3Q is Invalid I believe this has something to do with skype and all messengers rebooting my PC everytime I try to enable voice Thank you This is a question for a skype group. #cfe8bd 2588 code fixed 2007 Aug anonymous 2007 Aug 1 1 bug in sqlite3_mutex_leave Race scenario on same mutex leading to corruption: Thread 1 Thread 2 -------------------------- -------------------------- sqlite3_mutex_enter pthread_mutex_lock ok p->owner = Thread 1 p->nRef = 1; sqlite3_mutex_leave p->nRef--; pthread_mutex_unlock ok sqlite3_mutex_enter sqlite3_mutex_enter pthread_equal is true pthread_equal is false pthread_mutex_lock ok p->owner = Thread 2; p->nRef = 1; p->nRef>0 is true p->nRef++; p->nRef is now 2 and the mutex is "owned" by Thread 2. Any data structure protected by this mutex can now be changed by both threads at the same time. Here's a possible pseudo fix:, yypminor=0x1) at parse.c:1337 #10 0xb7d1279c in sqlite3Parser (yyp=0xa8fa470, yymajor= , yyminor= {z = 0xa8fec48 "system", dyn = 0, n = 86973048}, pParse=0xb7d39bc8) at parse.c:3268 #11 0xb7d1288f in sqlite3Parser (yyp=0xbfe1734c, yymajor=-1075744936, yyminor= {z = 0xa906428 "�l\212\no", dyn = 0, n = 86973072}, pParse=0xbfe17048) at parse.c:3477 #12 0xb7cf46da in ?? () at ./src/btree.c:2498 from /usr/lib/libsqlite3.so.0 #13 0xbfe1734c in ?? () #14 0xb7d39bc8 in ?? () from /usr/lib/libsqlite3.so.0 #15 0x0a906428 in ?? () #16 0x0a5e3520 in ?? () #17 0xbfe17048 in ?? () #18 0xb7f18300 in ?? () from /lib/ld-linux.so.2 #19 0xb7d16c56 in sqlite3Pragma (pParse=0xbfe1734c, pId1=0xa8fec48, pId2=0x0, pValue=0xb757a700, minusFlag=173792084) at ./src/pragma.c:601
Index: src/mutex.c =================================================================== RCS file: /sqlite/sqlite/src/mutex.c,v retrieving revision 1.9 diff -u -3 -p -r1.9 mutex.c --- src/mutex.c 24 Aug 2007 20:46:59 -0000 1.9 +++ src/mutex.c 25 Aug 2007 01:39:10 -0000 @@ -358,6 +358,7 @@ void sqlite3_mutex_leave(sqlite3_mutex * assert( p->nRef>0 ); p->nRef--; if( p->nRef==0 ){ + memset(&p->owner, 0, sizeof(p->owner)); pthread_mutex_unlock(&p->mutex); } }But even this fix is not guaranteed by POSIX standards, as an opaque pthread_t type when memset to zero may indeed be a valid pthread_t for the first (zeroeth) thread. This could lead to the same race condition and corruption. http://mail-archives.apache.org/mod_mbox/apr-dev/200307.mbox/%3c3F285220.7010501@netscape.com%3e This stuff is difficult to get right. Please consult correct recursive mutex implementations from various multi-threaded open source projects. _2007-Aug-25 02:35:35 by anonymous:_ {linebreak} Furthermore, the pthread_equal man page states: "If either t1 or t2 are not valid thread IDs, the behavior is undefined." http://www.opengroup.org/onlinepubs/009695399/functions/pthread_equal.html Only pthread_create and pthread_self can generate a valid pthread_t thread ID. ---- _2007-Aug-25 02:43:32 by anonymous:_ {linebreak} On POSIX systems that support it you should use PTHREAD_MUTEX_RECURSIVE instead of rolling your own. It is more efficient and guaranteed to be correct. http://www.opengroup.org/onlinepubs/000095399/functions/pthread_mutex_lock.html ---- _2007-Aug-25 03:23:56 by anonymous:_ {linebreak} Amusing back story of recursive POSIX mutexes from one of its creators: http://groups.google.com/group/comp.programming.threads/msg/d835f2f6ef8aed99?hl=en ---- _2007-Aug-25 04:13:08 by drh:_ {linebreak} I believe the problem is fixed by doing the p->nRef>0 test first and then the ownership test second. Rationale: 1: If p->nRef>0 it can only mean that some thread is holding the mutex. we don't know which thread, and the mutex might change owners multiple times before the next test. But we do at least know that somebody has held the mutex in the past. And we also know that p->owner has been initialized. 2: If the second pthread_equal() test is true, that means that the current thread claims to own the mutex. No other thread will ever put the current thread's threadid into p->owner. If p->owner==self, then only the current thread put it there. If another thread had claimed the mutex after the current thread, then the other thread would have changed the value of p->owner before raising p->nRef above zero. We know that p->nRef has been above zero, so the current thread must be the last one to have held the mutex. Hence, the current thread must still own the mutex. The above assumes that pthread_equal() is an atomic operation. In other words, it will not fail with a TRUE return if a separate thread changes the value of p->owner while the current thread is testing it. If p->owner is a multi-word value, then pthread_equal() might not be atomic. But pthread_t is an integer on every implementation of pthreads that I am aware of, so I think this is probably safe in most instances. Please double-check my reasoning above and let me know if I've said or done something goofy. Tnx. ---- _2007-Aug-25 04:25:26 by drh:_ {linebreak} Response to Butenhof: Threading in SQLite is not designed to make it more concurrent. It is designed to make the code safer to use by people who insist on writing multithreaded programs when multithreading really isn't necessary. Each database connection has its own mutex. Any thread using the database connection holds that mutex. So only a single thread can use a particular database connection at a time. The mutex is acquired when the API is entered and released when the API exits. But SQLite is reentrant. Top-level APIs can (and are) invoked as callbacks from within other top-level APIs. The only way to allow reentancy for one thread while excluding other threads (the only way that *I* know of, anyhow) is a recursive mutex. If David Butenhof thinks that recursive mutexes can be eliminated from SQLite by do "careful design", I'd like to hear him tell me how. One suspects that Mr. Butenhof has never tried to build a reentrant library for use in a multithreaded environment.... ---- _2007-Aug-25 04:47:52 by anonymous:_ {linebreak} My first impression is that the fix is not correct. Take a look at this post and the accompanying thread: http://groups.google.com/group/comp.programming.threads/msg/329ba20fe934b5e1?hl=en& pthread_equal() is probably not atomic on HPUX where pthread_t is a struct (of 4 int's as I recall). Nothing in the pthread_equal man page suggests that pthread_equal is guaranteed to be atomic, although on platforms where pthread_t is an int it would obviously be fine. Also, there is no concept of an official invalid pthread_t value - and even if there was, the pthread_equal man page is pretty clear that the result of such a comparison with an "invalid" pthread ID is undefined. For what it is worth, Apache Portable Runtime seems to have given up trying to roll their own recursive mutex on POSIX platforms that do not support PTHREAD_MUTEX_RECURSIVE. I appreciate that your use of recursive mutexes is for correctness and ease of API use. An alternate scheme would be to change the SQLite API to simply pass messages via work queues to a background thread that performs the database operations on your behalf. Much like your previous single thread "server" model. But then you have the headache of making every single SQLite API function work asynchronously - and you still would not improve concurrency on the same database. ---- _2007-Aug-25 05:05:17 by anonymous:_ {linebreak} This is somewhat related. When you work with SMP hardware and various optimizing compilers some things are not obvious. Take a look at the seemingly logical double-checked lock mis-pattern: http://www.cs.umd.edu/~pugh/java/memoryModel/DoubleCheckedLocking.html http://en.wikipedia.org/wiki/Double-checked_locking ---- _2007-Aug-25 14:45:21 by drh:_ {linebreak} Well, it turns out that SuSE Linux does support recursive mutexes after all. But in order to get it to work you have to have a line #define _XOPEN_SOURCE 500 prior to your #include
+ if( !isInit ){ + pthread_mutex_lock(&initMutex); + if( !isInit ){ + pthread_mutexattr_init(&recursiveAttr); + pthread_mutexattr_settype(&recursiveAttr, PTHREAD_MUTEX_RECURSIVE); + } + isInit = 1; + pthread_mutex_unlock(&initMutex); + }Although the liklihood of a problem occurring is rare, it is still present. Since SQLite does not create many recursive mutexes I don't think it is worth to try to optimize this case. The calls to pthread_mutexattr_init and pthread_mutexattr_settype are typically very fast. Please consider using this simpler code instead: case SQLITE_MUTEX_RECURSIVE: { p = sqlite3MallocZero( sizeof(*p) ); if( p ){ pthread_mutexattr_t recursiveAttr; pthread_mutexattr_init(&recursiveAttr); pthread_mutexattr_settype(&recursiveAttr, PTHREAD_MUTEX_RECURSIVE); pthread_mutex_init(&p->mutex, &recursiveAttr); p->id = iType; } break; } ---- _2007-Aug-25 16:26:01 by anonymous:_ {linebreak} Forgot the pthread_mutexattr_destroy... case SQLITE_MUTEX_RECURSIVE: { p = sqlite3MallocZero( sizeof(*p) ); if( p ){ pthread_mutexattr_t recursiveAttr; pthread_mutexattr_init(&recursiveAttr); pthread_mutexattr_settype(&recursiveAttr, PTHREAD_MUTEX_RECURSIVE); pthread_mutex_init(&p->mutex, &recursiveAttr); pthread_mutexattr_destroy(&recursiveAttr); p->id = iType; } break; } ---- _2007-Aug-25 16:34:59 by drh:_ {linebreak} You will notice that, unlike the examples cited previously, my double-lock was in fact correct. Nevertheless, your observation that recursive mutexes are rarely allocated (only at sqlite3_open()) and that the initialization and destruction of a pthread_mutexattr_t is relatively cheap are valid. And the code is simpler without the double-lock. So the double-lock has now been removed. ---- _2007-Aug-25 16:48:30 by anonymous:_ {linebreak} Well, I disagree about your double lock being correct. There is a window of failure on SMP machines on some hardware with certain optimizing compilers. http://www.cs.umd.edu/~pugh/java/memoryModel/DoubleCheckedLocking.html Nonetheless, the recursive mutex code as of the latest checkin is fine. The speed of millions of lock/unlock calls of native POSIX recursive mutexes more than makes up for the few cycles of initializing the attribute. Some older OSes will undoubtedly have trouble with _XOPEN_SOURCE, but this is easy enough for the odd port to fix. #c8c8c8 2585 code closed 2007 Aug anonymous 2007 Oct anonymous 1 1 Error Function GetAtomNameA The Procedure entry point GetAtomNameA could not be located in the dynamic link library sqlite3.dll Using CodeBlock _2007-Oct-05 15:11:29 by drh:_ {linebreak} Nothing in SQLite invokes GetAtomNameA(). This must be a problem with some other library. #c8c8c8 2581 code closed 2007 Aug anonymous 2007 Aug 1 1 ./configure && make broken: new source files: mem[12].c, mutex.c, etc Please apply the diff given by this command to Makefile.in: cvs diff -D'2007-08-14' main.mk _2007-Aug-21 20:31:44 by drh:_ {linebreak} We are in the middle of a major reimplementation of large sections of SQLite. We could fix the makefile, but the build still wouldn't work. So what's the point, really. I promise to fix the makefile before the next release.... #c8c8c8 2578 code closed 2007 Aug anonymous 2007 Aug 1 1 sqlite3BtreeEnter, sqlite3BtreeLeave threading issues p->wantToLock++ is not an atomic operation and must be done within a mutex lock, otherwise its result is undefined. Same goes for "variable--". You need an architecture-specific operations to do that safely between threads. See http://www.mozilla.org/projects/nspr/reference/html/pratom.html and http://lxr.mozilla.org/nspr/source/nsprpub/pr/include/pratom.h#60 for an example.
void sqlite3BtreeEnter(Btree *p){ Btree *pLater; /* Some basic sanity checking on the Btree. The list of Btrees ** connected by pNext and pPrev should be in sorted order by ** Btree.pBt value. All elements of the list should belong to ** the same connection. Only shared Btrees are on the list. */ assert( p->pNext==0 || p->pNext->pBt>p->pBt ); assert( p->pPrev==0 || p->pPrev->pBtThis needs fixing:pBt ); assert( p->pNext==0 || p->pNext->pSqlite==p->pSqlite ); assert( p->pPrev==0 || p->pPrev->pSqlite==p->pSqlite ); assert( p->sharable || (p->pNext==0 && p->pPrev==0) ); /* Check for locking consistency */ assert( !p->locked || p->wantToLock>0 ); assert( p->sharable || p->wantToLock==0 ); if( !p->sharable ) return; p->wantToLock++; if( p->locked ) return; /* In most cases, we should be able to acquire the lock we ** want without having to go throught the ascending lock ** procedure that follows. Just be sure not to block. */ if( sqlite3_mutex_try(p->pBt->mutex)==SQLITE_OK ){ p->locked = 1; return; }
Index: src/btree.c =================================================================== RCS file: /sqlite/sqlite/src/btree.c,v retrieving revision 1.401 diff -u -3 -p -r1.401 btree.c --- src/btree.c 17 Aug 2007 16:50:38 -0000 1.401 +++ src/btree.c 17 Aug 2007 20:42:49 -0000 @@ -1403,8 +1403,8 @@ void sqlite3BtreeEnter(Btree *p){ assert( pLater->pNext==0 || pLater->pNext->pBt>pLater->pBt ); assert( !pLater->locked || pLater->wantToLock>0 ); if( pLater->locked ){ - sqlite3_mutex_leave(pLater->pBt->mutex); pLater->locked = 0; + sqlite3_mutex_leave(pLater->pBt->mutex); } } sqlite3_mutex_enter(p->pBt->mutex); @@ -1427,8 +1427,8 @@ void sqlite3BtreeLeave(Btree *p){ p->wantToLock--; if( p->wantToLock==0 ){ assert( p->locked ); - sqlite3_mutex_leave(p->pBt->mutex); p->locked = 0; + sqlite3_mutex_leave(p->pBt->mutex); } } }Otherwise there would be corruption if there was a context switch just after sqlite3_mutex_leave to "if( p->locked ) return;" in sqlite3BtreeEnter() in another thread. You might want to replace the u8's below with int's if you intend to read or write to them outside of a mutex:
struct Btree { sqlite3 *pSqlite; /* The database connection holding this btree */ BtShared *pBt; /* Sharable content of this btree */ u8 inTrans; /* TRANS_NONE, TRANS_READ or TRANS_WRITE */ u8 sharable; /* True if we can share pBt with other pSqlite */ u8 locked; /* True if pSqlite currently has pBt locked */ int wantToLock; /* Number of nested calls to sqlite3BtreeEnter() */ Btree *pNext; /* List of Btrees with the same pSqlite value */ Btree *pPrev; /* Back pointer of the same list */ };Reading from and writing to a non-machine word (i.e., not an int) variable outside of a mutex is not atomic or threadsafe. MIPS, for example, can only manipulate 32 bit values at a time and must use bit operations and shifting to get at individual characters. If you read a char value at the wrong time it may contain 0, 255 or some other value. _2007-Aug-17 21:32:11 by anonymous:_ {linebreak} These 3 values will likely be stored in the same 32 bit integer: u8 inTrans; /* TRANS_NONE, TRANS_READ or TRANS_WRITE */ u8 sharable; /* True if we can share pBt with other pSqlite */ u8 locked; /* True if pSqlite currently has pBt locked */ Writing to any one of them outside of a mutex in a multithreaded situation can scramble the others' values. ---- _2007-Aug-20 00:49:28 by drh:_ {linebreak} Not a bug. The Btree.wantToLock values are accessed under the sqlite3.pMutex lock - or at least they will be once we get the new threading implementation half way completed. ---- _2007-Aug-20 05:17:47 by anonymous:_ {linebreak} Helgrind is useful to find these sorts of multi-thread race conditions, as well as mutex deadlocks. http://valgrind.org/docs/manual/hg-manual.html #c8c8c8 2576 code closed 2007 Aug anonymous 2007 Aug 1 1 Check-in [4239] mutex race conditions Nevermind. I missed seeing SQLITE_MUTEX_STATIC_MASTER. #c8c8c8 2575 code closed 2007 Aug anonymous 2007 Aug 1 1 sqlite3_mutex_enter missing return I know this is experimental #if'd out code, but nonetheless, sqlite3_mutex_enter is missing a return for the final else clause. #c8c8c8 2572 warn closed 2007 Aug anonymous 2007 Aug 1 1 Sqlitw error unrecognize token Sqlite error unrecognize token The "is human" test needs to be improved. ---- _2007-Aug-14 21:20:44 by anonymous:_ {linebreak} wa? #c8c8c8 2571 todo closed 2007 Aug anonymous 2007 Aug 1 1 SQLite opening in Access I am trying to open SQLite database through ODBC connections in Microsoft Access 2000. Some tables in Database have Primary key defined . Steps followed:- System DSN TestSQLite is created using Data sources(ODBC) 1)Open Blank access database named db1.mdb 2)Click on FIle->Get External data->Link tables 3)Select - ODBC databases in Files of type in Link Dialog 4)Select Machine data source TestSQLite=20 5)Select All Tables and click Ok 6)If table does not have Primary key ,getting dialog to select unique record identifier. 7)if Primary key is defined in table ,getting error Reserved error (-7748) There is no msg for this error . =20 I can open tables which does not have Primary Key and cannot use MSAccess to open SQLite database through ODBC (if the SQLite database has primary key defined) . Thanks for your help. _2007-Aug-13 21:23:54 by anonymous:_ {linebreak} Install this sqlite odbc driver. http://www.ch-werner.de/sqliteodbc/ #f2dcdc 2566 build active 2007 Aug anonymous 2007 Aug 2 1 fts2 broken after vacuum Hi there, I'm testing your database and I'm having problems with fts2: --------- sqlite> select * from distB where distB match "MARIANO"; Assertion failed: *pData!='\0', file fts2amal.c, line 16790 This application has requested the Runtime to terminate it in an unusual way. Please contact the application's support team for more information. --------- Steps: 1) Create a new .db 2) Import data in new distA table 3) Import data in new distB table 4) Create a new distC virtual table (dts2) 5) insert into distC (rowid, f1, f2, f3) select rowid, f1, f2, f3 from DistB Everything working like a charm until here!!! The fts2 works very well, but after 6) vacuum; the fts seems broken... doing a select throws the error I paste at the post of the topic If you want the .db file I can send it to you (607MB) Thanks.- #cfe8bd 2565 code fixed 2007 Aug drh 2007 Aug 1 1 Database corruption following ROLLBACK when soft_heap_limit set With {link: /pragma.html#pragma_auto_vacuum autovacuum mode} turned on low {link: /capi3ref.html#sqlite3_soft_heap_limit sqlite3_soft_heap_limit} database corruption results following a {link: /lang_transaction.html ROLLBACK} of a CREATE TABLE. The following script exhibits the problem: -- set sqlite3_soft_heap_limit to 5000 PRAGMA auto_vacuum=1; CREATE TABLE t1(x); INSERT INTO t1 VALUES(hex(randomblob(5000))); BEGIN; CREATE TABLE t2 AS SELECT * FROM t1; ROLLBACK; PRAGMA integrity_check; This problem was discovered following the enhanced sqlite_soft_heap_limit testing facility added by check-in [4202]. As of this writing, it has not been observed in the wild. _2007-Aug-11 11:55:04 by drh:_ {linebreak} **Background** The rollback journal file consists of a header followed by zero or more page records. Each page record contains original database content which can be used to restore the database back to its original state during a ROLLBACK or when the database is being recovered after a power-loss or crash. The header contains, among other things, the "nRec" field which is a count of the number of pages to follows. Whenever changes are flushed to the database file, the size of the journal is padded out to the next multiple of the disk sector size and a new header is written. (This is a defense against certain kinds of file corruption that might occur during a power failure.) A single journal can contain any number of instances of header followed by zero or more pages. While the journal is being constructed, the nRec value is 0. Then, just prior to writing changes back to the database file, the nRec value is changed to the correct number of records in the journal, the journal file is synced to disk, and only then is it safe to write to the database file. This all works great. **The Problem** The problem is that the ROLLBACK command also uses the journal file to restore changes. But the ROLLBACK command treats the nRec==0 value specially. When the ROLLBACK command sees an nRec of 0, it figures that it was just in the middle of doing some changes that haven't yet by written to the database file, so it tries to recompute the "correct" nRec value based on the current file size. The problem arises when a header contains an nRec value which really is suppose to be zero (no pages written into the journal) but that header is followed by one or more additional headers that contain pages that do need to be rolled back. When a journal is being rolled back during recovery, the recovery process sees the zero nRec, skips to the next header, and every thing works peachy. But during the ROLLBACK command, the zero nRec is converted into some positive value based on the journal file size. The new positive nRec is wrong. The ROLLBACK quickly discovers this and ignores the rest of the journal file under the assumption that it corrupted. This results in an incomplete ROLLBACK and database file corruption. **The Fix** The fix in check-in [4208] is to change the ROLLBACK process so that it only applies the special interpretation to an nRec==0 header if it is the very last header in the journal. **Why We Have Not Seen This Before** The problem only comes up when a header is written into the journal with nRec==0 and subsequent headers are written afterwards. This can only happen when the pager is having great difficulty finding new buffers into which new pages can be loaded and is having to spill dirty pages back to the disk. The minimum cache size is 10, and as it turns out a cache size of 10 still gives a handful of buffered pages before a cache spill occurs, even under the most severe circumstances. So even with a very small cache, the nRec field was always ending up larger than 0. But, if you set the soft heap limit to a ridiculously small number - so small that the pager is under continuous pressure to spill pages back to the database file as soon as they are unpinned - then there are cases where the pager will write non-terminal headers to the journal with nRec==0. Hence, as far as we are aware, you cannot hit this problem as long as you leave the soft heap limit disabled (which is the default configuration) or you set the soft heap limit large enough so that there are always enough memory for the pager to hold 10 or more pages in memory at the same time. It is difficult to imagine a real-world system that would do something different, so even though this problem has serious consequences, we cannot see how it might actually come up in practice. The problem was only noticed when we started stress-testing the soft heap limit mechanism by setting very low soft heap limits and running full regression tests. #c8c8c8 2563 code closed 2007 Aug anonymous 2007 Aug 1 1 sqlite3_column_database_name16() is broken =sqlite3_column_database_name16()= does not work at all. However I form my query, regardless of how many fields and in what order are returned in result, it always returns NULL for every single column. I'm using this code and it never returned anything but NULL: if ( pStatement) { UINT32 cols = sqlite3_column_count( pStatement); for ( UINT16 currCol = 0; !isDatabaseNameRetrieved && ( currCol < cols ); ++currCol ) { const WCHAR *databaseName = static_cast
Try this. It gives 2 but obviously the answer is 3 since the average of 1,2,3 is 2 and multiplying that by 3/2 gives 3. C:\tmp2>sqlite3 SQLite version 3.4.0 Enter ".help" for instructions sqlite> sqlite> create table tab (x num); sqlite> insert into tab values (1); sqlite> insert into tab values (2); sqlite> insert into tab values (3); sqlite> select * from tab; 1 2 3 sqlite> select sqlite_version(*); 3.4.0 sqlite> sqlite> select 3/2 * avg(x) from tab; 2.0_2007-Jul-30 04:10:09 by anonymous:_ {linebreak} Sorry, please ignore this one. I just released its doing integer arithmetic. #c8c8c8 2538 code closed 2007 Jul anonymous 2007 Jul 1 1 calling sqlite3_interrupt() randomly leaves db->ul.isInterrupted = 1 Using a sequence as follows, all invoked with sqlite3_exec(): BEGIN; ... long running script with many UPDATE/INSERT statements (with triggers firing, etc)... sqlite3_interrupt() is called Often (always?) the next sqlite3_exec call will also return SQLITE_INTERRUPT instead of SQLITE_OK. Apparently there is at least one point in sqlite execution that can be interrupted with sqlite3_interrupt() where db->ul.isInterrupted is not reset to 0, so the next sqlite3_exec() call also to returns SQLITE_INTERRUPT. Since the documentation states that calling sqlite3_interrupt() will rollback any EXPLICIT transactions (why specifically EXPLICIT ?? - I would assume auto [IMPLICIT] transactions would obviously also rollback with a sqlite3_interrupt call), any subsequent action done on the sqlite instance should be SQLITE_OK (or the error specific to that action since the previous call has been fully aborted). This behavior is definitely different than 3.2.8 (the version I am attempting to upgrade from), which works as expected. After much additional testing, I have concluded that this may actually have been due to my error (sqlite3_interrupt() being called again) and not due to sqlite. Sorry for the wasted bandwidth. Please consider the issue closed. #c8c8c8 2531 code closed 2007 Jul anonymous 2007 Jul a.rottmann 1 1 Can't find head files in icu.c /* Include ICU headers */ #include
CREATE TABLE "mfSysUsers" ("ID" numeric PRIMARY KEY , "Username" text (20) , "Password" text (20) ) CREATE TABLE "mfSysUserPermissions" ("UserID" numeric PRIMARY KEY , "StartUpForm" text (128) , "Flags" numeric ) CREATE TABLE "mfSysUsersToForms" ("PermissionID" numeric PRIMARY KEY , "UserID" numeric , "FormTitle" text (128) , "Flags" numeric ) CREATE TABLE mfSysSyncLog (ID integer IDENTITY PRIMARY KEY, UID integer, Sync nvarchar(19), Type integer, Records integer, FormName nvarchar(32), FailureReason integer) CREATE TABLE mfSysTrackingLog (ID integer IDENTITY PRIMARY KEY, UID integer, LogIn nvarchar(19), LogOut nvarchar(19), FormName nvarchar(32), Session integer) CREATE TABLE "mfForm_Welcome" ("SessionID" numeric PRIMARY KEY , "UID" numeric , "Flags" numeric , "Expiry" numeric , "Spare" numeric , "D3_txtAccessCode" text , "D3_txtMsgDay" text , "D3_txtUser" text , "D3_txtDesc" text , "D3_txtH" text , "D3_txtErr" text , "D3_txtDontSend" text , "D3_DEVICE" text , "D3_txtCode" text ) CREATE TABLE "mFSysVersion" (asdasd CHAR) CREATE INDEX 'IndexUserName_mfSysUsers' ON 'mfSysUsers' (Username ASC ) CREATE INDEX 'IndexUserID_mfSysUsers' ON 'mfSysUsers' (ID DESC ) CREATE INDEX [IdxTrackingLog] ON [mfSysTrackingLog] (ID) SELECT Username FROM mfSysUsers; SELECT Username FROM mfSysUsers; SELECT Username FROM mfSysUsers WHERE Username='Guest' SELECT Username FROM mfSysUsers WHERE Username='Guest' SELECT Password FROM mfSysUsers WHERE Username='Guest' SELECT Password FROM mfSysUsers WHERE Username='Guest' SELECT ID FROM mfSysUsers WHERE Username='Guest' SELECT ID FROM mfSysUsers WHERE Username='Guest' SELECT StartUpForm, Flags FROM mfSysUserPermissions WHERE UserID = 1; SELECT StartUpForm, Flags FROM mfSysUserPermissions WHERE UserID = 1; CREATE TABLE "mfForm_Welcome" ("SessionID" numeric PRIMARY KEY , "UID" numeric , "Flags" numeric , "Expiry" numeric , "Spare" numeric , "D3_txtAccessCode" text , "D3_txtMsgDay" text , "D3_txtUser" text , "D3_txtDesc" text , "D3_txtH" text , "D3_txtErr" text , "D3_txtDontSend" text , "D3_DEVICE" text , "D3_txtCode" text ); SELECT MAX(SessionID) FROM "mfForm_Welcome"; SELECT MAX(SessionID) FROM "mfForm_Welcome"; INSERT INTO mfSysTrackingLog (UID, Login, Session, FormName) VALUES (1, '2007-07-17 11:39:25', 1, 'Welcome'); SELECT MAX(ID) FROM mfSysTrackingLog; SELECT MAX(ID) FROM mfSysTrackingLog;Step 2. This is on thread 2
BEGIN; SELECT SessionID FROM "mfForm_PartFields" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_PartFields" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_testaa" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_testaa" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_testbb" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_testbb" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Issue Photos" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Issue Photos" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Inspections Guide" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Inspections Guide" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Street Inspections" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Street Inspections" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Street Issues" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Street Issues" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Street Inspections List" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Street Inspections List" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Inspection Photos" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Inspection Photos" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Menu" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Menu" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_IssuePCN" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_IssuePCN" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_IssuePCNStartUp" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_IssuePCNStartUp" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_PCNNoGenerator" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_PCNNoGenerator" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_PCNShift" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_PCNShift" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_DogFouling" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_DogFouling" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_OffenceList" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_OffenceList" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_PCNShiftDetails" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_PCNShiftDetails" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_IssuePCNMain" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_IssuePCNMain" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_AuditsToClaim" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_AuditsToClaim" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Check Item" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Check Item" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Audit" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Audit" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_AuditClaim" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_AuditClaim" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_AIMS" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_AIMS" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Main" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Main" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_CheckItemActions" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_CheckItemActions" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_HRA" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_HRA" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_TEST LISTS2" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_TEST LISTS2" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_OrderList_OL" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_OrderList_OL" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Statement_ST" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Statement_ST" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_LastOrderList" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_LastOrderList" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_StatementData_SD" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_StatementData_SD" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Receipt_R" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Receipt_R" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_ReceiptReport_RR" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_ReceiptReport_RR" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Form 1aa" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Form 1aa" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_ReceiptReportData_RRD" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_ReceiptReportData_RRD" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_testpush" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_testpush" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_pushlist" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_pushlist" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_eeeee" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_eeeee" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_PushTestData" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_PushTestData" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_PushTestUpdate" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_PushTestUpdate" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_JobDet" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_JobDet" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_userdet" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_userdet" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_VisitDetails" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_VisitDetails" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Welcome" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Welcome" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Photo" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Photo" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_JobFields" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_JobFields" WHERE Flags IN (2,3) GROUP BY SessionID; DROP TABLE 'mfForm_TEST LISTS'; DROP TABLE 'mfForm_Debug'; DROP TABLE 'mfForm_asfsd'; DROP TABLE 'mfForm_Street Issues List'; DROP TABLE 'mfForm_Check Groups'; DROP TABLE 'mfForm_OrderEntry_OE'; DROP TABLE 'mfForm_PCNList'; DROP TABLE 'mfForm_ProductEntry_PE'; DROP TABLE 'mfForm_PushTest'; DROP TABLE 'mfForm_NullTest'; DROP TABLE 'mfForm_Order details'; DROP TABLE 'mfForm_test1'; DROP TABLE 'mfForm_Form 1'; DROP TABLE 'mfForm_Form 3'; DROP TABLE 'mfForm_Form 2'; DROP TABLE 'mfForm_Options'; DROP TABLE 'mfForm_PartDetails'; DROP TABLE 'mfForm_Jobs'; DROP TABLE 'mfForm_Confirm'; DROP TABLE 'mfForm_Unfinished'; DROP TABLE 'mfForm_Errors'; DROP TABLE 'mfForm_JobOptions'; SELECT * FROM "mfSysSyncLog"; SELECT * FROM "mfSysSyncLog"; SELECT * FROM "mfSysTrackingLog"; SELECT * FROM "mfSysTrackingLog"; DELETE FROM mfSysTrackingLog; COMMIT; VACUUM;Step 3. This is on Thread 1
SELECT DISTINCT("SessionID") FROM "mfForm_Welcome" WHERE UID IN (-1, 1) AND "D3_TXTCODE" LIKE "1" ORDER BY SessionID DESC;Step 4. This is on thread 2
BEGIN; SELECT SessionID FROM "mfForm_PartFields" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT name, rootpage, sql FROM 'main'.sqlite_master CREATE TABLE "mfSysUsers" ("ID" numeric PRIMARY KEY , "Username" text (20) , "Password" text (20) ) CREATE TABLE "mfSysUserPermissions" ("UserID" numeric PRIMARY KEY , "StartUpForm" text (128) , "Flags" numeric ) CREATE TABLE "mfSysUsersToForms" ("PermissionID" numeric PRIMARY KEY , "UserID" numeric , "FormTitle" text (128) , "Flags" numeric ) CREATE TABLE mfSysSyncLog (ID integer IDENTITY PRIMARY KEY, UID integer, Sync nvarchar(19), Type integer, Records integer, FormName nvarchar(32), FailureReason integer) CREATE TABLE mfSysTrackingLog (ID integer IDENTITY PRIMARY KEY, UID integer, LogIn nvarchar(19), LogOut nvarchar(19), FormName nvarchar(32), Session integer) CREATE TABLE "mfForm_Welcome" ("SessionID" numeric PRIMARY KEY , "UID" numeric , "Flags" numeric , "Expiry" numeric , "Spare" numeric , "D3_txtAccessCode" text , "D3_txtMsgDay" text , "D3_txtUser" text , "D3_txtDesc" text , "D3_txtH" text , "D3_txtErr" text , "D3_txtDontSend" text , "D3_DEVICE" text , "D3_txtCode" text ) CREATE TABLE "mFSysVersion" (asdasd CHAR) CREATE INDEX 'IndexUserName_mfSysUsers' ON 'mfSysUsers' (Username ASC ) CREATE INDEX 'IndexUserID_mfSysUsers' ON 'mfSysUsers' (ID DESC ) CREATE INDEX [IdxTrackingLog] ON [mfSysTrackingLog] (ID) SELECT SessionID FROM "mfForm_PartFields" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_testaa" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_testaa" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_testbb" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_testbb" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Issue Photos" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Issue Photos" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Inspections Guide" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Inspections Guide" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Street Inspections" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Street Inspections" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Street Issues" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Street Issues" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Street Inspections List" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Street Inspections List" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Inspection Photos" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Inspection Photos" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Menu" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Menu" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_IssuePCN" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_IssuePCN" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_IssuePCNStartUp" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_IssuePCNStartUp" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_PCNNoGenerator" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_PCNNoGenerator" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_PCNShift" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_PCNShift" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_DogFouling" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_DogFouling" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_OffenceList" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_OffenceList" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_PCNShiftDetails" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_PCNShiftDetails" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_IssuePCNMain" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_IssuePCNMain" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_AuditsToClaim" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_AuditsToClaim" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Check Item" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Check Item" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Audit" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Audit" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_AuditClaim" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_AuditClaim" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_AIMS" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_AIMS" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Main" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Main" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_CheckItemActions" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_CheckItemActions" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_HRA" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_HRA" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_TEST LISTS2" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_TEST LISTS2" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_OrderList_OL" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_OrderList_OL" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Statement_ST" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Statement_ST" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_LastOrderList" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_LastOrderList" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_StatementData_SD" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_StatementData_SD" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Receipt_R" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Receipt_R" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_ReceiptReport_RR" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_ReceiptReport_RR" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Form 1aa" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Form 1aa" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_ReceiptReportData_RRD" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_ReceiptReportData_RRD" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_testpush" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_testpush" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_pushlist" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_pushlist" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_eeeee" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_eeeee" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_PushTestData" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_PushTestData" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_PushTestUpdate" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_PushTestUpdate" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_JobDet" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_JobDet" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_userdet" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_userdet" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_VisitDetails" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_VisitDetails" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Welcome" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Welcome" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Photo" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_Photo" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_JobFields" WHERE Flags IN (2,3) GROUP BY SessionID; SELECT SessionID FROM "mfForm_JobFields" WHERE Flags IN (2,3) GROUP BY SessionID; DROP TABLE 'mfForm_TEST LISTS'; DROP TABLE 'mfForm_Debug'; DROP TABLE 'mfForm_asfsd'; DROP TABLE 'mfForm_Street Issues List'; DROP TABLE 'mfForm_Check Groups'; DROP TABLE 'mfForm_OrderEntry_OE'; DROP TABLE 'mfForm_PCNList'; DROP TABLE 'mfForm_ProductEntry_PE'; DROP TABLE 'mfForm_PushTest'; DROP TABLE 'mfForm_NullTest'; DROP TABLE 'mfForm_Order details'; DROP TABLE 'mfForm_test1'; DROP TABLE 'mfForm_Form 1'; DROP TABLE 'mfForm_Form 3'; DROP TABLE 'mfForm_Form 2'; DROP TABLE 'mfForm_Options'; DROP TABLE 'mfForm_PartDetails'; DROP TABLE 'mfForm_Jobs'; DROP TABLE 'mfForm_Confirm'; DROP TABLE 'mfForm_Unfinished'; DROP TABLE 'mfForm_Errors'; DROP TABLE 'mfForm_JobOptions'; SELECT * FROM "mfSysSyncLog"; SELECT * FROM "mfSysSyncLog"; SELECT * FROM "mfSysTrackingLog"; SELECT * FROM "mfSysTrackingLog"; SELECT MAX(SessionID) FROM "mfForm_Welcome"; SELECT MAX(SessionID) FROM "mfForm_Welcome"; CREATE TABLE "mfForm_Welcome" ("SessionID" numeric PRIMARY KEY , "UID" numeric , "Flags" numeric , "Expiry" numeric , "Spare" numeric , "D3_txtUser" text , "D3_DEVICE" text , "D3_txtDesc" text , "D3_txtMsgDay" text , "D3_txtCode" text ); INSERT INTO "mfForm_Welcome" (SessionID, UID, Flags, Expiry, Spare, "D3_txtUser", "D3_DEVICE", "D3_txtDesc", "D3_txtMsgDay", "D3_txtCode") VALUES (1,-1,1,0,0,"1", "OEYQB-8LSG1-Y1GRW-RDG9X", "John Smith", "Welcome to the Integral mForms ExpressHaulage sample from Integral Mobile Data", "1234"); COMMIT; VACUUM;Step 5. This is on thread 1 and the query that fails but should work!
SELECT DISTINCT("SessionID") FROM "mfForm_Welcome" WHERE UID IN (-1, 1) AND "D3_TXTCODE" LIKE "1234" ORDER BY SessionID DESC;_2007-Jul-19 15:48:30 by anonymous:_ {linebreak} See Ticket #2486: pragma index_list does not detect index created on 2nd connection ---- _2007-Jul-19 16:32:00 by anonymous:_ {linebreak} With multi-threaded programs becoming more prevalent due to multi-core chips, having one thread not know the schema of the database is becoming a problem. ---- _2007-Jul-19 16:37:44 by drh:_ {linebreak} This problem has nothing to do with schema loading. The error is that the change counter in the header of the database file was not being incremented following a VACUUM. ---- _2007-Jul-20 10:48:38 by anonymous:_ {linebreak} Many thanks that seems to have sorted it. #f2dcdc 2517 code active 2007 Jul anonymous 2007 Jul 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. #c8c8c8 2515 code closed 2007 Jul anonymous 2007 Jul 1 1 Entered to ASCII zeros as a text string was return a single zero. I have created a Schema that defines all but one column as a string. When I insert data that is defined as '00', '06', '05', etc. I receive no error for the insertion. But, when I retrieve the data it returns the data with the preceeding zero removed. This should not occur when the data is defined as a string and not defined to be of a numeric type. This does not occur for any other type string data. _2007-Jul-18 21:47:55 by drh:_ {linebreak} Here's what I get: CREATE TABLE t1(x TEXT); INSERT INTO t1 VALUES('00'); SELECT * FROM t1; 00 Unable to reproduce the problem. #f2dcdc 2512 code active 2007 Jul shess 2007 Jul 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 2510 code active 2007 Jul anonymous 2007 Jul 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. #f2dcdc 2509 code active 2007 Jul anonymous 2007 Jul 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 2508 code active 2007 Jul anonymous 2007 Dec 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; } #cfe8bd 2507 code fixed 2007 Jul anonymous 2007 Jul 1 1 test_expr expr-1.104 fails (64-bit int handling) make test fails with: expr-1.104...make: *** [test] Floating point exception System info: gcc-4.1.2, glibc-2.5-r4, 2.6.22.1 x86_64 _2007-Jul-16 18:56:02 by drh:_ {linebreak} Already fixed. See [4130] and [4131]. #c8c8c8 2505 code closed 2007 Jul anonymous 2007 Jul 1 1 does not return error when use sqlite_open to open an non-sqlite db I use sqlite_open api to open an non sqlite database, for example an exe file.The result of sqlite_open is not an error code but sqlite_ok. Sqlite does not actually read the database file until the first SQL query is executed. So the sqlite3_open succeeds but the first call to sqlite3_prepare_v2() or sqlite3_exec() will return an error. #c8c8c8 2504 build closed 2007 Jul anonymous 2007 Jul 1 1 sqlite 3.4.0 fails to build FreeBSD 6.2 64-bit system Attached is the configure and make output. As this is a FreeBSD 6.2 AMD 64-bit box, several "warning: cast from pointer to integer of different size" in func.c, table.c, and vdbemem.c concern me as potential bugs. Added to the fact the the undefined reference to __isnanl. The warnings also appear in 3.3.17 version of SQLite, which I hadn't noticed before, but the there was no undefined reference preventing the build. _2007-Jul-13 17:13:47 by anonymous:_ {linebreak} submitter: anthony howe, achowe@snert.com ---- _2007-Jul-13 17:34:33 by drh:_ {linebreak} Already fixed by check-in [4103]. #cacae5 2499 build defer 2007 Jul anonymous 2007 Aug drh 1 1 undefined reference to `_WinMain@16' when attempting to build I get the error: /usr/lib/gcc/i686-pc-cygwin/3.4.4/../../../libcygwin.a(libcmain.o):(.text+0xab): undefined reference to `_WinMain@16' _2007-Jul-11 18:16:28 by anonymous:_ ls Makefile.in config.sub main.mk sqlite.pc.in Makefile.linux-gcc configure mkdll.sh sqlite3.1 README configure.ac mkopcodec.awk sqlite3.pc.in VERSION contrib mkopcodeh.awk src aclocal.m4 doc mkso.sh tclinstaller.tcl addopcodes.awk ext notes test art install-sh publish.sh tool config.guess ltmain.sh spec.template www ./configure && make install checking build system type... i686-pc-cygwin checking host system type... i686-pc-cygwin checking for gcc... gcc checking for C compiler default output file name... a.exe checking whether the C compiler works... yes checking whether we are cross compiling... no checking for suffix of executables... .exe checking for suffix of object files... o checking whether we are using the GNU C compiler... yes checking whether gcc accepts -g... yes checking for gcc option to accept ISO C89... none needed checking for a sed that does not truncate output... /usr/bin/sed checking for grep that handles long lines and -e... /usr/bin/grep checking for egrep... /usr/bin/grep -E checking for ld used by gcc... /usr/i686-pc-cygwin/bin/ld.exe checking if the linker (/usr/i686-pc-cygwin/bin/ld.exe) is GNU ld... yes checking for /usr/i686-pc-cygwin/bin/ld.exe option to reload object files... -r checking for BSD-compatible nm... /usr/bin/nm -B checking whether ln -s works... yes checking how to recognise dependent libraries... file_magic ^x86 archive import|^x86 DLL checking how to run the C preprocessor... gcc -E checking for ANSI C header files... yes checking for sys/types.h... yes checking for sys/stat.h... yes checking for stdlib.h... yes checking for string.h... yes checking for memory.h... yes checking for strings.h... yes checking for inttypes.h... yes checking for stdint.h... yes checking for unistd.h... yes checking dlfcn.h usability... yes checking dlfcn.h presence... yes checking for dlfcn.h... yes checking for g++... g++ checking whether we are using the GNU C++ compiler... yes checking whether g++ accepts -g... yes checking how to run the C++ preprocessor... g++ -E checking for g77... no checking for xlf... no checking for f77... no checking for frt... no checking for pgf77... no checking for cf77... no checking for fort77... no checking for fl32... no checking for af77... no checking for xlf90... no checking for f90... no checking for pgf90... no checking for pghpf... no checking for epcf90... no checking for gfortran... no checking for g95... no checking for xlf95... no checking for f95... no checking for fort... no checking for ifort... no checking for ifc... no checking for efc... no checking for pgf95... no checking for lf95... no checking for ftn... no checking whether we are using the GNU Fortran 77 compiler... no checking whether accepts -g... no checking the maximum length of command line arguments... 8192 checking command to parse /usr/bin/nm -B output from gcc object... ok checking for objdir... .libs checking for ar... ar checking for ranlib... ranlib checking for strip... strip checking for correct ltmain.sh version... yes checking if gcc supports -fno-rtti -fno-exceptions... no checking for gcc option to produce PIC... checking if gcc static flag -static works... yes checking if gcc supports -c -o file.o... yes checking whether the gcc linker (/usr/i686-pc-cygwin/bin/ld.exe) supports shared libraries... yes checking whether -lc should be explicitly linked in... yes checking dynamic linker characteristics... Win32 ld.exe checking how to hardcode library paths into programs... immediate checking whether stripping libraries is possible... yes checking if libtool supports shared libraries... yes checking whether to build shared libraries... yes checking whether to build static libraries... yes configure: creating libtool appending configuration tag "CXX" to libtool checking for ld used by g++... /usr/i686-pc-cygwin/bin/ld.exe checking if the linker (/usr/i686-pc-cygwin/bin/ld.exe) is GNU ld... yes checking whether the g++ linker (/usr/i686-pc-cygwin/bin/ld.exe) supports shared libraries... yes checking for g++ option to produce PIC... checking if g++ static flag -static works... yes checking if g++ supports -c -o file.o... yes checking whether the g++ linker (/usr/i686-pc-cygwin/bin/ld.exe) supports shared libraries... yes checking dynamic linker characteristics... Win32 ld.exe checking how to hardcode library paths into programs... immediate appending configuration tag "F77" to libtool checking for a BSD-compatible install... /usr/bin/install -c checking for gawk... gawk Version set to 3.4 Release set to 3.4.0 Version number set to 3004000 checking whether to support threadsafe operation... no checking whether to allow connections to be shared across threads... no checking whether threads can override each others locks... no checking whether to support shared library linked as release mode or not... no checking whether to use an in-ram database for temporary tables... no checking if executables have the .exe suffix... unknown checking host system type... (cached) i686-pc-cygwin checking for Tcl configuration... found /usr/lib/tclConfig.sh checking for existence of /usr/lib/tclConfig.sh... loading checking for library containing tgetent... -ltermcap checking for readline in -lreadline... no checking readline.h usability... no checking readline.h presence... no checking for readline.h... no checking for /usr/include/readline.h... no checking for /usr/include/readline/readline.h... no checking for /usr/local/include/readline.h... no checking for /usr/local/include/readline/readline.h... no checking for /usr/local/readline/include/readline.h... no checking for /usr/local/readline/include/readline/readline.h... no checking for /usr/contrib/include/readline.h... no checking for /usr/contrib/include/readline/readline.h... no checking for /mingw/include/readline.h... no checking for /mingw/include/readline/readline.h... no checking for library containing fdatasync... none required checking for usleep... yes checking for fdatasync... yes configure: creating ./config.status config.status: creating Makefile config.status: creating sqlite3.pc gcc -g -O2 -o lemon.exe ./tool/lemon.c cp ./tool/lempar.c . cp ./src/parse.y . ./lemon.exe parse.y mv parse.h parse.h.temp awk -f ./addopcodes.awk parse.h.temp >parse.h cat parse.h ./src/vdbe.c | gawk -f ./mkopcodeh.awk >opcodes.h sort -n -b -k 3 opcodes.h | gawk -f ./mkopcodec.awk >opcodes.c gcc -g -O2 -o mkkeywordhash.exe ./tool/mkkeywordhash.c ./mkkeywordhash.exe >keywordhash.h sed -e s/--VERS--/3.4.0/ ./src/sqlite.h.in | \ sed -e s/--VERSION-NUMBER--/3004000/ >sqlite3.h rm -rf tsrc mkdir -p tsrc cp ./src/alter.c ./src/analyze.c ./src/attach.c ./src/auth.c ./src/btree.c ./src/btree.h ./src/build.c ./src/callback.c ./src/complete.c ./src/date.c ./src/delete.c ./src/expr.c ./src/func.c ./src/hash.c ./src/hash.h ./src/insert.c ./src/legacy.c ./src/loadext.c ./src/main.c ./src/malloc.c ./src/os.c ./src/os_unix.c ./src/os_win.c ./src/os_os2.c ./src/pager.c ./src/pager.h ./src/parse.y ./src/pragma.c ./src/prepare.c ./src/printf.c ./src/random.c ./src/select.c ./src/shell.c ./src/sqlite.h.in ./src/sqliteInt.h ./src/table.c ./src/tclsqlite.c ./src/tokenize.c ./src/trigger.c ./src/utf.c ./src/update.c ./src/util.c ./src/vacuum.c ./src/vdbe.c ./src/vdbe.h ./src/vdbeapi.c ./src/vdbeaux.c ./src/vdbeblob.c ./src/vdbefifo.c ./src/vdbemem.c ./src/vdbeInt.h ./src/vtab.c ./src/where.c ./ext/fts1/fts1.c ./ext/fts1/fts1.h ./ext/fts1/fts1_hash.c ./ext/fts1/fts1_hash.h ./ext/fts1/fts1_porter.c ./ext/fts1/fts1_tokenizer.h ./ext/fts1/fts1_tokenizer1.c sqlite3.h ./src/btree.h ./src/btreeInt.h ./src/hash.h ./src/limits.h opcodes.h ./src/os.h ./src/os_common.h ./src/sqlite3ext.h ./src/sqliteInt.h ./src/vdbe.h parse.h ./ext/fts1/fts1.h ./ext/fts1/fts1_hash.h ./ext/fts1/fts1_tokenizer.h ./src/vdbeInt.h tsrc cp: warning: source file `./src/btree.h' specified more than once cp: warning: source file `./src/hash.h' specified more than once cp: warning: source file `./src/sqliteInt.h' specified more than once cp: warning: source file `./src/vdbe.h' specified more than once cp: warning: source file `./ext/fts1/fts1.h' specified more than once cp: warning: source file `./ext/fts1/fts1_hash.h' specified more than once cp: warning: source file `./ext/fts1/fts1_tokenizer.h' specified more than once cp: warning: source file `./src/vdbeInt.h' specified more than once rm tsrc/sqlite.h.in tsrc/parse.y cp parse.c opcodes.c keywordhash.h tsrc tclsh ./tool/mksqlite3c.tcl cc sqlite3.c -o sqlite3 /usr/lib/gcc/i686-pc-cygwin/3.4.4/../../../libcygwin.a(libcmain.o):(.text+0xab): undefined reference to `_WinMain@16' collect2: ld returned 1 exit status make: *** [sqlite3] Error 1 ---- _2007-Jul-11 18:17:17 by anonymous:_ {linebreak} when initially filing this bug, i was told to not paste large screen dumps. i was also promised that i would be able to add an attachment later. however, that option was not given, so I have attached a screen transcript. ---- _2007-Jul-11 18:18:33 by anonymous:_ {linebreak} my email is metaperl at gmail.com ---- _2007-Jul-11 18:38:53 by drh:_ {linebreak} The [Attach] link in the upper-left is what you use to add attachments. The sqlite3.c source file is a library, not a program. If you want to compile a command-line shell, add the "shell.c" source file: cc -o sqlite3 sqlite3.c shell.c ---- _2007-Jul-23 20:35:06 by anonymous:_ {linebreak} I believe this bug report should be reopened because this error comes when you try to configure and compile USING THE DEFAULTS. When you try to do a standard configure and make, it should either try to compile shell.c as is needed or not compile sqlite.c. It shouldn't put in a command that fails the compile by default. Sorry to bother you about this, but I don't see how this isn't a bug or at least a glitch. It's a problem in the configuration and makefile system. ---- _2007-Aug-15 03:26:05 by anonymous:_ {linebreak} Compile and link passed by following patch (at least for me). diff -ru sqlite-3.4.2/Makefile.in sqlite-3.4.2-new/Makefile.in --- sqlite-3.4.2/Makefile.in 2007-08-15 10:37:18.921875000 +0900 +++ sqlite-3.4.2-new/Makefile.in 2007-08-15 10:36:08.750000000 +0900 @@ -681,7 +681,7 @@ mkdir -p doc mv $(DOC) doc -install: sqlite3 libsqlite3.la sqlite3.h ${HAVE_TCL:1=tcl_install} +install: sqlite3$(TEXE) libsqlite3.la sqlite3.h ${HAVE_TCL:1=tcl_install} $(INSTALL) -d $(DESTDIR)$(libdir) $(LTINSTALL) libsqlite3.la $(DESTDIR)$(libdir) $(INSTALL) -d $(DESTDIR)$(exec_prefix)/bin ---- _2007-Aug-22 12:33:17 by anonymous:_ {linebreak} Why is this bug closes and marked "not a bug"? sqlite fails on make install on Cygwin (as in untar/configure/make/make install), which is *clearly* a bug. The "fix" posted above does not work, make install fails instantly with tclsh ../sqlite-3.4.2-n/tclinstaller.tcl 3.4 can't read "env(DESTDIR)": no such variable while executing "set LIBDIR $env(DESTDIR)$env(TCLLIBDIR)" (file "../sqlite-3.4.2-n/tclinstaller.tcl" line 11) make: *** [tcl_install] Error 1 ---- _2007-Aug-22 12:53:45 by drh:_ {linebreak} I misread the problem. Cygwin is not a supported platform. So in that sense, this is *not* clearly a bug in SQLite. This is an imcompatibility with Cygwin. We will take the problem under advisement, but since Cygwin is not officially support and because we have many much more serious problems to work on, progress on this is likely to be slow. You can expedite a fix by posting a patch. #c8c8c8 2494 code closed 2007 Jul anonymous 2007 Jul 1 1 DRow["ID"] = reader.GetString(reader.GetOrdinal("ID")); fails DRow["ID"] = reader.GetString(reader.GetOrdinal("ID")); This fails under Vista Ultimate, succeeds under XP Pro. It is Sqlite 3.1, will upgrading help? Thanks! Jim Please post your question to the author of the sqlite wrapper for whatever language you're using. #f2dcdc 2491 code active 2007 Jul anonymous 2007 Jul 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. #c8c8c8 2490 code closed 2007 Jul anonymous 2007 Oct 1 1 Latest cvs 3.4.0 seg fault This problem is the same with 3.3.1.13, Fedora's 3.4.0 and the debug version I just built. Using latest Fedora and perl modules: net1#r mx mxaddrs ... perl : 5.8.8 DBI : 1.58 DBD::SQLite : 1.13 SQLite::DBMS: 3.4.0.1 Code is simple db create and inserts. Problem only occurs when a DBI sth->prepare() is used with multiple execute inserts of duplicate data. Instead of returning an error seg fault occurs. If required I can try and put together a small code sample. _2007-Jul-07 06:25:54 by anonymous:_ Why do you think its pure SQLite problem ? ---- _2007-Jul-07 11:46:55 by drh:_ {linebreak} To amplify the remark by anonymous above: what makes you think this is a problem with SQLite and not a problem with perl or the DBD::SQLite module? We have run literally millions of test cases through the core SQLite without hitting any segfaults, yet you say that a simple test case is sufficient to cause the problem. That would seem to implicate DBD::SQLite rather than the SQLite core, would it not? Please tell us why you think this is an SQLite problem. And please also provide us with a reproducible test case, keeping in mind that the core SQLite developers do not normally have DBD::SQLite installed. ---- _2007-Aug-10 15:18:32 by anonymous:_ {linebreak} This is a DBD::SQLite bug. See http://rt.cpan.org/Public/Bug/Display.html?id=28757 for a test case and a patch. Mike #c8c8c8 2489 build closed 2007 Jul anonymous 2007 Jul 1 1 Latest cvs 3.4.0 make test fails related to TCL Here's how I'm building. I've tried --disable-tcl and --enable-tcl with no joy. I'm using latest Fedora 7 with tcl-8.4.13 installed. mkdir -p /build/work/sqlite-3.4.0.1 cd /build/work/sqlite-3.4.0.1 unset CDPATH export CFLAGS='-pipe -O3 -g' make distclean cvs -d :pserver:anonymous@www.sqlite.org:/sqlite -r update . ./configure --prefix=/usr/local/pkgs/sqlite-3.4.0.1 --disable-tcl make groupadd vuser || /bin/true useradd -M -g vuser -d /vhost/davidfavor.com/users/david -s /bin/zsh david || /bin/true useradd -M -g vuser -d /vhost/livefeast.com/users/yemiah -s /bin/zsh yemiah || /bin/true chown david:vuser -R . su -c "make test" david /build/work/sqlite-3.4.0.1/./src/tclsqlite.c:2416: undefined reference to `Tcl_CreateObjCommand' /build/work/sqlite-3.4.0.1/./src/tclsqlite.c:2417: undefined reference to `Tcl_PkgProvide' /build/work/sqlite-3.4.0.1/./src/tclsqlite.c:2418: undefined reference to `Tcl_CreateObjCommand' ... hundreds of errors deleted ... "make test" requires Tcl to function. Post the result of "grep -i tcl config.log" after you run ./configure with no arguments. The key to it working is finding tclConfig.sh
configure:19220: checking for Tcl configuration configure:19302: result: found /usr/lib/tclConfig.sh configure:19305: checking for existence of /usr/lib/tclConfig.sh ac_cv_c_tclconfig=/usr/lib HAVE_TCL='1' TCL_BIN_DIR='/usr/lib' TCL_INCLUDE_SPEC='-I/usr/include/tcl8.4.13' TCL_LIBS='-ldl -lpthread -lieee -lm' TCL_LIB_FILE='libtcl8.4.so' TCL_LIB_FLAG='-ltcl8.4' TCL_LIB_SPEC='-L/usr/lib -ltcl8.4' TCL_SRC_DIR='/usr/include/tcl8.4.13' TCL_STUB_LIB_FILE='libtclstub8.4.a' TCL_STUB_LIB_FLAG='-ltclstub8.4' TCL_STUB_LIB_SPEC='-L/usr/lib -ltclstub8.4' TCL_VERSION='8.4'---- _2007-Jul-07 03:49:21 by anonymous:_ {linebreak} Ah I see now. I'm using a 64 bit machine so tcl lives in the 64 bit directory hierarchy. Here's how I got most of the tests to run: export CFLAGS='-pipe -O3 -g -DSQLITE_DISABLE_DIRSYNC=1 -Wall' make distclean cvs -d :pserver:anonymous@www.sqlite.org:/sqlite -r update . ./configure --prefix=/usr/local/pkgs/sqlite-3.4.0.1 --enable-tcl --with-tcl=/usr/lib64 This ticket can close. I'll do some further testing and open another ticket about the single thread test that fails. ---- _2007-Jul-07 11:43:05 by drh:_ {linebreak} Please note that this is an open ticketing system. Anybody can close a ticket. You do not need to ask me or Dan to do it. #f2dcdc 2487 code active 2007 Jul anonymous 2007 Jul 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 2479 code active 2007 Jun anonymous 2007 Jun 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. #c8c8c8 2472 code closed 2007 Jun anonymous 2007 Jun 1 1 smart quotes turning question marks while storing in oracle 9i DB whenever end-user copy & paste the statement from the word which contains smart quotes into the java application and submit, it stores in the oracle 9i database. But the smart quotes are getting converted into question mark (?) / reverse question mark symbol. Kindly tell me the solution. Environment: Java 2.0,Jrun 4.0, Oracle 9.2.0.1.0 Where does SQLite figure into the system? #c8c8c8 2471 secure closed 2007 Jun anonymous 2007 Jun 1 1 a bug for 64 bit driver under operation system Firstly,I can't find a 64bit driver just like a.so file under the specail system Unix/Linxu. Secondly,I find a drvier writen by java,but it has many problem while multi threads operate the database. Sometimes I lost all of my data. Can you offer a 64bit driver under Unix/Linux in your website? This is probably better discussed on the mailing list. Any bugs in a java wrapper (which one) can be figured out there too. #f2dcdc 2469 build active 2007 Jun anonymous 2007 Jul 1 1 test fails on Solaris I have a problem running the test suite on Solaris 9. Build was done using gcc 4.2.0. The build completes without error but many tests fail. I've created my own minimal test that exhibits the problem:
set testdir [file dirname $argv0] source $testdir/tester.tcl db close file delete -force test.db test.db-journal sqlite db test.db do_test tdb-1 { execsql { PRAGMA auto_vacuum = 1; BEGIN; CREATE TABLE t1(a, b); } execsql { COMMIT; } } {} integrity_check tdb-2 finish_testWhen running this test I get the following output:
tdb-1... Ok tdb-2... Expected: [ok] Got: [{*** in database main *** List of tree roots: 2nd reference to page 1 Page 3 is never used}] Thread-specific data deallocated properly 1 errors out of 3 tests Failures on these tests: tdb-2This error happens on lots of, but not all, tests. I'm happy to do whatever is necessary to help debug this. Thanks, Tim. _2007-Jun-27 10:44:14 by anonymous:_ {linebreak} Further to this, it appears to be related to gcc 4.2.0. It works fine with gcc 3.4.6. ---- _2007-Jun-28 09:54:35 by anonymous:_ {linebreak} Further more, it doesn't appear to be specific to Solaris. The same problem occurs on Linux with gcc 4.2.0. So I guess the subject of this ticket should be changed to "build/test problems with gcc 4.2.0". This is probably a significant problem - the build completes find but the resultant code is broken. People may not notice this until it's too late. ---- _2007-Jun-28 12:24:05 by drh:_ {linebreak} I installed gcc 4.2.0 on my SuSE linux i686 desktop and built test harnesses under three different configurations: gcc420 -g -O0 -Wall -fstrict-aliasing gcc420 -g -O3 -Wall gcc420 -g -O3 -fstrict-aliasing -fomit-frame-pointer The first two configurations used separate source files. The third configuration was built using the amalgamation. I ran the "quick" test under all configurations. All tests ran to completion with no errors. ---- _2007-Jun-28 13:22:20 by anonymous:_ {linebreak} Two ideas: 1. Compile with gcc 4.2.0 using -O0 instead of -O2 and see what happens. Disable any other optimizations you may have. 2. Run truss with full read/write buffer display on the gcc 3.4.6 compiled testfixture running your simple test case and compare its output to the gcc 4.2.0 compiled test case. ---- _2007-Jul-01 19:00:40 by anonymous:_ {linebreak} I've done tests with optimisation, and this appears to tickle the problem. With no optimisation, -O, -O0, -O1 and -03 it works. With -O2 and -Os it's broken. I was compiling with -O2 when I submitted the initial report. Tim. ---- _2007-Jul-01 19:54:54 by drh:_ {linebreak} I can reproduce the problem now on Linux when compiling as follows: gcc420 -g -O2 -Wall ---- _2007-Jul-01 21:50:42 by drh:_ {linebreak} This appears to be a bug in GCC 4.3.0. A work-around is to compile with the -fno-tree-vrp option. GCC appears to miscompile a single loop within the logic that implements the integrity_check PRAGMA. The code that gets miscompiled is in the file vdbe.c (lines numbers added): 4308 for(j=0; j
RCS file: /sqlite/sqlite/src/vdbe.c,v retrieving revision 1.636 diff -u -3 -p -r1.636 vdbe.c --- src/vdbe.c 1 Jul 2007 21:18:40 -0000 1.636 +++ src/vdbe.c 21 Jul 2007 19:10:13 -0000 @@ -4306,7 +4306,8 @@ case OP_IntegrityCk: { pnErr = &p->aMem[j]; assert( (pnErr->flags & MEM_Int)!=0 ); for(j=0; ju.i; } aRoot[j] = 0; popStack(&pTos, nRoot);