/ Check-in [afdae104]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Fix a locking-page related problem with the ".recover" command.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | dbdata
Files: files | file ages | folders
SHA3-256: afdae10424f0f3d0f10a4b73e9732aa55c5ee664814d8ca0edd372cfb17c2445
User & Date: dan 2019-04-26 15:14:53
Context
2019-04-26
15:40
Fix another problem with database freelist handling in the ".recover" command. check-in: bee2652a user: dan tags: dbdata
15:14
Fix a locking-page related problem with the ".recover" command. check-in: afdae104 user: dan tags: dbdata
2019-04-25
20:06
Merge latest trunk changes into this branch. check-in: 1da302d8 user: dan tags: dbdata
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to ext/misc/dbdata.c.

    85     85   
    86     86     int iPgno;                      /* Current page number */
    87     87     u8 *aPage;                      /* Buffer containing page */
    88     88     int nPage;                      /* Size of aPage[] in bytes */
    89     89     int nCell;                      /* Number of cells on aPage[] */
    90     90     int iCell;                      /* Current cell number */
    91     91     int bOnePage;                   /* True to stop after one page */
           92  +  int szDb;
    92     93     sqlite3_int64 iRowid;
    93     94   
    94     95     /* Only for the sqlite_dbdata table */
    95     96     u8 *pRec;                       /* Buffer containing current record */
    96     97     int nRec;                       /* Size of pRec[] in bytes */
    97     98     int nHdr;                       /* Size of header in bytes */
    98     99     int iField;                     /* Current field number */
................................................................................
   299    300     sqlite3_stmt *pStmt = pCsr->pStmt;
   300    301   
   301    302     *ppPage = 0;
   302    303     *pnPage = 0;
   303    304     sqlite3_bind_int64(pStmt, 2, pgno);
   304    305     if( SQLITE_ROW==sqlite3_step(pStmt) ){
   305    306       int nCopy = sqlite3_column_bytes(pStmt, 0);
   306         -    u8 *pPage = (u8*)sqlite3_malloc64(nCopy);
          307  +    if( nCopy>0 ){
          308  +      u8 *pPage;
          309  +      pPage = (u8*)sqlite3_malloc64(nCopy);
   307    310       if( pPage==0 ){
   308    311         rc = SQLITE_NOMEM;
   309    312       }else{
   310    313         const u8 *pCopy = sqlite3_column_blob(pStmt, 0);
   311    314         memcpy(pPage, pCopy, nCopy);
          315  +      }
   312    316         *ppPage = pPage;
   313    317         *pnPage = nCopy;
   314    318       }
   315    319     }
   316    320     rc2 = sqlite3_reset(pStmt);
   317         -  if( *ppPage==0 ) rc = rc2;
          321  +  if( rc==SQLITE_OK ) rc = rc2;
   318    322   
   319    323     return rc;
   320    324   }
   321    325   
   322    326   /*
   323    327   ** Read a varint.  Put the value in *pVal and return the number of bytes.
   324    328   */
