CODEWITHSUNDEEP
pythonsqliteblobzodbrelstorage
Woltan
Woltan

Reputation: 14033

How to use blobs > 1GB in zodb relstorage with sqlite backend?

I am trying to save a blob > 1GB via relstorage in a sqlite backend. The following minimum working example

Minimum Working Example:

import os
import shutil
from ZODB import blob, config

connectionString = """
%import relstorage
<zodb main>
<relstorage>
    blob-dir blob
    keep-history false
    cache-local-mb 0
    <sqlite3>
        data-dir .
    </sqlite3>
</relstorage>
</zodb>
"""

# cleaning up
for x in os.listdir():
    if "sqlite" in x:
        os.remove(x)

shutil.rmtree("blob", True)

# creating database
db = config.databaseFromString(connectionString)

with db.transaction() as conn:
    conn.root.blob = blob.Blob()
    with conn.root.blob.open("w") as f:
        f.write(b"\0" * 1024 ** 3)

During the commit (at the __exit__ method of the db.transaction with-block) of the newly created object an error occurs with the following final exception:

sqlite3.InterfaceError: Error binding parameter 2 - probably unsupported type.

Saving a blob with size 1024 ** 2 does not raise the exception.

How is it possible to save a blob in a sqlite backend using relstorage without the usage of a shared blob directory?

Upvotes: 0

Views: 387

Answers (2)

Peter Brittain
Peter Brittain

Reputation: 13629

According to my research, you can't store arbitrary sized blobs in this setup. You are hitting the sqlite blob size limit (https://www.sqlite.org/limits.html).

Your only options at this stage appear to be:

  1. Don't store the blob in sqlite, e.g. by using a shared blob directory.
  2. Increase the blob limit, using a custom build of sqlite (as covered in the above link).
  3. Decrease the size of what you store - e.g. by compressing your blobs, or chunking your data into smaller blobs.
  4. Move to a different storage backend.

Personally, I would be inclined to use the shared blob directory if I was using sqlite, but you have discounted that option, so I think your choices are limited.

Upvotes: 3

Basile Starynkevitch
Basile Starynkevitch

Reputation: 1

I am trying to save a blob > 1GB via relstorage in a sqlite backend.

A related answer is this one.

My suggestion would be to handle differently large blobs (e.g. bigger than 8 to 32MBytes in 2020) and small ones.

Large content would be saved as files, and your sqlite database would know (and store) the file path (e.g. some generated file path such as /var/tmp/bigblob1234 on Linux. See hier(7) and inode(7)...).

Small content would be saved as SQLITE blobs.

Of course both your database schema and the Python code using it should be improved. Be aware of database normalization.

Upvotes: 0

Related Questions