[sqlite] .import command/NULL values
Any particular reason why the shell '.import' command cannot interpret || as a NULL for the field? Does || have another use/interpretation? Just seems a shame that the standard bulk input mechanism cannot cope directly with NULLs. Best regards On Wed, Mar 16, 2016 at 9:13 AM, Simon Slavin wrote: > > On 16 Mar 2016, at 9:02am, Simon Slavin wrote: > > > No. Use one of these two methods. > > I'm sorry. I read your 'import' but I thought you meant using INSERT. If > you are using the shell's ".import" command then Clemens is right. You are > importing from a text file, and there is no way to put a NULL in a text > file. After your import you should do something like > > UPDATE t SET b=NULL WHERE b='NULL'; > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] .import command/NULL values
I couldn't find this issue documented anywhere. If it is required to import NULL values into an Sqlite table field, can this be done simply by arranging that the before and after delimiters for that field are consecutive? Example: For a three field table (all three fields text type), importing from a text file, using default delimiters, something like A||H would put A in the first field, H in the third, NULL in the second? And appropriate modifications to put NULL's into the first or last field? Best regards
[sqlite] Bug: Successfully committed transaction rolled back after power failure
=== https://www.sqlite.org/src/timeline?y=ci=af92401826f5cf49e62c === To clarify: Is it correct to say that, under Linux, if SQLITE_EXTRA_DURABLE is set (and all other settings are left as default values), and with the further assumption that the hardware reports write status accurately to the OS, then SQLITE_OK will only be returned after a transaction is irreversably fixed on the disk? Best regards On Sat, Feb 6, 2016 at 2:45 PM, Richard Hipp wrote: > On 2/6/16, Bernard McNeill wrote: > > Please can I formally propose that, for Linux: > > 1. A Pragma or other compile-time option is created such that SQLITE_OK > is > > not issued on file writes/modifications/deletes until the hardware > > indicates that all associated Directory syncs, etc., are done. > > https://www.sqlite.org/src/timeline?y=ci=af92401826f5cf49e62c > > > 2. Since the absence of 1. appears to break the 'D' in ACID, the option > is > > set on by default, > > No. This would be a serious performance hit for billions and billions > of devices that have soldered-in batteries and generally do not care > about durability in the event of an OS crash. Remember that SQLite > has never synced directories like this before in its 15+ year history > and that was never a problem for anyone until a few weeks ago. If > handset manufacturers, and/or OS vendors, want to make it the default > on their systems, there is a compile-time option for that: > https://www.sqlite.org/draft/compile.html#extra_durable > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Bug: Successfully committed transaction rolled back after power failure
Please can I formally propose that, for Linux: 1. A Pragma or other compile-time option is created such that SQLITE_OK is not issued on file writes/modifications/deletes until the hardware indicates that all associated Directory syncs, etc., are done. 2. Since the absence of 1. appears to break the 'D' in ACID, the option is set on by default, but the docs make the point that it could produce a substantial performance hit. The docs could also make the point that if the hardware returns incorrect status to the OS, then, even if the option is on, 'D' may still not be true (but not Sqlite's fault). Best regards On Mon, Jan 25, 2016 at 3:47 PM, Richard Hipp wrote: > On 1/25/16, Matthias-Christian Ott wrote: > > > > Does this mean that if I use SQLite SQLITE_EXTRA_DURABLE=0, PRAGMA > > journal_mode=DELETE and PRAGMA synchronous=FULL, SQLite could loose a > > transaction that it said to be committed depending on the VFS? > > Sort of. This appears to be true if you are running on QNX and you > lose power (or do a hard reset) shortly after the transaction commits. > It might be the case on other OSes/filesystems but it has never before > been observed. > > This is not new behavior. This is apparently what SQLite has been > doing for 15 years, across quadrillions of transactions on many > billions of devices, and it has never before caused any issues, until > just recently when Mr. Meinlschmidt upgraded to a newer version of > QNX. > > > > > If so, why isn't SQLITE_EXTRA_DURABLE=1 the default? Should correctness > > be more important than performance, except when the constraints are such > > that correctness has to be sacrificed for performance? > > > > The trade-off that is described in the description of SQLite > > SQLITE_EXTRA_DURABLE reads like an excerpt from the MySQL manual when > > MyISAM was still widely used. Perhaps I'm also too irritated by > > discussions with advocates of MySQL who would argue against the fact > > that proper transactions were necessary because the DBMS would be faster > > without them. That is not to say that the ACID properties and > > transactions solve every concurrency or correctness problem but they > > help significantly. > > > > As you point out, it is an engineering tradeoff. > > The feedback I receive is that most users of SQLite would much rather > avoid the extra directory syncs, even if it means having the last > transaction rollback following a power loss. Most developers do not > care that much about durability, at least not enough to want to take > the performance hit of syncing the directory after every unlink. > Non-durable commits on power-loss have long been the default in WAL > mode (run-time fixable by setting PRAGMA synchronous=FULL) and nobody > has before ever complained. Most people consider this a feature. In > fact, if I recall correctly, we first made synchronous=NORMAL the > default in WAL mode by popular request. WAL mode used to default to > power-loss durable but people requested the change for performance > reasons. > > Note especially that this is about durability, not consistency. > SQLite guarantees consistency regardless. People care about > consistency. Durability, not so much. I'm not a MySQL expert, but I > think the historical complaints about MyISAM had more to do with > consistency than with durability, did they not? > > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Bug: Successfully committed transaction rolled back after power failure
=== ...you must fsync the containing directory... === Is there an Sqlite option to achieve that? In fact, to summarise: Suppose I would like to maximise my chances of avoiding the 'Lost Post-It' problem described above. What are _all_ the Sqlite compile-time options, and their values, needed under Linux? (I appreciate the disk hard/firmware will need to be looked at as well) Best regards On Thu, Jan 28, 2016 at 4:37 PM, Howard Chu wrote: > Simon Slavin wrote: > >> >> On 28 Jan 2016, at 1:38pm, Bernard McNeill wrote: >> >> === >>> Like the user reading ?saving OK? and throwing away the >>> Post-It with the original information >>> === >>> >>> This is exactly my concern. >>> The user throwing away the Post-It is entirely reasonable if he sees a >>> message like that. >>> >>> Do you happen to know if Linux/Debian (which I think uses a journalling >>> filesystem) carries this risk? >>> >> >> The problem is not at the software level. >> > > Not true. There *is* a problem at the software level - on Linux, current > BSD (and apparently also on QNX) you must fsync the containing directory > when you make changes to the contents of a directory (create/delete/rename > files). This is above and beyond whatever lies the hardware layer may tell > you. It's a documented requirement in Linux, at least. It is also > independent of whether or not the filesystem uses journaling. > > -- > -- Howard Chu > CTO, Symas Corp. http://www.symas.com > Director, Highland Sun http://highlandsun.com/hyc/ > Chief Architect, OpenLDAP http://www.openldap.org/project/ > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Bug: Successfully committed transaction rolled back after power failure
=== Like the user reading ?saving OK? and throwing away the Post-It with the original information === This is exactly my concern. The user throwing away the Post-It is entirely reasonable if he sees a message like that. Do you happen to know if Linux/Debian (which I think uses a journalling filesystem) carries this risk? Best regards On Thu, Jan 28, 2016 at 8:00 AM, Meinlschmidt Stefan < Stefan.Meinlschmidt at esolutions.de> wrote: > >> Using the standard defaults (which avoid WAL), is there any > >> possibility whatsoever of that last SQL transaction being lost? > > > > I have an unusual answer: Yes, and it doesn't matter. > > While I agree in principle, your answer depends on some assumptions that > need not hold. > > > Let's suppose, as you did, that the application got back "Commit > > Successful" followed quickly by a power failure. You want to know, > > could the transaction be lost anyway? But I ask you, what action could > > the application possibly take, in that subsecond interval, that it > > matters? > > Under the QNX OS using a QNX6 filesystem with default configuration, > that ?subsecond interval? is actually up to 10s. For any non-journalling > filesystem (SD cards, anyone?) mounted without immediate write-through > (for efficiency) on Linux the interval can be, IIRC, up to 30s. So how > much can happen in this period is very sensitive to details not > necessarily under control of or even available to the SQLite user. > > The application could for example write to some non-SQLite storage > (other file system, raw flash, physical journal printout, ?) and try to > guarantee global consistency by waiting for the SQLite transaction to > complete. Like the user reading ?saving OK? and throwing away the > Post-It with the original information. Or (what we did) it could shut > off device power. > > > There is no God's-eye view of application state. The important service > > provided by the DBMS is not "what's committed is definitely saved", but > > rather that "what's committed is definitely *consistent*". > > So when your application requires consistency of some broader scope, you > need the DBMS give you enough rope^h^h^h^h^h^h^h^h^h^h^hthe tools to > implement that yourself. Without a durability guarantee you're screwed. > > The more frequent simpler usecases of course are not susceptible to that > and then indeed it doesn't matter. > > S.M. > -- > Dipl.-Phys. (Univ) Stefan Meinlschmidt, Senior Software Engineer > Am Wolfsmantel 46, 91058 Tennenlohe, Germany > Tel: +49-8458-3332-531 stefan.meinlschmidt at esolutions.de > Fax: +49-8458-3332-20-531 > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Bug: Successfully committed transaction rolled back after power failure
Just to be clear Situation: Under Linux/Debian, Sqlite opens an entirely valid DB, and runs an entirely valid SQL transaction against that database. Following a Commit, the application gets back a 'Commit Successful' code. (Ignore any issues of disks returning hardware 'write done' flags prematurely). There is then a power/OS failure. Using the standard defaults (which avoid WAL), is there any possibility whatsoever of that last SQL transaction being lost? Best regards On Mon, Jan 25, 2016 at 8:39 PM, Richard Hipp wrote: > On 1/25/16, Howard Chu wrote: > > > > This is actually quite an unusual requirement; on older Unix systems you > > couldn't even *open* a directory, let alone obtain write access to it or > > fsync it. > > Yeah. When the SQLITE_DISABLE_DIRSYNC compile-time option is present, > we disable the directory sync logic for this reason. Some unixes > (HP/UX) require -DSQLITE_DISABLE_DIRSYNC in order to work. But Linux, > MacOS, and *BSD all work without it, so I thought I'd just not bring > that up... > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Running Sqlite under Linux Terminal Server
Hello, Anyone with any experiences with this (good or bad)? Idea to have Sqlite database on server, accessed from a few ultra-thin Terminal clients. Best regards
