Re: [sqlite] wal mode

Even in rollback journal mode, it is not universally safe to use normal file operations on a SQLite database. See section 1.3 and 1.4 of https://www.sqlite.org/howtocorrupt.html If you want to use normal file system operations (or any type of manipulation not using the SQLite library) on an

Re: [sqlite] wal mode

On Fri, 6 Dec 2019 at 19:06, Simon Slavin wrote: > On 6 Dec 2019, at 6:39pm, MM wrote: > > > So it suffices that I run "PRAGMA journal_mode=WAL;" once from say the > sqlite3 cli, for all future connections from any tool will use WAL mode for > this database file? > > Correct. > > > What happens

Re: [sqlite] wal mode

On 6 Dec 2019, at 6:39pm, MM wrote: > So it suffices that I run "PRAGMA journal_mode=WAL;" once from say the > sqlite3 cli, for all future connections from any tool will use WAL mode for > this database file? Correct. > What happens when 2 processes that have had their connection open for a

Re: [sqlite] wal mode

"So it suffices that I run "PRAGMA journal_mode=WAL;" once from say the sqlite3 cli, for all future connections from any tool will use WAL mode for this database file?" Yup, the journal mode is stored in the database header. So the pragma will update the file's header, and any new connection

[sqlite] wal mode

Hello 3.26.0 From https://www.sqlite.org/wal.html, 3.3 I understand that "The persistence of WAL mode means that applications can be converted to using SQLite in WAL mode without making any changes to the application itself. One has merely to run "PRAGMA journal_mode=WAL;" on the database file(s)

Re: [sqlite] WAL mode much slower in certain use cases

On Behalf Of Andrew Cunningham Sent: Monday, July 01, 2019 2:04 PM To: SQLite Maillist Subject: [sqlite] WAL mode much slower in certain use cases I am using SQLite 3.24.0, as a single user persistent data store for storing simulation data. The database can grow to many gigabytes as the software can in

[sqlite] WAL mode much slower in certain use cases

I am using SQLite 3.24.0, as a single user persistent data store for storing simulation data. The database can grow to many gigabytes as the software can ingest a lot of binary data which I store as multiple BLOBs. In the following example I am reading several 22GB of data into the DB. Times

Re: [sqlite] WAL mode readonly errors to SELECT statements

The sensible permissions in this case would probably be rw-rw-r-- with the same group as the service and owned by the service group. That is how group permissions were designed to work. > > ___ sqlite-users mailing list

Re: [sqlite] WAL mode readonly errors to SELECT statements

Thanks for the hint about pragma QUERY_ONLY, that might work but we will have to rethink the database file management. The database is currently owned by root and has sensible unix permissions of rw, r, r (ie only root has write permission) We can't run the intended service as root because that

Re: [sqlite] WAL mode readonly errors to SELECT statements

On 24 Jan 2019, at 2:37am, Robert Searle wrote: > occasionally get either > SQLITE_READONLY_RECOVERY or SQLITE_READONLY_CANTINIT responses to select > statements No programmer should be seeing these. They indicate low-level errors that cannot be handled in a systematic manner. Rather than

Re: [sqlite] WAL mode readonly errors to SELECT statements

Robert Searle wrote: > We have recently started trying to provide read-only access to the database > (service run as user with group/other read access permissions under Linux, > service not database owner) and occasionally get either > SQLITE_READONLY_RECOVERY or SQLITE_READONLY_CANTINIT responses

[sqlite] WAL mode readonly errors to SELECT statements

Hi, I have an sqlite3 database (version 3.25.3) in tmpfs which has many readers and writers. The database is running in WAL mode and seems to work efficiently in that mode. Since the database files are in a memory based file-system, we don't care about the usual corruption on power-cycle issues

Re: [sqlite] WAL mode for in-memory databases?

That doesn't sound healthy at all. If your application dies, what happens to the database? What if something rogue starts hitting it and just chews up your memory? IMO, Mem databases should be short lived and treated simply as an intentional cache. I get they're fast, but, long term life for a

Re: [sqlite] WAL mode for in-memory databases?

It would also be very helpful if more control about in-memory-databases was available. As far as I have understood, an in-memory database is deleted when the last connection closes. This requires me to always hold a connection to an in-memory database even if don't need it right now. Maybe one

Re: [sqlite] WAL mode for in-memory databases?

