[sqlite] Assertion hit in sqlite3VarintLen

[Martin Raiber]

Hi,

I have used sqlite3VarintLen outside of SQLite and noticed that the
assertion in this function is hit if the highest bit is set (and it
returns 10 when it should return 9). E.g. with

sqlite3VarintLen(0xF000);

It could be that this is not relevant to SQLite as it is guaranteed that
the function is not used with numbers that large.

Re: [sqlite] Random IOERR_SHORT_READ in sqlite3_prepare_v2

[Martin Raiber]

On 14.09.2016 13:31 Dan Kennedy wrote:
> On 09/14/2016 06:05 PM, Martin Raiber wrote:
>> there have been three reports by users using my software of SQLite
>> 3.12.0 returning SQLITE_IOERR and logging a SQLITE_IOERR_SHORT_READ
>> (522). Specifically:
>>
>> 2016-09-12 04:37:04: WARNING: SQLite: disk I/O error errorcode: 522
>> 2016-09-12 04:37:04: ERROR: Error preparing Query [PRAGMA cache_size =
>> -2048]: disk I/O error
>>
>> One instance was on FreeBSD where I thought it could be caused by ZFS.
>> The other two instances are on Linux now. On FreeBSD the issue was
>> "fixed" by repeating the prepare after it failed with an IO-error.
>>
>> One user has captured an strace. I cannot actually see the short read,
>> though: https://forums.urbackup.org/t/urbackup-crashing/2402/8
>
> Can't see the write() calls used to write the "WARNING" or "ERROR"
> messages either. Should we expect to?

Yes, hope the user manages to capture a proper one. Sorry.

>
> This trace might be an unrelated crash.
>
> I think strace data would be helpful though.
>
> Dan.
> ___
> 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] Random IOERR_SHORT_READ in sqlite3_prepare_v2

[Martin Raiber]

Hi,

there have been three reports by users using my software of SQLite
3.12.0 returning SQLITE_IOERR and logging a SQLITE_IOERR_SHORT_READ
(522). Specifically:

2016-09-12 04:37:04: WARNING: SQLite: disk I/O error errorcode: 522
2016-09-12 04:37:04: ERROR: Error preparing Query [PRAGMA cache_size =
-2048]: disk I/O error

One instance was on FreeBSD where I thought it could be caused by ZFS.
The other two instances are on Linux now. On FreeBSD the issue was
"fixed" by repeating the prepare after it failed with an IO-error.

One user has captured an strace. I cannot actually see the short read,
though: https://forums.urbackup.org/t/urbackup-crashing/2402/8

Environment:

* Databases are in WAL journal mode
* synchronous=NORMAL
* wal_autocheckpoint is OFF. Checkpointing is done in a separate thread
with PRAGMA wal_checkpoint(PASSIVE) and wal_checkpoint(TRUNCATE) if the
WAL file is bigger than a certain size

Thanks for any help!

Regards,
Martin



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

Re: [sqlite] Random IOERR_SHORT_READ in sqlite3_prepare_v2

[Martin Raiber]

On 14.09.2016 13:26 Simon Slavin wrote:
> Check the hard disk format for format errors (fsck).
>
> Run "PRAGMA integrity_check" on the database file.
>
> Is the database file on a disk inside the computer running the SQLite calls, 
> or is it accessed across a network ?
>
> Do you use any PRAGMAs in your program ?
Seems to be on a local ext4 file system on some kind of hardware RAID.
Integrity check is done nightly (only quick check) and does not seem to
find any issues.

I'd expect corruptions to affect sqlite3_step as well and earlier. This
IO error only occurs for sqlite3_prepare_v2. Additionally the user
reports the problem starting to occur when using SQLite 3.12.0 and no
such issues with 3.7.17. When using 3.7.17 it also did not checkpoint
the WAL file in a separate thread (that is wal_autocheckpoint was on).

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

Re: [sqlite] Random IOERR_SHORT_READ in sqlite3_prepare_v2

[Martin Raiber]

