SQLite Forum

Temporary file descriptor confusion
Login

Temporary file descriptor confusion

(1) By anonymous on 2022-12-06 08:51:28 [source]

Somewhere inside SQLite is a piece of code that gets confused by a zero value for the Unix file descriptor of a temporary file. I can reproduce it on macOS and Linux by throwing enough data at CREATE INDEX that it decides to use a temporary file for sorting. Code below.

#include <stdio.h>
#include <sqlite3.h>

static char const preamble[] =
    "create table t(c);"
    "begin immediate transaction;";

static char const insert[] =
    "insert into t values(randomblob(10000));";

static char const commit[] =
    "commit transaction;";

static char const indexify[] =
    "create index i on t(c);";

int main(void)
{
    int status;
    sqlite3 *db=NULL;
    char *errmsg=NULL;
    sqlite3_stmt *stmt=NULL;
    int i;

    status=sqlite3_open_v2(
        "",&db,SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE,NULL);
    if (status!=SQLITE_OK) {
        if (db) {
            fprintf(stderr,"sqlite3_open: %s\n",
                    sqlite3_errmsg(db));
        } else {
            fprintf(stderr,"sqlite3_open: %s\n",
                    sqlite3_errstr(status));
        }
        return 1;
    }
    status=sqlite3_exec(db,preamble,0,NULL,&errmsg);
    if (status!=SQLITE_OK) {
        fprintf(stderr,"sqlite3_exec(preamble): %s\n",errmsg);
        return 1;
    }
    status=sqlite3_prepare_v2(db,insert,-1,&stmt,NULL);
    if (status!=SQLITE_OK) {
        fprintf(stderr,"sqlite3_prepare(insert): %s\n",sqlite3_errmsg(db));
        return 1;
    }
    for (i=0; i<10000; i++) {
        status=sqlite3_step(stmt);
        if (status!=SQLITE_DONE) {
            fprintf(stderr,"sqlite3_step: %s\n",sqlite3_errmsg(db));
            return 1;
        }
        sqlite3_reset(stmt);
    }
    sqlite3_finalize(stmt);
    status=sqlite3_exec(db,commit,0,NULL,&errmsg);
    if (status!=SQLITE_OK) {
        fprintf(stderr,"sqlite3_exec(commit): %s\n",errmsg);
        return 1;
    }

    /* Remove the next line and the failure disappears! */
    fclose(stdin);

    status=sqlite3_exec(db,indexify,0,NULL,&errmsg);
    if (status!=SQLITE_OK) {
        fprintf(stderr,"sqlite3_exec(indexify): %s\n",errmsg);
        return 1;
    }
    sqlite3_close_v2(db);
    return 0;
}

(2) By Stephan Beal (stephan) on 2022-12-06 08:59:17 in reply to 1 [link] [source]

    /* Remove the next line and the failure disappears! */
    fclose(stdin);

Presumably, by "confused" you're referring to:

$ ./tfd 
sqlite3_exec(indexify): disk I/O error

If so, i can reproduce both it and your workaround on Linux Mint 21.

(3) By anonymous on 2022-12-06 09:23:01 in reply to 2 [link] [source]

Yes, that's it. Thank you for confirming my sanity.

(4) By Richard Hipp (drh) on 2022-12-06 11:43:02 in reply to 1 [link] [source]

Thanks for the bug report and the example problem. I now see what is happening.

The error arises from SQLite's MINIMUM_FILE_DESCRIPTOR defenses against database corrupt. See https://www.sqlite.org/howtocorrupt.html#_continuing_to_use_a_file_descriptor_after_it_has_been_closed for background information.

In your program, SQLite is trying to create a new, unique temporary file (named, for example, "/var/tmp/etilqs_1062fa79f017d944", though the name will always be different). It tries once and gets back file descriptor

  1. It then closes that file, reopens /dev/null to consume file descriptor 0 and then tried again.

But on the second attempt it fails because now the file already exists and for the case of temporary files, the O_EXCL and O_CREAT flags are both passed, causing the open to fail.

Your work-around is simple: Don't close stdin while SQLite is running. :-)

I'll try to come up with a real solution later this morning.

(5) By anonymous on 2022-12-06 12:46:51 in reply to 4 [link] [source]

Since this is Unix-specific code, the dup system call might help.

(6) By Richard Hipp (drh) on 2022-12-06 13:15:51 in reply to 4 [link] [source]

Fixed by check-in c0cfe0582add8798. For the record, here is a slight simplification to the original test case:


#include <stdio.h>
#include <sqlite3.h>

static char const preamble[] =
    "CREATE TEMP TABLE t(c);"
    "WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)"
    "  INSERT INTO t(c) SELECT randomblob(10000) FROM c;";

static void errorLogCallback(void *pArg, int iErrCode, const char *zMsg){
  fprintf(stderr, "(%d) %s\n", iErrCode, zMsg);
}

static int resultCallback(void *NotUsed, int argc, char **argv, char **colv){
  int i;
  for(i=0; i<argc; i++){
    const char *z = (const char*)argv[i];
    if( z==0 ) z = "NULL";
    if( i>0 ) printf(",");
    printf("%s", z);
  }
  printf("\n");
  return 0;
}

int main(void){
  int status;
  sqlite3 *db=NULL;
  char *errmsg=NULL;
  sqlite3_stmt *stmt=NULL;
  int i;

  sqlite3_config(SQLITE_CONFIG_LOG, errorLogCallback, 0);
  status = sqlite3_open("b1.db", &db);
  if (status!=SQLITE_OK) {
    fprintf(stderr,"sqlite3_open: %s\n",sqlite3_errmsg(db));
    return 1;
  }
  status=sqlite3_exec(db,preamble,0,NULL,&errmsg);
  if (status!=SQLITE_OK) {
    fprintf(stderr,"sqlite3_exec(preamble): %s\n",errmsg);
    return 1;
  }

  /* Remove the next line and the failure disappears! */
  fclose(stdin);

  status=sqlite3_exec(db,"CREATE INDEX i ON t(c)",0,NULL,&errmsg);
  if (status!=SQLITE_OK) {
    fprintf(stderr,"sqlite3_exec(indexify): %s\n",errmsg);
    return 1;
  }
  sqlite3_exec(db, "SELECT count(*) FROM t;", resultCallback, 0, 0);
  sqlite3_exec(db, "PRAGMA integrity_check;", resultCallback, 0, 0);
  sqlite3_close_v2(db);

  printf(
    "^^^^^^^^----- should be (approximately):\n"
    "  (28) attempt to open \"/var/tmp/etilqs_...\" as file descriptor 0\n"
    "  100\n"
    "  ok\n"
  );
  return 0;
}

(7) By anonymous on 2022-12-07 10:22:27 in reply to 6 [link] [source]

Thank you!