13 ms·
Why sqlite3 temp files were renamed 'etilqs_*' (2006)
- shadowgovt 3y agoSoftware is at least a much social as technology.
- kapp_in_life 3y agoLink directly to the reason comment: https://github.com/mackyle/sqlite/blob/18cf47156abe94255ae1495ba2da84517dce6081/src/os.h#L65 https://github.com/mackyle/sqlite/blob/18cf47156abe94255ae14...
- deleted 3y ago[deleted]
- endisneigh 3y agoSomewhat related - I’m very very curious to hear a detailed account of someone who uses SQLite for a production app with high traffic. For the embedded use case I think it’s a slam dunk, but there are many interesting use cases for server side but they all seem to be toyish. The locking behavior of SQLite is somewhat problematic unless you use WAL and even then not perfect
- nickstinemates 3y agoFly[1] uses it 1: https://fly.io/blog/introducing-litefs/ https://fly.io/blog/introducing-litefs/
- 8organicbits 3y agoDo they have any customer success stories about sqlite? I've mostly seen (compelling and well written) marketing posts. Details from someone using the product are much more interesting.
- endisneigh 3y agoFrom what I can tell I haven’t seen anything on their end customers using it much
- ShadowBanThis01 3y agoWasn't there an article on here within the last week saying how SQLite is "perfect for the 'edge'?"
- endisneigh 3y agoThat was more of the vein of toy example/hypothetically
- ripley12 3y agoNot a ton of detail, but Tailscale uses it: https://tailscale.com/blog/database-for-2022/ https://tailscale.com/blog/database-for-2022/
- eyegor 3y agoYes, the sqlite defaults are quite terrible out of the box. I'm not sure why they never changed them, it will start choking at 5k inserts where other dbs can do 100x that (and so will sqlite in wal and a few other settings). Getting it to perform well in high traffic scenarios would be a lot of effort. I struggle to get it to be vaguely performant in embedded use cases and often roll my own poor man's version unless I really care about data integrity (which is rare).
- arp242 3y ago> I'm not sure why they never changed them Because SQLite is run in many different environments and scenarios, and what's terrible in one scenario is perfect for another. There are no defaults that will work for everyone. This also applies to MySQL, PostgreSQL, etc. but the range of scenarios for those is more limited (no embedded for example) so the defaults are a bit more tuned to what's suitable for your scenario.
- vore 3y agoIsn't it more rare to not care about data integrity? Being unsure what state your data is at any point in time does not seem like a safe scenario.
- MatthiasPortzel 3y agoApps aren’t divided into “high-traffic” and “toys.” There are plenty of use cases where you have a low-write server in a production environment, and SQLite would work fine there. If you need high write volume, then yes, the locking behavior means SQLite is not a good fit.
- chasil 3y agoSQLite can easily hit 15k INSERTs per minute or more (setting processor affinity to a single core helps drive the max rate up). However, if a process begins a transaction and then stalls, it halts all dml. I think performance can be good, as long as a competent schema design is in place. Allowing ad-hoc queries from less trusted users will surely tank performance.
- bob1029 3y ago> there are many interesting use cases for server side but they all seem to be toyish. > The locking behavior of SQLite is somewhat problematic unless you use WAL and even then not perfect SQLite with WAL and synchronous configured appropriately will insert a row in ~50uS on NVMe hardware. This is completely serialized throughput (i.e. the highest isolation level available in SQL Server, et. al.). At scale, this can be reasonably described as "inserting a billion rows per day". I have yet to witness a database engine with latency that can touch SQLite. For some applications, this is a go/no-go difference. Why eschew the majesty of SQL because you can't wait for a network hop? Bring that engine in process to help solve your tricky data problems. I'd much rather write SQL than LINQ if I have to join more than 2 tables. We've been exclusively using SQLite in production for a long time. Big, multi-user databases. Looking at migrating to SQL Server Hyperscale, but not because SQLite is slow or causing any technical troubles. We want to consolidate our installs into one physical place so we can keep a better eye on them as we grow. Fun fact: SQL Server "Hyperscale" is capped at 100MB/s on its transaction log. I have written to SQLite databases at rates far exceeding this.
- endisneigh 3y agoIs your application a networked one?
- bob1029 3y agoIt is a web app that has exclusive ownership over its SQLite databases. Exactly one SQLiteConnection instance per for the lifetime of the application.
- endisneigh 3y agoInteresting. Any more info or link?
- belinder 3y agoDoes that mean you have to bring the whole app down if you need to manually insert something in sql?
- deaddodo 3y agoThere are plenty of use cases of SQLite being used in production servers, usually for cache locality of small datasets (think more complex read-focused Redis use cases) or for direct data operations (transforms, for instance). That being said; even if it weren't usable in a web service space, does that make it any less reasonable of a database? That whole mentality sounds like a web developer centric one. Berkeley DB was used for decades for application and system databases, a field that SQLite largely replaced it in. And one that MySQL, Postgres, Oracle, etc are generally completely unsuited for. It's the same reason Microsoft offered MS Access for so long alongside MSSQL, until MSSQL had it's own decent embeddable option to deprecate it.
- hakfoo 3y agoI've been pushing to try SQLite as a "sacrificial memozation." Basically we have two tasks separated by 5-10 days. When we do the first task, we calculate a bunch of information as a "side effect". At the second task, we don't have that information and trying to reconstruct it without the original context is very slow, because a lot of it is dependent on temporal state-- what was happening 5-10 days ago. The other use case I'm eager to explore is as persistence of status data in a long-running process. Occasionally the task blows up halfway and although we can recover the functional changes, we lose the reporting data for the original run. If we save it in a SQLite database instead of just in-script data structures, we don't have to try to reverse engineer it anymore. In both cases, I like the idea of "everything's one file and we nuke it when we're done" rather than "deal with the hostile operations team to spin up MySQL infrastructure."
- storyinmemo 3y agoYou should look into temporal.io. Your workflow problem is highly suited to it.
- rootw0rm 3y agoredis could possibly work, too, depending on how you actually plan to do it
- belthesar 3y agoA super smart guy on my team at a previous job replaced around $100,000 of server and SAN hardware used for a(n) (admittedly incredibly absurdly designed, well before our time) analytics system built using MySQL filtered replication triggering stored procedures in this magical Rube Goldberg-ian dystopia with a 3 node Flask app running with 2 cores and 4 GB of RAM each performing the analytics work JIT. The app would use in-memory SQLite3 tables to perform the same work as the stored procedure operations, and cost about 50ms of extra time per request for a feature of the app that was rarely used. Admittedly, not high traffic like you asked, but one of my favorite uses of SQLite hands down.
- aseipp 3y agoHigh-volume single-writer + WAL mode is perfectly doable for a lot of applications, but you have to keep the concurrency model in mind. I think of it more like a really advanced data structure library than a "database" in that sense. An old product I worked on (which is still around) used (still uses?) SQLite for storing filesystem blocks that came from the paired backup software. A single database only contained the blocks backed up from a single disk (based on its disk UUID.) So, this is a perfect scenario since there's only one writer at a time because there's only one device and one backup on that device at a time. We could write blocks to SQLite fast enough to saturate the IOPS of the underlying device and filesystem containing the database. This was over 10 years ago. Very practical and durable and way better than anything we could do in house. Today, you can definitely do GB/s on NVMe class hardware with the right pragma settings and the right workload. So for certain classes of multi-tenant solutions I think it's not so bad, you can just naturally have a single writer to a single data store (or can otherwise linearize writes) and the raw performance will be excellent and more than enough.
- vimda 3y agoCloudflare's D1 is built on top of it https://blog.cloudflare.com/introducing-d1/ https://blog.cloudflare.com/introducing-d1/
- pronoiac 3y agoLooks like the line numbers were lost: https://github.com/mackyle/sqlite/blob/18cf47156abe94255ae1495ba2da84517dce6081/src/os.h#L65-L75 https://github.com/mackyle/sqlite/blob/18cf47156abe94255ae14... It's because McAfee started using SQLite, angry users would stumble upon the files, do a minimum of searching or thinking, and be furious at SQLite developers.
- sedatk 3y agoI wonder why users were angry about some files in their temp folder. Did McAfee fail to cleanup those files, or were they too big? Edit: more information here: https://www2.sqlite.org/cvstrac/wiki?p=McafeeProblem https://www2.sqlite.org/cvstrac/wiki?p=McafeeProblem Apparently, McAfee kept those files locked when it was using them, so the files couldn't be deleted and people got angry that they couldn't clean them up. Sounds like a loud minority to me.
- banana_giraffe 3y agoThere's some level of power user that will try to fix things, not understand what's going on, and yell at the world when they break things further. I used to have a popular freely available 3rd party DLL that was included in lots of software packages. Because I was silly, it had my email address in the metadata that'd show up if you clicked "Properties" in Explorer. I'd get plenty of emails from random people asking for and sometimes _demanding_ help with software I've never heard of. I'm sure if I had an easy to find website with my name and a forum on it, it'd be full of such angry comments.
- thakoppno 3y ago> There's some level of power user that will try to fix things, not understand what's going on, and yell at the world when they break things further. That appears to me to be quite like a Chesterton fence. Everyone encounters these eventually. https://en.m.wiktionary.org/wiki/Chesterton%27s_fence https://en.m.wiktionary.org/wiki/Chesterton%27s_fence
- giantrobot 3y ago
- ShadowBanThis01 3y agoLove it. Too bad there aren't enough Mac users to prompt a similar backlash against Macs littering every computer they visit on the network with .DS_Store and other turds.
- sltkr 3y agoThis isn't remotely comparable. Those .DS_Store files are created in arbitrary directories by the Apple file manager or something. The SQLite temp files are created in the OS-specific temporary directory (e.g. C:/Users/username/AppData/Local/Temp or whatever on Windows) which is specifically intended for that purpose. SQLite isn't doing anything wrong; that's where it's supposed to store temporary data that doesn't fit into memory. The problem is that virus scanners sometimes misclassify those temp files as belonging to malware apps, or sometimes they might be written by real malware apps, but even in the latter case, that only happens because the malware uses sqlite as a library. The malware isn't written by the SQLite authors, so complaining to them is pointless.
- deaddodo 3y ago> Those .DS_Store files are created in arbitrary directories by the Apple file manager or something. .DS_Store files come from Apple's file systems containing a separate data and resource fork. APFS can natively store the contents, but for foreign file systems/network shares, a .DS_Store file is created to store those attributes.
- remram 3y agoI never knew that. How come they end up in Git repositories then? They shouldn't be visible to Git running on a native Mac filesystem?
- Ndymium 3y agoThey are visible to programs, for example if you run `ls -a` you will see them on macOS too. I reckon they're only transparent to Finder (where you can't see them even if you're showing hidden files).
- codedokode 3y agoThis is a bad choice, I wouldn't understand that it needs to be read backwards. Why not use process's executable name instead?
- lopkeny12ko 3y agoDid you even open the link? Human readability was not a goal, in fact quite the opposite.
- codedokode 3y agoI meant use the name of the program that embeds SQLite, for example, McAfee, Google Chrome etc. This way the user could easily understand which program has created the files.
- djbusby 3y agoHow does it get that in a cross platform way? Or want if the program name has exotic characters?
- codedokode 3y agoI think that in 2023 every decent OS should provide a method to get executable name. And every decent filesystem supports exotic characters.
- arp242 3y agoWhy spend time and effort on all of that when applications can just configure it themselves if they want to?
- yjftsjthsd-h 3y agoIf it were possible to determine the program name in a way that was portable and not too painful, it would be a nice feature for the library to automatically set a better default, both to save work for devs using it and to save sqlite devs the hassle of their library getting blamed for things that aren't its fault. Now, I don't think those conditions are likely to be met, but that doesn't mean it wouldn't be nice if it were practical.
- cwillu 3y agoThe relevant snippet: /* ** Temporary files are named starting with this prefix followed by 16 random ** alphanumeric characters, and no file extension. They are stored in the ** OS's standard temporary file directory, and are deleted prior to exit. ** If sqlite is being embedded in another program, you may wish to change the ** prefix to reflect your program's name, so that if your program exits ** prematurely, old temporary files can be easily identified. This can be done ** using -DSQLITE_TEMP_FILE_PREFIX=myprefix_ on the compiler command line. ** ** 2006-10-31: The default prefix used to be "sqlite_". But then ** Mcafee started using SQLite in their anti-virus product and it ** started putting files with the "sqlite" name in the c:/temp folder. ** This annoyed many windows users. Those users would then do a ** Google search for "sqlite", find the telephone numbers of the ** developers and call to wake them up at night and complain. ** For this reason, the default name prefix is changed to be "sqlite" ** spelled backwards. So the temp files are still identified, but ** anybody smart enough to figure out the code is also likely smart ** enough to know that calling the developer will not help get rid ** of the file. */
- penguin_booze 3y agoOr the direct link thither: https://github.com/mackyle/sqlite/blob/18cf47156abe94255ae1495ba2da84517dce6081/src/os.h#L56-L75 https://github.com/mackyle/sqlite/blob/18cf47156abe94255ae14....
- bondant 3y agoOr the link to the official repository: https://www.sqlite.org/src/file?ci=trunk&name=src/os.h&ln=57 https://www.sqlite.org/src/file?ci=trunk&name=src/os.h&ln=57
- pyeri 3y ago>> So the temp files are still identified, but anybody smart enough to figure out the code is also likely smart enough to know that calling the developer will not help get rid of the file. Building illuminati lairs now, are we!
- thrdbndndn 3y agoI often search for weird files in my %userprofile% (there are a lot random ones) just out of curiosity, despite I know they're not malicious. It doesn't help that if you Google any filenames, or even any semi-obscure file extensions, there would always be plenty of blogspam articles saying they're "possible virus". And oftentimes, there is no legit article to say what they really are even if you try, if they're from some relatively less popular software.
- lionkor 3y agoCould put the file(s) on a linux machine and run `file` on them
- yjftsjthsd-h 3y agoI expect that'll usually just give you a lot of "data" (i.e. some binary format that file doesn't recognize)
- 2h 3y agocorrect URL is: https://github.com/sqlite/sqlite/blob/master/src/os.h https://github.com/sqlite/sqlite/blob/master/src/os.h
- throwaway09223 3y agoI have a related story: Around the year 2000 I was working operations in the NOC for WebTV (then owned by Microsoft). For those who don't know, WebTV was a little set-top box with a modem which would dial up on demand and provide a very basic web/chat/email experience on the TV. The box would call a 1800 number to figure out its own phone number, then re-dial on a local toll-free number with a local sub-contracted ISP. One of the services we had would periodically send a UDP datagram out to online clients to let them know they had new email. The settop box would then light up a little indicator light. Of course, sometimes the client would hang up. The IP might get allocated to a PC dialup user. And sometimes, that PC dialup user might be running a firewall that was popular back then, called BLACK ICE DEFENDER. BLACK ICE DEFENDER had all these (not so) cool features, the kind that semi-technical people love. For example, it would log ATTACKS. What are ATTACKS? Unrecognized traffic, of course. Sometimes the little UDP datagram for our "you have mail" service would be delivered to a PC user running BLACK ICE DEFENDER, which would register it as an ATTACK. It would then ever so helpfully look up the ARIN contact information to see who sent the errant datagram -- which had the NOC phone number. It would then tell the user "THIS ENTITY IS HACKING YOU" and imply that contacting them would be productive. Yes, you could pick up a phone and call the Microsoft NOC. Back then, the internet was a smaller place. My job was to check the NOC voicemail, which was reliably filled with very angry people. Often they would threaten that they've reported us to the FBI or somesuch, or that it confirmed some conspiracy theory or another. We played the good ones on speakerphone for entertainment. Good times. Doesn't happen anymore.
- qingcharles 3y agoGreat story :) The same year I was working on various online music stores using Microsoft's Windows Media DRM. This would cause a licensing window to pop up in Media Player all the time when the license was missing or expired for someone's music. We would get various complaints emailed to us, and being head developer sometimes the thornier ones would end up in my inbox, and my friendly ass would be kind enough to reply and try to figure them out. One time one of the senior execs was walking past my screen and peered over my shoulder to read a new email which said "EVERY MORNING YOU ARE ON MY COMPUTER GET OFF MY COMPUTER!!!". The exec leaned over, typed "GO FUCK YOURSELF" and hit reply. The benefits of being the boss...
- qalmakka 3y agoI don't know, the more time passes the more I convince myself that the net benefits of antivirus software do not (an maybe never) exceed their downsides. In decades I've heard so many stories about AV software behaving suspiciously, using borderline shady tricks to monitor user activity, causing severe performance degradation, etc
- tempestn 3y agoThere was a time when they made sense, but it's been many years since the benefits of running a third party antivirus program outweighed the drawbacks.
- runlevel1 3y agoHow about that time Avast gave you RCE by simply adding HTML to the CN field of an invalid certificate?[^1] Or when TrendMicro added an unauthenticated listener that would exec anything you sent it?[^2] [1]: https://www.theregister.com/2015/10/06/google_zero_hacker_reports_remote_exec_hole_in_avast_antivirus/ https://www.theregister.com/2015/10/06/google_zero_hacker_re... [2]: https://bugs.chromium.org/p/project-zero/issues/detail?id=693&q=mitm&can=1 https://bugs.chromium.org/p/project-zero/issues/detail?id=69...
- ilyt 3y agoI remember our Java developers being very unhappy with ESET requirement that made the Linux boxes compile performance literally halve
- dspillett 3y ago> (an maybe never) exceed their downsides There were certainly times when they were necessary: when Windows had nothing built-in to defend itself, and for a time after then when those built-in features were crap. Those times are pretty much over now IMO. I'd go as far as to suggest that the market is now an attempt at a protection racket and hardware hawkers are complicit: things come pre-installed on new laptops and make very misleading claims about what might happen if you uninstall them instead of subscribing after the free trial period (ref: Dad got a new laptop recently, I went through and removed all the junk included with it, I can see why people with little technical experience might just pay up).
- withinrafael 3y agoSimilar story: Friend and I put out a kernel driver (uxstyle.sys) that would patch Microsoft's theming digital signature checks. It was free, buggy, and bugchecked the OS on upgrade. It was unsurprisingly added to compatibility blocks in Windows. I fixed the bug and asked Microsoft to loosen the block (version X and below). Microsoft refused citing a EULA violation. Valid or not, I renamed the driver to elytsxu.sys to circumvent their check and the app worked well enough until third-party theming fell out of favor.
- terinjokes 3y agoOn the rare occasion I install a Windows XP VM nowadays, I still patch uxstyle.dll and install my favorite style: DeMx[0]. [0]: https://www.deviantart.com/xandaman/art/DeMx-29075379 https://www.deviantart.com/xandaman/art/DeMx-29075379
- garganzol 3y agoWhy not to use sub-directories inside a temporary directory instead of file name prefixes?