How to convert SQL file into database when a column value is Inf?

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

How to convert SQL file into database when a column value is Inf?

mailing lists
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to convert SQL file into database when a column value is Inf?

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
[hidden email], http://www.simplesystems.org/users/bfriesen/
GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to convert SQL file into database when a column value is Inf?

mailing lists
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 <[hidden email]>:
>
> 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
> [hidden email], http://www.simplesystems.org/users/bfriesen/
> GraphicsMagick Maintainer,    http://www.GraphicsMagick.org/
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to convert SQL file into database when a column value is Inf?

Keith Medcalf
In reply to this post by mailing lists

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-
>[hidden email]] 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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to convert SQL file into database when a column value is Inf?

mailing lists
Hi Keith,

thanks for the suggestion.

Regards,
Hartwig

> Am 2018-06-13 um 00:26 schrieb Keith Medcalf <[hidden email]>:
>
>
> 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-
>> [hidden email]] 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to convert SQL file into database when a column value is Inf?

R Smith-2
In reply to this post by mailing lists

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to convert SQL file into database when a column value is Inf?

mailing lists
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 <[hidden email]>:
>
>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to convert SQL file into database when a column value is Inf?

Dennis Clarke
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to convert SQL file into database when a column value is Inf?

R Smith-2
In reply to this post by mailing lists
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to convert SQL file into database when a column value is Inf?

mailing lists
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 <[hidden email]>:
>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to convert SQL file into database when a column value is Inf?

Simon Slavin-3
On 13 Jun 2018, at 9:44pm, skywind mailing lists <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users