Re: [sqlite] When not using threads: should I switch to single-thread mode
On Friday, 27 December, 2019 16:37, Simon Slavin wrote: >On 27 Dec 2019, at 9:57pm, Keith Medcalf wrote: >> Setting "SINGLETHREAD" does indeed disable the multithreaded sorters. >> When in one of the multithreaded modes, that query utilizes an average of >> 60% CPU, compared to 12% when running singlethreaded. >So if I understand this right, SQLite in multithread mode can itself use >multiple threads at once, which means it can use many cores at once, >which means it might be faster, most likely for complicated queries which >involve lots of different things to be done. >I'm slightly stunned. That had never occurred to me. Thank you. Only sort operations are candidates for internal multi-threading in SQLite at the moment and then only if all the following conditions are met: - the amount of data to be sorted exceeds page_size * min(cache_size, pmasz) bytes - the library is compiled with SQLITE_DEFAULT_WORKER_THREADS greater than 0 (the default is 0) OR pragma threads=X; is used to set the default number of threads to a value greater than 0 at runtime for a connection OR sqlite3_limit(db, SQLITE_LIMIT_WORKER_THREADS ...) C interface is used to set a >0 number of threads for a connection - SQLITE_MAX_WORKER_THREADS is greater than 0 (the default is 8) - the threading mode is not SQLITE_SINGLETHREAD -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When not using threads: should I switch to single-thread mode
Another point being, fully normalized data can be a bear to extract. I see penalities in my humble database reports, so I leave 3rd and 4th normalization for managers wish lists. ;) On Fri, Dec 27, 2019, 6:37 PM Simon Slavin wrote: > On 27 Dec 2019, at 9:57pm, Keith Medcalf wrote: > > > Setting "SINGLETHREAD" does indeed disable the multithreaded sorters. > When in one of the multithreaded modes, that query utilizes an average of > 60% CPU, compared to 12% when running singlethreaded. > > So if I understand this right, SQLite in multithread mode can itself use > multiple threads at once, which means it can use many cores at once, which > means it might be faster, most likely for complicated queries which involve > lots of different things to be done. > > I'm slightly stunned. That had never occurred to me. Thank you. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Assertion Failed In sqlite3
On 12/27/19, Richard Hipp wrote: > On 12/27/19, Richard Hipp wrote: >> >> This is the third such false-positive bug like that this month > > And now there is a fourth: https://sqlite.org/src/info/5fbc159eeb092130c6f2 For those of you still keeping score... A fifth example of this is ticket https://www.sqlite.org/src/info/de4b04149b9fdeae The problem there was another false-positive in the OP_SCopy misuse detection mechanism. The fix was to improve that mechanism to avoid the false positive. But since the OP_SCopy misuse detection is a debugging feature that is only present in the code when it is compiled with SQLITE_DEBUG, the "fix" makes no changes to deliverable code. That is to say, the compiled machine code for the SQLite library is byte-for-byte identical before and after the fix. Even so, the OP_SCopy misuse detection logic is an important testing component of SQLite, and we sincerely appreciate bug reports against that feature. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When not using threads: should I switch to single-thread mode
On 27 Dec 2019, at 9:57pm, Keith Medcalf wrote: > Setting "SINGLETHREAD" does indeed disable the multithreaded sorters. When > in one of the multithreaded modes, that query utilizes an average of 60% CPU, > compared to 12% when running singlethreaded. So if I understand this right, SQLite in multithread mode can itself use multiple threads at once, which means it can use many cores at once, which means it might be faster, most likely for complicated queries which involve lots of different things to be done. I'm slightly stunned. That had never occurred to me. Thank you. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When not using threads: should I switch to single-thread mode
On Friday, 27 December, 2019 14:19, Simon Slavin wrote: >On 27 Dec 2019, at 7:46pm, Keith Medcalf wrote: >> Setting "SINGLE THREADED" mode *increased* the elapsed time to 4 >minutes. (Perhaps it disables some of the internal multithreaded sorters >-- I don't know). >Can anyone explain this ? The increase was in the summarization query which basically does a group by/order by of the ~11 million row run results into an ~25,000 row summary. This is a significant amount of data to group and sort, and since the database model is fully relational (that is it is normalized to 4th normal) that is a LOT of data to sort since indexes are non-helpful. Setting "SINGLETHREAD" does indeed disable the multithreaded sorters. When in one of the multithreaded modes, that query utilizes an average of 60% CPU, compared to 12% when running singlethreaded. (Looking at that query again I found an error in the group by/order by that was causing SQLite to have to sort twice -- once for the group by then a partial sort for a non-matching order by -- making them the same chopped the time in half again). -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Assertion Failed In sqlite3
On 12/27/19, Richard Hipp wrote: > > This is the third such false-positive bug like that this month And now there is a fourth: https://sqlite.org/src/info/5fbc159eeb092130c6f2 CREATE TABLE t0(c0 NOT NULL DEFAULT 1, c1 AS(c0) UNIQUE); REPLACE INTO t0 VALUES(NULL); If you understood my prior email, then you should clearly see what is going on here. The t0.c0 starts out as NULL. That value is SCopy-ied into t1.c1. Then the REPLACE causes t0.c0 to be overwritten with a numeric 1, which invalidates t1.c1, causing a memIsValid() assert further along in the processing. But the overwrite of t0.c0 didn't really disturb the value in t1.c1 since NULLs are copied by value, not by reference. But notice how this reveals a deeper more subtle problem in the new (unreleased) generated columns feature. The values of generated columns are computed *before* the NOT NULL constraints run during the INSERT. But the NOT NULL constraints might cause (normal) columns to change values due to the REPLACE. If those normal columns where previously used by generated columns, it might cause incorrect generated column values to be stored (for a STORED generated column) or incorrect entries to be written in indexes based on VIRTUAL generated columns (as in the case above). This is a design problem in generated columns. It has never come up before with another database because (as far as I know) SQLite is the only database that supports both generated columns and the NOT NULL ON CONFLICT REPLACE behavior. I suppose the correct solution here is to recompute the values of all generated columns *after* all NOT NULL ON CONFLICT REPLACE constraints have been run, if those constrains caused any changes. The point of this email: The bug report complains about the memIsValid() assertion fault. That assertion fault is not really a problem. It is more like a compiler warning. But in this case, the compiler warning lead me to discover a different, subtle, and unrelated problem in the design. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Causal profiling
> On Dec 26, 2019, at 3:45 PM, Doug wrote: > > Can you explain what the API is that you use to "set concurrency levels per > connection", please? Is it a parameter on open() or its own function call? > And how would the API break, exactly? sqlite3_config(), specifically the SQLITE_CONFIG_SINGLETHREAD, SQLITE_CONFIG_MULTITHREAD, SQLITE_CONFIG_SERIALIZED options. This API would break because configuring those options at runtime would have no effect on behavior; the only thing that would change threading behavior would be the compile-time flags SQLITE_MUTEX_OMIT, etc. (This is actually global, not per-connection, but that doesn't invalidate what I said.) > The talk suggested removing the SQLite virtual table of functions > (specifically the call to free a mutex). The user calls the function > directly. How does that break an API? If SQLite's implementation directly called the mutex lock/unlock functions, instead of indirecting, then there would be no way to control whether or not mutexes were used. In other words, it would be impossible to change any of the above options at runtime. > The talk suggested removing the SQLite virtual table of functions > (specifically the call to free a mutex). The user calls the function > directly. How does that break an API? No, the user does not call those functions directly. The code shown in the video is deep inside SQLite itself and not visible through the API. (You say you're using a TCL wrapper … so you may not be aware of what is or isn't in the C API. Trust me, I use the C API a lot.) —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When not using threads: should I switch to single-thread mode
On 27 Dec 2019, at 7:46pm, Keith Medcalf wrote: > Setting "SINGLE THREADED" mode *increased* the elapsed time to 4 minutes. > (Perhaps it disables some of the internal multithreaded sorters -- I don't > know). Can anyone explain this ? (To save you reading all the stuff I snipped, it's one extremely complicated query which takes 2 minutes in multi-threaded mode.) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Heap Use After Free In sqlite.
On 28/12/62 01:58, Yongheng Chen wrote: Hi, We found a heap UAF bug in sqlite. Here’s the PoC: — CREATE TABLE v0 ( v1 CHECK( CASE v1 WHEN '13' THEN 10 ELSE 10 END ) ) ; CREATE TRIGGER x INSERT ON v0 BEGIN INSERT INTO v0 ( v1 , v1 ) SELECT v1 , v1 FROM v0 WHERE v1 < 10 ON CONFLICT DO NOTHING ; END ; INSERT INTO v0 SELECT * FROM v0 WHERE v1 OR 0 ; CREATE VIEW v2 ( v3 ) AS WITH x1 AS ( SELECT * FROM v2 ) SELECT v3 AS x , v3 AS y FROM v2 ; ALTER TABLE zipfile RENAME TO t3 ; — This bug exists in both release code and development code. It triggers uaf with asan with release code while triggering an assert in the delevelopment code. Thanks for this. Looks like the asan error was fixed here: https://www.sqlite.org/src/info/de6e6d6846d6a41c The assert() failure is now fixed here: https://www.sqlite.org/src/info/d29edef93451cc67 Dan. Yongheng & Rui ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Assertion Failed In sqlite3
On 12/27/19, Manuel Rigger wrote: > Hi Yongheng and Rui, > > This might be a duplicate to a bug that I reported, since both test cases > trigger the same assertion error. See > https://sqlite.org/src/tktview?name=37823501c6. That particular assert() is sort of like an ASAN fault except that it is for the SQLite byte-code rather than for machine code. The assert means that one of the registers in the virtual machine is being used when it is uninitialized or has been invalidated. This is usually harmless, but it is important to find and fix those problems nevertheless. Sometimes these warnings are false-positives. Let me explain: The byte-code engine has an instruction OP_SCopy that makes a "shallow copy" of the value in one register into some other register. The copy is "shallow" because if it is a string or a blob value, it only copies a pointer to the string or blob, not the string or blob itself. This is an important performance optimization when dealing with large strings and blobs. But using OP_SCopy carries risk. If you OP_SCopy register 1 into register 2, then you change the value of register 1, then the value in register 2 is invalid. When you compile with SQLITE_DEBUG, SQLite adds extra logic that looks for values that have been invalided after an SCopy. If you SCopy register 1 into register 2, then change the value of register 1, then try to access the value of register 2 in any way, you get the memIsValid() assertion. The value of register 2 only truly goes invalid if the SCopy-ed value was a string or a blob. But the memIsValid() mechanism marks the register as invalid regardless of what kind of value was copied, under the theory that the same SCopy opcode might copy a string or a blob on a different iteration, depending on the database content. The mrigger bug I'm working on right now (https://www.sqlite.org/src/info/37823501c68a09f9) is an example of false-positive in this SCopy error detection mechanism. In that ticket, the value of virtual column C1 is SCopy-ed from C0. If that value is a NULL, then the REPLACE conflict resolution logic causes the original value for C0 to be replaced by an empty string. But since C1 was SCopy-ed from C0, that invalidates the value inside of C1, which causes problems later. But this is a false positive, because C0 will only be overwritten in a NOT NULL ON CONFLICT REPLACE constraint if its original value was NULL. So the SCopy did not copy a string or a blob and so there really is no reason to invalidate the C1 register. So, the https://www.sqlite.org/src/info/37823501c68a09f9 ticket is really a false-positive in the SCopy misuse validation logic. It is still an important ticket and needs to be fixed. But the problem would never appear in practice. It is a bug in logic added by SQLITE_DEBUG and which does not appear in release builds. This is the third such false-positive bug like that this month. The previous two were: https://www.sqlite.org/src/info/5ad2aa6921faa1ee https://www.sqlite.org/src/info/c62c5e58524b204d None of these tickets would have resulted in actual problems in deployment. But it is important to fix them, just as it is important to get your C-code to compile without warnings even though the warnings in many cases are spurious. Warnings are often false-positive, but the warning mechanism does sometimes find real bugs, so it is helpful to keep it in place. And, of course, I could bypass all of this heartache by using OP_Copy (which does a deep copy of the entire value) everywhere instead of sometimes using OP_SCopy. Doing so would not make a noticeable difference for most applications, but it might cause some applications that deal with lots of large strings and blobs to run slower. In other words, these is really all about an optimization. That last sentence is true about most things we do. Almost without exception, bugs found in SQLite arise from our attempts to cut corners and make it run faster. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug Report
Hi,
We tried debugging a little bit with the core dump, it crashes with a null
reference actually:
`
Program received signal SIGSEGV, Segmentation fault.
[--registers---]
RAX: 0x74 ('t')
RBX: 0x782550 --> 0x76b088 --> 0x1
RCX: 0x61 ('a')
RDX: 0x0
RSI: 0x0
RDI: 0x782098 --> 0x31656c626174 ('table1')
RBP: 0x782548 --> 0x10001
RSP: 0x7fffb6b0 --> 0x78d1b0 --> 0x78d1e8 --> 0x50804496
RIP: 0x4b4237 (:movzx ecx,BYTE PTR [rdx+rsi*1])
R8 : 0x77d0e8 --> 0x1
R9 : 0x0
R10: 0x77d0f8 --> 0x0
R11: 0x0
R12: 0x1
R13: 0x7fffc680 --> 0x76a9b8 --> 0x73c300 --> 0x780003
R14: 0x7fffc680 --> 0x76a9b8 --> 0x73c300 --> 0x780003
R15: 0x0
EFLAGS: 0x10246 (carry PARITY adjust ZERO sign trap INTERRUPT direction
overflow)
[-code-]
0x4b422d : jne0x4b4270
0x4b422f : addrsi,0x1
0x4b4233 : movzx eax,BYTE PTR [rdi+rsi*1]
=> 0x4b4237 : movzx ecx,BYTE PTR [rdx+rsi*1]
`
We got the same result if we debug with address sanitizer, not an out of memory
error.
Thanks,
Ming Jia
> On Dec 27, 2019, at 2:56 PM, Keith Medcalf wrote:
>
>
> On Friday, 27 December, 2019 12:50, Igor Korot wrote:
>
>> On Fri, Dec 27, 2019 at 12:57 PM Bigthing Do wrote:
>
>>> We met an accidental crash in sqlite with the following sample:
>
>>> CREATE VIEW table1 ( col1 , col2 ) AS WITH aaa AS ( SELECT * FROM table1 )
>>> SELECT col2 FROM table1 ORDER BY 1 ;
>>> WITH aaa AS ( SELECT * FROM table1 ) SELECT col1 , rank () OVER( ORDER BY
>>> col1 DESC ) FROM table1 ;
>
>> Could you please provide the schema for table1?
>
> table1 is a circular view ... that is table1 is a view that tries to select
> from table1 which is a view which selects from table1 which is a view which
> selects from table1 ... until eventually all memory and stack is consumed and
> sqlite crashes.
>
> --
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a
> lot about anticipated traffic volume.
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Assertion Bug in sqlite
Hi, There’s one bug that triggers assertion failed in sqlite: — CREATE TABLE v0 ( v1 ) ; CREATE TABLE v2 ( v3 VARCHAR(1) UNIQUE ) ; SELECT * FROM v0 WHERE v1 IN ( 'AIR' ) GROUP BY v1 , v1 ; CREATE INDEX v4 ON v0 ( v1 , v1 , v1 , v1 , v1 , v1 , v1 , v1 , v1 , v1 , v1 , v1 , v1 , v1 , v1 ) WHERE v1 IS NOT NULL ; UPDATE v0 SET v1 = v1 + 10 WHERE ( v1 = '1995-03-15' OR v1 = '**%s**' ) AND v1 IS NOT NULL OR ( v1 = 10 ) ; SELECT DISTINCT 10 FROM v4 AS NO_CACHE NATURAL JOIN v2 NATURAL JOIN v4 WHERE v1 = v4 . v1 OR v1 = v4 . v1 ; — The bug exists in both development code and release code. Yongheng & Rui ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Assertion Bugs in Sqlite
Hi, We found some assertion bugs in sqlite: #1 — CREATE TABLE v0 ( v1 INTEGER PRIMARY KEY UNIQUE ) ; INSERT INTO v0 VALUES ( 10 ) ON CONFLICT DO NOTHING ; SELECT * FROM v0 NATURAL JOIN v0 AS y WHERE v1 IN ( SELECT DISTINCT v1 FROM v0 ORDER BY v1 ); sqlite3.c:100324: Select *isCandidateForInOpt(Expr *): Assertion `p->pGroupBy==0' failed. — This bug exists in the development code and release code. #2 — CREATE TABLE v0 ( v1 TEXT , v2 ) ; CREATE TABLE v3 ( v4 , v5 ) ; CREATE TABLE v6 ( v7 , v8 ) ; INSERT INTO v0 VALUES ( 10 , 2 ) ; INSERT INTO v6 VALUES ( 10 , 1 ) ; CREATE VIRTUAL TABLE v9 USING rtree ( v12 , v11 , v10 INTEGER UNIQUE ON CONFLICT IGNORE AS( v8 ) CHECK( v2 ) ) ; SELECT v4 , max ( v4 + v5 ) FROM v6 , v3 AS t ; CREATE TABLE v13 ( v15 INTEGER PRIMARY KEY , v14 INT ) ; INSERT INTO v0 ( v1 ) VALUES ( 2 ) ,( 10 ) ; SELECT * FROM v6 LEFT JOIN v9 ON v12 = 0 OR v11 = 10 WHERE v10 = v11 AND v11 = 10 ; (sqlite3.c:141119: Bitmask sqlite3WhereCodeOneLoopStart(Parse *, Vdbe *, WhereInfo *, int, WhereLevel *, Bitmask): Assertion `(pTerm->prereqRight & pLevel->notReady)!=0' failed.) — This bug exists in the development code. #3 — CREATE TABLE v0 ( v1 CHAR(2) UNIQUE PRIMARY KEY ON CONFLICT ROLLBACK ) ; CREATE TABLE v2 ( v3 INT ) ; CREATE INDEX v4 ON v0 ( v1 , v1 , v1 , v1 , v1 , v1 , v1 , v1 , v1 , v1 , v1 , v1 , v1 , v1 , v1 , v1 ) ; ANALYZE ; SELECT * FROM v2 LEFT JOIN v0 ON v1 = 0 WHERE ( v1 = 'MED P' OR v1 = 'DELIVER IN PERSON' ) AND v1 IS NOT NULL ; (Bitmask sqlite3WhereCodeOneLoopStart(Parse *, Vdbe *, WhereInfo *, int, WhereLevel *, Bitmask): Assertion `(pTabItem[0].fg.jointype & JT_LEFT)==0 || ExprHasProperty(pOrExpr, EP_FromJoin)’) — This bug exists in the development code. Yongheng & Rui ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug Report
On Friday, 27 December, 2019 12:50, Igor Korot wrote: >On Fri, Dec 27, 2019 at 12:57 PM Bigthing Do wrote: >> We met an accidental crash in sqlite with the following sample: >> CREATE VIEW table1 ( col1 , col2 ) AS WITH aaa AS ( SELECT * FROM table1 ) >> SELECT col2 FROM table1 ORDER BY 1 ; >> WITH aaa AS ( SELECT * FROM table1 ) SELECT col1 , rank () OVER( ORDER BY >> col1 DESC ) FROM table1 ; >Could you please provide the schema for table1? table1 is a circular view ... that is table1 is a view that tries to select from table1 which is a view which selects from table1 which is a view which selects from table1 ... until eventually all memory and stack is consumed and sqlite crashes. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug Report
Hi, On Fri, Dec 27, 2019 at 12:57 PM Bigthing Do wrote: > > Dear sqlite developers: > > We met an accidental crash in sqlite with the following sample: > > CREATE VIEW table1 ( col1 , col2 ) AS WITH aaa AS ( SELECT * FROM table1 ) > SELECT col2 FROM table1 ORDER BY 1 ; > WITH aaa AS ( SELECT * FROM table1 ) SELECT col1 , rank () OVER( ORDER BY > col1 DESC ) FROM table1 ; Could you please provide the schema for table1? Thank you. > > > We are using release version of sqlite: `SQLite version 3.30.1 2019-10-10 > 20:19:45` > > Thanks > > Ming Jia > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When not using threads: should I switch to single-thread mode
On Friday, 27 December, 2019 10:29, Cecil Westerhof wrote: >Op vr 27 dec. 2019 om 17:01 schreef Simon Slavin : >> On 27 Dec 2019, at 3:06pm, Cecil Westerhof wrote: >>> My applications only use one thread (for the db stuff). Would it be a >>> good idea to switch to single-thread mode, or does that not give a real >>> performance improvement? >>> On a desktop computer, or a mobile phone, the increase in speed is not >>> large. Maybe a few percent. If your application is already fast >>> enough. >> to please your users, I would not do the switching. >OK, thanks. I will not bother about that then. ;-) There are, of course, other considerations. For example, I wrote a program which runs initial connection packets against some firewall rules. The current database size is ~11 million initial packets and ~2000 rules. Basically, running the entire thing is one (admittedly large and complex) SQL statement. The initial total runtime was about 25 minutes (includes a couple of other sumarization steps -- about 22 minutes spent just running the ruleset). After redesigning the query the time to run it dropped to 23 seconds for a total elapsed time of 2 minutes. Setting "SINGLE THREADED" mode *increased* the elapsed time to 4 minutes. (Perhaps it disables some of the internal multithreaded sorters -- I don't know). Setting "MUTITHREAD" shaved an additional 50 nanoseconds off the runtime. The moral of the story is that it is (in this case) not worth changing the default mode of SERIALIZED and that it may not have the result you intend. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Assertion Failed In sqlite3
Hi Manuel, Hh, what a coincident. It might be so. The test case looks very different though. Let’s wait for Richard to find it out then. Yongheng & Rui > On Dec 27, 2019, at 2:03 PM, Manuel Rigger wrote: > > Hi Yongheng and Rui, > > This might be a duplicate to a bug that I reported, since both test cases > trigger the same assertion error. See > https://sqlite.org/src/tktview?name=37823501c6. > > Best, > Manuel > > On Fri, Dec 27, 2019 at 6:09 PM Yongheng Chen wrote: > >> Hi, >> >> We found an assertion violation bug in sqlite. Here’s the PoC: >> — >> CREATE TABLE v0 ( v1 INTEGER PRIMARY KEY ) ; INSERT INTO v0 VALUES ( 10 ) >> ; >> SELECT '29' , count () OVER( ORDER BY v1 ) AS m FROM v0 ORDER BY v1 > ( >> SELECT m ) ; >> — >> >> The bug exists in the latest development code and release code. >> >> Yongheng & Rui >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Assertion Failed In sqlite3
Hi Yongheng and Rui, This might be a duplicate to a bug that I reported, since both test cases trigger the same assertion error. See https://sqlite.org/src/tktview?name=37823501c6. Best, Manuel On Fri, Dec 27, 2019 at 6:09 PM Yongheng Chen wrote: > Hi, > > We found an assertion violation bug in sqlite. Here’s the PoC: > — > CREATE TABLE v0 ( v1 INTEGER PRIMARY KEY ) ; INSERT INTO v0 VALUES ( 10 ) > ; > SELECT '29' , count () OVER( ORDER BY v1 ) AS m FROM v0 ORDER BY v1 > ( > SELECT m ) ; > — > > The bug exists in the latest development code and release code. > > Yongheng & Rui > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Heap Use After Free In sqlite.
Hi, We found a heap UAF bug in sqlite. Here’s the PoC: — CREATE TABLE v0 ( v1 CHECK( CASE v1 WHEN '13' THEN 10 ELSE 10 END ) ) ; CREATE TRIGGER x INSERT ON v0 BEGIN INSERT INTO v0 ( v1 , v1 ) SELECT v1 , v1 FROM v0 WHERE v1 < 10 ON CONFLICT DO NOTHING ; END ; INSERT INTO v0 SELECT * FROM v0 WHERE v1 OR 0 ; CREATE VIEW v2 ( v3 ) AS WITH x1 AS ( SELECT * FROM v2 ) SELECT v3 AS x , v3 AS y FROM v2 ; ALTER TABLE zipfile RENAME TO t3 ; — This bug exists in both release code and development code. It triggers uaf with asan with release code while triggering an assert in the delevelopment code. Yongheng & Rui ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug Report
Dear sqlite developers: We met an accidental crash in sqlite with the following sample: CREATE VIEW table1 ( col1 , col2 ) AS WITH aaa AS ( SELECT * FROM table1 ) SELECT col2 FROM table1 ORDER BY 1 ; WITH aaa AS ( SELECT * FROM table1 ) SELECT col1 , rank () OVER( ORDER BY col1 DESC ) FROM table1 ; We are using release version of sqlite: `SQLite version 3.30.1 2019-10-10 20:19:45` Thanks Ming Jia ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When not using threads: should I switch to single-thread mode
Op vr 27 dec. 2019 om 17:01 schreef Simon Slavin : > On 27 Dec 2019, at 3:06pm, Cecil Westerhof wrote: > > > My applications only use one thread (for the db stuff). Would it be a > good idea to switch to single-thread mode, or does that not give a real > performance improvement? > > On a desktop computer, or a mobile phone, the increase in speed is not > large. Maybe a few percent. If your application is already fast enough. > to please your users, I would not do the switching. > OK, thanks. I will not bother about that then. ;-) -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Assertion Failed In sqlite3
Hi, We found an assertion violation bug in sqlite. Here’s the PoC: — CREATE TABLE v0 ( v1 INTEGER PRIMARY KEY ) ; INSERT INTO v0 VALUES ( 10 ) ; SELECT '29' , count () OVER( ORDER BY v1 ) AS m FROM v0 ORDER BY v1 > ( SELECT m ) ; — The bug exists in the latest development code and release code. Yongheng & Rui ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] When not using threads: should I switch to single-thread mode
On 27 Dec 2019, at 3:06pm, Cecil Westerhof wrote: > My applications only use one thread (for the db stuff). Would it be a good > idea to switch to single-thread mode, or does that not give a real > performance improvement? On a desktop computer, or a mobile phone, the increase in speed is not large. Maybe a few percent. If your application is already fast enough. to please your users, I would not do the switching. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] When not using threads: should I switch to single-thread mode
My applications only use one thread (for the db stuff). Would it be a good idea to switch to single-thread mode, or does that not give a real performance improvement? If the performance is not really improved, I can better keep the default. Then there is no risk that I forget the change the mode when I switch to multiple threads. Maybe a good idea to add something about that at: https://www.sqlite.org/threadsafe.html -- Cecil Westerhof ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] oserror-2.1.x fails when run on an BTRFS volume - directories may have any st_size.
On Donnerstag, 26. Dezember 2019 02:01:36 CET Richard Hipp wrote: > Tnx for the report. Should be fixed as of > https://sqlite.org/src/info/c8c6dd0e6582ec91 > > Please do us the favor of trying this out on both Btrfs and XFS and > making sure it works correctly on both filesystems. Tnx. Thanks for the quick fix. I did a quick test on BTRFS and it works, XFS is pending. The fix requires a small amendment though, there are other types than directories which have an st_size of 0, sockets, pipes, ... The size check probably should only be done for regular files - symlinks can never have a size of 0, and all others are implementation defined. Kind regards, Stefan -- Stefan Brüns / Bergstraße 21 / 52062 Aachen home: +49 241 53809034 mobile: +49 151 50412019 signature.asc Description: This is a digitally signed message part. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Only enter higher values in table
Op vr 27 dec. 2019 om 13:12 schreef Hick Gunter :
> You need an UPDATE trigger for this, since the comparison requires
> knowledge of the old and new values.
>
Of-course. I should have thought of that. :'-(
I will look into that this weekend.
In my case it is not important (I do not expect to insert records from the
past), but I also add that it will not be higher as record from a later
date.
-Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Cecil Westerhof
> Gesendet: Freitag, 27. Dezember 2019 13:05
> An: SQLite mailing list
> Betreff: [EXTERNAL] [sqlite] Only enter higher values in table
>
> Just to have a way to see my progress at Udemy, I created the following
> table and view:
> CREATE TABLE rawSummaryUdemy (
> dateTEXTNOT NULL DEFAULT CURRENT_DATE,
> total INTEGER NOT NULL,
> completed INTEGER NOT NULL,
>
> CONSTRAINT formatDate CHECK(date = date(strftime('%s',
> date), 'unixepoch')),
> CONSTRAINT notInFuture CHECK(date <= date()),
> CONSTRAINT totalIsInt CHECK(TYPEOF(total) = 'integer'),
> CONSTRAINT completedIsInt CHECK(TYPEOF(completed) = 'integer'),
> CONSTRAINT totalGEZero CHECK(total >= 0),
> CONSTRAINT completedGEZero CHECK(completed >= 0),
> CONSTRAINT completedLETotal CHECK(completed <= total),
>
> PRIMARY KEY(date)
> );
> CREATE VIEW summaryUdemy AS
> SELECT *
> , total - completed AS toComplete
> FROM rawSummaryUdemy
> ;
>
> If this can be done better: let me know.
>
> Normally speaking total and completed should never decrease. It is not
> really important, but just as an exercise: is it possible to add
> constraints so that you cannot enter a total, or a completed that is lower
> as the previous one?
>
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Only enter higher values in table
You need an UPDATE trigger for this, since the comparison requires knowledge of
the old and new values.
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Cecil Westerhof
Gesendet: Freitag, 27. Dezember 2019 13:05
An: SQLite mailing list
Betreff: [EXTERNAL] [sqlite] Only enter higher values in table
Just to have a way to see my progress at Udemy, I created the following table
and view:
CREATE TABLE rawSummaryUdemy (
dateTEXTNOT NULL DEFAULT CURRENT_DATE,
total INTEGER NOT NULL,
completed INTEGER NOT NULL,
CONSTRAINT formatDate CHECK(date = date(strftime('%s',
date), 'unixepoch')),
CONSTRAINT notInFuture CHECK(date <= date()),
CONSTRAINT totalIsInt CHECK(TYPEOF(total) = 'integer'),
CONSTRAINT completedIsInt CHECK(TYPEOF(completed) = 'integer'),
CONSTRAINT totalGEZero CHECK(total >= 0),
CONSTRAINT completedGEZero CHECK(completed >= 0),
CONSTRAINT completedLETotal CHECK(completed <= total),
PRIMARY KEY(date)
);
CREATE VIEW summaryUdemy AS
SELECT *
, total - completed AS toComplete
FROM rawSummaryUdemy
;
If this can be done better: let me know.
Normally speaking total and completed should never decrease. It is not really
important, but just as an exercise: is it possible to add constraints so that
you cannot enter a total, or a completed that is lower as the previous one?
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
Gunter Hick | Software Engineer | Scientific Games International GmbH |
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43
1 80100 - 0
May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Only enter higher values in table
Just to have a way to see my progress at Udemy, I created the following
table and view:
CREATE TABLE rawSummaryUdemy (
dateTEXTNOT NULL DEFAULT CURRENT_DATE,
total INTEGER NOT NULL,
completed INTEGER NOT NULL,
CONSTRAINT formatDate CHECK(date = date(strftime('%s',
date), 'unixepoch')),
CONSTRAINT notInFuture CHECK(date <= date()),
CONSTRAINT totalIsInt CHECK(TYPEOF(total) = 'integer'),
CONSTRAINT completedIsInt CHECK(TYPEOF(completed) = 'integer'),
CONSTRAINT totalGEZero CHECK(total >= 0),
CONSTRAINT completedGEZero CHECK(completed >= 0),
CONSTRAINT completedLETotal CHECK(completed <= total),
PRIMARY KEY(date)
);
CREATE VIEW summaryUdemy AS
SELECT *
, total - completed AS toComplete
FROM rawSummaryUdemy
;
If this can be done better: let me know.
Normally speaking total and completed should never decrease. It is not
really important, but just as an exercise: is it possible to add
constraints so that you cannot enter a total, or a completed that is lower
as the previous one?
--
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
