Fix MariaDB CHECK constraint quoting that crash-looped startup seeding #103

2026-10-05T18:32:07+05:30

ImanThiyanga commented

2026-10-05 18:32:07 +05:30

Three CHECK constraints shipped with PostgreSQL identifier quoting left over
from the MySQL port:

ck_grn_config_singleton CHECK ("ConfigId" = 1)
ck_sales_config_singleton CHECK ("ConfigId" = 1)
ck_warranty_periods_months CHECK ("Months" BETWEEN 1 AND 600)

ANSI_QUOTES is not in MariaDB's default sql_mode, so a double-quoted token is a
string literal, not an identifier. "ConfigId" = 1 therefore evaluates as
'ConfigId' = 1; MariaDB coerces the string to a number to compare it and,
under strict mode, raises Truncated incorrect DECIMAL value: 'ConfigId'.

The DDL creates fine and only breaks at runtime, so this got through the
deploy and then failed every INSERT into grn_config, sales_config and
warranty_periods. That includes DataSeeder's two singleton config rows, which
run immediately after MigrateAsync() in Program.cs — so the service threw
"Database migration succeeded, but startup seeding failed" on every boot and
systemd restart-looped (counter reached 110, each restart dumping core).

Fixed in the entity configurations and repaired on existing databases by
migration 20261005190000_FixCheckConstraintQuotingForMySql, which drops and
re-adds the three constraints with backticks. The drop/add is emitted as raw
SQL rather than DropCheckConstraint because MariaDB spells the removal
DROP CONSTRAINT, not MySQL's DROP CHECK; IF EXISTS keeps it re-runnable on a
database where the broken constraint was already removed by hand.

The identical trap was swept during the port itself; these three were
reintroduced by a feature migration written afterwards.

No data cleanup is needed: the failing inserts never committed, so the
affected tables are empty.

Three CHECK constraints shipped with PostgreSQL identifier quoting left over from the MySQL port: ck_grn_config_singleton CHECK ("ConfigId" = 1) ck_sales_config_singleton CHECK ("ConfigId" = 1) ck_warranty_periods_months CHECK ("Months" BETWEEN 1 AND 600) ANSI_QUOTES is not in MariaDB's default sql_mode, so a double-quoted token is a *string literal*, not an identifier. `"ConfigId" = 1` therefore evaluates as `'ConfigId' = 1`; MariaDB coerces the string to a number to compare it and, under strict mode, raises `Truncated incorrect DECIMAL value: 'ConfigId'`. The DDL creates fine and only breaks at runtime, so this got through the deploy and then failed every INSERT into grn_config, sales_config and warranty_periods. That includes DataSeeder's two singleton config rows, which run immediately after MigrateAsync() in Program.cs — so the service threw "Database migration succeeded, but startup seeding failed" on every boot and systemd restart-looped (counter reached 110, each restart dumping core). Fixed in the entity configurations and repaired on existing databases by migration 20261005190000_FixCheckConstraintQuotingForMySql, which drops and re-adds the three constraints with backticks. The drop/add is emitted as raw SQL rather than DropCheckConstraint because MariaDB spells the removal DROP CONSTRAINT, not MySQL's DROP CHECK; IF EXISTS keeps it re-runnable on a database where the broken constraint was already removed by hand. The identical trap was swept during the port itself; these three were reintroduced by a feature migration written afterwards. No data cleanup is needed: the failing inserts never committed, so the affected tables are empty.

ImanThiyanga added 1 commit 2026-10-05 18:32:07 +05:30

Fix MariaDB CHECK constraint quoting that crash-looped startup seeding c97a7e9ae6

Three CHECK constraints shipped with PostgreSQL identifier quoting left over
from the MySQL port:

  ck_grn_config_singleton       CHECK ("ConfigId" = 1)
  ck_sales_config_singleton     CHECK ("ConfigId" = 1)
  ck_warranty_periods_months    CHECK ("Months" BETWEEN 1 AND 600)

ANSI_QUOTES is not in MariaDB's default sql_mode, so a double-quoted token is a
*string literal*, not an identifier. `"ConfigId" = 1` therefore evaluates as
`'ConfigId' = 1`; MariaDB coerces the string to a number to compare it and,
under strict mode, raises `Truncated incorrect DECIMAL value: 'ConfigId'`.

The DDL creates fine and only breaks at runtime, so this got through the
deploy and then failed every INSERT into grn_config, sales_config and
warranty_periods. That includes DataSeeder's two singleton config rows, which
run immediately after MigrateAsync() in Program.cs — so the service threw
"Database migration succeeded, but startup seeding failed" on every boot and
systemd restart-looped (counter reached 110, each restart dumping core).

Fixed in the entity configurations and repaired on existing databases by
migration 20261005190000_FixCheckConstraintQuotingForMySql, which drops and
re-adds the three constraints with backticks. The drop/add is emitted as raw
SQL rather than DropCheckConstraint because MariaDB spells the removal
DROP CONSTRAINT, not MySQL's DROP CHECK; IF EXISTS keeps it re-runnable on a
database where the broken constraint was already removed by hand.

The identical trap was swept during the port itself; these three were
reintroduced by a feature migration written afterwards.

No data cleanup is needed: the failing inserts never committed, so the
affected tables are empty.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>

ImanThiyanga added 1 commit 2026-10-05 18:32:49 +05:30

Merge branch 'production' into Dev

CI/CD - ERP Core Monorepo / Build & Deploy Frontend (pull_request) Failing after 14m4s

Details

CI/CD - ERP Core Monorepo / Build & Deploy Backend (pull_request) Failing after 15m25s

Details

ea8c8210e5

ImanThiyanga merged commit 533b642367 into production

2026-10-05 18:32:55 +05:30

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: New_REP_SYSTEM/ERP-core#103