Re: [sqlite] Still getting "Insertion failed because database isfull." errors
On 4/18/07, Dan Kennedy <[EMAIL PROTECTED]> wrote:If the win32 SetFilePointer() function fails (used to position the "pointer" at a given file offset, which SQLite does as part of a SELECT) SQLite assumes the reason is that the disk is full and returns SQLITE_FULL. This is probably what's happening here - SetFilePointer() is saying "the file-system is gone!" and SQLite is misinterpreting it. Probably SQLite should return SQLITE_IOERR instead. True: the error message really through me from the beginning because you could easily surmise that the database was NOT full. So why can't you just handle this in the application? Open and close the database connection when an SQLITE_FULL occurs. If the device is really shutting down (not starting up) then the second attempt to open will fail. Or just try every couple of seconds from that point on. Dan. This was one of the suggestions we came up with, however there are dozens of places in lots of different classes and files that would need this code added. By comparison, moving the database to the internal memory store and backing it up on the Card took about 10 lines of code in one class. And others have suggested that it will perform faster from internal memory: if so, there's an added bonus. So far, my limited testing here in the office (disconnected, of course) has not produced any error. I'm going to the field tomorrow with our field tester to confirm the solution. For the first time in a long time I am confident that what we are doing is going to work. Thanks to everyone for their input and patience during this thread. I'll report back when I know more. -- Joel Cochran
Re: [sqlite] Still getting "Insertion failed because database isfull." errors
On Wed, 2007-04-18 at 10:06 -0400, Joel Cochran wrote: > OK, then I won't be worrying about Transactions for SELECT statements, it > doesn't really apply to our application. > > And some additionaly confirmation that Christian seems to have been right on > key: according to the problems reported at the System.Data.SQLite forums, > the problem is most likely due to the retaining of an ope Connection against > the database residing on removable media. When the system returns, the > "pointer" to the media is not guaranteed to work again. In other words, > every time the system shuts down, there is the potential for losing database > connectivity. The recommended solution is to move the database to internal > memory and use the CF card as a backup device. If the win32 SetFilePointer() function fails (used to position the "pointer" at a given file offset, which SQLite does as part of a SELECT) SQLite assumes the reason is that the disk is full and returns SQLITE_FULL. This is probably what's happening here - SetFilePointer() is saying "the file-system is gone!" and SQLite is misinterpreting it. Probably SQLite should return SQLITE_IOERR instead. So why can't you just handle this in the application? Open and close the database connection when an SQLITE_FULL occurs. If the device is really shutting down (not starting up) then the second attempt to open will fail. Or just try every couple of seconds from that point on. Dan. > This also confirms why I can't replicate the problem in DEBUG: the device > never sleeps and the connection is never lost. > > I'll keep the list posted. > > Joel > > On 4/18/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > > > "Joel Cochran" <[EMAIL PROTECTED]> wrote: > > > should I be using > > > Transactions for SELECT statements? > > > > The only reason to use a transaction around SELECT statements > > is if you want to make sure that the database is not changed > > by another process in between consecutive SELECTs. > > > > It used to be the case that putting multiple SELECTs within > > a transaction would be slightly faster since doing so avoided > > a cache flush after each statement. But as of version 3.3.16 > > that cache flush is avoided regardless of whether or not > > transactions are used so performance should no longer be a > > factor. > > > > -- > > D. Richard Hipp <[EMAIL PROTECTED]> > > > > > > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > > > > - > > > > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Still getting "Insertion failed because database isfull." errors
Another option is to change the SQLite.NET wrapper to automatically retry on SQLITE_FULL error similar to the way it handles a schema error. Then it would be transparent to your app. It would have to close and reopen the connection of course, not just retry, but still the solution is manageable and can be encapsulated within the wrapper. Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Joel Cochran [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 18, 2007 10:55 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Still getting "Insertion failed because database isfull." errors OK, I understand. This was my initial instinct, that it had to be coming from the Database, which was why I contacted DRH. His reponse was basically that my symptoms didn't match a problem in SQLite, given the other information at hand, and he is correct. So really, it isn't SQLite's problem OR the wrappers problem: it is the way the device handles its handles regarding the removable media. I'm testing it now with moving the database to internal memory. Thanks, Joel - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Still getting "Insertion failed because database isfull." errors
> -Original Message- > From: Christian Schwarz [mailto:[EMAIL PROTECTED] > Sent: Wednesday, April 18, 2007 7:25 AM > To: sqlite-users@sqlite.org > Subject: AW: [sqlite] Still getting "Insertion failed because database > isfull." errors > > > the database residing on removable media. When the system returns, > the > > "pointer" to the media is not guaranteed to work again. In other > words, > > The file handle remains perfectly valid when the media has not been > removed or changed. Besides, I've observed that sometimes the media is > not accessible at all after an application was trying to use one of the > open file handles while the media is being remounted. Maybe that's just > a sign for a poorly written driver and thus device dependent... It is in fact, device-dependent. Some hardware manufacturers have more durable remounting infrastructure that enables your file handles to survive a resume from standby, and some don't. I always recommend short-lived connections on a mobile device. Get in and get out quickly, and don't keep anything in memory or active longer than it needs to be. Robert - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Still getting "Insertion failed because database isfull." errors
I was able to recreate this problem on the testing device (but NOT in DEBUG, of course). I pulled up the application, did some operations, and then let the device go to sleep. I then powered back up, and the first operation I tried to do threw the error. Joel On 4/18/07, Christian Schwarz <[EMAIL PROTECTED]> wrote: > the database residing on removable media. When the system returns, the > "pointer" to the media is not guaranteed to work again. In other words, The file handle remains perfectly valid when the media has not been removed or changed. Besides, I've observed that sometimes the media is not accessible at all after an application was trying to use one of the open file handles while the media is being remounted. Maybe that's just a sign for a poorly written driver and thus device dependent... Greetings, Christian - To unsubscribe, send email to [EMAIL PROTECTED] - -- Joel Cochran
Re: [sqlite] Still getting "Insertion failed because database isfull." errors
OK, I understand. This was my initial instinct, that it had to be coming from the Database, which was why I contacted DRH. His reponse was basically that my symptoms didn't match a problem in SQLite, given the other information at hand, and he is correct. So really, it isn't SQLite's problem OR the wrappers problem: it is the way the device handles its handles regarding the removable media. I'm testing it now with moving the database to internal memory. Thanks, Joel On 4/18/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote: By wrapper I think we are all talking about the managed provider, SQLite.NET. This wrapper is not generating the error message--it is being reported to the wrapper by SQLite core. It is possible that the wrapper is contributing to the problem, but it is not generating the error. The SQLite3.Reset() method in the wrapper calls sqlite_reset(), handles the schema and locked errors, and then just passes along other errors. There's nothing in the code that can actually generate the error you reported. HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Joel Cochran [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 18, 2007 9:25 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Still getting "Insertion failed because database isfull." errors OK, now I am confused... On 4/18/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote: > > > One thing to note is that the SQLite.NET wrapper by default issues all > transactions as "BEGIN IMMEDIATE" so if you're running the SELECT within a > transaction it will be within the context of an exclusive transaction > (that's what BEGIN IMMEDIATE means, right?). I plead ignorance: I don't actually know that much about Transactions. I had asked the list before, and it seems correct to ask again: should I be using Transactions for SELECT statements? They make sense for things that change the database, but what would be the reason when reading? I am currently NOT running the SELECTs inside Transactions. You can override this by using the wrapper-specific > BeginTransaction(deferred) override but it is not accessible if using the > DbFactory standard interface. > > I completely agree with Dan that there is no way the wrapper is generating > this error message, however behavior in the wrapper such as the above > could > be contributing to it. By "Wrapper" are we talking about the same thing? DRH said that it IS the wrapper causing the problem, if by the wrapper we mean the .NET Managed Provider... if not, then I am just confused... HTH, > > Sam > Thanks Sam, -- Joel Cochran - To unsubscribe, send email to [EMAIL PROTECTED] - -- Joel Cochran
Re: [sqlite] Still getting "Insertion failed because database isfull." errors
Or you can close the database connection if it's idle for a little bit (less than it takes for it to sleep)? On 4/18/07, Joel Cochran <[EMAIL PROTECTED]> wrote: OK, then I won't be worrying about Transactions for SELECT statements, it doesn't really apply to our application. And some additionaly confirmation that Christian seems to have been right on key: according to the problems reported at the System.Data.SQLite forums, the problem is most likely due to the retaining of an ope Connection against the database residing on removable media. When the system returns, the "pointer" to the media is not guaranteed to work again. In other words, every time the system shuts down, there is the potential for losing database connectivity. The recommended solution is to move the database to internal memory and use the CF card as a backup device. This also confirms why I can't replicate the problem in DEBUG: the device never sleeps and the connection is never lost. I'll keep the list posted. Joel On 4/18/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > "Joel Cochran" <[EMAIL PROTECTED]> wrote: > > should I be using > > Transactions for SELECT statements? > > The only reason to use a transaction around SELECT statements > is if you want to make sure that the database is not changed > by another process in between consecutive SELECTs. > > It used to be the case that putting multiple SELECTs within > a transaction would be slightly faster since doing so avoided > a cache flush after each statement. But as of version 3.3.16 > that cache flush is avoided regardless of whether or not > transactions are used so performance should no longer be a > factor. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > -- Joel Cochran
Re: [sqlite] Still getting "Insertion failed because database isfull." errors
OK, then I won't be worrying about Transactions for SELECT statements, it doesn't really apply to our application. And some additionaly confirmation that Christian seems to have been right on key: according to the problems reported at the System.Data.SQLite forums, the problem is most likely due to the retaining of an ope Connection against the database residing on removable media. When the system returns, the "pointer" to the media is not guaranteed to work again. In other words, every time the system shuts down, there is the potential for losing database connectivity. The recommended solution is to move the database to internal memory and use the CF card as a backup device. This also confirms why I can't replicate the problem in DEBUG: the device never sleeps and the connection is never lost. I'll keep the list posted. Joel On 4/18/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: "Joel Cochran" <[EMAIL PROTECTED]> wrote: > should I be using > Transactions for SELECT statements? The only reason to use a transaction around SELECT statements is if you want to make sure that the database is not changed by another process in between consecutive SELECTs. It used to be the case that putting multiple SELECTs within a transaction would be slightly faster since doing so avoided a cache flush after each statement. But as of version 3.3.16 that cache flush is avoided regardless of whether or not transactions are used so performance should no longer be a factor. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - -- Joel Cochran
RE: [sqlite] Still getting "Insertion failed because database isfull." errors
So as of the next version of SQLite, transactions on reads for performance will no longer be necessary.. That's great! Thanks, Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 18, 2007 9:32 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Still getting "Insertion failed because database isfull." errors "Joel Cochran" <[EMAIL PROTECTED]> wrote: > should I be using > Transactions for SELECT statements? The only reason to use a transaction around SELECT statements is if you want to make sure that the database is not changed by another process in between consecutive SELECTs. It used to be the case that putting multiple SELECTs within a transaction would be slightly faster since doing so avoided a cache flush after each statement. But as of version 3.3.16 that cache flush is avoided regardless of whether or not transactions are used so performance should no longer be a factor. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Still getting "Insertion failed because database isfull." errors
By wrapper I think we are all talking about the managed provider, SQLite.NET. This wrapper is not generating the error message--it is being reported to the wrapper by SQLite core. It is possible that the wrapper is contributing to the problem, but it is not generating the error. The SQLite3.Reset() method in the wrapper calls sqlite_reset(), handles the schema and locked errors, and then just passes along other errors. There's nothing in the code that can actually generate the error you reported. HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Joel Cochran [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 18, 2007 9:25 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Still getting "Insertion failed because database isfull." errors OK, now I am confused... On 4/18/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote: > > > One thing to note is that the SQLite.NET wrapper by default issues all > transactions as "BEGIN IMMEDIATE" so if you're running the SELECT within a > transaction it will be within the context of an exclusive transaction > (that's what BEGIN IMMEDIATE means, right?). I plead ignorance: I don't actually know that much about Transactions. I had asked the list before, and it seems correct to ask again: should I be using Transactions for SELECT statements? They make sense for things that change the database, but what would be the reason when reading? I am currently NOT running the SELECTs inside Transactions. You can override this by using the wrapper-specific > BeginTransaction(deferred) override but it is not accessible if using the > DbFactory standard interface. > > I completely agree with Dan that there is no way the wrapper is generating > this error message, however behavior in the wrapper such as the above > could > be contributing to it. By "Wrapper" are we talking about the same thing? DRH said that it IS the wrapper causing the problem, if by the wrapper we mean the .NET Managed Provider... if not, then I am just confused... HTH, > > Sam > Thanks Sam, -- Joel Cochran - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Still getting "Insertion failed because database isfull." errors
OK, now I am confused... On 4/18/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote: One thing to note is that the SQLite.NET wrapper by default issues all transactions as "BEGIN IMMEDIATE" so if you're running the SELECT within a transaction it will be within the context of an exclusive transaction (that's what BEGIN IMMEDIATE means, right?). I plead ignorance: I don't actually know that much about Transactions. I had asked the list before, and it seems correct to ask again: should I be using Transactions for SELECT statements? They make sense for things that change the database, but what would be the reason when reading? I am currently NOT running the SELECTs inside Transactions. You can override this by using the wrapper-specific BeginTransaction(deferred) override but it is not accessible if using the DbFactory standard interface. I completely agree with Dan that there is no way the wrapper is generating this error message, however behavior in the wrapper such as the above could be contributing to it. By "Wrapper" are we talking about the same thing? DRH said that it IS the wrapper causing the problem, if by the wrapper we mean the .NET Managed Provider... if not, then I am just confused... HTH, Sam Thanks Sam, -- Joel Cochran
Re: [sqlite] Still getting "Insertion failed because database isfull." errors
"Joel Cochran" <[EMAIL PROTECTED]> wrote: > should I be using > Transactions for SELECT statements? The only reason to use a transaction around SELECT statements is if you want to make sure that the database is not changed by another process in between consecutive SELECTs. It used to be the case that putting multiple SELECTs within a transaction would be slightly faster since doing so avoided a cache flush after each statement. But as of version 3.3.16 that cache flush is avoided regardless of whether or not transactions are used so performance should no longer be a factor. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Still getting "Insertion failed because database isfull." errors
One thing to note is that the SQLite.NET wrapper by default issues all transactions as "BEGIN IMMEDIATE" so if you're running the SELECT within a transaction it will be within the context of an exclusive transaction (that's what BEGIN IMMEDIATE means, right?). You can override this by using the wrapper-specific BeginTransaction(deferred) override but it is not accessible if using the DbFactory standard interface. I completely agree with Dan that there is no way the wrapper is generating this error message, however behavior in the wrapper such as the above could be contributing to it. HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Dan Kennedy [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 18, 2007 1:25 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Still getting "Insertion failed because database isfull." errors > At first I thought this had solved the problem, because all in house testing > runs beautifully. However, as soon as the device is sent to the field, the > error starts again. Unfortunately, it means that I have never been able to > catch this in debug. I did, however, change the error reporting a little > and got some more information. The SQLiteException I am not getting > includes this information: > > Insertion failed because the database is full That message is from the wrapper. > database or disk is full And the above is from sqlite3. The corresponding return code is SQLITE_FULL. Search source file "os_win.c" for where SQLITE_FULL errors can be generated - there's only a couple of places. Odds on it's one of them. Looks like on windows, any error writing or seeking a file is reported as SQLITE_FULL. > at System.Data.SQLite.SQLite3.Reset() > at System.Data.SQLite.SQLite3.Step() > at System.Data.SQLite.SQLiteDataReader.NextResult() > at System.Data.SQLite.SQLiteDataReader.ctor() > at System.Data.SQLite.SQLiteCommand.ExecuteReader() > at System.Data.SQLite.SQLiteCommand.ExecuteDbDataReader() > at ... etc etc So is this really a SELECT? Probably good to check that. If it is a SELECT, why would it be filling up the database? Is it rolling back a journal file and running out of space like that? Or the user rolling back the journal file doesn't have permission to write the database file and SQLite is reporting the error incorrectly. Check for a journal file on the device after the error. Also run the SQLite integrity-check on the database. Dan. - To unsubscribe, send email to [EMAIL PROTECTED] -
