Mariadb

---
name: MariaDB
slug: mariadb
version: 1.0.0
description: Write efficient MariaDB queries with proper indexing, temporal tables, and clustering.
homepage: https://clawic.com/skills/mariadb
metadata:
  clawdbot:
    emoji: 🦭
    requires:
      bins:
      - mariadb
    os:
    - linux
    - darwin
    - win32
    displayName: MariaDB
---

## Character Set

- Always use `utf8mb4` for tables and connections—full Unicode including emoji
- `utf8mb4_unicode_ci` for proper linguistic sorting, `utf8mb4_bin` for byte comparison
- Set connection charset: `SET NAMES utf8mb4` or in connection string
- Collation mismatch in JOINs forces conversion—kills index usage

## Indexing

- TEXT/BLOB columns need prefix length: `INDEX (description(100))`
- Composite index order matters—`(a, b)` serves `WHERE a=?` but not `WHERE b=?`
- Foreign keys auto-create index on child table—but verify with `SHOW INDEX`
- Covering indexes: include all SELECT columns to avoid table lookup

## Sequences

- `CREATE SEQUENCE seq_name` for guaranteed unique IDs across tables
- `NEXT VALUE FOR seq_name` to get next—survives transaction rollback
- Better than auto-increment when you need ID before insert
- `SETVAL(seq_name, n)` to reset—useful for migrations

## System Versioning (Temporal Tables)

- `ALTER TABLE t ADD SYSTEM VERSIONING` to track all historical changes
- `FOR SYSTEM_TIME AS OF '2024-01-01 00:00:00'` queries past state
- `FOR SYSTEM_TIME BETWEEN start AND end` for change history
- Invisible columns `row_start` and `row_end` store validity period

## JSON Handling

- `JSON_VALUE(col, '$.key')` extracts scalar, returns NULL if not found
- `JSON_QUERY(col, '$.obj')` extracts object/array with quotes preserved
- `JSON_TABLE()` converts JSON array to rows—powerful for unnesting
- `JSON_VALID()` before insert if column isn't strictly typed

## Galera Cluster

- All nodes writable—but same-row conflicts cause rollback
- `wsrep_sync_wait = 1` before critical reads—ensures node is synced
- Keep transactions small—large transactions increase conflict probability
- `wsrep_cluster_size` should be odd number—avoids split-brain

## Window Functions

- `ROW_NUMBER() OVER (PARTITION BY x ORDER BY y)` for ranking within groups
- `LAG(col, 1) OVER (ORDER BY date)` for previous row value
- `SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING)` for running total
- CTEs with `WITH cte AS (...)` for readable complex queries

## Thread Pool

- Enable with `thread_handling=pool-of-threads`—better than thread-per-connection
- `thread_pool_size` = CPU cores for CPU-bound, higher for I/O-bound
- Reduces context switching with many concurrent connections
- Monitor with `SHOW STATUS LIKE 'Threadpool%'`

## Storage Engines

- InnoDB default—ACID transactions, row locking, crash recovery
- Aria for temporary tables—crash-safe replacement for MyISAM
- MEMORY for caches—data lost on restart, but fast
- Check engine: `SHOW TABLE STATUS WHERE Name='table'`

## Locking

- `SELECT ... FOR UPDATE` locks rows until commit
- `LOCK TABLES t WRITE` for DDL-like exclusive access—blocks all other sessions
- Deadlock detection automatic—one transaction rolled back; must retry
- `innodb_lock_wait_timeout` default 50s—lower for interactive apps

## Query Optimization

- `EXPLAIN ANALYZE` for actual execution times (10.1+)
- `optimizer_trace` for deep dive: `SET optimizer_trace='enabled=on'`
- `FORCE INDEX (idx)` when optimizer chooses wrong index
- `STRAIGHT_JOIN` to force join order—last resort

## Backup and Recovery

- `mariadb-dump --single-transaction` for consistent backup without locks
- `mariadb-backup` for hot InnoDB backup—incremental supported
- Binary logs for point-in-time recovery: `mysqlbinlog binlog.000001 | mariadb`
- Test restores regularly—backups that can't restore aren't backups

## Common Errors

- "Too many connections"—increase `max_connections` or use connection pool
- "Lock wait timeout exceeded"—find blocking query with `SHOW ENGINE INNODB STATUS`
- "Row size too large"—TEXT/BLOB stored off-page, but row pointers have limits
- "Duplicate entry for key"—check unique constraints, use `ON DUPLICATE KEY UPDATE`