................................................................................
   415    419   
   416    420     pCsr->iRowid++;
   417    421     while( 1 ){
   418    422       int rc;
   419    423       int iOff = (pCsr->iPgno==1 ? 100 : 0);
   420    424   
   421    425       if( pCsr->aPage==0 ){
          426  +      while( 1 ){
          427  +        if( pCsr->bOnePage==0 && pCsr->iPgno>pCsr->szDb ) return SQLITE_OK;
   422    428         rc = dbdataLoadPage(pCsr, pCsr->iPgno, &pCsr->aPage, &pCsr->nPage);
   423         -      if( rc!=SQLITE_OK || pCsr->aPage==0 ) return rc;
          429  +        if( rc!=SQLITE_OK ) return rc;
          430  +        if( pCsr->aPage ) break;
          431  +        pCsr->iPgno++;
          432  +      }
   424    433         pCsr->iCell = pTab->bPtr ? -2 : 0;
   425    434         pCsr->nCell = get_uint16(&pCsr->aPage[iOff+3]);
   426    435       }
   427    436   
   428    437       if( pTab->bPtr ){
   429    438         if( pCsr->aPage[iOff]!=0x02 && pCsr->aPage[iOff]!=0x05 ){
   430    439           pCsr->iCell = pCsr->nCell;
................................................................................
   569    578   /* We have reached EOF if previous sqlite3_step() returned
   570    579   ** anything other than SQLITE_ROW;
   571    580   */
   572    581   static int dbdataEof(sqlite3_vtab_cursor *pCursor){
   573    582     DbdataCursor *pCsr = (DbdataCursor*)pCursor;
   574    583     return pCsr->aPage==0;
   575    584   }
          585  +
          586  +static int dbdataDbsize(DbdataCursor *pCsr, const char *zSchema){
          587  +  DbdataTable *pTab = (DbdataTable*)pCsr->base.pVtab;
          588  +  char *zSql = 0;
          589  +  int rc, rc2;
          590  +  sqlite3_stmt *pStmt = 0;
          591  +
          592  +  zSql = sqlite3_mprintf("PRAGMA %Q.page_count", zSchema);
          593  +  if( zSql==0 ) return SQLITE_NOMEM;
          594  +  rc = sqlite3_prepare_v2(pTab->db, zSql, -1, &pStmt, 0);
          595  +  sqlite3_free(zSql);
          596  +  if( rc==SQLITE_OK && sqlite3_step(pStmt)==SQLITE_ROW ){
          597  +    pCsr->szDb = sqlite3_column_int(pStmt, 0);
          598  +  }
          599  +  rc2 = sqlite3_finalize(pStmt);
          600  +  if( rc==SQLITE_OK ) rc = rc2;
          601  +  return rc;
          602  +}
   576    603   
   577    604   /* Position a cursor back to the beginning.
   578    605   */
   579    606   static int dbdataFilter(
   580    607     sqlite3_vtab_cursor *pCursor, 
   581    608     int idxNum, const char *idxStr,
   582    609     int argc, sqlite3_value **argv
................................................................................
   590    617     assert( pCsr->iPgno==1 );
   591    618     if( idxNum & 0x01 ){
   592    619       zSchema = (const char*)sqlite3_value_text(argv[0]);
   593    620     }
   594    621     if( idxNum & 0x02 ){
   595    622       pCsr->iPgno = sqlite3_value_int(argv[(idxNum & 0x01)]);
   596    623       pCsr->bOnePage = 1;
          624  +  }else{
          625  +    pCsr->nPage = dbdataDbsize(pCsr, zSchema);
          626  +    rc = dbdataDbsize(pCsr, zSchema);
   597    627     }
   598    628   
          629  +  if( rc==SQLITE_OK ){
   599    630     if( pTab->pStmt ){
   600    631       pCsr->pStmt = pTab->pStmt;
   601    632       pTab->pStmt = 0;
   602    633     }else{
   603    634       rc = sqlite3_prepare_v2(pTab->db, 
   604    635           "SELECT data FROM sqlite_dbpage(?) WHERE pgno=?", -1,
   605    636           &pCsr->pStmt, 0
   606    637       );
   607    638     }
          639  +  }
   608    640     if( rc==SQLITE_OK ){
   609    641       rc = sqlite3_bind_text(pCsr->pStmt, 1, zSchema, -1, SQLITE_TRANSIENT);
   610    642     }else{
   611    643       pTab->base.zErrMsg = sqlite3_mprintf("%s", sqlite3_errmsg(pTab->db));
   612    644     }
   613    645     if( rc==SQLITE_OK ){
   614    646       rc = dbdataNext(pCursor);

Changes to src/shell.c.in.

  6160   6160       rc = sqlite3_exec(db, zSql, 0, 0, &zErr);
  6161   6161       if( rc!=SQLITE_OK ){
  6162   6162         raw_printf(stderr, "SQL error: %s\n", zErr);
  6163   6163       }
  6164   6164       *pRc = rc;
  6165   6165     }
  6166   6166   }
         6167  +
         6168  +static void shellExecPrintf(sqlite3 *db, int *pRc, const char *zFmt, ...){
         6169  +  char *z = 0;
         6170  +  if( *pRc==SQLITE_OK ){
         6171  +    va_list ap;
         6172  +    va_start(ap, zFmt);
         6173  +    z = sqlite3_vmprintf(zFmt, ap);
         6174  +    va_end(ap);
         6175  +    if( z==0 ){
         6176  +      *pRc = SQLITE_NOMEM;
         6177  +    }else{
         6178  +      shellExec(db, pRc, z);
         6179  +    }
         6180  +    sqlite3_free(z);
         6181  +  }
         6182  +}
  6167   6183   
  6168   6184   static void *shellMalloc(int *pRc, sqlite3_int64 nByte){
  6169   6185     void *pRet = 0;
  6170   6186     if( *pRc==SQLITE_OK ){
  6171   6187       pRet = sqlite3_malloc64(nByte);
  6172   6188       if( pRet==0 ){
  6173   6189         *pRc = SQLITE_NOMEM;
................................................................................
  6401   6417   ** on stream pState->out.
  6402   6418   */
  6403   6419   static int recoverDatabaseCmd(ShellState *pState, int nArg, char **azArg){
  6404   6420     int rc = SQLITE_OK;
  6405   6421     sqlite3_stmt *pLoop = 0;        /* Loop through all root pages */
  6406   6422     sqlite3_stmt *pPages = 0;       /* Loop through all pages in a group */
  6407   6423     sqlite3_stmt *pCells = 0;       /* Loop through all cells in a page */
         6424  +  const char *zRecoveryDb = "";   /* Name of "recovery" database */
  6408   6425     int i;
  6409   6426   
  6410   6427     int bFreelist = 1;              /* 0 if --freelist-corrupt is specified */
  6411   6428     for(i=1; i<nArg; i++){
  6412   6429       char *z = azArg[i];
  6413   6430       int n;
  6414   6431       if( z[0]=='-' && z[1]=='-' ) z++;
  6415   6432       n = strlen(z);
  6416   6433       if( n<=17 && memcmp("-freelist-corrupt", z, n)==0 ){
  6417   6434         bFreelist = 0;
  6418   6435       }
         6436  +    if( n<=12 && memcmp("-recovery-db", z, n)==0 && i<(nArg-1) ){
         6437  +      i++;
         6438  +      zRecoveryDb = azArg[i];
         6439  +    }
  6419   6440       else{
  6420         -      raw_printf(stderr, 
  6421         -        "unexpected option: %s - expected \"--freelist-corrupt\"\n", 
  6422         -        azArg[i]
  6423         -      );
         6441  +      raw_printf(stderr, "unexpected option: %s\n", azArg[i]); 
         6442  +      raw_printf(stderr, "options are:\n");
         6443  +      raw_printf(stderr, "    --freelist-corrupt\n");
         6444  +      raw_printf(stderr, "    --recovery-db DATABASE\n");
  6424   6445         return 1;
  6425   6446       }
  6426   6447     }
  6427   6448   
  6428         -  shellExec(pState->db, &rc, 
         6449  +  shellExecPrintf(pState->db, &rc,
  6429   6450       /* Attach an in-memory database named 'recovery'. Create an indexed 
  6430   6451       ** cache of the sqlite_dbptr virtual table. */
  6431         -    "ATTACH '' AS recovery;"
         6452  +    "ATTACH %Q AS recovery;"
         6453  +    "DROP TABLE IF EXISTS recovery.dbptr;"
         6454  +    "DROP TABLE IF EXISTS recovery.freelist;"
         6455  +    "DROP TABLE IF EXISTS recovery.map;"
         6456  +    "DROP TABLE IF EXISTS recovery.schema;"
  6432   6457       "CREATE TABLE recovery.dbptr("
  6433   6458       "      pgno, child, PRIMARY KEY(child, pgno)"
  6434   6459       ") WITHOUT ROWID;"
  6435         -    "INSERT OR IGNORE INTO dbptr(pgno, child) SELECT * FROM sqlite_dbptr;"
         6460  +    "INSERT OR IGNORE INTO recovery.dbptr(pgno, child) "
         6461  +    "    SELECT * FROM sqlite_dbptr;"
  6436   6462   
  6437   6463       /* Delete any pointer to page 1. This ensures that page 1 is considered
  6438   6464       ** a root page, regardless of how corrupt the db is. */
  6439   6465       "DELETE FROM recovery.dbptr WHERE child = 1;"
  6440   6466   
  6441   6467       /* Delete all pointers to any pages that have more than one pointer
  6442   6468       ** to them. Such pages will be treated as root pages when recovering
  6443   6469       ** data.  */
  6444   6470       "DELETE FROM recovery.dbptr WHERE child IN ("
  6445   6471       "  SELECT child FROM recovery.dbptr GROUP BY child HAVING count(*)>1"
  6446   6472       ");"
  6447   6473   
  6448         -    "CREATE TABLE recovery.freelist(pgno INTEGER PRIMARY KEY);"
         6474  +    "CREATE TABLE recovery.freelist(pgno INTEGER PRIMARY KEY);", zRecoveryDb
  6449   6475     );
  6450   6476   
  6451   6477     if( bFreelist ){
  6452   6478       shellExec(pState->db, &rc,
  6453   6479         "WITH trunk(pgno) AS ("
  6454   6480         "  SELECT shell_int32("
  6455   6481         "      (SELECT data FROM sqlite_dbpage WHERE pgno=1), 8) AS x "