On 16.09.2016 00:53 Simon Slavin wrote:
> On 15 Sep 2016, at 11:38pm, Martin Raiber <mar...@urbackup.org> wrote:
>
>> There are two instances in the program where the sqlite database file is
>> opened
>> and closed outside of sqlite3 (to backup the database file and to sync
>> it before
>> checkpointing). This clears away the posix locks on the database files.
>> This does
>> not cause problems unless another process accesses the database file.
>> For instance
>> it deletes the wal file while it is still in use.
> Do you mean that the process makes an SQLite call which deletes the WAL file, 
> or that it deletes the WAL file using a file-handling call ?
>
> It should be impossible for a SQLite call to delete a WAL file while it's in 
> use.  The only times I've seen this done are when a SQLite database was open 
> by two different computers, one accessing it on a local disk and the other 
> accessing it across a network using a SMB share.  This, naturally, messes up 
> multi-access filehandling.

The program opens the database file with fd =
open("/path/to/database/file", ...) and then closes it with close(fd)
using the OS file api. The close() clears the posix file locks of the
process in the database file (that is all posix file locks of all open
connections in the process). The sqlite command line tool is able to get
an exclusive lock on quitting, checkpoints and deletes the wal file
which later causes the IO errors.


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

Re: [sqlite] Random IOERR_SHORT_READ in sqlite3_prepare_v2

[Martin Raiber]

After getting some additional information, namely that the users are
also using the
sqlite3 command line tool to read data from the database, I think I
found out how
this issue occurs.

There are two instances in the program where the sqlite database file is
opened
and closed outside of sqlite3 (to backup the database file and to sync
it before
checkpointing). This clears away the posix locks on the database files.
This does
not cause problems unless another process accesses the database file.
For instance
it deletes the wal file while it is still in use. This causes the error
messages below.

Solution seems to be to not close the database file after opening it and
to reuse
the file handle (on Linux).

On 14.09.2016 13:05 Martin Raiber wrote:
> Hi,
>
> there have been three reports by users using my software of SQLite
> 3.12.0 returning SQLITE_IOERR and logging a SQLITE_IOERR_SHORT_READ
> (522). Specifically:
>
> 2016-09-12 04:37:04: WARNING: SQLite: disk I/O error errorcode: 522
> 2016-09-12 04:37:04: ERROR: Error preparing Query [PRAGMA cache_size =
> -2048]: disk I/O error
>
> One instance was on FreeBSD where I thought it could be caused by ZFS.
> The other two instances are on Linux now. On FreeBSD the issue was
> "fixed" by repeating the prepare after it failed with an IO-error.
>
> One user has captured an strace. I cannot actually see the short read,
> though: https://forums.urbackup.org/t/urbackup-crashing/2402/8
>
> Environment:
>
> * Databases are in WAL journal mode
> * synchronous=NORMAL
> * wal_autocheckpoint is OFF. Checkpointing is done in a separate thread
> with PRAGMA wal_checkpoint(PASSIVE) and wal_checkpoint(TRUNCATE) if the
> WAL file is bigger than a certain size
>
> Thanks for any help!
>
> Regards,
> Martin
>
>
>
> ___
> 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] Potential small incompatibility 3.15 -> 3.16

[Martin Raiber]

Hi,

with 3.15:

sqlite> PRAGMA quick_check;
integrity_check
ok

with 3.16:

sqlite> PRAGMA quick_check;
quick_check
ok

The second one is more intuitive, but existing applications may use the
first column name.

Regards,
Martin Raiber


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

Re: [sqlite] Enabling MMAP in Android

[Martin Raiber]

On 12.12.2017 19:47 Simon Slavin wrote:
> On 12 Dec 2017, at 6:27pm, Jens Alfke  wrote:
>
>> On Dec 12, 2017, at 5:46 AM, Simon Slavin  wrote:
>>> Before you answer that question, you should know that both Windows and 
>>> macOS have been proved to have serious bugs in their memory mapping code.
>> This has been brought up several times recently, but I’ve never seen any 
>> details given about exactly what was wrong with macOS’s mmap implementation. 
>> Does anyone have a pointer to authoritative information about this?
> See this thread:
>
> 
>
> This is the thread which led to memory mapping being disabled for writing on 
> macOS, as discussed here:
>
> 

