mysql

MySQL / MariaDB Production Standards

MySQL and MariaDB are relationship-driven databases, but AI agents MUST adhere to these strict behavioral boundaries when executing queries, utilizing the mysql-mcp server, or generating application code.

[!NOTE] Actively target 8.4 LTS or 9.0+ for production environments. Both MySQL 9.0+ and MariaDB now feature native VECTOR data types and search capabilities.

1. Query Safety & Execution Rules

• Absolute Parameterization: You MUST ALWAYS use parameterized queries (?). Under zero circumstances are you permitted to string-interpolate or concatenate variables into a raw SQL string. This is to enforce strict SQL-injection prevention.
• Guarded Reads: Every top-level SELECT statement MUST contain a LIMIT clause. Never use SELECT * in production code; always enumerate required columns explicitly.
• Safe Destructive Ops: DELETE and UPDATE queries MUST include a WHERE clause.
• DDL Destructive Ops: TRUNCATE TABLE, DROP TABLE, DROP DATABASE, ALTER TABLE ... DROP COLUMN, and ALTER TABLE ... RENAME COLUMN MUST require explicit user confirmation before execution. Never infer intent for these.
• Action Scoping: Limit operations to single-statements. Do not stack multiple statements (query1; query2;) in a single payload unless executing a batch migration.
• Performance Validation: ALWAYS run EXPLAIN (or EXPLAIN ANALYZE in MySQL 9.0+ for JSON output) before suggesting index optimizations or query rewrites to the user.

2. Connections & Transactions

• Connection Pooling: Always assume and design for connection pooling. Code evaluating database connections should handle acquiring from and releasing to a pool, avoiding connection leaks.
  • Sizing Formula: pool_size = (max_connections - reserved_admin) / app_instances.
  • Multiplexing: Utilize ProxySQL to handle connection multiplexing and read/write splitting. CRITICAL: Avoid anti-patterns that pin connections and kill multiplexing efficiency (e.g., long-running transactions, temporary tables, SET @var, and caching prepared statements on the client without closing them).
• Data Mutability Scopes: Whenever executing writes spanning across multiple tables (e.g., inserts requiring foreign key links), they MUST be wrapped in a transaction block. You MUST implement a ROLLBACK in every catch block for these operations.