3.29.0 .recover command

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

3.29.0 .recover command

Olivier Mascia
On one database instance, a .dump command gives me (among many other lines) things like:

    INSERT INTO STATISTICS VALUES(11237795927160,11868);

while the output of .recover command gives me things this way:

    INSERT INTO 'STATISTICS'('_rowid_', STATDATE, DISKUSED) VALUES( 1, 11237795927160, 11868 );

I'm wondering why these differences in the way to construct the instructions to rebuild a sound database instance. What are the (probably rightful) motivations?

1) Why 'STATISTICS' (and not STATISTICS as in .dump command)? If escaping wanted, why not double quotes instead of single quotes?
2) Why do the insert statement prefer to name and repeat, ad nausea, the column names on each insert when, apparently, the shortcut syntax capitalizing on the known column order in the schema might seem much less verbose?

On the real DB I quickly tested .recover on (with no reason, I have nothing to recover, just testing the feature) I had an issue while rebuilding a new DB from the script made by .recover. I got foreign key constraint failures (which I have not yet traced exactly).

sqlite> .once system.sql
sqlite> .recover

sqlite3 recover.db
sqlite> .read system.sql
Error: near line 14658: FOREIGN KEY constraint failed
Error: near line 14659: FOREIGN KEY constraint failed
Error: near line 14660: FOREIGN KEY constraint failed
sqlite> .q

While doing the same kind of work around .dump worked nicely:

sqlite> .once systemd.sql
sqlite> .dump

sqlite3 dump.db
sqlite> .read systemd.sql
sqlite> .q

The source test db passes successfully those tests:

sqlite> pragma integrity_check;
integrity_check
ok
sqlite> pragma foreign_key_check;
sqlite> .dbconfig
       enable_fkey on
    enable_trigger on
    fts3_tokenizer off
    load_extension on
  no_ckpt_on_close off
       enable_qpsg off
       trigger_eqp off
    reset_database off
         defensive off
   writable_schema off
legacy_alter_table off
           dqs_dml off
           dqs_ddl off

Again, I have no recovery to attempt for now. I was just exercising the .recover feature for learning, using a db I'm not suspecting of anything.

—  
Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten Grüßen,
Olivier Mascia


_______________________________________________
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: 3.29.0 .recover command

Chris Locke-3
>  I got foreign key constraint failures

I don't know why one would work and one would fail, but usually, this
occurs when you insert a record which has foreign keys to another table,
but that table hasn't been imported yet.  The workaround is usually to
ensure all the 'lookup' tables are done first, so when the main record is
inserted, the required record exists, or to turn off foreign key checks,
and only put them into the database once all the imports have completed.


On Tue, Aug 6, 2019 at 11:27 AM Olivier Mascia <[hidden email]> wrote:

> On one database instance, a .dump command gives me (among many other
> lines) things like:
>
>     INSERT INTO STATISTICS VALUES(11237795927160,11868);
>
> while the output of .recover command gives me things this way:
>
>     INSERT INTO 'STATISTICS'('_rowid_', STATDATE, DISKUSED) VALUES( 1,
> 11237795927160, 11868 );
>
> I'm wondering why these differences in the way to construct the
> instructions to rebuild a sound database instance. What are the (probably
> rightful) motivations?
>
> 1) Why 'STATISTICS' (and not STATISTICS as in .dump command)? If escaping
> wanted, why not double quotes instead of single quotes?
> 2) Why do the insert statement prefer to name and repeat, ad nausea, the
> column names on each insert when, apparently, the shortcut syntax
> capitalizing on the known column order in the schema might seem much less
> verbose?
>
> On the real DB I quickly tested .recover on (with no reason, I have
> nothing to recover, just testing the feature) I had an issue while
> rebuilding a new DB from the script made by .recover. I got foreign key
> constraint failures (which I have not yet traced exactly).
>
> sqlite> .once system.sql
> sqlite> .recover
>
> sqlite3 recover.db
> sqlite> .read system.sql
> Error: near line 14658: FOREIGN KEY constraint failed
> Error: near line 14659: FOREIGN KEY constraint failed
> Error: near line 14660: FOREIGN KEY constraint failed
> sqlite> .q
>
> While doing the same kind of work around .dump worked nicely:
>
> sqlite> .once systemd.sql
> sqlite> .dump
>
> sqlite3 dump.db
> sqlite> .read systemd.sql
> sqlite> .q
>
> The source test db passes successfully those tests:
>
> sqlite> pragma integrity_check;
> integrity_check
> ok
> sqlite> pragma foreign_key_check;
> sqlite> .dbconfig
>        enable_fkey on
>     enable_trigger on
>     fts3_tokenizer off
>     load_extension on
>   no_ckpt_on_close off
>        enable_qpsg off
>        trigger_eqp off
>     reset_database off
>          defensive off
>    writable_schema off
> legacy_alter_table off
>            dqs_dml off
>            dqs_ddl off
>
> Again, I have no recovery to attempt for now. I was just exercising the
> .recover feature for learning, using a db I'm not suspecting of anything.
>
> —
> Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten
> Grüßen,
> Olivier Mascia
>
>
> _______________________________________________
> 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: 3.29.0 .recover command

Olivier Mascia
> Le 6 août 2019 à 14:18, Chris Locke <[hidden email]> a écrit :
>
>> I got foreign key constraint failures
>
> I don't know why one would work and one would fail, but usually, this
> occurs when you insert a record which has foreign keys to another table,
> but that table hasn't been imported yet.  The workaround is usually to
> ensure all the 'lookup' tables are done first, so when the main record is
> inserted, the required record exists, or to turn off foreign key checks,
> and only put them into the database once all the imports have completed.

