Re: [sqlite] CLI option to NOT autocheckpoint WAL databases

[Howard Kapustein]

>> Is it possible to use the CLI to read a WAL database and exit without
>> modifying the database?
>>
>The checkpoint-on-close feature is not affected by
>PRAGMA wal_autocheckpoint.  The NO_CKPT_ON_CLOSE DB config flag is the
>only mechanism to prevent it from inside the connection.



SQLITE_API int sqlite3_db_config(sqlite3 *db, int op, ...){
...
{ SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE,  SQLITE_NoCkptOnClose  },

and

SQLITE_PRIVATE int sqlite3PagerClose(Pager *pPager, sqlite3 *db){
  u8 *pTmp = (u8 *)pPager->pTmpSpace;
...
  sqlite3WalClose(pPager->pWal, db, pPager->ckptSyncFlags, pPager->pageSize,
  (db && (db->flags & SQLITE_NoCkptOnClose) ? 0 : pTmp)
  );


abd


SQLITE_PRIVATE int sqlite3WalClose(
  Wal *pWal,  /* Wal to close */
  sqlite3 *db,/* For interrupt flag */
  int sync_flags, /* Flags to pass to OsSync() (or 0) */
  int nBuf,
  u8 *zBuf/* Buffer of at least nBuf bytes */
){
  int rc = SQLITE_OK;
  if( pWal ){
int isDelete = 0; /* True to unlink wal and wal-index files */

/* If an EXCLUSIVE lock can be obtained on the database file (using the
** ordinary, rollback-mode locking methods, this guarantees that the
** connection associated with this log file is the only connection to
** the database. In this case checkpoint the database and unlink both
** the wal and wal-index files.
**
** The EXCLUSIVE lock is not released before returning.
*/
if( zBuf!=0
 && SQLITE_OK==(rc = sqlite3OsLock(pWal->pDbFd, SQLITE_LOCK_EXCLUSIVE))
){
...checkpointy goodness...
}

walIndexClose(pWal, isDelete);
sqlite3OsClose(pWal->pWalFd);
if( isDelete ){
  sqlite3BeginBenignMalloc();
  sqlite3OsDelete(pWal->pVfs, pWal->zWalName, 0);
  sqlite3EndBenignMalloc();
}
WALTRACE(("WAL%p: closed\n", pWal));
sqlite3_free((void *)pWal->apWiData);
sqlite3_free(pWal);
  }
  return rc;
}



If checkpoint-on-close is disabled, sqlite3WalClose(...,zBuf=null) which skips 
doing the checkpoint.
This gives me the behavior I want. I just have no way of doing this via the CLI

I was pawing through various corrupted databases. What I *want* is

sqlite3 my.db
sqlite> PRAGMA checkpoint_on_close=0;
sqlite> PRAGMA integrity_check;
...
sqlite> PRAGMA user_version;
...
sqlite> .q

and have the filesystem in the same state as when as I started. Since the CLI 
can't set that, I had to COPY THE DB before I touched it

If the CLI could disable checkpoint-on-close it would have saved me a bunch of 
time


I don't need the db to be physically readonly (and that's not possible for 
WAL), but disabling checkpoint-on-close does it for me. I can issue readonly 
commands, but SQLite's default checkpoint-on-close behavior in the CLI always 
'writes'/modifies the database files regardless of my desire.

Make sense?

- Howard
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CLI option to NOT autocheckpoint WAL databases

[Rowan Worth]

On 29 May 2017 at 14:46, Clemens Ladisch  wrote:

> Howard Kapustein wrote:
> > I'm effectively looking for a -readonly option, or how to achieve that
> > net effect
>
> sqlite3 "file:test.db?mode=ro"
>
> But if you want to be really sure, set the file permissions to disallow
> writes.  (You have to except the -shm file, and this is not officially
> supported, and it appears there is a delay due to a lock timeout
> somewhere.)
>

Note that there are some circumstances where sqlite _must_ be able write to
the database even if you are only interested in reading data. Eg. in
rollback journal mode, if a program crashes or you lose power partway
through COMMIT the database is likely left in an inconsistent state. Sqlite
resolves this next time the database is accessed by using information in
the rollback journal to arrive back at a consistent state, which of course
involves writing to the database to undo the partial COMMIT.

It seems like a similar situation might arise in WAL mode if there's a
crash/power failure during a checkpoint? But I'm not very familiar with WAL
so forgive me if this is an irrelevant tangent.

-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CLI option to NOT autocheckpoint WAL databases

[Clemens Ladisch]

Howard Kapustein wrote:
> Is it possible to use the CLI to read a WAL database and exit without
> modifying the database?

The checkpoint-on-close feature is not affected by
PRAGMA wal_autocheckpoint.  The NO_CKPT_ON_CLOSE DB config flag is the
only mechanism to prevent it from inside the connection.

However, the comment in sqlite3WalClose() says:

/* If an EXCLUSIVE lock can be obtained on the database file (using the
** ordinary, rollback-mode locking methods, this guarantees that the
** connection associated with this log file is the only connection to
** the database. In this case checkpoint the database and unlink both
** the wal and wal-index files.

Well, using a second instance of the CLI to take a lock just kicks the
can down the road.

And I guess you don't simply want to kill the CLI.

> I'm effectively looking for a -readonly option, or how to achieve that
> net effect

sqlite3 "file:test.db?mode=ro"

But if you want to be really sure, set the file permissions to disallow
writes.  (You have to except the -shm file, and this is not officially
supported, and it appears there is a delay due to a lock timeout
somewhere.)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] CLI option to NOT autocheckpoint WAL databases

[Simon Slavin]

On 29 May 2017, at 5:33am, Howard Kapustein  
wrote:

> Is it possible to use the CLI to read a WAL database and exit without 
> modifying the database? 

I don’t know about the database file itself.  I think that one is only read 
unless you explicitly do writing.

SQLite will always create/modify/delete the -shm file.  That’s the shared 
memory file.

I /think/ SQLite always creates/modifies/deletes the -wal file if you do 
anything that requires database access, even just reading.

The database is actually just in the database file.  In fact, after a clean 
quit from all programs using the SQLite library the other two files can both be 
deleted without messing up the database in any way.

The two other files are used by SQLite in getting its work done.  SQLite must 
be free to modify or delete them whenever it wants — don’t try to make them 
read-only since this can cause errors in SQLite.  Unless you understand SQLite 
internals and you’re engaged in some sort of forensic work you don’t care 
what’s in those two files.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users