[sqlite] WAL option doesn't release handles (v3.7.13)
Hi all, I wrote a simple C++ program that spawns 10 threads, each thread - creates an SQLite handle (threads don't share connections) - opens a database (common to all the threads), or creates it (with Write-Ahead Log option) if open fails - creates a table on the database - goes into an infinite loop in which: o opens a connection with the SQLITE_OPEN_NOMUTEX flag set o sets timeout to 1 minute o does a BEGIN TRANSACTION o writes 2 rows o commits the transaction o closes the connection I found out that the program 'leaks' about 2 handles every 5 minutes. A colleague and I found that the SQLite method 'winShmMap' is storing the handles in the array pShmNode-aRegion (line 35488). This array should be cleared in 'winShmPurge' but in our code that number of connection references (line 35233) is never 0, se we never call it. Is there a way to release handles even if a connection is still opened? Maybe a PRAGMA that needs to be set or a function call? You can find the Visual Studio 2010 project used for testing in the following link: https://docs.google.com/open?id=0B1Z2DQ4ECc2OWFRvbVpGYnpDamM Thank you very much for your help. Best Regards, Andrea Guardascione _ This email is intended only for the use of the individual or entity named above and may contain information that is confidential and/or privileged. If you are not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this Email is strictly prohibited. If you have received this Email in error, please notify us immediately by return email or telephone +61 2 9966 1066 and destroy the original message. Thank You ___ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Size of WAL file and cache
Hi, I was wondering what the size (in MB) of a WAL file and of a shared/private cache is in SQLite. I'm running SQLite on an Android smartphone so space (RAM/HDD) is scarce. 1. I understand that the WAL file is stored on the disk. Provided auto-checkpointing, how big would the WAL file grow? It says in the documentation: 1000 pages. How big is a page? Also, does WAL has any (significant) memory consumption (per connection/thead, or dependent on the WAL file size)? 2. I'm guessing a SQLite cache (connection private [SQLITE_OPEN_PRIVATECACHE] or shared [SQLITE_OPEN_SHAREDCACHE]) is stored in memory only (i.e. not on the disk), right? What's its size in memory? Or, on what factors does this size depend on? Thanks Sebastian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] .mode tabs does not work
.mode tabs does not work: sqlite3 EOF .mode tabs .show EOF echo: off explain: off headers: off mode: list nullvalue: output: stdout separator: \t stats: off width: sqlite3 --version 3.7.14 2012-09-03 15:42:36 c0d89d4a9752922f9e367362366efde4f1b06f2a ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] .mode tabs does not work
.mode tabs does not work: echo -ne \\t f sqlite3 --version sqlite3 EOF create table t(a text, b text); .mode tabs .import f t EOF 3.7.14 2012-09-03 15:42:36 c0d89d4a9752922f9e367362366efde4f1b06f2a Error: f line 1: expected 2 columns of data but found 1 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Seemingly random Access Violation errors
I'm currently trying to write an app for the compact framework (3.5) using an sqlite database in c#. The application is occasionally throwing up odd Access Violation errors, crashing the application. Getting precious little from the crash dumps produced but from experimentation it seems that the error occurs when the background thread accesses the database while the foreground thread switches between forms. (Increasing the speed of the background thread and continually opening and closing forms in the foreground make the crash more frequent). Does anyone know of any issues with sqlite/garbage collection/the compact framework that could cause this? Any common coding mistakes I could be making in declaring the connections that could cause this? Have tried two versions of SQLite. 1.0.56.0 and 1.0.82.0 but both have same results. Switching the database to sqlce seems to fix the issue but really don't want to do that if possible. Also read one old comment on a support site that suggested it was essential to keep sqliteconnections in using blocks to avoid a similar issue but this has had no effect in this case. Don't know if the following helps: ExceptionCode: 0xc005 ExceptionAddress: 0x03f6bba8 Reading: 0x0010 Faulting module: coredll.dll Offset: 0x00022ba8 at UnsafeNativeMethods.sqlite3_close_interop(IntPtr db) at SQLiteBase.CloseConnection(SQLiteConnectionHandle db) at SQLiteConnectionHandle.ReleaseHandle() at CriticalHandle.Cleanup() at CriticalHandle.Dispose(Boolean disposing) at CriticalHandle.Dispose() at SQLite3.Close() at SQLiteConnection.Close() Any input welcome. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How much disk space is required to checkpoint?
On 26 Sep 2012, at 5:42am, Dan Kennedy danielk1...@gmail.com wrote: So when SQLite requires a temporary file, it normally creates and opens a file with a randomized name in /tmp then calls unlink() on it immediately - before it starts using the file. Goodness me. Okay, I wasn't aware that SQLite used this trick. Thanks for the correction. That certainly does tie in with what the OP reported. There are problems with doing this under some versions of Unix/Linux, and especially variants like Android. For instance, another user/app can make a file with the same name. Or the system may crash while the file is still open. Is there any chance that the use of this trick can be discontinued ? I would believe that any Unix user who knows to look in /tmp could deal with what they found there. I would say that the file should be deleted normally when SQLite is finished with it rather than this trick being used. Of course, there may be a specific reason why the programmers of SQLite decided to do this. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] .mode tabs does not work
somebody wrote: .mode tabs does not work: sqlite3 EOF .mode tabs .show EOF ... separator: \t Obviously, it does work. echo -ne \\t f sqlite3 EOF create table t(a text, b text); .mode tabs .import f t .mode sets the *output* mode. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How much disk space is required to checkpoint?
Simon Slavin wrote: On 26 Sep 2012, at 5:42am, Dan Kennedy danielk1...@gmail.com wrote: So when SQLite requires a temporary file, it normally creates and opens a file with a randomized name in /tmp then calls unlink() on it immediately - before it starts using the file. [...] There are problems with doing this under some versions of Unix/ Linux, and especially variants like Android. For instance, another user/app can make a file with the same name. Andorid doesn't have a shared /tmp. In any case, temporary files are opened with O_CREAT|O_EXCL, so two apps will never accidentally open each other's file. Furthermore, by deleting the file immediately after creating it, the time window in which a conflict is possible is made as short as possible. Or the system may crash while the file is still open. How would a file that clogs up /tmp be preferrable to some unnamed data that will be automatically removed from the file system by the fsck after the crash? Is there any chance that the use of this trick can be discontinued ? This is not a trick, it's a widely used Unix idiom. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How much disk space is required to checkpoint?
On Wed, Sep 26, 2012 at 5:55 AM, Simon Slavin slav...@bigfraud.org wrote: On 26 Sep 2012, at 5:42am, Dan Kennedy danielk1...@gmail.com wrote: So when SQLite requires a temporary file, it normally creates and opens a file with a randomized name in /tmp then calls unlink() on it immediately - before it starts using the file. Goodness me. Okay, I wasn't aware that SQLite used this trick. Thanks for the correction. That certainly does tie in with what the OP reported. There are problems with doing this under some versions of Unix/Linux, and especially variants like Android. I wonder what problems do you have in mind? For instance, another user/app can make a file with the same name. If it doesn't happen in between file creation and unlinking then there's absolutely no problem in this -- this app will see a different file, not the one that SQLite sees. And with name randomization and extremely short time between creation and unlinking there virtually no chances to have any problems of this type. Or the system may crash while the file is still open. What's the problem with this? File will be deleted by the file system after reboot. To the contrary this scenario will give you problems if you don't do unlinking. Is there any chance that the use of this trick can be discontinued ? Hopefully you have some compelling reason for suggesting that. Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How much disk space is required to checkpoint?
On 26 Sep 2012, at 2:36pm, Clemens Ladisch clem...@ladisch.de wrote: Simon Slavin wrote: On 26 Sep 2012, at 5:42am, Dan Kennedy danielk1...@gmail.com wrote: So when SQLite requires a temporary file, it normally creates and opens a file with a randomized name in /tmp then calls unlink() on it immediately - before it starts using the file. [...] There are problems with doing this under some versions of Unix/ Linux, and especially variants like Android. For instance, another user/app can make a file with the same name. Andorid doesn't have a shared /tmp. In any case, temporary files are opened with O_CREAT|O_EXCL, so two apps will never accidentally open each other's file. Furthermore, by deleting the file immediately after creating it, the time window in which a conflict is possible is made as short as possible. Allow me to present an alternative point of view. If filling up your filespace is causing you to crash, it's easier to understand what's going on if you can see the files which are filling it up. Running out of disk space when you can't see huge files makes it difficult to debug. With an unlinked file I would find it hard to figure out what was filling my hard disk causing me to crash. Avoiding filename clashes can be done by creating files with random or time-related elements to their names. It's less of a problem. Or the system may crash while the file is still open. How would a file that clogs up /tmp be preferrable to some unnamed data that will be automatically removed from the file system by the fsck after the crash? Unix deletes the contents of /tmp at boot time. That's why it's special. In contrast, using unlink() can cause some chaos including filespace hogs turning up in lost+found -- the sort of thing that might cause problems that a mundane user might never understand. Is there any chance that the use of this trick can be discontinued ? This is not a trick, it's a widely used Unix idiom. It's widely used outside /tmp. It's an alternative to using /tmp, especially on platforms which don't support a /tmp. I don't think the two techniques are often used with the same file. If you're using /tmp then your sysadmin/programmer needs to know what's suddenly killing freespace on their working volume and looking in /tmp is a great way to find everything that might be a problem. But if the file is unlinked they'll never see it. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How much disk space is required to checkpoint?
On Wed, Sep 26, 2012 at 01:55:33PM +0100, Simon Slavin scratched on the wall: There are problems with doing this under some versions of Unix/Linux, and especially variants like Android. This technique is almost as old as Unix itself. If some OS versions can't deal with it, they're buggy. I would not consider it a trick, but rather a standard best-practice for dealing with temp files. For instance, another user/app can make a file with the same name. And how, exactly, would keeping the file around fix this? If the file is properly unlinked, it doesn't exist in the directory. It has no name, so it is impossible to create another file with the same name. Besides, if the you kept the file around, you'd have the exact same problem. There are also APIs to get unique temp file names. If an application isn't using them, its buggy. SQLite also uses the string etilqs in temp files to avoid collisions. Or the system may crash while the file is still open. That's why file systems fsck when they remount. There are all kinds of things that can go wrong with a file system when a machine crashes. An application crash is no big deal, however. I would believe that any Unix user who knows to look in /tmp could deal with what they found there. No offense to the professional system administrators out there, but I think you're vastly over estimating the average sys-admin, especially when they're a teenager with a new MacBook and just enough knowledge of Unix to be dangerous. Richard made a post about it some months ago. SQLite is embedded in a lot of applications. Some of them are buggy, crashy applications. The reason SQLite uses etilqs rather than sqlite in the temp file names (as it used to) is because of such clueless people using bad software find the temp files and then go off ranting about how SQLite sucks and needs to be fixed. Flipping the name around weeds out enough Google searches to avoid such people. Of course, emails like this, that use the string, don't help the situation. PLEASE NOTE: if you found this message via a Google search at some future date, please re-read the previous paragraph until you understand the full ramifications of what it is saying. Don't be clueless. I would say that the file should be deleted normally when SQLite is finished with it rather than this trick being used. Of course, there may be a specific reason why the programmers of SQLite decided to do this. Because it is the standard, time-tested way of doing this kind of thing on Unix-- for a lot of very good reasons. The file cannot be open by another process, period. Even a root process. So it provides security and isolation from stupid programs doing dumb things. It avoids file name collisions, as the file doesn't exist in the file tree. Deleting the file means that as soon as that SQLite process exits-- no matter how or why-- the file will be cleaned up and removed. That's important for files put outside of /tmp. It can be important for files inside /tmp... most systems only clean /tmp on reboot, and that can be months, if not years, on many Unix systems. Some don't clean /tmp at all. The create and unlink pattern is so common, many UNIX systems have a tmpfile() or similar library call to do the whole thing... create a unique file in /tmp, open it, unlink it, and return the file pointer. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How much disk space is required to checkpoint?
On 26 Sep 2012, at 3:07pm, Jay A. Kreibich j...@kreibi.ch wrote: Because it is the standard, time-tested way of doing this kind of thing on Unix-- for a lot of very good reasons. Okay. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Seemingly random Access Violation errors
Matthew Dumbleton wrote: at UnsafeNativeMethods.sqlite3_close_interop(IntPtr db) at SQLiteBase.CloseConnection(SQLiteConnectionHandle db) at SQLiteConnectionHandle.ReleaseHandle() at CriticalHandle.Cleanup() at CriticalHandle.Dispose(Boolean disposing) at CriticalHandle.Dispose() at SQLite3.Close() at SQLiteConnection.Close() Are you using a new SQLiteConnection object instance on each thread? -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Seemingly random Access Violation errors
Yes. Each time either thread connects a new sqliteconnection instance is created. I use a mutex protected static method through which both threads do their database connections in order to ensure database is accessed by only one thread at a time. From: Joe Mistachkin Sent: 26/09/2012 20:54 To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Seemingly random Access Violation errors Matthew Dumbleton wrote: at UnsafeNativeMethods.sqlite3_close_interop(IntPtr db) at SQLiteBase.CloseConnection(SQLiteConnectionHandle db) at SQLiteConnectionHandle.ReleaseHandle() at CriticalHandle.Cleanup() at CriticalHandle.Dispose(Boolean disposing) at CriticalHandle.Dispose() at SQLite3.Close() at SQLiteConnection.Close() Are you using a new SQLiteConnection object instance on each thread? -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Seemingly random Access Violation errors
Matthew Dumbleton wrote: at UnsafeNativeMethods.sqlite3_close_interop(IntPtr db) at SQLiteBase.CloseConnection(SQLiteConnectionHandle db) at SQLiteConnectionHandle.ReleaseHandle() at CriticalHandle.Cleanup() at CriticalHandle.Dispose(Boolean disposing) at CriticalHandle.Dispose() at SQLite3.Close() at SQLiteConnection.Close() Yes. Each time either thread connects a new sqliteconnection instance is created. I use a mutex protected static method through which both threads do their database connections in order to ensure database is accessed by only one thread at a time. I just noticed that the method signature for CloseConnection in the above stack trace is outdated. The most recent versions have the following signature: internal static void CloseConnection(SQLiteConnectionHandle hdl, IntPtr db) Could you please try using the latest release version (1.0.82.0) or the latest code in trunk and see if that clears the issue. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Size of WAL file and cache
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 26/09/12 01:15, Sebastian Krysmanski wrote: 1. I understand that the WAL file is stored on the disk. During a transaction the old pages and the new pages will both be present at some point no matter what mode is used. Consequently you should expect maximum usage to be around the size of your existing data plus twice the size of the changes you are undertaking while usage outside of a transaction will be roughly existing data plus changes. Worst case would be something like changing a value on every single page. Best case would be adding new data with no indices. It says in the documentation: 1000 pages. How big is a page? 1kb default. http://www.sqlite.org/pragma.html#pragma_page_size Also, does WAL has any (significant) memory consumption (per connection/thead, or dependent on the WAL file size)? There is some but not really significant. 2. I'm guessing a SQLite cache (connection private [SQLITE_OPEN_PRIVATECACHE] or shared [SQLITE_OPEN_SHAREDCACHE]) is stored in memory only (i.e. not on the disk), right? What's its size in memory? Or, on what factors does this size depend on? SharedCache is about sharing the cache memory between connections in the same process. It is unlikely you'll ever want to do this, or even benefit from it if you did use it. The default cache size is 2MB from sqlite.org - - I haven't checked to see what value Android changed it to. Many of the system apps, and a lot of others apps are using SQLite behind the scenes. You are not the first developer by a long shot. Remember that the first Android phones allocated 16MB per process for everything. I recommend you just go ahead and use SQLite as the Android system intends you to. Actually I recommend you expose things as a ContentProvider and use SQLite behind the scenes. It is trivial to use SQLite on Android. Note that the library code does some things behind your back such as dealing with collation registrations to make sorting consistent. (If you ever .dump a database that was created on Android you can see that.) Start with this class that helps manage the schema on your database, as well as creating the database in the right place: http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAlBjbWgACgkQmOOfHg372QQUmACfTE4NyVslAFHDkNfiwPvgBP6w uO0AoJ/YFlZ1a2y0ne3Lk0GCJy781WqG =48fo -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Seemingly random Access Violation errors
Sorry, should have highlighted this better in my initial message. Started out using 1.0.56.0 and changed to 1.0.82.0, however problem remains. From: Joe Mistachkin Sent: 26/09/2012 21:42 To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Seemingly random Access Violation errors Matthew Dumbleton wrote: at UnsafeNativeMethods.sqlite3_close_interop(IntPtr db) at SQLiteBase.CloseConnection(SQLiteConnectionHandle db) at SQLiteConnectionHandle.ReleaseHandle() at CriticalHandle.Cleanup() at CriticalHandle.Dispose(Boolean disposing) at CriticalHandle.Dispose() at SQLite3.Close() at SQLiteConnection.Close() Yes. Each time either thread connects a new sqliteconnection instance is created. I use a mutex protected static method through which both threads do their database connections in order to ensure database is accessed by only one thread at a time. I just noticed that the method signature for CloseConnection in the above stack trace is outdated. The most recent versions have the following signature: internal static void CloseConnection(SQLiteConnectionHandle hdl, IntPtr db) Could you please try using the latest release version (1.0.82.0) or the latest code in trunk and see if that clears the issue. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Seemingly random Access Violation errors
Matthew Dumbleton wrote: Sorry, should have highlighted this better in my initial message. Started out using 1.0.56.0 and changed to 1.0.82.0, however problem remains. I'm unable to reproduce the issue here using the latest version. Could you provide some sample C# code that demonstrates the issue? -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
