SQLite: Check-in [564ae829]

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

Overview

Comment:	Fix an RBU problem causing spurious SQLITE_CONSTRAINT errors when restarting an RBU update in which more than one source table writes to a single target database table.
Downloads:	Tarball \| ZIP archive \| SQL archive
Timelines:	family \| ancestors \| descendants \| both \| trunk
Files:	files \| file ages \| folders
SHA3-256:	564ae8297d417ba4b7978e430d41f125007177673163f6ed9adc3a3974f73d24
User & Date:	dan 2018-04-28 18:20:01

Context

2018-04-28
19:08		Test cases added for SQLITE_DBCONFIG_RESET_DATABASE. check-in: 08665a9e user: drh tags: trunk
18:20		Fix an RBU problem causing spurious SQLITE_CONSTRAINT errors when restarting an RBU update in which more than one source table writes to a single target database table. check-in: 564ae829 user: dan tags: trunk
13:21		Add the SQLITE_DBCONFIG_RESET_DATABASE control for resetting a corrupt database file without closing any database connections. Added the ".dbconfig" command to the CLI. check-in: a200a49e user: drh tags: trunk

Changes

Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to ext/rbu/rbu1.test.

} {

  eval $create_vfs

  foreach {tn2 cmd} {
      1 run_rbu 
      2 step_rbu 3 step_rbu_uri 4 step_rbu_state

  } {
    foreach {tn schema} {
      1 {
        CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
      }
      2 { 
        CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);

} {

  eval $create_vfs

  foreach {tn2 cmd} {
      1 run_rbu 
      2 step_rbu 3 step_rbu_uri 4 step_rbu_state
      5 step_rbu_legacy
  } {
    foreach {tn schema} {
      1 {
        CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
      }
      2 { 
        CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);

Changes to ext/rbu/rbu_common.tcl.

    set rc [rbu step]
    check_poststep_state $rc $target $state
    rbu close
    if {$rc != "SQLITE_OK"} break
  }
  set rc
}

















proc do_rbu_vacuum_test {tn step} {
  forcedelete state.db
  uplevel [list do_test $tn.1 {
    if {$step==0} { sqlite3rbu_vacuum rbu test.db state.db }
    while 1 {
      if {$step==1} { sqlite3rbu_vacuum rbu test.db state.db }








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

    set rc [rbu step]
    check_poststep_state $rc $target $state
    rbu close
    if {$rc != "SQLITE_OK"} break
  }
  set rc
}

proc step_rbu_legacy {target rbu} {
  while 1 {
    sqlite3rbu rbu $target $rbu
    set state [rbu state]
    check_prestep_state $target $state
    set rc [rbu step]
    check_poststep_state $rc $target $state
    rbu close
    if {$rc != "SQLITE_OK"} break
    sqlite3 tmpdb $rbu
    tmpdb eval { DELETE FROM rbu_state WHERE k==10 }
    tmpdb close
  }
  set rc
}

proc do_rbu_vacuum_test {tn step} {
  forcedelete state.db
  uplevel [list do_test $tn.1 {
    if {$step==0} { sqlite3rbu_vacuum rbu test.db state.db }
    while 1 {
      if {$step==1} { sqlite3rbu_vacuum rbu test.db state.db }

Added ext/rbu/rbusplit.test.

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

# 2018 April 28
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
#

source [file join [file dirname [info script]] rbu_common.tcl]
set ::testprefix rbusplit

db close
sqlite3_shutdown
sqlite3_config_uri 1

autoinstall_test_functions

proc build_db {db} {
  $db eval {
    CREATE TABLE t1(a PRIMARY KEY, b, c);
    CREATE TABLE t2(a PRIMARY KEY, b, c);

    CREATE INDEX t1c ON t1(c);
  }
}

proc build_rbu {filename} {
  forcedelete $filename
  sqlite3 dbRbu $filename
  dbRbu eval {
    CREATE TABLE data0_t1(a, b, c, rbu_control);
    CREATE TABLE data1_t1(a, b, c, rbu_control);
    CREATE TABLE data2_t1(a, b, c, rbu_control);
    CREATE TABLE data3_t1(a, b, c, rbu_control);

    CREATE TABLE data_t2(a, b, c, rbu_control);

    INSERT INTO data0_t1 VALUES(1, 1, 1, 0);
    INSERT INTO data0_t1 VALUES(2, 2, 2, 0);
    INSERT INTO data0_t1 VALUES(3, 3, 3, 0);
    INSERT INTO data0_t1 VALUES(4, 4, 4, 0);
    INSERT INTO data1_t1 VALUES(5, 5, 5, 0);
    INSERT INTO data1_t1 VALUES(6, 6, 6, 0);
    INSERT INTO data1_t1 VALUES(7, 7, 7, 0);
    INSERT INTO data1_t1 VALUES(8, 8, 8, 0);
    INSERT INTO data3_t1 VALUES(9, 9, 9, 0);

    INSERT INTO data_t2 VALUES(1, 1, 1, 0);
    INSERT INTO data_t2 VALUES(2, 2, 2, 0);
    INSERT INTO data_t2 VALUES(3, 3, 3, 0);
    INSERT INTO data_t2 VALUES(4, 4, 4, 0);
    INSERT INTO data_t2 VALUES(5, 5, 5, 0);
    INSERT INTO data_t2 VALUES(6, 6, 6, 0);
    INSERT INTO data_t2 VALUES(7, 7, 7, 0);
    INSERT INTO data_t2 VALUES(8, 8, 8, 0);
    INSERT INTO data_t2 VALUES(9, 9, 9, 0);
  }
  
  dbRbu close
}

foreach {tn cmd} {
  1 run_rbu
  2 step_rbu
} {
  reset_db
  build_db db
  build_rbu testrbu.db

  do_test 1.$tn.1 {
    $cmd test.db testrbu.db
  } {SQLITE_DONE}
  do_execsql_test 1.$tn.1 {
    SELECT * FROM t1;
  } {
    1 1 1 2 2 2 3 3 3 4 4 4
    5 5 5 6 6 6 7 7 7 8 8 8
    9 9 9
  }
  do_execsql_test 1.$tn.2 {
    SELECT * FROM t2;
  } {
    1 1 1 2 2 2 3 3 3 4 4 4
    5 5 5 6 6 6 7 7 7 8 8 8
    9 9 9
  }
}

finish_test

Changes to ext/rbu/sqlite3rbu.c.

**
** RBU_STATE_COOKIE:
**   Valid if STAGE==1. The current change-counter cookie value in the 
**   target db file.
**
** RBU_STATE_OALSZ:
**   Valid if STAGE==1. The size in bytes of the *-oal file.




*/
#define RBU_STATE_STAGE        1
#define RBU_STATE_TBL          2
#define RBU_STATE_IDX          3
#define RBU_STATE_ROW          4
#define RBU_STATE_PROGRESS     5
#define RBU_STATE_CKPT         6
#define RBU_STATE_COOKIE       7
#define RBU_STATE_OALSZ        8
#define RBU_STATE_PHASEONESTEP 9


#define RBU_STAGE_OAL         1
#define RBU_STAGE_MOVE        2
#define RBU_STAGE_CAPTURE     3
#define RBU_STAGE_CKPT        4
#define RBU_STAGE_DONE        5

................................................................................

/*
** A structure to store values read from the rbu_state table in memory.
*/
struct RbuState {
  int eStage;
  char *zTbl;

  char *zIdx;
  i64 iWalCksum;
  int nRow;
  i64 nProgress;
  u32 iCookie;
  i64 iOalSz;
  i64 nPhaseOneStep;
................................................................................

/*
** Free an RbuState object allocated by rbuLoadState().
*/
static void rbuFreeState(RbuState *p){
  if( p ){
    sqlite3_free(p->zTbl);

    sqlite3_free(p->zIdx);
    sqlite3_free(p);
  }
}

/*
** Allocate an RbuState object and load the contents of the rbu_state 
................................................................................
      case RBU_STATE_OALSZ:
        pRet->iOalSz = (u32)sqlite3_column_int64(pStmt, 1);
        break;

      case RBU_STATE_PHASEONESTEP:
        pRet->nPhaseOneStep = sqlite3_column_int64(pStmt, 1);
        break;





      default:
        rc = SQLITE_CORRUPT;
        break;
    }
  }
  rc2 = sqlite3_finalize(pStmt);
................................................................................
          "(%d, %Q), "
          "(%d, %Q), "
          "(%d, %d), "
          "(%d, %d), "
          "(%d, %lld), "
          "(%d, %lld), "
          "(%d, %lld), "
          "(%d, %lld) ",

          p->zStateDb,
          RBU_STATE_STAGE, eStage,
          RBU_STATE_TBL, p->objiter.zTbl, 
          RBU_STATE_IDX, p->objiter.zIdx, 
          RBU_STATE_ROW, p->nStep, 
          RBU_STATE_PROGRESS, p->nProgress,
          RBU_STATE_CKPT, p->iWalCksum,
          RBU_STATE_COOKIE, (i64)pFd->iCookie,
          RBU_STATE_OALSZ, p->iOalSz,
          RBU_STATE_PHASEONESTEP, p->nPhaseOneStep

      )
    );
    assert( pInsert==0 || rc==SQLITE_OK );

    if( rc==SQLITE_OK ){
      sqlite3_step(pInsert);
      rc = sqlite3_finalize(pInsert);
................................................................................
  assert( p->rc==SQLITE_OK );
  if( pState->zTbl ){
    RbuObjIter *pIter = &p->objiter;
    int rc = SQLITE_OK;

    while( rc==SQLITE_OK && pIter->zTbl && (pIter->bCleanup 
       || rbuStrCompare(pIter->zIdx, pState->zIdx)
       || rbuStrCompare(pIter->zTbl, pState->zTbl) 

    )){
      rc = rbuObjIterNext(p, pIter);
    }

    if( rc==SQLITE_OK && !pIter->zTbl ){
      rc = SQLITE_ERROR;
      p->zErrmsg = sqlite3_mprintf("rbu_state mismatch error");

**
** RBU_STATE_COOKIE:
**   Valid if STAGE==1. The current change-counter cookie value in the 
**   target db file.
**
** RBU_STATE_OALSZ:
**   Valid if STAGE==1. The size in bytes of the *-oal file.
**
** RBU_STATE_DATATBL:
**   Only valid if STAGE==1. The RBU database name of the table 
**   currently being read.
*/
#define RBU_STATE_STAGE        1
#define RBU_STATE_TBL          2
#define RBU_STATE_IDX          3
#define RBU_STATE_ROW          4
#define RBU_STATE_PROGRESS     5
#define RBU_STATE_CKPT         6
#define RBU_STATE_COOKIE       7
#define RBU_STATE_OALSZ        8
#define RBU_STATE_PHASEONESTEP 9
#define RBU_STATE_DATATBL     10

#define RBU_STAGE_OAL         1
#define RBU_STAGE_MOVE        2
#define RBU_STAGE_CAPTURE     3
#define RBU_STAGE_CKPT        4
#define RBU_STAGE_DONE        5

................................................................................

/*
** A structure to store values read from the rbu_state table in memory.
*/
struct RbuState {
  int eStage;
  char *zTbl;
  char *zDataTbl;
  char *zIdx;
  i64 iWalCksum;
  int nRow;
  i64 nProgress;
  u32 iCookie;
  i64 iOalSz;
  i64 nPhaseOneStep;
................................................................................

/*
** Free an RbuState object allocated by rbuLoadState().
*/
static void rbuFreeState(RbuState *p){
  if( p ){
    sqlite3_free(p->zTbl);
    sqlite3_free(p->zDataTbl);
    sqlite3_free(p->zIdx);
    sqlite3_free(p);
  }
}

/*
** Allocate an RbuState object and load the contents of the rbu_state 
................................................................................
      case RBU_STATE_OALSZ:
        pRet->iOalSz = (u32)sqlite3_column_int64(pStmt, 1);
        break;

      case RBU_STATE_PHASEONESTEP:
        pRet->nPhaseOneStep = sqlite3_column_int64(pStmt, 1);
        break;

      case RBU_STATE_DATATBL:
        pRet->zDataTbl = rbuStrndup((char*)sqlite3_column_text(pStmt, 1), &rc);
        break;

      default:
        rc = SQLITE_CORRUPT;
        break;
    }
  }
  rc2 = sqlite3_finalize(pStmt);
................................................................................
          "(%d, %Q), "
          "(%d, %Q), "
          "(%d, %d), "
          "(%d, %d), "
          "(%d, %lld), "
          "(%d, %lld), "
          "(%d, %lld), "
          "(%d, %lld), "
          "(%d, %Q)  ",
          p->zStateDb,
          RBU_STATE_STAGE, eStage,
          RBU_STATE_TBL, p->objiter.zTbl, 
          RBU_STATE_IDX, p->objiter.zIdx, 
          RBU_STATE_ROW, p->nStep, 
          RBU_STATE_PROGRESS, p->nProgress,
          RBU_STATE_CKPT, p->iWalCksum,
          RBU_STATE_COOKIE, (i64)pFd->iCookie,
          RBU_STATE_OALSZ, p->iOalSz,
          RBU_STATE_PHASEONESTEP, p->nPhaseOneStep,
          RBU_STATE_DATATBL, p->objiter.zDataTbl
      )
    );
    assert( pInsert==0 || rc==SQLITE_OK );

    if( rc==SQLITE_OK ){
      sqlite3_step(pInsert);
      rc = sqlite3_finalize(pInsert);
................................................................................
  assert( p->rc==SQLITE_OK );
  if( pState->zTbl ){
    RbuObjIter *pIter = &p->objiter;
    int rc = SQLITE_OK;

    while( rc==SQLITE_OK && pIter->zTbl && (pIter->bCleanup 
       || rbuStrCompare(pIter->zIdx, pState->zIdx)
       || (pState->zDataTbl==0 && rbuStrCompare(pIter->zTbl, pState->zTbl))
       || (pState->zDataTbl && rbuStrCompare(pIter->zDataTbl, pState->zDataTbl))
    )){
      rc = rbuObjIterNext(p, pIter);
    }

    if( rc==SQLITE_OK && !pIter->zTbl ){
      rc = SQLITE_ERROR;
      p->zErrmsg = sqlite3_mprintf("rbu_state mismatch error");