Not a Bug -- works as documented: [BUG REPORT] Open blob is invalidated by the update to unrelated fields in the same row

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Not a Bug -- works as documented: [BUG REPORT] Open blob is invalidated by the update to unrelated fields in the same row

Keith Medcalf

Working as coded (and as documented):

https://sqlite.org/c3ref/blob_open.html

In particular read the paragraph that says:

If the row that a BLOB handle points to is modified by an UPDATE, DELETE, or by ON CONFLICT side-effects then the BLOB handle is marked as "expired". This is true if any column of the row is changed, even a column other than the one the BLOB handle is open on. Calls to sqlite3_blob_read() and sqlite3_blob_write() for an expired BLOB handle fail with a return code of SQLITE_ABORT. Changes written into a BLOB prior to the BLOB expiring are not rolled back by the expiration of the BLOB. Such changes will eventually commit if the transaction continues to completion.

Even though you do not bother to say what the error code is, it is 4.  Which means SQLITE_ABORT ...


> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Yuri
> Sent: Saturday, 4 March, 2017 00:21
> To: [hidden email]
> Subject: [sqlite] [BUG REPORT] Open blob is invalidated by the update to
> unrelated fields in the same row
>
> The write operation using the open sqlite3_blob object fails after some
> other field in the same row is updated.
>
> The testcase below illustrates the problem.
>
>
> Yuri
>
>
>
> ---testcase---
>
> #include <stdio.h>
> #include <stdlib.h>
> #include "sqlite3.h"
>
> int main(int argc, char **argv) {
>          if(argc < 2) {
>                  fprintf(stderr, "Usage: %s <dbname> <filename>\n",
> argv[0]);
>                  exit(1);
>          }
>
>          // file to insert
>          FILE *f = fopen(argv[2], "rb");
>          if(NULL == f) {
>                  fprintf(stderr, "Couldn't open file %s\n", argv[2]);
>                  exit(1);
>          }
>          // Calculate size of file
>          fseek(f, 0, SEEK_END);
>          long filesize = ftell(f);
>          fseek(f, 0, SEEK_SET);
>
>          // Table name we're going to use
>          char tablename[] = "testblob";
>          char columnname[] = "blobby";
>
>          // Actual database handle
>          sqlite3 *db = NULL;
>
>          // Database commands
>          char create_sql[1024];
>          snprintf(create_sql, sizeof(create_sql), "CREATE TABLE IF NOT
> EXISTS %s ("
>                          "id INTEGER PRIMARY KEY, fld INTEGER, %s
> BLOB)", tablename, columnname);
>
>          // Going to insert a zeroblob of the size of the file
>          char insert_sql[1024];
>          snprintf(insert_sql, sizeof(insert_sql), "INSERT INTO %s (%s)
> VALUES (?)", tablename, columnname);
>
>          // SQLite return value
>          int rc;
>          // Open the database
>          rc = sqlite3_open(argv[1], &db);
>          if(SQLITE_OK != rc) {
>                  fprintf(stderr, "Can't open database %s (%i): %s\n",
> argv[1], rc, sqlite3_errmsg(db));
>                  exit(1);
>          }
>
>          char *exec_errmsg;
>          rc = sqlite3_exec(db, create_sql, NULL, NULL, &exec_errmsg);
>          if(SQLITE_OK != rc) {
>                  fprintf(stderr, "Can't create table (%i): %s\n", rc,
> sqlite3_errmsg(db));
>                  sqlite3_close(db);
>                  exit(1);
>          }
>          sqlite3_stmt *insert_stmt;
>          rc = sqlite3_prepare_v2(db, insert_sql, -1, &insert_stmt, NULL);
>          if(SQLITE_OK != rc) {
>                  fprintf(stderr, "Can't prepare insert statment %s (%i):
> %s\n", insert_sql, rc, sqlite3_errmsg(db));
>                  sqlite3_close(db);
>                  exit(1);
>          }
>          // Bind a block of zeros the size of the file we're going to
> insert later
>          sqlite3_bind_zeroblob(insert_stmt, 1, filesize);
>          if(SQLITE_DONE != (rc = sqlite3_step(insert_stmt))) {
>                  fprintf(stderr, "Insert statement didn't work (%i):
> %s\n", rc, sqlite3_errmsg(db));
>                  exit(1);
>          }
>
>          sqlite3_int64 rowid = sqlite3_last_insert_rowid(db);
>          printf("Created a row, id %i, with a blank blob size %i\n",
> (int)rowid, (int)filesize);
>
>          sqlite3_blob *blob;
>          rc = sqlite3_blob_open(db, "main", tablename, columnname,
> rowid, 1, &blob);
>          if(SQLITE_OK != rc) {
>                  fprintf(stderr, "Couldn't get blob handle (%i): %s\n",
> rc, sqlite3_errmsg(db));
>                  exit(1);
>          }
>
>          const int BLOCKSIZE = 1024;
>          int len;
>          void *block = malloc(BLOCKSIZE);
>          int offset = 0;
>          while(0 < (len = fread(block, 1, BLOCKSIZE, f))) {
>                  if(SQLITE_OK != (rc = sqlite3_blob_write(blob, block,
> len, offset))) {
>                          fprintf(stderr, "Error writing to blob handle.
> Offset %i, len %i, rc=%d\n", offset, len, rc);
>                          exit(1);
>                  }
>                  offset+=len;
>                  printf("... wrote block @off=%d\n", offset);
>
>                  // update that breaks the following sqlite3_blob_write
>
>                  if(SQLITE_OK != (rc = sqlite3_exec(db, "UPDATE testblob
> SET fld=1", NULL, NULL, &exec_errmsg))) {
>                          fprintf(stderr, "Error while updating the
> integer field, error=%d\n", rc);
>                          exit(1);
>                  }
>
>          }
>
>          sqlite3_blob_close(blob);
>
>          printf("Successfully wrote to blob\n");
>
>          free(block);
>
>          fclose(f);
>          sqlite3_finalize(insert_stmt);
>          sqlite3_close(db);
>          return 0;
> }
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Not a Bug -- works as documented: [BUG REPORT] Open blob is invalidated by the update to unrelated fields in the same row

Yuri
On 03/04/2017 00:08, Keith Medcalf wrote:
> If the row that a BLOB handle points to is modified by an UPDATE, DELETE, or by ON CONFLICT side-effects then the BLOB handle is marked as "expired". This is true if any column of the row is changed, even a column other than the one the BLOB handle is open on. Calls to sqlite3_blob_read() and sqlite3_blob_write() for an expired BLOB handle fail with a return code of SQLITE_ABORT. Changes written into a BLOB prior to the BLOB expiring are not rolled back by the expiration of the BLOB. Such changes will eventually commit if the transaction continues to completion.

What is the reason to expire the blob handle when unrelated fields are
modified? I just don't see how and why is this a reasonable behavior?
Blob lives as a separate object, and the pointer to it should be able to
stay valid unless its field is modified.

Another bug is that reopening the blob every time is incredibly slow.
Why is opening/closing the blob is so much slower? It also seems to get
slower with the size of the data in the blob.

Yuri
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Not a Bug -- works as documented: [BUG REPORT] Open blob is invalidated by the update to unrelated fields in the same row

Simon Slavin-3

On 4 Mar 2017, at 11:25pm, Yuri <[hidden email]> wrote:

> What is the reason to expire the blob handle when unrelated fields are modified?

Because the BLOB has moved in the file.  Because SQLite stores all of a row’s data in one continuous chunk.  When you change any field it has to write the entire row of data back to the database file and the BLOB can end up in a different place.  (Don’t forget that it’s not as simple as changing the contents of the database file since the row is actually written to the journal file first.)

> Another bug is that reopening the blob every time is incredibly slow. Why is opening/closing the blob is so much slower? It also seems to get slower with the size of the data in the blob.

Related to your first question.  SQLite has to read through the data for that row in order to find out where the BLOB starts.  Then it has to read through the blob until it finds the octet you want to modify.  In the database a BLOB may be spread over several non-contigous pages.  So finding the start of the bit you want to modify is non-trivial.

It does seem to be that you are using BLOBs as a continuous data store.  If you are modifying just one BLOB at a time it might be worth reading it from the file, keeping it in memory until you are finished with the modifications, then writing it back in one chunk using UPDATE.

PS: This list uses a specific meaning for 'bug'.  It means that SQLite gives the wrong result back to the programmer, either by storing the wrong thing in the database or by reporting the wrong thing when reading the database or by corrupting the database.  Things that aren’t bugs include opportunities to improve speed.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...