I’m very interested in the answer to this as I was planning on do the exact same thing.  Not sure my app would even be able to function without WAL mode. Brian Macy On Jan 14, 2019, 8:28 AM -0500, Dominique Devienne , wrote: > On Mon, Jan 14, 2019 at 2:23 PM Wout Mertens wrote: > > > AFAIK,

Re: [sqlite] WAL mode for in-memory databases?

On Mon, Jan 14, 2019 at 2:23 PM Wout Mertens wrote: > AFAIK, your best bet is to put a file db on a ramdisk (tmpfs). That's not a very portable solution, and a work-around at best. I don't see anything technical that would prevent WAL to work for ":memory:". "Shared-memory" "in-process" is

Re: [sqlite] WAL mode for in-memory databases?

e deemed not useful for in-memory? > i.e. is there a work-around that makes WAL-mode in-memory superfluous? > Or it's not superfluous and not supported, but could technically be > supported? > > Thanks for any insights. --DD > > [1] > > https://stackoverflow.com/quest

[sqlite] WAL mode for in-memory databases?

://stackoverflow.com/questions/28358153/sqlite-wal-mode-in-memory-database-with-private-cache ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] wal-mode and checkpoint

2018-09-02 17:31 GMT+02:00 Simon Slavin : > On 2 Sep 2018, at 2:43pm, Cecil Westerhof wrote: > > > When I do in sqlitebrowser: > >PRAGMA TABLE_INFO(messages) > > Just for peace of mind, since you are reporting unexpected behaviour, > please run an integrity_check. > As expected that gave

Re: [sqlite] wal-mode and checkpoint

On 2 Sep 2018, at 2:43pm, Cecil Westerhof wrote: > When I do in sqlitebrowser: >PRAGMA TABLE_INFO(messages) Just for peace of mind, since you are reporting unexpected behaviour, please run an integrity_check. Certain cleaning-up jobs are done only when the last connection to the database

[sqlite] wal-mode and checkpoint

I changed from the default delete mode to wal mode. I had some strange results, but it is working now. One of the programs is a service and can run for weeks. That is why I decided to call every hour: PRAGMA WAL_CHECKPOINT(TRUNCATE) I was wondering what people on this list thought about

Re: [sqlite] WAL mode with readers and writers

On 20 Nov 2017, at 7:37pm, Jim Dossey wrote: > sqlite3_prepare("SELECT * FROM table;"); > while (sqlite3_step() == SQLITE_ROW) { > x = current_rowid(); > sqlite3_exec("UPDATE table SET value=1 WHERE rowid=x;"); > sleep(1); > } In SQLite, as in other SQL engines, all

Re: [sqlite] WAL mode with readers and writers

says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Jim Dossey >Sent: Monday, 20 November, 2017 12:37 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] WAL mode wi

[sqlite] WAL mode with readers and writers

Thanks to feedback from Simon Slavin, I now understand how data_version works and have it working in my code.  But in my testing, I tried another situation to see what would happen with locking in WAL mode.  I have a process that does the following pseudo-code with a table:

Re: [sqlite] WAL mode

Yes, I am closing the DB before program exits. Like this: db.Close(). I am using Sqlite3 with Golang. Regards, Niti On Wed, Feb 8, 2017 at 11:08 PM, Jens Alfke wrote: > > > On Feb 8, 2017, at 9:21 AM, Niti Agarwal wrote: > > > > According to the

Re: [sqlite] WAL mode

On 8 Feb 2017, at 6:26pm, Jens Alfke wrote: > On Feb 8, 2017, at 9:46 AM, Simon Slavin wrote: > >> Does your program execute sqlite3_shutdown() and check to see whether it >> returns an error code ? > > Never noticed that function before … the docs

Re: [sqlite] WAL mode

> On Feb 8, 2017, at 9:46 AM, Simon Slavin wrote: > > Does your program execute sqlite3_shutdown() and check to see whether it > returns an error code ? Never noticed that function before … the docs say it’s "designed to aid in process initialization and shutdown on

Re: [sqlite] WAL mode

On 8 Feb 2017, at 5:21pm, Niti Agarwal wrote: > The databases I'm working with are write ahead logging (WAL) databases. > According to the SQLite documentation, the shm and wal files are supposed > to be deleted upon completion of the program. However, these files are >

Re: [sqlite] WAL mode

