[sqlite] affinity critical problem with 3.8.5 - IN single value optimisation
Hi,
It seems there's a problem with 3.8.5 and its affinity behavior.
It's quite critical.
--- CUT ---
sqlite> CREATE TABLE T (v text);
sqlite> insert into T values('1');
sqlite> insert into T values('2');
sqlite> select v from T where v=1;
1
sqlite> select v from T where v='1';
1
sqlite> select v from T where v IN(1);
sqlite> select v from T where v IN('1');
1
sqlite> select v from T where v IN(1,2);
1
2
sqlite> select v from T where v IN('1','2');
1
2
--- /CUT ---
It must be linked to this change:
"Render expressions of the form "x IN (?)" (with a single value in the list on
the right-hand side of the IN operator) as if they where "x==?", Similarly
optimize "x NOT IN (?)""
Best regards,
--
Guillaume FOUGNIES
Eulerian Technologies
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: Re[2]: [sqlite] how can i speed up inserts ?
Wed, Feb 07, 2007 at 07:36:10PM +0200: DragonK wrote: > On 2/7/07, Teg <[EMAIL PROTECTED]> wrote: > > > >Hello ohadp, > > > >Well, my experience is, particularly when it's users using it and not > >a personal project, that corruption happens fairly frequently when you > >use this pragma. That's why I don't use it any more in my production > >code. > > > >Transactions are far safer and fast too. > > > >Indeed, transactions are safer. > > But I wonder, why did you experienced corruption with this pragma? Was it > because of crashes of the OS or the application? Or are there other factors > which can corrupt the data if not syncing ? As I understood from the > documentation, the only thing that can corrupt data when using this pragma > are crashes and power failures. Hello, If you don't care of corruption on crashes and power failures, you can modify sqlite to write your temporary '-journal' files in a ramfs mount directory. DB file: /opt/sqlite/mydb Journal in smtg like: /ramfs/opt#sqlite#mydb-journal -- Guillaume FOUGNIES Eulerian Technologies - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Virtual tables and locking.
hi, Is it possible to add some informations about locking mechanism in the wiki page VirtualTables? I'm facing concurrency problem in developing a cluster Sqlite database. One table per database. Multiple databases indexed like x.db, x.db-001, x.db-002, x.db-003, ... each containing a choosen number of rows (like 100K rows with incremental prikey). Conceptually, this should increase concurrent access to data... 1) "CREATE VIRTUAL TABLE" applied on the x.db 2) Update a row in x.db-001 via x.db vtab 3) Update a row in x.db-002 via x.db vtab I'm afraid each update processed on the x.db to modify x.db-001 via the virtual table module could create a lock on x.db and x.db-001. So the trick will be useless. I was thinking integrating some other storage engine like PostgreSQL or MySQL with virtual tables... but the locking concurrency problem could be the same. Any ideas? Thanks. -- Guillaume - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Question about threads
Hello, A patch for this: http://www.sqlite.org/cvstrac/tktview?tn=1417 We are using it since September on our systems without any problem. Bye. Thu, Dec 22, 2005 at 10:04:31AM +0100: Martin Engelschalk wrote: > Hello Eduardo, > > SQLite does not uses threads in itself. Every call to sqlite executes in > the context of the calling thread. > There is a restriction explained in > http://www.hwaci.com/sw/sqlite/faq.html#q8 > > I hope this helps. > Martin > > Eduardo schrieb: > > >Hello: > >We are going to implement p-threads in our embebbed OS and want to > >know how SQLite uses them. Each database will have its own thread or > >each part of SQLite (tokenizer, parser, btree...) have its own thread > >or a mixture of both? -- Guillaume FOUGNIES Eulerian Technologies
[sqlite] bug with progress handler and sql functions.
Hello, Using sqlite3_progress_handler with functions in the sql request (like ifnull()) seems to make the nProgressOps (in vdbe.c) not increment properly. Set a sqlite3_progress_handler with a frequency of 10 virtual machine opcodes. The sql request contains 9 calls on ifnull. sqlite3_step never activate the progress handler. I've corrected the problem by adding a nProgressOpsI in the sqlite3 structure which increments properly despite many sqlite3VdbeExec calls. The patch is trivial. Bye. -- Guillaume FOUGNIES Eulerian Technologies
Re: [sqlite] Share an sqlite3 struct between multiple threads
So SQLite is not "portable" on some Unix (more precisely on recent linux NPTL where file locks seems broken). i submitted a patch this month: http://www.sqlite.org/cvstrac/tktview?tn=1417 Best regards, Wed, Sep 21, 2005 at 02:56:48PM +0100: Christian Smith wrote: > On Tue, 20 Sep 2005, Marco Bambini wrote: > > >I know based on http://www.sqlite.org/faq.html#q8 that it is not safe > >to share the same sqlite3 structure between multiple threads. > >But what if I protect its access with a mutex? > >It is safe? > > > Until recently, by chance, yes, on some platforms. Recent versions of > SQLite explicitly disallow it and return an error if tried. > > Christian -- Guillaume FOUGNIES Eulerian Technologies
Re: [sqlite] DEFAULT PAGE SIZE option = malformed database schema
Ok, i saw the Check-in #2724. You should make in pager.h: #if SQLITE_DEFAULT_PAGE_SIZE > SQLITE_MAX_PAGE_SIZE #error "blah blah" #endif Mon, Sep 19, 2005 at 02:53:53PM -0400: D. Richard Hipp wrote: > On Mon, 2005-09-19 at 13:42 -0400, D. Richard Hipp wrote: > > On Mon, 2005-09-19 at 19:04 +0200, Guillaume Fougnies wrote: > > > Hello, > > > > > > During the upgrade from 3.2.5 to 3.2.6, i removed my > > > compile time option SQLITE_DEFAULT_PAGE_SIZE. > > > > > > 3.2.6 is not able to access anymore to databases !?! > > > SQLite version 3.2.6 > > > Enter ".help" for instructions > > > sqlite> .schema > > > Error: malformed database schema > > > > > > > > Recompile with -DSQLITE_MAX_PAGE_SIZE=32768 or ...=16384. > Should work then. -- Guillaume FOUGNIES Cell: +33 6 23869151 Eulerian Technologies Fax : +33 1 43665802 52 bis rue ramponeau ICQ : 19261222 75020 Paris - FRANCE MSN : [EMAIL PROTECTED]
[sqlite] DEFAULT PAGE SIZE option = malformed database schema
Hello, During the upgrade from 3.2.5 to 3.2.6, i removed my compile time option SQLITE_DEFAULT_PAGE_SIZE. 3.2.6 is not able to access anymore to databases !?! SQLite version 3.2.6 Enter ".help" for instructions sqlite> .schema Error: malformed database schema SQLite version 3.2.6 Enter ".help" for instructions sqlite> pragma SQLITE_DEFAULT_PAGE_SIZE=16384; sqlite> .schema Error: malformed database schema Best regards, -- Guillaume FOUGNIES Eulerian Technologies 'I can't believe my eyes I must be dreaming, Wake up Jack, this isn't fair!' ~ Jack
Re: [sqlite] Thread safe in 3.2.5
21 => SQLITE_MISUSE You are using a db handle in multiple threads. You can read this: http://www.mail-archive.com/sqlite-users@sqlite.org/msg09910.html Best regards, Fri, Sep 16, 2005 at 05:34:37PM -0700: Rick Keiner wrote: > I recently updated my system with the 3.2.5 version from 3.0.8. I configure > with --enable-threadsafe because I use mutliple threads to access a single > database. It had been working fine (it's the apr_dbd driver for the > apr.apache.org project), but after the upgrade I started seeing "error 21". > The library call was out of sequence. I noticed there was a change in the > 3.2.4 version related to configuring the threadsafe operation that stated the > threadsafe option wasn't working. I don't understand why it was changed. I > haven't had any problems until now. Any help would be appreciated. > > Thanks, > Rick Keiner -- Guillaume FOUGNIES Eulerian Technologies
Re: [sqlite] Re: Thread safety guarantees
Wed, Sep 14, 2005 at 11:43:43PM +0200: Rolf Schaeuble wrote: > > > Dennis Jenkins wrote: > >Rolf Schaeuble wrote: > > > >>Hello, > >> > >>if I understand the whole issue correctly, there is only one reason > >>why it's not safe to use a sqlite handle in any thread of a process > >>(as long as only one thread at a time uses it): File locks are tied to > >>the thread that created them (except this special Redhat version). > >>That's why on Windows everything's safe, since file locks are > >>per-process there. > >> > >>If that's correct, couldn't this problem be solved by letting sqlite > >>use a dedicated thread to do the locking? Each time a lock needed to > >>be set/removed, a message would be sent to that thread which then > >>would perform the locking. That should make it safe to use the sqlite* > >>from any thread. > >> > >>For single threaded applications that would mean more thread > >>switching. For multithreaded applications, however, life wouldn't only > >>become easier, but performance may well increase. In my applications I > >>have a dedicated database access thread. Each time a query needs to be > >>executed, it's sent to this thread. In this case I have thread > >>switching anyway. Only having it when a lock is set/removed will > >>decrease the amount of switches. > >> > >>Best regards > >>Rolf Schäuble > >> > >> > >I disagree with the entire approach. It seems hackish. > > > >Just have each thread create it's own sqlite handle. So long as one > >thread does not need to "pass off" an exisiting handle to a different > >thread you will be fine. > > That doesn't work for me. During one single transaction several threads > need to insert data into the db; due to this transaction, all threads > have to share a single database handle. No. Each thread needs to open its own db handle and let SQLite make the lock job. That's all. Since you don't need a db handle opened by thread(a) to be used by another thread (b), you won't have any problem... If you need to, you will need my "lost patch" :) http://www.sqlite.org/cvstrac/tktview?tn=1417 -- Guillaume FOUGNIES
[sqlite] Re: (diff/patch) Problems with threadsafe opt correction #2623
Hello,
In attachement, there's a diff/patch against os_unix.c
in version 3.2.5.
Its allows threads to access successively to a DB handle
and remove the heavy restriction of the SQLITE_MISUSE.
In case of simultaneous access, threads get SQLITE_BUSY
until the OsFile is unlocked.
I test it a bit, it works well with my code and my bad
unix threaded file locks.
Can anybody test it too?
Best regards,
Mon, Sep 05, 2005 at 02:18:41PM -0400: D. Richard Hipp wrote:
> On Mon, 2005-09-05 at 19:58 +0200, René Tegel wrote:
>
> > As far as i can tell now the windows version of 3.2.5 seems not affected.
> >
>
> The restriction that a DB handle can only be used from a single
> thread is currently only enforced on Unix, because Unix boxes
> are the only place where using DB handles in multiple threads is
> a problem.
--
Guillaume FOUGNIES
Eulerian Technologies
--- ../../sqlite-3.2.5/src/os_unix.c2005-08-25 14:48:33.0 +0200
+++ os_unix.c 2005-09-07 20:17:38.0 +0200
@@ -82,7 +82,8 @@
*/
#ifdef SQLITE_UNIX_THREADS
# define SET_THREADID(X) X->tid = pthread_self()
-# define CHECK_THREADID(X) (!pthread_equal(X->tid, pthread_self()))
+# define CHECK_THREADID(X) ( threadsOverrideEachOthersLocks>0 && \
+ check_threadid(X) )
#else
# define SET_THREADID(X)
# define CHECK_THREADID(X) 0
@@ -275,6 +276,26 @@ struct threadTestData {
int result;/* Result of the locking operation */
};
+/*
+** Check if the thread is allowed to use the OsFile
+**
+** If another thread opened the OsFile and this OsFile
+** is now lock clear, the new thread is allowed to work
+** with. Its tid is set.
+** This routine is be protected by the global mutex.
+*/
+static int check_threadid(OsFile *id) {
+ if ( pthread_equal(id->tid, pthread_self()) ) {
+return 0;
+ }
+ if ( id->pLock->locktype == NO_LOCK ) {
+SET_THREADID(id);
+return 0;
+ }
+ return 1;
+}
+
+
#ifdef SQLITE_LOCK_TRACE
/*
** Print out information about all locking operations.
@@ -914,9 +935,14 @@ int sqlite3OsCheckReservedLock(OsFile *i
int r = 0;
assert( id->isOpen );
- if( CHECK_THREADID(id) ) return SQLITE_MISUSE;
sqlite3OsEnterMutex(); /* Needed because id->pLock is shared across threads
*/
+#ifdef SQLITE_UNIX_THREADS
+ if( CHECK_THREADID(id) ) {
+r = 1;
+ }
+#endif
+
/* Check if a thread in this process holds such a lock */
if( id->pLock->locktype>SHARED_LOCK ){
r = 1;
@@ -1032,7 +1058,6 @@ int sqlite3OsLock(OsFile *id, int lockty
TRACE7("LOCK%d %s was %s(%s,%d) pid=%d\n", id->h,
locktypeName(locktype),
locktypeName(id->locktype), locktypeName(pLock->locktype), pLock->cnt
,getpid() );
- if( CHECK_THREADID(id) ) return SQLITE_MISUSE;
/* If there is already a lock of this type or more restrictive on the
** OsFile, do nothing. Don't use the end_lock: exit path, as
@@ -1053,6 +1078,13 @@ int sqlite3OsLock(OsFile *id, int lockty
*/
sqlite3OsEnterMutex();
+#ifdef SQLITE_UNIX_THREADS
+ if( CHECK_THREADID(id) ){
+rc = SQLITE_BUSY;
+goto end_lock;
+ }
+#endif
+
/* If some thread using this PID has a lock via a different OsFile*
** handle that precludes the requested lock, return BUSY.
*/
@@ -1188,13 +1220,20 @@ int sqlite3OsUnlock(OsFile *id, int lock
assert( id->isOpen );
TRACE7("UNLOCK %d %d was %d(%d,%d) pid=%d\n", id->h, locktype,
id->locktype,
id->pLock->locktype, id->pLock->cnt, getpid());
- if( CHECK_THREADID(id) ) return SQLITE_MISUSE;
assert( locktype<=SHARED_LOCK );
if( id->locktype<=locktype ){
return SQLITE_OK;
}
sqlite3OsEnterMutex();
+
+#ifdef SQLITE_UNIX_THREADS
+ if( CHECK_THREADID(id) ){
+/* This should never happen */
+rc = SQLITE_IOERR;
+ }
+#endif
+
pLock = id->pLock;
assert( pLock->cnt!=0 );
if( id->locktype>SHARED_LOCK ){
@@ -1265,7 +1304,6 @@ int sqlite3OsUnlock(OsFile *id, int lock
*/
int sqlite3OsClose(OsFile *id){
if( !id->isOpen ) return SQLITE_OK;
- if( CHECK_THREADID(id) ) return SQLITE_MISUSE;
sqlite3OsUnlock(id, NO_LOCK);
if( id->dirfd>=0 ) close(id->dirfd);
id->dirfd = -1;
Re: [sqlite] Problems with threadsafe opt correction #2623
Mon, Sep 05, 2005 at 12:45:42PM -0400: D. Richard Hipp wrote: > On Mon, 2005-09-05 at 10:57 -0500, Ian Monroe wrote: > > I do not see how such a major change can be justified in a minor point > > release. For instance, currently amaroK does not work when using a > > sqlite database on Debian Sid since they package it with sqlite 3.2.5. > > > > Shouldn't this have waited for 4.0? > > > > There are no plans at this time to ever release version 4.0. > > SQLite has *never* supported the ability of a handle to be used > by more than one thread. By luck, such use would sometimes work on > some operating systems. But it would fail on others. Such a > situation is very dangerous since if a developer is working on > a system where the misuse of SQLite just happened to work, they > might not detect their design error and then ship non-working code > to a customer where it would fail. A minor change in check-in [2517] > detects the misuse of SQLite on all systems and prevents it from > working even by chance. This allows the problem to be detected > early and corrected before it reaches a customer. In this case, the ability of a handle to be used by more than one thread should be always denied to avoid portability problems. -- Guillaume FOUGNIES Eulerian Technologies
Re: [sqlite] Problems with threadsafe opt correction #2623
> This works on some systems but not on others. On some > versions of Linux, a thread is not able to override locks > created by a different thread in the same process. When > that happens, a database connection created on one thread > will not be usable by a different thread. This is not the point here because the file lock should be released to allow the previous OsFile->tid to be reset (this is my case). (i forgot this from the enhancement...) 1: - "sqlite3_release" function. It checks the lock state of files. If all is clear, set OsFile->tid to 0. return the state. 1-bis: - "sqlite3_release" function. It checks the lock state of files. If something is locked, it releases transactions/file locks and finally set OsFile->tid to 0. 2: - Atomically, a thread try to 'CHECK_THREADID' in sqlite3OsLock and if the OsFile->tid eq 0, just call SET_THREADID. Best regards, -- Guillaume FOUGNIES Eulerian Technologies
[sqlite] Problems with threadsafe opt correction #2623
Hello, After an upgrade from sqlite 3.2.2 to 3.2.5, i got problems with strange SQLITE_MISUSE returns. After a little investigation, i found it was the --enable-threadsafe option correction in Check-In 2623. It seems SQLite, in os_unix.c sqlite3OsLock with the SQLITE_UNIX_THREADSAFE flag (corrected), force a connection to be always used by the same thread. The SET_THREADID is only done during an 'open'. My code is sharing a pool of SQLite connections on multiple databases between a bunch of treatment threads. Each thread pops a connection from the pool safely and push it back once finished. So i get a SQLITE_MISUSE return from sqlite3OsLock when a second thread try to use safely an unused connection. Here is a possible way for the enhancement: - a "release" function called at the end of treatment to set the SQLite OsFile->tid to 0. - Atomically, a thread try to 'CHECK_THREADID' in sqlite3OsLock and if the OsFile->tid eq 0, just call SET_THREADID. Bye, -- Guillaume FOUGNIES Eulerian Technologies
Re: [sqlite] SQLITE_OMIT_*
http://www.sqlite.org/cvstrac/tktview?tn=1187 Sun, Apr 24, 2005 at 03:08:12PM -0600: Jack Lewis wrote: > Hello, > > How do you configure SqLite 3 with some of the SQLITE_OMIT_* > omits. > > For example, this results in a compilation error. > > CFLAGS="-DSQLITE_OMIT_ALTERTABLE" ./configure --enable-threadsafe > --disable-tcl > make clean > make # fails during make -- Guillaume FOUGNIES
Re: [sqlite] Library routine called out of sequence
You are probably working in a threaded environment and a sqlite* is shared by at least two threads. bye. Fri, Oct 22, 2004 at 10:44:22AM +0200: P. Morandi wrote: > Hi everybody. > Does anybody know the main causes of error 'Library routine called out of sequence' > after a sqlite_exec() calling? > > Thank you. > P. Morandi -- Guillaume FOUGNIES
Re: [sqlite] Busy handlers in SQLite3
Hi, This problem was evoked on the list. sqlite3_exec do not catch SQLITE_BUSY on the sqlite3_step. If you want to continue to use the sqlite3_exec properly with busy handler, you can copy and modify the method stored in src/legacy.c. I can send you my modified version if you need. It seems, to me, the only conversion problem between v2 and v3. Cheers, Thu, Oct 21, 2004 at 03:51:10PM +0200: Gerhard Haering wrote: > Hi, > > I'm converting SQLite2 code to SQLite3. It looks to me like the > sqlite3_busy_handler() and sqlite3_busy_timeout() don't do anything. > At least not anything useful. At least not when using the new API with > sqlite3_step() and friends. > > Is that true? Do I have to catch SQLITE_BUSY from the _step() etc. > calls myself and call an appropriate busy handler? > > All in all, I wonder why the old functions were left in the API if > they don't work properly. But maybe I've overseen something. > > Don't get me wrong: I like the new API a lot :-) > > -- Gerhard -- Guillaume FOUGNIES
[sqlite] multiple indexes.
Hello, I'd like to know if the management of multiple indexes on select statement is in the TODO list of SQLite? On joined request, this lack is a real performance killer. Example: select t1.id,t2.name from t1,t2 where t1.id IN(a00,b00, ,z99) and t1.t2id = t2.id; For now, SQLite use by default the wrong index and make a full join ignoring the 'IN' restriction. I need to force the index selection like this to gain a little speed: select t1.id,t2.name from t1,t2 where t1.id IN(a00,b00, ,z99) and +t1.t2id = t2.id; Once rows from t1 are selected (fast with the forced index), SQLite does not use the t2 index on 'id' to speed up the join. In my case, t2 is quite big. The only "solution" i see could be to split the request in two to use indexes on both tables... Can anyone advise me? Thanks. Bye, -- Guillaume FOUGNIES
[sqlite] concurrency in sqlite 3.0
Hello, SQLite 3.0 concurrency was greatly improved via multi lock states. Despite this, some stuff seems, to me, quite inappropriate. Busy handler is the "sure" way to avoid deadlocks; But on heavily multi threaded systems, you are forced to determine an acceptable latency time to avoid many context switches due to repeatitive try/sleep periods. Lots of cycles are lost here... and you don't know which waiting request will be served first. Of course, you can build a layer around SQLite to "over" lock transactions... to be relatively sure that no other thread will try to access the ressource in the same time... In this case, you bypass the multi locking state feature of 3.0 but you can gain a FIFO stack around each db access. SQLite knows when a db is "RESERVED" locked or unlocked. Adding a handler, like the busy one, which could inform when a (void*) is locked or unlocked could be great; Allowing to manage cond signals on the busy stack of many waiting (sqlite*). This could allow to manage fine tuned policies on request priority like some RT systems need. Is this sound crazy? :) Or is there another way to manage this without featuring SQLite? Thanks, bye. -- Guillaume FOUGNIES
Re: [sqlite] Risks involved with using synchronous=OFF (was 'Single INSERT is very slow')
Tue, Sep 07, 2004 at 12:59:37PM +0100: Christian Smith wrote: > On Tue, 7 Sep 2004, Guillaume Fougnies wrote: > > >Mon, Sep 06, 2004 at 11:56:21PM -0700: Darren Duncan wrote: > >> > >> What you probably saw with the 3ms is the time between when you > >> issued the insert command and when control was returned to your app, > >> but the new record was simply in RAM and not on disk. The operating > >> system would have written it to the disk some time later. So in > >> other words, the time is so much faster because the slower action > >> actually did something but the faster action did nothing during the > >> time. The main risk is that your app is thinking the data is saved > >> at a certain point in time, but it actually isn't. -- Darren Duncan > > > >Using synchronous=OFF is a design choice, not a risk. > >For non-mission critical databases, you can easily have a "good" > >data reliability after a crash using a journaling FS and incremental > >backups. > > So long as data is slow changing. I wouldn't like to trust my data to > software with that attitude. > > And what's a few milliseconds between friends if it results in more > reliable data. If changes are that frequent that performance is vital, > then batching updates makes sense. If updates are so infrequent as to make > batching unfeasible, then single insert performance should not be a > problem in the grand scheme of things. Difference between 5ms and 100ms is not a few milliseconds on heavily loaded systems. I'm currently using this kind of approach in a grid computing software; Data is replicated synchronously among 6 servers. If one fails, data is surely consistent on others. Batch update is not the right choice in a threaded or over multi proc environment if one process needs to read some data. This results of completely async storage... This is not the case if you use the synchronous=OFF. The kernel (like linux) manages like an expert the fs cache and sync... After all, that's its job. Proning synchronous off is stupid... in most cases, the default 'full' is smartest; it's always a question of design. -- Guillaume FOUGNIES
Re: [sqlite] Risks involved with using synchronous=OFF (was 'Single INSERT is very slow')
Mon, Sep 06, 2004 at 11:56:21PM -0700: Darren Duncan wrote: > At 9:20 AM +0300 9/7/04, George Ionescu wrote: > >as Nuno Lucas suggested, I've tried inserting one record using > >synchronous = OFF. That makes a *huge* difference: 150 ms > >(synchronous=FULL) vs. 3 ms (synchronous=OFF) !! So I'm asking this: > >anyone has had any real-word experience with multi-user access and > >setting synchronous = OFF on Windows NT systems ? I've read the docs > >and I know the risks, but I was wondering if any of you could share > >your real-word experience... If the database becomes corrupted after > >a power failure while inserting records, is there any way of > >repairing the database? > > What you probably saw with the 3ms is the time between when you > issued the insert command and when control was returned to your app, > but the new record was simply in RAM and not on disk. The operating > system would have written it to the disk some time later. So in > other words, the time is so much faster because the slower action > actually did something but the faster action did nothing during the > time. The main risk is that your app is thinking the data is saved > at a certain point in time, but it actually isn't. -- Darren Duncan Using synchronous=OFF is a design choice, not a risk. For non-mission critical databases, you can easily have a "good" data reliability after a crash using a journaling FS and incremental backups. If you want the speed and the fs sync, you probably need the hardware (less and less expensive). -- Guillaume FOUGNIES
Re: [sqlite] 3.x pragma synchronous
Yes, i'm fine with the synchronous=YES -> synchronous=FULL. It's the good choice. But in my case, i always need synchronous=OFF. I'm working on an high availability project using the model of Extended Virtual Synchrony. For the speed of the distributed system during update/insert, i need to reduce in all nodes the group transaction time. If a node failure happens, the recovery procedure will trash and/or resync databases from valid nodes. That's why i do not care of local disk sync. For now, it's running with 2.8 because i was unable to get the same speed with 3.0 and synchronous off... during my tests, consequences are lots of busy timeout despite the same busy handler. I'm sad because concurrency and btree+ storage in 3.0 seems nicely improved, a great work as far as i can see. Fri, Jul 23, 2004 at 02:18:14PM -0400: D. Richard Hipp wrote: > Guillaume Fougnies wrote: > > > > PRAGMA default_synchronous disappeared from 3.x. Should we now > execute a > > PRAGMA synchronous each time a db is opened? > > > > Yes. The thinking was that there is rarely ever a good > reason to make a persistent database less than fully synchronous. > So synchronous=FULL is now the default. If you want something > other than synchronous=FULL, you have to define it anew at the > beginning of each session. > > So tell me: Why do you not want synchronous=FULL? -- Guillaume FOUGNIES
[sqlite] 3.x pragma synchronous
Hello, PRAGMA default_synchronous disappeared from 3.x. Should we now execute a PRAGMA synchronous each time a db is opened? Could it be possible to get a configure flag or a define in sqliteInt.h to set this by default? Regards, -- Guillaume FOUGNIES
[sqlite] problem on table create and synchronous in 3.0.1
This bug is happening on a new empty db file with two sqlite_exec. It's a test request, it's nonsense. $ sqlite3 test.db SQLite version 3.0.1 Enter ".help" for instructions sqlite> BEGIN;CREATE TABLE TEST(id INT); PRAGMA default_synchronous=OFF; sqlite> COMMIT; sqlite3: src/pager.c:1548: syncJournal: Assertion `pPg->needSync==0' failed.Aborted -- Guillaume FOUGNIES - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] SQL Syntax from 2.8 to 3.0 not backward compatible?
Ok. Perhaps it should be written in the documentation of "SQLite Version 3" or in the chapter "Transaction Control At The SQL Level" of the "Locking And Concurrency In SQLite Version 3". Thanks. bye. Fri, Jun 25, 2004 at 04:22:10AM -0700: Daniel K wrote: > A BEGIN cannot have an ON CONFLICT clause in sqlite > version 3. > > Dan. > > > --- Guillaume Fougnies <[EMAIL PROTECTED]> wrote: > > Here is it: > > > > SQLite version 3.0.1 > > Enter ".help" for instructions > > sqlite> BEGIN TRANSACTION ON CONFLICT ROLLBACK; > > SQL error: near "ON": syntax error > > > > bye. -- Guillaume FOUGNIES - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] SQL Syntax from 2.8 to 3.0 not backward compatible?
Here is it: SQLite version 3.0.1 Enter ".help" for instructions sqlite> BEGIN TRANSACTION ON CONFLICT ROLLBACK; SQL error: near "ON": syntax error bye. -- Guillaume FOUGNIES - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Many bugs with threads (errors and corruption)
Fri, Jan 09, 2004 at 09:08:37AM -0500: D. Richard Hipp wrote: > Guillaume Fougnies wrote: > Probably the SQLITE_SCHEMA error is being incorrectly converted > into an SQLITE_ERROR somewhere as the call stack unwinds. I'll > fix it - but I consider this a minor problem. If you can give > me more hints on how to reproduce it, that would be helpful. You can try with your source: "src/threadtest.c" included in sqlite. It works on my machine. > This is very serious. If you are able to send me the code > that generates this problem and tell me how to reproduce it, > that would be very helpful. Let's focus on problem (2) for > the time being and go back to problem (1) after (2) has been > resolved. The code is quite big with lots of dependancies. I will do another version to focus on this problem and send it to you. Regards, -- Guillaume FOUGNIES pgp0.pgp Description: PGP signature
