MySqlMariaChangesToDefaultBehavior - pengdows/pengdows.crud GitHub Wiki
pengdows.crud applies a set of session-level overrides to ensure MySQL and MariaDB behave more like standards-compliant SQL engines and to prevent common forms of silent data corruption.
SET SESSION sql_mode = 'STRICT_ALL_TABLES,ONLY_FULL_GROUP_BY,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION,ANSI_QUOTES';
These session settings are injected automatically when a connection is opened. They ensure:
| SQL Mode | Description | Rationale |
|---|---|---|
| STRICT_ALL_TABLES | Rejects invalid or out-of-range data on INSERT or UPDATE | Prevents silent truncation or coercion of data |
| ONLY_FULL_GROUP_BY | Enforces ANSI-compliant GROUP BY behavior | Prevents ambiguous queries from executing with non-deterministic results |
| NO_ZERO_DATE | Disallows use of '0000-00-00' as a date value | Ensures proper coercion to .NET DateTime and avoids edge-case behavior |
| NO_ENGINE_SUBSTITUTION | Fails if a requested engine is unavailable | Prevents silent fallback to engines lacking key features (e.g., foreign keys) |
| ANSI_QUOTES | Treats double quotes ("") as identifier quotes, not string literals | Aligns MySQL/MariaDB with ANSI SQL behavior and other DB engines |
- These settings may cause legacy or loosely written queries to fail (intentionally)
- They force explicitness and safety in your SQL and schema definitions
- Queries and schema that run under these settings are far more portable across DB engines
With ANSI_QUOTES enabled:
- Use
'for string literals - Use
"for object identifiers
- PostgreSQL
- SQL Server (when
QUOTED_IDENTIFIER ON) - Oracle
- If your schema or queries rely on non-strict behavior, you will need to update them.
- You should review old migrations or third-party tooling for unsafe defaults.