Error with keymap sync

[sirex]

When running spinta push command, I got following error:

╭─────────────────────────────── Traceback (most recent call last) ────────────────────────────────╮
│ spinta/cli/push.py:204 in push                                                                   │
│                                                                                                  │
│    201 │   │   │   │   synchronize = True                                                        │
│    202 │   │   │                                                                                 │
│    203 │   │   │   dependant_models = extract_dependant_nodes(models, not synchronize)           │
│ ❱  204 │   │   │   sync_keymap(                                                                  │
│    205 │   │   │   │   context=context,                                                          │
│    206 │   │   │   │   keymap=km,                                                                │
│    207 │   │   │   │   client=client,                                                            │
│                                                                                                  │
│ ╭─────────────────────────────────────────── locals ───────────────────────────────────────────╮ │
│ │            dataset = 'datasets/gov/vpt/sutartys'                                             │ │
│ │         first_time = True                                                                    │ │
│ │        incremental = False                                                                   │ │
│ │              limit = None                                                                    │ │
│ │               mode = <Mode.external: 'external'>                                             │ │
│ │    no_progress_bar = True                                                                    │ │
│ │             output = 'get.data.gov.lt'                                                       │ │
│ │               page = []                                                                      │ │
│ │         page_model = None                                                                    │ │
│ │        retry_count = 5                                                                       │ │
│ │      stop_on_error = False                                                                   │ │
│ │           stop_row = None                                                                    │ │
│ │          stop_time = None                                                                    │ │
│ │        synchronize = True                                                                    │ │
│ ╰──────────────────────────────────────────────────────────────────────────────────────────────╯ │
│                                                                                                  │
│ spinta/datasets/keymaps/synchronize.py:74 in                                                     │
│ sync_keymap                                                                                      │
│                                                                                                  │
│    71 │   │   │   │   sync_cid = row['_cid']                                                     │
│    72 │   │   │                                                                                  │
│    73 │   │   │   for keymap_name in model_keymaps:                                              │
│ ❱  74 │   │   │   │   keymap.update_sync_data(keymap_name, sync_cid, datetime.datetime.now())    │
│    75 │   │   │                                                                                  │
│    76 │   │   │   if model.model_type() in counters:                                             │
│    77 │   │   │   │   for counter in counters[model.model_type()].values():                      │
│                                                                                                  │
│ ╭─────────────────────────────────────────── locals ───────────────────────────────────────────╮ │
│ │             row = {                                                                          │ │
│ │                   │   '_cid': 16190,                                                         │ │
│ │                   │   '_created': '2023-05-22T15:36:07.988145',                              │ │
│ │                   │   '_op': 'insert',                                                       │ │
│ │                   │   '_id': '387cc596-b883-4497-a67f-393247861b4d',                         │ │
│ │                   │   '_txn': '97c8f95a-32b0-4898-a7d7-dedb6ccfd9c3',                        │ │
│ │                   │   '_revision': '4d2820e1-ca45-4553-b01c-dd4164031ded',                   │ │
│ │                   │   'dok_id': 2002897085,                                                  │ │
│ │                   │   'dok_atask_id': None,                                                  │ │
│ │                   │   'dok_formos_tipas': 1,                                                 │ │
│ │                   │   'dok_reg_data': None,                                                  │ │
│ │                   │   ... +23                                                                │ │
│ │                   }                                                                          │ │
│ │          server = 'https://get.data.gov.lt'                                                  │ │
│ │     status_code = 200                                                                        │ │
│ │        sync_cid = 16190                                                                      │ │
│ │             url = 'https://get.data.gov.lt/datasets/gov/vpt/sutartys/VptNSutartys/:changes'  │ │
│ ╰──────────────────────────────────────────────────────────────────────────────────────────────╯ │
│                                                                                                  │
│ spinta/datasets/keymaps/sqlalchemy.py:119 in                                                     │
│ update_sync_data                                                                                 │
│                                                                                                  │
│   116 │   │   │   index_elements=[table.c.model],                                                │
│   117 │   │   │   set_=dict(cid=cid, updated=time)                                               │
│   118 │   │   )                                                                                  │
│ ❱ 119 │   │   self.conn.execute(query)                                                           │
│   120 │                                                                                          │
│   121 │   def synchronize(self, name: str, value: Any, primary_key: str):                        │
│   122 │   │   table = self.get_table(name)                                                       │
│                                                                                                  │
│ ╭─────────────────────────────────────────── locals ───────────────────────────────────────────╮ │
│ │   cid = 16190                                                                                │ │
│ │  name = 'datasets/gov/vpt/sutartys/VptNSutartys'                                             │ │
│ │ table = Table('_synchronize', ...)                                                           │ │
│ │  time = datetime.datetime(2023, 10, 4, 6, 11, 34, 740914)                                    │ │
│ ╰──────────────────────────────────────────────────────────────────────────────────────────────╯ │
╰──────────────────────────────────────────────────────────────────────────────────────────────────╯
OperationalError: (sqlite3.OperationalError) near "ON": syntax error
SQL:
    INSERT INTO _synchronize (model, cid, updated)
    VALUES (?, ?, ?)
    ON CONFLICT (model)
    DO UPDATE SET cid = ?, updated = ?
parameters:
    'datasets/gov/vpt/sutartys/VptNSutartys',
    16190,
    '2023-10-04 06:11:34.740914',
    16190,
    '2023-10-04 06:11:34.740914'

I think, this might be related to SQLite version:

$ python -c "import sqlite3; print(sqlite3.version)"
2.6.0

Most servers run very old SQLite version and we don't have any requirements, for SQLite version. Maybe it is possible to use less fancy SQL query?

SQLite documentation says:

UPSERT syntax was added to SQLite with version 3.24.0 (2018-06-04).

Since this is not a first time, when we get errors because of old SQLite version, it would be nice, to add 2.6.0 SQLite version to the CI, so that we would not miss such errors next time.

Another option, would be to use pysqlite-binary, which comes with a newer SQLite version:

https://github.com/pysqlite3/pysqlite3

Related

• Sync push state from push target #289
• Add support for older SQLite versions #411