#How to apply this file

Each section opens with one imperative line; apply every rule in the section it introduces. Do not summarise or skip a section.

---

#Purpose

Rules specific to MySQL/MariaDB with InnoDB. Portable schema design is Database/schema-design. This package covers the defaults that differ from every other engine and the ones that lose data quietly.

---

#Non-negotiable defaults

[INST] Apply every rule in this section: Non-negotiable defaults. [/INST]

sql
-- Verify before shipping anything
SELECT @@sql_mode, @@character_set_server, @@collation_server,
       @@innodb_file_per_table, @@transaction_isolation;

Never ship on a server where sql_mode omits strict mode. It is the single largest source of silent data corruption in MySQL deployments — an INSERT of a 300-character value into varchar(255) succeeds, truncated, with a warning nobody reads.

Never use utf8/utf8mb3. It is a three-byte subset that rejects any four-byte codepoint. Use utf8mb4 for every column, table, connection, and client.

---

#InnoDB and the clustered index

[INST] Apply every rule in this section: InnoDB and the clustered index. [/INST]

InnoDB stores the table in the primary key. Two consequences follow, and they drive most MySQL schema decisions:

1. Every secondary index stores the primary key as its row pointer. A wide primary key inflates every index in the table.
2. Insert order matters. A monotonically increasing key appends to the right edge of the B-tree; a random key (uuid v4) writes everywhere, causing page splits and fragmentation.

sql
-- Preferred: compact, monotonic
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY

-- If UUIDs are required, store them ordered and binary
id BINARY(16) NOT NULL PRIMARY KEY   -- UUIDv7, or UUID_TO_BIN(uuid, 1) on 8.0

UUID_TO_BIN(x, 1) swaps the time-low and time-high fields so v1 UUIDs sort chronologically. Storing a UUID as char(36) costs 36 bytes in the table and 36 more in every secondary index.

---

#Locking

[INST] Apply every rule in this section: Locking. [/INST]

InnoDB's default isolation is REPEATABLE READ, which is unusual — most engines default to READ COMMITTED. At REPEATABLE READ, InnoDB takes gap locks: locking not only matching rows but the ranges between them.

sql
-- Locks a gap; a concurrent INSERT into the same range blocks
SELECT * FROM orders WHERE created_at > '2026-01-01' FOR UPDATE;

This causes deadlocks that do not appear on other engines. Options, in order:

• Lock by primary key where possible, and always in a consistent order.
• Keep transactions short — never hold one across an HTTP call.
• Set transaction_isolation = READ-COMMITTED if gap locks are causing contention and your application does not rely on repeatable reads.

Diagnose with:

sql
SHOW ENGINE INNODB STATUS\G           -- LATEST DETECTED DEADLOCK section
SELECT * FROM performance_schema.data_locks;

Deadlocks are normal under concurrency. Retry the losing transaction — do not try to eliminate deadlocks entirely. → Database/transactions

---

#Schema changes

[INST] Apply every rule in this section: Schema changes. [/INST]

MySQL 8.0 supports ALGORITHM=INSTANT for a growing set of operations (adding a nullable column at the end, renaming, changing a default). Anything else copies or rebuilds the table, holding a metadata lock.

sql
ALTER TABLE orders ADD COLUMN note text NULL, ALGORITHM=INSTANT;

If INSTANT is rejected, the operation is not instant — use gh-ost or pt-online-schema-change on a large table rather than accepting the rebuild. Both work by building a shadow table and replaying binlog changes, then swapping.

Never run a blocking ALTER on a large table in business hours. A metadata lock queues behind any open transaction and then blocks every subsequent query on that table, including reads. → Database/migration

---

#Replication

[INST] Apply every rule in this section: Replication. [/INST]

Replicas are asynchronous by default: a read immediately after a write may not see it. Route read-after-write to the primary, or use semi-synchronous replication. → Database/replication

---

#Anti-patterns

[INST] Apply every rule in this section: Anti-patterns. [/INST]

---

#Checklist

• Verify: sql_mode includes STRICT_TRANS_TABLES
• Verify: Server, database, table, column and connection charset are all utf8mb4
• Verify: Collation is utf8mb4_0900_ai_ci or utf8mb4_unicode_ci, not general_ci
• Verify: All tables use InnoDB with innodb_file_per_table = ON
• Verify: Primary keys are compact and monotonically increasing
• Verify: UUID keys, where used, are BINARY(16) and time-ordered
• Verify: Transactions are short and contain no network calls
• Verify: Deadlock retry with backoff is implemented
• Verify: Large ALTERs use ALGORITHM=INSTANT or an online schema-change tool
• Verify: binlog_format = ROW and GTIDs are enabled
• Verify: Read-after-write traffic is routed to the primary