[sqlite] [AGAIN] SQLite on network share
Where's the like button when you actually want to use it? On Sun, Nov 15, 2015 at 8:05 PM, James K. Lowden wrote: > On Fri, 13 Nov 2015 13:19:33 -0800 > Roger Binns wrote: > > > On talking to sites that had the competitor devices, we'd find they > > did notice increases in programs crashing and data file issues, but > > had written it off as the kind of thing that happens with Windows. > > Q: Why doesn't Microsoft write fault-tolerant software? > > A: No need, they have fault-tolerant customers. > > (I don't know whom to credit.) > > --jkl > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] [AGAIN] SQLite on network share
Em 13/11/2015 15:29, "A. Mannini" escreveu: > > Hi, > > i read SQLite FAQ and understood that use of SQLite on network share CAN > corrupts database file. > Fo me, it isn't clear if there is a way to safely use SQLite on a > network share in contests with few clients (max 5 for ex) and low read / > write concurrency.. Disclaimer I'm pretty aware of warnings about network share, and that is a bad design. I have two systems running with sqlite db shared in a windows only network running for almost 5 years. No issues related to the network until now. The apps are mostly read and db file size is around 20 MB. I took the precaution to not update the sqlite DLL to avoid different versions acessing the same db Luiz > > Thanks > Alessandro > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [AGAIN] SQLite on network share
On Fri, 13 Nov 2015 13:19:33 -0800 Roger Binns wrote: > On talking to sites that had the competitor devices, we'd find they > did notice increases in programs crashing and data file issues, but > had written it off as the kind of thing that happens with Windows. Q: Why doesn't Microsoft write fault-tolerant software? A: No need, they have fault-tolerant customers. (I don't know whom to credit.) --jkl
[sqlite] [AGAIN] SQLite on network share
I've read through all the other posts on this thread, and I must agree. Your "serverless" configuration can't exist because each client becomes a server, BY DEFINITION, when it starts PROVIDING information to other clients. I've written a book, by now, when this topic comes up. Believe me Alessandro, you're not the only one that wonders why. So for you, and all my future writing endeavors, to save the planet some electricity due to the servers of the world having to deal with my words, and, well, because I don't want to flood a novel worth of text to all followers of this mailing list who may not WANT to read what I say, I've written a blog post on this very subject. http://randomthoughts.ca/index.php?/archives/7-Serverless-Servers-using-NFS-Why-it-cant-happen.html In conclusion, no. There is no safe way to use SQLite on any NFS. See the link above to find out why. On Fri, Nov 13, 2015 at 1:29 PM, A. Mannini wrote: > Hi, > > i read SQLite FAQ and understood that use of SQLite on network share CAN > corrupts database file. > Fo me, it isn't clear if there is a way to safely use SQLite on a > network share in contests with few clients (max 5 for ex) and low read / > write concurrency.. > > Thanks > Alessandro > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] [AGAIN] SQLite on network share
On Fri, Nov 13, 2015 at 11:15 PM, A. Mannini wrote: > Yes I use it in other contests but, as written in another message, in > need a serverless solutions. > A shared filesystem _is_ a network service! Since they have a system sharing a drive, they can just as easily install MySQL on it and completely bypass the file-sharing problems and corruption which _will_ happen if you try to use sqlite3 on a shared network filesystem. -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
[sqlite] [AGAIN] SQLite on network share
> Why do you think that is a problem? (the x86_64)? > Yes there isn't a x64 Jet version. Or at least, there is the ACE x64 but can't be installed side-by-side to Office 32 bit. > Other suggests are welcomed? > Microsoft SQL Server is free (with a limitation of a 4GB database) and it > probably integrates the best with other Microsoft "technologies" (I use that > word very loosely). > Yes I use it in other contests but, as written in another message, in need a serverless solutions. Thanks A
[sqlite] [AGAIN] SQLite on network share
On 2015/11/13 10:52 PM, A. Mannini wrote: >> Basically the decision is easy - If you require either of: >> - Network data >> - User control >> >> Then you should use a suited Network DB and not a file-based DB. Best >> free (without limitations) choices are (In no particular order): >> - PostGres | http://www.postgresql.org/ >> - MariaDB / MySQL | https://www.mysql.com/ >> >> ... in fact, let me simply link you to a site with a listing already: >> http://tech.gaeatimes.com/index.php/archive/top-7-free-open-source-database-server/ >> >> >> Personally I just use the two above, MySQL especially for Web things, >> and PG for Networked systems (but that's just my preference, they both >> work either way). I use SQLite to store local data always. I don't >> wish to start a fanboy fight, but my feeling is that: MySQL is easier >> to code the broad-spectrum SQL for... Postgres is more stable, strict >> and secure. (My biased views). >> >> HTH, >> Ryan >> >> > Hi Ryan, > > your suggestions are good choices (that i use in other constests) but i > was oriented to server-less solutions because the application should be > able to work in a peer-to-peer environment without a server. Moreover I > need a solution that doesn't require much skills to setup. I would avoid > MySql/MSSQL/PostGRES/Other server setup, open ports or other possible > problems. Understandable, but that's a very odd use case, though not the first of its kind. What you need is your own file lock marshaling - perhaps write your own VFS for SQLite that do a bit of checking for file lock truth / exclusivity. The VFS capability is well-documented and many people have implemented it here, so any help you need should be easily had - but it will be quite a bit of work, and whatever method you use to ascertain file statuses over a network will be slower than not doing it. It might be worth it for your use-case. Someone might even have done it already and be willing to share the code. Ask here (in a different thread perhaps) and check google / github etc. Best of luck! Ryan
[sqlite] [AGAIN] SQLite on network share
On 2015/11/13 9:55 PM, A. Mannini wrote: > Ok, thanks for all your replies!!! > > First, i was asking to understand...before to start development in a > wrong direction. > > I don't have experience with SQLite and even less on a network share. I > would understand if corruption is a remote possibility or a certainty. > > Someone said that Access suffer the same problem... In my experience > even with 20-30 clients with low concurrency (management software) MS > Access file corruption is a rare event. > (the article you linked refer to a bug with an hotfix) > I can't use Access in my case because my application is x64. > > About VistaDB it support use on network share look at > http://www.gibraltarsoftware.com/Support/VistaDB/Documentation/WebFrame.html#VistaDB_Introduction_SupportedPlatforms.html > and confirmed from its support. Unfortunately i have not experiences > with iti can't say how much this is true... > > Other suggests are welcomed? Basically the decision is easy - If you require either of: - Network data - User control Then you should use a suited Network DB and not a file-based DB. Best free (without limitations) choices are (In no particular order): - PostGres | http://www.postgresql.org/ - MariaDB / MySQL | https://www.mysql.com/ ... in fact, let me simply link you to a site with a listing already: http://tech.gaeatimes.com/index.php/archive/top-7-free-open-source-database-server/ Personally I just use the two above, MySQL especially for Web things, and PG for Networked systems (but that's just my preference, they both work either way). I use SQLite to store local data always. I don't wish to start a fanboy fight, but my feeling is that: MySQL is easier to code the broad-spectrum SQL for... Postgres is more stable, strict and secure. (My biased views). HTH, Ryan
[sqlite] [AGAIN] SQLite on network share
> Basically the decision is easy - If you require either of: > - Network data > - User control > > Then you should use a suited Network DB and not a file-based DB. Best > free (without limitations) choices are (In no particular order): > - PostGres | http://www.postgresql.org/ > - MariaDB / MySQL | https://www.mysql.com/ > > ... in fact, let me simply link you to a site with a listing already: > http://tech.gaeatimes.com/index.php/archive/top-7-free-open-source-database-server/ > > > Personally I just use the two above, MySQL especially for Web things, > and PG for Networked systems (but that's just my preference, they both > work either way). I use SQLite to store local data always. I don't > wish to start a fanboy fight, but my feeling is that: MySQL is easier > to code the broad-spectrum SQL for... Postgres is more stable, strict > and secure. (My biased views). > > HTH, > Ryan > > Hi Ryan, your suggestions are good choices (that i use in other constests) but i was oriented to server-less solutions because the application should be able to work in a peer-to-peer environment without a server. Moreover I need a solution that doesn't require much skills to setup. I would avoid MySql/MSSQL/PostGRES/Other server setup, open ports or other possible problems. Thanks Alessandro
[sqlite] [AGAIN] SQLite on network share
On 13 Nov 2015, at 6:46pm, A. Mannini wrote: > 1) is there a list of FS where SQLite works fine? It's not usually the FS which is causing the problem. When your application tells the OS to write to a remote disk ... program calls OS API to write to a file OS calls Network FS on client Network Filing System communicates across the network NFS on server calls FS FS calls storage driver storage driver talks to hardware hardware performs the write hardware reads the sector just written hardware checks what was read matches what it was told to write hardware returns success/error code to storage driver storage driver returns success/error code to file system file system returns success/error code to server-side NFS Network Filing System communicates across the network Network FS on client returns success/error code to the OS OS API returns success/error code to the program program correctly handles success/error (Above is simplified. For example, any stage might try again if it gets an error.) Only in server-range setups (expensive, slow) is this correctly implemented. It takes a lot of time (a millisecond or two for each call) to do it right. If this setup was used for your desktop computer you'd be down to three or four characters a second in Word. I've set computers up this way for demonstrations and they are ridiculously slow and annoying to use. Like 25 minutes to from power-on to being able to type in Word. In standard desktop setups the pause for the check is skipped, either by the hardware (check your jumper settings) or in the storage driver. Instead of waiting for the check, it receives the command, returns "Command executed without error." and /then/ passes the command along to the lower level. Much faster. As you can see this occurs below file system level and is dependent on your hard drive and its driver, the DIP (jumper) settings set on your hard drive, and the mode your driver is loaded in. Worse still, manufacturers change the specification of a drive and what the jumpers mean without changing the model name. And it can happen even if both your FS and NFS are bugless. The whole thing's a nightmare. > 2) why there are SERVERLESS database (MS Access or VistaDB) that works > without FS restrictions? Both have similar problems. They all suffer from rare failures. You can use any of these databases for a year without problems and then get database corruption for no obvious reason. Simon.
[sqlite] [AGAIN] SQLite on network share
Ok, thanks for all your replies!!! First, i was asking to understand...before to start development in a wrong direction. I don't have experience with SQLite and even less on a network share. I would understand if corruption is a remote possibility or a certainty. Someone said that Access suffer the same problem... In my experience even with 20-30 clients with low concurrency (management software) MS Access file corruption is a rare event. (the article you linked refer to a bug with an hotfix) I can't use Access in my case because my application is x64. About VistaDB it support use on network share look at http://www.gibraltarsoftware.com/Support/VistaDB/Documentation/WebFrame.html#VistaDB_Introduction_SupportedPlatforms.html and confirmed from its support. Unfortunately i have not experiences with iti can't say how much this is true... Other suggests are welcomed? Thanks Alessandro
[sqlite] [AGAIN] SQLite on network share
You said you wanted something that didn't require too much skill to set up? PostgreSQL seems safer and easier than implementing VFS yourself. One is **slightly** less error-prone than the other. On Fri, Nov 13, 2015 at 7:08 PM, Keith Medcalf wrote: > You realize that the marketing translation of "support" is "make money from"? > It does not mean "works". > This. If you use SQLite or not, the list loses **very** little. But they could always use another customer. You shouldn't ask their support this questions, they are not giving you any formal guarantees. -- Bernardo Sulzbach
[sqlite] [AGAIN] SQLite on network share
Il 13/11/2015 19:31, Richard Hipp ha scritto: > On 11/13/15, A. Mannini wrote: >> Hi, >> >> i read SQLite FAQ and understood that use of SQLite on network share CAN >> corrupts database file. >> Fo me, it isn't clear if there is a way to safely use SQLite on a >> network share in contests with few clients (max 5 for ex) and low read / >> write concurrency.. >> > If your network filesystem implements file locks correctly, then > SQLite will work fine. Just be warned that there are many network > filesystems that claim to implement locks correctly, and do most of > the time, but sometimes mess up. > Ok, two questions: 1) is there a list of FS where SQLite works fine? 2) why there are SERVERLESS database (MS Access or VistaDB) that works without FS restrictions? Thanks Alessandro
[sqlite] [AGAIN] SQLite on network share
Hi, i read SQLite FAQ and understood that use of SQLite on network share CAN corrupts database file. Fo me, it isn't clear if there is a way to safely use SQLite on a network share in contests with few clients (max 5 for ex) and low read / write concurrency.. Thanks Alessandro
[sqlite] [AGAIN] SQLite on network share
On Fri, 13 Nov 2015 18:29:32 +, A. Mannini wrote: > > Hi, > > i read SQLite FAQ and understood that use of SQLite on network share CAN > corrupts database file. > Fo me, it isn't clear if there is a way to safely use SQLite on a > network share in contests with few clients (max 5 for ex) and low read / > write concurrency.. Alessandro, It's not just for you that it isn't clear. It's not clear for anyone else either. Typically, remote file systems give potentially misleading signals that a file write operation has completed, even though data are still "in flight" and may never arrive at their destination. As a consequence, there is a risk, in using SQLite or any other application, that what is stored on disk is not as intended. It's not very long ago that there was a discussion on this list about the risk of corruption on a local file-system using consumer-grade disks. For a remote file-system using similar technology, the risk cannot be less. The scale of this risk depends on how your particular remote file system and network connections are set up. The acceptability of the risk depends on what the consequences may cost in your case. People on this mailing list can't do your risk assessment or impact analysis for you. Best regards, Niall O'Reilly
[sqlite] [AGAIN] SQLite on network share
On Fri, Nov 13, 2015 at 5:04 PM, Niall O'Reilly wrote: > People on this mailing list can't do your risk assessment or > impact analysis for you. > > Best regards, > Niall O'Reilly > Seconded. You asked if there was a way to safely use it. I don't think there is. You also mentioned "max 5". In some cases your current "max" is far below what your true "max" is going to be. If you need a DB that works over a network, SQLite doesn't look like the best candidate to me. -- Bernardo Sulzbach
[sqlite] [AGAIN] SQLite on network share
> > Why do you think that is a problem? (the x86_64)? > Yes there isn't a x64 Jet version. Or at least, there is the ACE x64 but > can't be installed side-by-side to Office 32 bit. Ah, I see. Microsoft introduces artificial restrictions "because they can". Just like they could have fixed all the stupid bugs and programming errors in SQL Server in the 32-bit version, but refused to do so because they think more bits is better. Just like they cannot comprehend "data structures" and instead artificially limit all sorts of things by choosing stupid arbitrary limits that cannot be changed. You compiled your application as 64-bit and the Jet in-process com objects are 32-bit. Why not just recompile with a 32-bit compiler? > > Other suggests are welcomed? > > Microsoft SQL Server is free (with a limitation of a 4GB database) and > it probably integrates the best with other Microsoft "technologies" (I use > that word very loosely). > Yes I use it in other contests but, as written in another message, in > need a serverless solutions. There is an embedded version of SQL Server (forget what it is called) but it does not work with network filesystems either. Very little works for shared-update access over a network filesystem -- and if you turn off the "features" required to make them work (the same features that make network filesystems useable for any other purpose that does NOT include non-exclusive updating), they will then work about the same speed as a secretary with a rolodex and a pen (ie, speed measured in transactions/hour rather than transactions/second).
[sqlite] [AGAIN] SQLite on network share
On Fri, Nov 13, 2015 at 1:46 PM, A. Mannini wrote: > 2) why there are SERVERLESS database (MS Access or VistaDB) that works > without FS restrictions? If you think Access works reliably on a network share, you're going to run in to trouble sooner or later: https://support.microsoft.com/en-us/kb/2028965 (Among many other issues)
[sqlite] [AGAIN] SQLite on network share
> Ok, thanks for all your replies!!! > > First, i was asking to understand...before to start development in a > wrong direction. > > I don't have experience with SQLite and even less on a network share. I > would understand if corruption is a remote possibility or a certainty. > > Someone said that Access suffer the same problem... In my experience > even with 20-30 clients with low concurrency (management software) MS > Access file corruption is a rare event. > (the article you linked refer to a bug with an hotfix) > I can't use Access in my case because my application is x64. Why do you think that is a problem? (the x86_64)? > About VistaDB it support use on network share look at > http://www.gibraltarsoftware.com/Support/VistaDB/Documentation/WebFrame.ht > ml#VistaDB_Introduction_SupportedPlatforms.html > and confirmed from its support. Unfortunately i have not experiences > with iti can't say how much this is true... > > Other suggests are welcomed? Microsoft SQL Server is free (with a limitation of a 4GB database) and it probably integrates the best with other Microsoft "technologies" (I use that word very loosely).
[sqlite] [AGAIN] SQLite on network share
On Friday, 13 November, 2015 12:55 A. Mannini said: > About VistaDB it support use on network share look at > http://www.gibraltarsoftware.com/Support/VistaDB/Documentation/WebFrame.ht > ml#VistaDB_Introduction_SupportedPlatforms.html > and confirmed from its support. Unfortunately i have not experiences > with iti can't say how much this is true... You realize that the marketing translation of "support" is "make money from"? It does not mean "works". I don't know how you found that page, but it is not in the Table of Contents. If you are going to use their product and pay money for it, I would recommend that you get a warranty in writing that their product works for multiuser database access (including updates) via a shared filesystem using the embedded server. Often one must take with a boulder of salt most of the claims made by product marketing organizations. Most vendors will not provide you with a list of bugs, caveats, and restrictions prior to purchase, only afterwards when you have problems -- except for a very rare few.
[sqlite] [AGAIN] SQLite on network share
On 11/13/15, A. Mannini wrote: > Hi, > > i read SQLite FAQ and understood that use of SQLite on network share CAN > corrupts database file. > Fo me, it isn't clear if there is a way to safely use SQLite on a > network share in contests with few clients (max 5 for ex) and low read / > write concurrency.. > If your network filesystem implements file locks correctly, then SQLite will work fine. Just be warned that there are many network filesystems that claim to implement locks correctly, and do most of the time, but sometimes mess up. -- D. Richard Hipp drh at sqlite.org
[sqlite] [AGAIN] SQLite on network share
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/13/2015 11:55 AM, A. Mannini wrote: > About VistaDB it support use on network share look at > http://www.gibraltarsoftware.com/Support/VistaDB/Documentation/WebFram e.html#VistaDB_Introduction_SupportedPlatforms.html > > and confirmed from its support. Unfortunately i have not experiences > with iti can't say how much this is true... They don't list any supported network filesystems. Those various combinations of Windows they list speak different versions of SMB to each other. Some aren't even supported by Microsoft any more. And they don't actually say what they mean by "support". They also don't appear to provide any tool that lets you do the certification yourself. You should understand where we come from in the SQLite world. Data integrity matters. This is how things are tested: https://www.sqlite.org/testing.html - From that vantage point, other vendors can look sloppy :-) How about a tale from the past. I used to work on WAN optimizers. They intercept network traffic, compress it to reduce bandwidth, and cache plus write behind to reduce latency. One of our competitors had a shoddy implementation, that for example would paper over errors, incorrectly cache (or not flush cached) information and various other things. These conditions weren't hit too often, while the bandwidth and latency improvements were very noticeable. On talking to sites that had the competitor devices, we'd find they did notice increases in programs crashing and data file issues, but had written it off as the kind of thing that happens with Windows. ie expectations on data integrity for Windows is already pretty low, even though it wasn't at all at fault. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v2 iEYEARECAAYFAlZGU+UACgkQmOOfHg372QQQeQCfVIgWO1n/X7x9A0mUkMzRTvp8 9aUAn1Ma2DLPaGoQ3c9+9mIo02kGfXXR =arIX -END PGP SIGNATURE-
[sqlite] [AGAIN] SQLite on network share
On Fri, Nov 13, 2015 at 12:55 PM, A. Mannini wrote: > > Ok, thanks for all your replies!!! > > First, i was asking to understand...before to start development in a > wrong direction. > > I don't have experience with SQLite and even less on a network share. I > would understand if corruption is a remote possibility or a certainty. > > Someone said that Access suffer the same problem... In my experience > even with 20-30 clients with low concurrency (management software) MS > Access file corruption is a rare event. > (the article you linked refer to a bug with an hotfix) > I can't use Access in my case because my application is x64. > > About VistaDB it support use on network share look at > http://www.gibraltarsoftware.com/Support/VistaDB/Documentation/WebFrame.html#VistaDB_Introduction_SupportedPlatforms.html > and confirmed from its support. Unfortunately i have not experiences > with iti can't say how much this is true... The quote from that page reads: "Multi-user applications that access data on a shared network drive". The problem is that not all multi-user applications are created equal. For example, maybe there is a multi-user application that accesses data on a shared network drive, but the multiple users work different shifts and there is never more than one person using it at a time. Or perhaps each user accesses a particular customer account, and each customer account is stored in a different database or directory. In many (perhaps most) cases, there won't be any problems. You might run these types of applications for months or years without ever seeing a problem. Until one day when a problem rears its ugly head. At that point you won't really care who is at fault: SQLite, MS Access, SMB or NFS network shares, a buggy file system, a buggy operating system, buggy firmware on the drive, misconfigured hardware or software, ... the list is practically endless. The reason that SQLite warns against using network shares is because they have been a repeated source of problems. Many people use them successfully, but when they break, they break hard. Asking "which network file system is best for my data integrity" might be likened to asking "which brand of cigarettes are best for my health". You can probably answer them in some way, but the real answer is "none" in both cases. -- Scott Robison
[sqlite] [AGAIN] SQLite on network share
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/13/2015 10:46 AM, A. Mannini wrote: > 1) is there a list of FS where SQLite works fine? I don't know of any. Network filesystems are very hard to implement (so many corner cases), and there is a lot of complexity if you also want them to be performant. > 2) why there are SERVERLESS database (MS Access or VistaDB) that > works without FS restrictions? Vendor snake oil. Even a poorly implemented one will appear to work. As far as I can tell, Access does not have checksums or similar data integrity measures in its file format. Consequently, how would you even know? Here is a random page (possibly selling a "solution") describing how Access gets corrupted, especially on a network. Note how they say same stuff we say about using SQLite over a network: http://www.everythingaccess.com/tutorials.asp?ID=Access-Database-Corrupt ion-Repair-Guide I don't see any mention on the VistaDB pages that they support networked filesystems. Heck they claim to run on anything (eg also Mono), which by definition means they can't have tested every possibility. Unless a vendor can provide a guarantee of some kind, or at the very least some certified configuration, I wouldn't trust it either. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v2 iEYEARECAAYFAlZGNkMACgkQmOOfHg372QQdNQCfVECWQymsAzgikzQkuBjm01R/ rR4AnjyoSAfKBcF8hG3MxC2YXdNO0XWp =UtWN -END PGP SIGNATURE-
[sqlite] [AGAIN] SQLite on network share
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/13/2015 10:31 AM, Richard Hipp wrote: > Just be warned that there are many network filesystems that claim > to implement locks correctly, and do most of the time, but > sometimes mess up It is also worth mentioning that SQLite trusts the filesystem 100%. SQLite does not verify that what it thought was written out, is in fact the same as what just got read in[1]. Consequently it could be quite a while after corruption has happened before it is detected or effects found. Since SQLite doesn't keep redundant copies of information[2], you are unlikely to recover everything or even know what is missing/wron g. [1] Some sort of checksumming mechanism would help. It got rejected: http://www.sqlite.org/src/info/72b01a982a [2] Indexes are the exception, although recovering information from them isn't particularly practical Roger -BEGIN PGP SIGNATURE- Version: GnuPG v2 iEYEARECAAYFAlZGMC0ACgkQmOOfHg372QRb7QCeJOIRGKRWY0lFFYzz8Fn+8l6L IeUAoKzyOO51ldK6xm2f3XK9PuzUTuRG =wvUQ -END PGP SIGNATURE-
