SQLite

Check-in [ba1afc0401]
Login

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

Overview
Comment:Alternative fix for ticket #3810. This is a replacement for check-in (6956). (CVS 6960)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: ba1afc040171810d0c996708d7b9cb11abcd99d8
User & Date: drh 2009-08-06 17:43:31.000
References
2020-11-05
19:13
Suppress errors associated with TEMP triggers that reference objects in non-TEMP databases. This is a continuation of the fix for ticket #3810 shown in check-in [ba1afc040171810d] from 2009-08-06, based on a bug report in forum post 157dc791df (check-in: 991ca9b26b user: drh tags: trunk)
Context
2009-08-06
18:36
Mark the rtreeUpdate function as static. (CVS 6961) (check-in: b6bdfdc69d user: danielk1977 tags: trunk)
17:43
Alternative fix for ticket #3810. This is a replacement for check-in (6956). (CVS 6960) (check-in: ba1afc0401 user: drh tags: trunk)
17:40
Change a hyperlink label on shared cache mode documentation. No changes to code. (CVS 6959) (check-in: 3d08ca0e54 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/prepare.c.
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains the implementation of the sqlite3_prepare()
** interface, and routines that contribute to loading the database schema
** from disk.
**
** $Id: prepare.c,v 1.130 2009/08/01 16:27:00 drh Exp $
*/
#include "sqliteInt.h"

/*
** Fill the InitData structure with an error message that indicates
** that the database is corrupt.
*/
static void corruptSchema(
  InitData *pData,     /* Initialization context */
  const char *zObj,    /* Object being parsed at the point of error */
  const char *zExtra   /* Error information */
){
  sqlite3 *db = pData->db;
  if( pData->iDb==1 && zObj && zExtra ){
    *pData->pzErrMsg = sqlite3MPrintf(db, "in %s: %s", zObj, zExtra);
    pData->rc = SQLITE_ERROR;
    return;
  }
  if( !db->mallocFailed && (db->flags & SQLITE_RecoveryMode)==0 ){
    if( zObj==0 ) zObj = "?";
    sqlite3SetString(pData->pzErrMsg, db,
      "malformed database schema (%s)", zObj);
    if( zExtra ){
      *pData->pzErrMsg = sqlite3MAppendf(db, *pData->pzErrMsg, 
                                 "%s - %s", *pData->pzErrMsg, zExtra);







|













<
<
<
<
<







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29





30
31
32
33
34
35
36
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains the implementation of the sqlite3_prepare()
** interface, and routines that contribute to loading the database schema
** from disk.
**
** $Id: prepare.c,v 1.131 2009/08/06 17:43:31 drh Exp $
*/
#include "sqliteInt.h"

/*
** Fill the InitData structure with an error message that indicates
** that the database is corrupt.
*/
static void corruptSchema(
  InitData *pData,     /* Initialization context */
  const char *zObj,    /* Object being parsed at the point of error */
  const char *zExtra   /* Error information */
){
  sqlite3 *db = pData->db;





  if( !db->mallocFailed && (db->flags & SQLITE_RecoveryMode)==0 ){
    if( zObj==0 ) zObj = "?";
    sqlite3SetString(pData->pzErrMsg, db,
      "malformed database schema (%s)", zObj);
    if( zExtra ){
      *pData->pzErrMsg = sqlite3MAppendf(db, *pData->pzErrMsg, 
                                 "%s - %s", *pData->pzErrMsg, zExtra);
81
82
83
84
85
86
87

88
89
90
91



92
93
94
95
96

97
98
99
100
101
102
103
    ** structures that describe the table, index, or view.
    */
    char *zErr;
    int rc;
    assert( db->init.busy );
    db->init.iDb = iDb;
    db->init.newTnum = atoi(argv[1]);

    rc = sqlite3_exec(db, argv[2], 0, 0, &zErr);
    db->init.iDb = 0;
    assert( rc!=SQLITE_OK || zErr==0 );
    if( SQLITE_OK!=rc ){



      pData->rc = rc;
      if( rc==SQLITE_NOMEM ){
        db->mallocFailed = 1;
      }else if( rc!=SQLITE_INTERRUPT && rc!=SQLITE_LOCKED ){
        corruptSchema(pData, argv[0], zErr);

      }
      sqlite3DbFree(db, zErr);
    }
  }else if( argv[0]==0 ){
    corruptSchema(pData, 0, 0);
  }else{
    /* If the SQL column is blank it means this is an index that







>




>
>
>
|
|
|
|
|
>







76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
    ** structures that describe the table, index, or view.
    */
    char *zErr;
    int rc;
    assert( db->init.busy );
    db->init.iDb = iDb;
    db->init.newTnum = atoi(argv[1]);
    db->init.orphanTrigger = 0;
    rc = sqlite3_exec(db, argv[2], 0, 0, &zErr);
    db->init.iDb = 0;
    assert( rc!=SQLITE_OK || zErr==0 );
    if( SQLITE_OK!=rc ){
      if( db->init.orphanTrigger ){
        assert( iDb==1 );
      }else{
        pData->rc = rc;
        if( rc==SQLITE_NOMEM ){
          db->mallocFailed = 1;
        }else if( rc!=SQLITE_INTERRUPT && rc!=SQLITE_LOCKED ){
          corruptSchema(pData, argv[0], zErr);
        }
      }
      sqlite3DbFree(db, zErr);
    }
  }else if( argv[0]==0 ){
    corruptSchema(pData, 0, 0);
  }else{
    /* If the SQL column is blank it means this is an index that
Changes to src/sqliteInt.h.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2001 September 15
**
** 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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.896 2009/07/28 16:44:26 danielk1977 Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Include the configuration header output by 'configure' if we're using the
** autoconf-based build













|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
/*
** 2001 September 15
**
** 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.
**
*************************************************************************
** Internal interface definitions for SQLite.
**
** @(#) $Id: sqliteInt.h,v 1.897 2009/08/06 17:43:31 drh Exp $
*/
#ifndef _SQLITEINT_H_
#define _SQLITEINT_H_

/*
** Include the configuration header output by 'configure' if we're using the
** autoconf-based build
796
797
798
799
800
801
802

803
804
805
806
807
808
809
  int nTotalChange;             /* Value returned by sqlite3_total_changes() */
  sqlite3_mutex *mutex;         /* Connection mutex */
  int aLimit[SQLITE_N_LIMIT];   /* Limits */
  struct sqlite3InitInfo {      /* Information used during initialization */
    int iDb;                    /* When back is being initialized */
    int newTnum;                /* Rootpage of table being initialized */
    u8 busy;                    /* TRUE if currently initializing */

  } init;
  int nExtension;               /* Number of loaded extensions */
  void **aExtension;            /* Array of shared library handles */
  struct Vdbe *pVdbe;           /* List of active virtual machines */
  int activeVdbeCnt;            /* Number of VDBEs currently executing */
  int writeVdbeCnt;             /* Number of active VDBEs that are writing */
  void (*xTrace)(void*,const char*);        /* Trace function */







>







796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
  int nTotalChange;             /* Value returned by sqlite3_total_changes() */
  sqlite3_mutex *mutex;         /* Connection mutex */
  int aLimit[SQLITE_N_LIMIT];   /* Limits */
  struct sqlite3InitInfo {      /* Information used during initialization */
    int iDb;                    /* When back is being initialized */
    int newTnum;                /* Rootpage of table being initialized */
    u8 busy;                    /* TRUE if currently initializing */
    u8 orphanTrigger;           /* Last statement is orphaned TEMP trigger */
  } init;
  int nExtension;               /* Number of loaded extensions */
  void **aExtension;            /* Array of shared library handles */
  struct Vdbe *pVdbe;           /* List of active virtual machines */
  int activeVdbeCnt;            /* Number of VDBEs currently executing */
  int writeVdbeCnt;             /* Number of active VDBEs that are writing */
  void (*xTrace)(void*,const char*);        /* Trace function */
Changes to src/trigger.c.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
/*
**
** 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.
**
*************************************************************************
**
**
** $Id: trigger.c,v 1.141 2009/05/28 01:00:55 drh Exp $
*/
#include "sqliteInt.h"

#ifndef SQLITE_OMIT_TRIGGER
/*
** Delete a linked list of TriggerStep structures.
*/












|







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
/*
**
** 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.
**
*************************************************************************
**
**
** $Id: trigger.c,v 1.142 2009/08/06 17:43:31 drh Exp $
*/
#include "sqliteInt.h"

#ifndef SQLITE_OMIT_TRIGGER
/*
** Delete a linked list of TriggerStep structures.
*/
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
  int op,             /* One of TK_INSERT, TK_UPDATE, TK_DELETE */
  IdList *pColumns,   /* column list if this is an UPDATE OF trigger */
  SrcList *pTableName,/* The name of the table/view the trigger applies to */
  Expr *pWhen,        /* WHEN clause */
  int isTemp,         /* True if the TEMPORARY keyword is present */
  int noErr           /* Suppress errors if the trigger already exists */
){
  Trigger *pTrigger = 0;
  Table *pTab;
  char *zName = 0;        /* Name of the trigger */
  sqlite3 *db = pParse->db;
  int iDb;                /* The database to store the trigger in */
  Token *pName;           /* The unqualified db name */
  DbFixer sFix;
  int iTabDb;

  assert( pName1!=0 );   /* pName1->z might be NULL, but not pName1 itself */
  assert( pName2!=0 );
  assert( op==TK_INSERT || op==TK_UPDATE || op==TK_DELETE );
  assert( op>0 && op<0xff );
  if( isTemp ){
    /* If TEMP was specified, then the trigger name may not be qualified. */







|
|

|


|
|







82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
  int op,             /* One of TK_INSERT, TK_UPDATE, TK_DELETE */
  IdList *pColumns,   /* column list if this is an UPDATE OF trigger */
  SrcList *pTableName,/* The name of the table/view the trigger applies to */
  Expr *pWhen,        /* WHEN clause */
  int isTemp,         /* True if the TEMPORARY keyword is present */
  int noErr           /* Suppress errors if the trigger already exists */
){
  Trigger *pTrigger = 0;  /* The new trigger */
  Table *pTab;            /* Table that the trigger fires off of */
  char *zName = 0;        /* Name of the trigger */
  sqlite3 *db = pParse->db;  /* The database connection */
  int iDb;                /* The database to store the trigger in */
  Token *pName;           /* The unqualified db name */
  DbFixer sFix;           /* State vector for the DB fixer */
  int iTabDb;             /* Index of the database holding pTab */

  assert( pName1!=0 );   /* pName1->z might be NULL, but not pName1 itself */
  assert( pName2!=0 );
  assert( op==TK_INSERT || op==TK_UPDATE || op==TK_DELETE );
  assert( op>0 && op<0xff );
  if( isTemp ){
    /* If TEMP was specified, then the trigger name may not be qualified. */
134
135
136
137
138
139
140











141
142
143
144
145
146
147
  if( sqlite3FixInit(&sFix, pParse, iDb, "trigger", pName) && 
      sqlite3FixSrcList(&sFix, pTableName) ){
    goto trigger_cleanup;
  }
  pTab = sqlite3SrcListLookup(pParse, pTableName);
  if( !pTab ){
    /* The table does not exist. */











    goto trigger_cleanup;
  }
  if( IsVirtual(pTab) ){
    sqlite3ErrorMsg(pParse, "cannot create triggers on virtual tables");
    goto trigger_cleanup;
  }








>
>
>
>
>
>
>
>
>
>
>







134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
  if( sqlite3FixInit(&sFix, pParse, iDb, "trigger", pName) && 
      sqlite3FixSrcList(&sFix, pTableName) ){
    goto trigger_cleanup;
  }
  pTab = sqlite3SrcListLookup(pParse, pTableName);
  if( !pTab ){
    /* The table does not exist. */
    if( db->init.iDb==1 ){
      /* Ticket #3810.
      ** Normally, whenever a table is dropped, all associated triggers are
      ** dropped too.  But if a TEMP trigger is created on a non-TEMP table
      ** and the table is dropped by a different database connection, the
      ** trigger is not visible to the database connection that does the
      ** drop so the trigger cannot be dropped.  This results in an
      ** "orphaned trigger" - a trigger whose associated table is missing.
      */
      db->init.orphanTrigger = 1;
    }
    goto trigger_cleanup;
  }
  if( IsVirtual(pTab) ){
    sqlite3ErrorMsg(pParse, "cannot create triggers on virtual tables");
    goto trigger_cleanup;
  }

Changes to test/tkt3810.test.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25


26
27
28
29
30
31
32
# 2009 August 1
#
# 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.
#
#***********************************************************************
#
# Tests to make sure #3810 is fixed.
#
# $Id: tkt3810.test,v 1.3 2009/08/01 18:22:30 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Create a table using the first database connection.
#
do_test tkt3810-1.1 {
  execsql {
    CREATE TABLE t1(x);
    INSERT INTO t1 VALUES(123);
    SELECT * FROM t1;


  }
} 123

# Create a second connection to the same database.  Make sure the
# schema of the database has been parsed by the second connection.
#
do_test tkt3810-2 {













|











>
>







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
# 2009 August 1
#
# 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.
#
#***********************************************************************
#
# Tests to make sure #3810 is fixed.
#
# $Id: tkt3810.test,v 1.4 2009/08/06 17:43:31 drh Exp $

set testdir [file dirname $argv0]
source $testdir/tester.tcl

# Create a table using the first database connection.
#
do_test tkt3810-1.1 {
  execsql {
    CREATE TABLE t1(x);
    INSERT INTO t1 VALUES(123);
    SELECT * FROM t1;
    CREATE TABLE t2(y);
    CREATE TABLE t3(z);
  }
} 123

# Create a second connection to the same database.  Make sure the
# schema of the database has been parsed by the second connection.
#
do_test tkt3810-2 {
40
41
42
43
44
45
46
47
48
49
50
51
52

53























54
55
56
57
# but the first connection does not yet know this.  Then try to create a TEMP
# trigger in the first connection that references the table that was dropped.
#
do_test tkt3810-3 {
  execsql {DROP TABLE t1} db2
  execsql {
     CREATE TEMP TRIGGER r1 AFTER INSERT ON t1 BEGIN
       INSERT INTO t1 VALUES(2345);
     END;
  }
  catchsql {
    SELECT * FROM t1;
  }

} {1 {in r1: no such table: t1}}
























db2 close

finish_test







|



|

>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>




42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
# but the first connection does not yet know this.  Then try to create a TEMP
# trigger in the first connection that references the table that was dropped.
#
do_test tkt3810-3 {
  execsql {DROP TABLE t1} db2
  execsql {
     CREATE TEMP TRIGGER r1 AFTER INSERT ON t1 BEGIN
       INSERT INTO t2 VALUES(new.rowid);
     END;
  }
  catchsql {
    SELECT * FROM t3;
  }
} {0 {}}

# Trigger still exists in the sqlite_temp_master table, but now it is
# an orphan.
#
do_test tkt3810-4 {
  execsql {SELECT name FROM sqlite_temp_master ORDER BY name}
} {r1}

# Because it is an orphan, it cannot be dropped.
#
do_test tkt3810-5 {
  catchsql {DROP TRIGGER r1}
} {1 {no such trigger: r1}}

# Create a table t1 then drop the table in order to drop the orphaned
# trigger.
#
do_test tkt3810-6 {
  execsql {CREATE TABLE t1(x)} db2
  execsql {DROP TABLE t1}
  execsql {
    SELECT name FROM sqlite_temp_master;
  }
} {}

db2 close

finish_test