Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

On Fri, 13 Mar 2020 at 08:15, Jens Alfke wrote: > In a messaging system, the user interface is critically important. Agree absolutely! I don't think it matters much whether the SQLite forum can render a page in > "about 0.003s" as it says in the footer. But I take issue with this -- I find

Re: [sqlite] Sqlite error code 14 when using 3.31.0+

On Mon, 9 Mar 2020 at 23:22, Daniel Polski wrote: > Updated to 3.31.1 but my application started spitting out an error when > opening the database, so I tested some earlier sqlite versions to figure > out when the problem starts. > I don't get the message in versions <= 3.30.1. > > (from the

Re: [sqlite] How should I use the sqlite3 tool?(for linux)

This means you're missing a dependency - in this case zlib. It's hard to believe you don't have zlib on your system at all; probably this is happening because your system is amd64 but the sqlite binary you've downloaded is x86. I'm not a debian user but this should get you going: apt-get install

Re: [sqlite] Is randomblob(16) a good guid generation across multiple computers?

On Fri, 21 Feb 2020 at 03:59, Jens Alfke wrote: > > On Feb 20, 2020, at 10:48 AM, Richard Hipp wrote: > > > > That assumption is not correct for SQLite, which does you a > > cryptographically strong PRNG. And the SQLite PRNG is seeded from > > /dev/random on unix. > > Not quite; I'm looking at

Re: [sqlite] notify all processes of database modification

On Mon, 10 Feb 2020 at 12:53, Simon Slavin wrote: > On 10 Feb 2020, at 4:26am, Rowan Worth wrote: > > > See also PRAGMA data_version when it comes to polling the DB, the return > value of which changes when another process modifies the DB. IIRC the > implementation of this

Re: [sqlite] How to group this?

On Mon, 10 Feb 2020 at 11:12, Richard Damon wrote: > On 2/9/20 7:24 PM, Bart Smissaert wrote: > > ID ENTRY_DATE TERM NUMERIC_VALUE ROWID > > > > 1308 15/Mar/2013 Systolic 127 701559 > > 1308 15/Mar/2013 Diastolic 81 701568 > > 1308

Re: [sqlite] notify all processes of database modification

On Sat, 8 Feb 2020 at 04:02, Jens Alfke wrote: > > On Feb 7, 2020, at 6:23 AM, Kees Nuyt wrote: > > > > Anyway, SQLite doesn't have such a mechanism by itself. > > Maybe inotify is useful to you : > > > > https://en.wikipedia.org/wiki/Inotify < > https://en.wikipedia.org/wiki/Inotify> > >

Re: [sqlite] Performance problem with DELETE FROM/correlated subqueries