> On Feb 8, 2017, at 9:21 AM, Niti Agarwal wrote: > > According to the SQLite documentation, the shm and wal files are supposed > to be deleted upon completion of the program. Are you explicitly closing the database before your program exits? (In other words, I think

Re: [sqlite] WAL mode

On 2017/02/08 7:21 PM, Niti Agarwal wrote: The databases I'm working with are write ahead logging (WAL) databases. According to the SQLite documentation, the shm and wal files are supposed to be deleted upon completion of the program. However, these files are still there after execution of

[sqlite] WAL mode

The databases I'm working with are write ahead logging (WAL) databases. According to the SQLite documentation, the shm and wal files are supposed to be deleted upon completion of the program. However, these files are still there after execution of this program. The documentation says the files

Re: [sqlite] Is it safe to use sqlite WAL mode on windows server 2003+ with TSE access ?

What's the programming language used in the application? I use Freepascal / Lazarus as a programming language and my own sqlite wrapper written in Freepascal as well. I will add the sqlite3_extended_errcode + sqlite3_errstr to the wrapper lib in order to get extended error infos and will

Re: [sqlite] Is it safe to use sqlite WAL mode on windows server 2003+ with TSE access ?

On 2016/06/13 3:41 PM, Luc DAVID wrote: Thanks Ryan and Oliver for your answers You are too fast for me, I didn't have the time to answer the first posts... @Ryan > There is no safe way to do what you need. Networked file systems do not > play nice with file-locking. SQLite is suitable for

Re: [sqlite] Is it safe to use sqlite WAL mode on windows server 2003+ with TSE access ?

> Le 13 juin 2016 à 15:41, Luc DAVID a écrit : > > The worst case is when you get a "Database is locked" message which mean you > have to kill all the application processes in order to unlock the db (unless > another means exists ?) You shouldn't get any such with

Re: [sqlite] Is it safe to use sqlite WAL mode on windows server 2003+ with TSE access ?

Thanks Ryan and Oliver for your answers You are too fast for me, I didn't have the time to answer the first posts... @Ryan > There is no safe way to do what you need. Networked file systems do not > play nice with file-locking. SQLite is suitable for localized storage, > not

Re: [sqlite] Is it safe to use sqlite WAL mode on windows server 2003+ with TSE access ?

On 2016/06/13 3:12 PM, Olivier Mascia wrote: Le 13 juin 2016 à 15:02, R Smith a écrit : The application will be used by ± 10 users via RDP TSE clients. Hi Luc, There is no safe way to do what you need. Networked file systems do not play nice with file-locking. SQLite

Re: [sqlite] Is it safe to use sqlite WAL mode on windows server 2003+ with TSE access ?

> Le 13 juin 2016 à 15:02, R Smith a écrit : > >> The application will be used by ± 10 users via RDP TSE clients. >> > Hi Luc, > There is no safe way to do what you need. Networked file systems do not play > nice with file-locking. SQLite is suitable for localized storage,

Re: [sqlite] Is it safe to use sqlite WAL mode on windows server 2003+ with TSE access ?

> Le 13 juin 2016 à 14:20, Luc DAVID a écrit : > > Hello, > > I'm planning to install an application using sqlite on a windows 2003 > server. > > The application exe + sqlite dll + database will be installed in the same > server directory. Best would be to compile

Re: [sqlite] Is it safe to use sqlite WAL mode on windows server 2003+ with TSE access ?

On 2016/06/13 2:20 PM, Luc DAVID wrote: Hello, I'm planning to install an application using sqlite on a windows 2003 server. The application exe + sqlite dll + database will be installed in the same server directory. The application will be used by ± 10 users via RDP TSE clients. I need to

[sqlite] Is it safe to use sqlite WAL mode on windows server 2003+ with TSE access ?

Hello, I'm planning to install an application using sqlite on a windows 2003 server. The application exe + sqlite dll + database will be installed in the same server directory. The application will be used by ± 10 users via RDP TSE clients. I need to take care of possible database locks

Re: [sqlite] WAL mode, references to IO errors

sers- > > boun...@sqlite.org] On Behalf Of Aemon Cannon > > Sent: Tuesday, February 14, 2012 3:03 PM > > To: sqlite-users@sqlite.org > > Subject: [sqlite] WAL mode, references to IO errors > > > > Hello, > > > > In reading about WAL mode, I found the follo

Re: [sqlite] WAL mode, references to IO errors

age- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Aemon Cannon > Sent: Tuesday, February 14, 2012 3:03 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] WAL mode, references to IO errors > > Hello, > > In r

[sqlite] WAL mode, references to IO errors

Hello, In reading about WAL mode, I found the following passage disconcerting: "WAL works best with smaller transactions. WAL does not work well for very large transactions. For transactions larger than about 100 megabytes, traditional rollback journal modes will likely be faster. For

Re: [sqlite] WAL mode is reliable?

> boun...@sqlite.org] On Behalf Of Richard Hipp > (2) WAL really does give better concurrency since it allows other processes > to continue reading while a single process is writing to the database. In > rollback mode you can have one writer *or* multiple readers. In WAL mode > you can have one

Re: [sqlite] WAL mode is reliable?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/11/11 11:32, Paxdo Presse wrote: > Roger, I meant: I feel that WAL is rarely used with its new > capabilities. Outside almost 100 million Android devices? > SQLite + WAL may, perhaps, now compete with Mysql / postgres to handle > web apps with

Re: [sqlite] WAL mode is reliable?

> >> I feel that WAL is rarely used now. > > It is used on my Android phone running 2.3. That is almost 100 million > devices with all sorts of programs and crash scenarios. Is that rare? Roger, I meant: I feel that WAL is rarely used with its new capabilities. SQLite + WAL may, perhaps,

Re: [sqlite] WAL mode is reliable?

On 3 Nov 2011, at 6:05pm, Paxdo Presse wrote: > You think SQLite can handle a website / web app that has 1000 readers per > day, including 200 to 300 who will write regularly? You should be able to handle 10,000 posts a day without problems. A single INSERT command keeps the database locked

Re: [sqlite] WAL mode is reliable?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/11/11 11:05, Paxdo Presse wrote: > I feel that WAL is rarely used now. It is used on my Android phone running 2.3. That is almost 100 million devices with all sorts of programs and crash scenarios. Is that rare? Roger -BEGIN PGP

Re: [sqlite] WAL mode is reliable?

On 3 Nov 2011, at 5:44pm, Richard Hipp wrote: > On Thu, Nov 3, 2011 at 12:13 PM, Simon Slavin wrote: > >> >> If I start getting contention issues (locks failing because of too many >> concurrent attempts at access) then I'll look into using some PRAGMAs, >> possibly

Re: [sqlite] WAL mode is reliable?

Thank you M. Hipp ! I feel that WAL is rarely used now. It is difficult to get opinions / feedback on this feature. And for users of database servers (MySQL, Postgres, etc.). It is impossible to have a reliable database without a server. You think SQLite can handle a website / web app that

Re: [sqlite] WAL mode is reliable?

On Thu, Nov 3, 2011 at 12:13 PM, Simon Slavin wrote: > > If I start getting contention issues (locks failing because of too many > concurrent attempts at access) then I'll look into using some PRAGMAs, > possibly switching to WAL. > > Several points: (1) You only have to

Re: [sqlite] WAL mode is reliable?

On 3 Nov 2011, at 3:57pm, Paxdo Presse wrote: > Even if the requests are very fast, it seems risky not to use WAL? As opposed to no journal ? Yes, using no journal at all is risky. But the older style rollback journal system was about as fast and as trustworthy as WAL journals. I see no

Re: [sqlite] WAL mode is reliable?

On 03 Nov 2011 at 14:02, Simon Slavin wrote: > On 3 Nov 2011, at 1:38pm, Paxdo Presse wrote: > >> How did you get concurrent connections? (about) > > This is related specifically to the web language I use (PHP) so it won't help > you if you're using something else. > >

Re: [sqlite] WAL mode is reliable?

> For concurrency it works fine. The amount of time a connection takes is very > small: just long enough to open, set timeout, do a single query/exec, and > close the connection. So even with lots of users there aren't that many > concurrent connections to the database file. > > Looking

Re: [sqlite] WAL mode is reliable?

On 3 Nov 2011, at 1:38pm, Paxdo Presse wrote: > How did you get concurrent connections? (about) This is related specifically to the web language I use (PHP) so it won't help you if you're using something else. Actually my system works like this: the web pages themselves are '.html' files do

Re: [sqlite] WAL mode is reliable?

Thank you Simon! How did you get concurrent connections? (about) Le 3 nov. 2011 à 14:32, Simon Slavin a écrit : > > On 3 Nov 2011, at 1:22pm, Paxdo Presse wrote: > >> What do you think of SQLite with WAL mode? >> >> Is it reliable? >> >> It is for a web application. >> >> The database

