OperationalError: (sqlite3.OperationalError) Cannot add a NOT NULL column with default value NULL

[minxu74]

The problem

1. clone the latest main
2. cd climate_ref
3. uv sync
4. source .venv/bin/activate
5. ref datasets --help

The error:

│ /autofs/nccsopen-svm1_home/mfx/MyGit/climate-ref/.venv/lib64/python3.11/site-packages/sqlalchemy/engine/base.py:1964 in _exec_single_context                                                                                                             │
│                                                                                                                                                                                                                                                          │
│   1961 │   │   │   │   │   │   │   evt_handled = True                                           ╭─────────────────────────────────────────────────── locals ───────────────────────────────────────────────────╮                                         │
│   1962 │   │   │   │   │   │   │   break                                                        │              context = <sqlalchemy.dialects.sqlite.base.SQLiteExecutionContext object at 0x7f6289fecd10>     │                                         │
│   1963 │   │   │   │   if not evt_handled:                                                      │               cursor = <sqlite3.Cursor object at 0x7f628a643540>                                             │                                         │
│ ❱ 1964 │   │   │   │   │   self.dialect.do_execute(                                             │              dialect = <sqlalchemy.dialects.sqlite.pysqlite.SQLiteDialect_pysqlite object at 0x7f628c8fe3d0> │                                         │
│   1965 │   │   │   │   │   │   cursor, str_statement, effective_parameters, context             │ effective_parameters = ()                                                                                    │                                         │
│   1966 │   │   │   │   │   )                                                                    │          evt_handled = False                                                                                 │                                         │
│   1967                                                                                          │           parameters = [()]                                                                                  │                                         │
│                                                                                                 │                 self = <sqlalchemy.engine.base.Connection object at 0x7f628a7e9e50>                          │                                         │
│                                                                                                 │            statement = <sqlalchemy.dialects.sqlite.base.SQLiteDDLCompiler object at 0x7f6289fecd50>          │                                         │
│                                                                                                 │        str_statement = 'ALTER TABLE metric_value ADD COLUMN type VARCHAR(6) NOT NULL'                        │                                         │
│                                                                                                 ╰──────────────────────────────────────────────────────────────────────────────────────────────────────────────╯                                         │
│                                                                                                                                                                                                                                                          │
│ /autofs/nccsopen-svm1_home/mfx/MyGit/climate-ref/.venv/lib64/python3.11/site-packages/sqlalchemy/engine/default.py:945 in do_execute                                                                                                                     │
│                                                                                                                                                                                                                                                          │
│    942 │   │   cursor.executemany(statement, parameters)                                        ╭────────────────────────────────────────────── locals ──────────────────────────────────────────────╮                                                   │
│    943 │                                                                                        │    context = <sqlalchemy.dialects.sqlite.base.SQLiteExecutionContext object at 0x7f6289fecd10>     │                                                   │
│    944 │   def do_execute(self, cursor, statement, parameters, context=None):                   │     cursor = <sqlite3.Cursor object at 0x7f628a643540>                                             │                                                   │
│ ❱  945 │   │   cursor.execute(statement, parameters)                                            │ parameters = ()                                                                                    │                                                   │
│    946 │                                                                                        │       self = <sqlalchemy.dialects.sqlite.pysqlite.SQLiteDialect_pysqlite object at 0x7f628c8fe3d0> │                                                   │
│    947 │   def do_execute_no_params(self, cursor, statement, context=None):                     │  statement = 'ALTER TABLE metric_value ADD COLUMN type VARCHAR(6) NOT NULL'                        │                                                   │
│    948 │   │   cursor.execute(statement)                                                        ╰────────────────────────────────────────────────────────────────────────────────────────────────────╯                                                   │
╰──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────╯
OperationalError: (sqlite3.OperationalError) Cannot add a NOT NULL column with default value NULL
[SQL: ALTER TABLE metric_value ADD COLUMN type VARCHAR(6) NOT NULL]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

Definition of "done"

Additional context

[minxu74]

Interesting. It only happened on one HPC and no problem on others.

[lewisjared]

@minxu74 I think you found a work around to this by not using any system packages? By default an old system verison was being used.

[minxu74]

@minxu74 I think you found a work around to this by not using any system packages? By default an old system verison was being used.

Yes. I use the uv env variable named UV_SYSTEM_PYTHON to let UV

1. Ignore the system-wide Python interpreter
2. Avoid linking or depending on system-installed Python libraries

Using the UV_SYSTEM_PYTHON=0 uv sync instead of us sync in the step 3 mentioned above.