On Fri, 7 Feb 2020 at 16:25, Clemens Ladisch wrote: > Jürgen Baier wrote: > > CREATE TABLE main ( ATT1 INT, ATT2 INT, PRIMARY KEY (ATT1,ATT2) ); > > CREATE TABLE staging ( ATT1 INT, ATT2 INT ); > > > > Then I execute > > > > DELETE FROM main WHERE EXISTS (SELECT 1 FROM staging WHERE

Re: [sqlite] New word to replace "serverless"

On Tue, 28 Jan 2020 at 06:19, Richard Hipp wrote: > Note that "in-process" and "embedded" are not adequate substitutes for > "serverless". An RDBMS might be in-process or embedded but still be > running a server in a separate thread. In fact, that is how most > embedded RDBMSes other than

Re: [sqlite] Causal profiling

On Fri, 27 Dec 2019 at 06:11, Jens Alfke wrote: > > > On Dec 25, 2019, at 2:53 PM, Doug wrote: > > > > I wrote an application in Qt which uses SQLite. Therefore, I invoke > SQLite functions with some wrapper. For a 9% performance improvement in > SQLite using the direct call versus indirect

Re: [sqlite] Persisting Database Lock Issue

On Fri, 13 Dec 2019 at 23:50, 고예찬 wrote: > Hello, I am experiencing `database is locked` error. I wonder if anyone has > gone through or resolved similar issue. > > To illustrate, I have a .db file with below settings: > ``` > PRAGMA journal_mode=WAL; > PRAGMA wal_autocheckpoint=128; > PRAGMA

Re: [sqlite] database disk image is malformed

On Fri, 15 Nov 2019 at 16:10, Graham Holden wrote: > I've been having problems with my email system... I don't think > earlier attempts at sending have made it to the list, but if they > did, apologies for any duplication... > > Monday, November 11, 2019, 5:46:05 PM, Jukka Marin > wrote: > > >>

Re: [sqlite] proposal for write-lock on "commit" rather than "begin transaction"

On Sat, 26 Oct 2019 at 00:07, Brannon King wrote: > This is a request for a small change to the handling of multiple > connections. I think it would significantly enhance the usefulness there > via allowing multiple "views" of the data. > > Consider that I have two simultaneous connections to

Re: [sqlite] Theoretical write performance for low-throughput devices

On Mon, 21 Oct 2019 at 23:28, Jonathan Brandmeyer wrote: > Or, how many times is each page written by SQLite for an insert-heavy > test? The answer appears to be "4", but I can only account for two of > those four. > > I'm working on an embedded system that uses a log-structured > filesystem on

Re: [sqlite] Roadmap?

On Sun, 20 Oct 2019 at 17:04, Simon Slavin wrote: > Another common request is full support for Unicode (searching, sorting, > length()). But even just the tables required to identify character > boundaries are huge. > Nitpick: there are no tables required to identify character boundaries. For

Re: [sqlite] Opposite of SQLite

SQLdark is free to use for any purpose except those which benefit Anish Kapoor or an affiliate of Anish Kapoor. -Rowan On Fri, 11 Oct 2019 at 03:37, wrote: > etiLQS or SQLead or SQLdark > Haha > > On Thu, Oct 10, 2019, 3:07 PM David Raymond > wrote: > > > SQLephantine > > > > > > -Original

Re: [sqlite] Online backup of in memory database

On Sun, 6 Oct 2019 at 23:27, Kadirk wrote: > How to do online backup of an in memory database (to disk)? > > Planning to use in memory database with 10 gb+ data, there are queries > continuously so stopping application is not an option. Looks like for on > disk databases it is possible with a

Re: [sqlite] disable file locking mechanism over the network

On Sat, 28 Sep 2019 at 06:59, Roman Fleysher wrote: > ( somewhat related to Re: [sqlite] Safe to use SQLite over a sketchy > network?) > > Dear SQLiters, > > I am using SQLite over GPFS distributed file system. I was told it > honestly implements file locking. I never experienced corruption. But

Re: [sqlite] Safe to use SQLite over a sketchy network?

On Thu, 26 Sep 2019 at 13:01, Jens Alfke wrote: > > > On Sep 24, 2019, at 3:48 PM, Keith Medcalf wrote: > > > > There are not, to my knowledge, any client/server database systems that > will work properly if the database resides on a network filesystem (meaning > remote multi-access). The

Re: [sqlite] Safe to use SQLite over a sketchy network?

On Wed, 25 Sep 2019 at 12:58, Simon Slavin wrote: > When I first learned the SQLite had problems with Network File Systems I > read a ton of stuff to learn why there doesn't seem to be a Network File > Systems that implements locking properly. > > Still, I wonder why someone working on a Linux

Re: [sqlite] Safe to use SQLite over a sketchy network?

On Wed, 25 Sep 2019 at 05:14, Randall Smith wrote: > I have an application where remote users will be connecting to a SQLite DB > over a network connection that seems to be somewhat sketchy (I can't > characterize it well; I'm hearing this second-hand). > > My question is: Do the

Re: [sqlite] The best way to check if a file is a sqlite3 DB file?

On Thu, 19 Sep 2019 at 16:03, Dominique Devienne wrote: > On Wed, Sep 18, 2019 at 6:43 PM Clemens Ladisch > wrote: > > > Peng Yu wrote: > > > Is there a better way to just return an exit status of 0 for > > > a sqlite3 DB file and 1 otherwise? > > > > Extract the magic header string from a

Re: [sqlite] Query for Many to Many

On Tue, 3 Sep 2019 at 22:17, Keith Medcalf wrote: > And the "," in the list of tables may be replaced by the word JOIN. It is > merely an alternate spelling. > I was surprised when this behaved differently in other SQL engines. eg. in SQLite you can write: SELECT col1, col2 FROM table1,

Re: [sqlite] SQLITE_BUSY, database is locked in "PRAGMA journal_mode"

On Fri, 30 Aug 2019 at 04:18, test user wrote: > B. Is there any method for determining lock transitions for connections? > - Is there an API? > - Would it be possible to use dtrace to instrument SQLite to detect > lock transitions? > - Where should I be looking? > On unix

Re: [sqlite] database like file archive

On Tue, 27 Aug 2019 at 21:57, Peng Yu wrote: > I haven't found an archive format that allows in-place delete (I know > that .zip, .7z and .tar don't). This means that whenever delete is > needed, the original archive must be copied first. This can be > problematic when the archive is large and

Re: [sqlite] SQLITE_BUSY (SQLITE_BUSY_SNAPSHOT) due to a simple SELECT query -- Documentation needed!

On Mon, 12 Aug 2019 at 16:55, Kira Backes wrote: > > When you do not use explicit transactions, SQLite will automatically > create implicit transactions. > > But the documentation only says that an implicit transaction is > created for data-changing queries like INSERT: > >

Re: [sqlite] round function inconsistent

On Mon, 27 May 2019 at 23:36, Jose Isaias Cabrera wrote: > Ok, I think it happens even before the casting. This should be, > 3.2598, and yet, it's 3.26. > > sqlite> SELECT 0.005 + 3.2548; > 3.26 > Note that no arithmetic is required to see these symptoms: sqlite>

Re: [sqlite] readfile() enhancement request

On Sat, 18 May 2019 at 00:34, Tony Papadimitriou wrote: > It’s quite often (for me, at least) the case I need to do something like > this from the command line: > > >sqlite3.exe my.db “insert into t values(‘simple field’,’multi-line text > copied from some other app’) > > The problem is the

Re: [sqlite] Location of error in SQL statements ?

On Tue, 7 May 2019 at 16:00, Eric Grange wrote: > Is there are way to get more information about an SQL syntax error message > ? > For example on a largish SQL query I got the following error message > > near "on": syntax error > > but as the query is basically a long list of joins, this

Re: [sqlite] Go & SQLite asserts

On Fri, 3 May 2019 at 16:03, Dominique Devienne wrote: > On Mon, Apr 29, 2019 at 9:49 PM Russ Cox wrote: > > > On Mon, Apr 29, 2019 at 3:28 PM Richard Hipp wrote: > > For what it's worth, it was not clear to me until just now that the > article > > existed to push back on a general "asserts

Re: [sqlite] sqlite3 java.lang.IllegalStateException: SQLite JDBC: inconsistent internal state

On Wed, 1 May 2019 at 19:30, Frank Kemmer wrote: > > https://github.com/xerial/sqlite-jdbc/blob/14839bae0ceedff805f9cda35f5e52db8c4eea88/src/main/java/org/sqlite/core/CoreResultSet.java#L86 > > Here we see, that colsMeta == null results in throwing the seen exception. > > But how can colsMeta be

Re: [sqlite] Regarding sqlite3 reliability in using in service oriented architecture

On Mon, 29 Apr 2019 at 01:22, Lullaby Dayal wrote: > > Considering all this, I have written a test application running on Linux > with sqlite3 library in serialized mode. My test application has 200 > parallel threads in which 100 threads are executing SELECT * operation from > a table and 100

Re: [sqlite] Please help me fix the SQLite Git mirror

Richard Hipp wrote (quoting from several emails): > The problem is that Git now thinks that 9b888fcc is the HEAD of master > and that the true continuation of master (check-in 4f35b3b7 and > beyond) are disconnected check-ins > Because from the git perspective it _is_ still the HEAD -- there's

Re: [sqlite] importing a large TSV file

On Mon, 1 Apr 2019 at 19:20, Domingo Alvarez Duarte wrote: > Hello Gert ! > > I normally do this (be aware that if there is a power outage the > database is screwed): > > === > > PRAGMA synchronous = OFF; > begin; > > --processing here > > commit; > PRAGMA synchronous = ON; > You can probably

Re: [sqlite] Handling ROLLBACK

On Sun, 3 Mar 2019 at 20:53, Keith Medcalf wrote: > My observation (on the current tip version 3.28.0) of Schrodingers > Transactions is that if there is (for example) a transaction in progress > and that is COMMIT or ROLLBACK, then the changes are either committed or > rolled back and the

Re: [sqlite] Deserialize a WAL database file

On Fri, 1 Mar 2019 at 18:26, Lloyd wrote: > I have two database files. One in Rollback mode and the other in WAL mode. > I am able to serialize, deserialize and prepare a SQL query against the > rollback database. When I do the same against the WAL database file, the > 'prepare' statement fails

Re: [sqlite] Disk I/O errors

On Sun, 24 Feb 2019 at 01:55, Tim Streater wrote: > (sorry for the duplicate - vibrating finger). > > I have a hosted web site using the SQLite functions from PHP. The page > where PHP is used was failing, and on investigation this is because an > SQLite function called from within PHP is now

Re: [sqlite] Index on expression optimization

On Fri, 15 Feb 2019 at 16:13, Wout Mertens wrote: > sqlite> create index b on t(b) where b is not null; > sqlite> explain query plan select b from t where b is not null; > QUERY PLAN > `--SCAN TABLE t USING COVERING INDEX b > sqlite> explain query plan select b from t where (b is not null)=1; >

Re: [sqlite] lost errors on writeback/fsync/close - does SQLite need to be fixed similary to PostgreSQL?

On Tue, 12 Feb 2019 at 15:07, Rowan Worth wrote: > Huh, fascinating stuff. I'm not an sqlite developer but I can shed light > on some of your questions. > > On Tue, 12 Feb 2019 at 09:54, Edwin Török wrote: > >> A very conservative interpretation of various fsync bugs in var

Re: [sqlite] lost errors on writeback/fsync/close - does SQLite need to be fixed similary to PostgreSQL?

Huh, fascinating stuff. I'm not an sqlite developer but I can shed light on some of your questions. On Tue, 12 Feb 2019 at 09:54, Edwin Török wrote: > A very conservative interpretation of various fsync bugs in various OS > kernels [2][5] would suggest that: > > #1. the list of known OS issues

Re: [sqlite] Committing changes to the database without releasing a writer lock

On Fri, 8 Feb 2019 at 20:03, Theodore Dubois wrote: > I'd like to essentially commit changes to disk in the middle of the > transaction, resulting in a transaction that is atomic with respect to > other database connections but is two atomic transactions with respect to > the filesystem. >

Re: [sqlite] Min/Max and skip-scan optimizations

On Tue, 5 Feb 2019 at 22:46, Simon Slavin wrote: > On 5 Feb 2019, at 8:59am, Rowan Worth wrote: > > > SELECT source1, source2, ts, value > > FROM rolling > > WHERE source1 = 'aaa' > > AND ts > 1 AND ts < 1 > > ORDER BY source1, source2, ts; &g

Re: [sqlite] Min/Max and skip-scan optimizations

On Tue, 5 Feb 2019 at 16:06, Simon Slavin wrote: > On 5 Feb 2019, at 8:00am, Gerlando Falauto > wrote: > > > Thank you for your explanations guys. All this makes perfect sense. > > I still can't find a solution to my problem though -- write a query that > is guaranteed to return sorted results,

Re: [sqlite] Feedback on article about SQLITE_BUSY

On Fri, 25 Jan 2019 at 13:21, Rahul Jayaraman wrote: > > 1. "In the above case, since all transactions are started with > IMMEDIATE,” -- the diagram is actually using EXCLUSIVE transactions not > IMMEDIATE > > 2. "they behave as writers, and concurrent transactions are blocked" — > this implies

Re: [sqlite] Feedback on article about SQLITE_BUSY

On Tue, 22 Jan 2019 at 17:24, Rahul Jayaraman wrote: > From an operational perspective, which describes algorithms used and > implementation details. I think it’s useful to understand algorithms > because different algorithms give rise to different `busy` scenarios, and > having a better mental

Re: [sqlite] Feedback on article about SQLITE_BUSY

On Tue, 22 Jan 2019 at 12:51, Rahul Jayaraman wrote: > > I think "single writer, multiple readers" is the simplest way to describe > sqlite's approach to isolation > > I’m not sure if this summarization paints enough of a picture about how > SQLite restricts interleaving of read & write

Re: [sqlite] Database locking problems

On Mon, 21 Jan 2019 at 07:21, Keith Medcalf wrote: > In DELETE or TRUNCATE (that is, all modes except WAL) a READ transaction > in progress blocks a WRITE transaction and a WRITE transaction in progress > blocks all other attempts to commence a transaction of any type on any > other connection.

Re: [sqlite] Database locking problems

On Mon, 21 Jan 2019 at 15:46, wrote: > For the moment, the solution that is working for me is to disable syncing > with PRAGMA synchronous = OFF. This is acceptable in this particular > application because a power failure or OS crash will necessitate restarting > the data gathering process

Re: [sqlite] SQLite error (5): database is locked

On Tue, 15 Jan 2019 at 02:54, Simon Slavin wrote: > The "just-in-time" idea mentioned in your question doesn't work in real > life, since constantly checking mutex status keeps one core completely > busy, using lots of power and generating lots of heat. > Technically "just-in-time" could be

Re: [sqlite] Feedback on article about SQLITE_BUSY

There seems to be a few misconceptions in the article regarding the difference between DEFERRED/IMMEDIATE/EXCLUSIVE, and the fine details of how different lock states interact. Specifically, your diagrams suggest that once a writer obtains a RESERVED lock (as happens when an IMMEDIATE transaction

Re: [sqlite] Question about floating point

On Sat, 15 Dec 2018 at 15:10, Frank Millman wrote: > On Dec 15, 2018, at 08.58, Jay Kreibich wrote: > > > > On Dec 15, 2018, at 12:49 AM, Frank Millman > wrote: > > > > > > I know that floating point is not precise and not suitable for > financial uses. Even so, I am curious about the following

Re: [sqlite] [Question] Non-EXCLUSIVE modes in dot-file locking strategy

On Sun, 16 Dec 2018 at 05:00, Pierre Tempel wrote: > > “... programs which rely on [the O_CREAT and O_EXCL flags of > > open(2) to work on filesystems accessed via NFS version 2] for > > performing locking tasks will contain a race condition. The solution > > for performing atomic file locking

Re: [sqlite] Parallel reading can be slow on APFS

"The problem will affect you only if you have multiple reads/writes happening at the same time." ie. The problem will only manifest if the user is doing anything at all with their computer? :P Interesting analysis - thanks for sharing. -Rowan On Tue, 30 Oct 2018 at 10:13, Simon Slavin wrote:

Re: [sqlite] Regarding CoC

On Fri, 19 Oct 2018 at 19:52, Mantas Gridinas wrote: > I found code of conduct in documentation and I was wondering if it were > true. Checking the version history it appears to have been added on > 2018-02-22. > > 23. Do not nurse a grudge. ::sigh:: DROP TABLE grudges; I was amassing such a

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

On Wed, 10 Oct 2018 at 05:18, Warren Young wrote: > That event was the immediate spur to start this Fossil forum project, but > if you search the archives, there are multiple threads. Here’s one from > about a year ago: > > >

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

On Sat, 13 Oct 2018 at 02:20, Lars Frederiksen wrote: > I type info into some labeledits and by pressing "Append to DB" button > this code is executed (fdqGoser2 is a FDQuery) > > fdqGloser2.Open; > fdqGloser2.Append; > fdqGloser2.FieldByName('Graesk').AsString:= ledGræsk.Text; > ... >

Re: [sqlite] error message: [FireDAC][Phys][SQLite] Database is locked

On Sat, 13 Oct 2018 at 00:21, Chris Locke wrote: > > Database is locked > > Close your application. Is there a xxx-journal file in the same directory > as the database? (where xxx is the name of the database) > Try deleting this file. > For the record, "delete the journal file" is terrible

Re: [sqlite] [EXTERNAL] shell csv import

You can also filter out specific messages at the shell level: sqlite foo.db 2> >(grep -v 'expected 7 columns but found 6 - filling the rest with NULL' >&2) But note that the >() syntax is not a POSIX sh feature, and will not work in a script using a shebang of #!/bin/sh. You need to change it to

Re: [sqlite] Database occasionally very slow for trivial query

On 10 September 2018 at 22:28, Joshua Watt wrote: > BEGIN TRANSACTION; DELETE FROM BB_URI_HEADREVS; COMMIT; > Normally, this query takes no more than 1-3 seconds to complete, > however, on rare occasion this will take an order of magnitude more > (20-30 seconds). > > pragma synchronous =

Re: [sqlite] Unsigned

above select shows the issues. > > > On Tue, Aug 21, 2018 at 6:25 PM, Rowan Worth wrote: > > sqlite is pretty loose about types. The column definitions don't > constrain > > what is stored in the rows at all: > > > > sqlite> CREATE TABLE a(c INTEGER); &g

Re: [sqlite] Unsigned

sqlite is pretty loose about types. The column definitions don't constrain what is stored in the rows at all: sqlite> CREATE TABLE a(c INTEGER); sqlite> INSERT INTO a VALUES ("fourty-two"); sqlite> SELECT * FROM a; fourty-two So "UNSIGNED" seems kind of pointless as it's implies a further

Re: [sqlite] Safe sqlite over remote filesystem?

On 15 August 2018 at 14:12, Wout Mertens wrote: > On Tue, Aug 14, 2018 at 6:28 PM Rowan Worth wrote: > > > FWIW in the building I work in we have 20-30 users hitting around a dozen > > SQLite DBs 8 hours a day 5 days a week, with all DBs served over nfs. > > > Mult

Re: [sqlite] Safe sqlite over remote filesystem?

On 15 August 2018 at 13:57, Wout Mertens wrote: > For the interested: > > In NixOS (http://nixos.org), a very interesting Linux distribution, the > entire OS (libraries, binaries, shared files, up to and including > configuration files) is composed out of "build products" that are addressed > by

Re: [sqlite] Safe sqlite over remote filesystem?

FWIW in the building I work in we have 20-30 users hitting around a dozen SQLite DBs 8 hours a day 5 days a week, with all DBs served over nfs. The number of corruptions I've seen in the last 5 years which nfs *might* be responsible for is *very low*. The number of corruptions where nfs was

Re: [sqlite] No unicode characters in sqlite3 console prg

On 12 August 2018 at 00:51, Lars Frederiksen wrote: > Is this an error of my windows 10 cmd prompt or is it a general problem > that > the sqlite3 console is not able to show unicode in the cmd-window.?? > > No problem writing to the database and show (greek) unicode characters in > the cmd

Re: [sqlite] Database locks

On 7 August 2018 at 21:25, David Raymond wrote: > Correct. > > In rollback journal mode when one connection says "I'm ready to write now" > it blocks any new transactions from being made, but it can't do anything > about existing read transactions. It has to wait for them to finish their > reads

Re: [sqlite] [EXTERNAL] Save text file content in db: lines or whole file?

On 6 August 2018 at 22:20, R Smith wrote: > Think of paragraphs in English as large records delimited by 2 or more > Line-break characters (#10+#13 or perhaps only #10 if on a *nix platform) > between texts. > > Each paragraph record could be comprised of one or more sentences (in > English) as

Re: [sqlite] Reducing index size

On 30 July 2018 at 18:10, Eric Grange wrote: > @Rowan Worth > > Doesn't that problem already exist with the current index? Except worse > > because it's storing the cryptographic hash *and* the rowid. > > No, because SQLite is using a B-Tree (and with cryptographic hashes,

Re: [sqlite] Reducing index size

On 30 July 2018 at 17:53, Eric Grange wrote: > @Rowan Worth > > What if you could create a "lite" index, which stores just the rowids in > a particular order and > > refers back to the table for the rest of the column data? > > As I have millions of rows, an

Re: [sqlite] Reducing index size

On 30 July 2018 at 17:25, Dominique Devienne wrote: > On Mon, Jul 30, 2018 at 10:42 AM Simon Slavin > wrote: > > > On 30 Jul 2018, at 9:32am, Eric Grange wrote: > > > > > As these are cryptographic GUIDs, the first few bytes of a values are > in > > > practice unique, so in theory I can index

Re: [sqlite] Backup and integrity check questions

On 28 July 2018 at 05:41, Rune Torgersen wrote: > > Rowan Worth Thu, 26 Jul 2018 22:02:50 -0700 > > > > On 26 July 2018 at 05:56, Rune Torgersen wrote: > > > > > The databases have been opened with two connections (one for reads, one > > >

Re: [sqlite] Backup and integrity check questions

On 26 July 2018 at 05:56, Rune Torgersen wrote: > The databases have been opened with two connections (one for reads, one > for writes), and use the following options: > sqlite3_busy_timeout(mDbConn, 500); > sqlite3_exec(mDbConn, "PRAGMA locking_mode = EXCLUSIVE;", 0, 0, 0); > Surely

Re: [sqlite] two threads block eachother opening db (WAL)

On 2 July 2018 at 23:32, Charles Samuels wrote: > I have found that when my process has a lot of threads each of which opens > a > DIFFERENT database, they each block on eachother while opening each > database. > > This is at least on conflict with the documentation, as the documentation >

Re: [sqlite] Strange Corruption Issue

Between updates, automatic maintenance, registry churn, event logs, and background "optimisations" I reckon windows could give 400G/day a run for its money :P -Rowan On 19 June 2018 at 12:37, Keith Medcalf wrote: > > The new "consumer" SSDs from Samsung carry a 1200 TBW/8 year warranty on a >

Re: [sqlite] Trigger behaviour on UPDATE after release 3.24.0

On 6 June 2018 at 07:14, Richard Hipp wrote: > On 6/5/18, Stéphane Aulery wrote: > > Hello, > > > > The changelog of the last release [1] say at the point 13 : > > > > UPDATE avoids writing database pages that do not actually change. For > > example, "UPDATE t1 SET x=25 WHERE y=?" becomes a

Re: [sqlite] Feature suggestion / requesst

On 3 June 2018 at 07:28, Scott Robison wrote: > I've encountered a feature that I think would be awesome: > https://www.postgresql.org/docs/9.3/static/dml-returning.html > > Example: INSERT INTO blah (this, that, another) VALUES (x, y, z) RETURNING > id; > > my thoughts are just that this

Re: [sqlite] This is driving me nuts

On 28 May 2018 at 17:29, x wrote: > I’ve just discovered the thread in the original app decreases the > available memory by around 4 GB. Are they really that expensive? A thread itself is not expensive in terms of memory. > It has very little data of its own Either

Re: [sqlite] Stored Procedures

On 14 May 2018 at 01:08, Richard Damon <rich...@damon-family.org> wrote: > On 5/13/18 12:55 PM, Rowan Worth wrote: > > On 9 May 2018 at 08:56, Richard Hipp <d...@sqlite.org> wrote: > > > >> But with > >> SQLite, there is no round-trip latency.

Re: [sqlite] Stored Procedures

On 9 May 2018 at 08:56, Richard Hipp wrote: > But with > SQLite, there is no round-trip latency. A "round-trip" to and > database is just a function call, and is very very cheap. > I want to emphasise that Dr. Hipp's usage of "round-trip" only includes the latency of

Re: [sqlite] Database locking with online backup API

On 8 May 2018 at 17:22, R Smith wrote: > On 2018/05/08 9:37 AM, Donald Shepherd wrote: > >> I've long assumed that when using the online backup API on a SQLite >> database, other processes will not be able to write to the source database >> for the duration of the

Re: [sqlite] Always call a value-quoting routine

On 7 May 2018 at 15:13, Scott Robison <sc...@casaderobison.com> wrote: > On Sun, May 6, 2018 at 11:34 PM, Rowan Worth <row...@dug.com> wrote: > > Its omission is interesting though. Does it indicate an incompetent > > attacker, or is companieshouse.gov.uk using

Re: [sqlite] Always call a value-quoting routine

Amusing -- but without the leading single-quote it would take intentional effort for a programmer to detonate this payload. Its omission is interesting though. Does it indicate an incompetent attacker, or is companieshouse.gov.uk using some bespoke approach like "delete all single quotes" instead

Re: [sqlite] Index on expression goes corrupt from valid SQL

On 23 March 2018 at 08:54, Deon Brewis wrote: > Most of the time when the database gets corrupted, we don't crash, it > corrupts midway through valid SQL (no pattern to it - completely unrelated > SQL). I was thinking if the expression functions have bugs in them it could >

Re: [sqlite] SQLITE_CANTOPEN_ISDIR and other extended error codes

On 20 March 2018 at 22:33, Deon Brewis wrote: > How do you actually get a SQLITE_CANTOPEN_ISDIR error? > > In order to get an extended result code, we need to pass a sqlite3* > connection, but you don't have that if the file can't be opened in the > first place. > I understand

Re: [sqlite] How to get ORDER BY / LIMIT to stick to the fast-path?

On 23 March 2018 at 05:24, Jonathan Moules wrote: > Hi List, > > The below query takes just 0.002 seconds to run (sqlite 3.15.0 and 3.23.0 > (preview)) despite looking through hundreds of thousands of records in each > table, and it returns 86 records in all. This

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

5/10 1/11 5/10 Always in conjunction with INTEGER PRIMARY KEY fwiw. Also the following command is perhaps more portable: sqlite3 yourfile.db .schema | grep -ic autoincrement The sqlite3 shell on my system is too old to understand .schema --indent and doesn't output anything so there's

Re: [sqlite] UPSERT

On 16 March 2018 at 21:44, Paul wrote: > A few years back I've been asking the same question. To be honest, there's > no more > efficient alternative, than the one that can be implemented within library > itself. > Both performance-wise and productivity-wise. > > Doing hacks with

Re: [sqlite] UPSERT

On 16 March 2018 at 18:24, Robert M. Münch wrote: > Hi, is there an elegant way to simulate a dynamic UPSERT in SQLite since > it doesn’t has an UPSERT? > > So, if I have a table with 30 columns and my code updates sub-sets out of > these columns, I don’t want to

Re: [sqlite] High performance and concurrency

On 2 March 2018 at 03:43, Shevek wrote: > We use HikariCP, so a connection is in use by one thread at a time with > JMM-safe handoff, and they all share the mmap region. > Shevek also wrote: > What I think is happening is that either a pthread mutex or a database lock is

Re: [sqlite] printf() problem padding multi-byte UTF-8 code points

What is your expected answer for: select length(printf ('%4s', 'です')) -Rowan On 18 February 2018 at 01:39, Ralf Junker wrote: > Example SQL: > > select > length(printf ('%4s', 'abc')), > length(printf ('%4s', 'äöü')), > length(printf ('%-4s', 'abc')), >

Re: [sqlite] Recommended tool to read SQLITE btree?

On 17 February 2018 at 08:34, Deon Brewis wrote: > Anybody have a recommendation for a tool that can read/show/interpret a > SQLITE file at the BTREE level? > > Want to be able to decode the links between pages, figure out what all the > data mean etc. And should be able to work

Re: [sqlite] "Error: NOT NULL constraint failed ..." when no such constraint exists

https://www.sqlite.org/withoutrowid.html "NOT NULL is enforced on every column of the PRIMARY KEY in a WITHOUT ROWID table." It goes on to say that NOT NULL is supposed to be enforced on all PRIMARY KEY columns of _every_ table according to the SQL standard, but an early version of sqlite

Re: [sqlite] os_unix.c:36136: (2)

On 12 January 2018 at 07:21, wei1.z wrote: > What is the meaning of this line? > > 01-11 14:40:59.733 10011 2864 2877 E SQLiteLog: (14) os_unix.c:36136: (2) > open() - > > db file cannot be found, or permission issue ? > To decipher this in future, the first number in

Re: [sqlite] Search % using sqlite

Firstly this code is extremely dangerous. What would happen if acInputString contained this string? ';DROP TABLE AUDIO; SELECT ' It's best practice to use bound parameters to prevent this kind of problem, ie. sqlite3_prepare(db, "SELECT NAME FROM AUDIO WHERE NAME LIKE ?", -1, , NULL);

Re: [sqlite] What happens if an in memory database runs out of memory

? > > Does that mean anything to you? If it doesn’t I’ll make a post on the c++ > builder forum. > > > From: Rowan Worth<mailto:row...@dug.com> > Sent: 29 December 2017 03:13 > To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org> > Subject: Re: [s

Re: [sqlite] Btree page corruption

On 28 December 2017 at 02:55, Simon Slavin wrote: > On 27 Dec 2017, at 6:10pm, Nikhil Deshpande wrote: > > >> Can you include a "pragma integrity_check" at startup ? > >> Can you include a "pragma integrity_check" executed at regular > intervals ? >

Re: [sqlite] What happens if an in memory database runs out of memory

On 23 December 2017 at 00:17, curmudgeon wrote: > >You can run tests yourself by compiling with -DSQLITE_ENABLE_MEMSYS5 > > Is that a minus sign before the DSQLITE_ENABLE_MEMSYSS? If I try compiling > with a minus sign before that directive I get a compile error "macro

Re: [sqlite] Minor bug reports during build.

On 22 December 2017 at 23:57, Michael Tiernan wrote: > > > It just doesn’t install to a directory it can’t write to, because you > > told it to install system-level things. > > Not going to hash it out here but I didn't tell it to install system-level > things, I told

Re: [sqlite] Btree page corruption

Does either process take backups of the DB? If so, how is that implemented? -Rowan On 22 December 2017 at 05:47, Nikhil Deshpande wrote: > Hi, > > We have an application that in a Linux VM that's running into > SQLite DB corruption (after weeks and months of running, > 4

Re: [sqlite] Minor bug reports during build.

Seems to be working as advertised. Unless you specify --disable-tcl, the configure script defaults to building an sqlite extension for TCL. The extension can't go in $PREFIX, since tcl wouldn't be able to find it. So your options for a non-root install are: 1) --disable-tcl 2) set the

  1   2   3   >