Re: [sqlite] Conflict between snapshots and checkpoints
> I have a concern, because this turns automatic checkpointing into a mortal danger for all snapshots. This sentence may be false. Automatic snapshots are PASSIVE, and I'm not sure passive checkpoints invalidate snapshots returned by sqlite3_snapshot_get(). But i'm not sure, and I would appreciate a confirmation! Thanks is advance, Gwendal Roué On Thu, Sep 26, 2019 at 6:13 PM Gwendal Roué wrote: > Hello, > > The documentation for sqlite3_snapshot_open() [1] says: > > > A call to sqlite3_snapshot_open() will fail to open if the specified > snapshot has been overwritten by a checkpoint. > > And indeed I am able to create a snapshot with sqlite3_snapshot_get() [2], > then run checkpoints with sqlite3_wal_checkpoint_v2() [3], and finally have > sqlite3_snapshot_open() fail. > > I have a concern, because this turns automatic checkpointing into a mortal > danger for all snapshots. As soon as a checkpoint is performed, snapshots > are invalidated, and whoever is relying on them has lost the database state > he's interested into. > > My question is: is it possible to prevent checkpoints from completing > successfully when a snapshot is alive? > > I know this is possible with a "hand-made checkpoint", made of a distinct > connection which has started a deferred transaction and has performed an > initial read. Such hand-made checkpoint has SQLITE_CHECKPOINT_RESTART and > SQLITE_CHECKPOINT_TRUNCATE fail, and are still able to access their > precious database state after a SQLITE_CHECKPOINT_PASSIVE or a > SQLITE_CHECKPOINT_FULL. > > The behavior of those "hand-made checkpoint" matches well the needs of > users who want to use WAL without thinking too much about it: they do not > disable automatic checkpointing, and are guaranteed with a stable access to > a given database state as long as they need it. > > I was wondering if such a behavior is possible with snapshots returned > from sqlite3_snapshot_get(). > > If it is not, then I humbly suggest that this feature would be added, and > am willing to listen to the opinion of SQLite experts on this subject. > > Regards, > Gwendal Roué > > [1] https://www.sqlite.org/c3ref/snapshot_open.html > [2] https://www.sqlite.org/c3ref/snapshot_get.html > [3] https://www.sqlite.org/c3ref/wal_checkpoint_v2.html > > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 "client" is remote from the "server" because the > "client" and "server" use some sort of IPC mechanism (of which a network is > an example) so that the "client" can send commands to and receive responses > from the "server". > > Well, obviously. “Client/server” means databases like MySQL or Oracle. No > one would run those with the server using a networked file system. > Haha, you'd be surprised! https://blogs.msdn.microsoft.com/varund/2010/09/02/create-a-sql-server-database-on-a-network-shared-drive/ -Rowan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.30.0 in about two weeks.
Hello, I have noticed that security CVE (https://nvd.nist.gov/vuln/detail/CVE-2019-16168) has been submitted on SQLite. As far as I can see the patch was submitted to the trunk. Will CVE patch be included in the 3.30.0? Regards, Alex -Original Message- From: sqlite-users On Behalf Of Richard Hipp Sent: Thursday, September 26, 2019 9:26 PM To: General Discussion of SQLite Database ; sqlite-dev Subject: [sqlite] SQLite version 3.30.0 in about two weeks. Our plan is to release SQLite version 3.30.0 in about two weeks - on or about 2019-10-10. Please review the change log https://www.sqlite.org/draft/releaselog/3_30_0.html And perhaps download, build, and test the latest snapshot. Please let us know if you encounter any problems or concerns. -- 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 - Intel Israel (74) Limited This e-mail and any attachments may contain confidential material for the sole use of the intended recipient(s). Any review or distribution by others is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] localtime on current_time differs from localtime on current_timestamp
On 26/9/19 11:51 PM, Keith Medcalf wrote: > So, when you ask for time(current_time, 'localtime') you are saying to > get the current utc datetime, discard the date part, then assume that > the date part is 2000-01-01 with that time, then compute the > "localtime" for that UTC time, and then discard the date part and > return the result. So the answer is correct, but for 2000-01-01 > according to the whims of the politicians who set the localtime rules > at that time (and whether or not your OS knows how to compute that, > Windows does not, for example). I must remember that phrase, "whim of the politicians". Such a beautiful hand-wavy description of perfectly mundane issues like daylight saving time. Given the magnitude of the difference (1 hour) and the seasonal differences between Jan 1 and the date of the original question (Sep 26), I'd surmise this was the most likely cause. -- Best Regards, Adrian ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.30.0 in about two weeks.
"Still says" due to 3_30_0.html is in a draft, I would assume. On Thu, Sep 26, 2019 at 3:31 PM Olivier Mascia wrote: > > Le 26 sept. 2019 à 20:26, Richard Hipp a écrit : > > > > Our plan is to release SQLite version 3.30.0 in about two weeks - on > > or about 2019-10-10. Please review the change log > > > >https://www.sqlite.org/draft/releaselog/3_30_0.html > > > > And perhaps download, build, and test the latest snapshot. Please let > > us know if you encounter any problems or concerns. > > Just reading for now. https://www.sqlite.org/draft/releaselog/3_30_0.html > says: > > • Enhance the RBU extension to support indexes on expressions. > > While https://www.sqlite.org/draft/rbu.html still says: > > • The target database may not contain indexes on expressions. > > — > Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten > Grüßen, > Olivier Mascia > > ___ > 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] SQLite version 3.30.0 in about two weeks.
> Le 26 sept. 2019 à 20:26, Richard Hipp a écrit : > > Our plan is to release SQLite version 3.30.0 in about two weeks - on > or about 2019-10-10. Please review the change log > >https://www.sqlite.org/draft/releaselog/3_30_0.html > > And perhaps download, build, and test the latest snapshot. Please let > us know if you encounter any problems or concerns. Just reading for now. https://www.sqlite.org/draft/releaselog/3_30_0.html says: • Enhance the RBU extension to support indexes on expressions. While https://www.sqlite.org/draft/rbu.html still says: • The target database may not contain indexes on expressions. — Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten Grüßen, Olivier Mascia ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.30.0 in about two weeks.
Looks good. One request that popped to mind while looking at this: Would you expand the documentation on the index_info and index_xinfo pragmas to include what they return when run on an expression index? -Original Message- From: sqlite-users On Behalf Of Richard Hipp Sent: Thursday, September 26, 2019 2:26 PM To: General Discussion of SQLite Database ; sqlite-dev Subject: [sqlite] SQLite version 3.30.0 in about two weeks. Our plan is to release SQLite version 3.30.0 in about two weeks - on or about 2019-10-10. Please review the change log https://www.sqlite.org/draft/releaselog/3_30_0.html And perhaps download, build, and test the latest snapshot. Please let us know if you encounter any problems or concerns. -- 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite version 3.30.0 in about two weeks.
Our plan is to release SQLite version 3.30.0 in about two weeks - on or about 2019-10-10. Please review the change log https://www.sqlite.org/draft/releaselog/3_30_0.html And perhaps download, build, and test the latest snapshot. Please let us know if you encounter any problems or concerns. -- 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
[sqlite] Conflict between snapshots and checkpoints
Hello, The documentation for sqlite3_snapshot_open() [1] says: > A call to sqlite3_snapshot_open() will fail to open if the specified snapshot has been overwritten by a checkpoint. And indeed I am able to create a snapshot with sqlite3_snapshot_get() [2], then run checkpoints with sqlite3_wal_checkpoint_v2() [3], and finally have sqlite3_snapshot_open() fail. I have a concern, because this turns automatic checkpointing into a mortal danger for all snapshots. As soon as a checkpoint is performed, snapshots are invalidated, and whoever is relying on them has lost the database state he's interested into. My question is: is it possible to prevent checkpoints from completing successfully when a snapshot is alive? I know this is possible with a "hand-made checkpoint", made of a distinct connection which has started a deferred transaction and has performed an initial read. Such hand-made checkpoint has SQLITE_CHECKPOINT_RESTART and SQLITE_CHECKPOINT_TRUNCATE fail, and are still able to access their precious database state after a SQLITE_CHECKPOINT_PASSIVE or a SQLITE_CHECKPOINT_FULL. The behavior of those "hand-made checkpoint" matches well the needs of users who want to use WAL without thinking too much about it: they do not disable automatic checkpointing, and are guaranteed with a stable access to a given database state as long as they need it. I was wondering if such a behavior is possible with snapshots returned from sqlite3_snapshot_get(). If it is not, then I humbly suggest that this feature would be added, and am willing to listen to the opinion of SQLite experts on this subject. Regards, Gwendal Roué [1] https://www.sqlite.org/c3ref/snapshot_open.html [2] https://www.sqlite.org/c3ref/snapshot_get.html [3] https://www.sqlite.org/c3ref/wal_checkpoint_v2.html ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] localtime on current_time differs from localtime on current_timestamp
On Thursday, 26 September, 2019 05:33, PALAMARA Alain
wrote:
>I'm quite new to sqlite and I'm surprised about the result I got from
>executing time(current_time, 'localtime') function.
>I use the sqlite3 command line (version 3.29.0) on Windows 7 and I tried
>to get my local time using this simple
>statement:
>select time(current_time, 'localtime');
>and the result was wrong. I did tried with:
> select time(current_timestamp, 'localtime');
>and the result was right.
>Is that behavior normal or is that a bug?
That would be expected behaviour, depending on the whim of politicians for the
last 20 years at your location.
CURRENT_TIME is syntactic sugar (simply a different spelling for) time('now'),
and CURRENT_TIMESTAMP is syntactic sugar for datetime('now'). So the former
(CURRENT_TIME) only yields the time part of the current datetime, whereas
CURRENT_TIMESTAMP returns the entire current date and time (both in UT1).
The function time(...) is the same as the function datetime(...) except that it
only returns the time part of the datetime and discards the date part (and the
date(...) function takes exactly the same but only returns the date part,
discarding the time part). Nevertheless, the datetime(...) functions require a
whole date and time on which to operate. If you do not provide one, then one
is provided for you. If you omit the time part, then the default time part is
00:00:00.000. If you omit the date part, then the default date is 2000-01-01.
So, when you ask for time(current_time, 'localtime') you are saying to get the
current utc datetime, discard the date part, then assume that the date part is
2000-01-01 with that time, then compute the "localtime" for that UTC time, and
then discard the date part and return the result. So the answer is correct,
but for 2000-01-01 according to the whims of the politicians who set the
localtime rules at that time (and whether or not your OS knows how to compute
that, Windows does not, for example).
So what you really want is to use the datetime functions directly, as in:
select time('now', 'localtime');
which will give you the date and time in UTC, convert it into 'localtime', and
then discard the date part, returning only the time part.
current_date, current_time, current_timestamp are merely alternate spellings
for date('now'), time('now'), and datetime('now') respectively.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] The LIKE operator and Swift
You can't have a variable inside a pattern. Use like '%' || ? || '%'
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Daniel Odom
Gesendet: Donnerstag, 26. September 2019 15:26
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] The LIKE operator and Swift
I am just now getting around to learning Swift and XCode. I am having a problem
with 'LIKE'. When I do this:
let queryString = "select name, phone, street, city, state from phone where
name like '%?%'"
And then this: if sqlite3_bind_text(stmt, 1, name, -1, SQLITE_TRANSIENT) !=
SQLITE_OK {do whatever}
I get an error "column index out of range". The rest of the code is fine. When
I do this:
let queryString = "select name, phone, street, city, state from phone where
name = ?"
everything works just fine. What am I missing?
___
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] The LIKE operator and Swift
I am just now getting around to learning Swift and XCode. I am having a
problem with 'LIKE'. When I do this:
let queryString = "select name, phone, street, city, state from phone
where name like '%?%'"
And then this: if sqlite3_bind_text(stmt, 1, name, -1, SQLITE_TRANSIENT)
!= SQLITE_OK {do whatever}
I get an error "column index out of range". The rest of the code is
fine. When I do this:
let queryString = "select name, phone, street, city, state from phone
where name = ?"
everything works just fine. What am I missing?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] localtime on current_time differs from localtime on current_timestamp
Hello,
I'm quite new to sqlite and I'm surprised about the result I got from executing
time(current_time, 'localtime') function.
I use the sqlite3 command line (version 3.29.0) on Windows 7 and I tried to get
my local time using this simple
statement:
select time(current_time, 'localtime');
and the result was wrong. I did tried with:
select time(current_timestamp, 'localtime');
and the result was right.
Is that behavior normal or is that a bug?
To reproduce it, here is the output of my complete test:
sqlite>select time(current_timestamp), time(current_time), time('now');
time(current_timestamp) time(current_time) time('now')
--- -- ---
10:47:40 10:47:4010:47:40
sqlite>select time(current_timestamp, 'localtime'), time(current_time,
'localtime'), time('now','localtime');
time(current_timestamp, 'localtime') time(current_time, 'localtime')
time('now','localtime')
---
---
12:47:44 11:47:44 12:47:44
As you can see, the localtime for current_time is one hour shifted from the two
others despite the "raw" results are the same.
Regards,
Alain
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] crash at VCRUNTIME140D!MoveSmall
Hi,
I have a crash at sqlite (encryption extension). Here is code:
sequence_t SQLiteDataFile::lastSequence(const string& keyStoreName)
const {
sequence_t seq = 0;
compile(_getLastSeqStmt, "SELECT lastSeq FROM kvmeta WHERE name=?");
UsingStatement u(_getLastSeqStmt);
_getLastSeqStmt->bindNoCopy(1, keyStoreName);
if (_getLastSeqStmt->executeStep()) <-- crash here
seq = (int64_t)_getLastSeqStmt->getColumn(0);
return seq;
}
It happens only once and I still can't reproduce. Here is brief stack trace
(detailed stacktrace in the attachment).
0004`ce1f9ba8 7ff7`6f4efcd4 : 01a7`cab470c0
`0021 ` ` :
VCRUNTIME140D!MoveSmall+0x68
0004`ce1f9bb0 7ff7`6f5247b9 : 01a7`cb51d900
` ` ` :
CortexService!sqlite3VdbeExec+0x3b54
0004`ce1faa50 7ff7`6f499a43 : 01a7`cb51d900
` ` ` :
CortexService!sqlite3Step+0x1d9
0004`ce1faa90 7ff7`6f653002 : 01a7`cb51d900
` ` ` :
CortexService!sqlite3_step+0x93
0004`ce1fab00 7ff7`6f5fdcd5 : 01a7`cb675f90
01a7`0001 01a7`cb3e4c00 ` :
CortexService!SQLite::Statement::executeStep+0x72
0004`ce1fabc0 7ff7`6f65d083 : 01a7`cb301d50
01a7`cb3e4c00 ` ` :
CortexService!litecore::SQLiteDataFile::lastSequence+0xc5
0004`ce1fac60 7ff7`6f65d773 : 01a7`cb3e4bf0
01a7`0005 01a7`cbedb4d4 `000c :
FAULTING_SOURCE_LINE: f:\dd\vctools\crt\vcruntime\src\string\amd64\memcpy.asm
FAULTING_SOURCE_FILE: f:\dd\vctools\crt\vcruntime\src\string\amd64\memcpy.asm
FAULTING_SOURCE_LINE_NUMBER: 226
FAULTING_SOURCE_CODE:
No source found for 'f:\dd\vctools\crt\vcruntime\src\string\amd64\memcpy.asm'
Can you please give some suggestions when this can happen and maybe
how to fix it?
Thanks in advance!
Tham
Microsoft (R) Windows Debugger Version 10.0.18972.1001 AMD64
Copyright (c) Microsoft Corporation. All rights reserved.
Loading Dump File
[C:\Users\Tham\Downloads\24ff4711-7801-422b-bbfb-79915bec7d22.dmp]
User Mini Dump File: Only registers, stack and portions of memory are available
* Path validation summary **
Response Time (ms) Location
Deferred srv*
DBGHELP: Symbol Search Path:
cache*;SRV*https://msdl.microsoft.com/download/symbols
Symbol search path is: srv*
Executable search path is:
Windows 10 Version 14393 MP (4 procs) Free x64
Product: WinNt, suite: SingleUserTS
10.0.14393.206 (rs1_release.160915-0644)
Machine Name:
Debug session time: Tue Sep 17 16:55:19.000 2019 (UTC + 7:00)
System Uptime: not available
Process Uptime: 0 days 0:13:14.000
...
This dump file has an exception of interest stored in it.
The stored exception information can be accessed via .ecxr.
(2944.1f4): Access violation - code c005 (first/second chance not available)
For analysis of this file, run !analyze -v
SYMSRV: BYINDEX: 0x1
C:\ProgramData\Dbg\sym
ntdll.dll
5825887F1d1000
SYMSRV: PATH: C:\ProgramData\Dbg\sym\ntdll.dll\5825887F1d1000\ntdll.dll
SYMSRV: RESULT: 0x
DBGHELP: C:\ProgramData\Dbg\sym\ntdll.dll\5825887F1d1000\ntdll.dll - OK
DBGENG: C:\ProgramData\Dbg\sym\ntdll.dll\5825887F1d1000\ntdll.dll - Mapped
image memory
SYMSRV: BYINDEX: 0x2
C:\ProgramData\Dbg\sym
ntdll.pdb
4E0AC1E0213F4DB5BE3D07B0A54565C01
SYMSRV: PATH:
C:\ProgramData\Dbg\sym\ntdll.pdb\4E0AC1E0213F4DB5BE3D07B0A54565C01\ntdll.pdb
SYMSRV: RESULT: 0x
DBGHELP: ntdll - public symbols
C:\ProgramData\Dbg\sym\ntdll.pdb\4E0AC1E0213F4DB5BE3D07B0A54565C01\ntdll.pdb
SYMSRV: BYINDEX: 0x3
C:\ProgramData\Dbg\sym
KERNELBASE.dll
582588E621d000
SYMSRV: PATH:
C:\ProgramData\Dbg\sym\KERNELBASE.dll\582588E621d000\KERNELBASE.dll
SYMSRV: RESULT: 0x
DBGHELP: C:\ProgramData\Dbg\sym\KERNELBASE.dll\582588E621d000\KERNELBASE.dll -
OK
Map C:\Windows\System32\KERNELBASE.dll:
Image region 400:cf200 does not fit in mapping
DBGENG: KERNELBASE.dll - Partial symbol image load missing image info
DBGHELP: Module is not fully loaded into memory.
DBGHELP: Searching for symbols using debugger-provided data.
SYMSRV: BYINDEX: 0x4
C:\ProgramData\Dbg\sym
kernelbase.pdb
2797F41DA1EB4D39A25C73C95BC7C7901
SYMSRV: PATH:
C:\ProgramData\Dbg\sym\kernelbase.pdb\2797F41DA1EB4D39A25C73C95BC7C7901\kernelbase.pdb
SYMSRV: RESULT: 0x
*** WARNING: Unable to verify timestamp for KERNELBASE.dll
DBGHELP: KERNELBASE - public symbols
C:\ProgramData\Dbg\sym\kernelbase.pdb\2797F41DA1EB4D39A25C73C95BC7C7901\kernelbase.pdb
ntdll!NtWaitForSingleObject+0x14:
7ff
