SQLite

Changes On Branch dump-recovery
Login
Patch Side-by-Side Diff

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Changes In Branch dump-recovery Excluding Merge-Ins

This is equivalent to a diff from 50eec5d9aa to 5dc82a155d

2017-03-09
18:13
Fix the ".dump" command to correctly extract tail data from corrupt WITHOUT ROWID tables. (check-in: 6c627e5062 user: drh tags: trunk)
16:32
Add the --recovery-mode option to the CLI ".dump" command. This involves enhancing the "PRAGMA reverse_unordered_selects" command to accept a "TOGGLE" option. Recovery mode used to be always on. Now it is opt-in. Not sure this is the correct approach. Also not sure if TOGGLE is a good feature to have on boolean PRAGMA statements. (Closed-Leaf check-in: 5dc82a155d user: drh tags: dump-recovery)
13:50
Begin moving separate boolean variables in the ShellState object of the CLI into the shellFlgs bitmask. (check-in: 50eec5d9aa user: drh tags: trunk)
2017-03-08
18:06
Make sure the database connection is open prior to running the ".sha3sum" command. (check-in: 2ea300fb8f user: drh tags: trunk)

Changes to src/pragma.c.
︙
1041
1042
1043
1044
1045
1046
1047
1048
1049



1050
1051
1052


1053

1054
1055
1056
1057
1058
1059
1060

1041
1042
1043
1044
1045
1046
1047


1048
1049
1050
1051
1052
1053
1054
1055

1056
1057
1058
1059
1060
1061
1062
1063








-
-
+
+
+



+
+
-
+








      }
#if SQLITE_USER_AUTHENTICATION
      if( db->auth.authLevel==UAUTH_User ){
        /* Do not allow non-admin users to modify the schema arbitrarily */
        mask &= ~(SQLITE_WriteSchema);
      }