Re: [sqlite] WAL mode is reliable?

On 3 Nov 2011, at 1:22pm, Paxdo Presse wrote: > What do you think of SQLite with WAL mode? > > Is it reliable? > > It is for a web application. > > The database is hosted on a single computer, but there may be multiple > simultaneous connections via Internet (and LAN). > All users access

[sqlite] WAL mode is reliable?

Hi, What do you think of SQLite with WAL mode? Is it reliable? It is for a web application. The database is hosted on a single computer, but there may be multiple simultaneous connections via Internet (and LAN). All users access to database via a web browser. In your opinion, how about

Re: [sqlite] WAL mode and Network filesystems

On Fri, Aug 26, 2011 at 4:43 PM, wrote: > Well, my system configuration is such that the RFS is mounted via NFS > server. All the processes that access the DB will be on the same CPU. Just FYI: in my very limited experience, using fcntl()-style locking on NFS can bring

Re: [sqlite] WAL mode and Network filesystems

umar TP > To: General Discussion of SQLite Database > Subject: Re: [sqlite] WAL mode and Network filesystems > Sent: Aug 26, 2011 20:08 > > http://www.sqlite.org/wal.html > > Disadvantage #2: > All processes using a database must be on the same host computer; WAL > does not

Re: [sqlite] WAL mode and Network filesystems

Well, my system configuration is such that the RFS is mounted via NFS server. All the processes that access the DB will be on the same CPU. --Original Message-- From: Pavel Ivanov To: Sreekumar TP To: General Discussion of SQLite Database Subject: Re: [sqlite] WAL mode and Network

Re: [sqlite] WAL mode and Network filesystems

http://www.sqlite.org/wal.html Disadvantage #2: All processes using a database must be on the same host computer; WAL does not work over a network filesystem. So as long as all users of your database are on the same host it seems that WAL will work even if file is on NFS. But then what's the

[sqlite] WAL mode and Network filesystems

I understand that WAL mode of sqlite is not supported over network file systems. Does this mean that if my DB is in a filesystem mounted on a NFS server will also not work in WAL mode? If so what is the bottleneck? Sent from BlackBerry® on Airtel ___

Re: [sqlite] sqlite WAL mode

Michael D. Black, Thank you for your suggestion use the sqlite 3.7 WAL mode. We wrote a small test program to open 2 WAL connections to the main database and insert 5.4 million rows into a table. The code is shown below. We wiil add sqlite error handling handling code tomorrow. The program

Re: [sqlite] WAL mode and backup API

On 30.11.2010 11:50, Dan Kennedy wrote: > >> BTW: >> Is there any chance that in the future the wal mode >> will avoid that the backup API will restart on DB >> changes during the backup loop ? >> Currently, even in wal mode, it does restart when changes >> are detected while the backup is

Re: [sqlite] WAL mode and backup API

> BTW: > Is there any chance that in the future the wal mode > will avoid that the backup API will restart on DB > changes during the backup loop ? > Currently, even in wal mode, it does restart when changes > are detected while the backup is ongoing. What happens if you open a read transaction

Re: [sqlite] WAL mode and backup API

On 30.11.2010 11:32, Dan Kennedy wrote: > On 11/30/2010 05:03 PM, Marco Bambini wrote: >> Hello, >> >> if a running sqlite database is in WAL mode and a backup is performed on >> that db using the sqlite3_backup API, does that process is considered like a >> reader and can proceed concurrently

Re: [sqlite] WAL mode and backup API

On 11/30/2010 05:03 PM, Marco Bambini wrote: > Hello, > > if a running sqlite database is in WAL mode and a backup is performed on that > db using the sqlite3_backup API, does that process is considered like a > reader and can proceed concurrently with other readers and with the other > writer?

[sqlite] WAL mode and backup API

Hello, if a running sqlite database is in WAL mode and a backup is performed on that db using the sqlite3_backup API, does that process is considered like a reader and can proceed concurrently with other readers and with the other writer? Thanks a lot for your answer. -- Marco Bambini

[sqlite] WAL mode enhancement suggestion

About a week ago, I posted this inside a response on another thread. It may have gotten lost in the mix. Curious what folks think. Considering things like bulk deletes (and updates) potentially really growing a WAL file to be quite large along with having a system that is constantly running and