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

[aalok-sathe]

Encountered this error during a write to the database using the library.
The objects being written were collections.Counter[Tuple[str]] and Dict[str, Counter[Tuple[str]]]

Both types of objects previously worked fine in being written to the DB, but this time, it threw an error. Not sure why, and I haven't been able to narrow it down any further, but it did corrupt my existing DB that was being built over a long process with successive (batched) write operations.

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

[piskvorky]

What version of Sqlitedict and Python? Did you upgrade sqlitedict / python / OS / something-else recently?

I've never seen that error before :(

[aalok-sathe]

Nope, no recent updates to anything.

Versions:

• Python: 3.9.7
• Sqlitedict:

[[package]]
name = "sqlitedict"
version = "2.0.0"
description = "Persistent dict in Python, backed up by sqlite3 and pickle, multithread-safe."
category = "main"
optional = false
python-versions = "*"

sqlitedict = [
    {file = "sqlitedict-2.0.0.tar.gz", hash = "sha256:23a370416f4e1e962daa293382f3a8dbc4127e6a0abc06a5d4e58e6902f05d17"},
]

• OS: RHEL 7, Linux 3.10.0-1062.el7.x86_64

More info and trace

Here's a reproduction, with more info copied from my terminal. In general the code works fine up until some time into execution, when it mysteriously fails. Re-running the code on the same limited chunk (at point of failure) does not reproduce the error, so it is somewhat stochastic; could have something to do with the filesystem i/o.

Exception will be re-raised at next call.
An exception occurred from a previous statement, view the logging namespace "sqlitedict" for outer stack.
_sentences_seen _lines_read _current_file _token_stats _pair_stats _skip_pair_stats _triplet_stats _files _total _n_sentences Traceback (most recent call last):
  File "/net/vast-storage/scratch/vast/evlab/asathe/code/composlang/notebooks/chain_caches.py", line 24, in <module>
    c = ChainedCorpus(files, cache_dir=cache_dir, cache_tag=cache_tag)
  File "/net/vast-storage/scratch/vast/evlab/asathe/code/composlang/composlang/corpus.py", line 498, in __init__
    if load: self.load_cache()
  File "/net/vast-storage/scratch/vast/evlab/asathe/code/composlang/composlang/corpus.py", line 525, in load_cache
    self.to_cache(cache_dir=self._cache_dir, cache_tag=self._cache_tag)
  File "/net/vast-storage/scratch/vast/evlab/asathe/code/composlang/composlang/corpus.py", line 540, in to_cache
    super().to_cache()
  File "/net/vast-storage/scratch/vast/evlab/asathe/code/composlang/composlang/corpus.py", line 217, in to_cache
    self.cache.commit()
  File "/home/asathe/om2-home/anaconda3/envs/composlang/lib/python3.9/site-packages/sqlitedict.py", line 317, in commit
    self.conn.commit(blocking)
  File "/home/asathe/om2-home/anaconda3/envs/composlang/lib/python3.9/site-packages/sqlitedict.py", line 553, in commit
    self.select_one('--commit--')
  File "/home/asathe/om2-home/anaconda3/envs/composlang/lib/python3.9/site-packages/sqlitedict.py", line 543, in select_one
    return next(iter(self.select(req, arg)))
  File "/home/asathe/om2-home/anaconda3/envs/composlang/lib/python3.9/site-packages/sqlitedict.py", line 535, in select
    self.check_raise_error()
  File "/home/asathe/om2-home/anaconda3/envs/composlang/lib/python3.9/site-packages/sqlitedict.py", line 499, in check_raise_error
    reraise(e_type, e_value, e_tb)
  File "/home/asathe/om2-home/anaconda3/envs/composlang/lib/python3.9/site-packages/sqlitedict.py", line 47, in reraise
    raise value
  File "/home/asathe/om2-home/anaconda3/envs/composlang/lib/python3.9/site-packages/sqlitedict.py", line 425, in run
    cursor.execute(req, arg)
sqlite3.InterfaceError: Error binding parameter 1 - probably unsupported type.

The .commit() method is called here, in case it sheds any light: https://github.com/aalok-sathe/composlang/blob/7a2fa36fa87ce5be52775bac0fc19447b8b040fa/composlang/corpus.py#L207

[aalok-sathe]

as a partial workaround, maybe we could add an option to backup the SQLite DB on each commit call (default: False, since this will probably be an expensive operation in terms of time and mem), and in case of failure, revert previous state, and retry k times? I could do this manually in top-level module, but would be more elegant if handled within sqlitedict

[piskvorky]

Thanks for the additional info. If the error is not deterministic, this will be a tricky. I'll try to google around.

-1 on bypassing DB errors by copying the DB… who knows what else is wrong, plus such copying can be done at the application level, like you said.

[aalok-sathe]

Is there a way to find out what object, if at all, when being committed, caused an error in sqlite3? That would give us a hint about the point of failure. I don't mean the top-level object, I mean the specific value. Maybe this can be phrased better, I'm unfortunately not too familiar with sqlite3.
Thanks!