pathwaycom/pathway · error · ValueError

pw.io.mysql.read primary_key column(s) {nullable_pks} are de

Error message

pw.io.mysql.read primary_key column(s) {nullable_pks} are declared nullable; primary-key columns must be non-nullable so the connector can derive a unique row identity. NULL values would collide on the same Pathway key and change tracking would silently merge unrelated rows.

What it means

The MySQL connector derives each row's Pathway key from the schema's primary-key columns; NULL key parts would make distinct rows collide on the same key, so change tracking would silently merge unrelated rows. pw.io.mysql.read therefore raises this ValueError at call time when any primary-key column is typed Optional, listing the offending names.

Source

Thrown at python/pathway/io/mysql/__init__.py:202

    """
    _check_entitlements("mysql")

    schema, api_schema = read_schema(schema)

    primary_key_columns = schema.primary_key_columns()
    if not primary_key_columns:
        raise ValueError(
            "pw.io.mysql.read requires at least one primary key column in the schema. "
            "Mark the column(s) that form the table's primary key with "
            "pw.column_definition(primary_key=True)."
        )

    pk_dtypes = schema._dtypes()
    nullable_pks = [
        name for name in primary_key_columns if isinstance(pk_dtypes[name], dt.Optional)
    ]
    if nullable_pks:
        raise ValueError(
            f"pw.io.mysql.read primary_key column(s) {nullable_pks} are declared "
            "nullable; primary-key columns must be non-nullable so the connector "
            "can derive a unique row identity. NULL values would collide on the "
            "same Pathway key and change tracking would silently merge unrelated "
            "rows."
        )

    is_streaming = mode == "streaming"

    data_storage = api.DataStorage(
        storage_type="mysql",
        connection_string=connection_string,
        table_name=table_name,
        mode=(
            api.ConnectorMode.STREAMING if is_streaming else api.ConnectorMode.STATIC
        ),
        mysql_server_id=server_id,
    )

View on GitHub (pinned to fa2f74a464)

Solutions

1. Change the primary-key column annotation from Optional[int] to int (MySQL PRIMARY KEY columns are NOT NULL by definition).
2. If NULLs actually occur in that column, it is not a real key — choose a different column or column set.
3. Keep Optional only on genuinely nullable non-key payload columns.

Example fix

# before
class MySchema(pw.Schema):
    id: int | None = pw.column_definition(primary_key=True)

# after
class MySchema(pw.Schema):
    id: int = pw.column_definition(primary_key=True)

import pathway as pw

dtypes = schema._dtypes()
nullable = [c for c in schema.primary_key_columns() if isinstance(dtypes[c], pw.dt.Optional)]
assert not nullable, f"Nullable PK columns: {nullable}"

import pathway as pw

def has_non_nullable_pks(schema) -> bool:
    d = schema._dtypes()
    return all(not isinstance(d[c], pw.dt.Optional) for c in schema.primary_key_columns())

try:
    table = pw.io.mysql.read(conn, table_name, schema=MySchema)
except ValueError as e:
    if "nullable" in str(e):
        raise SystemExit("Change PK annotations from Optional[...] to plain int/str") from e
    raise

When it happens

Trigger: Declaring a key column as Optional in the schema passed to pw.io.mysql.read, e.g. id: int | None = pw.column_definition(primary_key=True).

Common situations: Schemas generated from ORM models or introspection tools that mark everything nullable; defensive | None annotations copied from dataclass conventions; unioning schemas from heterogeneous sources.

Related errors

• pw.io.mssql.read primary_key column(s) {nullable_pks} are de
• pw.io.mysql.read requires at least one primary key column in
• primary_key column {pkey.name!r} is declared nullable ({pkey
• Iceberg reader requires explicit primary key fields specific
• Defining a primary key in the schema is not supported for pw

AI-assisted analysis of pathwaycom/pathway@fa2f74a464 (2026-08-15). Data as JSON: /api/errors/a9d451ff38bee3eb. Report an issue: GitHub.