Re: [sqlite] How to convert SQL file into database when a column value is Inf?
On 13 Jun 2018, at 9:44pm, skywind mailing lists wrote: > The operation system is iOS (therefore, it is difficult to remove the battery > and do several tests after each other) (First paragraph is background for others reading this message.) An iOS application which is running is supposed to receive a 'quit now' notification if the phone wants it to close. This notification allows it to save context and close files. The documentation for iOS explains this and tells developers that their Apps must deal with this notification correctly. The documentation doesn't mention that there's a situation where the 'Quit now' notification is not sent. This is where the phone thinks it's going to run out of power before all the running Apps, including all the background ones, have had a chance to do their filehandling. And you can simulate this situation. So here's how to simulate an 'out of power' shutdown situation where your app doesn't get the 'quit now' notification: Have the application running. Switch it to background by switching some other application to foreground. Make sure that the phone is running off of its own battery power. It doesn't matter what your charge level is. Double-click the home button to bring up the task manager interface. Find the display for your application and, without bringing it to the foreground, drag it upwards to terminate the App. In this situation the App will be terminated without the 'quit now' notification. It simulates the 'running out of batter power now' situation where the phone notifies only the foreground application to quit. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to convert SQL file into database when a column value is Inf?
Hi Ryan, I just became aware that I might give synchronisation level 3 a chance (currently the level is 1, default setting). I was not aware of this setting since a couple of minutes ago. Otherwise, the database is in serialised threading mode, though only one thread is accessing it. The operation system is iOS (therefore, it is difficult to remove the battery and do several tests after each other) and the database is opened with read-write access and no further options used but the default ones. The old style journal mode is used. I could never reproduce it by myself but as I said with iOS it is difficult to do a lot of tests. The incident occurs one or two times a year at different customers. I do not own the database therefore I am not able to share it publicly. I might get the permission to share it privately. Regards, Hartwig > Am 2018-06-13 um 21:32 schrieb R Smith : > > On 2018/06/13 6:35 PM, skywind mailing lists wrote: >> Hi Ryan, >> >> my problem is that I use the "most safest" mode that exists for SQLite and >> it still fails… Therefore, I need to know why it fails. > > Alright, but this implies a very serious flaw in SQLite, so mind if we > double-check some things? > > - What exactly is the "most safest" mode that you use? > - May we see the full schema?, > - May we see the initialization calls (pragma calls esp.) when opening the DB > file?, > - What is the specific file-system and file-locking methods it supports? > - Can you reproduce this error by removing the phone battery mid-write? (or > perhaps by any other service interruption?) > - If so, can you reproduce it with a DB with fake data (to protect privacy) > and then send us the broken DB file? > - if unable to reproduce it, how often have you had reports of it breaking? > And, > - Any chance we can get such a broken DB? (If the data is sensitive, perhaps > only send it to Richard). > > "Why it fails" is easy - it fails because it has data half-written during > power cut in a way that puts it out of sync with the schema, but then the > "most safest" journal modes actually protect against that, so if you use the > correct modes and it still fails, it means either SQLite is broken or your > implementation is doing something weird (such as non-standard file-locking > mechanism, or the OS lying about data being committed to storage) or you > might be assuming something that might not work as you expect, and we can > only learn which of these it is when having the above information. > > Let us know, > Ryan > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to convert SQL file into database when a column value is Inf?
On 2018/06/13 6:35 PM, skywind mailing lists wrote: Hi Ryan, my problem is that I use the "most safest" mode that exists for SQLite and it still fails… Therefore, I need to know why it fails. Alright, but this implies a very serious flaw in SQLite, so mind if we double-check some things? - What exactly is the "most safest" mode that you use? - May we see the full schema?, - May we see the initialization calls (pragma calls esp.) when opening the DB file?, - What is the specific file-system and file-locking methods it supports? - Can you reproduce this error by removing the phone battery mid-write? (or perhaps by any other service interruption?) - If so, can you reproduce it with a DB with fake data (to protect privacy) and then send us the broken DB file? - if unable to reproduce it, how often have you had reports of it breaking? And, - Any chance we can get such a broken DB? (If the data is sensitive, perhaps only send it to Richard). "Why it fails" is easy - it fails because it has data half-written during power cut in a way that puts it out of sync with the schema, but then the "most safest" journal modes actually protect against that, so if you use the correct modes and it still fails, it means either SQLite is broken or your implementation is doing something weird (such as non-standard file-locking mechanism, or the OS lying about data being committed to storage) or you might be assuming something that might not work as you expect, and we can only learn which of these it is when having the above information. Let us know, Ryan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to convert SQL file into database when a column value is Inf?
On 06/13/2018 12:35 PM, skywind mailing lists wrote: Hi Ryan, my problem is that I use the "most safest" mode that exists for SQLite and it still fails… Therefore, I need to know why it fails. I have been watching this from a distance and all I can think is : 1) what do you mean by a "SQL file"? Do you mean a backup file of some type? 2) what was the source database type ? 3) why not simply remove the offending "Inf" data? Really you need someone to look over the data from top to bottom and then render the data into a database for you. In any format possible given that the data may be trash. Then look at the result and determine if it will work for you or not. Why have not done this simple step? Dennis ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to convert SQL file into database when a column value is Inf?
Hi Ryan, my problem is that I use the "most safest" mode that exists for SQLite and it still fails… Therefore, I need to know why it fails. Regards, Hartwig > Am 2018-06-13 um 01:23 schrieb R Smith : > > > On 2018/06/13 12:21 AM, skywind mailing lists wrote: >> Hi, >> >> the original database is malformed. So, I cannot access it anymore besides >> doing a dump. > > There is currently no known way to read this since Inf and -Inf are not > recognized as floats but in stead look like identifiers. Perhaps this is a > useful enhancement to SQLite - but either way, right now your best bet is > indeed to search-replace the entire text file. > > As to your other question about why it is malformed - it simply means that > the schema doesn't match the data structure/layout/constraints anymore and > sqlite can't fix it and can't even know what all is wrong. Asking why > specifically is not useful. > > It's like when you see two random people on the street and ask "Are they > brother and sister?". If the answer is "No", there is no more that can be > said. It is not useful to ask "which one is not the brother?" or "why are > they not brother and sister?". > > It's a silly analogy, but it's hard to think of a better one now - The schema > and data are simply not happy together. In most cases this would be because a > Unique key (perhaps Primary) got written halfway when disk access died and > now has a situation where it has duplicate values in the Key. It might > however also be a root page index that falls outside the file, or indeed a > myriad of possible things - or worse - a combination of things so that if it > reported "Index has duplicates" and you manage to fix that (assuming you know > some magic), then you will find the next index is broken, and then page isn't > where it should be, and... and... and... - so there really is no point in in > saying what is wrong, just to know that the DB is broken to the point where > SQLite knows it is broken, but doesn't know if any of it is still o.k. or > what all is specifically broken. > > The corruptions may be vast and wide, but the reason is always singular - > There was a media write that failed to complete. That's all that needs to be > known, and to help prevent these things from happening, there is only one > good solution: choose more safe Journal modes. And yes, it may come at a > speed penalty. (On phones specifically you can also avoid DB updates when > battery is low, but that is not a fool-proof solution. A user can yank out a > battery, etc.) > > > I hope this helps to make sense of it somewhat, but I know none of it really > provides a solution, so apologies for that. > cheers, > Ryan > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to convert SQL file into database when a column value is Inf?
On 2018/06/13 12:21 AM, skywind mailing lists wrote: Hi, the original database is malformed. So, I cannot access it anymore besides doing a dump. There is currently no known way to read this since Inf and -Inf are not recognized as floats but in stead look like identifiers. Perhaps this is a useful enhancement to SQLite - but either way, right now your best bet is indeed to search-replace the entire text file. As to your other question about why it is malformed - it simply means that the schema doesn't match the data structure/layout/constraints anymore and sqlite can't fix it and can't even know what all is wrong. Asking why specifically is not useful. It's like when you see two random people on the street and ask "Are they brother and sister?". If the answer is "No", there is no more that can be said. It is not useful to ask "which one is not the brother?" or "why are they not brother and sister?". It's a silly analogy, but it's hard to think of a better one now - The schema and data are simply not happy together. In most cases this would be because a Unique key (perhaps Primary) got written halfway when disk access died and now has a situation where it has duplicate values in the Key. It might however also be a root page index that falls outside the file, or indeed a myriad of possible things - or worse - a combination of things so that if it reported "Index has duplicates" and you manage to fix that (assuming you know some magic), then you will find the next index is broken, and then page isn't where it should be, and... and... and... - so there really is no point in in saying what is wrong, just to know that the DB is broken to the point where SQLite knows it is broken, but doesn't know if any of it is still o.k. or what all is specifically broken. The corruptions may be vast and wide, but the reason is always singular - There was a media write that failed to complete. That's all that needs to be known, and to help prevent these things from happening, there is only one good solution: choose more safe Journal modes. And yes, it may come at a speed penalty. (On phones specifically you can also avoid DB updates when battery is low, but that is not a fool-proof solution. A user can yank out a battery, etc.) I hope this helps to make sense of it somewhat, but I know none of it really provides a solution, so apologies for that. cheers, Ryan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to convert SQL file into database when a column value is Inf?
Hi Keith, thanks for the suggestion. Regards, Hartwig > Am 2018-06-13 um 00:26 schrieb Keith Medcalf : > > > You can replace the "Inf" with 1e400 and -Inf with -1e400. These values will > be parsed and stored as the appropriate plus/minus Infinity since they are > larger than the maximum representable IEEE-754 Double Precision Binary Float. > > --- > The fact that there's a Highway to Hell but only a Stairway to Heaven says a > lot about anticipated traffic volume. > > >> -Original Message- >> From: sqlite-users [mailto:sqlite-users- >> boun...@mailinglists.sqlite.org] On Behalf Of skywind mailing lists >> Sent: Tuesday, 12 June, 2018 16:06 >> To: SQLite mailing list >> Subject: [sqlite] How to convert SQL file into database when a column >> value is Inf? >> >> Hi, >> >> I issued the following commands: >> >> echo .dump | sqlite3 Database.sldb > D.sldb >> sqlite3 -init D.sldb NewDatabase.sldb >> >> Unfortunately, reading the SQL file produced the following error: >> Error: near line 56721: no such column: Inf >> >> The corresponding SQL command is: >> INSERT INTO "Flights" >> VALUES(36,80,1470395806,'','','',NULL,0.0,0,0,0,0,0.0,-Inf,0.0,- >> Inf,0.0,- >> Inf,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NULL,0,0,NULL,NULL,NULL >> ,NULL,4,0,''); >> >> OK, it seems to be that the column values are not really correct (- >> Inf) but I would still like to do the conversion. Is there a >> command/trick in sqlite3 that makes it work? >> >> A workaround is of course to use a text editor and try to find and >> replace all occurrences of Inf or -Info. >> >> Regards, >> Hartwig >> >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to convert SQL file into database when a column value is Inf?
You can replace the "Inf" with 1e400 and -Inf with -1e400. These values will be parsed and stored as the appropriate plus/minus Infinity since they are larger than the maximum representable IEEE-754 Double Precision Binary Float. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of skywind mailing lists >Sent: Tuesday, 12 June, 2018 16:06 >To: SQLite mailing list >Subject: [sqlite] How to convert SQL file into database when a column >value is Inf? > >Hi, > >I issued the following commands: > >echo .dump | sqlite3 Database.sldb > D.sldb >sqlite3 -init D.sldb NewDatabase.sldb > >Unfortunately, reading the SQL file produced the following error: >Error: near line 56721: no such column: Inf > >The corresponding SQL command is: >INSERT INTO "Flights" >VALUES(36,80,1470395806,'','','',NULL,0.0,0,0,0,0,0.0,-Inf,0.0,- >Inf,0.0,- >Inf,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NULL,0,0,NULL,NULL,NULL >,NULL,4,0,''); > >OK, it seems to be that the column values are not really correct (- >Inf) but I would still like to do the conversion. Is there a >command/trick in sqlite3 that makes it work? > >A workaround is of course to use a text editor and try to find and >replace all occurrences of Inf or -Info. > >Regards, >Hartwig > >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to convert SQL file into database when a column value is Inf?
Hi, the original database is malformed. So, I cannot access it anymore besides doing a dump. Regards, Hartwig > Am 2018-06-13 um 00:17 schrieb Bob Friesenhahn : > > On Wed, 13 Jun 2018, skywind mailing lists wrote: >> >> A workaround is of course to use a text editor and try to find and replace >> all occurrences of Inf or -Info. > > Can you use update queries on the original database to change the Inf and > -Inf values to huge positive or negative values that sqlite is willing to > consume? > > The range of a double value is typically 2.225074e-308 to 1.797693e+308. > > Bob > -- > Bob Friesenhahn > bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ > GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to convert SQL file into database when a column value is Inf?
On Wed, 13 Jun 2018, skywind mailing lists wrote: A workaround is of course to use a text editor and try to find and replace all occurrences of Inf or -Info. Can you use update queries on the original database to change the Inf and -Inf values to huge positive or negative values that sqlite is willing to consume? The range of a double value is typically 2.225074e-308 to 1.797693e+308. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