There might also be a mmap bug in the Android 7.0 fuse layer:
https://www.mail-archive.com/openldap-its@openldap.org/msg10970.html


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

Re: [sqlite] Many ML emails going to GMail's SPAM

[Martin Raiber]

On 21.11.2017 17:30 John McKown wrote:
> On Tue, Nov 21, 2017 at 10:27 AM, Drago, William @ CSG - NARDA-MITEQ <
> william.dr...@l3t.com> wrote:
>
>>> I really need to come up with an alternative to the mailing list.
>>> Perhaps some kind of forum system.  Suggestions are welcomed.
>>> --
>>> D. Richard Hipp
>>> d...@sqlite.org
>> Please, not a forum. The email list is instant, dynamic, and convenient. I
>> don't think checking into a forum to stay current with the brisk activity
>> here is very practical or appealing.
> ​I completely agree. The problem with a forum is mainly that it is not _a_
> forum. It is a forum per list. Which means I spend way too much time
> "polling" 8 to 10 web "forums" during the day just to see if anybody has
> said anything of interest.

I am using Discourse as community forum and I cannot really see any
downside to that except for the increased server requirements.
Individuals who want to use it like a mailing list still can do that
(enable mailing list mode). They have a FAQ wrt. to cos/prons mailing
list: https://meta.discourse.org/t/discourse-vs-email-mailing-lists/54298

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

Re: [sqlite] Online backup of in memory database

[Martin Raiber]

On 06.10.2019 22:02 Keith Medcalf wrote:
> On Sunday, 6 October, 2019 13:03, Kadirk  wrote:
>
>> We already have an application specific WAL file, sqlite updates +
>> application data is in this WAL file. We are taking snapshot of sqlite +
>> application data to the disk to truncate WAL file, then we can rebuild
>> latest state whenever needed (after restart etc.)
>> We are evaluating sqlite in memory because response time is critical. We
>> target less than ~30 microseconds per query/update for sqlite itself
>> (Insert or selects are like 256 bytes to 10 kb). I tried sqlite on disk 
>> but there were 50+ milliseconds hiccups which might be expected as file 
>> IO overhead is quite high.
>> I expect there might be a way to take backup of sqlite in memory while
>> updates are still being processed (as in on disk online backup). Maybe
>> something like copy on write memory for that?
>> Our data on sqlite is around 10 gb~, so using serialize interface doesn't
>> look possible. If I'm correct, this interface will allocate continuous
>> space for all data, then copy into it. This will lead out of memory 
>> issues + 10 gb copy latency.
> I think you are barking up the wrong tree.  Why do you not simply process the 
> updates against both databases (the in memory transient copy and the on disk 
> persistent one).
>
Well, as for copy-on-write. Do it like redis and fork() the process then
persist the database in the forked process. Problem is if you are using
threads...

Or use a redis+sqlite combination like
https://github.com/RedBeardLab/rediSQL

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

Re: [sqlite] more efficient JSON encoding: idle musing

[Martin Raiber]

On 21.02.2020 15:03 Richard Hipp wrote:
> On 2/21/20, Wout Mertens  wrote:
>> The idea is that upon storing the JSON
>> data, the JSON1 extension parses it, extracts the layouts recursively,
>> stores them when they are not known yet, and then only stores the
>> values in the binary format with the layout identifiers.
> I experimented with a number of similar ideas for storing JSON when I
> was first designing the JSON components for SQLite.  I was never able
> to find anything that was as fast or as compact as just storing the
> original JSON text.  But I could have overlooked something.  If you
> have example code for a mechanism that is more space efficient and/or
> faster, please share it with us.

If you want to be as space efficient as possible, look into succinct
data structures. The balanced parenthesis representation of a tree can
be stored in a bit vector (each node 2 bits) and there are (succinct)
index structures to query that efficiently.

See e.g. https://core.ac.uk/download/pdf/81941172.pdf and
https://github.com/simongog/sdsl-lite/blob/master/include/sdsl/bp_support_g.hpp
as an implementation. Making this work with JSON would be a lot of work,
though, I guess.

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