#endif

      if( sqlite3GetBoolean(zRight, 0) ){
      if( sqlite3StrICmp(zRight, "toggle")==0 ){
        db->flags ^= mask;
      }else if( sqlite3GetBoolean(zRight, 0) ){
        db->flags |= mask;
      }else{
        db->flags &= ~mask;
      }
      if( mask==SQLITE_DeferFKs && (db->flags & mask)==0 ){
        if( mask==SQLITE_DeferFKs ) db->nDeferredImmCons = 0;
        db->nDeferredImmCons = 0;
      }

      /* Many of the flag-pragmas modify the code generated by the SQL 
      ** compiler (eg. count_changes). So add an opcode to expire all
      ** compiled SQL statements after modifying a pragma value.
      */
      sqlite3VdbeAddOp0(v, OP_Expire);
︙
Changes to src/shell.c.
︙
1406
1407
1408
1409
1410
1411
1412

1413
1414
1415
1416
1417
1418
1419

1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420








+








#define SHFLG_Scratch        0x00000001 /* The --scratch option is used */
#define SHFLG_Pagecache      0x00000002 /* The --pagecache option is used */
#define SHFLG_Lookaside      0x00000004 /* Lookaside memory is used */
#define SHFLG_Backslash      0x00000008 /* The --backslash option is used */
#define SHFLG_PreserveRowid  0x00000010 /* .dump preserves rowid values */
#define SHFLG_CountChanges   0x00000020 /* .changes setting */
#define SHFLG_Echo           0x00000040 /* .echo or --echo setting */
#define SHFLG_RecoveryMode   0x00000080 /* The --recovery-mode flag for .dump */

/*
** Macros for testing and setting shellFlgs
*/
#define ShellHasFlag(P,X)    (((P)->shellFlgs & (X))!=0)
#define ShellSetFlag(P,X)    ((P)->shellFlgs|=(X))
#define ShellClearFlag(P,X)  ((P)->shellFlgs&=(~(X)))
︙
2215
2216
2217
2218
2219
2220
2221
2222

2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
2234

2235
2236
2237
2238
2239
2240
2241

2216
2217
2218
2219
2220
2221
2222

2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
2234

2235
2236
2237
2238
2239
2240
2241
2242








-
+











-
+








    raw_printf(pArg->out,
            "Memory Used:                         %d (max %d) bytes\n",
            iCur, iHiwtr);
    iHiwtr = iCur = -1;
    sqlite3_status(SQLITE_STATUS_MALLOC_COUNT, &iCur, &iHiwtr, bReset);
    raw_printf(pArg->out, "Number of Outstanding Allocations:   %d (max %d)\n",
            iCur, iHiwtr);
    if( pArg->shellFlgs & SHFLG_Pagecache ){
    if( ShellHasFlag(pArg, SHFLG_Pagecache) ){
      iHiwtr = iCur = -1;
      sqlite3_status(SQLITE_STATUS_PAGECACHE_USED, &iCur, &iHiwtr, bReset);
      raw_printf(pArg->out,
              "Number of Pcache Pages Used:         %d (max %d) pages\n",
              iCur, iHiwtr);
    }
    iHiwtr = iCur = -1;
    sqlite3_status(SQLITE_STATUS_PAGECACHE_OVERFLOW, &iCur, &iHiwtr, bReset);
    raw_printf(pArg->out,
            "Number of Pcache Overflow Bytes:     %d (max %d) bytes\n",
            iCur, iHiwtr);
    if( pArg->shellFlgs & SHFLG_Scratch ){
    if( ShellHasFlag(pArg, SHFLG_Scratch) ){
      iHiwtr = iCur = -1;
      sqlite3_status(SQLITE_STATUS_SCRATCH_USED, &iCur, &iHiwtr, bReset);
      raw_printf(pArg->out,
              "Number of Scratch Allocations Used:  %d (max %d)\n",
              iCur, iHiwtr);
    }
    iHiwtr = iCur = -1;
︙
2260
2261
2262
2263
2264
2265
2266
2267

2268
2269
2270
2271
2272
2273
2274

2261
2262
2263
2264
2265
2266
2267

2268
2269
2270
2271
2272
2273
2274
2275








-
+








    sqlite3_status(SQLITE_STATUS_PARSER_STACK, &iCur, &iHiwtr, bReset);
    raw_printf(pArg->out, "Deepest Parser Stack:                %d (max %d)\n",
            iCur, iHiwtr);
#endif
  }

  if( pArg && pArg->out && db ){
    if( pArg->shellFlgs & SHFLG_Lookaside ){
    if( ShellHasFlag(pArg, SHFLG_Lookaside) ){
      iHiwtr = iCur = -1;
      sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_USED,
                        &iCur, &iHiwtr, bReset);
      raw_printf(pArg->out,
              "Lookaside Slots Used:                %d (max %d)\n",
              iCur, iHiwtr);
      sqlite3_db_status(db, SQLITE_DBSTATUS_LOOKASIDE_HIT,
︙
2942
2943
2944
2945
2946
2947
2948








2949
2950
2951
2952
2953
2954
2955
2956
2957
2958
2959
2960
2961
2962
2963



2964
2965
2966
2967
2968
2969
2970
2971
2972

2973
2974
2975
2976
2977
2978
2979

2943
2944
2945
2946
2947
2948
2949
2950
2951
2952
2953
2954
2955
2956
2957
2958
2959
2960
2961
2962
2963
2964
2965
2966
2967
2968
2969
2970


2971
2972
2973
2974
2975
2976
2977
2978
2979
2980
2981

2982
2983
2984
2985
2986
2987
2988
2989








+
+
+
+
+
+
+
+













-
-
+
+
+








-
+








    appendText(&sSelect, zTable, quoteChar(zTable));

    savedDestTable = p->zDestTable;
    savedMode = p->mode;
    p->zDestTable = sTable.z;
    p->mode = p->cMode = MODE_Insert;
    rc = shell_exec(p->db, sSelect.z, shell_callback, p, 0);
    if( (rc&0xff)==SQLITE_CORRUPT ){
      raw_printf(p->out, "/****** CORRUPTION ERROR *******/\n");
      if( ShellHasFlag(p, SHFLG_RecoveryMode) ){
        sqlite3_exec(p->db, "PRAGMA reverse_unordered_selects=TOGGLE",0,0,0);
        shell_exec(p->db, sSelect.z, shell_callback, p, 0);
        sqlite3_exec(p->db, "PRAGMA reverse_unordered_selects=TOGGLE",0,0,0);
      }
    }
    p->zDestTable = savedDestTable;
    p->mode = savedMode;
    freeText(&sTable);
    freeText(&sSelect);
    if( rc ) p->nErr++;
  }
  return 0;
}

/*
** Run zQuery.  Use dump_callback() as the callback routine so that
** the contents of the query are output as SQL statements.
**
** If we get a SQLITE_CORRUPT error, rerun the query after appending
** "ORDER BY rowid DESC" to the end.
** If we get a SQLITE_CORRUPT error and the --recovery-mode flag is
** set, then rerun the query in reverse order to try to get more
** data.
*/
static int run_schema_dump_query(
  ShellState *p,
  const char *zQuery
){
  int rc;
  char *zErr = 0;
  rc = sqlite3_exec(p->db, zQuery, dump_callback, p, &zErr);
  if( rc==SQLITE_CORRUPT ){
  if( rc==SQLITE_CORRUPT && ShellHasFlag(p, SHFLG_RecoveryMode) ){
    char *zQ2;
    int len = strlen30(zQuery);
    raw_printf(p->out, "/****** CORRUPTION ERROR *******/\n");
    if( zErr ){
      utf8_printf(p->out, "/****** %s ******/\n", zErr);
      sqlite3_free(zErr);
      zErr = 0;
︙
4566
4567
4568
4569
4570
4571
4572
4573

4574
4575
4576
4577
4578
4579
4580



4581
4582



4583
4584
4585
4586
4587
4588
4589
4590
4591
4592
4593
4594
4595
4596
4597
4598
4599
4600

4601
4602
4603
4604





4605
4606
4607
4608
4609
4610
4611

4576
4577
4578
4579
4580
4581
4582

4583
4584
4585
4586
4587
4588
4589
4590
4591
4592
4593
4594

4595
4596
4597
4598
4599
4600
4601
4602
4603
4604
4605
4606
4607
4608
4609
4610
4611
4612
4613
4614
4615
4616




4617
4618
4619
4620
4621
4622
4623
4624
4625
4626
4627
4628








-
+







+
+
+

-
+
+
+


















+
-
-
-
-
+
+
+
+
+








  if( c=='d' && strncmp(azArg[0], "dbinfo", n)==0 ){
    rc = shell_dbinfo_command(p, nArg, azArg);
  }else

  if( c=='d' && strncmp(azArg[0], "dump", n)==0 ){
    const char *zLike = 0;
    int i;
    ShellClearFlag(p, SHFLG_PreserveRowid);
    ShellClearFlag(p, SHFLG_PreserveRowid|SHFLG_RecoveryMode);
    for(i=1; i<nArg; i++){
      if( azArg[i][0]=='-' ){
        const char *z = azArg[i]+1;
        if( z[0]=='-' ) z++;
        if( strcmp(z,"preserve-rowids")==0 ){
          ShellSetFlag(p, SHFLG_PreserveRowid);
        }else
        if( strcmp(z,"recovery-mode")==0 ){
          ShellSetFlag(p, SHFLG_RecoveryMode);
        }else
        {
          raw_printf(stderr, "Unknown option \"%s\" on \".dump\"\n", azArg[i]);
          raw_printf(stderr, "Unknown option \"%s\" on \".dump\"\n"
                  "Should be one of: --preserve-rowids --recovery-mode\n",
                  azArg[i]);
          rc = 1;
          goto meta_command_exit;
        }
      }else if( zLike ){
        raw_printf(stderr, "Usage: .dump ?--preserve-rowids? ?LIKE-PATTERN?\n");
        rc = 1;
        goto meta_command_exit;
      }else{
        zLike = azArg[i];
      }
    }
    open_db(p, 0);
    /* When playing back a "dump", the content might appear in an order
    ** which causes immediate foreign key constraints to be violated.
    ** So disable foreign-key constraint enforcement to prevent problems. */
    raw_printf(p->out, "PRAGMA foreign_keys=OFF;\n");
    raw_printf(p->out, "BEGIN TRANSACTION;\n");
    p->writableSchema = 0;
    if( ShellHasFlag(p, SHFLG_RecoveryMode) ){
    /* Set writable_schema=ON since doing so forces SQLite to initialize
    ** as much of the schema as it can even if the sqlite_master table is
    ** corrupt. */
    sqlite3_exec(p->db, "SAVEPOINT dump; PRAGMA writable_schema=ON", 0, 0, 0);
      /* Set writable_schema=ON since doing so forces SQLite to initialize
      ** as much of the schema as it can even if the sqlite_master table is
      ** corrupt. */
      sqlite3_exec(p->db, "SAVEPOINT dump; PRAGMA writable_schema=ON", 0, 0, 0);
    }
    p->nErr = 0;
    if( zLike==0 ){
      run_schema_dump_query(p,
        "SELECT name, type, sql FROM sqlite_master "
        "WHERE sql NOT NULL AND type=='table' AND name!='sqlite_sequence'"
      );
      run_schema_dump_query(p,
︙
4632
4633
4634
4635
4636
4637
4638

4639
4640



4641
4642
4643
4644
4645
4646
4647

4649
4650
4651
4652
4653
4654
4655
4656


4657
4658
4659
4660
4661
4662
4663
4664
4665
4666








+
-
-
+
+
+








      run_table_dump_query(p, zSql, 0);
      sqlite3_free(zSql);
    }
    if( p->writableSchema ){
      raw_printf(p->out, "PRAGMA writable_schema=OFF;\n");
      p->writableSchema = 0;
    }
    if( ShellHasFlag(p, SHFLG_RecoveryMode) ){
    sqlite3_exec(p->db, "PRAGMA writable_schema=OFF;", 0, 0, 0);
    sqlite3_exec(p->db, "RELEASE dump;", 0, 0, 0);
      sqlite3_exec(p->db, "PRAGMA writable_schema=OFF;", 0, 0, 0);
      sqlite3_exec(p->db, "RELEASE dump;", 0, 0, 0);
    }
    raw_printf(p->out, p->nErr ? "ROLLBACK; -- due to errors\n" : "COMMIT;\n");
  }else

  if( c=='e' && strncmp(azArg[0], "echo", n)==0 ){
    if( nArg==2 ){
      setOrClearFlag(p, SHFLG_Echo, azArg[1]);
    }else{
︙