Of course :), but thanks too, Chris.  The real matter at hand is why the script produced by .recover (new feature of 3.29.0) differs from the one produced by .dump to the point that it triggers these 3 constraint failures on this particular db instance I happen to have tested.

I have both those scripts and will spend some time looking up where exactly it goes wrong and why. But I wanted to report the strange findings first.  Just in case this is well-known and expected in this initial implementation of the .recover command.  Indeed, I'm seeing multiple significant checkins about .recover after the 3.29.0 release, so I might first have to rebuild using head of trunk.

—  
Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten Grüßen,
Olivier Mascia


_______________________________________________
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: 3.29.0 .recover command

Dan Kennedy-4
In reply to this post by Olivier Mascia

On 6/8/62 17:26, Olivier Mascia wrote:

> On one database instance, a .dump command gives me (among many other lines) things like:
>
>      INSERT INTO STATISTICS VALUES(11237795927160,11868);
>
> while the output of .recover command gives me things this way:
>
>      INSERT INTO 'STATISTICS'('_rowid_', STATDATE, DISKUSED) VALUES( 1, 11237795927160, 11868 );
>
> I'm wondering why these differences in the way to construct the instructions to rebuild a sound database instance. What are the (probably rightful) motivations?
>
> 1) Why 'STATISTICS' (and not STATISTICS as in .dump command)? If escaping wanted, why not double quotes instead of single quotes?
> 2) Why do the insert statement prefer to name and repeat, ad nausea, the column names on each insert when, apparently, the shortcut syntax capitalizing on the known column order in the schema might seem much less verbose?
>
> On the real DB I quickly tested .recover on (with no reason, I have nothing to recover, just testing the feature) I had an issue while rebuilding a new DB from the script made by .recover. I got foreign key constraint failures (which I have not yet traced exactly).
>
> sqlite> .once system.sql
> sqlite> .recover
>
> sqlite3 recover.db
> sqlite> .read system.sql
> Error: near line 14658: FOREIGN KEY constraint failed
> Error: near line 14659: FOREIGN KEY constraint failed
> Error: near line 14660: FOREIGN KEY constraint failed

Thanks for the report. Looks like the .dump command adds "PRAGMA
foreign_keys=OFF;" to the output to avoid this. .recover now does this too:

https://sqlite.org/src/info/bfc29e62eff0ed00

Dan.



> sqlite> .q
>
> While doing the same kind of work around .dump worked nicely:
>
> sqlite> .once systemd.sql
> sqlite> .dump
>
> sqlite3 dump.db
> sqlite> .read systemd.sql
> sqlite> .q
>
> The source test db passes successfully those tests:
>
> sqlite> pragma integrity_check;
> integrity_check
> ok
> sqlite> pragma foreign_key_check;
> sqlite> .dbconfig
>         enable_fkey on
>      enable_trigger on
>      fts3_tokenizer off
>      load_extension on
>    no_ckpt_on_close off
>         enable_qpsg off
>         trigger_eqp off
>      reset_database off
>           defensive off
>     writable_schema off
> legacy_alter_table off
>             dqs_dml off
>             dqs_ddl off
>
> Again, I have no recovery to attempt for now. I was just exercising the .recover feature for learning, using a db I'm not suspecting of anything.
>
> —
> Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten Grüßen,
> Olivier Mascia
>
>
> _______________________________________________
> 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: 3.29.0 .recover command

Simon Slavin-3
On 6 Aug 2019, at 7:51pm, Dan Kennedy <[hidden email]> quoted:

>>     INSERT INTO 'STATISTICS'('_rowid_', STATDATE, DISKUSED) VALUES( 1, 11237795927160, 11868 );

Quoting these entity names using apostrophes looks wrong to me.  It may work but someone might read it, know it's official output from a program written by the SQLite dev team, and write a ton of software which does likewise.
_______________________________________________
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: 3.29.0 .recover command

Keith Medcalf
In reply to this post by Olivier Mascia
On Tuesday, 6 August, 2019 04:27, Olivier Mascia <[hidden email]> inquired:

>On one database instance, a .dump command gives me (among many other
>lines) things like:
>
>    INSERT INTO STATISTICS VALUES(11237795927160,11868);
>
>while the output of .recover command gives me things this way:
>
>    INSERT INTO 'STATISTICS'('_rowid_', STATDATE, DISKUSED) VALUES(
>1, 11237795927160, 11868 );
>
>I'm wondering why these differences in the way to construct the
>instructions to rebuild a sound database instance. What are the
>(probably rightful) motivations?

>1) Why 'STATISTICS' (and not STATISTICS as in .dump command)? If
>escaping wanted, why not double quotes instead of single quotes?

This is a good question and the single quotes are bad syntax (or should be).  Identifier quoting should be using double-quotes.  As to why identifier quotes are included when not required, this is a matter of taste.  Some people love quotes.  Some people use them only when necessary.  Others deliberately choose identifiers so quotes are never required.  I happen to fall into the latter camp.

>2) Why do the insert statement prefer to name and repeat, ad nausea,
>the column names on each insert when, apparently, the shortcut syntax
>capitalizing on the known column order in the schema might seem much
>less verbose?

In this particular case it is to preserve the _rowid_.  The .recover command is designed to "recover" the database, not merely ".dump" the data.  Therefore, if one wants to preserve the internal _rowid_ (because there is no explicitly named INTEGER PRIMARY KEY for a rowid table, for example), the only way to do so is to provide the list of attributes for which values are being provided.

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.





_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users