[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
7 messages Options
Reply | Threaded
Open this post in threaded view
|

[BUG REPORT] Open blob is invalidated by the update to unrelated fields in the same row

Yuri
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
Reply | Threaded
Open this post in threaded view
|

Re: [BUG REPORT] Open blob is invalidated by the update to unrelated fields in the same row

Dominique Devienne
On Sat, Mar 4, 2017 at 8:21 AM, Yuri <[hidden email]> wrote:

> The write operation using the open sqlite3_blob object fails after some
> other field in the same row is updated.
>

This is clearly documented in https://www.sqlite.org/c3ref/blob_open.html
though, so I'm afraid this is "by design". --DD

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.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [BUG REPORT] Open blob is invalidated by the update to unrelated fields in the same row

Yuri
On 03/06/2017 01:00, Dominique Devienne wrote:
> This is clearly documented inhttps://www.sqlite.org/c3ref/blob_open.html
> though, so I'm afraid this is "by design". --DD


Even though this is documented, parts of this limitation don't appear to
be reasonable. Updating an integer field in the same row shouldn't
affect the blob field. Rows can be very large and shouldn't move when
individual fields are updated.


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
|

Re: [BUG REPORT] Open blob is invalidated by the update to unrelated fields in the same row

Dominique Devienne
On Mon, Mar 6, 2017 at 11:39 AM, Yuri <[hidden email]> wrote:

> On 03/06/2017 01:00, Dominique Devienne wrote:
>
>> This is clearly documented inhttps://www.sqlite.org/c3ref/blob_open.html
>> though, so I'm afraid this is "by design". --DD
>>
>
> Even though this is documented, parts of this limitation don't appear to
> be reasonable. Updating an integer field in the same row shouldn't affect
> the blob field. Rows can be very large and shouldn't move when individual
> fields are updated.


Don't get me wrong, I'm not fond of SQLite blobs as they stand, and I've
said so before on this list.
But modulo the bug you found (mixup of rowids across tables, as mentioned
by Clemens), there's
not much any of us can do, this is in DRH's hands, and from my past threads
here, not much is likely
to happen in this regard.

Changing the way blobs work in SQLite would probably require a format
change (I think),
and that alone is a big enough change that makes it rather unlikely I'm
afraid.

FWIW, there are two main issues with blobs IMHO:
1) they are always stored "in-row". Oracle for example only stores the blob
"index" in-row,
  i.e. which "blob" pages make up the blob. This makes accessing columns
(notably later added columns)
  after the blobs very expensive with large blobs. But makes schema
evolution more difficult than it should be.
  And the advice to putting blobs in separate tables is just a workaround,
requires joins, and makes it more
  difficult to ensure 1-to-1 relationship between the "meta" row and
"bulk-data" row, and associated lifetime management.
2) they can't be updated incrementally generally, except in very limited
cases, and incurring more IO than should be.
  with "dual stage" blobs (or the proverbial level of indirection), if you
want to change a few bytes in the middle, the
  IO cost if updating the blob index and update that one "blob" page that
needs updating. Growing or shrinking can
  similarly be supported transactionally, w/o having to copy the whole blob.

SQLite works well with blobs, as long as they stay small, or they are never
updated.
Outside these use cases, things start the break down and you must either
resort to work-arounds,
and stop using SQLite for those bulk-data. And that's a pity/shame IMHO. My
$0.02 :)  --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [BUG REPORT] Open blob is invalidated by the update to unrelated fields in the same row

Hick Gunter
In reply to this post by Yuri
AFAICT It is not the new value of the integer but rather the new contents of the blob field that causes the record image to grow and exceed the previously allocated space, which means the row has to move. Rewriting the record and thus expiring the blob handle is triggered by the update of the other field.

You might like to read up on SQLite database and record format to gain insight into this.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Yuri
Gesendet: Montag, 06. März 2017 11:39
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] [BUG REPORT] Open blob is invalidated by the update to unrelated fields in the same row

On 03/06/2017 01:00, Dominique Devienne wrote:
> This is clearly documented
> inhttps://www.sqlite.org/c3ref/blob_open.html
> though, so I'm afraid this is "by design". --DD


Even though this is documented, parts of this limitation don't appear to be reasonable. Updating an integer field in the same row shouldn't affect the blob field. Rows can be very large and shouldn't move when individual fields are updated.


Yuri

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


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


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

Re: [BUG REPORT] Open blob is invalidated by the update to unrelated fields in the same row

Clemens Ladisch
In reply to this post by Yuri
Yuri wrote:
> Updating an integer field in the same row shouldn't affect the blob field.

Integer fields have a variable size (0 to 8 bytes):
http://www.sqlite.org/fileformat2.html#record_format


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

Re: [BUG REPORT] Open blob is invalidated by the update to unrelated fields in the same row

Scott Robison-2
In reply to this post by Yuri
On Mon, Mar 6, 2017 at 3:39 AM, Yuri <[hidden email]> wrote:

> On 03/06/2017 01:00, Dominique Devienne wrote:
>
>> This is clearly documented inhttps://www.sqlite.org/c3ref/blob_open.html
>> though, so I'm afraid this is "by design". --DD
>>
>
>
> Even though this is documented, parts of this limitation don't appear to
> be reasonable. Updating an integer field in the same row shouldn't affect
> the blob field. Rows can be very large and shouldn't move when individual
> fields are updated.


The potential difficulty here is that integers are not encoded as fixed
sized fields. Depending on the magnitude of the integer that is being
written, it could change sizes from 0 to 9 bytes. Thus the offset of the
blob in the row may change.

Once the fix for the "any table same rowid" problem is available to you,
your best bet is the separate blob table (which I think I read you've
already tried).
--
Scott Robison
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users