Re: [sqlite] Safe saving of in-memory db to disk file
On Tue, Jul 16, 2019 at 4:44 AM Adrian Ho wrote: > On 15/7/19 11:25 PM, Nelson, Erik - 2 wrote: > > Dominique Devienne wrote on Monday, July 15, 2019 2:41 AM > >> That's when you reach for virtual tables (and their "virtual indices"). > >> I.e. you keep your data in native data-structures (Boost.MultiIndex in > my case), > >> and just provide a SQLite view of it. Much faster than "pure-in-Memory" > with > >> SQLite-managed pager-backed B-tree tables. Steep learning curve, > especially > >> for the indexing part, but the results are well worth it IMHO. > >> Which can be freely mixed with "real" tables having "real" indexes (in > the in-memory DB). > > > That sounds really intriguing- does it significantly speed up queries > coming in through the SQLite engine? > Or the speed bump is only if accessing from the C++-native side? > It's been years since benchmarking this, but you can get 5x faster in some use cases (compared to in-memory DB, i.e. no disk IO at all, assuming no swapping of course). See, data in SQLite is "packed" into pages, in a compact format, that must be decoded/encoded on all reads/writes. Pages must be copied around. BTrees must be traversed, which means decoding pages again to traverse them. While your vtables reference C++ structs, with fields of "native" values. If your "vtables" are represented by hashes C++ collections, then a by-primary-key index access is super fast, and accessing the fields/columns of the selected row needs no unpacking either. Even full scans are typically faster too. The whole point of virtual tables is to make something outside an actual > SQLite DB accessible via normal SQL queries within SQLite; you'd still > use the normal access methods (pointer deferencing, lookups, etc.) > from the native side. > Right. The vtables just provide another mechanism to access the same data already part of the application and its data model, but unlike native code, it's flexible and "runtime" configurable. You can still always access the native C++ API accessing the same containers and data structures. But as Richard mentions, the amount of C++ code you replace by crafting a non-trivial query is sometimes quite large, and it's often less buggy to write the SQL than the C++. SQLite is fast enough that unless it's performance critical code, performance is not an issue at all. Just look at DRH's own Fossil, and the super complex queries it runs in mere milliseconds or less. Once you have SQLite embedded into your app, it tends to sip through, to all settings/preferences readily available in (typically "real") tables of the in-memory DB. Easily queryable and accessible. And even runtime editable if you allow it (see also "authorizer" in the doc). And you can also use it as your data format, of course, as often mentioned. Even if you "serialize" your whole memory state on each save, it's still efficient. And allows a path forward to one day enable incremental updates of the state as it changes. IMHO, most desktop apps would greatly benefit from in-memory SQLite combined with vtables and custom functions. It's fantastic for debugging and troubleshooting. It's flexible and dynamic, allowing to change behavior or appearance of your app and UI at runtime if you design for it. SQLite is just a wonderful tool. It's not perfect of course, but it's so well designed, so robust and well tested, and so fast when used judiciously, not using it is not an option for me at least :). --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_close() drops wal and shm files despite of other processes holding DB open
Interessting insights! Dan pointed out, that I should check the locks on the DB files and report that information back. Unluckily, my 'lsof' just reports PIDs, executable name and open file. It is a Busybox multicall binary. The good thing is: I have 'lslocks' on my system, which gives me exactly the information I need. It turns out, that one of the two main processes doesn't apply a lock at all (still under investigation why). The process, that uses exactly the same procedure to open the DB helds locks on the DB itself and the '-shm' file, the other does not: root:~> lsof |fgrep ecc.db 698 /usr/bin/lcd_manager /data/ecc/ecc.db 698 /usr/bin/lcd_manager /data/ecc/ecc.db-wal 698 /usr/bin/lcd_manager /data/ecc/ecc.db-shm 706 /usr/bin/ecc_core /data/ecc/ecc.db 706 /usr/bin/ecc_core /data/ecc/ecc.db-wal 706 /usr/bin/ecc_core /data/ecc/ecc.db-shm root:~> lslocks COMMAND PID TYPE SIZE MODE M START END PATH lcd_manager 698 POSIX 146.2M READ 0 1073741826 1073742335 /data/ecc/ecc.db lcd_manager 698 POSIX 32K READ 0 128 128 /data/ecc/ecc.db-shm root:~> At least, this explains why closing the second process (lcd_manager) will close the DB completely, removing the '-shm' and '-wal' file. I will now investigate, why the first process doesn't acquire the locks. I will report back, when I know more! Andreas Am 11.07.2019 um 18:07 schrieb Andreas Kretzer: > I'm using SQLITE3 (V3.29.0) on an arm embedded linux (2.6.39) on an ext3 > filesystem. > > Several processes hold the DB open and the "-wal" and "-shm" files exist. > if I use 'lsof | fgrep ' I can see all processes having all > three > files open. At least one of the processes uses threads, but every process > has just one single DB connection active which is shared among all > threads. > > The compilation of sqlite3 is done with multithreading in mind: > > sqlite> pragma compile_options; > COMPILER=gcc-6.2.0 > ENABLE_DBSTAT_VTAB > ENABLE_FTS4 > ENABLE_JSON1 > ENABLE_RTREE > ENABLE_STAT3 > ENABLE_STMTVTAB > ENABLE_UNKNOWN_SQL_FUNCTION > ENABLE_UPDATE_DELETE_LIMIT > HAVE_ISNAN > THREADSAFE=1 > > I can check, that the database is threadsafe (mode == 1) and is switched > to WAL-mode. > > So far I never noticed any problems dealing with concurrent updates or so. > The only thing (tested in depth with V3.15.2 and V3.29.0) is when one > process stops and closes the database using sqlite3_close(). This may even > be the sqlite3 CLI. That process closes DB (lsof shows that this > process has > closed its filedescriptors and is not in the listing anymore). Right > at the > next write access to the DB in the still running process (at least I > think that > this is exactly the point) the "-wal" and "-shm" files are removed. > The sqlite3_exec() function still returns SQLITE3_OK on all following > actions, > but 'lsof' reports, that this process has opened the "-wal" and "-shm" > files, > but marked as "deleted". And they are really deleted and none of the > upcoming > DB changes will ever reach the real DB. > > What is wrong? I already checked, that my kernel supports POSIX file > locking > (CONFIG_FILE_LOCKING=yes). What else can I check? Two or more sqlite3 CLI > processes started in parallel don't exhibit this behavior. > > Thanks > > Andreas -- Mit freundlichen Grüßen Andreas Kretzer ETB Electronic Team Beratungs- und Vertriebs GmbH Berliner Straße 8a 15537 Erkner FON +49 3362 889349-12 FAX +49 3362 889349-23 email: a.kret...@etb-electronic.de AG Potsdam HRB 16532; Sitz der Gesellschaft: Am Mellensee Geschäftsführer: Marco Runge, Jürgen Gentzsch ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_close() drops wal and shm files despite of other processes holding DB open
... and here is the solution! I daemonize my programs using daemon (1, 1); If I do that, before I open the DB, then everything is alright, locks are held - fine! In the other case I was first opening the DB to fetch some configuration options from it before I took care of program args and then possibly daemonize, if the "do not run in background"-option is not set. And so, if I use this foreground option, my process wild hold the locks just fine! -> Never use daemon() with open DB. Thanks for the hints to how to have a look at locks and that it is worth taking that look! I think, this problem is solved - I have to change the order of the startup. Maybe I even open the DB, read the configuration and close it again, before opening it again after being daemonized. Andreas Am 16.07.2019 um 12:21 schrieb Andreas Kretzer: > Interessting insights! > > Dan pointed out, that I should check the locks on the DB files and report > that information back. Unluckily, my 'lsof' just reports PIDs, > executable name > and open file. It is a Busybox multicall binary. > > The good thing is: I have 'lslocks' on my system, which gives me exactly > the information I need. It turns out, that one of the two main processes > doesn't apply a lock at all (still under investigation why). The > process, that > uses exactly the same procedure to open the DB helds locks on the DB itself > and the '-shm' file, the other does not: > > root:~> lsof |fgrep ecc.db > 698 /usr/bin/lcd_manager /data/ecc/ecc.db > 698 /usr/bin/lcd_manager /data/ecc/ecc.db-wal > 698 /usr/bin/lcd_manager /data/ecc/ecc.db-shm > 706 /usr/bin/ecc_core /data/ecc/ecc.db > 706 /usr/bin/ecc_core /data/ecc/ecc.db-wal > 706 /usr/bin/ecc_core /data/ecc/ecc.db-shm > root:~> lslocks > COMMAND PID TYPE SIZE MODE M START END PATH > lcd_manager 698 POSIX 146.2M READ 0 1073741826 1073742335 > /data/ecc/ecc.db > lcd_manager 698 POSIX 32K READ 0 128 128 > /data/ecc/ecc.db-shm > root:~> > > At least, this explains why closing the second process (lcd_manager) > will close > the DB completely, removing the '-shm' and '-wal' file. > > I will now investigate, why the first process doesn't acquire the locks. > > I will report back, when I know more! > > Andreas > > Am 11.07.2019 um 18:07 schrieb Andreas Kretzer: >> I'm using SQLITE3 (V3.29.0) on an arm embedded linux (2.6.39) on an ext3 >> filesystem. >> >> Several processes hold the DB open and the "-wal" and "-shm" files exist. >> if I use 'lsof | fgrep ' I can see all processes having all >> three >> files open. At least one of the processes uses threads, but every process >> has just one single DB connection active which is shared among all >> threads. >> >> The compilation of sqlite3 is done with multithreading in mind: >> >> sqlite> pragma compile_options; >> COMPILER=gcc-6.2.0 >> ENABLE_DBSTAT_VTAB >> ENABLE_FTS4 >> ENABLE_JSON1 >> ENABLE_RTREE >> ENABLE_STAT3 >> ENABLE_STMTVTAB >> ENABLE_UNKNOWN_SQL_FUNCTION >> ENABLE_UPDATE_DELETE_LIMIT >> HAVE_ISNAN >> THREADSAFE=1 >> >> I can check, that the database is threadsafe (mode == 1) and is switched >> to WAL-mode. >> >> So far I never noticed any problems dealing with concurrent updates or so. >> The only thing (tested in depth with V3.15.2 and V3.29.0) is when one >> process stops and closes the database using sqlite3_close(). This may even >> be the sqlite3 CLI. That process closes DB (lsof shows that this >> process has >> closed its filedescriptors and is not in the listing anymore). Right >> at the >> next write access to the DB in the still running process (at least I >> think that >> this is exactly the point) the "-wal" and "-shm" files are removed. >> The sqlite3_exec() function still returns SQLITE3_OK on all following >> actions, >> but 'lsof' reports, that this process has opened the "-wal" and "-shm" >> files, >> but marked as "deleted". And they are really deleted and none of the >> upcoming >> DB changes will ever reach the real DB. >> >> What is wrong? I already checked, that my kernel supports POSIX file >> locking >> (CONFIG_FILE_LOCKING=yes). What else can I check? Two or more sqlite3 CLI >> processes started in parallel don't exhibit this behavior. >> >> Thanks >> >> Andreas > -- Mit freundlichen Grüßen Andreas Kretzer ETB Electronic Team Beratungs- und Vertriebs GmbH Berliner Straße 8a 15537 Erkner FON +49 3362 889349-12 FAX +49 3362 889349-23 email: a.kret...@etb-electronic.de AG Potsdam HRB 16532; Sitz der Gesellschaft: Am Mellensee Geschäftsführer: Marco Runge, Jürgen Gentzsch ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: sqlite3_close() drops wal and shm files despite of other processes holding DB open
I think your file locks are getting stranded in the parent process during your daemon() call. When you open the SQLite db, you are opening file descriptors and generating memory state. Accessing the db to read config creates further memory state plus some file level locks. Which will persist until you commit the transaction. The first step in creating a daemon is to fork() from the parent process. This inherits the memory state and open file descriptors BUT NOT the file record locks. If you continue running the same image in the child process, SQLite will THINK it still has those locks and/or is still within a transaction. The parent process then needs to terminate, releasing the locks it still had. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Andreas Kretzer Sent: Dienstag, 16. Juli 2019 12:40 To: sqlite-users@mailinglists.sqlite.org Subject: [EXTERNAL] Re: [sqlite] sqlite3_close() drops wal and shm files despite of other processes holding DB open ... and here is the solution! I daemonize my programs using daemon (1, 1); If I do that, before I open the DB, then everything is alright, locks are held - fine! In the other case I was first opening the DB to fetch some configuration options from it before I took care of program args and then possibly daemonize, if the "do not run in background"-option is not set. And so, if I use this foreground option, my process wild hold the locks just fine! -> Never use daemon() with open DB. Thanks for the hints to how to have a look at locks and that it is worth taking that look! I think, this problem is solved - I have to change the order of the startup. Maybe I even open the DB, read the configuration and close it again, before opening it again after being daemonized. Andreas Am 16.07.2019 um 12:21 schrieb Andreas Kretzer: > Interessting insights! > > Dan pointed out, that I should check the locks on the DB files and > report that information back. Unluckily, my 'lsof' just reports PIDs, > executable name and open file. It is a Busybox multicall binary. > > The good thing is: I have 'lslocks' on my system, which gives me > exactly the information I need. It turns out, that one of the two main > processes doesn't apply a lock at all (still under investigation why). > The process, that uses exactly the same procedure to open the DB helds > locks on the DB itself and the '-shm' file, the other does not: > > root:~> lsof |fgrep ecc.db > 698/usr/bin/lcd_manager/data/ecc/ecc.db > 698/usr/bin/lcd_manager/data/ecc/ecc.db-wal > 698/usr/bin/lcd_manager/data/ecc/ecc.db-shm > 706/usr/bin/ecc_core/data/ecc/ecc.db > 706/usr/bin/ecc_core/data/ecc/ecc.db-wal > 706/usr/bin/ecc_core/data/ecc/ecc.db-shm root:~> lslocks > COMMAND PID TYPE SIZE MODE M STARTEND PATH > lcd_manager 698 POSIX 146.2M READ 0 1073741826 1073742335 > /data/ecc/ecc.db lcd_manager 698 POSIX32K READ 0128 > 128 /data/ecc/ecc.db-shm root:~> > > At least, this explains why closing the second process (lcd_manager) > will close the DB completely, removing the '-shm' and '-wal' file. > > I will now investigate, why the first process doesn't acquire the locks. > > I will report back, when I know more! > > Andreas > > Am 11.07.2019 um 18:07 schrieb Andreas Kretzer: >> I'm using SQLITE3 (V3.29.0) on an arm embedded linux (2.6.39) on an >> ext3 filesystem. >> >> Several processes hold the DB open and the "-wal" and "-shm" files exist. >> if I use 'lsof | fgrep ' I can see all processes having >> all three files open. At least one of the processes uses threads, but >> every process has just one single DB connection active which is >> shared among all threads. >> >> The compilation of sqlite3 is done with multithreading in mind: >> >> sqlite> pragma compile_options; >> COMPILER=gcc-6.2.0 >> ENABLE_DBSTAT_VTAB >> ENABLE_FTS4 >> ENABLE_JSON1 >> ENABLE_RTREE >> ENABLE_STAT3 >> ENABLE_STMTVTAB >> ENABLE_UNKNOWN_SQL_FUNCTION >> ENABLE_UPDATE_DELETE_LIMIT >> HAVE_ISNAN >> THREADSAFE=1 >> >> I can check, that the database is threadsafe (mode == 1) and is >> switched to WAL-mode. >> >> So far I never noticed any problems dealing with concurrent updates or so. >> The only thing (tested in depth with V3.15.2 and V3.29.0) is when one >> process stops and closes the database using sqlite3_close(). This may >> even be the sqlite3 CLI. That process closes DB (lsof shows that this >> process has closed its filedescriptors and is not in the listing >> anymore). Right at the next write access to the DB in the still >> running process (at least I think that this is exactly the point) the >> "-wal" and "-shm" files are removed. >> The sqlite3_exec() function still returns SQLITE3_OK on all following >> actions, but 'lsof' reports, that this process has opened the "-wal" >> and "-shm" >> files, >> but marked as "deleted". And they are really delete
Re: [sqlite] [EXTERNAL] Re: sqlite3_close() drops wal and shm files despite of other processes holding DB open
Sure - I loose my locks when calling daemon(). I already have changed my program to close and re-open the DB when daemonizing. I was not aware, that daemon() by itself would do a fork(). There is no explicit fork() in my program... Anyway, it is now clear what should be avoided: carrying an open DB over the daemon() call. Maybe it would be worth noting that in the section about "How to corrupt the DB". B.T.W.: You are right about the fork(). I just read the manual page for daemon(3) again and there is stated: RETURN VALUE (This function forks, and if the fork(2) succeeds, the parent calls _exit(2), so that further errors are seen by the child only.) On success (...) So it is clear, what happens here. But it was not obvious to me, that daemon() uses fork(). I was aware, that a fork() would be a good way to ruin the DB, but I thought I was not doing that :-D Anyway, thank you all for your support and the helpful hints. SQLite is a real cool and handy software - right what I needed. Andreas Am 16.07.2019 um 13:19 schrieb Hick Gunter: > I think your file locks are getting stranded in the parent process during > your daemon() call. > > When you open the SQLite db, you are opening file descriptors and generating > memory state. > Accessing the db to read config creates further memory state plus some file > level locks. > Which will persist until you commit the transaction. > > The first step in creating a daemon is to fork() from the parent process. > This inherits the memory state and open file descriptors BUT NOT the file > record locks. > > If you continue running the same image in the child process, SQLite will > THINK it still has those locks and/or is still within a transaction. > > The parent process then needs to terminate, releasing the locks it still had. > > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On > Behalf Of Andreas Kretzer > Sent: Dienstag, 16. Juli 2019 12:40 > To: sqlite-users@mailinglists.sqlite.org > Subject: [EXTERNAL] Re: [sqlite] sqlite3_close() drops wal and shm files > despite of other processes holding DB open > > ... and here is the solution! > > I daemonize my programs using > > daemon (1, 1); > > If I do that, before I open the DB, then everything is alright, locks are > held - fine! > > In the other case I was first opening the DB to fetch some configuration > options from it before I took care of program args and then possibly > daemonize, if the "do not run in background"-option is not set. > > And so, if I use this foreground option, my process wild hold the locks just > fine! -> Never use daemon() with open DB. > > Thanks for the hints to how to have a look at locks and that it is worth > taking that look! > > I think, this problem is solved - I have to change the order of the startup. > Maybe I even open the DB, read the configuration and close it again, before > opening it again after being daemonized. > > Andreas > > Am 16.07.2019 um 12:21 schrieb Andreas Kretzer: >> Interessting insights! >> >> Dan pointed out, that I should check the locks on the DB files and >> report that information back. Unluckily, my 'lsof' just reports PIDs, >> executable name and open file. It is a Busybox multicall binary. >> >> The good thing is: I have 'lslocks' on my system, which gives me >> exactly the information I need. It turns out, that one of the two main >> processes doesn't apply a lock at all (still under investigation why). >> The process, that uses exactly the same procedure to open the DB helds >> locks on the DB itself and the '-shm' file, the other does not: >> >> root:~> lsof |fgrep ecc.db >> 698/usr/bin/lcd_manager/data/ecc/ecc.db >> 698/usr/bin/lcd_manager/data/ecc/ecc.db-wal >> 698/usr/bin/lcd_manager/data/ecc/ecc.db-shm >> 706/usr/bin/ecc_core/data/ecc/ecc.db >> 706/usr/bin/ecc_core/data/ecc/ecc.db-wal >> 706/usr/bin/ecc_core/data/ecc/ecc.db-shm root:~> lslocks >> COMMAND PID TYPE SIZE MODE M STARTEND PATH >> lcd_manager 698 POSIX 146.2M READ 0 1073741826 1073742335 >> /data/ecc/ecc.db lcd_manager 698 POSIX32K READ 0128 >> 128 /data/ecc/ecc.db-shm root:~> >> >> At least, this explains why closing the second process (lcd_manager) >> will close the DB completely, removing the '-shm' and '-wal' file. >> >> I will now investigate, why the first process doesn't acquire the locks. >> >> I will report back, when I know more! >> >> Andreas >> >> Am 11.07.2019 um 18:07 schrieb Andreas Kretzer: >>> I'm using SQLITE3 (V3.29.0) on an arm embedded linux (2.6.39) on an >>> ext3 filesystem. >>> >>> Several processes hold the DB open and the "-wal" and "-shm" files exist. >>> if I use 'lsof | fgrep ' I can see all processes having >>> all three files open. At least one of the processes uses threads, but >>> every process has just one single DB connection activ
Re: [sqlite] sqlite3_close() drops wal and shm files despite of other processes holding DB open
Interessting insights! Dan pointed out, that I should check the locks on the DB files and report that information back. Unluckily, my 'lsof' just reports PIDs, executable name and open file. It is a Busybox multicall binary. The good thing is: I have 'lslocks' on my system, which gives me exactly the information I need. It turns out, that one of the two main processes doesn't apply a lock at all (still under investigation why). The process, that uses exactly the same procedure to open the DB helds locks on the DB itself and the '-shm' file, the other does not: root:~> lsof |fgrep ecc.db 698 /usr/bin/lcd_manager /data/ecc/ecc.db 698 /usr/bin/lcd_manager /data/ecc/ecc.db-wal 698 /usr/bin/lcd_manager /data/ecc/ecc.db-shm 706 /usr/bin/ecc_core /data/ecc/ecc.db 706 /usr/bin/ecc_core /data/ecc/ecc.db-wal 706 /usr/bin/ecc_core /data/ecc/ecc.db-shm root:~> lslocks COMMAND PID TYPE SIZE MODE M START END PATH lcd_manager 698 POSIX 146.2M READ 0 1073741826 1073742335 /data/ecc/ecc.db lcd_manager 698 POSIX 32K READ 0 128 128 /data/ecc/ecc.db-shm root:~> At least, this explains why closing the second process (lcd_manager) will close the DB completely, removing the '-shm' and '-wal' file. I will now investigate, why the first process doesn't acquire the locks. I will report back, when I know more! Andreas Am 11.07.2019 um 18:07 schrieb Andreas Kretzer: > I'm using SQLITE3 (V3.29.0) on an arm embedded linux (2.6.39) on an ext3 > filesystem. > > Several processes hold the DB open and the "-wal" and "-shm" files exist. > if I use 'lsof | fgrep ' I can see all processes having all > three > files open. At least one of the processes uses threads, but every process > has just one single DB connection active which is shared among all threads. > > The compilation of sqlite3 is done with multithreading in mind: > > sqlite> pragma compile_options; > COMPILER=gcc-6.2.0 > ENABLE_DBSTAT_VTAB > ENABLE_FTS4 > ENABLE_JSON1 > ENABLE_RTREE > ENABLE_STAT3 > ENABLE_STMTVTAB > ENABLE_UNKNOWN_SQL_FUNCTION > ENABLE_UPDATE_DELETE_LIMIT > HAVE_ISNAN > THREADSAFE=1 > > I can check, that the database is threadsafe (mode == 1) and is switched > to WAL-mode. > > So far I never noticed any problems dealing with concurrent updates or so. > The only thing (tested in depth with V3.15.2 and V3.29.0) is when one > process stops and closes the database using sqlite3_close(). This may even > be the sqlite3 CLI. That process closes DB (lsof shows that this process has > closed its filedescriptors and is not in the listing anymore). Right at the > next write access to the DB in the still running process (at least I > think that > this is exactly the point) the "-wal" and "-shm" files are removed. > The sqlite3_exec() function still returns SQLITE3_OK on all following > actions, > but 'lsof' reports, that this process has opened the "-wal" and "-shm" > files, > but marked as "deleted". And they are really deleted and none of the > upcoming > DB changes will ever reach the real DB. > > What is wrong? I already checked, that my kernel supports POSIX file locking > (CONFIG_FILE_LOCKING=yes). What else can I check? Two or more sqlite3 CLI > processes started in parallel don't exhibit this behavior. > > Thanks > > Andreas -- Mit freundlichen Grüßen Andreas Kretzer ETB Electronic Team Beratungs- und Vertriebs GmbH Berliner Straße 8a 15537 Erkner FON +49 3362 889349-12 FAX +49 3362 889349-23 email: a.kret...@etb-electronic.de AG Potsdam HRB 16532; Sitz der Gesellschaft: Am Mellensee Geschäftsführer: Marco Runge, Jürgen Gentzsch ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] strftime("%Y", "-1234-01-01") returns -123
Hi.
I found a doubtful behavior of strftime("%Y").
strftime("%Y", "-1234-01-01") returns -123 but
I feel it should be -1234.
(But it can be error because the manual describes "%Y year: -".)
% ./sqlite3
SQLite version 3.29.0 2019-07-10 17:32:03
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT strftime("%Y", "-1234-05-06");
-123
sqlite>
% ./sqlite3 -version
3.29.0 2019-07-10 17:32:03
fc82b73eaac8b36950e527f12c4b5dc1e147e6f4ad2217ae43ad82882a88bfa6
I built sqlite3 from sqlite-autoconf-329.tar.gz downloaded today.
% ls ..
sqlite-autoconf-329 sqlite-autoconf-329.tar.gz
--
Tanaka Akira
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] strftime("%Y", "-1234-01-01") returns -123
On 7/16/19, Tanaka Akira wrote:
> Hi.
> I found a doubtful behavior of strftime("%Y").
>
> strftime("%Y", "-1234-01-01") returns -123
Perhaps you overlooked the following paragraph from the documentation:
"These functions only work for dates between -01-01 00:00:00 and
-12-31 23:59:59 (julian day numbers 1721059.5 through 5373484.5).
For dates outside that range, the results of these functions are
undefined."
--
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
Re: [sqlite] [EXTERNAL] strftime("%Y", "-1234-01-01") returns -123
You should be using single quotes for string delimiters. Additionally, you are
seeing right handed truncation of a 5 character string in a 4 character field
asql> select strftime('%Y','-0123-04-05');
strftime('%Y','-0123-04-05')
-123
-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Tanaka Akira
Sent: Dienstag, 16. Juli 2019 14:08
To: sqlite-users@mailinglists.sqlite.org
Subject: [EXTERNAL] [sqlite] strftime("%Y", "-1234-01-01") returns -123
Hi.
I found a doubtful behavior of strftime("%Y").
strftime("%Y", "-1234-01-01") returns -123 but I feel it should be -1234.
(But it can be error because the manual describes "%Y year: -".)
% ./sqlite3
SQLite version 3.29.0 2019-07-10 17:32:03 Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT strftime("%Y", "-1234-05-06");
-123
sqlite>
% ./sqlite3 -version
3.29.0 2019-07-10 17:32:03
fc82b73eaac8b36950e527f12c4b5dc1e147e6f4ad2217ae43ad82882a88bfa6
I built sqlite3 from sqlite-autoconf-329.tar.gz downloaded today.
% ls ..
sqlite-autoconf-329 sqlite-autoconf-329.tar.gz
--
Tanaka Akira
___
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
